Database Management System

Unit 3
Structured Query Language
(SQL)
SQL is used to interface with a data base which is stored in the memory with the help
of database management system. We can fetch the data, modify that data, retrieved
that data, manipulate the data & update using SQL, statement SQL is easy to learn.
A query is statement requesting the retrieval of information. The portion of DML that
involves information retrieval is called a Query Language.
Fetch User DBMS Database [SELECT ......... Memory
FROM .............
WHERE………….] Data Retrieve
Design the Database :-A database most often contains one or more tables. Each
table is uniquely identified by a name (e.g. Customers or Order). Table contains
records (rows) with data. Table column contains the column name, data types, & any
other attributes for the column. Table rows contain the records or data for the
columns. E.g. Student
Roll Name College Percentage
1001 Anil Baburao Chavan CMCS College 78.50
2001 Dinesh Eknath Fadatare KTHM College 75.25
3001 Gayatri Hiraman Ingle RYK College 65.75
Relation Student (Roll, Name, College, Percentage)
 Queries in DBMS:- The language to extract data from a relational database is
called structured Query Language (SQL). SQL belongs to Forth Generation
Language (4GL). It has 3 main tasks i.e. data definition, data manipulation & data
control.
i) Data Definition Language (DDL):- To define the conceptual scheme & how to
implement it in the physical devices. DBMS have DDL compiler. The result of
compilation of DDL statement is a set of tables which are stored in a specialized
file i.e. data dictionary (data directory), which contains meta-data (data about
data) DDL allows to create database, tables & indexes, & allows to change. DDL
has commands like create table, drop table, alter table, create index
ii) Data Manipulation Language (DML):- It mostly used to perform operations
like- select one or more columns, insert record into table, delete a record from
table, updation of a record.
iii) Data Control Language (DCL):- Used to control access to data in the
database. It is subset of SQL DCL statements are used to control access
permissions on the tables, indexes, views etc of DBMS. There are commands
like DCL- CALL, RETURN, SET, SETPATH, SIGNAL, VALUES etc.
iv) Transaction Control Language (TCL):- Transaction is a series of action (e.g.
bank deposite, withdrawl etc.) reading & writing etc. TCL is a computer
language & subset of SQL, used to control transactional processing in a
database. It includes command like COMMIT, ROLLBACK, SAVEPOINT etc.
Creating Table:- The Create Table statement is used to create a new table.
syntax:- CREATE TABLE table-name
( Column-1 datatype [NOT NULL],
Column-2 datatype,
:
Column-n datatype [NOT NULL] );
e.g. Create a table for student database which contain Roll, Name, College & their
percentage. Query
Create table student ( Roll varchar(3)
PRIMARY KEY,
Name varchar(20) NOT NULL ,
College varchar(30) NOT NULL,
Percentage varchar(10) NOT NULL );
All SQL statement should end with a semicolon (;), make sure you separate each
column definition with a comma. The table & column names must start with a letter
& can be followed by letter, numbers, or underscores ( _ ) not to exceed total of 30
characters in length. If NOT NULL is not specified, the column can have NULL values.
 SQL Data Types
The SQL standard supports many data types, which includes the following :
i. char(n) : Fixed length string with user-defined length n.
ii. varchar(n) : Variable-length string with user-specified length n.
iii. int : Integer type data. INTEGER (Precision 10), SMALLINT(Precision 5)
BIGINT(Precision 19)
iv. numeric(p,s) : A fixed point number with user specified precision. The
numbers consists of p digits(plus a sign) and s of the p digits are to the right of
the decimal points. E.g. numeric(8,2) decimal(5,2) are 123.45
v. real : Floating point numbers. Mantissa precision 7
vi. float(n) : Floating point numbers with precision n. Mantissa precision 16
vii. date : Calendar date, yyyy-mm-dd e.g. 2020-11-26
viii. time : time of data, hr:mm:sec e.g. 12:45:00
ix. timestamp : Combination of date and time. e.g. 2020-11-26 12:45:00.45
x. binary(n) : Binary String, fixed length n
xi. boolean : True / False values
xii. number : This data type represents a decimal number. E.g. Rollno number(3);
Rollno number(3,2); i.e. 2 digits present after decimal points.
xiii. currency : for Money
Inserting into a table:- The insert statement is used to insert a data or adds the
new data into the previously existing data into the table.
e.g. Insert the value into student table.
Query insert into student (Roll, Name, College, Percentage)
values (6, ‘Rahul’, ‘CMCS’, 65);
OUTPUT Student
Roll Name College Percentage
1 - - -
2 - - -
String should be enclosed in single
3 - - - quotes & numbers should not.
4 - - -
5 - - -
6 Rahul CMCS 65

Altering Command:- Use ALTER command for following purposes :

i) To add a new column in the database. ii) To add an integrity constraints.
iii) To redefine a column (datatype, size, default value) iv) To modify storage
characteristics or other parameters with new name.
v) To enable, disable, or drop an integrity constraint or trigger.
We can MODIFY clause to change any of the following part of column definitions:
DATA TYPE, SIZE, VALUE, NOT NULL COLUMN CONSTRAINT
Column to be modified must be empty to change the data-type.
e.g. Write a query for add column into relation student named Address data type
VARCHAR with NOT NULL integrity constraints & a column phone of data type
NUMBER. Query ALTER TABLE STUDENT
ADD (
Address VARCHAR(50) NOT NULL,
Phone NUMBER(8) );

e.g. Write the query for increases the size of the phone number column of 10 digits.
Query ALTER TABLE STUDENT
MODIFY (Phone NUMBER(10));
i.e. The MODIFY clause require only to specify the column name & the modified part
of the definition, rather than the entire column definition.

e.g. Write a query to modify the percentage column of the student table so that
it has a default value of 0.
Query ALTER TABLE STUDENT
MODIFY (Percentage default 0);
e.g. Rename the table name student to student-info.
Query ALTER TABLE STUDENT RENAME TO Student-info;
 CONSTRAINT Clause:- We can use constraint clause with the either CREATE
TABLE or ALTER TABLE command. There are following types of constraints :
1. NOT NULL : It enforces a column to NOT accept NULL values.
2. UNIQUE: Uniquely identifies record in a database table.
e.g. CREATE TABLE student
( Rollno int NOT NULL,
Name varchar(20) UNIQUE );
3. CHECK : Used to limit the value range that can be placed in a column.
e.g. ( Rollno int NOT NULL,
Name varchar(20) UNIQUE
CHECK (Rollno > 0) );
4. DEFAULT : is used to insert a default value into a column.
e.g. ( Rollno int NOT NULL,
Name varchar(20) UNIQUE
City varchar(25) DEFAULT ‘Nashik’ );
 5. PRIMARY KEY : Unique identifier for record.
CREATE TABLE student
( Rollno int PRIMARY KEY,
Name varchar(30) NOT NULL,
Age int );

or ALTER TABLE student

ADD PRIMARY KEY(Rollno);

6. FOREIGN KEY : Reference key for other table (1-M or M-1 relationship)
e.g. Person(pid, lname, fname, address, city)
Orders (oid, orderno, pid)
CREATE TABLE Orders
( oid int NOT NULL,
orderno int NOT NULL, MySQL
pid int,
PRIMARY KEY(oid),
FOREIGN KEY(pid) REFERENCES Person(pid)
);

CREATE TABLE Orders

( oid int NOT NULL PRIMARY KEY, SQLServer
orderno int NOT NULL,
pid int FOREIGN KEY REFERENCES Person(pid)
);

Foreign Key on ALTER TABLE

ALTER TABLE Orders

ADD FOREIGN KEY(pid)
REFERENCES Person(pid)
 Basic structure of SQL:- There are major 3 types of this statements within queries.
1) SELECT - it corresponds to project operation (ϖ) of the relational algebra.
It retrieves data from a specified table, or multiple related tables, in a database.
i.e. attributes or column names
2) FROM - it corresponds to the cartesian product (X) of the relational algebra. From
clause which indicates the source table or tables from which data is to be retrieved.
r1, r2, ...... rm.
3) WHERE - it corresponds to the selection predicate (σ) of the relational algebra. It
can be used to compare the conditions Query.
* wildcard indicator.
SELECT A1, A1, A2, ...... An
FROM R1, R2,…,Rn
WHERE Condition;
SELECT Clause:-
e.g. Write a query to select the names of all students from student databases.
Query SELECT NAME
FROM Student;
SQL statements are not case sensitive. i.e. we can use small or capital or both in
combination, semicolon to separate each SQL statement. ALL as default or
DISTINCT for unique record. DISTINCT will eliminate the duplicate record for the
columns you specified after the SELECT statement.
e.g. Write a query to select names of all colleges from student database. Only show
which colleges are participated in the database. o/p
Query SELECT DISTINCT College College
FROM Student; C.M.C.S.
K.T.H.M.
e.g. Select the names of all colleges from student database. R.Y.K.
SELECT ALL College
FROM Student;
Mathematical operators - + - * / % modulo ^ Raised to
e.g. Write a query to increase the 3 percent in the percentage for every student &
show the name, original percentage as well as new percentage.
SELECT Name, Percentage, Percentage+3
FROM Student;
FROM Clause:- Itself defines a cartesian product (X) of a relation. It also uses 4
join natural & outer, inner, cartesian.
e.g. Student Table
Roll Name
1 Anil
2 Geeta
Project Table
Roll PName
1 Online Game
2 Railway Reservation
e.g. Write the SQL for finding all student Name, Pname with their rollno
SELECT Student.Roll, Name, Project.Roll, Pname
FROM Student, Project;
Output :
Roll Name Roll Pname
1 Anil 1 Online Game
1 Anil 2 Railway Reservation
2 Geeta 1 Online Game
2 Geeta 2 Railway Reservation
WHERE Clause:- Corresponds to selection operation (σ) of relational algebra.
Conditional selection used in WHERE clause.
Condition Symbols = > < >= <= <> or !=
LIKE To check some condition String comparison operator.
e.g. Write SQL Query to find all student with their respective roll number & project.
Query SELECT Student.Roll, Name, Project.Roll, Pname
FROM Student, Project
WHERE Student.Roll=Project.Roll;
Keep in mind that SQL statement will match any string names in the where clause
that strings must be in a single quote.
e.g. Write a query to display the Name & Project for everyone that’s not from RYK.
Query SELECT Name, Pname
FROM Student, Project
Where Student.Roll=Project.Roll
AND College <> ‘RYK’;
e.g. Write a query to displayall information of those student that are from at least
first class.
Query SELECT *
FROM Student
WHERE Percentage >= 60;
e.g. Write a query to display the all information of these students that
are from C.M.C.S. college.
Query SELECT *
FROM Student
WHERE College = ‘CMCS’;

OUTPUT : Roll Name College Percentage

- - - -

In where clause logical connectives like AND, OR & NOT are used.
AND & OR used for check two conditions.

e.g. Write a query to display the name of those students that are from CMCS college
& getting MINIMUM first class.
Query SELECT *
FROM Student
WHERE College = ‘CMCS’
AND Percentage >=60;
OUTPUT : Roll Name College Percentage
- - - -
The RENAME operation:- For both relation & attributes. This is done with as
keyword used in the FROM clause (for table-name to be change) or in the SELECT
clause (for attribute to be change).

Syntax Old Name as New Name

e.g. Write a query to change the name as Sname from student database & display
Sname with their college name.
Query SELECT Name as Sname, College
FROM Student;
OUTPUT :
Sname College
------ -------
e.g. Write a query to find the name of student, college & project name of only those
students who are getting distinction (i.e. above percent 69)
Query SELECT Name as S.name, S.College, P.Pname
FROM Student as S, Project as P
WHERE S.Roll = P.Roll
AND S.Percentage >= 70;
OUTPUT : Name College Pname
--------- ------- ---------
 String operation:- The LIKE operator is used for string comparison. The pattern,
which will match by LIKE operator, is case sensitive. Since upper case letters
cannot match with lower case letters. We use following types of characters,
1) ‘%Jan%’ 10 Jan 2008, 26 Jan 1950, 20 January etc.
2) ‘A%o%’ Amol, Alok, Ashutosh
3) ‘Al%’ Alok, Alka, Alpana etc. string begin with Al
4) ‘%tion’ mention, assumption, creation etc.
5) ‘----‘ Amol, Yash, Sonu etc. exact 4 character
6) ‘--o-’ Alok, Amol etc. exact 4 characters & third character o.
7) ‘ab\%cd’ ab%cd \ used for match
8) ‘ab\\cd’ ab\cd \ used for match
e.g. Write a query to find the name of student whose name are starting with letter A.
Query SELECT Name
FROM Student
WHERE Name LIKE ‘A%’; OUTPUT : Name Akash Ajinkya
e.g. Write a query to find the name of student whose names second position
are occupy with a.
Query SELECT Name
FROM Student
WHERE Name LIKE ‘-a%’ ; OUTPUT : Name Sagar Rahul
Aggregate Function:- Return a single value
Function Purpose
MIN Return the smallest value in a given column
MAX Return the largest value in a given column
SUM Return the sum of numeric values in a column
AVG Return the average values of a values in a column
COUNT Return the total number of values in a column
COUNT (*) Return the number of rows in a table
e.g. Write a query to find out the average salary of all teachers.
Query SELECT AVG (Salary)
FROM Teacher;
e.g. Write a query to find out the average salary of those teachers whose name
starts with character A
Query SELECT AVG (Salary)
FROM Teacher
WHERE Tname = ‘A%’;
e.g. Write a query to select first ranker student name with his Department name &
teacher belongs to that subject.
Query SELECT Name, Department, MAX (Marks), Tname
FROM Student, teacher
WHERE Student.department = Teacher.department;
Order by Clause:- The ORDER BY Clause is used to identify which columns are used
to sort the resulting data & in which order they should be sorted (options are
ascending or descending) based on specified order. If order is not specified then
default is Ascending (ASC), For Descending order (DESC).
If you would like to order based on multiple columns, you must separate the columns
with commas.
e.g. Write a query to find all over student information who get first class by
alphabetical order (ascending order) of college.
Query SELECT *
FROM Student
WHERE Percentage >= 60
ORDER BY College ASC;
o/p Roll Name College Percentage
5 Amol CMCS 91
8 Akash KTHM 85
e.g. Write a query to find all over student information who get first class by
alphabetical order (Descending order) of college.
SELECT *
FROM Student
WHERE Percentage >= 60
ORDER BY College DESC;
Scalar Functions:- (in MS-Access)
Function Description
UCASE (c) Convert a field to upper case.
LCASE (c) Convert a field to lower case.
MID(c, Start[, end]) Extracts characters from a text field.
LEN(c) Returns the length of a text field.
INSTR (C, char) Returns the numeric position of a named character within
text field.
LEFT(C, number of characters) Returns the left part of a text field required.
RIGHT(C, number of characters) Returns the right part of a text field required.
ROUND(C,decimals) Rounds a numeric field to the number of decimals specified.
MOD(x, y) Returns the remainder of a division operation.
NOW() Returns the current system date.
DATEDIFF (d, date1, date2) Used to perform date calculations.
Set Operation:- It is collection of group of same type of data. All the element are
enclosed within curly brackets denote it. ({ ... })
e.g. A : It is set of all days
A = {Sunday, Monday,........,Saturday}
In the SQL set provides 3 types of operation
1) Union 2) Intersection 3) Except
1) SELECT Name 2) SELECT Name
FROM Student FROM Student
WHERE College = ‘CMCS’ OR WHERE College = ‘RYK’ OR Percentage >
60; Percentage > 60;
Name Name
A - C -
B - E -
C - F -
D -

UNION Operation takes all the result of the corresponding tables. It automatically
eliminates duplicate values. It acts as OR operation. If we want to duplicate values
in the result table then we use UNION ALL instead of UNION.
INTERSECT Operation:- Used for finding out the common values from two different
tables. It acts as AND operator. If we want the duplicate value in the table then
we use keyword INTERSECT ALL.
EXCEPT Operation:- Is found out the difference (-) between two relations. It acts
as minus operator from relational algebra.
e.g. Write a query to select the all student information are from CMCS college & RYK
college or student who got more than 60 percentage.
Query ( SELECT Name
FROM Student
WHERE College = ‘CMCS’ OR Percentage > 60 )
UNION
( SELECT Name
FROM Student
WHERE College = ‘RYK’ OR Percentage > 60 );
o/p A B C D E F By UNION ALL o/p A B C C D E F

e.g. Write a query to select the student information who are from CMCS college or
RYK college or student who got more than 60 percentage.
Query ( SELECT Name
FROM Student
WHERE College = ‘CMCS’ OR Percentage > 60 )
INTERSECT
( SELECT Name
FROM Student
WHERE College = ‘RYK’ OR Percentage > 60 );
o/p C
Group By:- Aggregate functions (like SUM) often need an added GROUP BY
functionality. Without the GROUP BY function it is impossible to find the sum for each
individual group of column values.
General syntax of ( SELECT Name
FROM Student
WHERE College = ‘CMCS’ OR Percentage > 60 )
It will gather all of rows together that contains data in specified columns.
e.g. Select the Department name & sum of marks according to each department.
Query SELECT Dept, SUM (Marks)
FROM Student
GROUP BY Dept;
Having Clause :- is added to SQL because the WHERE keyword could not be used
against aggregate functions (like SUM), & without HAVING it would be impossible to
test for result conditions.
General syntax for HAVING
SELECT Column, SUM(Column)
FROM table
GROUP BY Column
HAVING SUM(Column) Condition value;
i.e. the HAVING clause are for specifying conditions on the rows for each group. i.e.
which rows should be selected will be based on the conditions you specify. HAVING
clause should follow the GROUP BY clause if you are going to use it.
 Data Manipulation using SQL :-
SQL-DML includes commands to insert tuples into database, to delete from database
and to modify tuples in the database.
Three types of Commands : INSERT, DELETE and UPDATE
1. INSERT : INSERT INTO tablename
VALUES(A1, A2, A3….,An);
2. DELETE : syntax DELETE FROM tablename
[WHERE Condition]; WHERE is optional.
e.g. Write a query to delete the whole database of student.
DELETE FROM Student;
e.g. Write a query to delete database of students who are from Botany Dept.
DELETE FROM Student
WHERE Dept = ‘Botany’;
3. UPDATE : change value from tuple without changing other values.
syntax UPDATE table-name
SET column-name = expression [, column-name = expression]
[WHERE Condition];
e.g. Update all the student marks with increment of 5 marks.
UPDATE Student
SET Marks = Marks + 5;
Nested Queries (Query within Query):- Sometimes inner query is referred as
nested sub-query. A sub-query is a SELECT-FROM-WHERE. The nested queries can
be written for following:
1) Set membership 2) Set Comparison 3) Set Cardinality
i) Set Membership:- Set is a collection of entities find out by a select clause
according to condition. Set membership is the operation, which allows testing the
tuples for membership into the relation.
There are two keywords used for set membership i.e. IN, NOT IN.
The IN is the connective test for set membership whereas NOT IN is the connective
test for absence of set membership. IN is similar to intersection & NOT IN is
similar to difference operation.
ii) SET Comparison:- Using keyword SOME
SQL allows < SOME, <= SOME, >= SOME, = SOME, <>SOME
here =SOME is same as IN <> SOME is same as NOT IN