How to Write an Oracle Query in SQL
What is constraint?
Constraints are set of rules that are applied to a Table.
- NOT NULL: – Not Null constrain states that a field/attribute/column can’t be left blank
- PRIMARY KEY: – Primary Key constraint states that a field/attribute column can’t contain duplicate value and it can’t be left blank.
- UNIQUE KEY: – Unique key constraint states that a field/attribute/column can’t contain duplicate value.
- DEFAULT KEY: – Constraint states that a column/filed/attribute may be given a default value when no values are passed for that column.
- CHECK: – Check constraints states that field/column/attribute can be check for a particular condition before entering it into the table. Exp:-SAL>10000.
- 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.
- NOT NULL: – इसमें Blank Field नहीं छोड़ सकते ।
- PRIMARY KEY:-इसमें डुप्लीकेट रिकॉर्ड नही भर सकते और न ही इसे Blank छोड़ सकते एक टेबल में सिर्फ एक ही Primary Key होती है ।
- UNIQUE KEY:-इसमें भी डुप्लीकेट रिकॉर्ड नहीं भर सकते पर इसमें Blank record छोड़ सकते हैं ।
- DEFAULT KEY:-अगर आपने किसी Field में Default Value set कर रखी है और उस Field में कुछ भी Entry नहीं कर रहे हैं तो Default Value उठा लेगा ।
- CHECK: – Table में कुछ डालने से पहले कुछ चेक करना चाहते हैं जैसे SAL>1000.
- 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’;
