Sankalp for DBMS SQL
LECTURE 4
Structure Query Language
SQL is a standard language for storing, manipulating and retrieving
data in databases
SQL commands are case insensitive.
SQL is non procedural query language.
SQL statements are divided into three category
DDL(Data Definition language)
Create, Alter, drop, truncate
DML(Data Manipulation language)
Select, Insert, delete, update
DCL(Data Control language)
Commit, Rollback, Grant, Revoke,savepoint.
1/8/2021
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
Create table student ( Rollno varchar(20), Name varchar(10), Dob
date, Phone number(10));
Rollno Name Dob Phone
1/8/2021
Create table student ( Rollno varchar(20) primary key, Name
varchar(10), Dob date, Phone number(10));
Create table student ( Enroll int(3), Rollno varchar(20) primary key,
Name varchar(10), Dob date, Phone number(10), primary key(enroll,
Rollno));
1/8/2021
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP column_name;
Alter table student add marks number(3);
Alter table student drop phone;
Alter table student modify marks int(3);
Alter table student modify rollno varchar(3) primary key;
Restrictions on Alter:
We can’t decrease the size of attribute if data present.
We can’t add constraint if data available in attribute not satisfy the
condition of constraint.
1/8/2021
DROP TABLE table_name;
drop table student;
TRUNCATE TABLE table_name;
Truncate table student;
DDL command are auto committed.
1/8/2021
Data Manipulation Language
DML commands are not auto committed.
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Insert into student values(‘CS101’, ‘RAJ’, 27-05-1988, 9999999999)
INSERT INTO table_name (column1, column2, column3, ...) VALUES
(value1, value2, value3, ...);
insert into student(rollno, name) values (‘CS102’, ‘RAM’);
1/8/2021
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE
condition;
UPDATE Customers SET ContactName = ‘rahul', City= ‘indore‘ WHERE
CustomerID = 1
Update student set phone = 9696969696;
Update student set phone = 9696969696 where rollno=101;
1/8/2021
DELETE FROM table_name;
DELETE FROM Customers;
DELETE FROM table_name WHERE condition;
DELETE FROM student WHERE rollno=101;
We can also write delete * from student;
1/8/2021
SELECT * FROM table_name;
SELECT * FROM student;
SELECT * FROM student WHERE rollno=101;
SELECT name FROM student WHERE rollno=101;
SELECT name , Phoneno FROM student;
1/8/2021
Employee( Ename , Empno ,job ,salary ,deptno)
WAQ to find name of employees of department number 10.
WAQ to find name of all employees of department number 10 whose
salary is greater than 2000.
WAQ to find name of employees of department number 10 or 20.
1/8/2021
Employee( Ename , Empno ,job ,salary ,deptno)
WAQ to find name of employees who work in either department
number 10 or 20 or 30.
WAQ to find name of all employees whose salary is between 10000
and 20000.
WAQ to find name of employees whose name is between A and D.
1/8/2021
Consider a database table T containing two columns X and Y each of type
integer. After the creation of the table, one record (X= 1, Y=l) is inserted in the
table. Let MX and MY denote the respective maximum values of X and Y among
all records in the table at any point in time. Using MX and MY, new records are
inserted in the table 128 times with X and Y values being MX+1, 2*MY+1
respectively. It may be noted that each time after the insertion, values of MX
and MY change. What will be the output of the following SQL query after the
steps mentioned above are carried out?
SELECT Y FROM T WHERE X=7;
(A) 127 (B) 255 (C) 129 (D) 257
1/8/2021
Creating Table with the help of another table.
Create table <table_name> (<col_name> ,….. <col_name>) as select <
col_name>,…..<col_name> from <table_name>;
1/8/2021
Insert into Table with the help of another table.
INSERT INTO table2 SELECT * FROM table1
WHERE condition;
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ... FROM table1
WHERE condition;
1/8/2021
RENAME table_name TO new_table_name;
Rename Student to employee;
Rename Student(rollno, name) to employee(eno, ename);
Describe <table_name>
Describe student; (it describe the structure of table )
1/8/2021
SQL by default print duplicate data. If we want to element duplicity
we should use distinct keyword.
Example:
1/8/2021
Employee( ename , salary, empno ,job ,deptno, email, commision)
WAQ to find name of employees with null commission.
WAQ to find name of all employees whose name start with C.
WAQ to find name of all employees whose name start with C and end
with T.
1/8/2021
Employee( ename , salary, empno ,job ,deptno, email, commision)
WAQ to find name of all employees whose name contain atleast 2 a’s.
WAQ to find name of all employees whose name start with C and at
third position B.
WAQ to find name of all employees whose name is three character
long.
1/8/2021
Employee( ename , salary, empno ,job ,deptno, email, commision)
WAQ to find name of all employees whose email id contain
_(underscore sign).
Wild characters :
1. /
2. %
3. _
1/8/2021
Symbol Description Example
Represents zero or bl% finds bl, black,
%
more characters blue, and blob
Represents a single h_t finds hot, hat, and
_
character hit
Represents any single
h[oa]t finds hot and
[] character within the
hat, but not hit
brackets
Represents any
h[^oa]t finds hit, but
^ character not in the
not hot and hat
brackets
c[a-b]t finds cat and
Represents a range of
- cbt
characters
Using this we can use
/ wild character as a
data.
1/8/2021
LIKE Operator Description
WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
Finds any values that have "r" in the second
WHERE CustomerName LIKE '_r%'
position
Finds any values that start with "a" and are at
WHERE CustomerName LIKE 'a_%'
least 2 characters in length
Finds any values that start with "a" and are at
WHERE CustomerName LIKE 'a__%'
least 3 characters in length
Finds any values that start with "a" and ends
WHERE ContactName LIKE 'a%o'
with "o"
1/8/2021
What is output of the following query?
SELECT ename FROM employee where ename not like ‘%aa%’.
1/8/2021
The ORDER BY keyword sorts the records in ascending order by default.
To sort the records in descending order, use the DESC keyword.
SELECT column1, column2, ...FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
WAQ to find name of all employees in ascending order.
WAQ to find name of all employees in decending order.
1/8/2021
What is output of the following query?
SELECT * FROM Customers ORDER BY Country, CustomerName;
1/8/2021
Aggregate functions or group valued function:
MIN(): The MIN() function returns the smallest value of the selected
column
MAX(): The MAX() function returns the largest value of the selected
column.
SUM(): The SUM() function returns the total sum of a numeric column.
AVG(): The AVG() function returns the average value of a numeric column.
COUNT(): The COUNT() function returns the number of rows that matches
a specified criterion
1/8/2021
Eno Ename Salary Deptno
1 Ankit 1000 10
2 Raj 2000 20
3 Ram 10
4 Zubin 3000 30
5 Rohit 1000 40
Select Min(Salary) from employee;
Select Max(Salary) from employee;
Select Sum(Salary) from employee;
Select Avg(Salary) from employee:
Select Count(Salary) from employee;
1/8/2021
Select Count (distinct Ename) from employee ;
Select Ename sum(Salary) from employee ;
1/8/2021
GROUP BY:
The GROUP BY statement is often used with aggregate functions (COUNT,
MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
SELECT column_name(s) FROM table_name WHERE condition
GROUP BY column_name(s)
Eno Ename Salary Deptno
1 Ankit 1000 10
2 Raj 2000 20
3 Ram 10
4 Zubin 3000 30
5 Rohit 1000
Select Ename , sum(Salary) from employee Group by Ename ;
1/8/2021
Eno Ename Salary Deptno
1 Ankit 1000 10
2 Raj 2000 20
3 Ram 1000 10
4 Zubin 3000 30
5 Rohit 1000
What is output of following Query?
Select deptno , sum(Salary) from employee Group by deptno ;
1/8/2021
HAVING:
The HAVING clause was added to SQL because the WHERE keyword
could not be used with aggregate functions
SELECT column_name FROM table_name WHERE condition
GROUP BY column_name HAVING condition;
SELECT COUNT(CustomerID), Country FROM Customers
GROUP BY Country HAVING COUNT(CustomerID) > 5;
1/8/2021
WAQ to find deptno with more then 5 employee
WAQ to find deptno whose average salary is greater than 2000.
WAQ to find deptno with more then 5 employee
and salary is greater than 2000.
1/8/2021
Which of the following statements are TRUE about an SQL query?
P : An SQL query can contain a HAVING clause even if it does not have a GROUP
BY clause
Q : An SQL query can contain a HAVING clause only if it has GROUP BY clause
R : All attributes used in the GROUP BY clause must appear in the SELECT clause
S : Not all attributes used in the GROUP BY clause need to appear in the SELECT
clause
(A) P and R (B) P and S (C) Q and R (D) Q and S
1/8/2021
Sub Query
WAQ to find name of employee drawing highest
salary.
WAQ to find name of employee drawing the salary greater then the salary of
Ram.
1/8/2021
1/8/2021
1/8/2021
Correlated SubQuery
If the execution of inner query is depends on the outer query then
such type of query is called correlated subquery.
Normal Subquery: Select name from employee where salary
>All(select salary from employee where ename = “RAJ”
Correlated Subquery: select title from book as B where (select
count(*) from book as T where T.price > B.price) < 5
1/8/2021
Correlated subqueries are used for row-by-row processing. Each
subquery is executed once for every row of the outer query.
1/8/2021
The relation book (title, price) contains the titles and prices of
different books. Assuming that no two books have the same price,
what does the following SQL query list?
select title from book as B where (select count(*) from book as T
where T.price > B.price) < 5
(A) Titles of the four most expensive books
(B) Title of the fifth most inexpensive book
(C) Title of the fifth most expensive bookTitles of the five most
expensive books
(D) Titles of the five most expensive books
1/8/2021
SQL Constraints
The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies
each row in a table
FOREIGN KEY- Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
1/8/2021
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
1/8/2021
CREATE TABLE Student(Rollno varchar(10) NOT NULL, Name
varchar(60), Age number(5));
CREATE TABLE Student(Rollno varchar(10) Unique, Name varchar(60),
Age number(5));
CREATE TABLE Student(Rollno varchar(10) NOT NULL Unique, Name
varchar(60), Age number(5));
CREATE TABLE Student(Rollno varchar(10) Primary key, Name
varchar(60), Age number(5));
1/8/2021
CREATE TABLE Student(Rollno varchar(10), Name varchar(60), Age number(5),
unique(ID));
CREATE TABLE Student(Rollno varchar(10), Name varchar(60), Age number(5),
Primary key(Rollno, name));
CREATE TABLE Student(Rollno varchar(10), Name varchar(60), Age int CHECK
(Age>=18));
CREATE TABLE Student(Rollno varchar(10), Name varchar(60), Age int Default
18);
CREATE TABLE Student(Enroll varchar(5) Primary key, Rollno varchar(10)
References Employee(ID), Name varchar(60), Age int);
CREATE TABLE Student(Enroll varchar(5) Primary key, Rollno varchar(10), Name
varchar(60), Age int(3), foreign key(Rollno) References Employee(ID));
1/8/2021
