Skip to content
Gyancs
Menu
  • Home
  • About Us
    • Contact Us
  • Course
    • C LANGUAGE
    • LINUX
    • CISCO
    • MICROPROCESSOR
    • Cryptography
    • DBMS
      • SQL Introduction
      • SQL Table Query
  • ONLINE COMPILER
    • C COMPILER
    • Java Compiler
    • Python Compiler
  • Computer Study
    • O Level
    • CCC NIELIT
    • MS-Office MCQ
  • CCC Mock Test
  • O Level Mock Test
  • NEET EXAM

How to Write an Oracle Query in SQL

27th April 202210th May 2022 B.ZAMANLeave a Comment on How to Write an Oracle Query in SQL

How to Write an Oracle Query in SQL

What is constraint?
Constraints are set of rules that are applied to a Table.

  1. NOT NULL: – Not Null constrain states that a field/attribute/column can’t be left blank
  2. PRIMARY KEY: – Primary Key constraint states that a field/attribute column can’t contain duplicate value and it can’t be left blank.
  3. UNIQUE KEY: – Unique key constraint states that a field/attribute/column can’t contain duplicate value.
  4. DEFAULT KEY: – Constraint states that a column/filed/attribute may be given a default value when no values are passed for that column.
  5. CHECK: – Check constraints states that field/column/attribute can be check for a particular condition before entering it into the table. Exp:-SAL>10000.
  6. FOREIGN KEY: – When a Key/Field is one Table is playing the role a non-primary key then the non-primary key will be referred to as foreign key.

 

  1. NOT NULL: – इसमें Blank Field नहीं छोड़ सकते ।
  2. PRIMARY KEY:-इसमें डुप्लीकेट रिकॉर्ड नही भर सकते और न ही इसे Blank छोड़ सकते एक टेबल में सिर्फ एक ही Primary Key होती है ।
  3. UNIQUE KEY:-इसमें भी डुप्लीकेट रिकॉर्ड नहीं भर सकते पर इसमें Blank record छोड़ सकते हैं ।
  4. DEFAULT KEY:-अगर आपने किसी Field में Default Value set कर रखी है और उस Field में कुछ भी Entry नहीं कर रहे हैं तो Default Value उठा लेगा ।
  5. CHECK: – Table में कुछ डालने से पहले कुछ चेक करना चाहते हैं जैसे SAL>1000.
  6. FOREIGN KEY:-Foreign Key किसी Table का Foreign Key किसी दुसरे Table के Primary Key को Point करता है । Foreign Key Null हो सकता है Foreign Key Duplicate भी हो सकता है। Foreign Key हमेशा वही Value Accept करता है जो Parent Table में Available है Primary Key बनाने के लिए Column के साथ Primary Key Keyword का Use करते हैं लेकिन Foreign Key बनाने के लिए Column के साथ हम लोग Primary Key नही बल्कि हम लोग References और Parent Table का नाम Use करते हैं ।

SQL in Oracle

Create DEPT Table in  Oracle,

CREATE TABLE dept (
deptno NUMBER(2,0),
dname VARCHAR2(14),
loc VARCHAR2(13),
PRIMARY KEY (deptno)
);

 SQL> desc dept;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

Values in DEPT table,

SQL>INSERT INTO dept VALUES(10, ‘ACCOUNTING’, ‘NEW YORK’);
SQL>INSERT INTO dept VALUES(20, ‘RESEARCH’, ‘DALLAS’);
SQL>INSERT INTO dept VALUES(30, ‘SALES’, ‘CHICAGO’);
SQL>INSERT INTO dept VALUES(40, ‘OPERATIONS’, ‘BOSTON’);
COMMIT;

Don’t forget to commit the records else records won’t be inserted permanently. It will be stored only in the buffer. Finally, the DEPT table records are,

 SQL> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
 SQL> SELECT deptno, dname FROM dept;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

EMP Table in SQL

The structure of the emp table in oracle is given below. Create EMP Table in SQL,

 CREATE TABLE emp (
  empno    NUMBER(4,0),
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4,0),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2,0),
  PRIMARY KEY (empno),
  FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
 SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

The employee table in Oracle contains empno, ename, job, mgr, hiredate, sal, comm, deptno columns.It also contains the primary key constraint on empno column, a foreign key constraint with deptno column.

The values for the EMP Table are,

 SQL>INSERT INTO emp VALUES(
 7839, 'KING', 'PRESIDENT', null,
 to_date('17-11-2022','dd-mm-yyyy'),
 5000, null, 10 );

SQL>INSERT INTO emp VALUES(
 7698, 'BLAKE', 'MANAGER', 7839,
 to_date('1-5-2022','dd-mm-yyyy'),
 2850, null, 30);

SQL>INSERT INTO emp VALUES(
 7782, 'CLARK', 'MANAGER', 7839,
 to_date('9-6-2022','dd-mm-yyyy'),
 2450, null, 10);

SQL>INSERT INTO emp VALUES(
 7566, 'JONES', 'MANAGER', 7839,
 to_date('2-4-2022','dd-mm-yyyy'),
 2975, null, 20);

SQL>INSERT INTO emp VALUES(
 7788, 'SCOTT', 'ANALYST', 7566,
 to_date('13-JUL-87','dd-mm-rr') - 85,
 3000, null, 20);

SQL>INSERT INTO emp VALUES(
 7902, 'FORD', 'ANALYST', 7566,
 to_date('3-12-2022','dd-mm-yyyy'),
 3000, null, 20 );

SQL>INSERT INTO emp VALUES(
 7369, 'SMITH', 'CLERK', 7902,
 to_date('17-12-2022','dd-mm-yyyy'),
 800, null, 20 );

SQL>INSERT INTO emp VALUES(
 7499, 'ALLEN', 'SALESMAN', 7698,
 to_date('20-2-2022','dd-mm-yyyy'),
 1600, 300, 30);

SQL>INSERT INTO emp VALUES(
 7521, 'WARD', 'SALESMAN', 7698,
 to_date('22-2-2022','dd-mm-yyyy'),
 1250, 500, 30 );

SQL>INSERT INTO emp VALUES(
 7654, 'MARTIN', 'SALESMAN', 7698,
 to_date('28-9-2022','dd-mm-yyyy'),
 1250, 1400, 30 );

SQL>INSERT INTO emp VALUES(
 7844, 'TURNER', 'SALESMAN', 7698,
 to_date('8-9-2022','dd-mm-yyyy'),
 1500, 0, 30);

SQL>INSERT INTO emp VALUES(
 7876, 'ADAMS', 'CLERK', 7788,
 to_date('13-JUL-22', 'dd-mm-rr') - 51,
 1100, null, 20 );

SQL>INSERT INTO emp VALUES(
 7900, 'JAMES', 'CLERK', 7698,
 to_date('3-12-2022','dd-mm-yyyy'),
 950, null, 30 );

SQL>INSERT INTO emp VALUES(
 7934, 'MILLER', 'CLERK', 7782,
 to_date('23-1-2022','dd-mm-yyyy'),
 1300, null, 10 );

SQL>COMMIT;

Now let us display the emp table records. 
SQL> SET pagesize 150;  (This will change the page size to 150 rows.)
SQL> SET line 150;      (This will change the line width to 150 characters.)

 SQL> SELECT * FROM emp;
EMPNO ENAME    JOB          MGR HIREDATE      SAL  COMM  DEPTNO
----- -------- --------- ------ --------- ------- ------ ------
7839 KING      PRESIDENT        17-NOV-22    5000            10
7698 BLAKE     MANAGER     7839 01-MAY-22    2850            30
7782 CLARK     MANAGER     7839 09-JUN-22    2450            10
7566 JONES     MANAGER     7839 02-APR-22    2975            20
7788 SCOTT     ANALYST     7566 19-APR-87    3000            20
7902 FORD      ANALYST     7566 03-DEC-22    3000            20
7369 SMITH     CLERK       7902 17-DEC-22     800            20
7499 ALLEN     SALESMAN    7698 20-FEB-22    1600     300    30
7521 WARD      SALESMAN    7698 22-FEB-22    1250     500    30
7654 MARTIN    SALESMAN    7698 28-SEP-22    1250    1400    30
7844 TURNER    SALESMAN    7698 08-SEP-22    1500       0    30
7876 ADAMS     CLERK       7788 23-MAY-22    1100            20
7900 JAMES     CLERK       7698 03-DEC-22     950            30
7934 MILLER    CLERK       7782 23-JAN-22    1300            10

If you print date format full year format.

SQL> ALTER session set nls_date_format=’DD/MM/YYYY’ ;
Session altered. (After that use following command)
SQL>SELECT * from emp;

 SQL> SELECT empno, ename, job, deptno FROM emp;
     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10
14 rows selected.

From the EMP table, display the jobs where DEPTNO = 10,

SQL> SELECT job FROM emp WHERE deptno = 10;

JOB
———
MANAGER
PRESIDENT
CLERK

From the EMP table, display the jobs where DEPTNO = 20,

SQL> SELECT job FROM emp WHERE deptno = 20;

JOB
———
CLERK
MANAGER
ANALYST
CLERK
ANALYST

Create SALGRADE Table

CREATE TABLE salgrade (
grade NUMBER,
losal NUMBER,
hisal NUMBER
);

SQL>DESC salgrade;

The values for the SALGRADE table are,

SQL>INSERT INTO salgrade VALUES (1, 700, 1200);
SQL>INSERT INTO salgrade VALUES (2, 1201, 1400);
SQL>INSERT INTO salgrade VALUES (3, 1401, 2000);
SQL>INSERT INTO salgrade VALUES (4, 2001, 3000);
SQL>INSERT INTO salgrade VALUES (5, 3001, 9999);
SQL>COMMIT;

Finally, the SALGRADE table records are,

 SQL> SELECT * FROM salgrade;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

Example:-

CREATE TABLE departments
( department_id number(10) NOT NULL,
department_name varchar2(50) NOT NULL,
PRIMARY KEY (department_id)
);

SQL>DESC departments;

CREATE TABLE employees
( employee_number number(10) NOT NULL,
employee_name varchar2(50) NOT NULL,
department_id number(10),
salary number(6),
PRIMARY KEY (employee_number),
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);

SQL>DESC employees;

Rename Table

CREATE TABLE customers
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50),
PRIMARY KEY (customer_id)
);

SQL>DESC customers;

RENAME Exist tablename TO newtablename;
OR
ALTER TABLE Exist tablename RENAME TO newtablename;
Example:-

SQL> RENAME customers TO student;
Table renamed.

Add DEFAULT Value to  Table

Example:-

SQL> CREATE TABLE gyancs (
name VARCHAR2(10),
sal NUMBER(10) DEFAULT 5000
);
Table created.

SQL> INSERT INTO gyancs(name) VALUES(‘abc’);
1 row created.

 SQL> SELECT * FROM gyancs;
NAME              SAL
---------- ----------
abc              5000

Another Example:-

SQL> CREATE TABLE gyancs01 (
name VARCHAR2(10),
id NUMBER(10) DEFAULT 100,
address VARCHAR2(15) DEFAULT ‘England’
);
Table created.

SQL> INSERT INTO gyancs01(name) VALUES(‘Gyancs’);
SQL> INSERT INTO gyancs01(name, id) VALUES(‘Robart’, 11);
SQL> INSERT INTO gyancs01 VALUES(‘Colvin’, 21, ‘Ukrain’);

 SQL> SELECT * FROM gyancs01;
NAME               ID ADDRESS
---------- ---------- ---------------
Gyancs            100 England
Robart             11 England
Colvin             21 Ukrain

Delete Table

SQL> DROP Table emp;
Delete Record from Table
Delete all rows from a table

SQL>DELETE FROM sales;

i) DELETE –  one row from a table
SQL>DELETE
FROM
emp
WHERE
empno = 7782
AND deptno = 10;

ii) DELETE – multiple rows from a table

SQL>DELETE
FROM
emp
WHERE
deptno = 30;

Update Table

SQL>CREATE TABLE suppliers
( supplier_id number(10) not null,
supplier_name varchar2(50) not null,
city varchar2(50),
PRIMARY KEY (supplier_id)
);

SQL>INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (6001, ‘Microsoft’, ‘Chicago’);

SQL>INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (6002, ‘IBM’, ‘Chicago’);

SQL>INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (6003, ‘Red Hat’, ‘Detroit’);

SQL>INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (6004, ‘NVIDIA’, ‘New York’);

SQL>UPDATE suppliers
SET city = ‘San Francisco’
WHERE supplier_name = ‘IBM’;

SQL Introduction
About me

B.ZAMAN

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

COMPUTER STUDY MATERIAL

  • Meaning of Computer
  • History of Computer
  • Classification of Computer
  • Structure of Computer
  • Agent of Computer
  • Number System
  • Computer Network
  • Operating System
  • DOS Commands
  • Mail Merge in MS Word
  • MS-Word MCQ
  • Power Point Slide Creation
  • CCC Course
  • CCC Mock Test
  • O Level Course
  • O Level Mock Test
  • CCC-Full-Paper 100 Question
  • 100 Computer Question in Hindi
  • CCC Most Important 30 Questions
  • CCC 2000 Important Questions

O Level M1-R5 MCQ

  • IT Tools & Network Basic Test-1
  • IT Tools & Network Basic Test-2
  • IT Tools & Network Basic Test-3
  • IT Tools & Network Basic Test-4
  • IT Tools & Network Basic Test-5

O Level M2-R5 MCQ

  • Web Designing and Publishing MCQ-1
  • Web Designing and Publishing MCQ-2
  • Web Designing and Publishing MCQ-3
  • Web Designing and Publishing MCQ-4
  • Web Designing and Publishing MCQ-5

M3-R5 PYTHON Programming

  • What is Python
  • Python Features
  • Python Indenting Code
  • String Data Types
  • Tuple Data Type
  • Python Classes and  Object
  • Phthon Programming MCQ-1
  • Phthon Programming MCQ-2
  • Phthon Programming MCQ-3

M4-R5 Internet of Things MCQ

  • Internet of Things and Its Applications-1
  • Internet of Things and Its Applications-2
  • Internet of Things and Its Applications-3
  • Internet of Things and Its Applications-4
  • Internet of Things and Its Applications-5

Tutorial Point

  • CBNST
  • Basic C Programming
  • Advance C Programming Part 1
  • Advance C Programming Part 2
  • C Programming Book in Hindi
  • C Programming Book in English
  • Java Programming Book in Hindi
  • Download Lab Report Cover Page(FIT)
  • C Compiler
  • Java Compiler
  • Python Compiler
  • How to Change Router Name and Password
  • Cisco Router Password Encryption
  • Basic Router Configuration Cisco
  • How to Configure Banner Motd on Cisco Router
  • How to Configure Telnet in Cisco Packet Tracer
  • Switch VLAN Configuration Commands Step by Step
  • Static Route Configuration in Cisco Router
  • RIP configuration on Cisco Router
  • Port Security in Cisco Switch
  • How to configure Spanning Tree Protocol on Cisco Switch
  • How to Configure Default Route on Cisco Router
  • How to Configure DHCP in Cisco Router using Packet Tracer
  • Cisco Packet Tracer Video List
  • How to Configure EIGRP on Cisco Router step by step
  • How to Configure OSPF on Cisco Router step by step
  • What is Cryptography
  • Cryptography & Network Security
  • 8086 Instruction Hex Code
  • Write a program to two add 16 bit Hexadecimal numbers without carry
  • Write a program to two add 16 bit Hexadecimal numbers with carry
  • Write a program to find the greatest number from an array of 10 numbers
  • Write a program to calculate the factorial of a number
  • Write a program to multiply two 16-bit numbers result should be greater than 16 bit
  • Write a program to input 5 numbers and arrange them in descending order
  • Write a program to convert the string data it’s Two’s complement form
  • Write a program to read 8 bit data from Port B. Complement this data & send it back to Port A of
  • Write a program to move a block of data from one memory location to another
  • Write a program to find Gray code equivalent of a Binary number using Lookup Table
  • Microprocessor 8086 Video Play List
  • Download Emu8086 Simulator
  • Download Lab Report Cover Page
  • Download Theory for Lab Report
  • Linux Commands with Example
  • VI Editor Commands and Networking Commands in Linux
  • Shell Scripting Programs  in Linux with Examples
  • C C++ and Java Program in Linux and Lamp Server Installation

About Us

I hope you like this website. If you want to share your thoughts or give a suggestion, you can contact by contact form. I wish God the success of your future career.

Service Provide

  • Laptop Trouble-Shoot
  • Desktop Trouble-Shoot
  • Network Trouble-Shoot
  • Hard Disk Data Recovery
  • Mobile Data Recovery

Our Training

  • WordPress Website
  • Blog Website
  • Cisco & GNS3 Training
  • Hardware Training
  • Computer Network Training

Useful Links

Copyright © 2020-22 | GYANCS.IN | By BADARUZZAMAN |
  • About Us
  • Contact Us
  • Privacy Policy
  • Disclaimer
  • Terms and Conditions

We have detected an adblocker in your browser,
please consider supporting us by disabling your ad blocker.