Structure Query Language (SQL)

- It is database language designed for the

retrieval and management of data in relational
database management system (RDBMS)
- It is also used for database schema creation
and modification, database object access
control management.
- It is non procedure query language.
 Parts of SQL language:
1. Data Definition Language(DDL):
- It provide command for defining, deleting
and modifying relation and view schemas.
- It is also used to specify integrity constraints.
2. Data manipulation Language (DML)
-It is based on both the relational algebra and
the tuple relational calculus.
-It also include command to insert tuples into,
delete tuples from, and modify tuples in the
database.
 3. Embedded and Dynamic SQL:
- Embedded SQL define how SQL statements can be
embedded within general purpose programming
languages like C, C++, Java etc. Dynamic SQL allows
us to construct queries at run time.
4. Transaction Control:
- SQL includes commands for specifying the
beginning and ending of transactions.
5. client-server Execution and Remote Database
Access:
These commands control how a client application
program can connect to an SQL database server, or
access data from a database over a network.
 Consider the following tables:
Student{ sid, sname, level, age, sex}
Instructor{ Iid, iname, age, sex}
course{cid, cname, credit_hours}
Enrolled_by { sid, cid}
Taught_by {Iid, cid}
 Student Table
Sid Sname Level Age Sex
101 Harendra Undergraduate 22 Male
102 Ramesh Undergraduate 21 Male
103 Nirab Graduate 25 Male
104 Pratibha Undergraduate 20 Female
105 Samrita Graduate 24 Female
106 Aastha Undergraduate 22 Female
107 Rabindra Graduate 28 Male
108 Abin Graduate 26 Male
201 Bharat Postgraudate 30 Male
202 Sohan Postgraduate 32 Male
 Instructor table
Iid Iname Age Sex

301 Bhawani 35 Male

302 Suresh 34 Male

303 Pratigya 27 Female

304 Sweta 33 Female

305 Nameesh 28 Male

201 Bharat 30 Male

202 Sohan 32 male

 Course Table
Cid Cname Credit_hours

Csc-401 DBMS 3

Csc-402 Cognitive Science 3

Csc-403 Computer Science 3

Csc-404 SAD 2

Csc-405 TW 2

Csc-406 UML 1
Sid cid

101 Csc-401
101 Csc-402
101 Csc-403
102 Csc-402
102 Csc-404
103 Csc-404
103 Csc-405
104 Csc-401
104 Csc-402
104 Csc-403
104 Csc-405
105 Csc-403
105 Csc-405
 1. Select Clause:
- It is mandatory. It specifies list of column to be retrieve from
table.
Q) Find the name of female student.
SELECT sname FROM student WHERE sex=“Female”
Q) Find the id of all instructors in tought_by table.
SELECT Iid FROM tought_by
Q) Select DISTINCT Iid FROM tought_by
NOTE: The distinct specifies that duplicate rows are discarded.
Q) Find all course records
SELECT * FROM course
 NOTE: * operator retrieve all columns.
Q) Find record of all course taught by instructor
302
SELECT* FROM course NATURAL JOIN
taught_by WHERE Iid=302
Q) Find id,name,level and age of student by
increasing age by 1
SELECT sid, sname, level, age+1 FROM student
 FROM Clause:
- It always follows the select clause.
- When FROM list contains multiple tables,
commas separate the table names.
 WHERE Clause:
- It is optional and it always follows FROM clause.
- it filters rows from “FROM” clause table.
-Additional comparison operator are used in Where clause like:
> , <, >=, <=, <> etc.
- SQL also support comparison operators like
- It also support keyword like AND, OR, NOT
BETWEEN, IN, LIKE ,IS NULL etc.
Q) Find ID and name of female students in undergraduate level.
SELECT sid, sname FROM student where sex= ‘Female’ AND
level= ‘Graduate’
 Q) Find id and name of courses whose credit
hour is above three or below 2.
SELECT cid, cname FROM course where
credit_hour>3 OR credit_hours <2
Q) Find id,name, and level of student whose age
is between 20 and 25
SELECT sid, sname, level FROM student WHERE
age BETWEEN 20 AND 25.
 Q) Find id,name and age of student whose age is
less or equal to 20 and more or equal to 30.
SELECT sid,sname,level FROM student WHERE age
NOT BETWEEN 20 AND 30.
Q) Find id,name and level of student in all level
except ‘undergraduate’.
SELECT sid,sname,level FROM student WHERE
level<> ‘undergraduate’
 Pattern Matching:
- It is one of the important operations used
with string.
- SQL allows pattern matching with string by
“LIKE” operator.
- It uses wild characters like percent (%) and
underscore (_)
- Underscore matches single character and
percent matches zero or more characters.
Q) Find name, level and age of all students
whose name starts from “A”
SELECT sname, level, age FROM student
WHERE sname like ‘A%’
Q) Find Id and name of all students whose
name contains exactly five characters.
SELECT sid, sname FROM student WHERE
sname like ‘_ _ _ _ _’
Q) Find id and name of all instructors whose
name contains characters ‘a’ and ‘s’
SELECT Iid, Iname FROM instructor WHERE
Iname like %a%s
 Set operations:
- It is mainly used to combine the same type of data
from two or more tables.
Rules on set operations:
- The result sets of all queries must have the same
number of columns.
- In every result set the data type of each column
must match the data type of its corresponding column
in the first result set.
- The column names or aliases must be found out by
the first select statement.
The set operators union, intersect and except allows
us to combine two or more select statement.
 Union: It combines two or more result sets into a single
set, without duplicates. Any duplicate records are
automatically removed unless UNION ALL is used.

INTERSECT: It takes the data from both result sets which

are in common. The INTERSECT operator removes
duplicate rows from the final result set. The INTERSECT ALL
does not remove duplicate rows from the final result set.

EXCEPT: It takes the data from first result set, but not the
second (i.e no matching to reach other). The EXCEPT
operation automatically eliminate duplicates. If we want to
retain all duplicates, we must write EXCEPT ALL in place of
EXCEPT.
 Q)Find id and name of all females who are instructor
or student
SELECT Iid AS ID, Iname AS Name FROM instructor
WHERE sex=‘Female’ UNION SELECT sid, sname FROM
student WHERE sex=‘Female’
Q) Find id, name, and age of all persons who are
instructors as well as student
SELECT Iid AS ID, Iname AS name, age FROM instructor
INTERSECT SELECT sid, sname, age FROM student
Q) Find id, name, and age of all persons who are
instructors but not student
SELECT Iid AS ID, Iname AS Name, age FROM
instructor EXCEPT SELECT sid, sname, age FROM
student
 ORDER BY Clause:
- It is optional and it must be the last in the
SELECT statements.
- It is used for sorting for the results of a
query.
- It defines the ordering of rows based on
columns from the SELECT clause.
 Q) Find id, name and age of all students in
ascending order of age.
SELECT sid, sname,age FROM student order by age

Q) Find id, name and age of all instructors in the

order of sex and then in descending order of age.
SELECT Iid, Iname, age FROM instructor ORDER BY
sex, age DESC
Q) Find id and name of all undergraduate
students in ascending order of name.
SELECT sid, sname FROM student WHERE level=
‘Undergraduate’ ORDER BY sname
 Comparison Operators:
- Comparison operators that introduces a subquery
can be modified by the keywords ALL or ANY.
- SOME is an ISO standard equivalent for ANY.
- ANY and SOME must match at least one row in the
sub query.
- ALL must match all rows in the sub query.
- Using operator > ALL means greater than every
value.
E.g > ALL (1,2,3) means greater than 3.
E.g > ANY (1,2,3) means greater than 1.
Q) Find id,name, and age of all instructors whose
age is greater than age of all students.
SELECT Iid, Iname, age FROM instructor WHERE
age>ALL (SELECT age FROM student)
Q) Find id, name and level of all students whose
age is greater than at least one student.
SELECT sid, sname,level FROM student WHERE
age>SOME (SELECT age FROM instructor)
Q) Find id,name,level, and enrolled course for all
students whose age is greater than age of at lest
one instructor.
SELECT sid, sname,level,cid FROM student
NATURAL INNER JOIN enrolled_by WHERE
age>SOME (SELECT age FROM instructor)
 Join operations:
- The join operations allows us to combine
information from two or more relations.
- While using join operation we have to specify
join type and a join condition.
Types of joins:
1. Inner join: Only rows satisfying selection
criteria from both joined tables are selected.
2. Left outer Join: Rows satisfying selection
criteria from both joined tables are selected as
well as all remaining rows from left table are being
kept along with Nulls instead of actual right table
values.
 3. Right outer Join: Rows satisfying selection
criteria from both joined tables are selected as
well as all remaining rows from right table are
being kept along with Nulls instead of actual left
table values.
4. Full Outer join: Rows satisfying selection
criteria from both joined tables are selected as
well as all remaining rows both from left joined
table and right joined table are being kept along
with Nulls instead of value from other table.
Note: the use of join condition is mandatory for
outer joins ,but is optional for inner joins.
5. Natural Join: it joins the source tables on all columns
having the same name. Natural joins are always equi-
joins. We must be careful while using natural joins
because it matches unrelated columns from both
tables if they have same name. The join attributes
appear first, in the order in which they appear in the
left-hand-side relation. Next come all non-join
attributes of the left-hand –side relation, and finally all
non-join attributes of the right –hand-side relation.
Q) Find id and name of students who enrolled
in course csc-403
SELECT sid, sname, FROM Student NATURAL
INNER JOIN enrolled_by WHERE cid=‘csc-403’
Q) Find id,name,and level of all students who
enrolled in course ‘SAD’
SELECT sid,sname,level FROM student
NATURAL JOIN course WHERE cname=‘SAD’
 Q) Find id and name of instructors who do not
teach any course.
SELECT Iid,Iname FROM instructor NATURAL LEFT
OUTER JOIN taught_by WHERE cid IS NULL
Q) Find all course records that is not enrolled by
any student
SELECT cid, cname, credit_hours FROM
enrolled_by NATURAL RIGHT OUTER JOIN course
WHERE sid IS NULL.
 Aggregate Functions:
- It operate against a collection of values, but return a
single value.
- It is mainly used in summarizing the information
stored in tables.
The aggregate functions are:
1. AVG (Column)- Returns the average value of column
2. COUNT (column)- Return the number of rows
(without a NULL value) of a column
3. COUNT (*) – Return the number of selected rows
4. MAX (column)- Returns the highest value of column
 - MIN (column)- Returns the lowest value of a
column.
- SUM (column)- Returns the total sum of a column.

Q) Find the minimum and maximum age of students.

SELECT max (age), min (age) FROM student

Q) Find average age of all undergraduate students

SELECT AVG (age) FROM student WHERE level=
‘Undergraduate’
Q) Find total number of course.
SELECT count (*) FROM course
 Database Modification:
- The SQL Modification statements make changes
to database data in tables and columns.
- There are 3 modification statement:
1. INSERT statement: add rows to tables
2. UPDATE statement: modify columns in table
rows
3. DELETE statement- remove rows from tables.
 Q) Insert new instructor record into database
INSERT INTO instructor VALUES (308, ‘Yonjan’, ‘29’, ‘Male’)
OR,
INSERT INTO instrctor (iid, iname, sex, age) VALUES (308,
‘Yonjan’, ‘Male’, 29

Q) Insert record of all graduate level, female students into

instructor table.
INSERT INTO instructor (iid, iname, sex, age) (SELECT
sid,sname,sex,age FROM student WHERE level=‘graduate’
and sex= ‘Female’)
OR,
INSERT into instructor (SELECT sid,sname,age,sex FROM
student WHERE level= ‘Graduate’ and sex= ‘Female’
Q) Insert record of student who teaches course
csc-401 into instructor table.
INSERT INTO instructor (iid, iname,sex,age)
(SELECT sid,sname,sex,age FROM student
NATURAL JOIN course where cid=‘csc-401’)
 Q) Modify age of student having sid 105 to 27
UPDATE student SET age=27 WHERE sid=105

Q) Change name and age of instructor having id 301

to ‘Bhuwan’ and 30 respectively.
UPDATE instructor SET age=30, iname=‘Bhuwan’
WHERE iid=301

Q) Increase credit hour of courses by 2 whose credit

hour is more than 3 other wise increase credit hours
by 1.
UPDATAE course SET credit_hours=credit_hours+2
WHERE credit_hours>=3 UPDATE course SET
credit_hours=Credit_hours+1 WHERE Credit_hours<3
Q) DELETE record or courses whose credit hour is
2
DELETE FROM course WHERE credit_hour=2
 Data Definition Language (DDL)
- The most important DDL statements in SQL are:
1. CREATE statement- Used to crate a tables,
views or other database objects.
2. ALTER statement: Used to alter(changes)
database tables, views or other database objects
3. DROP statement: used to delete database
tables, views or other database objects.
 Domain (Data) types in SQL:
- The SQL standard supports a variety of built-in domain types
like:
1. Char : A fixed- length character string with user- specified
length.
2. Varchar: A variable- length character string with user-
specified maximum length.
3. Int : Represent a whole number.
4. Numeric : A fixed- point number with user specified
precision.
5. Float: Floating point number.
6. Date: Calendar date with year, month and day. It contains a
4 digit year. E.g 2009-02-14
7. Time: Clock time with hour, minute and second e.g 11:30:45
CREATE TABLE Student
(
sid interger not null ,
sname varchar (12),
level varchar (12),
age interger,
sex varchar (6),
Primary key (sid)
)
 ALTER TABLE:
- It allows us to modify a table like
a. adding new column in existing table.
b. Deleting some columns from an existing table
c. Modifying some columns of given table.
Q) Add new column ‘addresses’ to an exiting table student
ALTER TABLE Student
ADD (addresses Varchar (15));
Q) Remove an existing column ‘addresses’ from the table student
ALTER TABLE Student
DROP (addresses);
Q) Change the attribute sname to name of student table.
ALTER TABLE student CHANGE (sname name varchar
(30));

DROP TABLE:
- It allows us to remove an existing table from the
database.
Q) Delete the table named student
DROP Table (student);
 Views:
- It is a logical table which does not physically
store data like table but represent data stored in
tables in different formats.
- It doesn’t required disk space.
- The view is derived from other tables so when
the source table is updated it is reflected in view.
- It is used by DBA to enforce database security.
 Advantages of Views:
1. Database Security: View allows users to access
only those sections of database that directly
concerns them.
2. View provides data independence.
3. Easier querying
4. Shielding from change.
5. Views provide group of users to access the
data according to their criteria.
6. View allow the same data to be seen by
different users in different ways at the same time.
Q) Following view contains the id,name,level,age
and sex of those students whose age is greater
than 24.
CREATE VIEW student_view AS
SELECT sid, sname,level,age,sex FROM student
WHERE age>24;

To delete view:
Syntax: Drop View View_name
e.g Drop VIEW customer_view;
 Transaction Control:
1. Commit Command:
- It is used to save changes invoked by a transaction to the database.
E.g
DELETE FROM customers
WHERE age=25;
SQL> COMMIT;
2. ROLLBACK Command:
- This command is used to undo transactions that have not already been
saved to the database.
- It can undo transaction since the last COMMIT or ROLLBACK command
is issued.
Syntax:
ROLLBACK;