Structured
Query
Language
SQL
SQL (Structured Query Language) is a standard
language for accessing and manipulatng databases.
SQL commands are used to create, transform and
retrieve informaton from Relatonal Database
Management Systems and also used to create
interface between user and database.
By using SQL commands, one can search any data
in the database and perform other functons like,
create tables, add records, modify data, remove rows,
drop table etc.
SQL
Basic categories of SQL statements are:
• DDL
• DML
• DCL
• DQL
Relatonal Operators are:
<, <=, >, >=, =, !=
Logical operators are: AND, OR, NOT
SELECT Statement Basics
The SQL SELECT statement queries data from tables
in the database. The statement begins with the
SELECT keyword. The basic SELECT statement has
3 clauses:
• SELECT
• FROM
• WHERE
General format is:
select <col1>, <col2>,............,<col n> from <table
name> where <conditon>
Relation: Emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-80 800 20
7499 ALLEN SALESMAN 7698 20-Feb-81 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-81 1250 500 30
7566 JONES MANAGER 7839 2-Apr-81 2975 20
7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-81 2850 30
7782 CLARK MANAGER 7839 9-Jun-81 2450 10
7788 SCOTT ANALYST 7566 19-Apr-87 3000 20
7839 KING PRESIDENT 17-Nov-81 5000 10
7844 TURNER SALESMAN 7698 8-Sep-81 1500 0 30
7876 ADAMS CLERK 7788 23-May-87 1100 20
7900 JAMES CLERK 7698 3-Dec-81 950 30
7902 FORD ANALYST 7566 3-Dec-81 3000 20
7934 MILLER CLERK 7782 23-Jan-82 1300 10
emp;
SELECT
Clause
Imp:
1. Select * from table;
• String or group of
Select * from emp; characters in single
quotes
2. Select [ALL | • Commands not case
DISTINCT] <col list> sensitve but data is
from table;
case sensitve.
(default is ALL)
JOB CLERK SALESMAN
PRESIDENT MANAGER
select distnct job from ANALYST
SELECT Clause
emp where NOT (job =
3. Output based on ‘CLERK’ OR job =
conditon ‘ANALYST’);
ENAME JOB
Select <col list> from
SMITH CLERK JONES MANAGER
table where <cond> SCOTT ANALYST ADAMS CLERK
FORD ANALYST
select ename, job from
ENAME JOB
emp where deptno = SMITH CLERK SCOTT ANALYST
20; Or ADAMS CLERK JAMES CLERK FORD
ANALYST MILLER CLERK
select ename, job from
emp where job =
ENAME JOB
‘CLERK’ OR job = ALLEN SALESMAN WARD
‘ANALYST’; Or SALESMAN JONES MANAGER
MARTIN SALESMAN BLAKE
select ename, job from MANAGER CLARK MANAGER KING
PRESIDENT TURNER SALESMAN
SELECT Clause
4. BETWEEN clause
Select ename, hiredate from emp where sal BETWEEN 2000
3000 (Note: BETWEEN includes both the lower and upper range)
Select ename, hiredate from emp where sal NOT BETWEEN
AND 3000
ENAME HIREDATE
WARD 22-FEB-81
ENAME HIREDATE
MARTIN 28-SEP-81
JONES 02-APR-81
KING 17-NOV-81
BLAKE 01-MAY-81 TURNER 08-SEP-81
CLARK 09-JUN-81 ADAMS 23-MAY-87
SCOTT 19-APR-87 JAMES 03-DEC-81
FORD 03-DEC-81 MILLER 23-JAN-82
SMITH 17-DEC-80
ALLEN 20-FEB-81
SELECT
Clause or
select * from emp
where job NOT IN
5. Conditon to select
(‘CLERK’, ‘ANALYST’,
from a list – IN clause
‘MANAGER’);
select ename from emp ENAME
where job IN (‘CLERK’, SMITH
‘ANALYST’, JONES
‘MANAGER’); BLAKE
SCOTT
ADAMS FORD
JAMES
CLARK
MILLER
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7839 KING PRESIDENT 17-NOV-81 5
TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SELECT Clause
where comm is NULL;
6. NULL values select * from emp
where comm is NOT
select * from emp
NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20
MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOT
7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7876 ADAMS CLERK 778
1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3
MILLER CLERK 7782 23-JAN-82 1300 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SELECT Clause
deptno desc, ename;
7. Sortng Output –
Order by clause (Note: default is ASC)
ENAME HIREDATE
(ASC/DESC) Select
ADAMS 23-MAY-87
ename, hiredate from
SCOTT 19-APR-87
emp where deptno = 20
FORD 03-DEC-81
order by hiredate desc;
JONES 02-APR-81
SMITH 17-DEC-80
DEPTNO ENAME
30 ALLEN 30 BLAKE 30 JAMES 30
MARTIN 30 TURNER 30 WARD 20
Select deptno, ename ADAMS 20 FORD 20 JONES 20
from emp order by SCOTT 20 SMITH 10 CLARK 10 KING
10 MILLER
SELECT Clause
calculatons Select
ename, sal+comm from
8. Display of simple emp where comm is not
NULL;
Select sum(sal) from
emp where deptno =
20;
9. Date functon select SUM(SAL) 10875
sysdate from dual;
ENAME SAL+COMM
ALLEN 1900 WARD 1750
MARTIN 2650 TURNER 1500 Select avg(sal) from
emp where deptno =
20;
AVG(SAL) 2175
SYSDATE 29-JUL-14 SELECT
SELECT Clause
Clause
10. Statstcal functon
10. Statstcal functon
• • • • • • avg sum min Select min(sal),
max count count(*) max(sal) from emp
where deptno = 20;
MIN(SAL) MAX(SAL) 800
3000
Select count(*) from
emp;
Select count(DISTINCT COUNT(DISTINCTJOB) 5
job ) from emp;
COUNT(*) 14
SELECT Clause
10. Group by clause
(Returns a single value for a set of rows)
which statstcal groups
Select deptno, count(*),
containing functon are
max(sal) from emp
GROUP BY deptno; formed GROUP and can
the BY be used.
Note: In clause atribute a DEPTNO COUNT(*)
MAX(SAL) 30 6 2850 20 5
select only on statement
3000 10 3 5000
SELECT Clause
10. Having clause
(Places conditon only on the feld on which group is made or on statstcal fun
AVG(SAL) PRESIDENT 1
select job, count(*), 5000 ANALYST 2 3000
avg(sal) from emp SELECT
group by job having Clause
job = ‘CLERK’;
10. Putng text in
query
select job, count(*), Select ename NAME,
sal*12 “ANNUAL
avg(sal) from emp SALARY” from emp;
group by job having
Note: Only place to use
count(*)<3; double quotes
JOB COUNT(*)
AVG(SAL) CLERK 4
1037.5 11. Placing text in
output
select ename, ‘works
in’, deptno from emp
where deptno = 10;
NAME ANNUAL SALARY
JOB COUNT(*)
SMITH 9600 SCOTT 36000
ALLEN 19200 KING 60000
WARD 15000 TURNER 18000
JONES 35700 ADAMS 13200
MARTIN 15000
BLAKE 34200
CLARK 29400
JAMES 11400
FORD 36000
MILLER 15600
ENAME 'WORKSIN' DEPTNO
CLARK works in 10 KING works in 10 MILLER
SELECT Clause
12. Joining string using || operator
select ename || ' works in ' || dname from emp E, dept D wher
E.deptno = D.deptno and E.deptno = 20
ENAME||'WORKSIN'||DNAME SMITH works in RESEARCH JONES works in RESE
SCOTT works in RESEARCH ADAMS works in RESEARCH FORD works in RESEARC
Order of combining clauses
SELECT column list FROM <table name> WHER
<predicate> GROUP BY <column name(s)> HAV
<search conditon> order by column_name;
SELECT Clause
10. Patern matching
% matches any substring (0 onwards) _ matches exactly one
character (no zero even)
select * from emp select * from emp where ename like ‘A%’; w
ename like ‘_ _ E%’;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
Extracting data from two tables
Dept table is:
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
EQUI JOIN
Join is Cartesian 1. select ename, dname
Product of two tables from emp, dept where
Equi join returns data emp.deptno =
from two tables using dept.deptno
OR select ename,
equality operator dname from emp E,
MARTIN SALES
dept D where E.deptno
BLAKE SALES
= D.deptno
CLARK ACCOUNTING
SCOTT RESEARCH
2. select ename, dname KING ACCOUNTING
from emp E, dept D TURNER SALES
ADAMS RESEARCH
where E.deptno =
JAMES SALES
D.deptno and E.deptno FORD RESEARCH
= 20
ENAME DNAME
SMITH RESEARCH ENAME DNAME
ALLEN SALES SMITH RESEARCH
WARD SALES JONES RESEARCH
JONES RESEARCH SCOTT RESEARCH
MILLER ACCOUNTING
ADAMS RESEARCH
FORD RESEARCH
Non-Equi JOIN
we use only relatonal
The non-equi join is a
type of join in which, operators except
= 20
equal operator. These ENAME DNAME
SMITH ACCOUNTING JONES
include >, <, >=, >= ACCOUNTING
and != SCOTT ACCOUNTING
ADAMS ACCOUNTING
Example FORD ACCOUNTING SMITH SALES
JONES SALES
SCOTT SALES
select ename, dname ADAMS SALES
from emp E, dept D FORD SALES SMITH OPERATIONS
where E.deptno = JONES OPERATIONS SCOTT
OPERATIONS
D.deptno and E.deptno
ADAMS OPERATIONS FORD OPERATIONS
E2.ename from emp
Self Join E1, emp E2 where
E1.mgr = E2.empno
SMITH FORD
Self join is joining a ALLEN BLAKE
table with itself WARD BLAKE
JONES KING
select E1.ename,
ENAME ENAME
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
Creating Table
Syntax is:
CREATE TABLE <table name> (<column name>
,<data type> [<size>] [<column constraint>],
........................., [<table constraint>] );
Constraints
Constraints restricts the data entry to a column.
Diferent types are:
1. Not Null 2. Unique 3. Check 4. Primary Key 5. Foreign K
Default
Constraints can be applied on column or table level.
NOT NULL constraint
Can be applied at column level only. Values in this feld can no
NULL / lef blank.
e.g. create table STUDENT
(admNo number(4) NOT NULL, name varchar2(50));
UNIQUE constraint
Can be applied at table or column level. If applied for a colum
values have to be unique for the column.
create table STUDENT (admNo number(4) NOT NULL UNI
name varchar2(50)) UNIQUE;
Or
create table STUDENT (admNo number(4) NOT NULL, nam
varchar2(50) UNIQUE(admNo, name));
Primary Key constraint
Can be applied at column or table level. A column specifed as
primary key contain unique and no null values.
create table STUDENT (admNo number(4) PRIMARY KEY,
varchar2(50));
Or
create table STUDENT (admNo number(4), name varchar2
PRIMARY KEY(admNo) );
Foreign Key constraint
Can be applied at column or table level. Values entered in the
must be a valid value in the primary key feld of the other spe
table
create table STUDENT (admNo number(4) primary key, na
varchar2(50), subjectCode char(2) REFERENCES
SUBJECT(subCode));
ORcreate table STUDENT
(admNo number(4) primary key, name varchar2(50),
subjectCode char(2), FOREIGN KEY(subjectCode)
REFERENCES SUBJECT(subCode));
Check constraint
Can be applied at column or table level. Values entered in the feld, must b
value in the primary key feld of the other specifed table
create table STUDENT (admNo number(4) primary key, name varchar2
age number(2) CHECK(age>=14 and age<=18));
ORcreate table STUDENT
(admNo number(4) primary key, name varchar2(50), age number(2)
CHECK(age BETWEEN 14 AND 18));
ORcreate table STUDENT
(admNo number(4) primary key, name varchar2(50), age number(2),
check(age>=14 and age<=18));
DEFAULT value
Specifes default value for a feld
create table STUDENT (admNo number(4) primary key, na
varchar2(50), age number(2) DEFAULT 15, subjectcode ch
DEFAULT ‘01’);
Creating Table
create table SUBJECT (subcode char(2) primary
subname varchar2(50));
create table STREAM (strCode number(1) prima
key, strName varchar(15));
Creating Table
create table STUDENT (admNo number(4) primary key, sn
varchar2(50) NOT NULL, fname varchar2(50), mname
varchar2(50), DOB date, Xmarks number(5,2) check(Xma
75) , Stream number(1) DEFAULT 1, sub1 varchar2(2)
DEFAULT '01' REFERENCES SUBJECT(subcode), sub2
varchar2(2) DEFAULT '02' REFERENCES SUBJECT(sub
sub3 varchar2(2) DEFAULT '03' REFERENCES
SUBJECT(subcode), sub4 varchar2(2) DEFAULT '04'
REFERENCES SUBJECT(subcode), sub5 varchar2(2)
DEFAULT '06’ REFERENCES SUBJECT(subcode) FOR
KEY(Stream) REFERENCES Stream(strCode) );
Inserting data into a table
Syntax is:
INSERT INTO <table name> (column list) //optonal if values f
columns to be entered VALUES(data values) //if char or date
with in single quote
Example:
1. Insert into SUBJECT
values(‘01’, ‘ENGLISH’);
2. Insert into STUDENT
(admNo, sname, fname, mname, DOB, Xmarks) values(3456
Kumar’, ‘ABC’, ‘XYZ’, ‘20-Sep-98’, 92);
Deleting Record
Syntax is:
DELETE FROM <tablename> WHERE <conditon>
Example:
delete from Student where stream = 2; //delete all records
corresponding to stream 2
delete from Student; //command will delete all records from
table
Updating/Modifying Record
Syntax is:
UPDATE <tablename> SET <column1> = <expression1>, .
WHERE<conditon>;
Example:
update emp set sal = sal + 1000 where deptno = 20;
Altering/Modifying Table Structure
Adding Column Syntax is:
ALTER TABLE <tablename> ADD/MODIFY/DROP (<colum
name> [<data type> <size>], <column name> [<data type>
<size>],.........);
Example
Alter table emp ADD (city varchar2(25));
Alter table emp MODIFY (city varchar2(35));
Altering/Modifying Table Structure
Dropping a column Syntax is:
ALTER TABLE <table name> DROP COLUMN <column na
Example:
Alter table emp DROP COLUMN city;
Restrictons on Alter table:
• Table name can’t change
• Column name can’t change
• Column size can’t decrease if record exists
Destroying Table
Conditon is table must be empty.
Syntax is:
DROP TABLE <table name>
Example:
DROP TABLE stream
