Ex. No.

1
PRACTICE ON DDL AND DML COMMANDS

AIM:
To create a database table, add constraints (primary key, unique, check, not null), insert rows, update
and delete rows using SQL DDL and DML commands.

DDL COMMANDS:
DDL is an abbreviation for Data Definition Language. It is concerned with database schemas and
descriptions of how data should be stored in the database. DDL statements are auto-committed,
meaning the changes are immediately made to the database and cannot be rolled back.
DDL is used to:
 Create an object
 Alter the structure of an object
 Drop the object created.
The commands used are: Create, Alter, Drop, Truncate

CREATE DATABASE
It creates a database with the given name. An error occurs if the database exists and you did not
specify IF NOT EXISTS.

Syntax: create {database} [if not exists] db_name

Example: create database if not exists cse001

USE
Creating a database does not select it for use. It must be done explicitly.

Syntax: use db_name

Example: use cse001

CREATE TABLE
It is used to create a table. To set default value for a column DEFAULT keyword is used.

Syntax: Create table tablename (column_name1 data_ type constraints, column_name2

data_ type constraints …, table level constraints)

CONSTRAINTS
Constraints are certain conditions or restrictions applied to the columns on the table. Some of
the constraints are
 PRIMARY KEY
 UNIQUE
 CHECK
 NOT NULL

PRIMARY KEY
A primary key is the column or set of columns that contain values that uniquely identify each row
in a table.

UNIQUE
Unique ensures there are no duplicate values stored in a particular column or a set of
columns.

CHECK
Check constraint ensures to enter only those values which fulfill the specified condition.

NOT NULL
The not null constraint is used to ensure that a given column of a table is never assigned the null
value.

Example: create table stud_001(sno int primary key, sname varchar(20) not null, email varchar(30)
unique, age int check (age >= 16), sdob date default '2001-01-01', sm1 float, sm2 float,
sm3 float);

ALTER
Alter command is used to
 Add a column.
Syntax: Alter table table_name add column_name datatype;
Example: Alter table stud_001 add (sm4 float);

 Drop a column
Syntax: Alter table table_name drop column column_name;
Example: Alter table stud_001 drop column age;

 Modify the existing column definition.

Syntax: Alter table table_name modify column column_name datatype;
Example: Alter table std modify(sname varchar(25));

 Include an integrity constraint.

Syntax: Alter table table_name add constraint constraint_name constraint_specification;
Example: Alter table stud_001 add constraint check_sm1 check (sm1>=0 and sm1 <=100);
  Drop an integrity constraint.
Syntax: Alter table table_name drop constraint constraint_name;
Example: Alter table stud_001 drop constraint check_sm1;

DROP
Drop table will delete the table structure.

Syntax: Drop table table_name;

Example: drop table stud_001;

TRUNCATE
If there is no further use of records stored in a table and the structure has to be retained then the
records alone can be deleted.

Syntax: Truncate table table_name;

Example: Truncate table stud_001;

RENAME
This statement renames a table name with a new table name.

Syntax: Rename table oldtablename to newtablename

Example: Rename table stud_001 to stud;

DML COMMANDS:
DML commands are the most frequently used SQL commands and is used to query and
manipulate the existing database objects. Some of the commands are Insert, Select, Update,
Delete

INSERT
Insert Statement is used to insert new records into the database table.

To insert all columns:

Syntax: INSERT INTO table_name VALUES (value1, value2,…..);
Example: insert into stud_001 values (123, 'Arun', 'arun@gmail.com', 18,
'2002-3-04', 89,98,34);

To insert for specific columns:

Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1,
value2, ...);
Example: insert into stud_001 (sno, sname, sm1, sm2, sm3) values (124, 'Hari',
78, 88, 98);

SELECT
Select statement is used to retrieve data from tables.

Syntax: SELECT column1, column2, ...FROM table_name [where condition];

To retrieve all the records with all the columns:

Example: Select * from stud_001;

To retrieve all the records with specific columns:

Example: Select sno,sname from stud_001;

To retrieve the records that satisfy a condition:

Example: Select * from stud_001 where sm1>80;

UPDATE
Update statement is used to modify the existing values of records present in the
database table.
Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ...[WHERE condition];

To update all the records:

Example: Update stud_001 SET sm3=99;

To update specific records that satisfy a condition:

Example: Update stud_001 SET sm3=95 where sno=124;

DELETE
Delete statement is used to delete the existing records present in the database table.
Syntax: DELETE FROM table_name [where condition];

To delete all the records:

Example: delete from stud_001;

To delete specific records that satisfy a condition:

Example: delete from stud_001 where sno=123;
 Worksheet 1

1. Create a database named cse<last three digits of your roll number>

2. Use the database created.

3. Create a table to represent an employee of an organization with necessary constraints as

given below. Each employee is provided with an employee_id. Their name and department
could not be left blank. Each employee is provided a default salary of 10000.

4. Insert five records into the table.

5. Retrieve all the records from the table.

6. Update the email id of the employee whose employee_id is 1234. After update retrieve the
records.

7. Add a column named bonus with appropriate datatype.

8. Update the value of the column created with 5% of the salary. After update retrieve the
records.

9. Delete the column whose bonus falls below 600. After delete retrieve the records.

10. Remove the table from the database.

 Ex. No. 2
PRACTICE ON REFERENTIAL INTEGRITY CONSTRAINTS

AIM:
To create a set of tables, add foreign key constraints and incorporate referential integrity.

REFERENTIAL INTEGRITY
Referential integrity is a property of data stating that all its references are valid. Referential integrity
refers to the accuracy and consistency of the relationship between tables. It is achieved with the help
of foreign key.

FOREIGN KEY
Foreign keys link data in one table to the data in another table. A foreign key column in a table
points to a column with unique values in another table (often the primary key column) to create a
way of cross-referencing the two tables. The table with the foreign key is called the child table, and
the table with the primary key is called the referenced or parent table.

CONVERTING RELATIONSHIP TO TABLES

Converting one-to-one relationship
 Modify either the A or B side of each one-to-one connection, add the Primary key of the
opposite side as a foreign key.
Example:

countrycode name citycode name

1 1
Country Capital of City

Create table country(countrycode int primary key,name varchar(10));

Create table City(citycode int primary key, name varchar(25));

Alter table country add capital int;

Alter table country add constraint fk_capcode foreign key (capital) references city(citycode);

Converting one-to-many relationship

 Modify the M side of each one-to-many relation to include the Primary key of one of the
sides as a foreign key.
Example:

eno ename deptno dname

m 1
Employee Works for Department

Create table dept_001(deptno int primary key,dname varchar(10));

Create table emp_001(eno int primary key, ename varchar(25));

Alter table emp_001 add deptno int;

alter table emp_001 add constraint fk_deptno foreign key (deptno) references dept_001(deptno);

Converting many-to-many relationship

 Create a new table for each many-to-many relationship, using the Primary key from the M
side and the N side as foreign keys.

Authorid name ISBN title

m n
Author writes Book

Create table author(authorid int primary key,name varchar(20));

Create table book(isbn int primary key, title varchar(25));

Create table au_writes(authorid int,isbn int, primary key(authorid,isbn));

Alter table au_writes add constraint fk_rel1 foreign key (authorid) references author(authorid);

Alter table au_writes add constraint fk_rel2 foreign key (isbn) references book(isbn);
Worksheet 2

1. Draw the ER diagram for the following hospital management system and convert them to
relations
 Hospital staff consist of doctors.
 Each doctor has a empid, name (first name, middle name, and last name), and phone
numbers, date of joining, specialization.
 Each doctor belongs to a specific department in the hospital.
 Doctors treat patients.
 Patient information collected by the hospital consists of patientID, name, phonenumber,
date of registration.
 A patient can undergo a number of tests.
 Doctors perform tests.
 Each test has a unique ID, name, a result, and a date and time the test was performed.

2. Insert records in each table and check referential integrity is maintained.

 Ex. No. 3
PRACTICE ON AGGREGATE FUNCTIONS

AIM
To query the database tables using different ‘where’ clause conditions and aggregate functions.

Following tables are used in the examples:

Create table student_marks(sno int, name varchar(20),m1 int,m2 int,m3 int,m4 int,m5 int);
insert into student_marks values(123,'iii',12,23,34,45,56);
insert into student_marks values(124,'jjj',42,63,74,85,96);

LOGICAL OPERATORS USED WITH WHERE CLAUSE

Operator Description and Example

AND TRUE if all the conditions separated by AND is TRUE
Example: select * from student_marks where m1>50 and m2>60;
BETWEEN TRUE if the operand is within the range of comparisons
Example: select * from student_marks where m1 between 50 and 60;
IN TRUE if the operand is equal to one of a list of expressions
Example: select * from student_marks where m3 in (45,47,49);
LIKE TRUE if the operand matches a pattern
 The percentage ( % ) wildcard matches any string of zero or more
characters.
Example: To retrieve students whose name starts with i
select * from student_marks where name like ‘i%’
 The underscore ( _ ) wildcard matches any single character.
Example: To retrieve students whose name has second character as i
select * from student_marks where name like ‘_i%’
NOT Displays a record if the condition(s) is NOT TRUE
 Example 1: select * from student_marks where m3 not in (45,47,49);
Example 2: select * from student_marks where name not like ‘i%’
OR TRUE if any of the conditions separated by OR is TRUE
Example: select * from student_marks where m1>50 or m2>60;

HANDLING NULL VALUES IN WHERE CLAUSE

A field with a NULL value is a field with no value. A NULL value is different from a zero value or
a field that contains spaces. A field with a NULL value is one that has been left blank.

Operator Description and Example

IS NULL This operator returns true, if the column value is NULL.
Example: select * from student_marks where m1IS NULL;
IS NOT This operator returns true, if the column value is not NULL.
NULL Example: select * from student_marks where m1 IS NOT NULL;

AGGREGATE FUNCTIONS
An aggregate function performs a calculation on a set of values and returns a single value. These
functions can be used with the SELECT statement to generate summary information, such as the
number of rows in a table or the average value of a column. Some of the aggregate functions are
AVG, COUNT, SUM, MAX, MIN

Consider the following:

Create table student_marks(sno int, name varchar(20),m1 int,m2 int,m3 int,m4 int,m5 int);

insert into student_marks values(123,'iii',12,23,34,45,56);

insert into student_marks values(124,'jjj',42,63,74,85,96);

select * from student_marks;

sno name m1 m2 m3 m4 m5
123 iii 12 23 34 45 56
123 jjj 42 63 74 85 96

alter table student_marks add total int;

update student_marks set total=m1+m2+m3+m4+m5;

select * from student_marks;

sno name m1 m2 m3 m4 m5 total
123 iii 12 23 34 45 56 170
123 jjj 42 63 74 85 96 360

Function Description Example Output

AVG Return the summation of all non- select avg(m3) from avg(m3)
NULL values in a set. student_marks; 54.0000

COUNT Return the number of rows in a select count(*) from count(*)

group, including rows with NULL student_marks; 2

values.
SUM Return the summation of all non- select sum(m4) from sum(m4)
NULL values a set. student_marks; 130

MAX Return the highest value (maximum) select max(m1) from max(m1)
in a set of non-NULL values. student_marks; 42

MIN Return the lowest value (minimum) select min(m2) from min(m2)
in a set of non-NULL values. student_marks; 23

GROUP BY
The GROUP BY clause groups a set of rows into a set of summary rows based on column values or
expressions. It returns one row for each group and reduces the number of rows in the result set.
The group by clause is an optional part of the select statement.

HAVING
The HAVING clause is used in conjunction with the GROUP BY clause to filter the groups based on
a specified condition. The HAVING clause allows you to apply a condition to the groups returned by
the GROUP BY clause and only include groups that meet the specified condition.

ORDER BY
The ORDER BY keyword is used to sort the result-set in ascending or descending
order.The ORDER BY keyword sorts the records in ascending order by default. To sort the records
in descending order, use the DESC keyword.

Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
[ORDER BY column_name(s)];

Consider the following(continuing the previous example):

insert into student_marks values(125,'jjj',100,63,74,85,96);
alter table student_marks add dept varchar(5);

update student_marks set dept='cse' where sno in (123,124);

update student_marks set dept='ece' where sno in (125);

select * from student_marks;

sno name m1 m2 m3 m4 m5 total dept
123 iii 12 23 34 45 56 170 cse
124 jjj 42 63 74 85 96 360 cse
125 jjj 100 63 74 85 96 418 ece

Example for Group by:

To retrieve the count of students in each department:
select dept,count(*) from student_marks group by dept;

dept count(*)
cse 2
ece 1

Example for Group by and Having:

To retrieve the department whose count is greater than or equal to 2
select dept,count(*) as c from student_marks group by dept having c>=2;

dept c
cse 2

Example for order by:

To retrieve the count of students in each department displayed in ascending order of dept
select dept,count(*) as c from student_marks group by dept order by dept asc;

dept c
cse 2
ece 1

To retrieve the count of students in each department displayed in descending order of dept
select dept,count(*) as c from student_marks group by dept order by dept desc;
dept c
ece 1
cse 2

Worksheet 3

1. Create the following table and insert 10 records.

Employee(Empno, Name, Date_of_birth, Date_of_joining, Department, Designation, Salary)

2. Display the details of employees whose salary is in the range 15000 to 20000.
3. Display the details of employees who works in ‘CSE’ department and get salary above
15000.
4. Display the details of employees who works in ‘CSE’ department or ‘ECE’ department.
5. Display the details of manager, electrician and technician.
6. Display the name of employees whose name ends with ‘an’.
7. Display the name of employees whose name does not have ‘n’ as the fourth character.
8. Display the details of employees whose Date_of_birth is left blank.
9. Display the lowest and highest salary of the employees.
10. Display the number of employees in the organization.
11. Display the number of employees in each department.
12. Display the average salary of employees in each department
13. Display the total salary of all the employees in each department
14. Calculate the age of each employee.
15. Calculate the years of experience (approximately) of each employee.
 Ex. No. 4
PRACTICE ON SUBQUERIES

AIM
To query the database tables and explore sub queries.

SUBQUERY
A subquery is a query that is used within another SQL query embedded in the WHERE clause. They
allow us to select specific rows that satisfy certain conditions at the run time. They are also known as
the inner query or inner select, and the query that contains them is known as the outer query or outer
select. Subqueries are majorly used in SELECT, INSERT, UPDATE, DELETE, FROM and
WHERE clauses. The subqueries are used wherever an expression is allowed. The data returned by
the subquery is used by the outer statement in the same way a literal value would be used.

TYPES OF SUBQUERY
 Single row subquery
 Multi row subquery
 Multi column subquery
 Nested subquery
 Correlated subquery

The relations of the following ER diagram is used in the examples.

ISBN title
Authorid name

price

m n
Author writes Book
Author
authorid name
123 ccc
124 ddd
125 eee
126 fff

Book
isbn title price
1 b1 1000
2 b2 1500
3 b3 2000

Au_writes
authorid isbn
123 1
124 1
125 2
123 3

SINGLE ROW SUBQUERY

Subqueries that return a single row as an output to their parent query are called single-row
subqueries.

Example: To retrieve the details of the book whose price is less than or equal to the average
price of all books

select * from book where price <= (select avg(price) from book);

Output:
isbn title price
1 b1 1000
2 b2 1500

The subquery in the above example retrieves only one average price(1500) which is used in the
where clause.

MULTI ROW SUBQUERY

Subqueries that return multiple rows as an output to their parent query are called multiple-row
subqueries.
Example: To retrieve the details of the author who authored book with ISBN = 1

Select * from author where authorid in (select authorid from auwrites where isbn=1);
Output:
authorid name
123 ccc
124 ddd

Since many author can write one book, the subquery in the above example retrieves more than one
authorid which is used in the where clause.

MULTI COLUMN SUBQUERY

Subqueries that return multiple columns as an output to their parent query are called multiple-
column subqueries.

Example: To retrieve the details of the book whose price is less than or equal to the average
price of all books

Select * from book where (isbn,price) in (select isbn,price from book where price <= (select
avg(price) from book));

Output:
isbn title price
1 b1 1000
2 b2 1500

The subquery in the above example retrieves multiple column namely isbn,price which is used in the
where clause.

NESTED SUBQUERY
Subqueries that are inside another subquery are called nested subqueries. Subqueries are executed
level by level. The innermost is executed first, and then the outer ones.

Example: To retrieve the details of the author who wrote the book that cost the least

Select * from author where authorid in (select authorid from au_writes where isbn in (select isbn
from book where price = (select min(price) from book)));

Output:
authorid name
123 ccc
124 ddd
In the above example there are three levels of nested where the execution of outer query depends on
inner query.

CORRELATED SUBQUERY
A correlated subquery is a subquery that depends on the outer query and is evaluated for each
instance of the outer query.

Example: To retrieve the details of the author along with the number of books written by them

Select *, (select count(*) from au_writes au where au.authorid = a.authorid) total_books_written

from author a;
Output:
authorid name total_books_written
123 ccc 2
124 ddd 1
125 eee 1
126 fff 0

In the above example, for each row in the author table, the subquery is executed by matching their
authorid.

EXISTS and NOT EXISTS

Subqueries with the EXISTS keyword can be used to know whether any row exists on the table or
not.
Subqueries that uses EXISTS keyword will return TRUE if the subquery returns any rows. If the
subquery using EXISTS keyword does not return any rows then it will return FALSE. The vice versa
when used with NOT.

Example: To retrieve the details of the author who have written at least one book

Select * from author where exists (select * from au_writes where

author.authorid=au_writes.authorid);

Output:
authorid name
123 ccc
124 ddd
125 eee

Example: To retrieve the details of the author who have not written any book

Select * from author where not exists (select * from au_writes where
author.authorid=au_writes.authorid);
Output:
authorid name
126 fff

Worksheet 4

Create the following table and insert values into it.

Accounts(AccNo, Name, Dateofopening, Balance)
Loan(LoanNo Accno Dateofopening, Loan_amount, No. of Installments, EMI)
EMI(LoanNo, Dateofpayment, Amount_paid)

Queries
1. Display the details of the account holder who have more number of loans.
2. Display the details of the account holder who have not taken any loan.
3. Display the loanno, loan amount, number of installments of the loan holder who have
paid EMI on December 3rd 2023.
4. Display the loanno, loanamount and total EMI paid for all the loan holders
5. Add thousand rupees to the EMI of loan holder whose account balance is greater
than 50000
 Ex. No. 5
PRACTICE ON JOINS

AIM
To query the database tables and explore cross join, inner join, equi join, non-equi join, self join,
natural join and outer joins.

JOIN OPEARTION
Joins are mostly used when a user is trying to retrieve data from multiple tables at one time. The join
keyword merges two or more tables and creates a temporary image of the merged table. Then
according to the conditions provided, it extracts the required data from the image table, and once
data is fetched, the temporary image of the merged tables is dumped.

TYPES OF JOIN
 Cross join
 Inner join
o Equi join
o Non-Equi join
o Self join
o Natural join
 Outer join
o Left outer join
o Right outer join
o Full outer join

CROSS JOIN
The CROSS JOIN is used to generate a paired combination of each row of the first table with each
row of the second table. This join type is also known as cartesian product.

Syntax: Select [columns] from [table_1] cross join [table_2];

(OR)
Select * from table1, table 2;
Example: Select * from author cross join book;

Output:
authorid name isbn title price
123 ccc 3 b3 2000
123 ccc 2 b2 1500
123 ccc 1 b1 1000
124 ddd 3 b3 2000
124 ddd 2 b2 1500
124 ddd 1 b1 1000
125 eee 3 b3 2000
125 eee 2 b2 1500
125 eee 1 b1 1000
126 fff 3 b3 2000
126 fff 2 b2 1500
126 fff 1 b1 1000

INNER JOIN
The inner join retrieves the records that matches a condition from the result of the cartesian product.

Types of Inner join:

o Equi join
o Non-Equi join
o Self join
o Natural join

EQUI JOIN
EQUI JOIN creates a JOIN for equality or matching column(s) values of the relative tables. EQUI
JOIN also create JOIN by using JOIN with ON and then providing the names of the columns with
their relative tables to check equality using equal sign (=).

Syntax: select column_list from table1 inner join table2 where

table1.column_name = table2.column_name;

Example: Select * from author a inner join au_writes au where

a.authorid=au.authorid;
Output:
authorid name authorid isbn
123 ccc 123 1
123 ccc 123 3
124 ddd 124 1
125 eee 125 2

NON EQUI JOIN

NON EQUI JOIN performs a JOIN using comparison operator other than equal(=) sign like >, <, >=,
<= with conditions.

Syntax: select column_list from table1 inner join table2 where

table1.column_name [> | < | >= | <= | <> ] table2.column_name;

Example: Select * from author a inner join au_writes au where a.authorid<>au.authorid;

Output:
authorid name authorid isbn
126 fff 123 1
125 eee 123 1
124 ddd 123 1
126 fff 124 1
125 eee 124 1
123 ccc 124 1
126 fff 125 2
124 ddd 125 2
123 ccc 125 2
126 fff 123 3
125 eee 123 3
124 ddd 123 3

SELF JOIN
A self join is an inner join where a table is joined with itself.

Syntax: select column_name(s) from table1 t1 inner join table1 t2 where condition;

Here t1 and t2 are different table aliases for the same table.

Example: Select * from author a inner join author au where a.authorid==au.authorid;

Output:
authorid name authorid name
123 ccc 123 ccc
124 ddd 124 ddd
125 eee 125 eee
126 fff 126 fff
Natural join
Natural Join is a type of Inner join based on the condition that columns having the same name and
datatype are present in both the tables to be joined.

Syntax: select column_list from table1 natural join table2;

Example: Select * from author natural join au_writes;

Output:
authorid name isbn
123 ccc 1
123 ccc 3
124 ddd 1
125 eee 2
OUTER JOIN
Outer join return all records matching from both tables. It can detect records having no match in
joined table. It returns NULL values for records of joined table if no match is found.

Types of Outer Join:

o Left outer join
o Right outer join
o Full outer join

LEFT OUTER JOIN

The left join returns all the rows from the table on the left even if no matching rows have been found
in the table on the right. Where no matches have been found in the table on the right, NULL is
returned.

Syntax: Select column_list from table1 left join table2 on join_condition;

Example: Select * from author a left join au_writes au on a.authorid=au.authorid;

Output:
authorid name authorid isbn
123 ccc 123 1
123 ccc 123 3
124 ddd 124 1
125 eee 125 2
126 fff NULL NULL

RIGHT OUTER JOIN

The right join is obviously the opposite of left join. The right join returns all the columns from the
table on the right even if no matching rows have been found in the table on the left. Where no
matches have been found in the table on the left, NULL is returned.

Syntax: Select column_list from table1 right join table2 on join_condition;

Example: Select * from au_writes a right join author au on a.authorid=au.authorid;

Output:
authorid isbn authorid name
123 1 123 ccc
123 3 123 ccc
124 1 124 ddd
125 2 125 eee
NULL NULL 126 fff

FULL OUTER JOIN

It is the union of left join and right join. It returns all records when there is a match in left or right
table records. Where no matches have been found in the tables on the left or right, NULL is returned.

Syntax: Select column_list from table1 left join table2 on join_condition union Select column_list
from table1 right join table2 on join_condition;

Example: Select * from author a left join au_writes au on a.authorid=au.authorid union

Select * from author a right join au_writes au on a.authorid=au.authorid;

Output:
authorid name authorid isbn
123 ccc 123 1
123 ccc 123 3
124 ddd 124 1
125 eee 125 2
126 fff NULL NULL