Experiment No.

– 1
Aim : Case study about SQL and its command languages .
Theory : SQL is Structured Query Language, which is a computer language for storing, manipulating and
retrieving data stored in a relational database.
SQL is the standard language for Relational Database System. All the Relational Database Management
Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their
standard database language.
Advantages -
• Allows users to access data in the relational database management systems.
• Allows users to describe the data.
• Allows users to define the data in a database and manipulate that data.
• Allows to embed within other languages using SQL modules, libraries & pre-compilers.
• Allows users to create and drop databases and tables.
• Allows users to create view, stored procedure, functions in a database.
• Allows users to set permissions on tables, procedures and views.

Types of SQL commands-

1. DDL (Data Definition Language):
Data Definition Language actually consists of the SQL commands that can be used to define the database
schema. It simply deals with descriptions of the database schema and is used to create and modify the
structure of database objects in the database.DDL is a set of SQL commands used to create, modify, and
delete database structures but not data. These commands are normally not used by a general user, who
should be accessing the database via an application.

List of DDL commands:

a. CREATE It is used to create a new table in the database.

Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

b. DROP: It is used to delete both the structure and record stored in the table.

Syntax DROP TABLE table_name;

 c. ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics
of an existing attribute or probably to add a new attribute.

Syntax:

To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

ALTER TABLE table_name MODIFY(column_definitions....);

d. TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.

Syntax: TRUNCATE TABLE table_name;

2. Data Manipulation Language

o DML commands are used to modify the database. It is responsible for all form of changes in the database.
o The command of DML is not auto-committed that means it can't permanently save all the changes in the
database. They can be rollback.

List of DML commands:

a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Syntax: INSERT INTO TABLE_NAME (col1, col2, col3,.... col N)

VALUES (value1, value2, value3, .... valueN);

b. UPDATE: This command is used to update or modify the value of a column in the table.

Syntax:UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

c. DELETE: It is used to remove one or more row from a table.

Syntax: DELETE FROM table_name [WHERE condition];

3. Data Control Language

DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:
a. Grant: It is used to give user access privileges to a database.
Example
1. GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

b. Revoke: It is used to take back permissions from the user.

 Example

1. REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4. Transaction Control Language
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.
These operations are automatically committed in the database that's why they cannot be used while creating tables
or dropping them.
Here are some commands that come under TCL:
a. Commit: Commit command is used to save all the transactions to the database.
Syntax: COMMIT;

b. Rollback: Rollback command is used to undo transactions that have not already been saved to the database.

Syntax: ROLLBACK;

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.

Syntax: SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database.
It uses only one command:
a. SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based
on the condition described by WHERE clause.
Syntax:
SELECT expressions
FROM TABLES
WHERE conditions;
 Experiment No. – 2
Aim : Delete duplicate row from the table.
Procedure:
Step 1: First we have to create a table having named “DETAILS”-
Step 2: Now, we have to insert values or data in the table .We have a view of the Table after inserting the
values:
Step 3: In this step, we have to find how many rows are duplicated.
Step 4: You can also find out the unique row by using this row.
Step 5: Finally we have to delete the duplicate row from the Database.
Step 6: After deleting the duplicate row, then we have a view of the table:
Query:
CREATE TABLE DETAILS
( SN INT IDENTITY(1,1)
EMPNAME VARCHAR(25),
DEPT VARCHAR(20),
CONTACTNO BIGINT NOT NULL,
CITY VARCHAR(15) );
INSERT INTO EMPDETAIL
VALUES ('VISHAL','SALES',9193458625,'GAZIABAD'),
('VIPIN','MANAGER',7352158944,'BAREILLY'),
('ROHIT','IT',7830246946,'KANPUR'),
('RAHUL','MARKETING',9635688441,'MEERUT'),
('SANJAY','SALES',9149335694,'MORADABAD'),
('VIPIN','MANAGER',7352158944,'BAREILLY'),
('VISHAL','SALES',9193458625,'GAZIABAD'),
('AMAN','IT',78359941265,'RAMPUR');
SELECT * FROM EMPDETAIL;
SELECT EMPNAME,DEPT,CONTACTNO,CITY, COUNT(*) FROM EMPDETAIL
GROUP BY EMPNAME,DEPT,CONTACTNO,CITY
HAVING COUNT(*)>1
SELECT EMPNAME,DEPT,CONTACTNO,CITY, COUNT(*) FROM DETAILS
GROUP BY EMPNAME,DEPT,CONTACTNO,CITY
DELETE FROM DETAILS WHERE SN NOT IN (
SELECT MAX(SN) FROM DETAILS GROUP BY EMPNAME,DEPT,CONTACTNO,CITY)
SELECT * FROM EMPDETAIL;

Output:
 Experiment No. – 3
Aim : Write a SQL statements for rollback, commit and save points.

Theory:
The ROLLBACK Command
The ROLLBACK command is the transactional command used to undo transactions that have not already been
saved to the database.
The syntax −ROLLBACK;
Example
Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would delete those records from the table which have the age = 25 and then
ROLLBACK the changes in the database.

SQL> DELETE FROM CUSTOMERS

WHERE AGE = 25;
SQL> ROLLBACK;

Thus, the delete operation would not impact the table and the SELECT statement would produce the following
result.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
The COMMIT Command
The COMMIT command is the transactional command used to save changes invoked by a transaction to the
database.
The syntax -COMMIT;
Example
Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example which would delete those records from the table which have age = 25 and then COMMIT
the changes in the database.

SQL> DELETE FROM CUSTOMERS

WHERE AGE = 25;
SQL> COMMIT;
Thus, two rows from the table would be deleted and the SELECT statement would produce the following result.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

The SAVEPOINT Command

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without
rolling back the entire transaction.
The syntax - SAVEPOINT SAVEPOINT_NAME;
The syntax for rolling back to a SAVEPOINT is as shown below.
ROLLBACK TO SAVEPOINT_NAME;
Example
Consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
The following code block contains the series of operations.

SQL> SAVEPOINT SP1;

Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.
Now that the three deletions have taken place, let us assume that you have changed your mind and decided to
ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the
last two deletions are undone −

SQL> ROLLBACK TO SP2;

Rollback complete.
Notice that only the first deletion took place since you rolled back to SP2.

SQL> SELECT * FROM CUSTOMERS;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.
 Experiment No. – 4
Aim : Update multiple rows in using single update statement.
Procedure: In SQL, sometimes we need to update multiple records in a single query. We will use the UPDATE
keyword to achieve this.
Step 1: Create a Database, create a database named Company.
Step 2: Use the Company database.
Step 3: Create a table of BANDS inside the database GeeksForGeeks. This table has 3 columns namely
BAND_NAME, PERFORMING_COST, and NUMBER_OF_MEMBERS containing the names, charges for
performing, and the number of members of a band.
Step 4: Insert 5 rows into the BANDS table. Display all the rows of the BANDS table.
Step 5: Update all records of the table BANDS satisfying two(multiple) conditions. The condition here is if
the BAND_NAME is ‘METALLICA’, then its PERFORMING_COST is set to 90000 and if the BAND_NAME is ‘BTS’,
then its PERFORMING_COST is set to 200000. Use the keyword UPDATE and WHEN to achieve this. This query
behaves like an if-else if-else block.
Step 6: Display all the rows of the updated FIRM table. The PERFORMING_COST for METALLICA and BTS have
been updated to 90000 and 200000 respectively

Syntax:
UPDATE TABLE_NAME
SET COLUMN_VALUE
= CASE COLUMN_NAME
WHEN 'COLUMN_NAME1' THEN COLUMN_VALUE1
WHEN 'COLUMN_NAME2' THEN COLUMN_VALUE2
ELSE COLUMN_VALUE
END
WHERE BAND_NAME IN('COLUMN_NAME1', 'COLUMN_NAME2');

Query:
CREATE DATABASE GeeksForGeeks
USE GeeksForGeeks
CREATE TABLE BANDS(
BAND_NAME VARCHAR(20),
PERFORMING_COST INT,
NUMBER_OF_MEMBERS INT);
INSERT INTO BANDS VALUES('INDIAN OCEAN',10000,5);
INSERT INTO BANDS VALUES('BTS',20000,6);
INSERT INTO BANDS VALUES('METALLICA',30000,10);
INSERT INTO BANDS VALUES('BEATLES',40000,4);
INSERT INTO BANDS VALUES('EAGLES',50000,4);
SELECT * FROM BANDS;
UPDATE BANDS
SET PERFORMING_COST = CASE BAND_NAME
WHEN 'METALLICA' THEN 90000
WHEN 'BTS' THEN 200000
ELSE PERFORMING_COST
END
WHERE BAND_NAME IN('METALLICA', 'BTS');
SELECT * FROM BANDS;
Output:
Before update-

After update-
 Experiment No. – 5
Aim : Find the third highest paid and third lowest paid salary
Query:
• FOR THE HIGHEST PAID-
As this query is nested query lets understand each part step by step:
Step 1: First this part of the query will get executed then the outer part of the query will act on the result p
roduced by this query :

select ename, salary, dense_rank() over(order by salary desc)rank from Emp

As you can see that few employees are getting the same salary(for example Bhaskar, Parul, and Chandan,
Garima are getting the same salary, therefore we have used dense_rank(), order by salary desc will arrang
e salary in descending order.

Output of : select ename, salary, dense_rank() over(order by salary desc)rank from Emp
ENAME SALARY RANK
------------ ---------- ----------
sonoo 40000 1
ravi 37000 2
rohit 32000 3
bhaskar 30000 4
parul 30000 4
akshita 28000 5
durgesh 28000 5
garima 25000 6
chandan 25000 6
amit 20000 7

Step 2: SQL> select * from(

select ename, salary, dense_rank()
over(order by salary desc)rank from Emp)
where r = &n;
n= the number of rank

select * from(select ename, salary, dense_rank() over(order by salary desc)r from Emp) where r=3
Output for n = 3 will be:

ENAME SALARY R
------------ ---------- ----------
rohit 32000 3
 • FOR THE LOWEST PAID-

Output of : select ename, salary, dense_rank() over(order by salary )rank from Emp
ENAME SALARY RANK
------------ ---------- ----------
amit 20000 1
chandan 25000 2
garima 25000 2
durgesh 28000 3
akshita 28000 3
parul 30000 4
bhaskar 30000 4
rohit 32000 5
ravi 37000 6
sonoo 40000 7

Step 2: SQL> select * from(

select ename, salary, dense_rank()
over(order by salary )rank from Emp)
where r = &n;

select * from(select ename, salary, dense_rank() over(order by salary desc)r from Emp) where r=2
Output for n = 3 will be:

ENAME SALARY R
------------ ---------- ----------
durgesh 28000 3

•
 Experiment No. – 6
Aim : Display the 3rd, 4th, 9th rows from table.

Syntax :
SELECT * FROM <table_name> LIMIT N-1,1;

Here N refers to the row which is to be retrieved.

Query : lets take this table

1. to display 3rd row-

SELECT * FROM Customers LIMIT 2,1;
Output-

2. to display 4th row-

SELECT * FROM Customers LIMIT 3,1;
Output-

3. to display 9th row-

SELECT * FROM Customers LIMIT 8,1;
Output-
 Experiment No. – 7
Aim : . Display the name, which is start with i, k, or e.

Theory : The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Here we
have to find more the one alphabet starting names so we will use ‘OR’ function , to get all those letter names
together.
Syntax: SELECT * FROM Table_name
WHERE column_name LIKE 'a%';

Query:
create the table –
CustomerID CustomerNam ContactNamE
17 Drachenblut Delikatessen Aachen
38 Island Tradin Cowes
39 Königlich Esse Brandenburg
19 Eastern Connectio London

Then apply the query –

SELECT * FROM Customers
WHERE CustomerName LIKE 'i%' or CustomerName LIKE 'e%' or CustomerName LIKE 'k%';

Output:
 Experiment No. – 8
Aim : Show all employees who were hired the first half of the month.

Syntax:
select * from table_name where to_char (column_name, ‘DD’)<‘16’;

Theory :
here below is given the table-
EMPLOYEES

Query:
select * from employees where to_char (HIRE_DATE, ‘DD’)<‘16’;

Output:
Employee_id First_name Last_name email Phone- Hire-date
number
100 Steven King SKING 515.123.4567 1987/06/17
 Experiment No. – 9
Aim : inner and full joint
Theory :
• INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.
Syntax-
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

• FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table
records.

Syntax-
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Example- Given tables-

Inner join[Apply the query]-
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer;
Output-

Full join[Apply the query]-

SELECT Customers.customer_id, Customers.first_name, Orders.amount

FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;

Output-
 Experiment No. – 10
Aim : Solving the case study of Library Management system using ER data model.

Theory : ER Diagram is known as Entity-Relationship Diagram, it is used to analyze the structure of the
Database. It shows relationships between entities and their attributes. An ER Model provides a means of
communication.
The Library Management System database keeps track of readers with the following considerations –
• The system keeps track of the staff with a single point authentication system comprising login Id and
password.
• Staff maintains the book catalog with its ISBN, Book title, price(in INR), category(novel, general, story),
edition, author Number and details.
• A publisher has publisher Id, Year when the book was published, and name of the book.
• Readers are registered with their user_id, email, name (first name, last name), Phone no (multiple
entries allowed), communication address. The staff keeps track of readers.
• Readers can return/reserve books that stamps with issue date and return date. If not returned within
the prescribed time period, it may have a due date too.
• Staff also generate reports that has readers id, registration no of report, book no and return/issue info.

ER Diagram of Library Management System

This Library ER diagram illustrates key information about the Library, including entities such as staff,
readers, books, publishers, reports, and authentication system. It allows for understanding the relationships
between entities.
Entities and their Attributes –
1. Book Entity : It has authno, isbn number, title, edition, category, price. ISBN is the Primary Key for
Book Entity.
2. Reader Entity : It has UserId, Email, address, phone no, name. Name is composite attribute of
firstname and lastname. Phone no is multi valued attribute. UserId is the Primary Key for Readers
entity.
3. Publisher Entity : It has PublisherId, Year of publication, name. PublisherID is the Primary Key.
4. Authentication System Entity : It has LoginId and password with LoginID as Primary Key.
5. Reports Entity : It has UserId, Reg_no, Book_no, Issue/Return date. Reg_no is the Primary Key of
reports entity.
6. Staff Entity : It has name and staff_id with staff_id as Primary Key.
7. Reserve/Return Relationship Set : It has three attributes: Reserve date, Due date, Return date.

Relationships between Entities –

• A reader can reserve N books but one book can be reserved by only one reader. The relationship 1:N.
• A publisher can publish many books but a book is published by only one publisher. The relationship 1:N.
• Staff keeps track of readers. The relationship is M:N.
• Staff maintains multiple reports. The relationship 1:N.
• Staff maintains multiple Books. The relationship 1:N.
• Authentication system provides login to multiple staffs. The relation is 1:N.