ಕರ್ನಾಟಕ ಸರ್ಕಾರ

ರ್ಕಲೇಜು ಮತ್ತು ತಾOತ್ರಿ ಕ ಶಿಕ್ಷಣ ಇಲಾಖೆ

ಬೆಂಗಳೂರು

Database System Concepts and PL/SQL

20CS34P

2022 - 2023

CERTIFICATE
This is to certify that Mr. / Miss _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
of III Semester Diploma in Computer Science and Engineering has conducted the
practical in Database System Concepts and PL/SQL (20CS34P) satisfactorily
during the year 2022 - 2023.

Staff Member In charge with date Head of the Section with date
SAYYAN SHAIKH Mohan K S

REGISTER NUMBER
1 6 2 C S 2 2 0

Examiner’s Signature: 1. _______________________

2. _______________________
PRINCIPAL
Date: / /2023 GOVT. POLYTECHNIC, JOIDA
Place: JOIDA
 TABLE OF CONTENTS
Chapter Page
Concepts Date Signature
No. No

CHAPTER 1 BASIC CONCEPTS OF SQL 1

1.1 Introduction to SQL 1

1.2 SQL Commands 1

1.2.1 DDL Commands 2

1.2.2 DML Commands 5

1.2.3 TCL Commands 7

1.2.4 DCL Commands 8

1.3 Views in SQL 9

CHAPTER 2 LAB PROGRAM 1 - LIBRARY DATABASE 10

2.1 Problem Statement 10

2.2 ER Diagram 10

2.3 Schema Diagram 11

2.4 Creating Tables 12

2.5 Inserting Values 13

2.6 Queries and Solutions 15

CHAPTER 3 LAB PROGRAM 2 - ORDER DATABASE 18

3.1 Problem Statement 18

3.2 ER Diagram 18

3.3 Schema Diagram 19

3.4 Creating Tables 20

3.5 Inserting Values 20

3.6 Queries and Solutions 21

CHAPTER 4 LAB PROGRAM 3 - MOVIE DATABASE 24

4.1 Problem Statement 24

4.2 ER Diagram 24

4.3 Schema Diagram 25

4.4 Creating Tables 26

4.5 Inserting Values 27

4.6 Queries and Solutions 29

CHAPTER 5 LAB PROGRAM 4 - COLLEGE DATABASE 31

5.1 Problem Statement 31

5.2 ER Diagram 31

5.3 Schema Diagram 32

5.4 Creating Tables 33

5.5 Inserting Values 34

5.6 Queries and Solutions 36

CHAPTER 6 LAB PROGRAM 5 - COMPANY DATABASE 38

5.1 Problem Statement 38

5.2 ER Diagram 38

5.3 Schema Diagram 39

5.4 Creating Tables 40

5.5 Inserting Values 41

5.6 Queries and Solutions 43

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

CHAPTER – 1

BASIC CONCEPTS

1.1 Introduction

Database is an organized collection of data stored and accessed electronically from a

computer system
DBMS dbms consists of a collection of interrelated data and a set of programs to manage
these data.
Database management system is the software that interacts with end users, applications and
the database itself to capture and analyze the data.
As the world’s most popular DBMS- with 39% of developing using MYSQL is a fast
reliable, general-purpose, relational database management system. It can be installed on all
platforms like Windows, Linux and Mac. It is secure and stable also provides high scalability.

1.2 MYSQL Commands

SQL commands are instructions used to communicate with the database to perform specific
task that work with data. SQL commands can be used not only for searching the database but
also to perform various other functions like, for example, you can create tables, add data to
tables, or modify data, drop the table, set permissions for users. SQL commands are grouped
into four major categories depending on their functionality:
 Data Definition Language (DDL) - These SQL commands are used for creating,
modifying, and dropping the structure of database objects. The commands are
CREATE, ALTER, DROP and TRUNCATE.
 Data Manipulation Language (DML) - These SQL commands are used for storing,
retrieving, modifying and deleting data. These commands are
SELECT, INSERT, UPDATE, and DELETE.
 Transaction Control Language (TCL) - These SQL commands are used for managing
changes affecting the data. These commands are COMMIT, ROLLBACK, and
SAVEPOINT.

 Data Control Language (DCL) - These SQL commands are used for providing
security to database objects. These commands are GRANT and REVOKE.

Government Polytechnic, Joida Page 1

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

1.2.1 Data Definition Language (DDL)

1.2.1.1 CREATE TABLE Statement
The CREATE TABLE Statement is used to create tables to store data. Integrity Constraints
like primary key, unique key and foreign key can be defined for the columns while creating
the table. The integrity constraints can be defined at column level or table level. The
implementation and the syntax of the CREATE Statements differs for different RDBMS.

The Syntax for the CREATE TABLE Statement is:

CREATE TABLE table_name

(column_name1 datatype constraint,

column_name2 datatype, ...

column_nameN datatype);
 table_name - is the name of the table.
 column_name1, column_name2.... - is the name of the columns
 datatype - is the datatype for the column like char, date, number etc.
MYSQL Data Types:
Character Datatypes:
MYSQL supports character data types for storing text values. MYSQL offers these character
data types: CHAR(n), VARCHAR(n).
Name Description

varchar(n) Allows you to declare variable-length with a limit

Char(n) Fixed-length, blank padded

Numeric Datatypes:
MYSQL supports two distinct types of numbers:
 Integers
 Floating-point numbers
Store
Name size Range
smallint 2 bytes -32768 to +32767
integer 4 bytes -2147483648 to +2147483647
bigint 8 bytes -9223372036854775808 to 9223372036854775807
Real 4 bytes
6 decimal digits precision.

Government Polytechnic, Joida Page 2

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

If you declare it as the number, you can include number up to 131072 digits
numeric variable
before the decimal point to 16383 digits after the decimal point
decimal . variable If you declared it as decimal datatype ranges from 131072 digits before the
decimal point to 16383 digits after the decimal point
double 8 bytes 15 decimal digits precision
Date Data Types:
The DATE data types is used to store date information.

MYSQL Integrity Constraints:

Integrity Constraints are used to apply business rules for the database tables.The constraints
available in SQL are Foreign Key, Primary key, Not Null, Unique, Check. Constraints can be
defined in two ways:
1. The constraints can be specified immediately after the column definition. This is
called column-level definition.
2. The constraints can be specified after all the columns are defined. This is called table-
level definition.
1) Primary key:
 This constraint defines, no two tuples can have identical values for attributes.
 Primary key attribute cannot have NULL values.
Syntax to define a Primary key at column level:

Column_name datatype [CONSTRAINT constraint_name] PRIMARY KEY

 Column_name1,column_name2 are the names of the columns which define the

primary key.
 The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.
2) Foreign key or Referential Integrity:
This constraint identifies any column referencing the PRIMARY KEY in another table. It
establishes a relationship between two columns in the same table or between different
tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary
Key in the table which it is referring. One or more columns can be defined as Foreign key.

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES

referenced_table_name(column_name);

Government Polytechnic, Joida Page 3

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

3) Not Null Constraint:

This constraint ensures all rows in the table contain a definite value for the column which is
specified as not null. Which means a null value is not allowed.
Syntax to define a Not Null constraint:
[CONSTRAINT constraint name] NOT NULL

4) Unique Key:
This constraint ensures that a column or a group of columns in each row have adistinct value.
A column(s) can have a null value but the values cannot be duplicated.
Syntax to define a Unique key at column level:
[CONSTRAINT constraint_name] UNIQUE

Syntax to define a Unique key at table level:

[CONSTRAINT constraint_name] UNIQUE(column_name)

5) Check Constraint:
This constraint defines a business rule on a column. All the rows must satisfy this rule. The
constraint can be applied for a single column or a group of columns.
Syntax to define a Check constraint:
[CONSTRAINT constraint_name] CHECK (condition)

1.2.1.2 ALTER TABLE Statement

The SQL ALTER TABLE command is used to modify the definition structure) of a table by
modifying the definition of its columns. The ALTER command is used to perform the
following functions.
1) Add, drop, modify table columns
2) Add and drop constraints
3) Enable and Disable constraints

Syntax to add a column

ALTER TABLE table_name ADD column_name datatype;

For Example: To add a column "experience" to the employee table, the query would be like

ALTER TABLE employee ADD experience int;

Government Polytechnic, Joida Page 4

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

Syntax to drop a column

ALTER TABLE table_name DROP column_name;

For Example: To drop the column "location" from the employee table, the query would be like

ALTER TABLE employee DROP location;

Syntax to modify a column

ALTER TABLE table_name MODIFY column_name datatype;

For Example: To modify the column salary in the employee table, the query would be like
ALTER TABLE employee MODIFY name varchar(20);

Syntax to add PRIMARY KEY constraint

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY

column_name;

Syntax to drop PRIMARY KEY constraint

ALTER TABLE table_name DROP PRIMARY KEY;

1.2.1.3 The DROP TABLE Statement

The DROP TABLE statement is used to delete a table.

Syntax: DROP TABLE table_name;

1.2.1.4 TRUNCATE TABLE Statement
What if we only want to delete the data inside the table, and not the table itself? Then, use
the TRUNCATE TABLE statement:
Syntax: TRUNCATE TABLE table_name;

1.2.2 Data Manipulation Language (DML):

The SELECT Statement
The SELECT statement is used to select data from a database. The result is stored in a result
table, called the result-set.
Syntax: SELECT * FROM table_name;
The SELECT DISTINCT Statement
In a table, some of the columns may contain duplicate values. This is not a problem, however,
sometimes you will want to list only the different (distinct) values in a table.The DISTINCT
keyword can be used to return only distinct (different) values.

Government Polytechnic, Joida Page 5

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

Syntax: SELECT DISTINCT column_name(s)

FROM table_name;

The WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion.
Syntax: SELECT column_name(s)
FROM table_name
WHERE column_name operator value;

The AND & OR Operators

 The AND operator displays a record if both the first condition and the second
condition is true.
 The OR operator displays a record if either the first condition or the second
condition is true.
The ORDER BY Clause
 The ORDER BY clause is used to sort the result-set by a specified column.
 The ORDER BY clause sort the records in ascending order by default.
 If you want to sort the records in a descending order, you can use the DESC
keyword.
Syntax: SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC;

The GROUP BY Clause

The GROUP BY clause can be used to create groups of rows in a table. Group functions can
be applied on such groups.
Syntax; SELECT column_name(s)
FROM table_name
GROUP BY column_name(s);

Group functions Meaning

AVG([DISTINCT|ALL],N]) Returns average value of n
MAX([DISTINCT|ALL]expr) Returns maximum value of expr.
MIN([DISTINCT|ALL]expr) Returns minimum value of expr.
SUM([DISTINCT|ALL]n) Returns sum of values of n.
COUNT(*|[DISTINCT|ALL]expr) Returns the number of rows in the query.

Government Polytechnic, Joida Page 6

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

The HAVING clause

The HAVING clause can be used to place the conditions to GROUP BY clause.
Syntax; SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition;

The INSERT INTO Statement

The INSERT INTO statement is used to insert a new row in a table.
It is possible to write the INSERT INTO statement in two forms.
 The first form doesn't specify the column names where the data will be inserted, only
their values:
INSERT INTO table_name VALUES (value1, value2, value3,...);
OR
INSERT INTO table_name VALUES (&column1, &column2, &column3,...);

 The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)

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

The UPDATE Statement

The UPDATE statement is used to update existing records in a table.

Syntax:
UPDATE table_name
SET column1=value, column2=value2,... WHERE some_column=some_value;

The DELETE Statement

The DELETE statement is used to delete rows in a table.

Syntax:

DELETE FROM table_name WHERE some_column=some_value;

1.2.3 Transaction Control language

Transaction Control Language (TCL) commands are used to manage transactions in
database.These are used to manage the changes made by DML statements. It also allows

Government Polytechnic, Joida Page 7

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

statements to be grouped together into logical transactions

Commit command

Commit command is used to permanently save any transaction into database.

Syntax: commit;
Rollback command
This command restores the database to last committed state. It is also use with savepoint
command to jump to a savepoint in a transaction.

Syntax : rollback to savepoint_name;

Savepoint command
savepoint command is used to temporarily save a transaction so that you can rollback to that
point whenever necessary.
Syntax:: savepoint savepoint_name;

1.2.4 Data Control Language

Data Control Language(DCL) is used to control privilege in Database. To perform any

operation in the database, such as for creating tables, sequences or views we need privileges. 
DCL defines two commands,
 Grant : Gives user access privileges to database.
 Revoke : Take back permissions from user.
To check number of users and their names
Select user from mysql.user;

To create new user

Syntax: create user user_account identified by password;
Ex: create user sudha@localhost identified by ‘wxyz’;

Granting SELECT privilege

Ex: GRANT SELECT ON student TO 'sudha'@'localhost';

Check applied privilege

Ex: show grants for 'sudha'@'localhost';

Granting all the privilege to user in a table

Ex: GRANT ALL ON student TO 'sudha'@'localhost';

Government Polytechnic, Joida Page 8

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

1.3 VIEWS IN MYSQL

 views in SQL are virtual table. A view also contains rows and columns.
 To create the view, we can select the fields from one or more tables present in the database.
 A view can either have specific rows based on certain condition or all the rows of a table. The
reasons for using Views.
 Views restrict access to the data because the view can display selective columns and rows from
table.
 Views provide groups of users with access to data according to their particular permissions.
 Views can be used to retrieve data from several tables, providing data independence for users.

Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

Government Polytechnic, Joida Page 9

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

CHAPTER – 2

LIBRARY DATABASE

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.
ER-Diagram:

Government Polytechnic, Joida Page 10

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

SCHEMA:

Government Polytechnic, Joida Page 11

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

Table Creation:

PUBLISHER

SQL> create table publisher(

Name varchar(18) primary key,
Address varchar(10),
phone int);

Table created.

BOOK

SQL> create table book(

book_id int primary key,
title varchar(20),
Publisher_name varchar(20),
foreign key(publisher_name) references publisher(Name) on
delete cascade,
pub_year int);

Table created.

BOOK_AUTHORS

SQL> create table book_authors(

Book_id int,
Author_name varchar(20),
Foreign key(book_id) references book(book_id) on delete
cascade);

Table created.

LIBRARY_BRANCH

SQL> create table library_branch(

branch_id int primary key,
branch_name varchar(18),
address varchar(15));

Table created.

BOOK_COPIES

SQL> create table book_copies(

book_id int,
foreign key(book_id)references book(book_id) on delete
cascade,
branch_id int primary key,
foreign key(branch_id) references library_branch(branch_id)
on delete cascade,
no_of_copies int);

Table created.

Government Polytechnic, Joida Page 12

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

BOOK_LENDING

SQL> create table book_lending(

book_id int,
foreign key(book_id) references book(book_id) on delete
cascade,
branch_id int,
foreign key(branch_id) references library_branch(branch_id) on
delete cascade,
card_no int primary key,
date_out date,
due_date date);

Table created.

Values for tables:

PUBLISHER

SQL>insert into publisher values('pearson','Bangalore',9875462530);

SQL> insert into publisher values('mcgraw','Newdelhi',7845691234);

SQL> insert into publisher values('sapna','Bangalore',7845963210);

BOOK

SQL> insert into book values(1111,'se','pearson',2005);

SQL> insert into book values(2222,'dbms','mcgraw',2004);

SQL> insert into book values(3333,'Anotomy','pearson',2010);

SQL> insert into book values(4444,'Encyclopedia','sapna',2010);

BOOK_AUTHORS
SQL> insert into book_authors values(1111,’Sommerville’);
SQL> insert into book_authors values(2222,’Navathe’);
SQL> insert into book_authors values(3333,’Henry gray’);
SQL> insert into book_authors values(4444,’Thomas’);

LIBRARY_BRANCH
SQL> insert into library_branch values(11,’central technical’,’mg
road’);
SQL> insert into library_branch values(22,’medical’,’bh road’);
SQL> insert into library_branch values(33,’children’,’ss puram’);
SQL> insert into library_branch values(44,’secretariat’,’siragate’);
SQL> insert into library_branch values(55,’general’,’jayanagar’);

Government Polytechnic, Joida Page 13

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

BOOK_COPIES

SQL> insert into book_copies values(1111,11,5);

SQL> insert into book_copies values(3333,22,6);

SQL> insert into book_copies values(4444,33,10);

SQL> insert into book_copies values(2222,11,12);

SQL> insert into book_copies values(4444,55,3);

BOOK_LENDING

SQL> insert into book_lending values(2222,11,1,’2017-01-10’,’2017-08-20’);

SQL> insert into book_lending values(3333,22,2,’2017-07-09’,’2017-08-12’);
SQL> insert into book_lending values(4444,55,1,’2017-04-11’,’2017-08-09’);
SQL> insert into book_lending values(2222,11,5,’2017-08-09’,’2017-08-19’);
SQL> insert into book_lending values(4444,33,1,’2017-06-10’,’2017-08-15’);
SQL> insert into book_lending values(1111,11,1,’2017-05-12’,’2017-06-10’);
SQL> insert into book_lending values(3333,22,1,’2017-07-10’,’2017-07-15’);

SQL> select * from book;

book_id title publisher_name pub_year

1111 Se pearson 2005

2222 dbms mcgraw 2004
3333 Anotomy pearson 2010
4444 Encyclopedia sapna 2010

4 rows selected.

SQL> select * from book_authors;

book_id author_name
--------- ------------
1111 Sommerville
2222 Navathe
3333 Henry gray
4444 Thomas
4 rows selected.

SQL> select * from publisher;

name address phone

pearson Bangalore 9875462530

mcgraw Newdelhi 7845691234
sapna Bangalore 7845963210

3 rows selected.

Government Polytechnic, Joida Page 14

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

SQL> select * from book_copies;

book_id branch_id no_of_copies

1111 11 5
3333 22 6
4444 33 10
2222 11 12
4444 55 3

5 rows selected.

SQL> select * from book_lending;

Book_id branch_id card_no date_out due_date

--------- ---------- ------- ---------- ---------
2222 11 1 2017-01-10 2017-08-20
3333 22 2 2017-07-09 2017-08-12
4444 55 1 2017-04-11 2017-08-09
2222 11 5 2017-08-09 2017-08-19
4444 33 1 2017-07-10 2017-08-15
1111 11 1 2017-05-12 2017-06-10
3333 22 1 2017-07-10 2017-07-15

7 rows selected.

SQL> select * from library_branch;

branch_id branch_name address

----------- ------------- ------------
11 central technical mg roads
22 medical bh roads
33 children ss puram
44 secretariat karwar
55 general jayanagar

5 rows selected.

QUERIES:

1) Retrieve details of all books in the library-id, title, name of publisher, authors, number of copies
in each branch e.t.c.
select branch_name, b.book_id, title, publisher_name, author_name,
no_of_copies
from book b, book_authors ba, book_copies bc, library_branch lb
where b.book_id=ba.book_id and
ba.book_id=bc.book_id and
bc.branch_id=lb.branch_id;

branch_name book_id title publisher_name author_name

central technical 1111 Se Pearson sommerville
central technical 2222 Dbms Mcgraw navathe
medical 3333 Encyclopedia Sapna henry gray
children 4444 Encyclopedia Sapna thomas
general 4444 encyclopedia sapna thomas
Government Polytechnic, Joida Page 15
Database System Concepts and PL/SQL- 20CS34P III Sem CSE

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 '2017-01-01' and '2017-06-30'
group by card_no
having count(*)>3;

card_no
1

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

delete from book

where book_id='3333';

1 row deleted.
SQL> select * from book;

Book_id title Publisher_name Pub_year

1111 se pearson 2005
2222 Dbms Mcgraw 2004
4444 encyclopedia sapna 2010

SQL> select * from book_copies;

Book_id Branch_id No_of_copies

1111 11 5
4444 33 10
2222 11 12
4444 55 3

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

Select book_id, title, publisher_name, pub_year

from book
group by pub_year, book_id, title, publisher_name;

Book_id title Publisher_name Pub_year

2222 Dbms Mcgraw 2004
1111 Se Pearson 2005
3333 Anatomy Pearson 2010
4444 encyclopedia sapna 2010

Government Polytechnic, Joida Page 16

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

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

create view books _available as

select b.book_id,title,c.no_of_copies
from book b,book_copies c
where b.book_id=c.book_id;

view created.

SQL> select * from books _available;

Book_id title No_of_copies

1111 Se 5
2222 Dbms 12
4444 Encyclopedia 3
4444 Encyclopedia 10
4444 Encyclopedia 3

Government Polytechnic, Joida Page 17

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

CHAPTER – 3

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

ER-Diagram:

Government Polytechnic, Joida Page 18

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

SCHEMA:

Government Polytechnic, Joida Page 19

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

Table Creation:

SALESMAN

create table salesman(

Salesman_id int primary key,
name varchar(10) not null,
city varchar(15) not null,
commission int);

Table created.

CUSTOMER

create table customer(

customer_id int primary key,
cust_name varchar(10) not null,
city varchar(10) not null,
grade int not null,
salesman_id int,
foreign key(salesman_id) references salesman(salesman_id) on
delete set null);
Table created.

ORDERS

create table orders(

ord_no int primary key,
purchase_amt int not null,
ord_date date not null,
customer_id int,
foreign key(customer_id) references customer(customer_id),
salesman_id int,
foreign key(salesman_id) references salesman(salesman_id) on delete
cascade);
Table created.

Values for tables:

SALESMAN

SQL> insert into salesman values (1000,'raj','bengaluru',50);

SQL> insert into salesman values (2000,'ashwin','tumkur',30);
SQL> insert into salesman values (3000,'bindu','mumbai',40);
SQL> insert into salesman values (4000,'lavanya','bengaluru',40);
SQL> insert into salesman values (5000,'rohit','mysore',60);

SQL> select * from book;

Salesman_id name city commission
1000 Raj Bengaluru 50
2000 Ashwin Tumkur 30
3000 Bindu Mumbai 40
4000 Lavanya Bengaluru 40
5000 Rohit mysore 60

Government Polytechnic, Joida Page 20

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

CUSTOMER
SQL> insert into customer values (11,'infosys','bengaluru',5,1000);
SQL> insert into customer values (22,'tcs','bengaluru',4,2000);
SQL> insert into customer values (33,'wipro','mysore',7,1000);
SQL> insert into customer values (44,'tcs','mysore',6,2000);
SQL> insert into customer values (55,'oracle','tumkur',3,3000);

SQL> select * from customers;

Customer_id Cust_name city grade Salesman_id
11 Infosys Bengaluru 5 1000
22 Tcs Bengaluru 4 2000
33 Wipro Mysore 7 1000
44 tcs Mysore 6 2000
55 oracle tumkur 3 3000

ORDERS
SQL> insert into orders values (1,200000,'2016-04-12',11,1000);
SQL> insert into orders values (2,300000,'2016-04-12',11,2000);
SQL> insert into orders values (3,400000,'2017-04-15',22,1000);

SQL> select * from orders;

Ord_no Purchase_amt Ord_date Customer_id Salesman_id
1 200000 2016-04-12 11 1000
2 300000 2016-04-12 11 2000
3 400000 2017-04-15 22 1000

QUERIES:

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

select count(customer_id)
from customer
where grade> (select avg(grade)
from customer
where city like '%bengaluru');

Count(customer_id)
03

2. Find the name and the numbers of all salesmen who had more than one customer.
select name, count(customer_id)
from salesman s, customer c
where s.salesman_id=c.salesman_id
group by name
having count(customer_id)>1;

Name Count(customer_id)
Raj 2
ashwin 2

Government Polytechnic, Joida Page 21

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

3. List all salesmen and indicate those who have and don’t have customers in their cities (USE
UNION operation).
(select name
from salesman s, customer c
where s.salesman_id=c.salesman_id and s.city=c.city)
union
(select name
from salesman
where salesman_id not in(select s1.salesman_id
from salesman s1,customer c1
where s1.salesman_id=c1.salesman_id and s1.city=c1.city));

Name
Raj
Ashwin
Bindu
Lavanya
rohit

4. Create a view that finds the salesman who has the customer with the highest order of a day.
create view sales_highorder as
select salesman_id, purchase_amt
from orders
where purchase_amt=(select max(o.purchase_amt)
from orders o
where o.ord_date='2016-04-12');

view created.

SQL> select * from sales_highorder;

Salesman_id Purchase_amt

2000 300000

Government Polytechnic, Joida Page 22

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

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

delete from salesman

where salesman_id=1000;

1 row deleted.

SQL> select * from salesman;

Salesman_id name city commission

2000 Ashwin Tumkur 30
3000 Bindu Mumbai 40
4000 Lavanya Bengaluru 40
5000 rohit mysore 60

SQL> select * from customer;

customer_id Cust_name city grade Salesman_id

11 Infosys Bengaluru 5 NULL
22 tcs Bengaluru 4 2000
33 Wipro Mysore 7 NULL
44 tcs Mysore 6 2000
55 oracle tumkur 3 3000

SQL> select * from orders;

Ord_no Purchase_amt Ord_date Customer_id Salesman_id

2 300000 2016-04-12 11 2000

Government Polytechnic, Joida Page 23

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

CHAPTER – 4

MOVIE DATABASE
Consider the following 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.

ER-Diagram:

Government Polytechnic, Joida Page 24

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

SCHEMA:

Government Polytechnic, Joida Page 25

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

Table Creation:

ACTOR
create table actor(
act_id int primary key,
act_name varchar(20) not null,
act_gender varchar(2) not null);

Table created.

DIRECTOR
create table director(
dir_id int primary key,
dir_name varchar(20) not null,
dir_phone int not null);

Table created.

MOVIES
create table movies(
mov_id int primary key,
mov_title varchar(20) not null,
mov_year int not null,
mov_lang varchar(10) not null,
dir_id int,
foreign key(dir_id) references director(dir_id));

Table created

MOVIE_CAST
create table movie_cast(
act_id int,
foreign key(act_id) references actor(act_id),
mov_id int,
foreign key(mov_id) references movies(mov_id),
role varchar(20));

Table created.

RATING

create table rating(

mov_id int,
foreign key(mov_id) references movies(mov_id),
rev_stars int not null);

Table created.

Government Polytechnic, Joida Page 26

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

Values for tables:

ACTOR

insert into actor values(111, 'Deepa Sannidhi', 'F');

insert into actor values(222, 'Sudeep', 'M');
insert into actor values(333, 'Puneeth', 'M');
insert into actor values(444, 'Dhiganth', 'M');
insert into actor values(555, 'Angela', 'F');

DIRECTOR

insert into director values(101, 'Hitchcock', 112267809);

insert into director values(103, 'Yogaraj', 584567809);
insert into director values(104, 'Steven Spielberg', 345667809);
insert into director values(105, 'Pavan Kumar', 345667809);

MOVIES

insert into movies values(1111, 'Last world', 2009, 'English', 104);

insert into movies values(2222, 'Eega', 2010, 'Telugu', 102);
insert into movies values(4444, 'Paramathma', 2012, 'Kannada', 103);
insert into movies values(3333, 'Male', 2006, 'Kannada', 103);
insert into movies values(5555, 'Manasare', 2010, 'Kannada', 103);
insert into movies values(6666, 'Rear Window', 1954, 'English', 101);
insert into movies values(7777, 'Notorious', 1946, 'English', 101);

MOVIE_CAST

insert into movie_cast values(222,2222,'vilan');

insert into movie_cast values(333,4444,'Hero');
insert into movie_cast values(111,4444,'Heroin');
insert into movie_cast values(444,3333,'Guest');
insert into movie_cast values(444,5555,'Hero');
insert into movie_cast values(555,7777,'Mother');

RATING

insert into rating values(1111,3);

insert into rating values(2222,4);
insert into rating values(3333,3);
insert into rating values(5555,4);
insert into rating values(4444,5);

Government Polytechnic, Joida Page 27

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

SQL> select * from actor;

Act_id Act_name Act_gender
111 Deepa sannidhi F
222 Sudeep M
333 Puneeth M
444 Dhiganth M
555 angela F

SQL> select * from director;

Dir_id Dir_name Dir_phone

101 hitchcock 112267809

102 raj mouli 152358709
103 yogaraj 272337808
104 steven spielberg 363445678
105 pavan kumar 385456809

SQL> select * from movies;

Mov_id Mov_title Mov_year Mov_lang Dir_id
1111 Last world 2009 English 104
2222 Eega 2010 Telugu 102
4444 Paramathma 2012 Kannada 103
3333 Male 2006 Kannada 103
5555 Manasare 2010 Kannada 103
6666 Rear Window 1954 English 101
7777 Notorious 1946 English 101

SQL> select * from movie_cast;

Act_id Mov_id role
2222 Vilan
222
333 4444 Hero

111 4444 Heroin

444 3333 Guest

444 5555 Hero

555 7777 mother

Government Polytechnic, Joida Page 28

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

SQL> select * from rating;

Mov_id Rev_stars
1111 3
2222 4
3333 3
5555 4
4444 5

QUERIES
1. List the titles of all movies directed by ‘Hitchcock’.
select mov_title
from movies m,director d
where d.dir_id=m.dir_id and dir_name='Hitchcock';

Mov_title
Rear window
notorious

2. Find the movie names where one or more actors acted in two or more movies.
select mov_title
from movies m,movie_cast mc
where m.mov_id=mc.mov_id and
mc.act_id in ( select act_id
from movie_cast
group by act_id
having count(mov_id)>=2);
Mov_title
Male
Manasare

3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN
operation).
(select 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 < 2000)
union
(select 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 > 2015);

Act_name
angela

Government Polytechnic, Joida Page 29

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

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,rev_stars
from movies m,rating r
where m.mov_id=r.mov_id and rev_stars>=1
order by mov_title;

Mov_title Rev_stars
Eega 4
Lastworld 3
Male 3
Manasare 4
paramathma 5

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

update rating
set rev_stars=5
where mov_id in ( select mov_id
from movies m, director d
where m.dir_id=d.dir_id and
dir_name='Steven Spielberg');

Query OK, 1 row affected (0.08 sec)

Rows matched: 1 Changed: 1 Warnings: 0

select * from rating;

Mov_id Rev_stars
1111 5
2222 4
3333 3
5555 4
4444 5

Government Polytechnic, Joida Page 30

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

CHAPTER – 5

COLLEGE DATABASE
Consider the following 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.

ER-DIAGRAM:

Government Polytechnic, Joida Page 31

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

SCHEMA:

Government Polytechnic, Joida Page 32

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

Table Creation:
STUDENT
create table student(
USN varchar(15) primary key,
sname varchar(20),
address varchar(20),
phone int,
gender char(1));
Table created.

SEMSEC
create table semsec(
SSID varchar(20) primary key,
sem int,
sec char(1));
Table created.

CLASS
create table class(
USN varchar(20),
SSID varchar(6),
foreign key(USN) references student(USN),
foreign key(SSID) references semsec(SSID));

Table created

SUBJECT
create table subject(
Subcode varchar(10) primary key,
title varchar(20),
sem int,
credits int));
Table created

IAMARKS
create table iamarks(
USN varchar(10),
Subcode varchar(10),
SSID varchar(6),
test1 int,
test2 int,
test3 int,
FinalIA int,
foreign key(USN) references student(USN),
foreign key(Subcode) references subject(Subcode),
foreign key(SSID) references semsec(SSID));
Table created.

Government Polytechnic, Joida Page 33

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

Values for tables:

STUDENT
insert into student values('1cg15cs001','Abhi','Tumkur',9874563,'M');
insert into student values('1cg15cs002','Amulya','Gubbi',878124563,'F');
insert into student values('1cg16me063','Chethan','Nittur',742547563,'M');
insert into student values('1cg14ec055','Raghavi','SSpuram',87425563,'F');
insert into student values('1cg15ee065','Sanjay','Bangalore',9874563,'M');

select * from student;

USN sname address phone gender
1cg15cs001 Abhi Tumkur 9874563 M
1cg15cs002 Amulya Gubbi 8781245 F
1cg16me063 Chethan Nittur 7425186 M
1cg14ec055 Raghavi SSpuram 8742551 F
1cg15ee065 Sanjay Bangalore 9875462 M

SEMSEC
insert into semsec values('5A',5,’A’);
insert into semsec values('3B',3,’B’);
insert into semsec values('7A',7,’A’);
insert into semsec values('2C',2,’C’);
insert into semsec values('4B',4,’B’);
insert into semsec values('4C',4,’C’);

select * from semsec;

SSID Sem Sec
5A 5 A
3B 3 B
7A 7 A
2C 2 C
4B 4 B
4C 4 C

CLASS
insert into class values('1cg15cs001','5A');
insert into class values('1cg15cs002','5A');
insert into class values('1cg16me063','3B');
insert into class values('1cg14ec055','7A');
insert into class values('1cg15ee065','3B');
insert into class values('1cg15ee065','4C');
insert into class values('1cg15cs002','4C');

Government Polytechnic, Joida Page 34

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

select * from class;

USN SSID
1cg15cs001 5A
1cg15cs002 5A
1cg16me063 3B
1cg14ec055 7A
1cg15ee065 3B
1cg15ee065 4C
1cg15cs002 4C

SUBJECT
insert into subject values('15cs53','dbms',5,4);
insert into subject values('15cs33','ds',3,4);
insert into subject values('15cs34','co',3,4);
insert into subject values('15csl58','dba',5,2);
insert into subject values('10cs71','oomd',7,4);

select * from subject;

Subcode Title Sem credits
15cs53 Dbms 5 4
15cs33 Ds 3 4
15cs34 Co 3 4
15csl58 Dba 5 2
10cs71 Oomd 7 4

IAMARKS
insert into iamarks values('1cg15cs001','15cs53',’5A’,18,19,15,19);
insert into iamarks values('1cg15cs002','15cs53',’5A’,15,16,14,16);
insert into iamarks values('1cg16me063','15cs33',’3B’,10,15,16,16);
insert into iamarks values('1cg14ec055','10cs71',’7A’,18,20,21,21);
insert into iamarks values('1cg15ee065','15cs33',’3B’,16,20,17,19);
insert into iamarks values('1cg15ee065','15cs53',’4C’,19,20,18,20);

select * from iamarks;

USN Subcode SSID Test1 Test2 Test3 FinalIA
1cg15cs001 15cs53 5A 18 19 15 19
1cg15cs002 15cs53 5A 15 16 14 16
1cg16me063 15cs33 3B 10 15 16 16
1cg14ec055 10cs71 7A 18 20 21 21
1cg15ee065 15cs33 3B 16 20 17 19
1cg15ee065 15cs53 4C 19 20 18 20

Government Polytechnic, Joida Page 35

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

QUERIES
1. List all the student details studying in fourth semester ‘C’ section.

select s.*,sem, sec

from student s, class c, semsec ss
where sem=4 and sec='c' and
ss.ssid=c.ssid and
c.usn=s.usn;

USN sname address phone gender sem sec

1cg15ee065 Sanjay Bangalore 5675560 M 4 C
1cg15cs002 Amulya Gubbi 8796541 F 4 C

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

select sem, sec, gender, count(*)

from student s, semsec ss, class c
where s.usn=c.usn and
c.ssid=ss.ssid
group by sem, sec, gender
order by sem;

sem sec gender Count(*)

3 B M 2
4 C F 1
4 C M 1
5 A F 1
5 A M 1
7 A F 1

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

create view test1 as

select subcode,test1
from iamarks
where USN='1cg15ee065';
Query OK.

MySQL> select * from test1;

Subcode Test1
15cs33 16
15cs53 19

Government Polytechnic, Joida Page 36

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

4. Calculate the FinalIA (average of best two test marks) and update the corresponding table for all
students.
update iamarks
set FinalIA=greatest(test1+test2,test2+test3,test1+test3)/2;

Query OK.
MySQL> select * from iamarks;

USN Subcode SSID Test1 Test2 Test3 FinalIA

1cg15cs001 15cs53 5A 18 19 15 19
1cg15cs002 15cs53 5A 15 16 14 16
1cg16me063 15cs33 3B 10 15 16 16
1cg14ec055 10cs71 7A 18 20 21 21
1cg15ee065 15cs33 3B 16 20 17 19
1cg15ee065 15cs53 4C 19 20 18 20

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.*,
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=5;

USN Sname address phone gender CAT

1cg15cs001 Abhi Tumkur 98745631 M OUTSTANDING
1cg15cs002 Amulya Gubbi 87541235 F AVERAGE
1cg15ee065 Sanjay Bangalore 96587412 M OUTSTANDING

Government Polytechnic, Joida Page 37

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

CHAPTER – 6

COMPANY DATABASE
Consider the following 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.

ER-DIAGRAM:

Government Polytechnic, Joida Page 38

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

SCHEMA:

Government Polytechnic, Joida Page 39

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

Table Creation
DEPARTMENT
create table department(
DNo int primary key,
DName varchar(15) not null,
MgrSSN int,
MgrStartDate date);
Table created.

EMPLOYEE
create table employee(
SSN int primary key,
name varchar(20) not null,
address varchar(20),
sex varchar(3),
salary real,
Super_SSN int,
DNo int,
foreign key(DNo) references department(DNo));
Table created.

DLOCATION
create table dlocation(
DLoc varchar(20) primary key,
DNo int,
foreign key(DNo) references department(DNo));
Table created.

PROJECT
create table project(
PNo int primary key,
PName varchar(20),
PLocation varchar(20),
DNo int,
foreign key(DNo) references department(DNo));
Table created.

WORKS_ON
create table works_on(
hours int,
ssn int,
foreign key(ssn) references employee(ssn),
PNo int,
foreign key(PNo) references project (PNo));
Table created.

Government Polytechnic, Joida Page 40

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

Values for tables

DEPARTMENT
insert into department values (1, 'Research', 111111, '2012-08-10');
insert into department values (2, 'Accounts', 222222, '2010-08-10');
insert into department values (3, 'AI', 333333, '2012-04-15');
insert into department values (4, 'Networks', 111111, '2014-05-18');
insert into department values (5, 'Bigdata', 666666, '2010-01-21');
select * from department;
DNo DName MgrSSN MgrStartDate
1 Research 111111 2012-08-10
2 Accounts 222222 2010-08-10
3 AI 333333 2012-04-15
4 Networks 111111 2014-05-18
5 Bigdata 666666 2010-01-21

EMPLOYEE

insert into employee values (111111, 'Raj', 'Bangalore', 'M', 700000, 111111, 1);
insert into employee values (222222, 'Rashmi', 'Mysore', 'F', 400000, 111111, 2);
insert into employee values (333333, 'Ragavi',' Tumkur', 'F', 800000, 222222, 3);
insert into employee values (444444, 'Rajesh', 'Tumkur', 'M', 650000, 444444, 3);
insert into employee values (555555, 'Raveesh', 'Bangalore', 'M', 500000, 555555, 3);
insert into employee values (666666, 'Scott', 'England', 'M', 700000, 666666, 5);
insert into employee values (777777, 'Niganth', 'Gubbi', 'M', 200000, 777777, 2);
insert into employee values (888888, 'Ramya', 'Gubbi', 'F', 400000, 888888, 3);
insert into employee values (999999, 'Vidya', 'Tumkur', 'F', 650000, 999999, 3);
insert into employee values (100000, 'Geetha', 'Tumkur', 'F', 800000, 100000, 3);

select * from employee;

+--------+---------+-----------+------+--------+-----------+------+
| ssn | name | address | sex | salary | Super_SSN | DNo |
+--------+---------+-----------+------+--------+-----------+------+
| 100000 | Geetha | Tumkur | F | 800000 | 100000 | 3 |
| 111111 | Raj | Bangalore | M | 700000 | 111111 | 1 |
| 222222 | Rashmi | Mysore | F | 400000 | 111111 | 2 |
| 333333 | Ragavi | Tumkur | F | 800000 | 222222 | 3 |
| 444444 | Rajesh | Tumkur | M | 650000 | 444444 | 3 |
| 555555 | Raveesh | Bangalore | M | 500000 | 555555 | 3 |
| 666666 | Scott | England | M | 700000 | 666666 | 5 |
| 777777 | Niganth | Gubbi | M | 200000 | 777777 | 2 |
| 888888 | Ramya | Gubbi | F | 400000 | 888888 | 3 |
| 999999 | Vidya | Tumkur | F | 650000 | 999999 | 3 |
+--------+---------+-----------+------+--------+-----------+------+

Government Polytechnic, Joida Page 41

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

DLOACTION
insert into dlocation values ('Mysore',1);
insert into dlocation values ('Tumkur',1);
insert into dlocation values ('Bangalore',2);
insert into dlocation values ('Gubbi',3);
insert into dlocation values ('Delhi',4);
insert into dlocation values ('Karwar',5);
select * from dlocation;
DLoc DNo
Mysore 1
Tumkur 1
Bangalore 2
Gubbi 3
Delhi 4
Karwar 5

PROJECT
insert into project values(111,'IOT','Gubbi',3);
insert into project values(222,'TextSpeech','Gubbi',3);
insert into project values(333,'IPSecurity','Delhi',4);
insert into project values(444,'Trafficanal','Bangalore',5);
insert into project values(555,'CloudSec','Delhi',1);
insert into project values(666,'TextSpeech','Gubbi',2);
select * from project;
+-----+-------------+-----------+------+
| PNo | PName | PLocation | DNo |
+-----+-------------+-----------+------+
| 111 | IOT | Gubbi | 3 |
| 222 | TextSpeech | Gubbi | 3 |
| 333 | IPSecurity | Delhi | 4 |
| 444 | Trafficanal | Bangalore | 5 |
| 555 | CloudSec | Delhi | 1 |
| 666 | TextSpeech | Gubbi | 2 |
+-----+-------------+-----------+------+

WORKS_ON

insert into works_on values(4,666666,333);

insert into works_on values(2,666666,111);
insert into works_on values(3,111111,222);
insert into works_on values(2,555555,222);
insert into works_on values(4,333333,111);
insert into works_on values(6,444444,111);
insert into works_on values(2,222222,111);

Government Polytechnic, Joida Page 42

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

select * from works_on;

Hours Ssn PNo

4 666666 333

2 666666 111

3 111111 222

2 555555 222

4 333333 111

6 444444 111

2 222222 111

QUERIES

1. Make a list of all project numbers for projects that involve an employee whose name is ‘Scott’,
either as a worker or as a manager of the department that controls the project.
(select distinct PNo
from project p, department d, employee e
where p.dno=d.dno and e.ssn=d.mgrssn and name='Scott')
union
( select distinct PNo
from works_on w, employee e
where w.ssn=e.ssn and name='Scott');

PNo
444
333
111

2. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent
raise.
select name, 1.1*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';

name Incr_sal
Scott 770000.0000000001
Ragavi 880000.0000000001
Rajesh 715000
Rashmi 440000.00000000006

Government Polytechnic, Joida Page 43

Database System Concepts and PL/SQL- 20CS34P III Sem CSE

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(salary),max(salary), min(salary), avg(salary)

from employee e, department d
where DName='Accounts' and d.dno=e.dno;

Sum(salary) Max(salary) Min(salary) Avg(salary)

600000 400000 200000 300000

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

select name
from employee e
where not exists (select DNo
from project p
where e.dno=p.dno and DNo<>5);

Name
Scott

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 DNo, count(ssn)

from employee
where salary>600000 and DNo in (select DNo
from employee
group by DNo
having count(ssn)>5);

DNo Count(ssn)

3 4

Government Polytechnic, Joida Page 44