BANGALORE INSTITUTE OF TECHNOLOGY

K.R. ROAD, V.V PURAM, BANGALORE – 560 004

DEPARTMENT OF INFORMATION SCIENCE AND ENGINEERING

(AFFILIATED TO VTU, BELAGAVI)

DBMS LABORATORY WITH MINI PROJECT

SUBJECT CODE: 17CSL58

As per Choice Based Credit System Scheme (CBCS)

FOR V SEMESTER ISE AS PRESCRIBED BY VTU

(Effective from the academic year 2017-2018)

Prepared By:
Prof. Chethana M
Dept. of ISE, BIT
 DBMS Laboratory with Mini Project [17CSL58]

DBMS LABORATORY WITH MINI PROJECT

[As per Choice Based Credit System (CBCS) scheme]
(Effective from the academic year 2017 -2018)
SEMESTER – V
Subject Code 17CSL58 IA Marks 40
Number of Lecture Hours/Week 01I + 02P Exam Marks 60
Total Number of Lecture Hours 40 Exam Hours 03
CREDITS – 02
Course objectives: This course will enable students to
Foundation knowledge in database concepts, technology and practice to groom
Students into well-informed database application developers.
Strong practice in SQL programming through a variety of database problems.
Develop database applications using front-end tools and back-end DBMS.
PART-A: SQL Programming (Max. Exam Marks. 50)
Design, develop, and implement the specified queries for the following problems
using Oracle, MySQL, MS SQL Server, or any other DBMS under LINUX/Windows
environment.
Create Schema and insert at least 5 records for each table. Add appropriate
database constraints.
PART-B: Mini Project (Max. Exam Marks. 30)
Use Java, C#, PHP, Python, or any other similar front-end tool. All
applications must be demonstrated on desktop/laptop as a stand-alone or web
based application (Mobile apps on Android/IOS are not permitted.)
Lab Experiments:
1. 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. 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.

2. 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)

Department of ISE Page 1

 DBMS Laboratory with Mini Project [17CSL58]

Write SQL queries to

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

2. Find the name and numbers of all salesman who had more than one customer.
3. List all the 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.
3. 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)
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.
4. 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.
5. 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)

Department of ISE Page 2

 DBMS Laboratory with Mini Project [17CSL58]

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 salaries if every employee working on the ‘IoT’ project is
given a 10 percent raise.
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
4. Retrieve the name of each employee who works on all the projects controlled by
department number 5 (use NOT EXISTS operator).
6. 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.
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.
Course outcomes: The students should be able to:
Create, Update and query on the database.
Demonstrate the working of different concepts of DBMS
Implement, analyze and evaluate the project developed for an application.
Conduction of Practical Examination:
1. All laboratory experiments from part A are to be included for practical examination.
2. Mini project has to be evaluated for 30 Marks.
3. Report should be prepared in a standard format prescribed for project work.
4. Students are allowed to pick one experiment from the lot.
5. Strictly follow the instructions as printed on the cover page of answer script.
6. Marks distribution:
a) Part A: Procedure + Conduction + Viva:10 + 35 +5 =50 Marks
b) Part B: Demonstration + Report + Viva voce = 15+10+05 = 30 Marks
7. Change of experiment is allowed only once and marks allotted to the procedure part to
be made zero.

Department of ISE Page 3

 DBMS Laboratory with Mini Project [17CSL58]

DBMS – Overview
Database is a collection of related data and data is a collection of facts and figures that can
be processed to produce information.
A Relational database management system (RDBMS) is a database management system
(DBMS) that is based on the relational model as introduced by E. F. Code.
Mostly data represents recordable facts. Data aids in producing information, which is based
on facts. For Example, if we have data about marks obtained by all students, we can then
conclude about toppers and average marks.
A database management system stores data in such a way that it becomes easier to retrieve,
manipulate, and produce information.

SQL Constraints:

Constraints are the rules enforced on data columns on a table. These are used to limit the type
of data that can go into a table. This ensures the accuracy and reliability of the data in the
database.

Constraints can either be column level or table level. Column level constraints are applied
only to one column whereas; table level constraints are applied to the entire table.

Following are some of the most commonly used constraints available in SQL −

 NOT NULL Constraint − Ensures that a column cannot have a NULL value.
 DEFAULT Constraint − Provides a default value for a column when none is specified.
 UNIQUE Constraint − Ensures that all the values in a column are different.
 PRIMARY Key − uniquely identifies each row/record in a database table.
 FOREIGN Key − uniquely identifies a row/record in any another database table.
 CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy
certain conditions.
 INDEX − Used to create and retrieve data from the database very quickly.

Data Integrity:

The following categories of data integrity exist with each RDBMS are:

 Entity Integrity − There are no duplicate rows in a table.

 Domain Integrity − Enforces valid entries for a given column by restricting the type,
the format, or the range of values.
 Referential integrity − Rows cannot be deleted, which are used by other records.
 User-Defined Integrity − Enforces some specific business rules that do not fall into
entity, domain or referential integrity.

Department of ISE Page 4

 DBMS Laboratory with Mini Project [17CSL58]

DDL, DML, DCL and TCL Commands

SQL commands are mainly categorized into four categories as discussed below:
1. DDL (Data Definition Language) : DDL or Data Definition Language actually consists of
the SQL commands that can be used to define the database schema. It simply deals with
descriptions of the database schema and is used to create and modify the structure of database
objects in database.

Example: of DDL commands:

CREATE – is used to create the database or its objects (like table, index, function, views,
store procedure and triggers).
Syntax:
CREATE DATABASE databasename;

Example: CREATE DATABASE testDB;

Tip: Make sure you have admin privilege before creating any database. Once a database is
created, you can check it in the list of databases with the following SQL command: SHOW
DATABASES;

The CREATE TABLE statement is used to create a new table in a database.

Syntax:
CREATE TABLE table_name
(
column1 data_type(size),
column2 data_type(size),
....
);

Example: CREATE TABLE PERSONS

(
PERSONID INT,
LASTNAME VARCHAR(15),
FIRSTNAME VARCHAR(15),
ADDRESS VARCHAR(15),
CITY VARCHAR(15)
);

FOREIGN KEY on CREATE TABLE

The FOREIGN KEY constraint is a key used to link two tables together. A FOREIGN KEY is
a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

Department of ISE Page 5

 DBMS Laboratory with Mini Project [17CSL58]

The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders"
table is created:

Example:

CREATE TABLE ORDERS

(
ORDERID INT ,
ORDERNUMBER INT NOT NULL,
PERSONID INT,
PRIMARY KEY (ORDERID),
FOREIGN KEY (PERSONID) REFERENCES PERSONS(PERSONID)
);

OR

CREATE TABLE ORDERS

(
ORDERID INT PRIMARY KEY,
ORDERNUMBER INT NOT NULL,
PERSONID INT FOREIGN KEY REFERENCES PERSONS(PERSONID)
);

DROP – is used to delete objects from the database. The DROP DATABASE statement is
used to drop an existing SQL database.

Syntax:
DROP DATABASE databasename;

Note: Be careful before dropping a database. Deleting a database will result in loss of
complete information stored in the database!
Example:
DROP DATABASE testDB;

Tip: Make sure you have admin privilege before dropping any database. Once a database is
dropped, you can check it in the list of databases with the following SQL command: SHOW
DATABASES;

The DROP TABLE statement is used to drop an existing table in a database.

Syntax:
DROP TABLE table_name;

Note: Be careful before dropping a table. Deleting a table will result in loss of complete
information stored in the table!

Department of ISE Page 6

 DBMS Laboratory with Mini Project [17CSL58]

Example:

DROP TABLE Shippers;

ALTER TABLE - is used to alter the structure of the database.

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing
table.

ALTER TABLE - ADD COLUMN: To add a column in a table, use the following Syntax:

ALTER TABLE table_name ADD column_name datatype;

Example:
The following SQL adds an "Email" column to the "Customers" table:

ALTER TABLE Customers ADD Email varchar(25);

ALTER TABLE - DROP COLUMN: To delete a column in a table, use the following
Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example:
The following SQL deletes the "Email" column from the "Customers" table:

ALTER TABLE Customers DROP COLUMN Email;

ALTER TABLE - ALTER/MODIFY COLUMN: To change the data type of a column in a

table, use the following Syntax:

ALTER TABLE table_name ALTER/MODIFY COLUMN column_name datatype;

Example:
To change the data type of the column named "DateOfBirth" in the "Persons" table.

ALTER TABLE Persons ALTER/MODIFY COLUMN DateOfBirth date;

Notice that the "DateOfBirth" column is now of type date.

ALTER TABLE - NOT NULL: To create a NOT NULL constraint on the "Age" column
when the "Persons" table is already created, use the following SQL:

Example:
ALTER TABLE Persons MODIFY Age int NOT NULL;

Department of ISE Page 7

 DBMS Laboratory with Mini Project [17CSL58]

UNIQUE Constraint on ALTER TABLE: To create a UNIQUE constraint on the "ID"

column when the table is already created, use the following SQL:

Example:

ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID, LastName);

DROP a UNIQUE Constraint : To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons DROP CONSTRAINT UC_Person;

PRIMARY KEY on ALTER TABLE: To create a PRIMARY KEY constraint on the "ID"
column when the table is already created, use the following SQL:

Example:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Note: If you use the ALTER TABLE statement to add a primary key, the primary key
column(s) must already have been declared to not contain NULL values (when the table was
first created).

DROP a PRIMARY KEY Constraint: To drop a PRIMARY KEY constraint, use the
following SQL:

ALTER TABLE Persons DROP CONSTRAINT PK_Person;

FOREIGN KEY on ALTER TABLE: To create a FOREIGN KEY constraint on the

"PersonID" column when the "Orders" table is already created, use the following SQL:

ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

DROP a FOREIGN KEY Constraint: To drop a FOREIGN KEY constraint, use the following
SQL:

ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;

TRUNCATE– TRUNCATE TABLE: is used to remove all records from a table, including
all spaces allocated for the records are removed.The TRUNCATE TABLE statement is used to
delete the data inside a table, but not the table itself. Syntax would be as follows:

TRUNCATE TABLE table_name;

COMMENT –is used to add comments to the data dictionary. Syntax would be as follows:

SELECT * FROM /* Customers; */

RENAME – is used to rename an object existing in the database. Sometimes we may want to
rename our table to give it a more relevant name. For this purpose we can use ALTER
TABLE to rename the name of table.

Department of ISE Page 8

 DBMS Laboratory with Mini Project [17CSL58]

ALTER TABLE table_name RENAME TO new_table_name;

Example: To change the name of the table Student to Student_Details
ALTER TABLE Student RENAME TO Student_Details;

2. DML (Data Manipulation Language): The SQL commands that deals with the
manipulation of data present in database belong to DML or Data Manipulation Language and
this includes most of the SQL statements.

SELECT – is used to retrieve data from the database. The SELECT statement is used to select
data from a database. The data returned is stored in a result table, called the result-set. Syntax
would be as follows:

SELECT column1, column2….FROM table_name;

Example:

SELECT CustomerName, City FROM Customers;

If you want to select all the fields available in the table, use the following Syntax:

SELECT * FROM table_name;

Example:

SELECT * FROM Customers;

INSERT – is used to insert data into a table. It is possible to write the INSERT INTO
statement in two ways. The first way specifies both the column names and the values to be
inserted:

INSERT INTO table_name (column1, column2, ...)VALUES (value1, value2, ...);

Example:

The following SQL statement inserts a new record in the "Customers" table:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,

Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

If you are adding values for all the columns of the table, you do not need to specify the column
names in the SQL query. However, make sure the order of the values is in the same order as
the columns in the table. The INSERT INTO Syntax would be as follows:

INSERT INTO table_name VALUES (value1, value2, value3, ...);

Department of ISE Page 9

 DBMS Laboratory with Mini Project [17CSL58]

Example:

The following SQL statement will insert a new record, but only insert data in the
"CustomerName", "City", and "Country" columns (CustomerID will be updated
automatically):

INSERT INTO Customers (CustomerName,City,Country)

VALUES ('Cardinal', 'Stavanger', 'Norway');

UPDATE – is used to update existing data within a table. The UPDATE statement is used to
modify the existing records in a table. Syntax would be as follows:

UPDATE table_nameSET column1 = value1, column2 = value2, ... WHERE condition;

Example:

The following SQL statement updates the first customer (CustomerID = 1) with a new contact
person and a new city.

UPDATE Customers SET ContactName = 'Alfred ', City= 'Delhi' WHERE CustomerID = 1;

DELETE – is used to delete records from a database table.The DELETE statement is used to
delete existing records in a table. Syntax would be as follows:

DELETE FROM table_name WHERE condition;

Example:

The following SQL statement deletes the customer "Alfred" from the "Customers" table:

DELETE FROM Customers WHERE CustomerName=' Alfred ';

3. DCL (Data Control Language): DCL includes commands such as GRANT and
REVOKE which mainly deals with the rights, permissions and other controls of the database
system.

GRANT-gives user’s access privileges to database. Grant Command is used for offering
access or privileges to the users on the objects of the database. Through this command, the
users get access to the privileges in the database. Syntax would be as follows:

GRANT privilege_name
ON object_name
TO {user_name I PUBLIC I role_name}
[WITH GRANT OPTION];

REVOKE- withdraws user’s access privileges given by using the GRANT command. The
main purpose of the revoke command is canceling the previously denied or granted
permissions. Through the revoke command, the access to the given privileges can be
withdrawn. In simple words, the permission can be taken back from the user with this
command. Syntax would be as follows:

Department of ISE Page 10

 DBMS Laboratory with Mini Project [17CSL58]

REVOKE<privilege list>
ON <relation name or view name>
From <user name>

Example:

REVOKE UPDATE
ON worker
FROM MNO;
4. TCL (transaction Control Language): TCL commands deals with the transaction
within the database. TCL commands are: COMMIT, ROLLBACK, SAVEPOINT, SET
TRANSACTION.

COMMIT– commits a Transaction. The main use of Commit command is to make the
transaction permanent. If there is a need for any transaction to be done in the database that
transaction permanent through commit command. Here is the general Syntax:

COMMIT;

ROLLBACK– rollbacks a transaction in case of any error occurs. If any error occurs with any
of the SQL grouped statements, all changes need to be aborted. The process of reversing
changes is called rollback. This command can only be used to undo transactions since the last
COMMIT or ROLLBACK command was issued. Here is the general Syntax:

ROLLBACK;

SAVEPOINT– sets a savepoint within a transaction. Creates points within the groups of
transactions in which to ROLLBACK. A SAVEPOINT is a point in a transaction in which you
can roll the transaction back to a certain point without rolling back the entire transaction. Here
is the general Syntax:

SAVEPOINT SAVEPOINT_NAME;

SET TRANSACTION– Specify characteristics for the transaction. Places a name on a

transaction. Here is the general Syntax:

SET TRANSACTION [READ WRITE | READ ONLY];

SQL Keywords:
GROUP BY: The GROUP BY command is used to group the result set (used with aggregate
functions: COUNT, MAX, MIN, SUM, AVG). The GROUP BY Statement in SQL is used to
arrange identical data into groups with the help of some functions. i.e if a particular column
has same values in different rows then it will arrange these rows in a group.

The following SQL lists the number of customers in each country, sorted high to low:

Department of ISE Page 11

 DBMS Laboratory with Mini Project [17CSL58]

Example:
SELECT COUNT (CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT (CustomerID) DESC;

HAVING: We cannot use the aggregate functions like SUM(), COUNT() etc. with WHERE
clause. The HAVING command is used instead of WHERE with aggregate functions.

The following SQL lists the number of customers in each country, sorted high to low (Only
include countries with more than 5 customers):

Example:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

ORDER BY: The ORDER BY statement in sql is used to sort the fetched data in either
ascending or descending according to one or more columns. By default ORDER BY sorts the
data in ascending order. We can use the keyword DESC to sort the data in descending order
and the keyword ASC to sort in ascending order. General syntax is:

SELECT * FROM table_name ORDER BY column_name ASC|DESC;

Example:

SELECT * FROM Student ORDER BY Age ASC , ROLL_NO DESC;

Query will fetch all data from the table Student and then sort the result in ascending order
first according to the column Age and then in descending order according to the
column ROLL_NO.

BETWEEN Operator: The SQL BETWEEN condition allows you to easily test if an
expression is within a range of values (inclusive). The values can be text, date, or numbers. It
can be used in a SELECT, INSERT, UPDATE, or DELETE statement. The SQL BETWEEN
Condition will return the records where expression is within the range of value1 and value2.
General syntax is:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example: List all the Employee Fname, Lname who is having salary between 30000 and
4500.

SELECT Fname, Lname

FROM Employee
WHERE Salary
BETWEEN 30000 AND 45000;
Department of ISE Page 12
 DBMS Laboratory with Mini Project [17CSL58]

IN Operator: IN operator allows us to easily test if the expression matches any value in the
list of values. It is used to remove the need of multiple OR condition in SELECT, INSERT,
UPDATE or DELETE. We can also use NOT IN to exclude the rows in your list.
The General syntax is:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (list_of_values);

Example: Find the Fname, Lname of the Employees who have Salary equal to 30000, 40000
or 25000.

SELECT Fname, Lname

FROM Employee
WHERE Salary IN (30000, 40000, 25000);

AND, OR and NOT operators: In SQL, the AND & OR operators are used for filtering the
data and getting precise result based on conditions. The AND and OR operators are used with
the WHERE clause. These two operators are called conjunctive operators.

AND operator: This operator displays only those records where both the
conditions condition1 and condition2 evaluates to True.

OR operator: This operators displays the records where either one of the conditions
condition1 and condition2 evaluates to True. That is, either condition1 is True or condition2
is True.

NOT operator: displays a record if the condition(s) is NOT TRUE.

SELECT * FROM table_name WHERE condition1 AND condition2 and ...conditionN;

UNION Operator: The UNION operator is used to combine the result-set of two or more
SELECT statements.

1. The fields to be used in both the select statements must be in same order, same number
and same data type.
2. The Union clause produces distinct values in the result set, to fetch the duplicate values
too UNION ALL must be used instead of just UNION.

The General syntax is:

Resultant set consists of distinct values.

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

Resultant set consists of duplicate values too.

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM

table2;
Department of ISE Page 13
 DBMS Laboratory with Mini Project [17CSL58]

MINUS Operator: The Minus Operator in SQL is used with two SELECT statements. The
MINUS operator is used to subtract the result set obtained by first SELECT query from the
result set obtained by second SELECT query. In simple words, we can say that MINUS operator will
return only those rows which are unique in only first SELECT query and not those rows which are
common to both first and second SELECT queries. General syntax is:

SELECT column1 , column2 , ... columnN

FROM table_name
WHERE condition
MINUS
SELECT column1 , column2 , ... columnN
FROM table_name
WHERE condition;

EXISTS Operator: The EXISTS condition in SQL is used to check whether the result of a
correlated nested query is empty (contains no tuples) or not. The result of EXISTS is a
Boolean value True or False. The EXISTS operator returns true if the subquery returns one or
more records. It can be used in a SELECT, UPDATE, INSERT or DELETE statement. The
General syntax is:

SELECT column_name(s)
FROM table_name
WHERE EXISTS
( SELECT column_name FROM table_name WHERE condition );

SQL Joins: A SQL Join statement is used to combine data or rows from two or more tables
based on a common field between them. Different types of Joins are: INNER JOIN, LEFT
JOIN, RIGHT JOIN, FULL JOIN. General syntax is:

SELECT table1.column1, table1.column2, table2.column1....

FROM table1
INNER JOIN table2 ON
table1.matching_column = table2.matching_column;

Note: Here matching_column is the Column common to both the tables.

Nested Queries in SQL: In nested queries, a query is written inside a query. In nested
queries, query execution starts from innermost query to outermost queries. The execution of
inner query is independent of outer query, but the result of inner query is used in execution of
outer query. Various operators like IN, NOT IN, ANY, ALL etc are used in writing
independent nested queries.

Views: Views in SQL are kind of virtual tables. A view also has rows and columns as they are
in a real table in the database. We can create a view by selecting fields from one or more
tables present in the database. A View can either have all the rows of a table or specific rows
based on certain condition. We can create View using CREATE VIEW statement. A View
can be created from a single table or multiple tables. General syntax is:

Department of ISE Page 14

 DBMS Laboratory with Mini Project [17CSL58]

CREATE VIEW view_name AS

SELECT column1, column2.....
FROM table_name
WHERE condition;

Example: In this example, we will create a view named DetailsView from the table
StudentDetails.

CREATE VIEW DetailsView AS

SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;

Stored Procedures in PL/SQL: PL/SQL is a block-structured language that enables

developers to combine the power of SQL with procedural statements. A stored procedure in
PL/SQL is nothing but a series of declarative SQL statements which can be stored in the
database catalogue. A procedure can be thought of as a function or a method. They can be
invoked through triggers, other procedures, or applications on Java, PHP etc. All the
statements of a block are passed to Oracle engine all at once which increases processing speed
and decreases the traffic.

Advantages:
 They result in performance improvement of the application. If a procedure is being
called frequently in an application in a single connection, then the compiled version of the
procedure is delivered.
 They reduce the traffic between the database and the application, since the lengthy
statements are already fed into the database and need not be sent again and again via the
application.
 They add to code reusability, similar to how functions and methods work in other
languages such as C/C++ and Java.

Disadvantages:

 Stored procedures can cause a lot of memory usage. The database administrator should
decide an upper bound as to how many stored procedures are feasible for a particular
application.
 MySQL does not provide the functionality of debugging the stored procedures.

A stored procedure is a prepared SQL code that you can save, so the code can be reused over
and over again. So if you have an SQL query that you write over and over again, save it as a
stored procedure, and then just call it to execute it. You can also pass parameters to a stored
procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Stored Procedure Syntax:

CREATE PROCEDURE procedure_name
AS sql_statement
GO;

Department of ISE Page 15

 DBMS Laboratory with Mini Project [17CSL58]

Execute a Stored Procedure:

EXEC procedure_name;

Example: The following SQL statement creates a stored procedure named

"SelectAllCustomers" that selects all records from the "Customers" table:

CREATE PROCEDURE SelectAllCustomers

AS
SELECT * FROM Customers
GO;

Execute the stored procedure above as follows:

EXEC SelectAllCustomers;

Stored Procedure With Multiple Parameters:

Setting up multiple parameters is very easy. Just list each parameter and the data type
separated by a comma as shown below.

Example: The following SQL statement creates a stored procedure that selects Customers
from a particular City with a particular PostalCode from the "Customers" table:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode

nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Execute the stored procedure above as follows:

EXEC SelectAllCustomers City = "London", PostalCode = "WA1 1DP";

SQL Trigger | Student Database

Trigger: A trigger is a stored procedure in database which automatically invokes whenever a

special event in the database occurs. For Example:, a trigger can be invoked when a row is
inserted into a specified table or when certain table columns are being updated.

Syntax:

create trigger [trigger_name]

[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[trigger_body]

Department of ISE Page 16

 DBMS Laboratory with Mini Project [17CSL58]

Explanation of Syntax:
1. create trigger [trigger_name]: Creates or replaces an existing trigger with the
trigger_name.
2. [before | after]: This specifies when the trigger will be executed.
3. {insert | update | delete}: This specifies the DML operation.
4. on [table_name]: This specifies the name of the table associated with the trigger.
5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for
each row being affected.
6. [trigger_body]: This provides the operation to be performed as trigger is fired.

Definition of KEYS in DBMS:

Super Key: A super key is a set of one or more attributes (columns), which can uniquely
identify a row in a table.

Candidate Key: A super key with no redundant attribute is known as candidate key.
Candidate keys are selected from the set of super keys, the only thing we take care while
selecting candidate key is that the candidate key should not have any redundant attributes.
That’s the reason they are also termed as minimal super key.

Primary key: A Primary key is selected from a set of candidate keys. This is done by database
admin or database designer.

Foreign key: are the columns of a table that points to the primary key of another table. They
act as a cross-reference between tables.

Composite key: A key that has more than one attributes is known as composite key. It is also
known as compound key.

Note: Any key such as super key, primary key, candidate key etc. can be called composite key
if it has more than one attributes.

Let’s take an example to understand this:

Table: Employee

Emp_SSN Emp_Number Emp_Name

--------- ---------- --------
123456789 226 Steve
999999321 227 Ajeet
888997212 228 Chaitanya
777778888 229 Robert

Department of ISE Page 17

 DBMS Laboratory with Mini Project [17CSL58]

Super keys: The above table has following super keys. All of the following sets of super key
are able to uniquely identify a row of the employee table.

 {Emp_SSN}
 {Emp_Number}
 {Emp_SSN, Emp_Number}
 {Emp_SSN, Emp_Name}
 {Emp_SSN, Emp_Number, Emp_Name}
 {Emp_Number, Emp_Name}

Candidate Keys: As mentioned in the beginning, a candidate key is a minimal super key with
no redundant attributes. The following two set of super keys are chosen from the above sets as
there are no redundant attributes in these sets.

 {Emp_SSN}
 {Emp_Number}

Only these two sets are candidate keys as all other sets are having redundant attributes that are
not necessary for unique identification.

Primary Key: Either {Emp_SSN} or {Emp_Number} can be chosen as a primary key for the
table Employee.

Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database

Normalization is a process of organizing the data in database to avoid data redundancy,

insertion anomaly, update anomaly & deletion anomaly.

Here are the most commonly used normal forms:

 First normal form(1NF)

 Second normal form(2NF)
 Third normal form(3NF)
 Boyce & Codd normal form (BCNF)

First Normal Form (1NF)

For a table to be in the First Normal Form, it should follow the following 4 rules:

1. It should only have single(atomic) valued attributes/columns.

2. Values stored in a column should be of the same domain
3. All the columns in a table should have unique names.
4. And the order in which data is stored, does not matter.

Department of ISE Page 18

 DBMS Laboratory with Mini Project [17CSL58]

Second Normal Form (2NF)

For a table to be in the Second Normal Form,

1. It should be in the First Normal form.

2. And, it should not have Partial Dependency.

Third Normal Form (3NF)

A table is said to be in the Third Normal Form when,

1. It is in the Second Normal form.

2. And, it doesn't have Transitive Dependency.

Boyce and Codd Normal Form (BCNF)

Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals
with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have
multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF,
following conditions must be satisfied:

1. R must be in 3rd Normal Form

2. and, for each functional dependency ( X → Y ), X should be a super Key.

Department of ISE Page 19

 DBMS Laboratory with Mini Project [17CSL58]

LAB EXPERIMENTS
Program 1:

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. 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.

Creating Table :

CREATE TABLE PUBLISHER

(
NAME VARCHAR2 (20),
PHONE INTEGER,
ADDRESS VARCHAR2 (20),
PRIMARY KEY(NAME)
);

CREATE TABLE BOOK

(
BOOK_ID VARCHAR(8),
TITLE VARCHAR2 (20),
PUBLISHER_NAME VARCHAR(20),
PUB_YEAR INTEGER,
PRIMARY KEY(BOOK_ID),
FOREIGN KEY(PUBLISHER_NAME) REFERENCES PUBLISHER(NAME)
);

CREATE TABLE BOOK_AUTHORS

(
BOOK_ID VARCHAR(8),
AUTHOR_NAME VARCHAR2 (20),
PRIMARY KEY (BOOK_ID,AUTHOR_NAME),
FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON DELETE CASCADE
);
Department of ISE Page 20
 DBMS Laboratory with Mini Project [17CSL58]

CREATE TABLE LIBRARY_BRANCH

(
BRANCH_ID VARCHAR(6),
BRANCH_NAME VARCHAR2 (20),
ADDRESS VARCHAR2 (20),
PRIMARY KEY(BRANCH_ID)
);

CREATE TABLE BOOK_COPIES

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

CREATE TABLE BOOK_LENDING

(
BOOK_ID VARCHAR(8),
BRANCH_ID VARCHAR2(6),
CARD_NO INTEGER,
DATE_OUT DATE,
DUE_DATE DATE,
PRIMARY KEY(BOOK_ID, BRANCH_ID,CARD_NO),
FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON DELETE CASCADE
);

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

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');

SQL> SELECT * FROM PUBLISHER;

NAME PHONE ADDRESS

-------------------- ---------- --------------------
MCGRAW-HILL 9989076587 BANGALORE
PEARSON 9889076565 NEWDELHI
RANDOM HOUSE 7455679345 HYDRABAD
HACHETTE LIVRE 8970862340 CHENAI
GRUPO PLANETA 7756120238 BANGALORE

INSERT INTO BOOK VALUES ('1','DBMS', 'MCGRAW-HILL',2017);

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

Department of ISE Page 21

 DBMS Laboratory with Mini Project [17CSL58]

SQL> SELECT * FROM BOOK;

BOOK_ID TITLE PUBLISHER_NAME PUB_YEAR

-------- -------------------- ------------------- ---------
1 DBMS MCGRAW-HILL 2017
2 ADBMS MCGRAW-HILL 2016
3 CN PEARSON 2016
4 CG GRUPO PLANETA 2015
5 OS PEARSON 2016

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

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

SQL> SELECT * FROM BOOK_AUTHORS ;

BOOK_ID AUTHOR_NAME
-------- --------------------
1 NAVATHE
2 NAVATHE
3 TANENBAUM
4 EDWARD ANGEL
5 GALVIN

INSERT INTO LIBRARY_BRANCH VALUES ('10','VV PURAM','BANGALORE');

INSERT INTO LIBRARY_BRANCH VALUES ('11','BIT','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES ('12','RAJAJI NAGAR', 'BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES ('13','JP NAGAR','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES ('14','JAYANAGAR','BANGALORE');

SQL> SELECT * FROM LIBRARY_BRANCH;

BRANCH BRANCH_NAME ADDRESS

------ ----------------------------- ----------------
10 VV PURAM BANGALORE
11 BIT BANGALORE
12 RAJAJI NAGAR BANGALORE
13 JP NAGAR BANGALORE
14 JAYANAGAR BANGALORE

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

INSERT INTO BOOK_COPIES VALUES ( '1','11', 5);
INSERT INTO BOOK_COPIES VALUES ( '2','12', 2);
INSERT INTO BOOK_COPIES VALUES ( '2','13', 5);
INSERT INTO BOOK_COPIES VALUES ( '3','14', 7);
INSERT INTO BOOK_COPIES VALUES ( '5','10', 1);
Department of ISE Page 22
 DBMS Laboratory with Mini Project [17CSL58]

INSERT INTO BOOK_COPIES VALUES ( '4','11', 3);

SQL> SELECT * FROM BOOK_COPIES;

BOOK_ID BRANCH NO_OF_COPIES

-------- ------ ------------
1 10 10
1 11 5
2 12 2
2 13 5
3 14 7
5 10 1
4 11 3
INSERT INTO BOOK_LENDING VALUES ('1', '10', 101,'01-JAN-17','01-JUN-17');
INSERT INTO BOOK_LENDING VALUES ('3', '14', 101,'11-JAN-17','11-MAR-17' );
INSERT INTO BOOK_LENDING VALUES ('2', '13', 101,'21-FEB-17','21-APR-17');
INSERT INTO BOOK_LENDING VALUES ('4', '11', 101,'15-MAR-17','15-JUL-17');
INSERT INTO BOOK_LENDING VALUES ('1', '11', 104,'12-APR-17','12-MAY-17' )

SQL> SELECT * FROM BOOK_LENDING;

BOOK_ID BRANCH CARD_NO DATE_OUT DUE_DATE

-------- ------ ---------- --------- ---------
1 10 101 01-JAN-17 01-JUN-17
3 14 101 11-JAN-17 11-MAR-17
2 13 101 21-FEB-17 21-APR-17
4 11 101 15-MAR-17 15-JUL-17
1 11 104 12-APR-17 12-MAY-17

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;

OUTPUT:

BOOK_ID TITLE PUBLISHER_NAME AUTHOR_NAME NO_OF_COPIES BRANCH

-------- ------- ----------- ----------- ------------ ------
1 DBMS MCGRAW-HILL NAVATHE 10 10
1 DBMS MCGRAW-HILL NAVATHE 5 11
2 ADBMS MCGRAW-HILL NAVATHE 2 12
2 ADBMS MCGRAW-HILL NAVATHE 5 13
3 CN PEARSON TANENBAUM 7 14
5 OS PEARSON GALVIN 1 10
4 CG GRUPO PLANETA EDWARD ANGEL 3 11

Department of ISE Page 23

 DBMS Laboratory with Mini Project [17CSL58]

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;

OUTPUT:

CARD_NO
----------
101

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

Before Deleting:

SQL> SELECT * FROM BOOK;

BOOK_ID TITLE PUBLISHER_NAME PUB_YEAR

-------- -------------------- -------------------- ---------
1 DBMS MCGRAW-HILL 01-JAN-17
2 ADBMS MCGRAW-HILL 10-JUN-16
3 CN PEARSON 16-SEP-16
4 CG GRUPO PLANETA 11-SEP-15
5 OS PEARSON 23-MAY-16

SQL> SELECT * FROM BOOK_COPIES;

BOOK_ID BRANCH NO_OF_COPIES

-------- ------ ------------
1 10 10
1 11 5
2 12 2
2 13 5
3 14 7
5 10 1
4 11 3

DELETE FROM BOOK WHERE BOOK_ID='3';

Department of ISE Page 24

 DBMS Laboratory with Mini Project [17CSL58]

SQL> SELECT * FROM BOOK;

BOOK_ID TITLE PUBLISHER_NAME PUB_YEAR

-------- -------------------- -------------------- ---------
1 DBMS MCGRAW-HILL 01-JAN-17
2 ADBMS MCGRAW-HILL 10-JUN-16
4 CG GRUPO PLANETA 11-SEP-15
5 OS PEARSON 23-MAY-16

SQL> SELECT * FROM BOOK_COPIES;

BOOK_ID BRANCH NO_OF_COPIES

-------- ------ ------------
1 10 10
1 11 5
2 12 2
2 13 5
5 10 1
4 11 3

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

CREATE TABLE BOOKPART

PARTITION BY RANGE (PUB_YEAR)
(PARTITION P1 VALUES LESS THAN(2016),
PARTITION P2 VALUES LESS THAN (MAXVALUE))
AS SELECT * FROM BOOK;

OUTPUT:

SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS;

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
BOOKPART P2
BOOKPART P1

SQL> SELECT * FROM BOOKPART PARTITION (P1);

BOOK_ID TITLE PUBLISHER_NAME PUB_YEAR

-------- -------------- -------------------- ----------
4 CG GRUPO PLANETA 2015

SQL> SELECT * FROM BOOKPART PARTITION (P2);

BOOK_ ID TITLE PUBLISHER_NAME PUB_YEAR

-------- ------------ -------------------- ----------
1 DBMS MCGRAW-HILL 2017
2 ADBMS MCGRAW-HILL 2016
5 OS PEARSON 2016

Department of ISE Page 25

 DBMS Laboratory with Mini Project [17CSL58]

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

CREATE VIEW BC AS SELECT B.BOOK_ID,C.TITLE,B.BRANCH_ID,

(B.NO_OF_COPIES-(SELECT COUNT(*) FROM BOOK_LENDING WHERE
B.BOOK_ID=BOOK_ID AND B.BRANCH_ID=BRANCH_ID)) AS NO_COPY
FROM BOOK_COPIES B,BOOK C
WHERE B.BOOK_ID=C.BOOK_ID;

OUTPUT:
SQL> SELECT * FROM BC;

BOOK_ID TITLE BRANCH NO_COPY

-------- ------------------ ------ ----------
1 DBMS 10 9
1 DBMS 11 5
2 ADBMS 12 2
2 ADBMS 13 4
5 OS 10 1
4 CG 11 2

Department of ISE Page 26

 DBMS Laboratory with Mini Project [17CSL58]

Program 2:

Consider the following schema for a 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.
Use ON DELETE CASCADE at the end of foreign key definitions while
creating child table orders and then execute the following:

Creating Table :

CREATE TABLE SALESMAN

(
SALESMAN_ID VARCHAR(8),
NAME VARCHAR(20),
CITY VARCHAR(20),
COMMISSION VARCHAR2(10),
PRIMARY KEY(SALESMAN_ID)
);

CREATE TABLE CUSTOMER

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

CREATE TABLE ORDERS

(
ORD_NO VARCHAR(8),
PURCHASE_AMT NUMBER(10, 2),
ORD_DATE DATE,

Department of ISE Page 27

 DBMS Laboratory with Mini Project [17CSL58]

CUSTOMER_ID VARCHAR(8),
SALESMAN_ID VARCHAR(8),
PRIMARY KEY (ORD_NO),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON
DELETE CASCADE,
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN (SALESMAN_ID) ON
DELETE CASCADE
);

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 CUSTOMER VALUES ('C1', 'PREETHI','BANGALORE', 100, '1000');

INSERT INTO CUSTOMER VALUES ('C2', 'VIVEK','MANGALORE', 300, '1000');
INSERT INTO CUSTOMER VALUES ('C3', 'BHASKAR','CHENNAI', 400, '2000');
INSERT INTO CUSTOMER VALUES ('C4', 'CHETHAN','BANGALORE', 200, '2000');
INSERT INTO CUSTOMER VALUES ('C5', 'MAMATHA','BANGALORE', 400, '3000');

INSERT INTO ORDERS VALUES ('O1', 5000, '04-MAY-17', 'C1', '1000');

INSERT INTO ORDERS VALUES ('O2', 6000, '04-MAY-17', 'C1', '1000');
INSERT INTO ORDERS VALUES ('O3', 7000, '04-MAY-17', 'C2', '1000');
INSERT INTO ORDERS VALUES ('O4', 450, '20-JAN-17', 'C1', '2000');
INSERT INTO ORDERS VALUES ('O5', 1000, '24-FEB-17', 'C2','2000');
INSERT INTO ORDERS VALUES ('O6', 3500, '13-APR-17', 'C3', '3000');
INSERT INTO ORDERS VALUES ('O7', 550, '09-MAR-17', 'C4', 2000);
INSERT INTO ORDERS VALUES ('O8', 6500, '04-MAY-17', 'C5', 1000);
INSERT INTO ORDERS VALUES ('O9', 7500, '09-MAR-17', 'C2', 2000);

SELECT * FROM SALESMAN;

SALESMAN NAME CITY COMMISSION

-------- -------------------- -------------------- ----------
1000 JOHN BANGALORE 25%
2000 RAVI BANGALORE 20%
3000 KUMAR MYSORE 15%
4000 SMITH DELHI 30%
5000 HARSHA HYDRABAD 15%

SELECT * FROM CUSTOMER;

CUSTOMER CUST_NAME CITY GRADE SALESMAN

-------- ------------- -------------------- ---------- --------
C1 PREETHI BANGALORE 100 1000
C2 VIVEK MANGALORE 300 1000
C3 BHASKAR CHENNAI 400 2000
C4 CHETHAN BANGALORE 200 2000
C5 MAMATHA BANGALORE 400 3000

Department of ISE Page 28

 DBMS Laboratory with Mini Project [17CSL58]

SELECT * FROM ORDERS;

ORD_NO PURCHASE_AMT ORD_DATE CUSTOMER SALESMAN

-------- ------------ --------- -------- --------
O1 5000 04-MAY-17 C1 1000
O2 6000 04-MAY-17 C1 1000
O3 7000 04-MAY-17 C2 1000
O4 450 20-JAN-17 C1 2000
O5 1000 24-FEB-17 C2 2000
O6 3500 13-APR-17 C3 3000
O7 550 09-MAR-17 C4 2000
O8 6500 04-MAY-17 C5 1000
O9 7500 09-MAR-17 C2 2000

Queries:

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

SELECT GRADE, COUNT (DISTINCT CUSTOMER_ID) AS NO_OF_CUSTOMER

FROM CUSTOMER
GROUP BY GRADE
HAVING GRADE > (SELECT AVG(GRADE)
FROM CUSTOMER
WHERE CITY='BANGALORE');
OUTPUT:
GRADE NO_OF_CUSTOMER
---------- --------------
400 2
300 1

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

SELECT SALESMAN_ID, NAME

FROM SALESMAN S
WHERE ((SELECT COUNT (*)
FROM CUSTOMER
WHERE SALESMAN_ID=S.SALESMAN_ID)>1);
OUTPUT:
SALESMAN NAME
-------- -----
1000 JOHN
2000 RAVI

Department of ISE Page 29

 DBMS Laboratory with Mini Project [17CSL58]

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

SELECT S.SALESMAN_ID, S.CITY

FROM SALESMAN S
WHERE EXISTS (SELECT CITY FROM CUSTOMER WHERE S.CITY=CITY AND
S.SALESMAN_ID=SALESMAN_ID)
UNION
SELECT SALESMAN_ID,'NO MATCH OF CITIES'
FROM SALESMAN S
WHERE NOT EXISTS (SELECT CITY FROM CUSTOMER WHERE S.CITY=CITY
AND S.SALESMAN_ID=SALESMAN_ID);
OUTPUT:
SALESMAN CITY
-------- --------------------
1000 BANGALORE
2000 BANGALORE
3000 NO MATCH OF CITIES
4000 NO MATCH OF CITIES
5000 NO MATCH OF CITIES

4.Create a view that finds the salesman who has the customer with the highest order of a
day.
SELECT DISTINCT S.SALESMAN_ID,S.ORD_DATE FROM ORDERS S
WHERE (SELECT SUM(PURCHASE_AMT)
FROM ORDERS
WHERE SALESMAN_ID=S.SALESMAN_ID AND ORD_DATE=S.ORD_DATE AND
S.CUSTOMER_ID=CUSTOMER_ID)
=(SELECT MAX(SUM(PURCHASE_AMT))
FROM ORDERS S1
WHERE S1.ORD_DATE=S.ORD_DATE GROUP BY
S1.ORD_DATE,S1.SALESMAN_ID,S1.CUSTOMER_ID);
OUTPUT:
SALESMAN ORD_DATE
-------- ---------
1000 04-MAY-17
3000 13-APR-17
2000 20-JAN-17
2000 24-FEB-17
2000 09-MAR-17

Department of ISE Page 30

 DBMS Laboratory with Mini Project [17CSL58]

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:
DELETE FROM SALESMAN
WHERE SALESMAN_ID=1000;

SQL> SELECT * FROM SALESMAN;

SALESMAN NAME CITY COMMISSION
-------- -------------- -------------------- ----------
2000 RAVI BANGALORE 20%
3000 KUMAR MYSORE 15%
4000 SMITH DELHI 30%
5000 HARSHA HYDRABAD 15%
SQL> SELECT * FROM CUSTOMER;
CUSTOMER CUST_NAME CITY GRADE SALESMAN
------- ------------ --------------------- ---------- --------
C1 PREETHI BANGALORE 100
C2 VIVEK MANGALORE 300
C3 BHASKAR CHENNAI 400 2000
C4 CHETHAN BANGALORE 200 2000
C5 MAMATHA BANGALORE 400 3000
SQL> SELECT * FROM ORDERS;
ORD_NO PURCHASE_AMT ORD_DATE CUSTOMER SALESMAN
-------- ------------ -------- -------- --------
O2 450 20-JAN-17 C1 2000
O3 1000 24-FEB-17 C2 2000
O4 3500 13-APR-17 C3 3000

Department of ISE Page 31

 DBMS Laboratory with Mini Project [17CSL58]

Program 3:

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)

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.

Creating Table :

CREATE TABLE ACTOR

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

CREATE TABLE DIRECTOR

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

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)
);

Department of ISE Page 32

 DBMS Laboratory with Mini Project [17CSL58]

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)
);

CREATE TABLE RATING

(
MOV_ID NUMBER (4),
REV_STARS INTEGER,
FOREIGN KEY(MOV_ID) REFERENCES MOVIES(MOV_ID)
);
INSERT INTO ACTOR VALUES (301,'ANUSHKA','F');
INSERT INTO ACTOR VALUES (302,'PRABHAS','M');
INSERT INTO ACTOR VALUES (303,'ARAVIND','M');
INSERT INTO ACTOR VALUES (304,'JERMY','M');
INSERT INTO ACTOR VALUES (305,'KIM NEWMEN','M');

SQL> SELECT * FROM ACTOR;

ACT_ID ACT_NAME ACT_G

---------- -------------------- --------
301 ANUSHKA F
302 PRABHAS M
303 ARAVIND M
304 JERMY M
305 KIM NEWMEN 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 DIRECTOR VALUES (64,'MAHESH', 8989776539);

SQL> SELECT * FROM DIRECTOR;

DIR_ID DIR_NAME DIR_PHONE

---------- -------------------- ----------
60 RAJAMOULI 8751611001
61 HITCHCOCK 7766138911
62 FARAN 9986776531
63 STEVEN SPIELBERG 8989776530
64 MAHESH 8989776539

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,'PSYCHO', 2008, 'ENGLISH', 61);
INSERT INTO MOVIES VALUES (1004,'WAR HORSE', 2011, 'ENGLISH', 63);

Department of ISE Page 33

 DBMS Laboratory with Mini Project [17CSL58]

INSERT INTO MOVIES VALUES (1005,'LAST BUS', 2016, 'KANNADA', 64);

INSERT INTO MOVIES VALUES (1006,'THE BIRDS', 2011, 'ENGLISH', 61);
INSERT INTO MOVIES VALUES (1007,'TITANIC', 2012, 'ENGLISH', 63);

SQL> SELECT * FROM MOVIES;

MOV_ID MOV_TITLE MOV_YEAR MOV_LANG DIR_ID

---------- ---------------------- ---------- ------------ ----------
1001 BAHUBALI-2 2017 TELAGU 60
1002 BAHUBALI-1 2015 TELAGU 60
1003 PSYCHO 2008 ENGLISH 61
1004 WAR HORSE 2011 ENGLISH 63
1005 LAST BUS 2016 KANNADA 64
1006 THE BIRDS 2011 ENGLISH 61
1007 TITANIC 2012 ENGLISH 63

INSERT INTO MOVIE_CAST VALUES (301, 1002, 'HEROINE');

INSERT INTO MOVIE_CAST VALUES (301, 1001, 'HEROINE');
INSERT INTO MOVIE_CAST VALUES (303, 1005, 'HERO');
INSERT INTO MOVIE_CAST VALUES (302, 1002, 'HERO');
INSERT INTO MOVIE_CAST VALUES (302, 1001, 'HERO');
INSERT INTO MOVIE_CAST VALUES (304, 1004, 'HERO');
INSERT INTO MOVIE_CAST VALUES (305, 1005, 'HERO');
INSERT INTO MOVIE_CAST VALUES (305, 1007, 'HERO');

SQL> SELECT * FROM MOVIE_CAST;

ACT_ID MOV_ID ROLE

---------- --------- ----------
301 1002 HEROINE
301 1001 HEROINE
303 1005 HERO
302 1002 HERO
302 1001 HERO
304 1004 HERO
305 1005 HERO
305 1007 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);
INSERT INTO RATING VALUES (1005, 3);
INSERT INTO RATING VALUES (1006, 8);
INSERT INTO RATING VALUES (1007, 0);
INSERT INTO RATING VALUES (1001, 2);
INSERT INTO RATING VALUES (1002, 5);

Department of ISE Page 34

 DBMS Laboratory with Mini Project [17CSL58]

SQL> SELECT * FROM RATING;

MOV_ID REV_STARS
---------- ----------
1001 4
1001 2
1002 2
1002 5
1003 5
1004 4
1005 3
1006 8
1007 0

Queries:

1. List the titles of all movies directed by ‘Hitchcock’.

SELECT M.MOV_TITLE
FROM MOVIES M,DIRECTOR D
WHERE M.DIR_ID=D.DIR_ID AND D.DIR_NAME = 'HITCHCOCK';

OUTPUT:

MOV_TITLE
-------------------------
PSYCHO
THE BIRDS

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;

OUTPUT:

MOV_TITLE
-------------------------
BAHUBALI-1
BAHUBALI-2
LAST BUS

Department of ISE Page 35

 DBMS Laboratory with Mini Project [17CSL58]

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

SELECT A.ACT_NAME
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;

OUTPUT:

ACT_NAME
--------------------
ANUSHKA
PRABHAS
ARAVIND
KIM NEWMEN

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;

OUTPUT:
MOV_TITLE MAX(REV_STARS)
------------------------- --------------
BAHUBALI-1 5
BAHUBALI-2 4
LAST BUS 3
PSYCHO 5
THE BIRDS 8
WAR HORSE 4

Department of ISE Page 36

 DBMS Laboratory with Mini Project [17CSL58]

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

UPDATE RATING
SET REV_STARS=5
WHERE MOV_ID IN (SELECT M.MOV_ID FROM MOVIES M,DIRECTOR D
WHERE M.DIR_ID=D.DIR_ID AND
D.DIR_NAME = 'STEVEN SPIELBERG');

BEFORE UPDATING:
SQL> SELECT * FROM RATING;
MOV_ID REV_STARS
---------- ----------
1001 4
1002 2
1003 5
1004 4 <-------
1005 3
1006 8
1007 0 <-------
1001 2
1002 5
AFTER UPDATING:
SQL> SELECT * FROM RATING;
MOV_ID REV_STARS
---------- ----------
1001 4
1002 2
1003 5
1004 5 <-------
1005 3
1006 8
1007 5 <-------
1001 2
1002 5

Department of ISE Page 37

 DBMS Laboratory with Mini Project [17CSL58]

Program 4:

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.

Creating Table :

CREATE TABLE STUDENT

(
USN VARCHAR (10),
SNAME VARCHAR (20),
ADDRESS VARCHAR (20),
PHONE NUMBER (10),
GENDER CHAR (1),
PRIMARY KEY(USN)
);

CREATE TABLE SEMSEC

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

CREATE TABLE CLASS

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

Department of ISE Page 38

 DBMS Laboratory with Mini Project [17CSL58]

CREATE TABLE SUBJECT

(
SUBCODE VARCHAR (8),
TITLE VARCHAR (20),
SEM NUMBER (2),
CREDITS NUMBER (5),
PRIMARY KEY (SUBCODE)
);

CREATE TABLE IAMARKS

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

INSERT INTO STUDENT VALUES ('1BI16CS001','ABHILASH','BELAGAVI',8877881122,'M');

INSERT INTO STUDENT VALUES ('1BI16CS011','AMOGH','BENGALURU',7722829912,'M');
INSERT INTO STUDENT VALUES ('1BI16CS113','ANANYA','BENGALURU',7712312312,'F');
INSERT INTO STUDENT VALUES ('1BI16CS049','HARSHA','MANGALURU',8877881122,'M');
INSERT INTO STUDENT VALUES ('1BI16CS065','KRUTHI','BENGALURU',9900211201,'F');

INSERT INTO STUDENT VALUES ('1BI16CS071','MEGHA','BENGALURU',9923211099,'F');

INSERT INTO STUDENT VALUES ('1BI16CS091','MANJU','BENGALURU', 7894737377,'M');
INSERT INTO STUDENT VALUES ('1BI16CS009','KIRAN','BENGALURU',7894737377,'M');
INSERT INTO STUDENT VALUES ('1BI16CS021','NAYANA','BENGALURU',7894737377,'F');
INSERT INTO STUDENT VALUES ('1BI16CS093','KUMAR','BENGALURU',7894737377,'M');

INSERT INTO STUDENT VALUES ('1BI16CS100','SWETHA','BENGALURU',7894737377,'F'

INSERT INTO STUDENT VALUES ('1BI15CS027','ANVITHA','TUMKUR', 9845091341,'F');
INSERT INTO STUDENT VALUES ('1BI15CS012','AJAY','DAVANGERE',7696772121,'M');
INSERT INTO STUDENT VALUES ('1BI15CS015','ANVITHA','BELLARY', 9944850121,'F');
INSERT INTO STUDENT VALUES ('1BI15CS101','NEMISA SINHA', 'MANGALURU',
8812332201,'M');

INSERT INTO STUDENT VALUES ('1BI15CS200','PAVAN','KALBURGI',9900232201,'M');

INSERT INTO STUDENT VALUES ('1BI15CS191','SIRI','SHIMOGA',9905542212,'F');
INSERT INTO STUDENT VALUES ('1BI14CS007','ADITYA','SHIMOGA',9905542212,'M');
INSERT INTO STUDENT VALUES ('1BI14CS018','AMOGH ','MYSORE',9905541112,'M');
INSERT INTO STUDENT VALUES ('1BI14CS020','AMULYA','SHIMOGA',8812332201,'F');

INSERT INTO STUDENT VALUES ('1BI14CS051','KEERTHI','SHIMOGA',9905542212,'M');

INSERT INTO STUDENT VALUES ('1BI14CS078','MANJULA','SHIMOGA',9905541234,'F');
INSERT INTO STUDENT VALUES ('1BI14CS112','POOJA','SHIMOGA',9985541112,'F');

Department of ISE Page 39

 DBMS Laboratory with Mini Project [17CSL58]

INSERT INTO STUDENT VALUES ('1BI14CS114','PRADEEP','SHIMOGA',9901232212,'M');

INSERT INTO STUDENT VALUES ('1BI14CS066','PRAKASH','SHIMOGA',9901232212,'M');

INSERT INTO STUDENT VALUES ('1BI14CS132','PRIYA','MYSORE',9901232212,'F');

INSERT INTO STUDENT VALUES ('1BI14CS161','SIRI','TUMKUR',9901232212,'F');

SQL> SELECT * FROM STUDENT;

USN SNAME ADDRESS PHONE G
---------- -------- ----------------- ---------- -
1BI16CS001 ABHILASH BELAGAVI 8877881122 M
1BI16CS011 AMOGH BENGALURU 7722829912 M
1BI16CS113 ANANYA BENGALURU 7712312312 F
1BI16CS049 HARSHA MANGALURU 8877881122 M
1BI16CS065 KRUTHI BENGALURU 9900211201 F
1BI16CS071 MEGHA BENGALURU 9923211099 F
1BI16CS091 MANJU BENGALURU 7894737377 M
1BI16CS009 KIRAN BENGALURU 7894737377 M
1BI16CS021 NAYANA BENGALURU 7894737377 F
1BI16CS093 KUMAR BENGALURU 7894737377 M
1BI16CS100 SWETHA BENGALURU 7894737377 F
1BI15CS027 ANVITHA TUMKUR 9845091341 F
1BI15CS012 AJAY DAVANGERE 7696772121 M
1BI15CS015 ANVITHA BELLARY 9944850121 F
1BI15CS101 NEMISA SINHA MANGALURU 8812332201 M
1BI15CS200 PAVAN KALBURGI 9900232201 M
1BI15CS191 SIRI SHIMOGA 9905542212 F
1BI14CS007 ADITYA SHIMOGA 9905542212 M
1BI14CS018 AMOGH MYSORE 9905541112 M
1BI14CS020 AMULYA SHIMOGA 8812332201 F
1BI14CS051 KEERTHI SHIMOGA 9905542212 M
1BI14CS078 MANJULA SHIMOGA 9905541234 F
1BI14CS112 POOJA SHIMOGA 9985541112 F
1BI14CS114 PRADEEP SHIMOGA 9901232212 M
1BI14CS066 PRAKASH SHIMOGA 9901232212 M
1BI14CS132 PRIYA MYSORE 9901232212 F
1BI14CS161 SIRI TUMKUR 9901232212 F

INSERT INTO SEMSEC VALUES ('CSE4A', 4,'A');

INSERT INTO SEMSEC VALUES ('CSE4B', 4,'B');
INSERT INTO SEMSEC VALUES ('CSE4C', 4,'C');
INSERT INTO SEMSEC VALUES ('CSE6A', 6,'A');
INSERT INTO SEMSEC VALUES ('CSE6B', 6,'B');
INSERT INTO SEMSEC VALUES ('CSE8A', 8,'A');
INSERT INTO SEMSEC VALUES ('CSE8B', 8,'B');
INSERT INTO SEMSEC VALUES ('CSE8C', 8,'C');

Department of ISE Page 40

 DBMS Laboratory with Mini Project [17CSL58]

SQL> SELECT * FROM SEMSEC;

SSID SEM S
----- ------ -----
CSE4A 4 A
CSE4B 4 B
CSE4C 4 C
CSE6A 6 A
CSE6B 6 B
CSE8A 8 A
CSE8B 8 B
CSE8C 8 C
INSERT INTO CLASS VALUES ('1BI16CS001','CSE4A');
INSERT INTO CLASS VALUES ('1BI16CS011','CSE4A');
INSERT INTO CLASS VALUES ('1BI16CS113','CSE4A');
INSERT INTO CLASS VALUES ('1BI16CS049','CSE4B');
INSERT INTO CLASS VALUES ('1BI16CS065','CSE4B');

INSERT INTO CLASS VALUES ('1BI16CS071','CSE4B');

INSERT INTO CLASS VALUES ('1BI16CS091','CSE4B');
INSERT INTO CLASS VALUES ('1BI16CS009','CSE4C');
INSERT INTO CLASS VALUES ('1BI16CS021','CSE4C');
INSERT INTO CLASS VALUES ('1BI16CS093','CSE4C');

INSERT INTO CLASS VALUES ('1BI16CS100','CSE4C');

INSERT INTO CLASS VALUES ('1BI15CS027','CSE6A');
INSERT INTO CLASS VALUES ('1BI15CS012','CSE6A');
INSERT INTO CLASS VALUES ('1BI15CS015','CSE6A');
INSERT INTO CLASS VALUES ('1BI15CS101','CSE6B');

INSERT INTO CLASS VALUES ('1BI15CS200','CSE6B');

INSERT INTO CLASS VALUES ('1BI15CS191','CSE6B');
INSERT INTO CLASS VALUES ('1BI14CS007','CSE8A');
INSERT INTO CLASS VALUES ('1BI14CS018','CSE8A');
INSERT INTO CLASS VALUES ('1BI14CS020','CSE8A');

INSERT INTO CLASS VALUES ('1BI14CS051','CSE8A');

INSERT INTO CLASS VALUES ('1BI14CS078','CSE8B');
INSERT INTO CLASS VALUES ('1BI14CS112','CSE8B');
INSERT INTO CLASS VALUES ('1BI14CS114','CSE8B');
INSERT INTO CLASS VALUES ('1BI14CS066','CSE8C');

INSERT INTO CLASS VALUES ('1BI14CS132','CSE8C');

INSERT INTO CLASS VALUES ('1BI14CS161','CSE8C');

SQL> SELECT * FROM CLASS;

USN SSID
---------- -----
1BI14CS007 CSE8A
1BI14CS018 CSE8A
1BI14CS020 CSE8A
1BI14CS051 CSE8A
1BI14CS066 CSE8C
1BI14CS078 CSE8B

Department of ISE Page 41

 DBMS Laboratory with Mini Project [17CSL58]

1BI14CS112 CSE8B
1BI14CS114 CSE8B
1BI14CS132 CSE8C
1BI14CS161 CSE8C
1BI15CS012 CSE6A
1BI15CS015 CSE6A
1BI15CS027 CSE6A
1BI15CS101 CSE6B
1BI15CS191 CSE6B
1BI15CS200 CSE6B
1BI16CS001 CSE4A
1BI16CS009 CSE4C
1BI16CS011 CSE4A
1BI16CS021 CSE4C
1BI16CS049 CSE4B
1BI16CS065 CSE4B
1BI16CS071 CSE4B
1BI16CS091 CSE4B
1BI16CS093 CSE4C
1BI16CS100 CSE4C
1BI16CS113 CSE4A

INSERT INTO SUBJECT VALUES ('10CS81','SA', 8, 4);

INSERT INTO SUBJECT VALUES ('10CS82','SMAD', 8, 4);
INSERT INTO SUBJECT VALUES ('10CS83','WNMC', 8, 4);
INSERT INTO SUBJECT VALUES ('10CS84','WEB', 8, 4);

INSERT INTO SUBJECT VALUES ('10CS61', 'ME', 6, 4);

INSERT INTO SUBJECT VALUES ('10CS62','USP', 6, 4);
INSERT INTO SUBJECT VALUES ('10CS63','SD', 6, 4);
INSERT INTO SUBJECT VALUES ('10CS64','CNII', 6, 4);
INSERT INTO SUBJECT VALUES ('10CS65','CG', 6, 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);

SQL> SELECT * FROM SUBJECT;

SUBCODE TITLE SEM CREDITS
-------- -------------------- ---------- ----------
10CS81 SA 8 4
10CS82 SMAD 8 4
10CS83 WNMC 8 4
10CS84 WEB 8 4
10CS61 ME 6 4
10CS62 USP 6 4
10CS63 SD 6 4
10CS64 CNII 6 4
10CS65 CG 6 3
15CS41 M4 4 4
15CS42 SE 4 4

Department of ISE Page 42

 DBMS Laboratory with Mini Project [17CSL58]

15CS43 DAA 4 4
15CS44 MPMC 4 4
15CS45 OOC 4 3
15CS46 DC 4 3

INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)

VALUES ('1BI15CS101','10CS61','CSE6B', 20, 23, 20);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI15CS101','10CS62','CSE6B', 18, 19, 19);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI15CS101','10CS63','CSE6B', 19, 20, 20);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI15CS101','10CS64','CSE6B', 20, 20, 19);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI15CS101','10CS65','CSE6B', 18, 20, 19);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS007','10CS81','CSE8A', 15, 10, 12);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS007','10CS82','CSE8A', 15, 20, 12);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS007','10CS83','CSE8A', 5, 10, 5);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS007','10CS84','CSE8A', 15, 20, 12);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS078','10CS81','CSE8B', 15, 20, 12);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS078','10CS82','CSE8B', 15, 20, 12);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS078','10CS83','CSE8B', 10, 8, 10);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS078','10CS84','CSE8B', 15, 20, 12);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS066','10CS81','CSE8C', 15, 20, 12);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS066','10CS82','CSE8C', 12, 13, 14);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS066','10CS83','CSE8C', 15, 20, 12);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)
VALUES ('1BI14CS066','10CS84','CSE8C', 15, 20, 12);

SQL> SELECT * FROM IAMARKS;

USN SUBCODE SSID TEST1 TEST2 TEST3 FINAL IA

---------- -------- ----- ---------- ---------- ---------- ----------
1BI15CS101 10CS61 CSE6B 20 23 20
1BI15CS101 10CS62 CSE6B 18 19 19
1BI15CS101 10CS63 CSE6B 19 20 20
1BI15CS101 10CS64 CSE6B 20 20 19
1BI15CS101 10CS65 CSE6B 18 20 19
1BI14CS007 10CS81 CSE8A 15 10 12

Department of ISE Page 43

 DBMS Laboratory with Mini Project [17CSL58]

1BI14CS007 10CS82 CSE8A 15 20 12

1BI14CS007 10CS83 CSE8A 5 10 5
1BI14CS007 10CS84 CSE8A 15 20 12
1BI14CS078 10CS81 CSE8B 15 20 12
1BI14CS078 10CS82 CSE8B 15 20 12
1BI14CS078 10CS83 CSE8B 10 8 10
1BI14CS078 10CS84 CSE8B 15 20 12
1BI14CS066 10CS81 CSE8C 15 20 12
1BI14CS066 10CS82 CSE8C 12 13 14
1BI14CS066 10CS83 CSE8C 15 20 12
1BI14CS066 10CS84 CSE8C 15 20 12

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';

OUTPUT:

USN SNAME ADDRESS PHONE G SEM S

------- ------- ----------- ------- --- -------- ----
1BI16CS009 KIRAN BENGALURU 7894737377 M 4 C
1BI16CS021 NAYANA BENGALURU 7894737377 F 4 C
1BI16CS093 KUMAR BENGALURU 7894737377 M 4 C
1BI16CS100 SWETHA BENGALURU 7894737377 F 4 C

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;

OUTPUT:

SEM S G COUNT
---------- - --- -----------
4 A F 1
4 A M 2
4 B F 2
4 B M 2
4 C F 2
4 C M 2
6 A F 2

Department of ISE Page 44

 DBMS Laboratory with Mini Project [17CSL58]

6 A M 1
6 B F 1
6 B M 2
8 A F 1
8 A M 3
8 B F 2
8 B M 1
8 C F 2
8 C M 1

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

CREATE VIEW STU_TEST1_MARKS_VIEW

AS
SELECT TEST1, SUBCODE
FROM IAMARKS
WHERE USN = '1BI15CS101';

OUTPUT:

SQL> SELECT * FROM STU_TEST1_MARKS_VIEW;

TEST1 SUBCODE
---------- --------
20 10CS61
12 10CS62
19 10CS63
20 10CS64
15 10CS65

4.Calculate the Final IA (average of best two test marks) and update the
corresponding table for all students.

UPDATE IAMARKS SET FINALIA=((TEST1+TEST2+TEST3)-

LEAST(TEST1,TEST2,TEST3))/2;

OUTPUT:

SQL> SELECT * FROM IAMARKS;

USN SUBCODE SSID TEST1 TEST2 TEST3 FINALIA

---------- -------- ----- ---------- ---------- ---------- ----------
1BI15CS101 10CS61 CSE6B 20 23 20 21.5
1BI15CS101 10CS62 CSE6B 18 19 19 19
1BI15CS101 10CS63 CSE6B 19 20 20 20
1BI15CS101 10CS64 CSE6B 20 20 19 20
1BI15CS101 10CS65 CSE6B 18 20 19 19.5
1BI14CS007 10CS81 CSE8A 15 10 12 13.5
1BI14CS007 10CS82 CSE8A 15 20 12 17.5
1BI14CS007 10CS83 CSE8A 5 10 5 7.5

Department of ISE Page 45

 DBMS Laboratory with Mini Project [17CSL58]

1BI14CS007 10CS84 CSE8A 15 20 12 17.5

1BI14CS078 10CS81 CSE8B 15 20 12 17.5
1BI14CS078 10CS82 CSE8B 15 20 12 17.5
1BI14CS078 10CS83 CSE8B 10 8 10 10
1BI14CS078 10CS84 CSE8B 15 20 12 17.5
1BI14CS066 10CS81 CSE8C 15 20 12 17.5
1BI14CS066 10CS82 CSE8C 12 13 14 13.5
1BI14CS066 10CS83 CSE8C 15 20 12 17.5
1BI14CS066 10CS84 CSE8C 15 20 12 17.5

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,IA.SUBCODE,
(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;

OUTPUT:

USN SNAME ADDRESS PHONE G SUBCODE CAT

--------------- ------------ ---------------- ------------- ------------ ------------
1BI14CS007 ADITYA SHIMOGA 9905542212 M 10CS84 OUTSTANDING
1BI14CS007 ADITYA SHIMOGA 9905542212 M 10CS83 WEAK
1BI14CS007 ADITYA SHIMOGA 9905542212 M 10CS82 OUTSTANDING
1BI14CS007 ADITYA SHIMOGA 9905542212 M 10CS81 AVERAGE
1BI14CS078 MANJULA SHIMOGA 9905541234 F 10CS84 OUTSTANDING
1BI14CS078 MANJULA SHIMOGA 9905541234 F 10CS83 WEAK
1BI14CS078 MANJULA SHIMOGA 9905541234 F 10CS82 OUTSTANDING
1BI14CS078 MANJULA SHIMOGA 9905541234 F 10CS81 OUTSTANDING
1BI14CS066 PRAKASH SHIMOGA 9901232212 M 10CS84 OUTSTANDING
1BI14CS066 PRAKASH SHIMOGA 9901232212 M 10CS83 OUTSTANDING
1BI14CS066 PRAKASH SHIMOGA 9901232212 M 10CS82 AVERAGE
1BI14CS066 PRAKASH SHIMOGA 9901232212 M 10CS81 OUTSTANDING

Department of ISE Page 46

 DBMS Laboratory with Mini Project [17CSL58]

Program 5:

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 salaries if every employee working on the ‘IoT’ project is
given a 10 percent raise.
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
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..

Creating Table :

CREATE TABLE EMPLOYEE

(
SSN VARCHAR(8),
Name VARCHAR(10),
Address VARCHAR(30),
Sex CHAR(2),
Salary NUMBER(10),SuperSSN VARCHAR(8),DNo VARCHAR(6),
CONSTRAINT PK_SSN PRIMARY KEY(SSN)
);

CREATE TABLE DEPARTMENT

(
DNo VARCHAR(6),
DName VARCHAR(10),
MgrSSN VARCHAR(8),
MgrStartDate DATE,
DNo PRIMARY KEY(DNo),
MgrSSN FOREIGN KEY(MgrSSN) REFERENCES EMPLOYEE(SSN)
);
CREATE TABLE DLOCATION
(
DNo VARCHAR(6),
DLoc VARCHAR(15),
DNo_DLoc PRIMARY KEY(DNo,DLoc),
DNo FOREIGN KEY(DNo) REFERENCES DEPARTMENT(DNo)
Department of ISE Page 47
 DBMS Laboratory with Mini Project [17CSL58]

);
CREATE TABLE PROJECT
(
PNo VARCHAR(5),
PName VARCHAR(10),
PLocation VARCHAR(14),
DNo VARCHAr(6),
PNo PRIMARY KEY(PNo),
PDNo FOREIGN KEY(DNo) REFERENCES DEPARTMENT(DNo)
);

CREATE TABLE WORKS_ON

(
SSN VARCHAR(8),
PNo VARCHAR(5),
Hours NUMBER(5),
PNo_SSN PRIMARY KEY(PNo,SSN),
WSSN FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN),
PNo FOREIGN KEY(PNo) REFERENCES PROJECT(PNo)
);

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_SSN FOREIGN KEY(SuperSSN)

REFERENCES EMPLOYEE(SSN);

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EDNo FOREIGN KEY(DNo)

REFERENCES DEPARTMENT(DNo);

INSERT INTO EMPLOYEE(SSN, Name, Address, Sex, Salary)

VALUES('100','John','VV Puram,Bangalore','M',660000);
INSERT INTO EMPLOYEE(SSN, Name, Address, Sex, Salary)
VALUES('200','Scott','MG Road,Bangalore','M',700500);
INSERT INTO EMPLOYEE(SSN, Name, Address, Sex, Salary)
VALUES('300','Smith','Jayanagar,Bangalore','M',600000);
INSERT INTO EMPLOYEE(SSN, Name, Address, Sex, Salary)
VALUES('400','Vani','Vijayanagar,Bangalore','F',800000);
INSERT INTO EMPLOYEE(SSN, Name, Address, Sex, Salary)
VALUES('500','Gopal','PB Nagar,Bangalore','M',500000);

INSERT INTO EMPLOYEE(SSN, Name, Address, Sex, Salary)

VALUES(600,'Ravi','Kormangala Bangalore','M',700000);
INSERT INTO EMPLOYEE(SSN, Name, Address, Sex, Salary)
VALUES(700,'Raghu','RR Nagar Bangalore','M',680000);
INSERT INTO EMPLOYEE(SSN, Name, Address, Sex, Salary)
VALUES(800,'Vinod','RT Nagar Bangalore','M',800000);
INSERT INTO EMPLOYEE(SSN, Name, Address, Sex, Salary)
VALUES(900,'Shankar','CH pete Bangalore','M',606000);
INSERT INTO EMPLOYEE(SSN, Name, Address, Sex, Salary)
VALUES(1000,'Sagar','VV Puram Bangalore','M',800000);

INSERT INTO DEPARTMENT VALUES('D1','Accounts','200','11-Feb-2015');

INSERT INTO DEPARTMENT VALUES('D2','Research','200','11-Mar-2016');

Department of ISE Page 48

 DBMS Laboratory with Mini Project [17CSL58]

INSERT INTO DEPARTMENT VALUES('D3','Finance','400','16-Jun-2015');

INSERT INTO DEPARTMENT VALUES('D4','Admin','100','30-Apr-2017');
INSERT INTO DEPARTMENT VALUES('D5','Testing','400','21-Mar-2016');

INSERT INTO DLOCATION VALUES('D1','Bangalore');

INSERT INTO DLOCATION VALUES('D2','Mysore');
INSERT INTO DLOCATION VALUES('D1','Mysore');
INSERT INTO DLOCATION VALUES('D3','Bangalore');
INSERT INTO DLOCATION VALUES('D4','Mangalore');

INSERT INTO PROJECT VALUES('P1','Billing','Bangalore','D1');

INSERT INTO PROJECT VALUES('P8','IoT','Mysore','D2');
INSERT INTO PROJECT VALUES('P3','Network','Davangere','D2');
INSERT INTO PROJECT VALUES('P4','Tax','Kolar','D1');
INSERT INTO PROJECT VALUES('P5','Salary','Bangalore','D3');

INSERT INTO PROJECT VALUES('P6','Placement','Mysore','D4');

INSERT INTO PROJECT VALUES('P7','Software','Bangalore','D5');
INSERT INTO WORKS_ON VALUES('100','P1',8);
INSERT INTO WORKS_ON VALUES('200','P3',10);
INSERT INTO WORKS_ON VALUES('300','P8',10);

INSERT INTO WORKS_ON VALUES('100','P8',10);

INSERT INTO WORKS_ON VALUES('400','P4',10);
INSERT INTO WORKS_ON VALUES('400','P6',12);
INSERT INTO WORKS_ON VALUES('500','P7',10);
INSERT INTO WORKS_ON VALUES('600','P4',10);

INSERT INTO WORKS_ON VALUES('700','P5',10);

INSERT INTO WORKS_ON VALUES('800','P1',10);
INSERT INTO WORKS_ON VALUES('900','P4',10);
INSERT INTO WORKS_ON VALUES('1000','P5',10);

UPDATE EMPLOYEE SET SuperSSN='200' where SSN='100';

UPDATE EMPLOYEE SET SuperSSN='200' where SSN='300';
UPDATE EMPLOYEE SET SuperSSN='200' where SSN='400';
UPDATE EMPLOYEE SET SuperSSN='300' where SSN='200';
UPDATE EMPLOYEE SET SuperSSN='300' where SSN='500';

UPDATE EMPLOYEE SET SuperSSN='200' where SSN='600';

UPDATE EMPLOYEE SET SuperSSN='200' where SSN='700';
UPDATE EMPLOYEE SET SuperSSN='200' where SSN='800';
UPDATE EMPLOYEE SET SuperSSN='200' where SSN='900';
UPDATE EMPLOYEE SET SuperSSN='200' where SSN='1000';

UPDATE EMPLOYEE SET DNo='D1' where SSN='100';

UPDATE EMPLOYEE SET DNo='D2' where SSN='200';
UPDATE EMPLOYEE SET DNo='D3' where SSN='300';
UPDATE EMPLOYEE SET DNo='D4' where SSN='400';
UPDATE EMPLOYEE SET DNo='D2' where SSN='500';

Department of ISE Page 49

 DBMS Laboratory with Mini Project [17CSL58]

UPDATE EMPLOYEE SET DNo='D1' where SSN='600';

UPDATE EMPLOYEE SET DNo='D1' where SSN='700';
UPDATE EMPLOYEE SET DNo='D1' where SSN='800';
UPDATE EMPLOYEE SET DNo='D1' where SSN='900';
UPDATE EMPLOYEE SET DNo='D1' where SSN='1000';

SELECT * FROM EMPLOYEE;

SSN NAME ADDRESS SE SALARY SUPERSSN DNO

---- ------- --------------- ------- ------- ------- ---
100 John VV Puram,Bangalore M 660000 200 D1
200 Scott MG Road,Bangalore M 700500 300 D2
300 Smith Jayanagar,Bangalore M 600000 200 D3
400 Vani Vijayanagar,Bangalore F 800000 200 D4
500 Gopal PB Nagar,Bangalore M 500000 300 D2
600 Ravi Kormangala Bangalore M 700000 200 D1
700 Raghu RR Nagar Bangalore M 680000 200 D1
800 Vinod RT Nagar Bangalore M 800000 200 D1
900 Shankar CH pete Bangalore M 606000 200 D1
1000 Sagar VV Puram Bangalore M 800000 200 D1

SELECT * FROM DEPARTMENT;

DNO DNAME MGRSSN MGRSTARTD

------ ---------- -------- ---------
D1 Accounts 200 11-FEB-15
D2 Research 200 11-MAR-16
D3 Finance 400 16-JUN-15
D4 Admin 100 30-APR-17
D5 Testing 400 21-MAR-16

SELECT * FROM DLOCATION;

DNO DLOC
------ ---------------
D1 Bangalore
D1 Mysore
D2 Mysore
D3 Bangalore
D4 Mangalore

SELECT * FROM PROJECT;

PNO PNAME PLOCATION DNO

----- ---------- -------------- ------
P1 Billing Bangalore D1
P8 IoT Mysore D2
P3 Network Davangere D2
P4 Tax Kolar D1
P5 Salary Bangalore D3
P6 Placement Mysore D4
P7 Software Bangalore D5

Department of ISE Page 50

 DBMS Laboratory with Mini Project [17CSL58]

SELECT * FROM WORKS_ON;

SSN PNO HOURS

-------- ----- ----------
100 P1 8
300 P3 10
300 P8 10
100 P8 10
400 P4 10
400 P6 12
500 P7 10
400 P8 10
600 P4 10
700 P5 10
800 P1 10
900 P4 10
1000 P5 10

Queries:

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 PNo

FROM PROJECT
WHERE PNo IN(
(SELECT P.PNo
FROM PROJECT P,DEPARTMENT D ,EMPLOYEE E
WHERE P.DNo=D.DNo AND D.MgrSSN=E.SSN AND E.Name='Scott')
UNION
(SELECT W.PNo
FROM WORKS_ON W, EMPLOYEE E
WHERE E.SSN=W.SSN AND E.Name='Scott'));
OUTPUT:

PNO
-----
P1
P3
P4
P8

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

SELECT E.Name,1.1* E.Salary AS Increased_salary

FROM EMPLOYEE E, WORKS_ON W, PROJECT P
WHERE E.SSN=W.SSN AND W.PNo=P.PNo AND P.PName='IoT';

Department of ISE Page 51

 DBMS Laboratory with Mini Project [17CSL58]

OUTPUT:
NAME INCREASED_SALARY
---------- ----------------
John 726000
Smith 660000
Vani 880000

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.

SELECT SUM (E. Salary) AS TOTAL_SALARY,MAX(E. Salary) AS

MAX_SALARY,MIN(E. Salary) AS MIN_SALARY,AVG(E. Salary) AS
AVG_SALARY
FROM EMPLOYEE E, DEPARTMENT D
WHERE E. DNo= D. DNo AND D.DName='Accounts';

OUTPUT:
TOTAL_SALARY MAX_SALARY MIN_SALARY AVG_SALARY
------------ ---------- ---------- ----------
4246000 800000 606000 707666.667

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

SELECT E.Name
FROM EMPLOYEE E
WHERE NOT EXISTS((SELECT PNo FROM PROJECT WHERE DNo='D5')
MINUS (SELECT W.PNo FROM WORKS_ON W WHERE E.SSN=W.SSN));

OUTPUT:
NAME
----------
Gopal

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 EMPLOYEE E, DEPARTMENT D
WHERE E.DNo= D.DNo AND E.Salary>600000
GROUP BY D.DNo
HAVING COUNT(*)>=5;

OUTPUT:

DNO COUNT(*)
------ ----------
D1 6

Department of ISE Page 52