DBMS LAB WITH MINI PROJECT 18CSL58

THE OXFORD COLLEGE OF ENGINEERING

HosurRoad, Bommanahalli, Bengaluru-560068
2020-2021

DEPARTMENT OF INFORMATION SCIENCE AND ENGINEERING

[Updated as on 15/08/20]

SUBJECT NAME/CODE : DBMS LABORATORY WITH MINI PROJECT

(18CSL58)

SCHEME : 2018 Scheme

SEMESTER :V

FACULTY NAME : Sandya Rani V, Jeevitha M

The Oxford College of Engineering Page 0

DBMS LAB WITH MINI PROJECT 18CSL58

Table of contents
SL TITLE PAGE
No. NO.

I Introduction to SQL 3

II List of Programs as per VTU Syllabus [2017 Scheme] 10

Part A: SQL Programming

Consider the following schema for a Library Database: BOOK(Book_id, Title,
Publisher_Name, Pub_Year) BOOK_AUTHORS(Book_id, Author_Name)
PUBLISHER(Name, Address, Phone) BOOK_COPIES(Book_id, Branch_id, No-
of_Copies) BOOK_LENDING(Book_id, Branch_id, Card_No, Date_Out, Due_Date)
LIBRARY_BRANCH(Branch_id, Branch_Name, Address) Write SQL queries to 1.
A Retrieve details of all books in the library – id, title, name of publisher, authors, number
10
of copies in each branch, etc. 2. Get the particulars of borrowers who have borrowed
more than 3 books, but from Jan 2017 to Jun 2017. 3. Delete a book in BOOK table.
Update the contents of other tables to reflect this data manipulation operation. 4.
Partition the BOOK table based on year of publication. Demonstrate its working with a
simple query. 5. Create a view of all books and its number of copies that are currently
available in the Library.

Consider the following schema for Order Database: SALESMAN(Salesman_id, Name,

City, Commission) CUSTOMER(Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS(Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id) Write SQL
queries to 1. Count the customers with grades above Bangalore’s average. 2. Find the
B
name and numbers of all salesman who had more than one customer. 3. List all the 18
salesman and indicate those who have and don’t have customers in their cities (Use
UNION operation.) 4. Create a view that finds the salesman who has the customer with
the highest order of a day. 5. Demonstrate the DELETE operation by removing
salesman with id 1000. All his orders must also be deleted.

Consider the schema for Movie Database: ACTOR(Act_id, Act_Name, Act_Gender)

DIRECTOR(Dir_id, Dir_Name, Dir_Phone) MOVIES(Mov_id, Mov_Title, Mov_Year,
Mov_Lang, Dir_id) MOVIE_CAST(Act_id, Mov_id, Role) RATING(Mov_id, 24
Rev_Stars) Write SQL queries to 1. List the titles of all movies directed by ‘Hitchcock’.
2. Find the movie names where one or more actors acted in two or more movies. 3. List
C all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN
operation). 4. Find the title of movies and number of stars for each movie that has at
least one rating and find the highest number of stars that movie received. Sort the result
by movie title. 5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.

The Oxford College of Engineering Page 1

DBMS LAB WITH MINI PROJECT 18CSL58

Consider the schema for College Database: STUDENT (USN, SName, Address, Phone,
Gender) SEMSEC (SSID, Sem, Sec) CLASS(USN, SSID) SUBJECT(Subcode, Title,
Sem, Credits) IAMARKS(USN, Subcode, SSID, Test1, Test2, Test3, FinalIA) Write
SQL queries to 1. List all the student details studying in fourth semester ‘C’ section. 2.
Compute the total number of male and female students in each semester and in each
D
section. 3. Create a view of Test1 marks of student USN ‘1BI15CS101’ in all subjects. 32
4. Calculate the FinalIA (average of best two test marks) and update the corresponding
table for all students. 5. Categorize students based on the following criterion: If FinalIA
= 17 to 20 then CAT = ‘Outstanding’ If FinalIA = 12 to 16 then CAT = ‘Average’ If
FinalIA< 12 then CAT = ‘Weak’ Give these details only for 8th semester A, B, and C
section students.

Consider the schema for Company Database: EMPLOYEE (SSN, Name, Address, Sex,
Salary, SuperSSN, DNo) DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)
DLOCATION (DNo,DLoc) PROJECT(PNo, PName, PLocation, DNo)
WORKS_ON(SSN, PNo, Hours) Write SQL queries to 1. Make a list of all project
numbers for projects that involve an employee whose last name is ‘Scott’, either as a
worker or as a manager of the department that controls the project. 2. Show the resulting
E
salaries if every employee working on the ‘IoT’ project is given a 10 percent raise. 3. 43
Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as
the maximum salary, the minimum salary, and the average salary in this department 4.
Retrieve the name of each employee who works on all the projects controlled by
department number 5 (use NOT EXISTS operator). 5. For each department that has
more than five employees, retrieve the department number and the number of its
employees who are making more than Rs. 6, 00,000.

Part B: Mini project

● For any problem selected, write the ER Diagram, apply ER-mapping rules,
normalize the relations, and follow the application development process. 52
III ● Make sure that the application should have five or more tables, at least one
trigger and one stored procedure, using suitable frontend tool.
● Indicative areas include; health care, education, industry, transport, supply
chain, etc.
Additional exercises (Updated on 2018-2019) 55
IV

V Additional exercises (Updated on 2019-2020) 58

VI Case Studies 62

Viva questions and answers 67

VII

VIII References 80

The Oxford College of Engineering Page 2

DBMS LAB WITH MINI PROJECT 18CSL58

I. Introduction to SQL

DATA DEFINITION, CONSTRAINTS, AND SCHEMA CHANGES

Used to CREATE, ALTER, and DROP the descriptions of the database tables (relations)
Data Definition in SQL
CREATE, ALTERand DROP
table…………………………………….……relation
row……………………………………..…….tuple
column………………………………….……attribute
DATA TYPES
● Numeric: NUMBER, NUMBER(s,p), INTEGER, INT, FLOAT, DECIMAL
● Character: CHAR(n), VARCHAR(n), VARCHAR2(n), CHAR VARYING(n)
● Bit String: BLOB, CLOB
● Boolean: true, false, and null
● Date and Time: DATE (YYYY-MM-DD) TIME( HH:MM:SS)
● Timestamp: DATE + TIME
● USER Defined types

CREATE SCHEMA
Specifies a new database schema by giving it a name

Ex: CREATE SCHEMA COMPANY AUTHORIZATION Jsmith;

CREATE TABLE
● Specifies a new base relation by giving it a name, and specifying each of its attributes and
their data types
Syntax of CREATE Command:
CREATE TABLE <table name>( <Attribute A1><Data Type D1> [<Constarints>],
<Attribute A2><Data Type D2> [<Constarints>],
…….
<Attribute An><Data Type Dn> [<Constarints>],

The Oxford College of Engineering Page 3

DBMS LAB WITH MINI PROJECT 18CSL58

[<integrity-constraint1>, <integrity-constraint k> ] );

DROP TABLE
● Used to remove a relation (base table) and its definition.
● The relation can no longer be used in queries, updates, or any other commands since its
description no longer exists

Example: DROP TABLE DEPENDENT;

ALTER TABLE:
● Used to add an attribute to/from one of the base relations drop constraint -- The new
attribute will have NULLs in all the tuples of the relation right after the command is
executed; hence, the NOT NULL constraint is not allowed for such an attribute.
Example: ALTER TABLE EMPLOYEE ADD JOB VARCHAR2(12);
● The database users must still enter a value for the new attribute JOB for each
EMPLOYEE tuple. This can be done using the UPDATE command.

DROP A COLUMN (AN ATTRIBUTE)

● ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE; All

constraints and views that reference the column are dropped automatically, along with the
column. ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS RESTRICT;
Successful if no views or constraints reference the column. ALTER TABLE
COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT;
● ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT
“333445555”;

BASIC QUERIES IN SQL

● SQL has one basic statement for retrieving information from a database; the SLELECT
statement
● This is not the same as the SELECT operation of the relational algebra
● Important distinction between SQL and the formal relational model;
● SQL allows a table (relation) to have two or more tuples that are identical in all their
attribute values

The Oxford College of Engineering Page 4

DBMS LAB WITH MINI PROJECT 18CSL58

● Hence, an SQL relation (table) is a multi-set (sometimes called a bag) of tuples; it is not a
set of tuples
● SQL relations can be constrained to be sets by using the CREATE UNIQUE INDEX
command, or by using the DISTINCT option
● Basic form of the SQL SELECT statement is called a mapping of a SELECT-FROM-
WHERE block
SELECT <attribute list> FROM <table list> WHERE <condition>
● <attribute list> is a list of attribute names whose values are to be retrieved by the query
● <table list > is a list of the relation names required to process the query
● <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved
by the query

SIMPLE SQL QUERIES

Basic SQL queries correspond to using the following operations of the relational algebra:
SELECT
PROJECT
JOIN
All subsequent examples uses COMPANY database as shown below:

ALIASES, * AND DISTINCT, EMPTY WHERE-CLAUSE

● In SQL, we can use the same name for two (or more) attributes as long as the attributes
are in different relations
● A query that refers to two or more attributes with the same name must qualify the
attribute name with the relation name by prefixing the relation name to the attribute name
Example: EMPLOYEE.LNAME, DEPARTMENT.DNAME
● Some queries need to refer to the same relation twice. In this case, aliases are given to the
relation name

UNSPECIFIED WHERE-clause
A missing WHERE-clause indicates no condition; hence, all tuples of the relations in the
FROM-clause are selected. This is equivalent to the condition WHERE TRUE
Example:

USE OF *

The Oxford College of Engineering Page 5

DBMS LAB WITH MINI PROJECT 18CSL58

To retrieve all the attribute values of the selected tuples, a * is used, which stands for all the
attributes

USE OF DISTINCT
SQL does not treat a relation as a set; duplicate tuples can appear. To eliminate duplicate
tuples in a query result, the keyword DISTINCT is used

SET OPERATIONS
SQL has directly incorporated some set operations such as union operation (UNION), set
difference (MINUS) and intersection (INTERSECT) operations. The resulting relations of these
set operations are sets of tuples; duplicate tuples are eliminated from the result. The set
operations apply only to union compatible relations; the two relations must have the same
attributes and the attributes must appear in the same order

NESTING OF QUERIES

A complete SELECT query, called a nested query, can be specified within the WHERE-
clause of another query, called the outer query. Many of the previous queries can be specified in
an alternative form using nesting

CORRELATED NESTED QUERIES

If a condition in the WHERE-clause of a nested query references an attribute of a relation
declared in the outer query, the two queries are said to be correlated. The result of a correlated
nested query is different for each tuple (or combination of tuples) of the relation(s) the outer
query

THE EXISTS FUNCTION

EXISTS is used to check whether the result of a correlated nested query is empty
(contains no tuples) or not

EXPLICIT SETS
It is also possible to use an explicit (enumerated) set of values in the WHERE-clause rather than
a nested query

NULLS IN SQL QUERIES

The Oxford College of Engineering Page 6

DBMS LAB WITH MINI PROJECT 18CSL58

SQL allows queries that check if a value is NULL (missing or undefined or not applicable). SQL
uses IS or IS NOT to compare NULLs because it considers each NULL value distinct from other
NULL values, so equality comparison is not appropriate.

AGGREGATE FUNCTIONS
Include COUNT, SUM, MAX, MIN, and AVG

GROUPING
● In many cases, we want to apply the aggregate functions to subgroups of tuples in a
relation
● Each subgroup of tuples consists of the set of tuples that have the same value for the
grouping attribute(s)
● The function is applied to each subgroup independently
● SQL has a GROUP BY-clause for specifying the grouping attributes, which must also
appear in the SELECT-clause

THE HAVING-CLAUSE
Sometimes we want to retrieve the values of these functions for only those groups that satisfy
certain conditions. The HAVING-clause is used for specifying a selection condition on groups
(rather than on individual tuples)

SUBSTRING COMPARISON
The LIKE comparison operator is used to compare partial strings. Two reserved
characters are used: '%' (or '*' in some implementations) replaces an arbitrary number of
characters, and '_' replaces a single arbitrary character.

ARITHMETIC OPERATIONS

The standard arithmetic operators '+', '-'. '*', and '/' (for addition, subtraction, multiplication, and
division, respectively) can be applied to numeric values in an SQL query result

ORDER BY

The ORDER BYclause is used to sort the tuples in a query result based on the values of some
attribute(s)

The Oxford College of Engineering Page 7

DBMS LAB WITH MINI PROJECT 18CSL58

The default order is in ascending order of values. We can specify the keyword DESCif
we want a descending order; the keyword ASCcan be used to explicitly specify ascending order,
even though it is the default

SPECIFYING UPDATES IN SQL

There are three SQL commands to modify the database: INSERT, DELETE, and UPDATE.

INSERT

● In its simplest form, it is used to add one or more tuples to a relation

● Attribute values should be listed in the same order as the attributes were specified in the
CREATE TABLE command

DELETE
● Removes tuples from a relation. Includes a WHERE-clause to select the tuples to be
deleted
● Referential integrity should be enforced
● Tuples are deleted from only one table at a time (unless CASCADE is specified on a
referential integrity constraint)
● A missing WHERE-clause specifies that all tuples in the relation are to be deleted; the
table then becomes an empty table
● The number of tuples deleted depends on the number of tuples in the relation that satisfy
the WHERE-clause

UPDATE
● Used to modify attribute values of one or more selected tuples
● A WHERE-clause selects the tuples to be modified
● An additional SET-clause specifies the attributes to be modified and their new values
● Each command modifies tuples in the same relation
● Referential integrity should be enforced

SQL TRIGGERS
● Objective: to monitor a database and take initiate action when a condition occurs

The Oxford College of Engineering Page 8

DBMS LAB WITH MINI PROJECT 18CSL58

● Triggers are nothing but the procedures/functions that involve actions and fired/executed
automatically whenever an event occurs such as an insert, delete, or update operation or
pressing a button or when mouse button is clicked
VIEWS IN SQL
● A view is a single virtual table that is derived from other tables. The other tables could be
base tables or previously defined view.
● Allows for limited update operations Since the table may not physically be stored
● Allows full query operations
● A convenience for expressing certain operations
● A view does not necessarily exist in physical form, which limits the possible update
operations that can be applied to views.

II. List of Programs as per VTU Syllabus [2015 Scheme]

PART A: SQL PROGRAMMING

A. Consider the following schema for a Library Database:

BOOK (Book_id, Title, Publisher_Name, Pub_Year)

BOOK_AUTHORS (Book_id, Author_Name)
PUBLISHER (Name, Address, Phone)
BOOK_COPIES (Book_id, Branch_id, No-of_Copies)
BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date)

The Oxford College of Engineering Page 9

DBMS LAB WITH MINI PROJECT 18CSL58

LIBRARY_BRANCH (Branch_id, Branch_Name, Address)

Write SQL queries to

1. Retrieve details of all books in the library – id, title, name of publisher, authors,
number of copies in each branch, etc.
2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan
2017 to Jun 2017
3. Delete a book in BOOK table. Update the contents of other tables to reflect this data
manipulation operation.
4. Partition the BOOK table based on year of publication. Demonstrate its working with a
simple query.
5. Create a view of all books and its number of copies that are currently available in the
Library.

Solution:
Entity-Relationship Diagram

Schema Diagram
Book

Book_i Titl Pub_Yea Publisher_Nam

d e r e

Book_Authors

The Oxford College of Engineering Page 10

DBMS LAB WITH MINI PROJECT 18CSL58

Book_i Author_nam
d e

Publisher

Nam Phone_n Addres

e o s

Book_Copies

Book_i Branch_i No_of_Copie

d d s

Book_Lending

Book_id Branch_id Card_n Date_out Due_date

o

Library_Branch

Branch_id Addres Branch_name

s

Table Creation

1. CREATE TABLE PUBLISHER

(NAME VARCHAR(20) PRIMARY KEY,
PHONE INTEGER,

The Oxford College of Engineering Page 11

DBMS LAB WITH MINI PROJECT 18CSL58

ADDRESS VARCHAR(20));

2. CREATE TABLE BOOK

(BOOK_ID INTEGER PRIMARY KEY,
TITLE VARCHAR (20),
PUB_YEAR VARCHAR (20),
PUBLISHER_NAME VARCHAR(20),
FOREIGN KEY(PUBLISHER_NAME) REFERENCES PUBLISHER (NAME) ON DELETE
CASCADE);

3. CREATE TABLE BOOK_AUTHORS

(AUTHOR_NAME VARCHAR (20),
BOOK_ID INTEGER,
FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
PRIMARY KEY (BOOK_ID, AUTHOR_NAME));

4. CREATE TABLE LIBRARY_BRANCH

(BRANCH_ID INTEGER PRIMARY KEY,
BRANCH_NAME VARCHAR(50),
ADDRESS VARCHAR(50));

5. CREATE TABLE BOOK_COPIES(

NO_OF_COPIES INTEGER,
BOOK_ID INTEGER,
BRANCH_ID INTEGER,
FOREIGN KEY(BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE
CASCADE,
FOREIGN KEY(BRANCH_ID) REFERENCES LIBRARY_BRANCH(BRANCH_ID) ON
DELETE CASCADE,
PRIMARY KEY (BOOK_ID, BRANCH_ID));

6. CREATE TABLE CARD

(CARD_NO INTEGER PRIMARY KEY);

7. CREATE TABLE BOOK_LENDING

(DATE_OUT DATE,
DUE_DATE DATE,
BOOK_ID INTEGER,
BRANCH_ID INTEGER,
FOREIGN KEY(BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE
CASCADE,
FOREIGN KEY(BRANCH_ID) REFERENCES LIBRARY_BRANCH(BRANCH_ID) ON
DELETE CASCADE,
CARD_NO INTEGER,

The Oxford College of Engineering Page 12

DBMS LAB WITH MINI PROJECT 18CSL58

FOREIGN KEY(CARD_NO) REFERENCES CARD (CARD_NO) ON DELETE

CASCADE,
PRIMARY KEY (BOOK_ID, BRANCH_ID, CARD_NO));

Table Descriptions

DESC PUBLISHER;

DESC BOOK;

DESC BOOK_AUTHORS;

DESC LIBRARY_BRANCH;

DESC BOOK_COPIES;

DESC CARD;

The Oxford College of Engineering Page 13

DBMS LAB WITH MINI PROJECT 18CSL58

DESC BOOK_LENDING;

Insertion of Values to Tables

INSERT INTO PUBLISHER VALUES (‘MCGRAW-HILL’, ‘BANGALORE’, 9989076587);

INSERT INTO PUBLISHER VALUES (‘PEARSON’, 9889076565, ‘NEWDELHI’);
INSERT INTO PUBLISHER VALUES (‘RANDOM HOUSE’, 7455679345, ‘HYDRABAD’);
INSERT INTO PUBLISHER VALUES (‘HACHETTE LIVRE’, 8970862340, ‘CHENAI’);
INSERT INTO PUBLISHER VALUES (‘GRUPO PLANETA’, 7756120238, ‘BANGALORE’);

INSERT INTO BOOK VALUES (1,’DBMS’,’JAN-2017’, ‘MCGRAW-HILL’);

INSERT INTO BOOK VALUES (2,’ADBMS’,’JUN-2016’, ‘MCGRAW-HILL’);
INSERT INTO BOOK VALUES (3,’CN’,’SEP-2016’, ‘PEARSON’);
INSERT INTO BOOK VALUES (4,’CG’,’SEP-2015’, ‘GRUPO PLANETA’);
INSERT INTO BOOK VALUES (5,’OS’,’MAY-2016’, ‘PEARSON’);

INSERT INTO BOOK_AUTHORS VALUES (’NAVATHE’, 1);

INSERT INTO BOOK_AUTHORS VALUES (’NAVATHE’, 2);
INSERT INTO BOOK_AUTHORS VALUES (’TANENBAUM’, 3);
INSERT INTO BOOK_AUTHORS VALUES (’EDWARD ANGEL’, 4);
INSERT INTO BOOK_AUTHORS VALUES (’GALVIN’, 5);

INSERT INTO LIBRARY_BRANCH VALUES (10,’RR NAGAR’,’BANGALORE’);

INSERT INTO LIBRARY_BRANCH VALUES (11,’RNSIT’,’BANGALORE’);
INSERT INTO LIBRARY_BRANCH VALUES (12,’RAJAJI NAGAR’, ’BANGALORE’);
INSERT INTO LIBRARY_BRANCH VALUES (13,’NITTE’,’MANGALORE’);
INSERT INTO LIBRARY_BRANCH VALUES (14,’MANIPAL’,’UDUPI’);

INSERT INTO BOOK_COPIES VALUES (10, 1, 10);

INSERT INTO BOOK_COPIES VALUES (5, 1, 11);
INSERT INTO BOOK_COPIES VALUES (2, 2, 12);
INSERT INTO BOOK_COPIES VALUES (5, 2, 13);
INSERT INTO BOOK_COPIES VALUES (7, 3, 14);
INSERT INTO BOOK_COPIES VALUES (1, 5, 10);
INSERT INTO BOOK_COPIES VALUES (3, 4, 11);

The Oxford College of Engineering Page 14

DBMS LAB WITH MINI PROJECT 18CSL58

INSERT INTO CARD VALUES (100);

INSERT INTO CARD VALUES (101);
INSERT INTO CARD VALUES (102);
INSERT INTO CARD VALUES (103);
INSERT INTO CARD VALUES (104);

INSERT INTO BOOK_LENDING VALUES (’01-JAN-17’,’01-JUN-17’, 1, 10, 101);

INSERT INTO BOOK_LENDING VALUES (’11-JAN-17’,’11-MAR-17’, 3, 14, 101);
INSERT INTO BOOK_LENDING VALUES (’21-FEB-17’,’21-APR-17’, 2, 13, 101);
INSERT INTO BOOK_LENDING VALUES (’15-MAR-17’,’15-JUL-17’, 4, 11, 101);
INSERT INTO BOOK_LENDING VALUES (‘12-APR-17’,’12-MAY-17’, 1, 11, 104);
SELECT * FROM PUBLISHER;

SELECT * FROM BOOK;

SELECT * FROM BOOK_AUTHORS;

SELECT * FROM LIBRARY_BRANCH;

The Oxford College of Engineering Page 15

DBMS LAB WITH MINI PROJECT 18CSL58

SELECT * FROM BOOK_COPIES;

SELECT * FROM CARD;

SELECT * FROM BOOK_LENDING;

Queries:

1. Retrieve details of all books in the library – id, title, name of publisher, authors,
number of copies in each branch, etc.

SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME,

C.NO_OF_COPIES, L.BRANCH_ID
FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L
WHERE B.BOOK_ID=A.BOOK_ID
AND B.BOOK_ID=C.BOOK_ID
AND L.BRANCH_ID=C.BRANCH_ID;

The Oxford College of Engineering Page 16

DBMS LAB WITH MINI PROJECT 18CSL58

2. Get the particulars of borrowers who have borrowed more than 3 books, but from
Jan 2017 to Jun 2017.

SELECT CARD_NO
FROM BOOK_LENDING
WHERE DATE_OUT BETWEEN ’01-JAN-2017’ AND ’01-JUL-2017’
GROUP BY CARD_NO
HAVING COUNT (*)>3;

3. Delete a book in BOOK table. Update the contents of other tables to reflect this data
manipulation operation.

DELETE FROM BOOK

WHERE BOOK_ID=3;

4. Partition the BOOK table based on year of publication. Demonstrate its working with a
simple query.

CREATE VIEW V_PUBLICATION AS

SELECT PUB_YEAR
FROM BOOK;

The Oxford College of Engineering Page 17

DBMS LAB WITH MINI PROJECT 18CSL58

5. Create a view of all books and its number of copies that are currently available in the
Library.

CREATE VIEW V_BOOKS AS

SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
FROM BOOK B, BOOK_COPIES C, LIBRARY_BRANCH L
WHERE B.BOOK_ID=C.BOOK_ID
AND C.BRANCH_ID=L.BRANCH_ID;

The Oxford College of Engineering Page 18

DBMS LAB WITH MINI PROJECT 18CSL58

B. Consider the following schema for Order Database:

SALESMAN (Salesman_id, Name, City, Commission)

CUSTOMER (Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)
Write SQL queries to
1. Count the customers with grades above Bangalore’s average.
2. Find the name and numbers of all salesmen who had more than one customer.
3. List all salesmen and indicate those who have and don’t have customers in their cities
(Use UNION operation.)
4. Create a view that finds the salesman who has the customer with the highest order of a
day.
5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders
must also be deleted.

Solution: Diagram bundle so there are three entities 3 tables seems small customer
salesman has salesman ID salesmen ID as primary key name city and Commission mean
city Commission salesman ID

Entity-Relationship Diagram

The Oxford College of Engineering Page 19

DBMS LAB WITH MINI PROJECT 18CSL58

The Oxford College of Engineering Page 20

DBMS LAB WITH MINI PROJECT 18CSL58

Schema Diagram

Salesman

Salesman_i Nam Cit Commissio

d e y n

Customer

Customer_i Cust_Nam Cit Grad Salesman_i

d e y e d

Orders
Ord_N Purchase_Am Ord Date Customer_i Salesman_i
o t d d

Table Creation

1. CREATE TABLE SALESMAN

(SALESMAN_ID INT,
NAME VARCHAR2 (20),
CITY VARCHAR2 (20),
COMMISSION VARCHAR2 (20),
PRIMARY KEY (SALESMAN_ID));

2. CREATE TABLE CUSTOMER1

(CUSTOMER_ID INT,
CUST_NAME VARCHAR2 (20),
CITY VARCHAR2 (20),
GRADE NUMBER (3),
SALESMAN_ID INT,
PRIMARY KEY (CUSTOMER_ID),
FOREIGN KEY(SALESMAN_ID) REFERENCES SALESMAN (SALESMAN_ID) ON
DELETE SET NULL);

3. CREATE TABLE ORDERS

(ORD_NO INT,
PURCHASE_AMT INT,

The Oxford College of Engineering Page 21

DBMS LAB WITH MINI PROJECT 18CSL58

ORD_DATE DATE,
PRIMARY KEY (ORD_NO),
CUSTOMER_ID INT,
SALESMAN_ID INT,
FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER1 (CUSTOMER_ID) ON
DELETE CASCADE,
FOREIGN KEY(SALESMAN_ID) REFERENCES SALESMAN (SALESMAN_ID) ON
DELETE CASCADE);

Table Descriptions

DESC SALESMAN;

DESC CUSTOMER1;

DESC ORDERS;

Insertion of Values to Tables

INSERT INTO SALESMAN VALUES (1000, ‘JOHN’,’BANGALORE’,’25 %’);

INSERT INTO SALESMAN VALUES (2000, ‘RAVI’,’BANGALORE’,’20 %’);
INSERT INTO SALESMAN VALUES (3000, ‘KUMAR’,’MYSORE’,’15 %’);
INSERT INTO SALESMAN VALUES (4000, ‘SMITH’,’DELHI’,’30 %’);
INSERT INTO SALESMAN VALUES (5000, ‘HARSHA’,’HYDRABAD’,’15 %’);

INSERT INTO CUSTOMER1 VALUES (10, ‘PREETHI’,’BANGALORE’, 100, 1000);

INSERT INTO CUSTOMER1 VALUES (11, ‘VIVEK’,’MANGALORE’, 300, 1000);

The Oxford College of Engineering Page 22

DBMS LAB WITH MINI PROJECT 18CSL58

INSERT INTO CUSTOMER1 VALUES (12, ‘BHASKAR’,’CHENNAI’, 400, 2000);

INSERT INTO CUSTOMER1 VALUES (13, ‘CHETHAN’,’BANGALORE’, 200, 2000);
INSERT INTO CUSTOMER1 VALUES (14, ‘MAMATHA’,’BANGALORE’, 400, 3000);

INSERT INTO ORDERS VALUES (50, 5000, ‘04-MAY-17’, 10, 1000);

INSERT INTO ORDERS VALUES (51, 450, ‘20-JAN-17’, 10, 2000);
INSERT INTO ORDERS VALUES (52, 1000, ‘24-FEB-17’, 13, 2000);
INSERT INTO ORDERS VALUES (53, 3500, ‘13-APR-17’, 14, 3000);
INSERT INTO ORDERS VALUES (54, 550, ‘09-MAR-17’, 12, 2000);

SELECT * FROM SALESMAN;

SELECT * FROM CUSTOMER1;

SELECT * FROM ORDERS;

Queries:

1. Count the customers with grades above Bangalore’s average.

SELECT GRADE, COUNT(DISTINCT CUSTOMER_ID)
FROM CUSTOMER1
GROUP BY GRADE
HAVING GRADE > (SELECT AVG(GRADE)
FROM CUSTOMER1
WHERE CITY='BANGALORE');

The Oxford College of Engineering Page 23

DBMS LAB WITH MINI PROJECT 18CSL58

2. Find the name and numbers of all salesmen who had more than one customer.

SELECT SALESMAN_ID, NAME

FROM SALESMAN A
WHERE 1 < (SELECT COUNT (*)
FROM CUSTOMER1
WHERE SALESMAN_ID=A.SALESMAN_ID);

3. List all salesmen and indicate those who have and don’t have customers in their
cities (Use UNION operation.)

SELECT SALESMAN.SALESMAN_ID, NAME, CUST_NAME, COMMISSION

FROM SALESMAN, CUSTOMER1
WHERE SALESMAN.CITY = CUSTOMER1.CITY
UNION
SELECT SALESMAN_ID, NAME, 'NO MATCH', COMMISSION
FROM SALESMAN
WHERE NOT CITY = ANY
(SELECT CITY
FROM CUSTOMER1)
ORDER BY 2 DESC;

4. Create a view that finds the salesman who has the customer with the highest order
of a day.

CREATE VIEW ELITSALESMAN AS

SELECT B.ORD_DATE, A.SALESMAN_ID, A.NAME
FROM SALESMAN A, ORDERS B
WHERE A.SALESMAN_ID = B.SALESMAN_ID

The Oxford College of Engineering Page 24

DBMS LAB WITH MINI PROJECT 18CSL58

AND B.PURCHASE_AMT=(SELECT MAX (PURCHASE_AMT)

FROM ORDERS C
WHERE C.ORD_DATE = B.ORD_DATE);

5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders
must also be deleted.

Use ON DELETE CASCADE at the end of foreign key definitions while creating child table
orders and then execute the following:

Use ON DELETE SET NULL at the end of foreign key definitions while creating child table
customers and then executes the following:

DELETE FROM SALESMAN

WHERE SALESMAN_ID=1000;

C. Consider the schema for Movie Database:

ACTOR (Act_id, Act_Name, Act_Gender)

DIRECTOR (Dir_id, Dir_Name, Dir_Phone)
MOVIES (Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id)
MOVIE_CAST (Act_id, Mov_id, Role)
RATING (Mov_id, Rev_Stars)

The Oxford College of Engineering Page 25

DBMS LAB WITH MINI PROJECT 18CSL58

Write SQL queries to

1. List the titles of all movies directed by ‘Hitchcock’.
2. Find the movie names where one or more actors acted in two or more movies.
3. List all actors who acted in a movie before 2000 and also in a movie after
2015 (use JOIN operation).
4. Find the title of movies and number of stars for each movie that has at least one
rating and find the highest number of stars that movie received. Sort the result by
movie title.
5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.
Solution:
Entity-Relationship Diagram

Schema Diagram
Actor
Act_id Act_Name Act_Gender

Director
Dir_i Dir_Nam Dir_Phon
d e e

Movies
Mov_i Mov_Titl Mov_Yea Mov_Lan Dir_i
d e r g d

Movie_Cast

The Oxford College of Engineering Page 26

DBMS LAB WITH MINI PROJECT 18CSL58

Act_i Mov_i Rol

d d e

Rating
Mov_i Rev_Star
d s

Table Creation

1. CREATE TABLE ACTOR (

ACT_ID NUMBER (3),
ACT_NAME VARCHAR (20),
ACT_GENDER CHAR (1),
PRIMARY KEY (ACT_ID));

2. CREATE TABLE DIRECTOR (

DIR_ID NUMBER (3),
DIR_NAME VARCHAR (20),
DIR_PHONE NUMBER (10),
PRIMARY KEY (DIR_ID));

3. CREATE TABLE MOVIES (

MOV_ID NUMBER (4),
MOV_TITLE VARCHAR (25),
MOV_YEAR NUMBER (4),
MOV_LANG VARCHAR (12),
DIR_ID NUMBER (3),
PRIMARY KEY (MOV_ID),
FOREIGN KEY (DIR_ID) REFERENCES DIRECTOR (DIR_ID));

4. CREATE TABLE MOVIE_CAST (

ACT_ID NUMBER (3),
MOV_ID NUMBER (4),
ROLE VARCHAR (10),
PRIMARY KEY (ACT_ID, MOV_ID),
FOREIGN KEY (ACT_ID) REFERENCES ACTOR (ACT_ID),
FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

5. CREATE TABLE RATING (

The Oxford College of Engineering Page 27

DBMS LAB WITH MINI PROJECT 18CSL58

MOV_ID NUMBER (4),

REV_STARS VARCHAR (25),
PRIMARY KEY (MOV_ID),
FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

Table Descriptions

DESC ACTOR;

DESC DIRECTOR;

DESC MOVIES;

DESC MOVIE_CAST;

DESC RATING;

The Oxford College of Engineering Page 28

DBMS LAB WITH MINI PROJECT 18CSL58

Insertion of Values to Tables

INSERT INTO ACTOR VALUES (301,’ANUSHKA’,’F’);

INSERT INTO ACTOR VALUES (302,’PRABHAS’,’M’);
INSERT INTO ACTOR VALUES (303,’PUNITH’,’M’);
INSERT INTO ACTOR VALUES (304,’JERMY’,’M’);

INSERT INTO DIRECTOR VALUES (60,’RAJAMOULI’, 8751611001);

INSERT INTO DIRECTOR VALUES (61,’HITCHCOCK’, 7766138911);
INSERT INTO DIRECTOR VALUES (62,’FARAN’, 9986776531);
INSERT INTO DIRECTOR VALUES (63,’STEVEN SPIELBERG’, 8989776530);

INSERT INTO MOVIES VALUES (1001,’BAHUBALI-2’, 2017, ‘TELAGU’, 60);

INSERT INTO MOVIES VALUES (1002,’BAHUBALI-1’, 2015, ‘TELAGU’, 60);
INSERT INTO MOVIES VALUES (1003,’AKASH’, 2008, ‘KANNADA’, 61);
INSERT INTO MOVIES VALUES (1004,’WAR HORSE’, 2011, ‘ENGLISH’, 63);

INSERT INTO MOVIE_CAST VALUES (301, 1002, ‘HEROINE’);

INSERT INTO MOVIE_CAST VALUES (301, 1001, ‘HEROINE’);
INSERT INTO MOVIE_CAST VALUES (303, 1003, ‘HERO’);
INSERT INTO MOVIE_CAST VALUES (303, 1002, ‘GUEST’);
INSERT INTO MOVIE_CAST VALUES (304, 1004, ‘HERO’);

INSERT INTO RATING VALUES (1001, 4);

INSERT INTO RATING VALUES (1002, 2);
INSERT INTO RATING VALUES (1003, 5);
INSERT INTO RATING VALUES (1004, 4);

SELECT * FROM ACTOR;

SELECT * FROM DIRECTOR;

The Oxford College of Engineering Page 29

DBMS LAB WITH MINI PROJECT 18CSL58

SELECT * FROM MOVIES;

SELECT * FROM MOVIE_CAST;

SELECT * FROM RATING;

Queries:
1. List the titles of all movies directed by ‘Hitchcock’.
SELECT MOV_TITLE
FROM MOVIES
WHERE DIR_ID IN (SELECT DIR_ID
FROM DIRECTOR
WHERE DIR_NAME = ‘HITCHCOCK’);

The Oxford College of Engineering Page 30

DBMS LAB WITH MINI PROJECT 18CSL58

2. Find the movie names where one or more actors acted in two or more movies.

SELECT MOV_TITLE
FROM MOVIES M, MOVIE_CAST MV
WHERE M.MOV_ID=MV.MOV_ID AND ACT_ID IN (SELECT ACT_ID
FROM MOVIE_CAST GROUP BY ACT_ID
HAVING COUNT (ACT_ID)>1)
GROUP BY MOV_TITLE
HAVING COUNT (*)>1;

3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use
JOIN operation).

SELECT ACT_NAME, MOV_TITLE, MOV_YEAR

FROM ACTOR A
JOIN MOVIE_CAST C
ON A.ACT_ID=C.ACT_ID
JOIN MOVIES M
ON C.MOV_ID=M.MOV_ID
WHERE M.MOV_YEAR NOT BETWEEN 2000 AND 2015;

OR

SELECT A.ACT_NAME, A.ACT_NAME, C.MOV_TITLE, C.MOV_YEAR

FROM ACTOR A, MOVIE_CAST B, MOVIES C
WHERE A.ACT_ID=B.ACT_ID
AND B.MOV_ID=C.MOV_ID
AND C.MOV_YEAR NOT BETWEEN 2000 AND 2015;

The Oxford College of Engineering Page 31

DBMS LAB WITH MINI PROJECT 18CSL58

4. Find the title of movies and number of stars for each movie that has at least one
rating and find the highest number of stars that movie received. Sort the result by
movie title.

SELECT MOV_TITLE, MAX (REV_STARS)

FROM MOVIES
INNER JOIN RATING USING (MOV_ID)
GROUP BY MOV_TITLE
HAVING MAX (REV_STARS)>0
ORDER BY MOV_TITLE;

5. Update rating of all movies directed by ‘Steven Spielberg’ to 5

KL
UPDATE RATING
SET REV_STARS=5
WHERE MOV_ID IN (SELECT MOV_ID FROM MOVIES
WHERE DIR_ID IN (SELECT DIR_ID
FROM DIRECTOR
WHERE DIR_NAME = ‘STEVEN
SPIELBERG’));

The Oxford College of Engineering Page 32

DBMS LAB WITH MINI PROJECT 18CSL58

D. Consider the schema for College Database:

STUDENT (USN, SName, Address, Phone, Gender)

SEMSEC (SSID, Sem, Sec)
CLASS (USN, SSID)
SUBJECT (Subcode, Title, Sem, Credits)
IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)
Write SQL queries to
1. List all the student details studying in fourth semester ‘C’ section.
2. Compute the total number of male and female students in each semester and in each
section.
3. Create a view of Test1 marks of student USN ‘1BI15CS101’ in all subjects.
4. Calculate the FinalIA (average of best two test marks) and update the
corresponding table for all students.
5. Categorize students based on the following criterion:
If FinalIA = 17 to 20 then CAT = ‘Outstanding’
If FinalIA = 12 to 16 then CAT = ‘Average’
If FinalIA< 12 then CAT = ‘Weak’
Give these details only for 8th semester A, B, and C section students.
Solution:

Entity - Relationship Diagram

The Oxford College of Engineering Page 33

DBMS LAB WITH MINI PROJECT 18CSL58

Schema Diagram

Table Creation

CREATE TABLE STUDENT (

USN VARCHAR (10) PRIMARY KEY,
SNAME VARCHAR (25),
ADDRESS VARCHAR (25),
PHONE BIGINT,
GENDER CHAR (1));

CREATE TABLE SEMSEC (

SSID VARCHAR (5) PRIMARY KEY,
SEM INTEGER,
SEC CHAR (1));

CREATE TABLE CLASS (

USN VARCHAR (10),
SSID VARCHAR (5),
PRIMARY KEY (USN, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN),

The Oxford College of Engineering Page 34

DBMS LAB WITH MINI PROJECT 18CSL58

FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID));

CREATE TABLE SUBJECT (
SUBCODE VARCHAR (8),
TITLE VARCHAR (20),
SEM INTEGER,
CREDITS INTEGER ,
PRIMARY KEY (SUBCODE));

CREATE TABLE IAMARKS (

USN VARCHAR (10),
SUBCODE VARCHAR (8),
SSID VARCHAR (5),
TEST1 INTEGER,
TEST2 INTEGER,
TEST3 INTEGER,
FINALIA INTEGER,
PRIMARY KEY (USN, SUBCODE, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN),
FOREIGN KEY (SUBCODE) REFERENCES SUBJECT (SUBCODE),
FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID));

Table Descriptions

DESC STUDENT;

DESC SEMSEC;

DESC CLASS;

The Oxford College of Engineering Page 35

DBMS LAB WITH MINI PROJECT 18CSL58

DESC SUBJECT;

DESC IAMARKS;

Insertion of values to tables

INSERT INTO STUDENT VALUES ('1OX13IS091','TEESHA','BENGALURU',

7712312312,'F');
INSERT INTO STUDENT VALUES ('1OX13IS066','SUPRIYA','MANGALURU',
8877881122,'F');
INSERT INTO STUDENTVALUES ('1OX14IS010','ABHAY','BENGALURU',
9900211201,'M');
INSERT INTO STUDENT VALUES ('1OX14IS032','BHASKAR','BENGALURU',
9923211099,'M');
INSERT INTO STUDENTVALUES ('1OX14IS025','ASMI','BENGALURU', 7894737377,'F');
INSERT INTO STUDENT VALUES ('1OX15IS011','AJAY','TUMKUR', 9845091341,'M');
INSERT INTO STUDENT VALUES ('1OX15IS029','CHITRA','DAVANGERE',
7696772121,'F');
INSERT INTO STUDENT VALUES ('1OX15IS045','JEEVA','BELLARY', 9944850121,'M');
INSERT INTO STUDENT VALUES ('1OX15IS091','SANTOSH','MANGALURU',
8812332201,'M');
INSERT INTO STUDENT VALUES ('1OX16IS045','ISMAIL','KALBURGI', 9900232201,'M');
INSERT INTO STUDENT VALUES ('1OX16IS088','SAMEERA','SHIMOGA',
9905542212,'F');

The Oxford College of Engineering Page 36

DBMS LAB WITH MINI PROJECT 18CSL58

INSERT INTO STUDENT VALUES ('1OX16IS122','VINAYAKA','CHIKAMAGALUR',

8800880011,'M');

INSERT INTO SEMSEC VALUES ('ISE8A', 8,'A');

INSERT INTO SEMSEC VALUES (‘ISE8B', 8,'B');
INSERT INTO SEMSEC VALUES (‘ISE8C’, 8,’C’);

INSERT INTO SEMSEC VALUES ('ISE7A', 7,’A’);

INSERT INTO SEMSEC VALUES (‘ISE7B’, 7,'B’);
INSERT INTO SEMSEC VALUES ('ISE7C', 7,'C');

INSERT INTO SEMSEC VALUES (‘ISE6A', 6,'A');

INSERT INTO SEMSEC VALUES (‘ISE6B’, 6,’B’);
INSERT INTO SEMSEC VALUES ('ISE6C’, 6,’C’);

INSERT INTO SEMSEC VALUES (‘ISE5A’, 5,'A’);

INSERT INTO SEMSEC VALUES ('ISE5B', 5,'B');
INSERT INTO SEMSEC VALUES (‘ISE5C', 5,'C');

INSERT INTO SEMSEC VALUES (‘ISE4A’, 4,’A’);

INSERT INTO SEMSEC VALUES ('ISE4B', 4,’B’);
INSERT INTO SEMSEC VALUES (‘ISE4C’, 4,'C’);

INSERT INTO SEMSEC VALUES ('ISE3A', 3,'A');

INSERT INTO SEMSEC VALUES (‘ISE3B', 3,'B');
INSERT INTO SEMSEC VALUES (‘ISE3C’, 3,’C’);

INSERT INTO SEMSEC VALUES ('ISE2A', 2,’A’);

INSERT INTO SEMSEC VALUES (‘ISE2B’, 2,'B’);
INSERT INTO SEMSEC VALUES ('ISE2C', 2,'C');
INSERT INTO SEMSEC VALUES (‘ISE1A', 1,'A');
INSERT INTO SEMSEC VALUES (‘ISE1B’, 1,’B’);
INSERT INTO SEMSEC VALUES ('ISE1C', 1,’C’);

INSERT INTO CLASS VALUES (‘1OX13IS020’,’ISE8A’);

INSERT INTO CLASS VALUES (‘1OX13IS062’,’ISE8A’);
INSERT INTO CLASS VALUES (‘1OX13IS066’,’ISE8B’);
INSERT INTO CLASS VALUES (‘1OX13IS091’,’ISE8C’);

INSERT INTO CLASS VALUES (‘1OX14IS010’,’ISE7A’);

INSERT INTO CLASS VALUES (‘1OX14IS025’,’ISE7A’);
INSERT INTO CLASS VALUES (‘1OX14IS032’,’ISE7A’);

INSERT INTO CLASS VALUES (‘1OX15IS011’,’ISE4A’);

INSERT INTO CLASS VALUES (‘1OX15IS029’,’ISE4A’);
INSERT INTO CLASS VALUES (‘1OX15IS045’,’ISE4B’);

The Oxford College of Engineering Page 37

DBMS LAB WITH MINI PROJECT 18CSL58

INSERT INTO CLASS VALUES (‘1OX15IS091’,’ISE4C’);

INSERT INTO CLASS VALUES (‘1OX16IS045’,’ISE3A’);

INSERT INTO CLASS VALUES (‘1OX16IS088’,’ISE3B’);
INSERT INTO CLASS VALUES (‘1OX16IS122’,’ISE3C’);

INSERT INTO SUBJECT VALUES ('10IS81','ACA', 8, 4);

INSERT INTO SUBJECT VALUES ('10CS82','SSM', 8, 4);
INSERT INTO SUBJECT VALUES ('10CS83','NM', 8, 4);
INSERT INTO SUBJECT VALUES ('10CS84','CC', 8, 4);
INSERT INTO SUBJECT VALUES ('10CS85','PW', 8, 4);

INSERT INTO SUBJECT VALUES ('10CS71','OOAD', 7, 4);

INSERT INTO SUBJECT VALUES ('10CS72','ECS', 7, 4);
INSERT INTO SUBJECT VALUES ('10CS73','PTW', 7, 4);
INSERT INTO SUBJECT VALUES ('10CS74','DWDM', 7, 4);
INSERT INTO SUBJECT VALUES (‘10CS75','JAVA', 7, 4);
INSERT INTO SUBJECT VALUES ('10CS76','SAN', 7, 4);

INSERT INTO SUBJECT VALUES ('15CS51', 'ME', 5, 4);

INSERT INTO SUBJECT VALUES ('15CS52','CN', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS53','DBMS', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS54','ATC', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS55','JAVA', 5, 3);
INSERT INTO SUBJECT VALUES ('15CS56','AI', 5, 3);
INSERT INTO SUBJECT VALUES ('15CS41','M4', 4, 4);
INSERT INTO SUBJECT VALUES ('15CS42','SE', 4, 4);
INSERT INTO SUBJECT VALUES ('15CS43','DAA', 4, 4);
INSERT INTO SUBJECT VALUES ('15CS44','MPMC', 4, 4);
INSERT INTO SUBJECT VALUES ('15CS45','OOC', 4, 3);
INSERT INTO SUBJECT VALUES ('15CS46','DC', 4, 3);

INSERT INTO SUBJECT VALUES ('15CS31','M3', 3, 4);

INSERT INTO SUBJECT VALUES ('15CS32','ADE', 3, 4);
INSERT INTO SUBJECT VALUES ('15CS33','DSA', 3, 4);
INSERT INTO SUBJECT VALUES ('15CS34','CO', 3, 4);
INSERT INTO SUBJECT VALUES ('15CS35','USP', 3, 3);
INSERT INTO SUBJECT VALUES ('15CS36','DMS', 3, 3);

INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1OX13IS091','10CS81','ISE8C', 15, 16, 18);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1OX13IS091','10CS82','ISE8C', 12, 19, 14);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1OX13IS091','10CS83','ISE8C', 19, 15, 20);

The Oxford College of Engineering Page 38

DBMS LAB WITH MINI PROJECT 18CSL58

INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1OX13IS091','10CS84','ISE8C', 20, 16, 19);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1OX13IS091','10CS85','ISE8C', 15, 15, 12);

SELECT * FROM STUDENT;

SELECT * FROM SEMSEC;

The Oxford College of Engineering Page 39

DBMS LAB WITH MINI PROJECT 18CSL58

SELECT * FROM CLASS;

SELECT * FROM SUBJECT;

The Oxford College of Engineering Page 40

DBMS LAB WITH MINI PROJECT 18CSL58

SELECT * FROM IAMARKS;

Queries:

1. List all the student details studying in fourth semester ‘C’ section.
SELECT S.*, SS.SEM, SS.SEC
FROM STUDENT S, SEMSEC SS, CLASS C
WHERE S.USN = C.USN AND
SS.SSID = C.SSID AND
SS.SEM = 4 AND SS.SEc=’C’;

The Oxford College of Engineering Page 41

DBMS LAB WITH MINI PROJECT 18CSL58

2. Compute the total number of male and female students in each semester and in each
section.

SELECT SS.SEM, SS.SEC, S.GENDER, COUNT (S.GENDER) AS COUNT

FROM STUDENT S, SEMSEC SS, CLASS C
WHERE S.USN = C.USN AND
SS.SSID = C.SSID
GROUP BY SS.SEM, SS.SEC, S.GENDER
ORDER BY SEM;

3. Create a view of Test1 marks of student USN ‘1BI15IS101’ in all subjects.

CREATE VIEW STU_TEST1_MARKS_VIEW
AS
SELECT TEST1, SUBCODE
FROM IAMARKS
WHERE USN = '1RN13IS091';

4. Calculate the FinalIA (average of best two test marks) and update the
corresponding table for all students.
UPDATE IAMARKS SET

The Oxford College of Engineering Page 42

DBMS LAB WITH MINI PROJECT 18CSL58

FINALIA = ((TEST1+TEST2+TEST3)-LEAST (TEST1,LEAST (TEST2,TEST3)))/2;

SELECT * FROM IAMARKS;

5. Categorize students based on the following criterion:

If FinalIA = 17 to 20 then CAT = ‘Outstanding’
If FinalIA = 12 to 16 then CAT = ‘Average’
If FinalIA< 12 then CAT = ‘Weak’
Give these details only for 8th semester A, B, and C section students.

SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
(CASE
WHEN IA.FINALIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'
WHEN IA.FINALIA BETWEEN 12 AND 16 THEN 'AVERAGE'
ELSE 'WEAK'
END) AS CAT
FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB
WHERE S.USN = IA.USN AND
SS. SSID = IA.SSID AND
SUB.SUBCODE = IA.SUBCODE AND
SUB.SEM = 8;

E. Consider the schema for Company Database:

The Oxford College of Engineering Page 43

DBMS LAB WITH MINI PROJECT 18CSL58

EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo)

DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)
DLOCATION (DNo,DLoc)
PROJECT (PNo, PName, PLocation, DNo)
WORKS_ON (SSN, PNo, Hours)
Write SQL queries to
1. Make a list of all project numbers for projects that involve an employee whose last
name is ‘Scott’, either as a worker or as a manager of the department that controls
the project.

The Oxford College of Engineering Page 44

DBMS LAB WITH MINI PROJECT 18CSL58

2.
3. Show the resulting salaries if every employee working on the ‘IoT’ project is given a
10 percent raise.
4. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well
as the maximum salary, the minimum salary, and the average salary in this
department
5. Retrieve the name of each employee who works on all the projects controlled by
department number 5 (use NOT EXISTS operator). For each department that has
more than five employees, retrieve the department number and the number of its
employees who are making more than Rs. 6,00,000.
Entity-Relationship Diagram

Schema Diagram

Employee

SSN Fname Lname Address Sex Salary SuperSSN DNO

Department

DNO Dname MgrSS MgrStartDate

N

DLocation

DNO DLOC

The Oxford College of Engineering Page 45

DBMS LAB WITH MINI PROJECT 18CSL58

Project

PN PNam PLocatio DN
O e n O

Works_on

SS PN Hour
N O s

Table Creation

CREATE TABLE DEPARTMENT

(DNO VARCHAR2 (20) PRIMARY KEY,
DNAME VARCHAR2 (20),
MGRSTARTDATE DATE);

CREATE TABLE EMPLOYEE

(SSN VARCHAR2 (20) PRIMARY KEY,
FNAME VARCHAR2 (20),
LNAME VARCHAR2 (20),
ADDRESS VARCHAR2 (20),
SEX CHAR (1),
SALARY INTEGER,
SUPERSSN REFERENCES EMPLOYEE (SSN),
DNO REFERENCES DEPARTMENT (DNO));
NOTE: Once DEPARTMENT and EMPLOYEE tables are created we must alter department
table to add foreign constraint MGRSSN using sql command

ALTER TABLE DEPARTMENT

ADD MGRSSN REFERENCES EMPLOYEE (SSN);

CREATE TABLE DLOCATION

(DLOC VARCHAR2 (20),
DNO REFERENCES DEPARTMENT (DNO),
PRIMARY KEY (DNO, DLOC));

CREATE TABLE PROJECT

(PNO INTEGER PRIMARY KEY,
PNAME VARCHAR2 (20),
PLOCATION VARCHAR2 (20),

The Oxford College of Engineering Page 46

DBMS LAB WITH MINI PROJECT 18CSL58

DNO REFERENCES DEPARTMENT (DNO));

CREATE TABLE WORKS_ON

(HOURS NUMBER (2),
SSN REFERENCES EMPLOYEE (SSN),
PNO REFERENCES PROJECT(PNO),
PRIMARY KEY (SSN, PNO));

Table Descriptions

DESC EMPLOYEE;

DESC DEPARTMENT;

DESC DLOCATION;

DESC PROJECT;

The Oxford College of Engineering Page 47

DBMS LAB WITH MINI PROJECT 18CSL58

DESC WORKS_ON;

Insertion of values to tables

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXECE01’,’JOHN’,’SCOTT’,’BANGALORE’,’M’, 450000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXISE01’,’JAMES’,’SMITH’,’BANGALORE’,’M’, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXISE02’,’HEARN’,’BAKER’,’BANGALORE’,’M’, 700000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXISE03’,’EDWARD’,’SCOTT’,’MYSORE’,’M’, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXISE04’,’PAVAN’,’HEGDE’,’MANGALORE’,’M’, 650000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXISE05’,’GIRISH’,’MALYA’,’MYSORE’,’M’, 450000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXISE06’,’NEHA’,’SN’,’BANGALORE’,’F’, 800000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXACC01’,’AHANA’,’K’,’MANGALORE’,’F’, 350000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXACC02’,’SANTHOSH’,’KUMAR’,’MANGALORE’,’M’, 300000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXISE01’,’VEENA’,’M’,’MYSORE’,’M’, 600000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘OXIT01’,’NAGESH’,’HR’,’BANGALORE’,’M’, 500000);

INSERT INTO DEPARTMENT VALUES (‘1’,’ACCOUNTS’,’01-JAN-01’,’OXACC02’);

INSERT INTO DEPARTMENT VALUES (‘2’,’IT’,’01-AUG-16’,’OXIT01’);
INSERT INTO DEPARTMENT VALUES (‘3’,’ECE’,’01-JUN-08’,’OXECE01’);
INSERT INTO DEPARTMENT VALUES (‘4’,’ISE’,’01-AUG-15’,’OXISE01’);
INSERT INTO DEPARTMENT VALUES (‘5’,’ISE’,’01-JUN-02’,’OXISE05’);

The Oxford College of Engineering Page 48

DBMS LAB WITH MINI PROJECT 18CSL58

Note: update entries of employee table to fill missing fields SUPERSSN and DNO

UPDATE EMPLOYEE SET

SUPERSSN=NULL, DNO=’3’
WHERE SSN=’OXECE01’;

UPDATE EMPLOYEE SET

SUPERSSN=’OXISE02’, DNO=’5’
WHERE SSN=’OXISE01’;

UPDATE EMPLOYEE SET

SUPERSSN=’OXISE03’, DNO=’5’
WHERE SSN=’OXISE02’;

UPDATE EMPLOYEE SET

SUPERSSN=’OXISE04’, DNO=’5’
WHERE SSN=’OXISE03’;

UPDATE EMPLOYEE SET

DNO=’5’, SUPERSSN=’OXISE05’
WHERE SSN=’OXISE04’;

UPDATE EMPLOYEE SET

DNO=’5’, SUPERSSN=’OXSISE06’
WHERE SSN=’OXISE05’;

UPDATE EMPLOYEE SET

DNO=’5’, SUPERSSN=NULL
WHERE SSN=’OXISE06’;

UPDATE EMPLOYEE SET

DNO=’1’, SUPERSSN=’OXACC02’
WHERE SSN=’OXACC01’;

UPDATE EMPLOYEE SET

DNO=’1’, SUPERSSN=NULL
WHERE SSN=’OXACC02’;

UPDATE EMPLOYEE SET

DNO=’4’, SUPERSSN=NULL
WHERE SSN=’OXISE01’;

UPDATE EMPLOYEE SET

The Oxford College of Engineering Page 49

DBMS LAB WITH MINI PROJECT 18CSL58

DNO=’2’, SUPERSSN

=NULL
WHERE SSN=’OXIT01’;

INSERT INTO DLOCATION VALUES (’BANGALORE’, ‘1’);

INSERT INTO DLOCATION VALUES (’BANGALORE’, ‘2’);
INSERT INTO DLOCATION VALUES (’BANGALORE’, ‘3’);
INSERT INTO DLOCATION VALUES (’MANGALORE’, ‘4’);
INSERT INTO DLOCATION VALUES (’MANGALORE’, ‘5’);

INSERT INTO PROJECT VALUES (100,’IOT’,’BANGALORE’,’5’);

INSERT INTO PROJECT VALUES (101,’CLOUD’,’BANGALORE’,’5’);
INSERT INTO PROJECT VALUES (102,’BIGDATA’,’BANGALORE’,’5’);
INSERT INTO PROJECT VALUES (103,’SENSORS’,’BANGALORE’,’3’);
INSERT INTO PROJECT VALUES (104,’BANK MANAGEMENT’,’BANGALORE’,’1’);
INSERT INTO PROJECT VALUES (105,’SALARY MANAGEMENT’,’BANGALORE’,’1’);
INSERT INTO PROJECT VALUES (106,’OPENSTACK’,’BANGALORE’,’4’);
INSERT INTO PROJECT VALUES (107,’SMART CITY’,’BANGALORE’,’2’);

INSERT INTO WORKS_ON VALUES (4, ‘OXISE01’, 100);

INSERT INTO WORKS_ON VALUES (6, ‘OXISE01’, 101);
INSERT INTO WORKS_ON VALUES (8, ‘OXISE01’, 102);
INSERT INTO WORKS_ON VALUES (10, ‘OXISE02’, 100);
INSERT INTO WORKS_ON VALUES (3, ‘OXISE04’, 100);
INSERT INTO WORKS_ON VALUES (4, ‘OXISE05’, 101);
INSERT INTO WORKS_ON VALUES (5, ‘OXISE06’, 102);
INSERT INTO WORKS_ON VALUES (6, ‘OXISE03’, 102);
INSERT INTO WORKS_ON VALUES (7, ‘OXECE01’, 103);
INSERT INTO WORKS_ON VALUES (5, ‘OXACC01’, 104);
INSERT INTO WORKS_ON VALUES (6, ‘OXACC02’, 105);
INSERT INTO WORKS_ON VALUES (4, ‘OXISE01’, 106);
INSERT INTO WORKS_ON VALUES (10, ‘OXIT01’, 107);

SELECT * FROM EMPLOYEE;

SELECT * FROM DEPARTMENT;

The Oxford College of Engineering Page 50

DBMS LAB WITH MINI PROJECT 18CSL58

SELECT * FROM DLOCATION;

SELECT * FROM PROJECT;

SELECT * FROM WORKS_ON;

Queries:

The Oxford College of Engineering Page 51

DBMS LAB WITH MINI PROJECT 18CSL58

1. Make a list of all project numbers for projects that involve an employee whose last
name is ‘Scott’, either as a worker or as a manager of the department that controls the
project.

(SELECT DISTINCT P.PNO

FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
WHERE E.DNO=D.DNO
AND D.MGRSSN=E.SSN
AND E.LNAME=’SCOTT’)
UNION
(SELECT DISTINCT P1.PNO
FROM PROJECT P1, WORKS_ON W, EMPLOYEE E1
WHERE P1.PNO=W.PNO
AND E1.SSN=W.SSN
AND E1.LNAME=’SCOTT’);

2. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10
percent raise.

SELECT E.FNAME, E.LNAME, 1.1*E.SALARY AS INCR_SAL

FROM EMPLOYEE E, WORKS_ON W, PROJECT P
WHERE E.SSN=W.SSN
AND W.PNO=P.PNO
AND P.PNAME=’IOT’;

3. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as
the maximum salary, the minimum salary, and the average salary in this department

The Oxford College of Engineering Page 52

DBMS LAB WITH MINI PROJECT 18CSL58

SELECT SUM (E.SALARY), MAX (E.SALARY), MIN (E.SALARY), AVG

(E.SALARY)
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO=D.DNO
AND D.DNAME=’ACCOUNTS’;

4. Retrieve the name of each employee who works on all the projects Controlled by
department number 5 (use NOT EXISTS operator).

SELECT E.FNAME, E.LNAME

FROM EMPLOYEE E
WHERE NOT EXISTS((SELECT PNO
FROM PROJECT
WHERE DNO=’5’)
MINUS (SELECT PNO
FROM WORKS_ON
WHERE E.SSN=SSN));

5. For each department that has more than five employees, retrieve the department
number and the number of its employees who are making more than Rs. 6, 00,000.

SELECT D.DNO, COUNT (*)

FROM DEPARTMENT D, EMPLOYEE E
WHERE D.DNO=E.DNO
AND E.SALARY>600000
AND D.DNO IN (SELECT E1.DNO
FROM EMPLOYEE E1
GROUP BY E1.DNO
HAVING COUNT (*)>5)
GROUP BY D.DNO;

The Oxford College of Engineering Page 53

 DBMS LAB WITH MINI PROJECT 18CSL58

Part B: Mini project

MYSQL DATABASE CONNECTION WITH JAVA

5 steps to connect any java application with the database in java using JDBC. They are as
follows:
o Register the driver class
o Creating connection
o Creating statement
o Executing queries
o Closing connection

Example to Connect Java Application with mysql database

1. importjava.sql.*;
2. class MysqlCon{
3. public static void main(String args[]){
4. try{
5. Class.forName("com.mysql.jdbc.Driver");
6. Connection con=DriverManager.getConnection(
7. //here sonoo is database name, root is username and password.
8. Statement stmt=con.createStatement();

MYSQL DATABASE CONNECTION WITH C#

To create a connection with a MySQL database, perform the following steps:

1. Start Visual Studio and open the Server Explorer window by clicking Viewand
then Server Explorer from the main menu.
2. Right-click the Data Connections node and click Add Connection.
3. From the Add Connection dialog, click change and select the MySQL Database as a
data source.
4. From the Change Data Source dialog, select MySQL Database from the list of data
sources (or the <other> option, if MySQL Database is absent), and leave .NET Framework
Data Provider for MySQL as the data provider.

using(SqlConnection conn = new SqlConnection())

The Oxford College of Engineering Page 54

DBMS LAB WITH MINI PROJECT 18CSL58

{
  conn.ConnectionString =
"Server=[server_name];Database=[database_name];Trusted_Connection=true";
// using the code here...
}

MYSQL DATABASE CONNECTION WITH PHP

To create a connection with a MySQL database proceed the following:

<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
?>

MYSQL DATABASE CONNECTION WITH PYTHON

To create a connection with a MySQL database proceed the following:

#!/usr/bin/python

importMySQLdb

# Open database connection

db=MySQLdb.connect("localhost","testuser","test123","TESTDB")

The Oxford College of Engineering Page 55

DBMS LAB WITH MINI PROJECT 18CSL58

# prepare a cursor object using cursor() method

cursor=db.cursor()

# execute SQL query using execute() method.

cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.

data=cursor.fetchone()

print"Database version : %s "% data

# disconnect from server

db.close()

IV.Additional Exercises (Updated on 2018-19)

Exercise 1: To understand some simple Database Applications and build Conceptual Data
Model.
a) Select an enterprise that you are familiar with (for example,a school, a college, a
company, a small business, a club or association).List all the information that this
enterprise uses.
b) Describe the steps involved in the database design process using E-R Modeling:
Requirements Analysis, Identify Entity Sets, Identify Relationship Sets, Value Sets and
Attributes, Specifying Primary keys, Building E-R diagram, Implementation
c) For the following mini-world example database applications, Design and Develop
Conceptual Data Model (E-R Diagram) with all the necessary entities, attributes,
constraints and relationships.
i. Medical Clinic Database –The clinic has a number of regular patients and new
patients come to the clinic regularly. Patients make appointments to see one of the

The Oxford College of Engineering Page 56

DBMS LAB WITH MINI PROJECT 18CSL58

doctors; several doctors attend the clinic and they each have their own hours. Some
doctors are General Practitioners (GPs) while others are specialists(cardiologists,
dermatologists etc.,). Patients have families and the family relationships are
important. A medical record of each patient needs to be maintained. Information on
prescriptions, insurance, allergies,etc needs tobe maintained. Different doctors may
charge different fees. Billing has to be done for Patients.

ii. University Database - The Visvesvaraya Technological University (VTU)is a large

Institution with several campuses scattered across Karnataka. Academically, the
university isdivided intoa number of faculties, such as Faculty of Engineering,
Faculty of Architecture, Faculty of Management and Faculty of Science. Some of the
Faculties operate on a number of campuses. Faculties, in turn, are divided into
schools; for example, theSchool of Architecture, the School of Information
Technology. Each school is headed by a Director and has a number of teaching and
non-teaching staff. Each school offers many courses. Each course consists of a fixed
core of subjects and a number of electives fromother courses. Each student in the
Universityis enrolled in a single courseof study. A subject is taught to the students
who have registered for that subject by a teacher. A student is awarded a grade in
each subject taken.

iii. Construction Company Database - A construction company has many branches

spread all over the country. The company has two types of constructions to offer:
Housing and Commercial. The housing company provides low-income housing,
medium-style housing, and high-end housing schemes, while commercial side, it
offers multiplexes and shopping zones. The customers of the company may be
individuals or corporate clients. Company stores the information about employees’
works for it.

iv. Time Table Preparation - An Engineering College has a number of Branches. Each
Branch has number sections, a number of courses and a number of faculty members
teaching the courses. Each branch has a number of class rooms and laboratories. Each
course may be scheduled in a class room at a particular time.

The Oxford College of Engineering Page 57

DBMS LAB WITH MINI PROJECT 18CSL58

Note: Similar applications may be explored and given as assignments to students in a

group.

Exercise 2: Design andbuild Relational Data Model for each of the application scenarios of
exercise 1 specifying all possible constraints. Extend the same for a database application of
students' choice.
Exercise 3 To understand and demonstrate DDL, DML and DCL Commands of SQL
a. Create a table called EMP with the following structure and describe it.

Name Type
-----------------------------------------------------
EMPNO NUMBER(6)
ENAME VARCHAR2(20)
DOB DATE
JOB VARCHAR2(10)
DEPTNO NUMBER(2)
SAL NUMBER(7,2)
Allow NULL for all columns except ENAME and JOB. EMPNO is the Primary
Key
b. Add a column EXPERIENCE of type NUMERIC to the EMP table. Allow NULL to it.
c. Modify the column width of the JOB field of EMP table.
d. Create DEPT table with the following structure and describe it

Name Type
-----------------------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(15)
LOCN VARCHAR2(10)
DEPTNO is the Primary Key and DNAME cannot be NULL

e. Add constraint to check the SAL value of EMP Table. SAL must be > 6000.
f. Drop a column EXPERIENCE from the EMP table.
g. Insert a single record into DEPT table. Repeat this for inserting at least 3 records

The Oxford College of Engineering Page 58

DBMS LAB WITH MINI PROJECT 18CSL58

h. Insert more than a record into EMP table using a single insert command. Insert at least 10
records
i. Update the EMP table to set the salary of all employees to Rs. 30000/- for a given JOB
type
j. Create a pseudo table EMPLOYEE with the same structure as the table EMP using
SELECT clause.
k. Delete employees from EMP table for a given JOB type. Delete the first five records of
EMP table
l. Grant all/some privileges of EMP table to DEPT table
m. Revoke some/all privileges of EMP table from DEPT table
n. Truncate the EMP table and drop the DEPT table
o. Demonstrate the use of COMMIT, SAVEPOINT and ROLLBACK commands

V.Additional Exercises (Updated on 2019-20)

1. PL/SQL : CURSOR PROGRAM
Cursor to select the five highest paid employees from the emp table.
Input Table
SQL> SELECT ename, empno, sal FROM emp ORDER BY sal DESC;

ENAME EMPNO SAL

---------- --------- --------
KING 7839 5000
SCOTT 7788 3000
FORD 7902 3000
JONES 7566 2975
BLAKE 7698 2850
CLARK 7782 2450
ALLEN 7499 1600
TURNER 7844 1500
MILLER 7934 1300
WARD 7521 1250
MARTIN 7654 1250
ADAMS 7876 1100
JAMES 7900 950
SMITH 7369 800
PL/SQL Block
-- available online in file 'sample2'
DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp

The Oxford College of Engineering Page 59

DBMS LAB WITH MINI PROJECT 18CSL58

ORDER BY sal DESC; -- start with highest paid employee

my_ename VARCHAR2(10);
my_empno NUMBER(4);
my_sal NUMBER(7,2);
BEGIN
OPEN c1;
FOR i IN 1..5 LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND; /* in case the number requested */
/* is more than the total */
/* number of employees */
INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
COMMIT;
END LOOP;
CLOSE c1;
END;
Output Table
SQL> SELECT * FROM temp ORDER BY col1 DESC;

NUM_COL1 NUM_COL2 CHAR_COL

-------- -------- --------
5000 7839 KING
3000 7902 FORD
3000 7788 SCOTT
2975 7566 JONES
2850 7698 BLAKE

2. PL/SQL : FOR Loop PROGRAM

FOR loop to insert ten rows into a database table. The values of a loop index, counter variable,
and either of two character strings are inserted. Which string is inserted depends on the value of
the loop index.
Input Table
Not applicable.
WRITE A PL/SQL PROGRAM TO GENERATE FIBONACCI SERIES
declare
a number;
b number;
c number;
n number;
i number;
begin
n:=&n;
a:=0;
b:=1;
dbms_output.put_line(a);
dbms_output.put_line(b);
for i in 1..n-2

The Oxford College of Engineering Page 60

DBMS LAB WITH MINI PROJECT 18CSL58

loop
c:=a+b;
dbms_output.put_line(c);
a:=b;
b:=c;
end loop;
end;
OUTPUT:
SQL> @ FIBONACCI.sql
21 /
Enter value for n: 5
old 8: n:=&n;
new 8: n:=5;
0
1
1
2
3

PL/SQL procedure successfully completed.

3. TRIGGERS

BEFORE TRIGGER
create or replace trigger tday before insert or delete or update on emp
declare
we varchar2(10);
begin
we:=to_char(sysdate,'dy');
if we='sat' or we='sun' then
raise_application_error(-20015,'its a weekend');
end if;
end;

output:-

SQL> @trig1
/

Trigger created.

SQL> delete from emp where empno=7902;

ERROR at line 1:
ORA-20015: its a weekend
ORA-06512: at "SCOTT.TDAY", line 6
ORA-04088: error during execution of trigger 'SCOTT.TDAY'

The Oxford College of Engineering Page 61

DBMS LAB WITH MINI PROJECT 18CSL58

AFTER TRIGGER

create or replace trigger ttime after insert or delete or update on emp1 for each row
declare
tt varchar2(5);
begin
tt:=to_char(sysdate,'hh24');
if tt not between 10 and 17 then
raise_application_error(-20010,'not working hours');
end if;
end;

output:-
SQL> @trig2;
10 /

Trigger created.

SQL> update emp1 set empno=7777 where empno=7902;

*
ERROR at line 1:
ORA-20010: not working hours
ORA-06512: at "SCOTT.TTIME", line 6
ORA-04088: error during execution of trigger 'SCOTT.TTIME'

4. PL/SQL EXCEPTION EXCEPTION HANDLING

DECLARE
c_id customers.id%type := &cc_id;
c_name customerS.Name%type;
c_addr customers.address%type;
-- user defined exception
ex_invalid_id EXCEPTION;
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
END IF;

EXCEPTION

The Oxford College of Engineering Page 62

DBMS LAB WITH MINI PROJECT 18CSL58

WHEN ex_invalid_id THEN

dbms_output.put_line('ID must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/

OUTPUT:
Enter value for cc_id: -6 (let's enter a value -6)
old 2: c_id customers.id%type := &cc_id;
new 2: c_id customers.id%type := -6;
ID must be greater than zero!
PL/SQL procedure successfully completed.

V. Case Studies

Case Study 1: Hospital Management System

Aim: XYZ hospital is a multi-specialty hospital that includes a number of departments,

rooms,doctors, nurses, compounders, and other staff working in the hospital. Patients having
different kinds of ailments come to the hospital and get checkup done from the concerned
doctors. If required they are admitted in the hospital and discharged after treatment. The aim of
this case study is to design and develop a database for the hospital to maintain the records of
various departments, rooms, and doctors in the hospital. It also maintains records of the regular
patients, patients admitted in the hospital, the checkup of patients done by the doctors, the
patients that have been operated, and patients discharged from the hospital.

Description: In hospital, there are many departments like Orthopedic, Pathology, Emergency,
Dental, Gynecology, Anesthetics, I.C.U., Blood Bank, Operation Theater, Laboratory, M.R.I.,
Neurology, Cardiology, Cancer Department, Corpse, etc. There is an OPD where patients come
and get a card (that is, entry card of the patient) for check up from the concerned doctor. After
making entry in the card, they go to the concerned doctor’s room and the doctor checks up their
ailments. According to the ailments, the doctor either prescribes medicine or admits the patient in
the concerned department. The patient may choose either private or general room according to
his/her need. But before getting admission in the hospital, the patient has to fulfill certain
formalities of the hospital like room charges, etc. After the treatment is completed, the doctor
discharges the patient. Before discharging from the hospital, the patient again has to complete
certain formalities of the hospital like balance charges, test charges, operation charges (if any),
blood charges, doctors’ charges, etc. Next we talk about the doctors of the hospital. There are
two types of the doctors in the hospital, namely, regular doctors and call on doctors. Regular
doctors are those doctors who come to the hospital daily. Calls on doctors are those doctors who
are called by the hospital if the concerned doctor is not available.

The Oxford College of Engineering Page 63

DBMS LAB WITH MINI PROJECT 18CSL58

Table Description: Following are the tables along with constraints used in Hospital
Management database.
1. DEPARTMENT: This table consists of details about the various departments in the hospital.
The information stored in this table includes department name, department location, and facilities
available in that department.
Constraint: Department name will be unique for each department.
2. ALL_DOCTORS: This table stores information about all the doctors working for the hospital
and the departments they are associated with. Each doctor is given an identity number starting
with DR or DC prefixes only.
Constraint: Identity number is unique for each doctor and the corresponding department should
exist in DEPARTMENT table.
3. DOC_REG: This table stores details of regular doctors working in the hospital. Doctors are
referred to by their doctor number. This table also stores personal details of doctors like name,
qualification, address, phone number, salary, date of joining, etc.
Constraint: Doctor’s number entered should contain DR only as a prefix and must exist in
ALL_DOCTORS table.
4. DOC_ON_CALL: This table stores details of doctors called by hospital when
additionaldoctors are required. Doctors are referred to by their doctor number. Other personal
details like name, qualification, fees per call, payment due, address, phone number, etc., are also
stored.
Constraint: Doctor’s number entered should contain DC only as a prefix and must exist in
ALL_DOCTORS table.
5. PAT_ENTRY: The record in this table is created when any patient arrives in the hospital for a
checkup. When patient arrives, a patient number is generated which acts as a primary key. Other
details like name, age, sex, address, city, phone number, entry date, and name of the doctor
referred to, diagnosis, and department name are also stored. After storing the necessary details
patient is sent to the doctor for checkup.
Constraint: Patient number should begin with prefix PT. Sex should be M or F only. Doctor’s
name and department referred must exist.
6. PAT_CHKUP: This table stores the details about the patients who get treatment from the
doctor referred to. Details like patient number from patient entry table, doctor number, date of
checkup, diagnosis, and treatment are stored. One more field status is used to indicate whether
patient is admitted, referred for operation or is a regular patient to the hospital. If patient is
admitted, further details are stored in PAT_ADMIT table. If patient is referred for operation, the
further details are stored in PAT_OPR table and if patient is a regular patient to the hospital, the
further details are stored in PAT_REG table.
Constraint: Patient number should exist in PAT_ENTRY table and it should be unique.
7. PAT_ADMIT: When patient is admitted, his/her related details are stored in this table.
Information stored includes patient number, advance payment, mode of payment, room number,
department, date of admission, initial condition, diagnosis, treatment, number of the doctor under
whom treatment is done, attendant name, etc.
Constraint: Patient number should exist in PAT_ENTRY table. Department, doctor number,
room number must be valid.
8. PAT_DIS: An entry is made in this table whenever a patient gets discharged from the hospital.
Each entry includes details like patient number, treatment given, treatment advice, payment
made, mode of payment, date of discharge, etc.

The Oxford College of Engineering Page 64

DBMS LAB WITH MINI PROJECT 18CSL58

Constraint: Patient number should exist in PAT_ENTRY table.

9. PAT_REG: Details of regular patients are stored in this table. Information stored includes date
of visit, diagnosis, treatment, medicine recommended, status of treatment, etc.
Constraint: Patient number should exist in patient entry table. There can be multiple entries of
one patient as patient might be visiting hospital repeatedly for checkup and there will be entry for
patient’s each visit.
10. PAT_OPR: If patient is operated in the hospital, his/her details are stored in this table.
Information stored includes patient number, date of admission, date of operation, number of the
doctor who conducted the operation, number of the Operation Theater in which operation was
carried out, type of operation, patient’s condition before and after operation, treatment advice,
etc.
Constraint: Patient number should exist in PAT_ENTRY table. Department, doctor number
should exist or should be valid.
11. ROOM_DETAILS: It contains details of all rooms in the hospital. The details stored in this
table include room number, room type (general or private), status (whether occupied or not), if
occupied, then patient number, patient name, charges per day, etc.
Constraint: Room number should be unique. Room type can only be G or P and statuscan only
be Y or N.

E‐R Diagram

The Oxford College of Engineering Page 65

DBMS LAB WITH MINI PROJECT 18CSL58

Relational Database Schema for Case Study

The relational database schema for Hospital Management database is as follows:
1. DEPARTMENT (D_NAME, D_LOCATION, FACILITIES)
2. ALL_DOCTORS (DOC_NO, DEPARTMENT)
3. DOC_REG(DOC_NO, D_NAME, QUALIFICATION, SALARY, EN_TIME, EX_TIME,
ADDRESS,
PH_NO, DOJ)
4. DOC_ON_CALL (DOC_NO, D_NAME, QUALIFICATION, FS_PR_CL, PYMT_DU,
ADDRESS,
PH_NO)
5. PAT_ENTRY (PAT_NO, PAT_NAME, CHKUP_DT, PT_AGE, SEX, RFRG_CSTNT,
DIAGNOSIS,
RFD, ADDRESS, CITY, PH_NO, DEPARTMENT)
6. PAT_CHKUP (PAT_NO, DOC_NO, DIAGNOSIS, STATUS, TREATMENT)

The Oxford College of Engineering Page 66

DBMS LAB WITH MINI PROJECT 18CSL58

7. PAT_ADMIT (PAT_NO, ADV_PYMT, MODE_PYMT, ROOM_NO, DEPTNAME,

ADMTD_ON,
COND_ON, INVSTGTN_DN, TRMT_SDT, ATTDNT_NM)
8. PAT_DIS (PAT_NO, TR_ADVS, TR_GVN, MEDICINES, PYMT_GV, DIS_ON)
9. PAT_REG (PAT_NO, DATE_VIS, CONDITION, TREATMENT, MEDICINES, DOC_NO,
PAYMT)
10. PAT_OPR (PAT_NO, DATE_OPR, IN_COND, AFOP_COND, TY_OPERATION,
MEDICINES,
DOC_NO, OPTH_NO, OTHER_SUG)
11. ROOM_DETAILS (ROOM_NO, TYPE, STATUS, RM_DL_CRG, OTHER_CRG)

Case Study 2:Railway Reservation System

Aim: The railway reservation system facilitates the passengers to enquire about the trains
available on the basis of source and destination, booking and cancellation of tickets, enquire
about the status of the booked ticket, etc.
The aim of case study is to design and develop a database maintaining the records of different
trains, train status, and passengers. The record of train includes its number, name, source,
destination, and days on which it is available, whereas record of train status includes dates for
which tickets can be booked, total number of seats available, and number of seats already
booked. The database has been developed and tested on the Oracle.

Description:
Passengers can book their tickets for the train in which seats are available. For this, passenger
has to provide the desired train number and the date for which ticket is to be booked. Before
booking a ticket for a passenger, the validity of train number and booking date is checked. Once
the train number and booking date are validated, it is checked whether the seat is available. If
yes, the ticket is booked with confirm status and corresponding ticket ID is generated which is
stored along with other details of the passenger. After all the available tickets are booked, certain
numbers of tickets are booked with waiting status. If waiting lot is also finished, then tickets are
not booked and a message of non‐availability of seats is displayed.
The ticket once booked can be cancelled at any time. For this, the passenger has to provide the
ticket ID (the unique key). The ticket ID is searched and the corresponding record is deleted.
With this, the first ticket with waiting status also gets confirmed.

List of Assumption
Since the reservation system is very large in reality, it is not feasible to develop the case study to
that extent and prepare documentation at that level. Therefore, a small sample case study has
been created to demonstrate the working of the reservation system. To implement this sample
case study, some assumptions have been made, which are as follows:
1. The number of trains has been restricted to 5.
2. The booking is open only for next seven days from the current date.
3. Only two categories of tickets can be booked, namely, AC and General.
4. The total number of tickets that can be booked in each category (AC and General) is 10.
5. The total number of tickets that can be given the status of waiting is 2.

The Oxford College of Engineering Page 67

DBMS LAB WITH MINI PROJECT 18CSL58

6. The in‐between stoppage stations and their bookings are not considered.

Description of Tables and Procedures

Tables and procedures that will be created are as follows:
1. Train List: This table consists of details about all the available trains. The information stored
in this table includes train number, train name, source, destination, fair for AC ticket, fair for
general ticket, and weekdays on which train is available.
Constraint: The train number is unique.
2. Train Status: This table consists of details about the dates on which ticket can be booked for a
train and the status of the availability of tickets. The information stored in this table includes
train number, train date, total number of AC seats, total number of general seats, number of AC
seats booked, and number of general seats booked.
Constraint: Train number should exist in Train List table.
3. Passenger: This table consists of details about the booked tickets. The information stored in
this table includes ticket ID, train number, date for which ticket is booked, name, age, sex and
address of the passenger, status of reservation (either confirmed or waiting), and category for
which ticket is booked.
Constraint: Ticket ID is unique and the train number should exist in Train List table.
4. Booking: In this procedure, the train number, train date, and category is read from the
passenger. On the basis of the values provided by the passenger, corresponding record is
retrieved from the Train_Status table. If the desired category is AC, then total number of AC
seats and number of booked AC seats are compared in order to find whether ticket can be booked
or not. Similarly, it can be checked for the general category. If ticket can be booked, then
passenger details are read and stored in the Passenger table.
E‐R diagram

Viva Questionsand answers

1. What is SQL?

The Oxford College of Engineering Page 68

DBMS LAB WITH MINI PROJECT 18CSL58

Structured Query Language

2. What is database?
A database is a logically coherent collection of data with some inherent meaning,
representing some aspect of real world and which is designed, built and populated with data
for a specific purpose.
3. What is DBMS?
It is a collection of programs that enables user to create and maintain a database. In other
words it is general-purpose software that provides the users with the processes of defining,
constructing and manipulating the database for various applications.
4. What is a Database system?
The database and DBMS software together is called as Database system.
5. Advantages of DBMS?
⮚ Redundancy is controlled.
⮚ Unauthorized access is restricted.
⮚ Providing multiple user interfaces.
⮚ Enforcing integrity constraints.
⮚ Providing backup and recovery.
6. Disadvantage in File Processing System?
⮚ Data redundancy & inconsistency.
⮚ Difficult in accessing data.
⮚ Data isolation.
⮚ Data integrity.
⮚ Concurrent access is not possible.
⮚ Security Problems.
7. Describe the three levels of data abstraction?
There are three levels of abstraction:
⮚ Physical level: The lowest level of abstraction describes how data are stored.
⮚ Logical level: The next higher level of abstraction, describes what data are stored in
database and what relationship among those data.
⮚ View level:The highest level of abstraction describes only part of entire database.
8. Define the "integrity rules"

The Oxford College of Engineering Page 69

DBMS LAB WITH MINI PROJECT 18CSL58

There are two Integrity rules.

⮚ Entity Integrity:States that “Primary key cannot have NULL value”
⮚ Referential Integrity:States that “Foreign Key can be either a NULL value or
should be Primary Key value of other relation.
9. What is extension and intension?
Extension - It is the number of tuples present in a table at any instance. This is time
dependent.
Intension -It is a constant value that gives the name, structure of table and the constraints laid
on it.
10. What is Data Independence?
Data independence means that “the application is independent of the storage structure and
access strategy of data”. In other words, The ability to modify the schema definition in one level
should not affect the schema definition in the next higher level.
Two types of Data Independence:
⮚ Physical Data Independence: Modification in physical level should not affect the
logical level.
⮚ Logical Data Independence: Modification in logical level should affect the view
level.
NOTE: Logical Data Independence is more difficult to achieve
11. What is a view? How it is related to data independence?
A view may be thought of as a virtual table, that is, a table that does not really exist in its
own right but is instead derived from one or more underlying base table. In other words, there is
no stored file that direct represents the view instead a definition of view is stored in data
dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can
insulate users from the effects of restructuring and growth in the database. Hence accounts for
logical data independence.
12. What is Data Model?
A collection of conceptual tools for describing data, data relationships data semantics and
constraints.
13. What is E-R model?

The Oxford College of Engineering Page 70

DBMS LAB WITH MINI PROJECT 18CSL58

This data model is based on real world that consists of basic objects called entities and of
relationship among these objects. Entities are described in a database by a set of attributes.
14. What is Object Oriented model?
This model is based on collection of objects. An object contains values stored in instance
variables within the object. An object also contains bodies of code that operate on the object.
These bodies of code are called methods. Objects that contain same types of values and the same
methods are grouped together into classes.
15. What is an Entity?
It is an 'object' in the real world with an independent existence.
16. What is an Entity type?
It is a collection (set) of entities that have same attributes.
17. What is an Entity set?
It is a collection of all entities of particular entity type in the database.
18. What is an Extension of entity type?
The collections of entities of a particular entity type are grouped together into an entity
set.
19. What is an attribute?
It is a particular property, which describes the entity.
20. What is a Relation Schema and a Relation?
A relation Schema denoted by R(A1, A2, …, An) is made up of the relation name
R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r
be the relation which contains set tuples (t1, t2, t3, ...,tn). Each tuple is an ordered list of n-
values t=(v1,v2, ..., vn).
21. What is degree of a Relation?
It is the number of attribute of its relation schema.
22. What is Relationship?
It is an association among two or more entities.
23. What is Relationship set?
The collection (or set) of similar relationships.
24. What is Relationship type?

The Oxford College of Engineering Page 71

DBMS LAB WITH MINI PROJECT 18CSL58

Relationship type defines a set of associations or a relationship set among a given set of
entity types.
25. What is degree of Relationship type?
It is the number of entity type participating.

SQL Questions:
1. Which is the subset of SQL commands used to manipulate Oracle Database structures,
including tables?
Data Definition Language (DDL)
2. What operator performs pattern matching?
LIKE operator
3. What operator tests column for the absence of data?
IS NULL operator
4. Which command executes the contents of a specified file?
START <filename> or @<filename>
5. What is the parameter substitution symbol used with INSERT INTO command?
&
6. Which command displays the SQL command in the SQL buffer, and then executes
it?
RUN
7. What are the wildcards used for pattern matching?
For single character substitution and % for multi-character substitution
8. State true or false. EXISTS, SOME, ANY are operators in SQL.
True
9. State true or false. !=, <>, ^= all denote the same operation.
True
10. What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all
11. What command is used to get back the privileges offered by the GRANT command?
REVOKE

The Oxford College of Engineering Page 72

DBMS LAB WITH MINI PROJECT 18CSL58

12. Which system tables contain information on privileges granted and privileges
obtained?
USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD
13. Which system table contains information on constraints on all the tables created?
USER_CONSTRAINTS
14. TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP.
15. What the difference is between TRUNCATE and DELETE commands?
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence
DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back.
WHERE clause can be used with DELETE and not with TRUNCATE.
16. What command is used to create a table by copying the structure of another table?
Answer:
CREATE TABLE AS SELECT command
Explanation:
To copy only the structure, the WHERE clause of the SELECT command should contain
a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE
1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be
copied to the new table.
17. What will be the output of the following query?
SELECT REPLACE (TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN',
'**'),'*','TROUBLE') FROM DUAL;
TROUBLETHETROUBLE
18. What will be the output of the following query?
SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );
Answer : NO
Explanation :

The Oxford College of Engineering Page 73

DBMS LAB WITH MINI PROJECT 18CSL58

The query checks whether a given string is a numerical digit.

19. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;
This displays the total salary of all employees. The null values in the commission
column will be replaced by 0 and added to salary.
20. Which date function is used to find the difference between two dates?
MONTHS_BETWEEN
21. Why does the following command give a compilation error?
DROP TABLE &TABLE_NAME;
Variable names should start with an alphabet. Here the table name starts with an '&'
symbol.
22. What is the advantage of specifying WITH GRANT OPTION in the GRANT
command?
The privilege receiver can further grant the privileges he/she has obtained from the
owner to any other user.

23. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.
24. What is the value of ‘comm’ and ‘sal’ after executing the following query if the
initial value of ‘sal’ is 10000?
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;
sal = 11000, comm = 1000
25. What is the use of DESC in SQL?
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from
table in descending order.The query SELECT * FROM EMP ORDER BY ENAME DESC will
display the output sorted on ENAME in descending order.
26. What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even
when a child table exists.

SQL HANDSON

The Oxford College of Engineering Page 74

DBMS LAB WITH MINI PROJECT 18CSL58

Questions Based On Data Definition Language Commands:

1. Create A Table EMP And DEPT Using The Following Information.

a. DEPT:
COLUMN NAME DATATYPE(SIZE)
-----------------------------------------------------------------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(14)

b. EMP:
COLUMN NAME DATATYPE(SIZE)
-----------------------------------------------------------------------------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
2. Check the Default Size of a Number, Char and Date Data types.
3. Describe the Structure of the Table and EMP Table.
4. Add two columns to the table EMP with the following information in one single
ALTER COMMAND.
COLUMN NAME DATATYPE(SIZE)
-----------------------------------------------------------------------------------------------
SEX CHAR(1)
PLACE CHAR(15)
5. Modify the column job present in the EMP table with the following information
given below:

The Oxford College of Engineering Page 75

DBMS LAB WITH MINI PROJECT 18CSL58

COLUMN NAME DATATYPE(SIZE)

-----------------------------------------------------------------------------------------------
JOB VARCHAR2(15)
6. modify the column ENAME present in the EMP table with the following
information given below:
COLUMN NAME DATATYPE(SIZE)
-----------------------------------------------------------------------------------------------
ENAME CHAR(15)
7. Decrease the size for the column EMPNO with the following information:-
COLUMN NAME DATATYPE(SIZE)
-----------------------------------------------------------------------------------------------
EMPNO NUMBER(2)
8. Modify the column name of EMPNO to EMPLOYEE_NUMBER present in the
EMP table verify the result.
9. Add a new column nationality placed between JOB and MGR columns and verify
the result
10. Drop the table DEPT and EMP.
11. What is the data type of the column HIREDATE and how many bytes it occupies.
12. Create a table EMP and DEPT using the following information.
a.DEPT:
COLUMN NAME DATATYPE(SIZE)
-----------------------------------------------------------------------------------------------
DEPTNO NUMBER(2) CONSTRAINT PK_DEPTNO
PRIMARY KEY
DNAME VARCHAR2(14)
LOC VARCHAR2(14)

b.EMP:
COLUMN NAME DATATYPE(SIZE)
-----------------------------------------------------------------------------------------------

The Oxford College of Engineering Page 76

DBMS LAB WITH MINI PROJECT 18CSL58

EMPNO NUMBER(4) CONSTRAINT PK_EMPNO

PRIMARY KEY
ENAME VARCHAR2(10) CONSTRAINT UQ_DEPTNO
UNIQUE
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE DEFAULT SYSDATE
SAL NUMBER(7,2) CONSTRAINT CK_SAL
CHECK(SAL)
COMM NUMBER(7,2)
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO
REFERENCE
13. Select all the constraints in the EMP table
SOL: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME=’EMP’;
14. select the owner, constraints name, constraints type, table name, status for DEPT
table
SOL: SELECT OWNER, CONSTRAINTS_ NAME, CONSTRAINTS_TYPE, TABLE_
NAME, STATUS FROM USER_CONSTRAINTS WHERE
TABLE_NAME=’DEPT’;
15. Drop the constraints UQ_FMANE from EMP table
SOL: ALTER TABLE EMP DROP CONSTRAINT UQ_FNAME;
16. Add a new column PINCODE with not null constraints to the existing table DEPT
SOL: ALTER TABLE DEPT ADD(PINCODE NUMBER(6) NOT NULL);
17. Disable the constraints PK_DEPTNO present in the DEPT table
SOL: ALTER TABLE DEPT DISABLE CONSTRAINTS PK_DEPTNO;
18. Enable the constraints PK_DEPTNO which is defined in the DEPTNO column of
DEPT table;
SOL: ALTER TABLE DEPT ENABLE CONSTRAINTS PK_DEPTNO;
19. Insert the given values into the tables:
EMP:
(i) 7369, SMITH, CLERK, 7902, 17 – DEC – 80, 800, NULL, 20

The Oxford College of Engineering Page 77

DBMS LAB WITH MINI PROJECT 18CSL58

(ii) 7499, ALLEN SALEMAN, 7698, 20 – FEB – 81, 1600, 300, 30

(iii) 7521, WARD, SALESMAN, 7698, 22 – FEB – 81, 1250, 500, 30
(iv) 7566, JONES, MANAGER, 7839, 02 – APR – 81, 2975, NULL, 20
(v) 7654, MARTIN, SALESMAN, 7698, 28 – SEP – 81, 1250, 1400, 30
(vi) 7698, BLAKE, MANAGER, 7839, 01 – MAY – 81, 2850, NULL, 30
(vii) 7782, CLERK, MANAGER, 7839, 09 – JUN – 81, 2450, NULL, 10
(viii) 7788, SCOTT, ANALYST, 7566, 19 – NOV – 96, 3000, NULL, 20
(ix) 7839, KING, PRESIDENT, NULL, 17 – NOV – 81, 5000, NULL, 10
(x) 7844, TURNER, SALESMAN, 7698, 08 –SEP – 81, 1500, 0, 30
(xi) 7876, ADAMS, CLERK , 7788, 23 – DEC – 96, 1100, NULL, 20
(xii) 7900, JAMES, CLERK, 7698, 03 – DEC – 81, 950, NULL, 30
(xiii) 7902, FORD, ANALYST, 7566, 03 – DEC – 81, 3000, NULL, 20
(xiv) 7934, MILLER, CLERK, 7782, 23 – JAN – 82, 1300, NULL, 10
(xv) 7943, JOHN, CLERK, 7943, 10 – DEC – 83, 2000, NULL, 50
DEPT:
(i) 10, ACCOUNTING, NEW YORK
(ii) 20, RESEARCH, DALLAS
(iii) 30, SALES, CHICAGO
(iv) 40, OPERATIONS, BOSTON
(v) 50, COMPUTER, AMERICA
20. Insert only the records of employee number, name, salary into EMP table
SOL: INSERT INTO EMP (EMPNO, ENAME, SAL) VALUES (‘7955’, ‘PAUL’,
1200);
21. insert two rows into EMP table using parameter substitution
SOL: INSERT INTO EMP VALUES (&EMPNO, ‘&ENAME’, ‘&JOB’, &MGR,
‘&IIIREDATE’, &SAL, &COMM, &DEPTNO);
22. insert the current transaction date temporary table
SOL: INSERT INTO TEMP VALUES (SYSDATE);

Problems on Select Command:

The Oxford College of Engineering Page 78

DBMS LAB WITH MINI PROJECT 18CSL58

23. List the Information of all Employees

SOL: SELECT * FROM EMP;
24. List the information of all the departments
SOL: SELECT * FROM DEPT;
25. LIST THE DEPARTMENT NUMBERS, EMPLOYEE NUMBERS AND THEIR
MANAGERS NUMBERS
SOL: SELECT DEPT. DEPTNO, EMP. EMPNO, EMP. MGR FROM EMP, DEPT;
26. List department name and locations from DEPT table
SOL: SELECT DNAME, LOC, FROM DEPT;
27. List the information of employees and their departments in a single DMI command
SOL: SELECT *. FROM EMP;
28. Copy all the records of their columns EMPNO, ENAME, JOB from EMP table and
insert the records into a temp table with column names same as EMPNO, ENAME,
JOB
SOL: INSERT INTO TEMP (EMPNO, ENAME, JOB) SELECT EMPNO, NAME, JOB
29. List the details of both the tables
SOL: SELECT * FROM EMP, DEPT;
30. List the information of all the employees present in the user named SCOTT
SOL: SELECT * FROM SCOTT. EMP;
31. List the information of the departments from your BATCHMATES DEPT table
SOL: SELECT * FROM ORA252P. DEPT;
32. List out the table names in your schema
SOL: SELECT * FROM TAB;
33. List all the system tables
SOL: SELECT *FROM SYS. DICTIONARY;
34. Get the information of the maximum available blocks allotted to a particular user for
creating tables from the system tables
SOL: SELECT * FROM USER_TS_QUOTAS;
35. List out all the privileges given to a particular user
36. List out all the tables which start with ‘S’

The Oxford College of Engineering Page 79

DBMS LAB WITH MINI PROJECT 18CSL58

SOL: SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME

LIKE ‘S%’;
37. Copy the structure of dept table alone to a temporary table called TEMP1
SOL: CREAT TABLE TEMP1, AS SELECT * FROM DEPT WHERE 1=2;

Problems on update command:

38. Update the salary by 10% hike to analysts working in department number 20 and 30
SOL: UPDATE EMP SET SAL = SAL + 0. 1 WHERE DEPTNO IN (10,20) AND JOB
=‘ANALYST’;
39. Give 5% raise in salary to all the salesman
SOL: UPDATE EMP SET SAL=SAL+.5 WHERE JOB = ‘SALESMAN’;
40. PROMOTE ALL THE EMPLOYEES DISCRIBED AS SALESMAN TO SALES
OFFICER IF THEIR GROSS SALARY PER MOUNTH IS GREATER THEN 3000
SOL: UPDATE EMP SET JOB = ‘SALESOFFICER’ WHERE JOB = ‘SALESMAN’
AND SAL>3000;
41. Give all the employees of commission of rs500
SOL: UPDATE EMP SET COMM = 500;
42. Change the department of JAMES to 20
SOL: UPDATE EMP SET DEPTNO = 20 WHERE ENAME = ‘JAMES’;
43. Calculate all the employees total salary with commission
SOL: SELECT SALES SAL +NVL (COMM) “TOTAL” FROM EMP;

Problems on delete command:

44. Delete all the records of employees
SOL: DELETE FROM EMP;
45. Get back the original records of employees back
SOL: ROLLBACK;
46. Allen’s record only
SOL: DELETE FROM EMP WHERE ENAME = ‘ALLEN’;
47. Delete records of ename column only and verify it
SOL: NOT POOSIBLE

The Oxford College of Engineering Page 80

DBMS LAB WITH MINI PROJECT 18CSL58

48. Delete the records of employee number 7782

SOL: DELETE FROM EMP WHERE EMPNO=7782;
49. Delete the employee’s records who doesn’t have commission
SOL: DELETE FROM EMP WHERE COMM IS NULL;
50. Get back the original records back
SOL: ROLLBACK;
51. Delete the duplicate records of the employee table
SOL: DELETE FROM EMP A WHERE ROWID<> (SELECT MIN (ROWID) FROM
EMP B WHERE A. EMPNO=B. EMPNO);
52. Delete the first five records of employee table
SOL: DELETE FROM EMP X WHERE 5 > (SELECT COUNT (ROWID) FROM EMP
Y WHERE Y. ROWID < X.ROWID);
53. Delete the rows of the temp table permanently
SOL: TURNCATE TABLE TEMP;

Problems on transactional commands:

54. Update a record of EMP table and save the changes permanently in the database
SOL: UPDATE EMP SET SAL=SAL+100 WHERE EMPNO=100; COMMIT;
55. Sql * plus has the facility to automatically save all the records without issuing the TCL
command which is that?
SOL: SET AUTOCOMMIT ON
56. Give all the privileges you have of a database object to another
SOL: GRANT ALL ON EMP TO ORA253A;
57. Give only select, insert privileges to another user
SOL: GRANT SELECT, INSERT ON EMP TO ORA267A;
58. List the user’s id and which database object you have granted
SOL:SELECT * FROM USE_TAB_PRIVS;

The Oxford College of Engineering Page 81

DBMS LAB WITH MINI PROJECT 18CSL58

VII. References
1. Database systems Models, Languages, Design and Application Programming, RamezElmasri
and Shamkant B. Navathe, 6th Edition, Pearson.

2. Database management systems, Ramakrishnan, and Gehrke, 3rd Edition, 2014,McGraw Hill

3. SilberschatzKorth and Sudharshan: Database System Concepts, 6th Edition, Mc-Graw Hill,
2013.

4. Coronel, Morris, and Rob, Database Principles Fundamentals of Design,Implementation and

Management, Cengage Learning 2012.

The Oxford College of Engineering Page 82