DBMS LAB

EXPERIMENT-1

AKSHAY HARISH
20191ISE0008
4ISE1
 COMMANDS
Enter password: **********

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sakila |

| studentdb |

| sys |

| world |

+--------------------+

7 rows in set (0.00 sec)

mysql> use studentdb;

Database changed

mysql> create table student(

-> snum int primary key,

-> sname varchar(20) not null,

-> major varchar(20) not null,

-> level char(5),

-> dob date);

Query OK, 0 rows affected (0.04 sec)

mysql> desc student;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| snum | int | NO | PRI | NULL | |

| sname | varchar(20) | NO | | NULL | |

| major | varchar(20) | NO | | NULL | |

| level | char(5) | YES | | NULL | |

| dob | date | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

5 rows in set (0.01 sec)

mysql> alter table student add sem int default 4;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| snum | int | NO | PRI | NULL | |

| sname | varchar(20) | NO | | NULL | |

| major | varchar(20) | NO | | NULL | |

| level | char(5) | YES | | NULL | |

| dob | date | YES | | NULL | |

| sem | int | YES | |4 | |

+-------+-------------+------+-----+---------+-------+

6 rows in set (0.02 sec)

mysql> alter table student modify major varchar(20) null;

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| snum | int | NO | PRI | NULL | |

| sname | varchar(20) | NO | | NULL | |

| major | varchar(20) | YES | | NULL | |

| level | char(5) | YES | | NULL | |

| dob | date | YES | | NULL | |

| sem | int | YES | |4 | |

+-------+-------------+------+-----+---------+-------+

6 rows in set (0.01 sec)

mysql> alter table student modify sname varchar(20) unique;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| snum | int | NO | PRI | NULL | |

| sname | varchar(20) | YES | UNI | NULL | |

| major | varchar(20) | YES | | NULL | |

| level | char(5) | YES | | NULL | |

| dob | date | YES | | NULL | |

| sem | int | YES | |4 | |

+-------+-------------+------+-----+---------+-------+

6 rows in set (0.01 sec)

SCREENSHOTS OF THE COMMANDS
DBMS LAB
EXPERIMENT-2

AKSHAY HARISH
20191ISE0008
4ISE1
 COMMANDS

Experiment No.2: To Study and Implement Data Manipulation Language

commands:
Data Manipulation Language: (insert,update,delete)
i)Insert Statement: Allows you to add new records to the
Table Syntax: insert into table_name[(column_list)] values (value_list)
Example: insert single record
INSERT INTO student VALUES (1, ‘Ganesh’, ‘CSE’, ’2000-05-01’,1)
INSERT INTO Student(snum, sname, major, DOB,sem)VALUES (2, ‘ramesh’, ‘CSE’, ’2000-
07-31’,1))
•Note: If the columns are not specified as in the first example the data goes in the order
specified in the table
ii)Delete Statement: It is used to remove records from a table of the database.
The where clause in the syntax is used to restrict the rows deleted from the
table otherwise all the rows from the table are deleted.
i)To remove all rows of a table Syntax:
delete from <tablename>;
mysql> DELETE FROM STUDENT;
ii)removal of a specified row/s Syntax:
DELETE FROM table_name [WHERE Condition]
mysql> DELETE FROM STUDENT WHERE SNAME = ‘Ramesh’
•Deletes all the rows where the sname is ‘Ramesh’ keeps all the other rows.
iii) Update Statement: It is used to make changes to existing rows of the table.
Syntax:
UPDATE table_name SET column_name1 = value1, column_name2 = value2, .....[WHERE
Condition]
Example:
UPDATE STUDENTSET SNAME = ‘Vignesh’, MAJOR = ‘IS’WHERE snum = 1;
Updating the contents of a table.
i)updating all rows
UPDATE STUDENT SET MAJOR='ISE';

ii)updating selected records.

Syntax: Update <table name> set <col>=<exp>, <col>=<exp> where <condition>;
mysql> UPDATE STUDENT SET MAJOR='ECE' WHERE SNUM=1;
DBMS LAB
EXPERIMENT-3

AKSHAY HARISH
20191ISE0008
4ISE1
 Experiment No.3: To Study and Implement SQL Constraints

1. To create a new table Faculty with FID auto_increment constraint

mysql> CREATE TABLE FACULTY

-> ( FID INT PRIMARY KEY AUTO_INCREMENT,
-> FNAME VARCHAR(20) NOT NULL,
-> ADDRESS VARCHAR(20),
-> DEPTID INT);
Query OK, 0 rows affected (0.13 sec)

2. To create a relationship table called COURSE with ON DELETE CASCADE Referential

Integrity Constraint.

mysql> CREATE TABLE COURSE (

-> CNAME VARCHAR(10) PRIMARY KEY,
-> MEETS_AT VARCHAR(10),
-> ROOM VARCHAR(5),
-> FID INTEGER,
-> FOREIGN KEY(FID) REFERENCES FACULTY(FID) ON DELETE CASCADE);
3. To CREATE a Many to Many relationship table “ENROLL” between STUDENT and COURSE
relations with ON DELETE CASCADE
mysql> CREATE TABLE ENROLL(
-> SNUM INTEGER,CNAME VARCHAR(10),
-> PRIMARY KEY(SNUM,CNAME),
-> FOREIGN KEY(SNUM) REFERENCES STUDENT(SNUM) ON DELETE CASCADE,
-> FOREIGN KEY(CNAME) REFERENCES COURSE(CNAME) ON DELETE CASCADE);

i)Entity Integrity Constraint: Primary Key Value cannot be NULL and duplicate.
mysql> INSERT INTO STUDENT VALUES (NULL,'CHETHAN','CSE','2000-20-03',4);
ERROR 1048 (23000): Column 'SNUM' cannot be null.
mysql> INSERT INTO STUDENT(SNUM,SNAME,MAJOR,DOB,SEM) VALUES (1001,'CHETHAN','CSE','2000-05-
03',4);
ERROR 1062 (23000): Duplicate entry '1001' for key 'PRIMARY'.

ii)DOMAIN Constraint: incorrect date format(‘YYYY-MM-DD’)

mysql> INSERT INTO STUDENT VALUES (1002,'CHETHAN','CSE','2000-20-03',4);
ERROR 1292 (22007): Incorrect date value: '2000-20-03' for column 'DOB' at row 1
mysql> INSERT INTO COURSE VALUES('PYTHON','10:05','DGL0123',123);
ERROR 1406 (22001): Data too long for column 'ROOM' at row 1

iii)Referential Integrity Constraint:

mysql> INSERT INTO COURSE VALUES('PYTHON','10:05','DGL01',123);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`studentdb`.`course`,
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`FID`) REFERENCES `faculty` (`FID`) ON DELETE CASCADE).
DBMS LAB
EXPERIMENT-4

AKSHAY HARISH
20191ISE0008
4ISE1
Experiment No. 4: To study and implement SQL data retrieval using
SELECT, FROM and WHERE clause.

1.Add the columns 'Fees' & 'Email' to the STUDENT table with default value '30000' &
'[email protected]'.

2. Update the fees & email of students with different values.

3. Display the Average Fees of students department-wise.

4. Find the names of students having fees between 25000 to 30000.

5. Find the names of students having domain 'gmail.com'

6. Display Names of students in CAPITAL Letters.

7. Increase the fees of all students by 10%.

8. Display the details of the student whose email id is missing.(NULL)

9. Display the details of the student whose student name starts with letter R.
10.Delete the first two records of a student table.
DBMS LAB
EXPERIMENT-5

AKSHAY HARISH
20191ISE0008
4ISE1
Experiment No.5: To study and implement different SQL single
row and multiple row functions.

1. SELECT sum(marks) FROM student;

2. SELECT min(Roll_no), max(marks) FROM student;
3. SELECT ASCII('a');
4. SELECT ASCII('A');
5. SELECT ASCII('1');
6. SELECT ASCII('ABC');
1. SELECT LOWER('STRING FUNCTION');
2. SELECT UPPER('string function') ;
3. SELECT LENGTH('STRING FUNCTION');
4. SELECT LEFT('STRING FUNCTION', 6);
5. SELECT REVERSE('STRING FUNCTION') ;
1. SELECT SUBSTRING('STRING FUNCTION', 1, 6);
2. SELECT SUBSTRING('STRING FUNCTION', 8, 8);
3. SELECT NOW();
4. SELECT SYSDATE();
DBMS LAB
EXPERIMENT-6

AKSHAY HARISH
20191ISE0008
4ISE1
Experiment No.6: To study and implement aggregating Data using Group By
Clause, HAVING clause and sort data using Order By clause.

CREATE ALL THE TABLES

BRANCH TABLE
CREATE TABLE BRANCH (BR_NAME VARCHAR(20) PRIMARY KEY, BR_CITY VARCHAR(20), ASSETS REAL);

ACCOUNT TABLE
CREATE TABLE ACCOUNT (ACCNO INT PRIMARY KEY, BR_NAME VARCHAR(20), BALANCE REAL, FOREIGN
KEY (BR_NAME) REFERENCES BRANCH (BR_NAME) ON DELETE CASCADE);

CUSTOMER TABLE
CREATE TABLE CUSTOMER (CUST_NAME VARCHAR(20) PRIMARY KEY, CUST_STREET VARCHAR (20),
CUST_CITY VARCHAR (20));

DEPOSITOR TABLE
CREATE TABLE DEPOSITOR (CUST_NAME VARCHAR (20), ACCNO INT, PRIMARY KEY (CUST_NAME,
ACCNO), FOREIGN KEY (CUST_NAME) REFERENCES CUSTOMER (CUST_NAME) ON DELETE CASCADE,
FOREIGN KEY (ACCNO) REFERENCES ACCOUNT (ACCNO) ON DELETE CASCADE);

LOAN TABLE
CREATE TABLE LOAN (LOAN_NO INT PRIMARY KEY, BR_NAME VARCHAR (20), AMOUNT REAL, FOREIGN
KEY (BR_NAME) REFERENCES BRANCH (BR_NAME) ON DELETE CASCADE);

BORROWER TABLE
CREATE TABLE BORROWER (CUST_NAME VARCHAR (20), LOAN_NO INT, PRIMARY KEY (CUST_NAME,
LOAN_NO), FOREIGN KEY (CUST_NAME) REFERENCES CUSTOMER (CUST_NAME) ON DELETE CASCADE,
FOREIGN KEY (LOAN_NO) REFERENCES LOAN (LOAN_NO) ON DELETE CASCADE);
INSERT INTO TABLES

INSERT INTO BRANCH VALUES

(‘KORMANGALA’ , ’BENGALURU’ , 20500.3),
(‘SADASHIVANAGAR’ , ’BENGALURU’ , 154329.5),
(‘VITTALNAGAR’ , ’HYDERABAD’ , 350000),
(‘KASTHURINAGAR’ , ’DELHI’ , 125000),
(‘MARUTINAGAR’ , ’HYDERABAD’ , 212351.6),
(‘RAJANKUNTE’ , ’MUMBAI’ , 53535.8);

INSERT INTO ACCOUNT VALUES

(123456 , ’KORMANGALA’ , 5000),
(123457 , ’SADASHIVANAGAR’ , 35000),
(123458 , ’VITTALNAGAR’ , 60000),
(123459 , ’KASTHURINAGAR’ , 255600),
(123460 , ’VITTALNAGAR’ , 37890),
(123461 , ’MARUTINAGAR’ , 20000),

INSERT INTO CUSTOMER VALUES

(‘KAVYA’ , ‘SADASHIVANAGAR’ , ‘BENGALURU’),
(‘ABHAY’ , ‘KAMALANAGAR’ , ‘TUMKUR’),
(‘SHEETAL’ , ‘KASTHURINAGAR’ , ‘BENGALURU’),
(‘KSHAMITHA’ , ‘MARUTILAYOUT’ , ‘TUMKUR’),
(‘LIKITH’ , ‘MADHURANAGAR’ , ‘HYDERABAD’),
(‘SACHIN’ , ‘VITTALNAGAR’ , ‘HYDERABAD’);

INSERT INTO DEPOSITOR VALUES

(‘KAVYA’ , 123457),
(‘ABHAY’ , 123456),
(‘KAVYA’ , 123456),
(‘KSHAMITHA’ , 123458),
(‘KSHAMITHA’ , 123460),
(‘LIKITH’ , 123461),
(‘KAVYA’ , 123462);

INSERT INTO LOAN VALUES

(231 , ’SADASHIVANAGAR’ , 50500.5),
(231 , ’SADASHIVANAGAR’ , 50500.5),
(233 , ’MARUTINAGAR’ , 60300.3),
(234 , ’KASTHURINAGAR’ , 45000.7),
(235 , ’KORMANGALA’ , 25534);

INSERT INTO BORROWER VALUES

(‘KAVYA’ , 231),
(‘KSHAMITHA’ , 232),
(‘ABHAY’ , 235),
(‘LIKITH’ , 234),
(‘SACHITH’ , 233);
1. Find bank accounts with a balance greater than 20000
2. Display results in increasing order of balance
3. Retrieve a list of all bank branch details, ordered by branch city, with each city’s
branches listed in reverse order of assets

4. Find average balance of accounts at Sadashivanagar branch

5. Find the sum of total account balance of any branch.
6. Find the number of branches that currently DONT have loans
7. Find branch names of Bengaluru city
8. Find number of accounts present in each branch
9. Find sum of balance of accounts at each branch

10. Find sum of balance of loan accounts at each branch

11. Find the city of a customer with account number 123456
12. Find branch names without account
13. Find the loan amount borrowed by a customer Abhay
14. Find the branch name and balance of a customer kavya with account number 12345
15. Find the loan amount taken by each customer
16. Display the loan details of a customer Kavya
17. Find the city of branch with loan number 100
18. Find the number of accounts of each customer
19. Find customers with an account but not a loan
20. Find all cities with more than two customers living in the city

21. Find all the customers who have at least two accounts at the main branch.
22. Demonstrate how you delete all account tuples at every branch located in a specific
city.
23. Find all the customers who have an account at all the branches located in a specific
city.
24. Find all the customers with more than one loan
25. Find branches with assets greater than all branches in Bangalore
DBMS LAB
EXPERIMENT-7

AKSHAY HARISH
20191ISE0008
4ISE1
Experiment No.7: To Study and Implement different types of Set
Operation in SQL.

Illustration of all Set Operations:

1.UNION OPERATION: Display all the cities of branches and customer.

SELECT br_city FROM branch UNION SELECT cust_city FROM customer;

2. INTERSECTION OPERATION: Find the branch name of the branch table that currently
have loans

SELECT br_name FROM branch WHERE br_name IN(SELECT br_name FROM loan);
3.MINUS/DIFFERENCE OPEARATION: Find the number of branches that currently DONT
have loans

SELECT br_name FROM branch WHERE br_name NOT IN(SELECT br_name FROM loan);

4.CARTESIAN PRODUCT:

select * from branch cross join customer;

DBMS LAB
EXPERIMENT-8

AKSHAY HARISH
20191ISE0008
4ISE1
Experiment No.8: To Study and Implement different types of Joins in SQL.

1. EQUI JOIN OPERATION: Display employee and their respective branch where employee
department number is same as department’s department id.

SELECT EMP_NAME, DEPT_NAME

FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPT_NUM=D.DEPT_ID;

2. JOIN WITH NOT EQUALITY OPERATOR(NON EQUI JOIN): Display employee and their
respective Department where employee department number is not same as department’s
department id.

SELECT EMP_NAME, DEPT_NAME FROM EMPLOYEE E JOIN DEPARTMENT D ON

E.DEPT_NUM<>D.DEPT_ID;

3. EQUI JOIN WITH SPECIFIED CONDITION: Display employee and their respective
Department where employee department number is not same as department’s department
id and Department name is MARKETING

SELECT EMP_NAME, DEPT_NAME

FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPT_NUM<>D.DEPT_ID WHERE

D.DEPT_NAME='MARKETING';
OUTER JOIN:

1. LEFT OUTER JOIN OR LEFT JOIN: Join Employee and department tables with reference to
employee table

SELECT * FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.DEPT_NUM=D.DEPT_ID;

2. RIGHT OUTER JOIN OR RIGHT JOIN: Join Employee and department tables with reference
to department table

SELECT * FROM EMPLOYEE E RIGHT JOIN DEPARTMENT D ON E.DEPT_NUM=D.DEPT_ID;

3. COMBINATION OF SET AND JOIN OPERATIONS

SELECT *FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.DEPT_NUM=D.DEPT_ID

UNION

SELECT *FROM EMPLOYEE E RIGHT JOIN DEPARTMENT D ON E.DEPT_NUM=D.DEPT_ID;

4.NATURAL JOIN Operation:

NOTE: First rename the column

ALTER TABLE employee Change dept_num dept_id int;

SELECT * FROM DEPARTMENT NATURAL JOIN EMPLOYEE;

DBMS LAB
EXPERIMENT-9

AKSHAY HARISH
20191ISE0008
4ISE1
Experiment No.9: To study and implement Sub queries/Nested queries,
Correlated nested queries in SQL.

1. Find the names of all Juniors (level = JR) who have enrolled in a Class taught by
Prof.Narasimha.

mysql> SELECT DISTINCT S.SNAME

FROM STUDENT S, COURSE C, ENROLLED E, FACULTY F

WHERE S.SID=E.SID AND E.CNAME=C.CNAME AND F.FID=C.FID

AND F.FNAME='NARASIMHA' AND S.LEVEL='JR';

2. Find the names of all courses that either meet in room KG04 or have five or more
Students enrolled.

mysql> SELECT C.CNAME

FROM COURSE C

WHERE C.ROOM='KG04' OR C.CNAME IN (SELECT E.CNAME

FROM ENROLLED E

GROUP BY E.CNAME

HAVING COUNT (*)>=5);

3. Find the names of all students who have enrolled in two courses that meet at the same
time

mysql> SELECT DISTINCT S.*

FROM STUDENT S

WHERE S.SID IN

( SELECT E1.SID

FROM ENROLLED E1, ENROLLED E2, COURSE C1, COURSE C2

WHERE E1.SID = E2.SID AND E1.CNAME <> E2.CNAME AND

E1.CNAME = C1.CNAME AND E2.CNAME = C2.CNAME AND C1.MEETS_AT = C2.MEETS_AT);

4. Find the names of students enrolled in the maximum number of courses.

SELECT DISTINCT S.SNAME

FROM STUDENT S

WHERE S.SID IN( SELECT E.SID

FROM ENROLLED E

GROUP BY E.SID

HAVING COUNT(*)>=ALL( SELECT COUNT(*)

FROM ENROLLED E2

GROUP BY E2.SID));

5. Find the names of faculty members for whom the combined enrolment of the courses
that they teach is less than five.

mysql> SELECT DISTINCT F.FNAME

FROM FACULTY F

WHERE F.FID IN (SELECT C.FID

FROM COURSE C, ENROLLED E

WHERE C.CNAME=E.CNAME

GROUP BY C.FID

HAVING COUNT(*)<5);
DBMS LAB
EXPERIMENT-10

AKSHAY HARISH
20191ISE0008
4ISE1
mysql> use studentdb;
Database changed
mysql> create view student_detail as select * from student;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from student_detail;

+-----+-------+-------+------------+------+-------+-----------------+-------+-------+
| sid | sname | major | dob | sem | marks | email | fees | level |
+-----+-------+-------+------------+------+-------+-----------------+-------+-------+
| 1 | JACK | ISE | 2001-12-04 | 4 | 67 | [email protected] | 55000 | JR |
| 2 | KARAN | ISE | 2001-05-08 | 4 | 88 | [email protected] | 75000 | JR |
| 3 | ROHAN | CSE | 2001-01-05 | 4 | 95 | [email protected] | 55000 | JR |
| 4 | RIYA | CSE | 2001-03-10 | 4 | 85 | [email protected] | 75000 | SR |
| 5 | RAM | CSE | 2001-09-06 | 4 | 87 | [email protected] | 75000 | SR |
+-----+-------+-------+------------+------+-------+-----------------+-------+-------+
5 rows in set (0.01 sec)

mysql> select sid,sname from student_detail where major='cse';

+-----+-------+
| sid | sname |
+-----+-------+
| 3 | ROHAN |
| 4 | RIYA |
| 5 | RAM |
+-----+-------+
3 rows in set (0.01 sec)

mysql> create view studentcount as select major,count(*) as 'NO.OF STUDENTS' from

student group by major;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from studentcount;

+-------+----------------+
| major | NO.OF STUDENTS |
+-------+----------------+
| ISE | 2 |
| CSE | 3 |
+-------+----------------+
2 rows in set (0.01 sec)

mysql> drop view student_detail;

Query OK, 0 rows affected (0.01 sec)

mysql> drop view studentcount;

Query OK, 0 rows affected (0.01 sec)
DBMS LAB
EXPERIMENT-11

AKSHAY HARISH
20191ISE0008
4ISE1
Experiment No.11: To study and implement Functions and Procedures.
Step 1: Change the delimiter (i.e., terminating character) of SQL statement from semicolon
(;) to something else (e.g., //) So that you can distinguish between the semicolon of the SQL
statements in the procedure and the terminating character of the procedure definition.
Step 2: 1.Define a procedure called updateSalary which takes as input a department
number. 2.The body of the procedure is an SQL command to update the totalsalary column
of the deptsal table. 3.Terminate the procedure definition using the delimiter you had
defined in step 1 (//)
Step 3: Change the delimiter back to semicolon (;)
mysql> delimeter ;
Step 4: Call the procedure to update the totalsalary for each department
Step 5: Show the updated total salary in the deptsal table
Example using Cursors:
•The previous procedure updates one row in deptsal table based on input parameter

•Suppose we want to update all the rows in deptsal simultaneously

•First, let’s reset the totalsalary in deptsal to zero
Functions:
•Functions are declared using the following syntax:
Create function <function-name> (param_spec1, ..., param_speck)
returns <return_type>
[not] deterministic
Begin
--execution code
end;
where param_spec is:
[in | out | in out] <param_name> <param_type>

Example of Functions:
DBMS LAB
EXPERIMENT-12

AKSHAY HARISH
20191ISE0008
4ISE1
Experiment No.12: To study and implement SQL Triggers

1.Create a trigger to update the total salary of a department when a new employee is hired:

2. Create a trigger to update the total salary of a department when an employee tuple is
modified:
3. Create a trigger to update the total salary of a department when an employee tuple is
deleted: