18CSL58 DBMS LAB

HKBK COLLEGE OF ENGINEERING

(Affiliated to VTU, Belgaum and Approved by AICTE)
BANGALORE -560045
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

DBMS LABORATORY WITH MINI PROJECT

18CSL58
As per VTU - Choice Based Credit System - 18 Scheme
(Effective from the academic year of 2018 -2019)
V Semester CSE

LABORATORY MANUAL

Prepared By
Dr. C. Viji
Prof. KSN Sushma
Prof. Rezni

Verified By

DQAC HOD

CSE @ HKBKCE 1 2022-23

18CSL58 DBMS LAB

HKBK COLLEGE OF ENGINEERING

(Affiliated to VTU, Belgaum and Approved by AICTE)
BANGALORE -560045
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

Vision and Mission of the Institution

Vision
To empower students through wholesome education and enable the students to develop into
highly qualified and trained professionals with ethics and emerge as responsible citizen with
broad outlook to build a vibrant nation.

Mission
 To achieve academic excellence in science, engineering and technology through
dedication to duty, innovation in teaching and faith in human values.
 To enable our students to develop into outstanding professional with high ethical
standards to face the challenges of 21st century.
 To provide educational opportunities to the deprived and weaker section of the
society to uplift their socioeconomic status.

Vision and Mission of the CSE

Department

Vision
To advance the intellectual capacity of the nation and the international community by imparting
knowledge to graduates who are globally recognized as innovators, entrepreneur and competent
professionals.

Mission
 To provide excellent technical knowledge and computing skills to make the graduates
globally competitive with professional ethics.
 To involve in research activities and be committed to lifelong learning to make positive
contributions to the society

CSE @ HKBKCE 2 2022-23

18CSL58 DBMS LAB

HKBK COLLEGE OF ENGINEERING

(Affiliated to VTU, Belgaum and Approved by AICTE)
BANGALORE -560045
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

Programme Outcomes
PO1 Engineering Knowledge: Apply knowledge of mathematics, science, engineering
fundamentals and an engineering specialization to the solution of complex engineering
problems.
PO2 Problem Analysis: Identify, formulate, research literature and analyze
complex engineering problems reaching substantiated conclusions using firstprinciples
of mathematics, natural sciences and engineering sciences
PO3 Design/Development of Solutions: Design solutions for complex engineering problems
and design system components or processes that meet specified needswith appropriate
consideration for public health and safety, cultural, societal and environmental
considerations.
PO4 Conduct investigations of complex problems: Using research-basedknowledge and
research methods including design of experiments, analysis and interpretation of data
and synthesis of information to provide valid conclusions.
PO5 Modern Tool Usage: Create, select and apply appropriate techniques, resources and
modern engineering and IT tools including prediction and modeling to complex
engineering activities with an under- standing of the limitations.
PO6 The Engineer and Society: Apply reasoning informed by contextual knowledge to
assess societal, health, safety, legal and cultural issues and the consequent
responsibilities relevant to professional engineering practice.
PO7 Environment and Sustainability: Understand the impact of professional engineering
solutions in societal and environmental contexts and demonstrateknowledge of and need
for sustainable development.
PO8 Ethics: Apply ethical principles and commit to professional ethics andresponsibilities
and norms of engineering practice.
PO9 Individual and Team Work: Function effectively as an individual, and as amember
or leader in diverse teams and in multi-disciplinary settings.
PO10 Communication: Communicate effectively on complex engineering activities with the
engineering community and with society at large, such as being able to comprehend and
write effective reports and design documentation, make effective presentations and give
and receive clear instructions.
PO11 Project Management and Finance: Demonstrate knowledge and understanding of
engineering and management principles and apply these to one’s own work, as a
member and leader in a team, to manage projects and in multidisciplinary
environments.
PO12 Life-long Learning: Recognize the need for and have the preparation andability to
engage in independent and life- long learning in the broadest context of technological
change.

CSE @ HKBKCE 3 2022-23

18CSL58 DBMS LAB

HKBK COLLEGE OF ENGINEERING

(Affiliated to VTU, Belgaum and Approved by AICTE)
BANGALORE -560045
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

Programme Specific Outcomes

PSO1 Problem-Solving Skills: An ability to investigate and solve a problem by analysis,
interpretation of data, design and implementation through appropriate techniques, tools
and skills.
PSO2 Professional Skills: An ability to apply algorithmic principles, computing
skills and computer science theory in the modelling and design of computer-based
systems.
PSO3 Entrepreneurial Ability: An ability to apply design, development principles and
management skills in the construction of software product of varying complexity to
become an entrepreneur

Programme Educational Objectives

PEO-1 To provide students with a strong foundation in engineering fundamentals an din the
computer science and engineering to work in the global scenario.
PEO-2 To provide sound knowledge of programming and computing techniques and good
communication and interpersonal skills so that they will be capable of analyzing,
designing and building innovative software systems.
PEO-3 To equip students in the chosen field of engineering and related fields to enable him to
work in multidisciplinary teams.
PEO-4 To inculcate in students professional, personal and ethical attitude to relate engineering
issues to broader social context and become responsible citizen.
PEO-5 To provide students with an environment for life-long learning which allow them to
successfully adapt to the evolving technologies throughout their professional carrier and
face the global challenges.

CSE @ HKBKCE 4 2022-23

18CSL58 DBMS LAB

COURSE OUTCOMES
CO1 Create, Update and query on the database.
CO2 Demonstrate the working of different concepts of DBMS
CO3 Implement, analyze and evaluate the project developed for an application.

Mapping of COs to POs to COs

PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12 PSO1 PSO2 PSO3
CO1 2 - 3 - 3 - - - 1 - 3 1
CO2 2 1 - 3 3 - - - 3 - - - - - -
CO3 2 - 3 3 3 - - 3 3 3 1 - - - 3
CO4 - - - - - - - - - - - - - - -
CO5 - - - - - - - - - - - - - - -
Avg 2 3 3 3 3 2.33 3 2 1 3

No Correlation -
Low 1
Moderate 2
High 3

CSE @ HKBKCE 5 2022-23

18CSL58 DBMS LAB

Syllabus

DBMS LABORATORY WITH MINI PROJECT

Course Objectives:

This course will enable students to

 Foundation knowledge in dat

practice to groomstu
developers.
 Str

PART-A: SQL Programming (Max. Exam Mks. 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 Mks. 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:
Part A: SQL Programming

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.

CSE @ HKBKCE 6 2022-23

18CSL58 DBMS LAB

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

CSE @ HKBKCE 7 2022-23

18CSL58 DBMS LAB

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

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.

Conduct of Practical Examination:

 Experiment distribution
o For laboratories having only one part: Students are allowed to pick one
experiment fromthe lot with equal opportunity.
o For laboratories having PART A and PART B: Students are allowed to pick one
experiment from PART A and one experiment from PART B, with equal
opportunity.
 Change of experiment is allowed only once and marks allotted for procedure to be made
zero ofthe changed part only.
 Marks Distribution (Courseed to change in accoradance with university regulations)
 For laboratories having only one part – Procedure + Execution + Viva-Voce: 15+70+15
=
100 Marks
 For laboratories having PART A and PART B
i. Part A – Procedure + Execution + Viva = 6 + 28 + 6 = 40 Marks
ii Part B – Procedure + Execution + Viva = 9 + 42 + 9 = 60 Marks

CSE @ HKBKCE 8 2022-23

18CSL58 DBMS LAB

List of Experiments

S.No. CONTENTS
PART-A: SQL Programming
1. EXPERITMENT 1 - Library Database

2. EXPERITMENT 2 - Order Database

3. EXPERITMENT 3 - Movie Database

4. EXPERITMENT 4 - College Database

5. EXPERITMENT 5 - Company Database

PART-B: Mini Project

CSE @ HKBKCE 9 2022-23

18CSL58 DBMS LAB

PART-A: SQL Programming

Introduction
Data are known facts that can be recorded and that have implicit meaning. A
Database is collection of related data. A database management system (DBMS) is a
collection of programs that enables users to create and maintain a database. The DBMS is
a general-purpose software system that facilitates the processes of defining, constructing,
manipulating, and sharing databases among various users and applications.

Relational database management system (RDBMS) is a database management

system (DBMS) that is based on the relational model as introduced by E. F. Codd. A
relational database system contains one or more objects called tables. The data or
information for the database are stored in these tables. Tables are uniquely identified by
their names and are comprised of columns and rows. Columns contain the column name,
data type, and any other attributes for the column. Rows contain the records or data for the
columns.

The name SQL is presently expanded as Structured Query Language. Originally,

SQL was called SEQUEL (Structured English QUEry Language) and was designed and
implemented at IBM Research as the interface for an experimental relational database
system called SYSTEM R. SQL is now the standard language for commercial relational
DBMSs.

SQL is a comprehensive database language: It has statements for data definitions,

queries, and updates. Hence, it is both a DDL and a DML. In addition, it has facilities for
defining views on the database, for specifying security and authorization, for defining
integrity constraints, and for specifying transaction controls. It also has rules for
embedding SQL statements into a general-purpose programming language such as Java,
COBOL, or C/C++.

SQL Data Definition and Data Types

SQL uses the terms table, row, and column for the formal relational model terms

relation, tuple, and attribute respectively.

The main SQL command for data definition is the CREATE statement, which can
be used to create schemas, tables (relations) as well as other constructs such as views,
assertions, and triggers.
A schema is created via the CREATE SCHEMA statement, which can include all
the schema elements definitions.
Create schema company authorization ‘Jsmith’;

CSE @ HKBKCE 10 2022-23

18CSL58 DBMS LAB

CREATE TABLE Command:

The CREATE TABLE command is used to specify a new relation by giving it a name
and specifying its attributes and initial constraints.
Create table table_name (
Attribute_name1 datatype [constraints],
Attribute_name2 datatype [constraints],
....
Attribute_namen datatype[constraints],
[integrity constraint1, … integrity_constaintk]
);
Create table department
(

Dname varchar(15) not null,

Dnumber int not null,
Mgr_ssn char(9) not null,
Mgr_start_date date,
Primary key (Dnumber),
Unique (Dname),
Foreign key (Mgr_ssn) references employee(Ssn)
);

Data Types:
 Numeric: NUMBER, NUMBER(s,p), INTEGER, INT, FLOAT, DECIMAL
 Character: CHAR(n), VARCHAR(n), VARCHAR2(n), CHAR VARYING(n)
 Bit String: BLOB, CLOB
 Boolean: true, false, and null
 Date and Time: DATE (YYYY-MM-DD) TIME( HH:MM:SS)
 Timestamp: DATE + TIME

SQL Constraints:

1. NOT NULL Constraint: By default, a column can hold NULL values. The NOT
NULL constraint enforces a column to NOT accept NULL values. This enforces
a field to always contain a value, which means that you cannot insert a new
record, or update a record without adding a value to this field.

Dname VARCHAR(15) NOT NULL

2. DEFAULT Constraint: The DEFAULT constraint is used to provide a default

value for a column. The default value will be added to all new records IF no other
value is specified.

college varchar(25) DEFAULT 'HKBKCE'

CSE @ HKBKCE 11 2022-23
18CSL58 DBMS LAB

3. CHECK Constraint: The CHECK constraint is used to limit the value range
that can be placed in a column.

age int CHECK (age >=18)

4. PRIMARY KEY Constraint: The PRIMARY KEY constraint uniquely identifies

each record in a database table. Primary keys must contain UNIQUE values, and
cannot contain NULL values. A table can have only one primary key, which may
consist of single or multiple fields.

PRIMARY KEY (Dnumber)

5. UNIQUE Constraint: The UNIQUE constraint ensures that all values in a columnare
different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for
uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically
has a UNIQUE constraint. However, you can have many UNIQUE constraints per table,
but only one PRIMARY KEY constraint per table.

ID int NOT NULL UNIQUE

6. FOREIGN KEY Constraint: A FOREIGN KEY 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. The table containing the foreign key
is called the child table, and the table containing the candidate key is called the
referenced or parent table.

FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)

Referential Triggered Action Clause:

Set NULL: Sets the column value to NULL when you delete the parent table row.

Set DEFAULT: sets the column value to DEFAULT when you delete the parent
table row.

CASCADE: CASCADE will propagate the change when the parent changes. If
you delete a row, rows in constrained tables that reference that row will also be
deleted, etc.

CSE @ HKBKCE 12 2022-23

18CSL58 DBMS LAB

RESTRICT: RESTRICT causes you cannot delete a given parent row if a child
row exists that references the value for that parent row.

NO ACTION: NO ACTION and RESTRICT are very much alike. When an

UPDATE or DELETE statement is executed on the referenced table, the DBMS
verifies at the end of the statement execution that none of the referential
relationships are violated. in short child row no concern if parent row delete or
update.

An option must be qualified with either ON DELETE or ON UPDATE.

FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) ON

DELETE SETDEFAULT ON UPDATE CASCADE);

The DROP Command:

The DROP command can be used to drop named schema elements, such as tables,
domains,or constraints. One can also drop a schema. For example, if a whole
schema is no longer needed, the DROP SCHEMA command can be used. There
are two drop behavior options:

CASCADE and RESTRICT. For example, to remove the COMPANY database

schema andall its tables, domains, and other elements, the CASCADE option is
used as follows:

Drop schema company cascade;

If the RESTRICT option is chosen in place of CASCADE, the schema is dropped only
if ithas no elements in it; otherwise, the DROP command will not be executed. To use
the RESTRICT option, the user must first individually drop each element in the
schema, then drop the schema itself.

Notice that the DROP TABLE command not only deletes all the records in the table if
successful, but also removes the table definition from the catalog.

CSE @ HKBKCE 13 2022-23

18CSL58 DBMS LAB

The ALTER Command

The definition of a base table or of other named schema elements can be changed by using
the ALTER command. For base tables, the possible alter table actions include adding or
dropping a column (attribute), changing a column definition, and adding or dropping table
constraints.

Alter table employee add column job varchar(12);

Alter table employee drop column address cascade;

Alter table department alter column mgr_ssn drop default;

Alter table department alter column mgr_ssn set default ‘333445555’;

Alter table employee drop constraint empsuperfk cascade;

Basic Retrieval Queries in SQL:

SQL has one basic statement for retrieving information from a database: the SELECT
statement.
SELECT <attribute list>
FROM <table list>
WHERE <condition>;

where
■ <attribute list> is a list of attribute names whose values are to be retrieved by the query.
■ <table list> is a list of the relation names required to process the query.
■ <condition> is a conditional (Boolean) expression that identifies the tuples to be
retrieved by the query.

In SQL, the basic logical comparison operators for comparing attribute values with one
another and with literal constants are =, <, <=, >, >=, and <>.

Retrieve the birth date and address of the employee(s) whose nameis ‘John B. Smith’.

select Bdate, Address

from employee
where Fname=‘John’ and Minit=‘B’ and Lname=‘Smith’;

CSE @ HKBKCE 14 2022-23

18CSL58 DBMS LAB

This query involves only the EMPLOYEE relation listed in the FROM clause. The
query selects the individual EMPLOYEE tuples that satisfy the condition of the WHERE
clause, then projects the result on the Bdate and Address attributes listed in the SELECT
clause. The SELECT clause of SQL specifies the attributes whose values are to be
retrieved, which are called the projection attributes, and the WHERE clause specifies the
boolean condition that must be true for any retrieved tuple, which is known as the
selection condition.

A query that involves only selection and join conditions plus projection attributes is known
as a select-project-join query.

Retrieve the name and address of all employees who work for the ‘Research’ department.

select Fname, Lname, Address

from employee, department
where Dname=‘Research’ and Dnumber=Dno;

In the WHERE clause, the condition Dname = ‘Research’ is a selection condition that
chooses the particular tuple of interest in the DEPARTMENT table, because Dname is an
attribute of DEPARTMENT. The condition Dnumber = Dno is called a join condition,
because it combines two tuples: one from DEPARTMENT and one from EMPLOYEE,
whenever the value of Dnumber in DEPARTMENT is equal to the value of Dno in
EMPLOYEE.

For each employee, retrieve the employee’s first and last name and the first and lastname of his or her
immediate supervisor.

select E.Fname, E.Lname, S.Fname, S.Lname

from EMPLOYEE AS E, EMPLOYEE AS S
where E.Super_ssn=S.Ssn;

In this case, we are required to declare alternative relation names E and S, called aliases or
tuple variables, for the EMPLOYEE relation. An alias can follow the keyword AS, or it
can directly follow the relation name.

Unspecified WHERE Clause and Use of the Asterisk

A missing WHERE clause indicates no condition on tuple selection; hence, all tuples of
the relation specified in the FROM clause qualify and are selected for the query result.

select Ssn
from employee;
CSE @ HKBKCE 15 2022-23
18CSL58 DBMS LAB

To retrieve all the attribute values of the selected tuples, we do not have to list the attribute
names explicitly in SQL; we just specify an asterisk (*), which stands for all the attributes.
select *
from employee
where Dno=5;

DISTINCT:

DISTINCT keyword in the SELECT clause, meaning that only distinct tuples should
remain in the result.
select distinct Salary
from employee;

Set Operations:

SQL has directly incorporated some of the set operations from mathematical set theory.
There are set union (UNION), set difference (EXCEPT) and set intersection
(INTERSECT) operations.

The relations resulting from these set operations are sets of tuples; that is, duplicate tuples
are eliminated from the result. These set operations apply only to union-compatible
relations, so we must make sure that the two relations on which we apply the operation
have the same attributes and that the attributes appear in the same order in both relations.

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

CSE @ HKBKCE 16 2022-23

18CSL58 DBMS LAB

(select distinct Pnumber

from project, department, employee

where Dnum=Dnumber and Mgr_ssn=Ssn
and Lname=‘Smith’ )

union
( select distinct Pnumber
from project, works_on, employee
where Pnumber=Pno and Essn=Ssn
and Lname=‘Smith’ );

The first SELECT query retrieves the projects that involve a ‘Smith’ as manager of the
department that controls the project, and the second retrieves the projects that involve a
‘Smith’ as a worker on the project. Notice that if several employees have the last name
‘Smith’, the project names involving any of them will be retrieved. Applying the UNION
operation to the two SELECT queries gives the desired result.

SQL also has corresponding multiset operations, which are followed by the keyword ALL
(UNION ALL, EXCEPT ALL, INTERSECT ALL). Their results are multisets (duplicates
are not eliminated).

Substring Pattern Matching and Arithmetic Operators:

The first feature allows comparison conditions on only parts of a character string, using the
LIKE comparison operator. This can be used for string pattern matching. Partial strings
are specified using two reserved characters: % replaces an arbitrary number of zero or
more characters, and the underscore (_) replaces a single character.

CSE @ HKBKCE 17 2022-23

18CSL58 DBMS LAB

Retrieve all employees whose address is in Houston, Texas.

select Fname, Lname

from employee

where Address like ‘%Houston,TX%’;

To retrieve all employees who were born during the 1950s, ‘5’must be the third character
of the string (according to our format for date), so we use the value ‘_ _ 5 ’,
with each underscore serving as a placeholder for an arbitrary character.

Find all employees who were born during the 1950s.

select Fname, Lname

from employee

where Bdate like ‘_ _ 5 ’;

If an underscore or % is needed as a literal character in the string, the character should be

preceded by an escape character, which is specified after the string using the keyword
ESCAPE.

Comparison operator Between

Retrieve all employees in department 5 whose salary is between $30,000 and $40,000.

select *
from employee
where (Salary BETWEEN 30000 AND 40000) and Dno = 5;

The condition (Salary BETWEEN 30000 AND 40000) is equivalent to the condition
((Salary >= 30000) AND (Salary <= 40000)).

Ordering of Query Results

SQL allows the user to order the tuples in the result of a query by the values of one or more
of the attributes that appear in the query result, by using the ORDER BY clause.

CSE @ HKBKCE 18 2022-23

18CSL58 DBMS LAB

The default order is in ascending order of values. We can specify the keyword DESC if
we want to see the result in a descending order of values. The keyword ASC can be used
to specify ascending order explicitly.

Retrieve a list of employees and the projects they are working on, ordered by department and, within
each department, ordered alphabetically by last name, then first name.

select D.Dname, E.Lname, E.Fname, P.Pname

from department d, employee e, works_on w, project p
where D.Dnumber= E.Dno and E.Ssn= W.Essn and W.Pno= P.Pnumber
ORDER BY D.Dname, E.Lname, E.Fname;

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

For each department, retrieve the department number, the number of employees inthe
department, and their average salary.

select dno, count (*), avg (salary)

from employee group by dno

The EMPLOYEE tuples are divided into groups. Each group having the same value for the
grouping attribute DNO. The COUNT and AVG functions are applied to each such group
of tuples separately. The SELECT-clause includes only the grouping attribute and the
functions to be applied on each group of tuples. A join condition can be used in
conjunctionwith grouping.

THE HAVING-CLAUSE

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

CSE @ HKBKCE 19 2022-23

18CSL58 DBMS LAB

For each project on which more than two employees work, retrieve the project number, project name, and
the number of employees who work on that project.

select pnumber, pname, count (*) from project,

works_on where pnumber=pno group by
pnumber, pname
having count (*) > 2

Nested Queries

A complete SELECT query, called a nested query, can be specified within the WHERE-
clause of another query, called the outer query.

Retrieve the name and address of all employees who work for the 'Research'department.

select fname, lname, address

from employee

where dno in (select dnumber

from department
where dname='Research' );

Note: The nested query selects the number of the 'Research' department. The outer query
selects an EMPLOYEE tuple if its DNO value is in the result of either nested query.

The comparison operator IN compares a value v with a set (or multi-set) of values V, and
evaluates to TRUE if v is one of the elements in V

In general, we can have several levels of nested queries. A reference to an unqualified

attribute refers to the relation declared in the innermost nested query.

CSE @ HKBKCE 20 2022-23

18CSL58 DBMS LAB

Correlated Queries

If a condition in the WHERE-clause of a nested query references an attribute of a

relation declared in the outer query, the two queries are said to be correlated.

If a condition in the WHERE-clause of a nested query references an attribute of a

relation declared in the outer query, the two queries are said to be correlated. The result of
a correlated nested query is different for each tuple (or combination of tuples) of the
relation(s) the outer query

Retrieve the name of each employee who has a dependent with the same first name asthe employee.

select e.fname, e.lname from employee as e where e.ssn in (select essn from dependent
where essn=e.ssn and e.fname=dependent_name)

The nested query has a different result in the outer query. A query written with nested
SELECT... FROM… WHERE... blocks and using the = or IN comparison operators can
always be expressed as a single block query.

The Exists Function

EXISTS is used to check whether the result of a correlated nested query is empty
(contains no tuples) or not. We can formulate the above Query in an alternative form that
uses EXIST.

select fname, lname from employee

where exists (select * from dependent where ssn=essn and
fname = dependent_name)

Retrieve the names of employees who have no dependents.

select fname, lname from employee

where not exists
(select * from dependent where ssn=essn)

Note: In this correlated nested query retrieves all DEPENDENT tuples related to an
EMPLOYEE tuple. If none exist, the EMPLOYEE tuple is selected

CSE @ HKBKCE 21 2022-23

18CSL58 DBMS LAB

EXPLICIT SETS

It is also possible to use an explicit (enumerated) set of values in the WHERE-clause

rather than a nested query.
Retrieve the social security numbers of all employees who work on project number
1, 2, or 3.
select distinct essn from works_on where pno in (1, 2, 3)

NULLS IN SQL QUERIES

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

Retrieve the names of all employees who do not have supervisors.

select fname, lname from employee

where superssn is null

Note: If a join condition is specified, tuples with NULL values for the join attributes are not
included in the result.

Aggregate functions

Include COUNT, SUM, MAX, MIN, and AVG.

Find the maximum salary, the minimum salary, and the average salary among allemployees.

SELECT MAX (SALARY), MIN(SALARY), AVG(SALARY)

FROM EMPLOYEE

Note: Some SQL implementations may not allow more than one function in the SELECT-
claus

CSE @ HKBKCE 22 2022-23

18CSL58 DBMS LAB

INSERT, DELETE, and UPDATE Statements in SQL:

In SQL, three commands can be used to modify the database: INSERT, DELETE, and
UPDATE.

The INSERT Command

In its simplest form, INSERT is used to add a single tuple to a relation. We must specify
the relation name and a list of values for the tuple. The values should be listed in the same
order in which the corresponding attributes were specified in the CREATE TABLE
command.

insert into employee

values ( ‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’, ’98 Oak Forest, Katy, TX’,
‘M’, 37000, ‘653298653’, 4 );

A second form of the INSERT statement allows the user to specify explicit attribute names
that correspond to the values provided in the INSERT command. This is useful if a
relation has many attributes but only a few of those attributes are assigned values in the
new tuple.

Insert into employee (Fname, Lname, Dno, Ssn)

values (‘Richard’, ‘Marini’, 4, ‘653298653’);

The DELETE Command

The DELETE command removes tuples from a relation. It includes a WHERE

clause, similar to that used in an SQL query, to select the tuples to be deleted. Tuples are
explicitly deleted from only one table at a time. However, the deletion may propagate to
tuples in other relations if referential triggered actions are specified in the referential
integrity constraints of the DDL.

delete from employee

where Lname=‘Brown’;

Depending on the number of tuples selected by the condition in the WHERE

clause, zero, one, or several tuples can be deleted by a single DELETE command. A
missing WHERE clause specifies that all tuples in the relation are to be deleted; however,
the table remains in the database as an empty table.

CSE @ HKBKCE 23 2022-23

18CSL58 DBMS LAB

DELETE FROM EMPLOYEE;

CSE @ HKBKCE 24 2022-23

18CSL58 DBMS LAB

The UPDATE Command

The UPDATE command is used to modify attribute values of one or more selected
tuples. As in the DELETE command, a WHERE clause in the UPDATE command selects
the tuples to be modified from a single relation. However updating a primary key value
may propagate to the foreign key values of tuples in other relations if such a referential
triggered action is specified in the referential integrity constraints of the DDL.

An additional SET clause in the UPDATE command specifies the attributes to be

modified and their new values.

For example, to change the location and controlling department number of projectnumber 10 to ‘Bellaire’
and 5, respectively.

Update project

set Plocation = ‘Bellaire’, Dnum = 5

where Pnumber=10;

Several tuples can be modified with a single UPDATE command. An example is to give all
employees in the ‘Research’ department a 10 percent raise in salary,

Update employee

set Salary = Salary * 1.1

where Dno = 5;

It is also possible to specify NULL or DEFAULT as the new attribute value. Notice that
each UPDATE command explicitly refers to a single relation only. To modify multiple
relations, we must issue several UPDATE commands.

CSE @ HKBKCE 25 2022-23

18CSL58 DBMS LAB

VIEWS IN SQL

 A view is a single virtual table that is derived from other tables. The other
tables could be base tables or previously defined view.
 Allows for limited update operations Since the table may not physically be stored
 Allows full query operations
 A convenience for expressing certain operations
 A view does not necessarily exist in physical form, which limits the possible
update operations that can be applied to views.

Additional features of SQL

SQL has various techniques for writing programs in various programming languages that
include SQL statements to access one or more databases. These include embedded (and
dynamic) SQL, SQL/CLI (Call Level Interface) and its predecessor ODBC (Open Data
Base Connectivity), and SQL/PSM (Persistent Stored Modules).

SQL has transaction control commands. These are used to specify units of database
processing for concurrency control and recovery purposes. SQL has language constructs
for specifying the granting and revoking of privileges to users. Privileges typically
correspond to the right to use certain SQL commands to access certain relations. Each
relation is assigned an owner, and either the owner or the DBA staff can grant to selected
users the privilege to use an SQL statement—such as SELECT INSERT, DELETE, or
UPDATE— to access the relation. In addition, the DBA staff can grant the privileges to
create schemas, tables, or views to certain users. These SQL commands—called GRANT
and REVOKE.

SQL has language constructs for creating triggers. These are generally referred to as active
database techniques, since they specify actions that are automatically triggered by events
such as database updates.

CSE @ HKBKCE 26 2022-23

18CSL58 DBMS LAB

PROGRAM 1
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,
numberof copies in each branch, etc.
2. Get the particulars of borrowers who have borrowed more than 3 books, but from
Jan2017 to Jun 2017.
3. Delete a book in BOOK table. Update the contents of other tables to reflect this
datamanipulation operation.
4. Partition the BOOK table based on year of publication. Demonstrate its working
with asimple query.
5. Create a view of all books and its number of copies that are currently available in
theLibrary.

CSE @ HKBKCE 27 2022-23

 18CSL58 DBMS LAB

Schema Diagram:

Creating Publisher table :-

CREATE TABLE Publisher ( Name varchar(200) PRIMARY KEY, Address varchar(400), Phone
varchar(10) );

Inserting values in Publisher table:-

INSERT INTO publisher (`Name`, `Address`, `Phone`) VALUES ('Pearson', 'New Delhi', '8976789543');
INSERT INTO `publisher` (`Name`, `Address`, `Phone`) VALUES ('PHI', 'New Delhi', '9876785333');
INSERT INTO `publisher` (`Name`, `Address`, `Phone`) VALUES ('Tata McGraw-Hill', 'New Delhi',
'9876785432');
INSERT INTO `publisher` (`Name`, `Address`, `Phone`) VALUES ('Technical Publications', 'Pune',
'9823451678');
INSERT INTO `publisher` (`Name`, `Address`, `Phone`) VALUES ('Universities Press', 'Hyderabad',
'9123214566');

Creating Book Table:-

CREATE TABLE Book ( Book_id int PRIMARY KEY,

CSE @ HKBKCE 28 2022-23

 18CSL58 DBMS LAB

Title varchar(200),
Publisher_Name varchar(200),
Pub_Year int,
FOREIGN KEY (Publisher_Name) REFERENCES Publisher(Name) ON DELETE SET NULL );

Inserting values in Book:-

INSERT INTO `book` (`Book_id`, `Title`, `Publisher_Name`, `Pub_Year`) VALUES ('1', 'Web
Engineering', 'Tata McGraw-Hill', '2008');
INSERT INTO `book` (`Book_id`, `Title`, `Publisher_Name`, `Pub_Year`) VALUES ('2', 'Unix System
Programming', 'Technical Publications', '2013');
INSERT INTO `book` (`Book_id`, `Title`, `Publisher_Name`, `Pub_Year`) VALUES ('3', 'Design of
UNIX OS', 'PHI', '2008');
INSERT INTO `book` (`Book_id`, `Title`, `Publisher_Name`, `Pub_Year`) VALUES ('4', 'Formal
Languages & Automata Theory', 'Technical Publications', '2012');
INSERT INTO `book` (`Book_id`, `Title`, `Publisher_Name`, `Pub_Year`) VALUES ('5', 'Fundamentals
of DS in C', 'Universities Press', '2008'); INSERT INTO `book` (`Book_id`, `Title`, `Publisher_Name`,
`Pub_Year`) VALUES ('6', 'Introduction to Automata Theory', 'Pearson', '2006'); INSERT INTO `book`
(`Book_id`, `Title`, `Publisher_Name`, `Pub_Year`) VALUES ('7', 'Operating Systems', 'Pearson', '2003');
INSERT INTO `book` (`Book_id`, `Title`, `Publisher_Name`, `Pub_Year`) VALUES ('8', 'OOAD', 'Tata
McGraw-Hill', '2012');
INSERT INTO `book` (`Book_id`, `Title`, `Publisher_Name`, `Pub_Year`) VALUES ('9', 'OOAD',
'Universities Press', '2012');
INSERT INTO `book` (`Book_id`, `Title`, `Publisher_Name`, `Pub_Year`) VALUES ('10', 'OS',
'Technical Publications', '2012');
INSERT INTO `book` (`Book_id`, `Title`, `Publisher_Name`, `Pub_Year`) VALUES ('11', 'PCD',
'Pearson', '2013');

Create table Book_Author:-

CREATE TABLE Book_Authors (Book_id int NOT NULL, Author_Name varchar(200) NOT NULL,
PRIMARY KEY (Book_id, Author_name), FOREIGN KEY (Book_id) REFERENCES Book(Book_id)
ON DELETE CASCADE );

Inserting values in table Book_Author:-

INSERT INTO `book_authors` (`Book_id`, `Author_Name`) VALUES ('1', 'Roger S Pressman');

INSERT INTO `book_authors` (`Book_id`, `Author_Name`) VALUES ('2', 'I A Dhotre');
INSERT INTO `book_authors` (`Book_id`, `Author_Name`) VALUES ('3', 'Maurice J Bach');
INSERT INTO `book_authors` (`Book_id`, `Author_Name`) VALUES ('4', 'A A Puntambekar');
INSERT INTO `book_authors` (`Book_id`, `Author_Name`) VALUES ('5', 'Horowitz');
INSERT INTO `book_authors` (`Book_id`, `Author_Name`) VALUES ('6', 'Hopcroft');
INSERT INTO `book_authors` (`Book_id`, `Author_Name`) VALUES ('7', 'Deitel');
INSERT INTO `book_authors` (`Book_id`, `Author_Name`) VALUES ('8', 'Simon Bennett');
INSERT INTO `book_authors` (`Book_id`, `Author_Name`) VALUES ('9', 'Brahma Dathan');
INSERT INTO `book_authors` (`Book_id`, `Author_Name`) VALUES ('10', 'I A Dhotre');

CREATE TABLE Library_Branch:-

CREATE TABLE Library_Branch ( Branch_id int PRIMARY KEY, Branch_name varchar(200) NOT
NULL, Address varchar(400) );

CSE @ HKBKCE 29 2022-23

 18CSL58 DBMS LAB

Inserting values in library_branch table:-

INSERT INTO `library_branch` (`Branch_id`, `Branch_name`, `Address`) VALUES ('101', 'BranchA',
'Nagawara');
INSERT INTO `library_branch` (`Branch_id`, `Branch_name`, `Address`) VALUES ('102', 'BranchB',
'KG Halli');
INSERT INTO `library_branch` (`Branch_id`, `Branch_name`, `Address`) VALUES ('103', 'BranchC',
'Indira Nagar');
INSERT INTO `library_branch` (`Branch_id`, `Branch_name`, `Address`) VALUES ('104', 'BranchD', 'M
G Road');
INSERT INTO `library_branch` (`Branch_id`, `Branch_name`, `Address`) VALUES ('105', 'BranchE', 'K
R Circle');

CREATE TABLE Book_Copies:-

CREATE TABLE Book_Copies (Book_id int NOT NULL, Branch_id int NOT NULL, No_of_copies int
DEFAULT 1, 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) ON DELETE CASCADE);

Inserting values in `book_copies:-

INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('1', '101', '2');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('1', '104', '3');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('2', '102', '10');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('3', '103', '5');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('4', '105', '6');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('5', '101', '7');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('6', '102', '10');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('6', '105', '8');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('7', '104', '12');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('8','103', '15');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('9', '102', '6');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('10', '103', '2');
INSERT INTO `book_copies` (`Book_id`, `Branch_id`, `No_of_copies`) VALUES ('10', '101', '4');

CREATE TABLE Borrower:-

CREATE TABLE Borrower ( Card_no int PRIMARY KEY, Name varchar(200) NOT NULL );

Inserting values in Borrower table:-

INSERT INTO `borrower` (`Card_no`, `Name`) VALUES ('201', 'John Smith');
INSERT INTO `borrower` (`Card_no`, `Name`) VALUES ('202', 'Franklin'); INSERT INTO `borrower`
(`Card_no`, `Name`) VALUES ('203', 'Joyce'); INSERT INTO `borrower` (`Card_no`, `Name`) VALUES
('204', 'James'); INSERT INTO `borrower` (`Card_no`, `Name`) VALUES ('205', 'Rose');

CREATE TABLE Book_Lending:-

CREATE TABLE Book_Lending (
Book_id int NOT NULL,
Branch_id int NOT NULL,
Card_no int NOT NULL,

CSE @ HKBKCE 30 2022-23

 18CSL58 DBMS LAB

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, FOREIGN KEY
(Branch_id) REFERENCES Library_Branch(Branch_id) ON DELETE CASCADE,
FOREIGN KEY (Card_no) REFERENCES Borrower(Card_no) ON DELETE CASCADE );

Inserting values in Book_Lending:-

INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES

('1', '101', '201', '2017-07-26', '2017-08-04');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('2', '102', '201', '2017-01-17', '2017-07-27');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('5', '101', '202', '2017-04-11', '2017-04-21');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('6', '102', '204', '2017-03-06', '2017-07-16');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('7', '104', '203', '2016-12-05', '2016-12-15');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('10', '101', '205', '2017-07-04', '2017-07-14');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('4', '105', '202', '2017-01-02', '2017-07-12');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('8', '103', '201', '2017-05-09', '2017-05-19');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('3', '103', '203', '2017-06-01', '2017-06-10');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('10', '103', '205', '2017-02-01', '2017-07-10');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('6', '105', '204', '2016-11-02', '2016-11-12');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('1', '104', '202', '2017-02-10', '2017-02-20');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('9', '102', '201', '2017-04-01', '2017-04-11');
INSERT INTO `book_lending` (`Book_id`, `Branch_id`, `Card_no`, `Date_out`, `Due_date`) VALUES
('2', '102', '202', '2017-05-02', '2017-05-12');

Table: Book

CSE @ HKBKCE 31 2022-23

18CSL58 DBMS LAB

Table: Book_Authors

CSE @ HKBKCE 32 2022-23

18CSL58 DBMS LAB

Table:Book_copies

Table: Book_lending

CSE @ HKBKCE 33 2022-23

18CSL58 DBMS LAB

Table:Borrower

Table: library_branch

Table: Publisher

CSE @ HKBKCE 34 2022-23

18CSL58 DBMS LAB

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

select branch_name,c.Book_id,title,publisher_name,author_name, No_of_copies FROM

book_copies c, book b, book_authors a,library_branch l where b.book_id=c.book_id and
a.book_id=b.book_id and l.branch_id=c.Branch_id group by c.Branch_id,c.Book_id;

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

SELECT b.card_no,b.name FROM book_lending l,borrower b where

b.card_no=l.card_no and date_out between '2017-01-01' and '2017-07-01' group by
l.card_no having count(l.card_no)>3;

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

delete from book where

book_id=11;select * from book;

CSE @ HKBKCE 35 2022-23

18CSL58 DBMS LAB

select * from book_authors;

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

select * from book where Pub_Year = '2012';

CSE @ HKBKCE 36 2022-23

18CSL58 DBMS LAB

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

Create view books as select c.Book_id, title, author_name, publisher_name, pub_year,

sum(No_of_copies) from Book b,book_authors a,book_copies c where
c.Book_id=a.Book_id and a.Book_id=b.Book_id group by Book_id;

select * from book;

CSE @ HKBKCE 37 2022-23

 18CSL58 DBMS LAB

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

create table salesman:-

create table salesman
( salesman_id int primary
key,name varchar(45),
city varchar(30), commission float
);

table salesman:-
INSERT INTO salesman (salesman_id, name, city, commission) VALUES (5001, 'James', 'Bangalore',
0.15);
INSERT INTO salesman (salesman_id, name, city, commission) VALUES (5002, 'John', 'Bangalore',
0.13);
INSERT INTO salesman (salesman_id, name, city, commission) VALUES (5003, 'Alex', 'Chennai', 0.11);
INSERT INTO salesman (salesman_id, name, city, commission) VALUES (5004, 'Lyon', 'Delhi', 0.14);
INSERT INTO salesman (salesman_id, name, city, commission) VALUES (5005, 'Nail', 'Delhi', 0.12);

CSE @ HKBKCE 38 2022-23

 18CSL58 DBMS LAB

Create table customer:-

Create table customer
( Customer_id int primary
key,Cust_name varchar(35),
City varchar(25),
Grade int,
Salesman_id int,
Foreign key (salesman_id) REFERENCES salesman(salesman_id) ON DELETE
CASCADE
);

table customer:-
INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id) VALUES (3001, 'Davis',
'Bangalore', 200, 5001);
INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id) VALUES (3002, 'Rimando',
'Bangalore', 100, 5001);
INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id) VALUES (3003, 'Johns',
'Delhi', 300, 5005);
INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id) VALUES (3004, 'Brainard',
'Chennai', 200, 5002);
INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id) VALUES (3005, 'Zusi',
'Chennai', 100, 5004);
INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id) VALUES (3006, 'Shaini',
'Bangalore', 200, 5004);
INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id) VALUES (3007, 'Smith',
'Mumbai', 400, 5005);

Create table orders :-

Create table orderss
(
Ord_no int primary key,
Purchase_amt float,
Ord_date date,
Customer_id int,
Salesman_id int,
Foreign key (salesman_id) REFERENCES salesman(salesman_id) ON DELETE
CASCADE,
Foreign key (customer_id) REFERENCES customer(customer_id) ON DELETE
CASCADE );

table orders :-
INSERT INTO orders (ord_no, purchase_amt, ord_date, customer_id, salesman_id)
VALUES (7001, 150.5, '2017-01-05’, 3005, 5004);
INSERT INTO orders (ord_no, purchase_amt, ord_date, customer_id, salesman_id)
VALUES (7002, 270.5, '2017-02-10’, 3001, 5001);
INSERT INTO orders (ord_no, purchase_amt, ord_date, customer_id, salesman_id)

CSE @ HKBKCE 39 2022-23

 18CSL58 DBMS LAB

VALUES (7003, 110.5, '2016-08-17’, 3002, 5001);

INSERT INTO orders (ord_no, purchase_amt, ord_date, customer_id, salesman_id)
VALUES (7004, 948.5, '2016-09-10’, 3005, 5004);
INSERT INTO orders (ord_no, purchase_amt, ord_date, customer_id, salesman_id)
VALUES (7005, 2400, '2017-02-10’, 3002, 5001);
INSERT INTO orders (ord_no, purchase_amt, ord_date, customer_id, salesman_id)
VALUES (7006, 349, '2017-02-10’, 3004, 5002);
INSERT INTO orders (ord_no, purchase_amt, ord_date, customer_id, salesman_id)
VALUES (7007, 5500, '2017-02-10’, 3006, 5004);
INSERT INTO orders (ord_no, purchase_amt, ord_date, customer_id, salesman_id)
VALUES (7008, 65, '2017-01-05’, 3004, 5002);

Table: Salesman

Table: Customer

CSE @ HKBKCE 40 2022-23

18CSL58 DBMS LAB

Table: Orders

CSE @ HKBKCE 41 2022-23

18CSL58 DBMS LAB

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

select count(*)No_of_Customers from customer where grade > (select avg(grade) from
customer where city='Bangalore');

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

select salesman_id,name from salesman where salesman_id in (select salesman_id from

customer group by salesman_id having count(*) >1);

5001 James
5004 Lyon
5005 Nail

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

select s.salesman_id from salesman s,customer c where

s.salesman_id=c.salesman_id and s.city=c.city UNION select s.salesman_id from
salesman s,customer c where s.salesman_id=c.salesman_id and s.city!=c.city;

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

Create view maxorder as select ord_date, max (`purchase_amt`)

Highest_Sales,o.salesman_id,name from orders o,salesman s where o.salesman_id=
s.salesman_id group by ord_date;

CSE @ HKBKCE 42 2022-23

18CSL58 DBMS LAB

select * from maxorder;

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

INSERT INTO `salesman` (`salesman_id`, `name`, `city`, `commission`) VALUES

('1000', 'Livingstone', 'Hyderabad', '0.09');
INSERT INTO `customer` (`customer_id`, `cust_name`, `city`, `grade`,
`salesman_id`) VALUES ('3008', 'Gabriel', 'Hyderabad', '150', '1000');
INSERT INTO `orders` (`ord_no`, `purchase_amt`, `ord_date`, `customer_id`,
`salesman_id`) VALUES ('7009', '400', '2017-07-28', '3008', '1000');

Delete from salesman where salesman_id=1000;

CSE @ HKBKCE 43 2022-23

18CSL58 DBMS LAB

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

Schema Diagram:

CSE @ HKBKCE 44 2022-23

 18CSL58 DBMS LAB

CREATE TABLE Actor:-

CREATE TABLE ACTOR(Act_id int NOT NULL,Act_Name varchar(200),Act_Gender
varchar(20),PRIMARY KEY (Act_id));

INSERT VALUES IN TABLE Actor:-

INSERT INTO `actor` (`Act_Id`, `Act_Name`, `Act_Gender`) VALUES ('101', 'James', 'M'),
('102', 'Deborah', 'F'), ('103', 'Peter', 'M'), ('104', 'Robert', 'M'), ('105', 'Murray', 'M');
INSERT INTO `actor` (`Act_Id`, `Act_Name`, `Act_Gender`) VALUES ('106', 'Harrison', 'M'),
('107', 'Nicole', 'F'), ('108', 'Stephen', 'M'), ('109', 'Jack', 'M'), ('110', 'Kate', 'F');

CREATE TABLE Director:-

CREATE TABLE Director ( Dir_Id INT NOT NULL, Dir_Name VARCHAR(25) NOT NULL,
Dir_Phone INT NOT NULL, PRIMARY KEY (Dir_Id) );

INSERT VALUES IN TABLE Director:-

INSERT INTO `director` (`Dir_Id`, `Dir_Name`, `Dir_Phone`) VALUES ('201', 'Alfred','675409'), ('202',
'Jack', '689543'), ('203', 'David', '660908'), ('204', 'Michael', '656432'), ('205', 'Milos', '600944');
INSERT INTO `director` (`Dir_Id`, `Dir_Name`, `Dir_Phone`) VALUES ('206', 'Stanley', '677543'),
('207', 'Roman', '660089');

CREATE TABLE Movies:-

CREATE TABLE Movies ( Mov_Id INT NOT NULL, Mov_Title VARCHAR(50) NOT NULL,
Mov_Year INT NOT NULL, Mov_Lang VARCHAR(15) NOT NULL, Dir_Id INT NOT NULL,
PRIMARY KEY (Mov_Id), FOREIGN key (Dir_Id) references Director(Dir_Id) on DELETE CASCADE
);

INSERT VALUES IN TABLE Movies:-

INSERT INTO `movies` (`Mov_Id`, `Mov_Title`, `Mov_Year`, `Mov_Lang`, `Dir_Id`) VALUES
('1', 'Vertigo', '1994', 'English', '201'), ('2', 'Innocents', '1997', 'English', '201'),
('3', 'Deer Hunter', '1972', 'English', '202'), ('4', 'Eyes Wid Shut', '2002', 'English', '202'),
('5', 'Wings', '2016', 'English', '203'), ('6', 'Usual Suspects', '2006', 'English', '204'),
('7', 'Samurai', '2017', 'English', '205'), ('8', 'The Prestige', '2016', 'English', '206'),
('9', 'American Beauty', '2015', 'English', '201'), ('10', 'Walls', '2000', 'English', '207');

CREATE TABLE Movie_Cast:-

CREATE TABLE Movie_Cast( Act_id INT NOT NULL , Mov_Id INT NOT NULL, Role
VARCHAR(45) NOT NULL , PRIMARY KEY (Act_id, Mov_Id), FOREIGN KEY (Act_Id)
REFERENCES Actor(Act_Id) on DELETE CASCADE, FOREIGN KEY (Mov_Id) REFERENCES
movies(Mov_Id) on DELETE CASCADE );

INSERT VALUES IN TABLE Movie_Cast:-

INSERT INTO `movie_cast` (`Act_id`, `Mov_Id`, `Role`) VALUES ('101', '1', 'James'), ('101', '6', 'Fero'),
('101', '2', 'Eddie'), ('102', '5', 'July'), ('103', '3', 'John'), ('104', '7', 'Adam'), ('105', '8', 'Manus'),
('106', '1', 'Rick'), ('107', '8', 'Rose'), ('107', '9', 'Sam'), ('108', '1', 'Rock'), ('108', '5', 'Bobby'), ('109', '10',
CSE @ HKBKCE 45 2022-23
 18CSL58 DBMS LAB

'Ed'),
('110', '4', 'Cathie');

CREATE TABLE Rating :-

CREATE TABLE Rating ( Mov_Id INT NOT NULL , Rev_Stars INT NOT NULL, PRIMARY KEY
(Mov_Id), FOREIGN KEY (Mov_Id) REFERENCES Movies(Mov_Id) on DELETE CASCADE);

INSERT VALUES IN TABLE Rating :-

INSERT INTO `rating` (`Mov_Id`, `Rev_Stars`) VALUES ('1', '3'), ('3', '2'), ('4', '5'), ('5', '4'), ('6', '3'),
('7', '2'), ('8', '1'), ('9', '5'), ('10', '4');

Table Actor:

Table: Director

Table: Movies
CSE @ HKBKCE 46 2022-23
18CSL58 DBMS LAB

CSE @ HKBKCE 47 2022-23

18CSL58 DBMS LAB

Table: Movie_cast

Table: Rating

CSE @ HKBKCE 48 2022-23

18CSL58 DBMS LAB

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

select m.mov_title from movies m,director d where d.dir_id=m.dir_id and d.Dir_Name

='Alfred';

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

select mov_title from movies where mov_id in( select mov_id from movie_cast where
act_id in( select act_id from movie_cast group by act_id having count(*)>1));
(OR)
select distinct(mov_title) from movies join movie_cast on movies.mov_id =
movie_cast.mov_id and act_id in( select act_id from movie_cast group by act_id
having count(*)>1);

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

SELECT A.act_id FROM

(SELECT act_id FROM movie_cast join movies on movie_cast.Mov_Id =
movies.Mov_Id WHERE mov_year<2000 )A ,
(SELECT act_id FROM movie_cast join movies on
movie_cast.Mov_Id=movies.Mov_Id WHERE mov_year>2015) B where A.act_id
=B.act_id;

CSE @ HKBKCE 49 2022-23

18CSL58 DBMS LAB

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

SELECT mov_title,rev_stars from movies m,rating r where m.mov_id=r.mov_id order

by mov_title;

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

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

CSE @ HKBKCE 50 2022-23

18CSL58 DBMS LAB

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

CSE @ HKBKCE 51 2022-23

18CSL58 DBMS LAB

Schema Diagram:

CSE @ HKBKCE 52 2022-23

 18CSL58 DBMS LAB

create table student:-

create table students(
usn varchar(20)not null,
sname varchar(20),
address varchar(20),
phone int,
gender char(1),
primary key(usn)
);

Insert values in table student:-

insert into students (usn, sname, address, phone, gender) values
("1hk17CS01","Ajith","Bangalore",1010101010,'M'),
("1hk17CS02","Barat","Mysore",2020202020,'M'),
("1hk17CS03","Clara","Delhi",303030303,'F'),
("1hk17CS04","Denis","Pune",4040404040,'M'),
("1hk17CS05","Elisa","Patna",505050505,'F'),
("1hk17CS06","Franc","Mumbai",6060606060,'M'),
("1hk17CS07","Gaury","Bangalore",077777777,'F'),
("1hk17CS08","Harry","Kolkatta",88888870,'M'),
("1hk17CS09","Isaac","Dehradun",9090909090,'M'),
("1hk17CS010","Jay","Sikkim",111110000,'M');

insert into students(usn, sname, address, phone, gender) values

("1hk16CS41","Aman","Manipal",110110110,'M'),
("1hk16CS42","Barka","Hyderabad",1212121212,'F'),
("1hk16CS43","Chahana","Chennai",1313131313,'F'),
("1hk16CS44","Deepa","Coimbatore",1414141141,'F'),
("1hk16CS45","Emad","Mumbai",15151515,'M'),
("1hk16CS46","Faroq","Chennai",1616161616,'M'),
("1hk16CS47","Ganga","Coimbatore",17171717,'F'),
("1hk16CS48","Hemalatha","Hyderabad",1818181818,'F'),
("1hk16CS49","Inder","Bangalore",1919191919,'M'),
("1hk16CS50","Jamal","Patna",2202020202,'M');

insert into students(usn, sname, address, phone, gender) values

("1hk15CS61","Arti","Pune",2121212121,'F'),
("1hk15CS62","Barti","Delhi",2202202201,'F'),
("1hk15CS63","Ceaser","Mysore",212121212,'M'),
("1hk15CS64","Dia","Mysore",222222222,'F'),
("1hk15CS65","Elmsri","Bangalore",2323232323,'M'),
("1hk15CS67","Geetha","Bangalore",252525252,'F'),
("1hk15CS68","Harish","Bangalore",565656565,'M'),
("1hk15CS69","Iman","Bangalore",2929292929,'M'),
("1hk15CS70","Jibin","Mysore",990990909,'M'),
("1hk15CS71","Kruthi","Mysore",6767676767,'F'),
("1hk15CS72","Lara","Pune",3030303030,'F');
CSE @ HKBKCE 53 2022-23
 18CSL58 DBMS LAB

insert into students(usn, sname, address, phone, gender) values

("1hk14CS81", "Athiya", "Patna", 3131313131, 'F'),
("1hk14CS82", "Beema", "New Delhi", 3232323232, 'M'),
("1hk14CS83", "Chitra", "Pune",3333333333, 'F'),
("1hk14CS84", "Dipika", "Patna", 3434343434, 'F'),
("1hk14CS85", "Elizabeth", "Mumbai", 2353535355, 'F'),
("1hk14CS86", "Fakruddin", "Mumbai", 363663663, 'M'),
("1hk14CS87", "Gary", "Bangalore", 3737373737, 'M'),
("1hk14CS88", "Hema", "Kolkatta", 3838383838, 'M'),
("1hk14CS89", "Ishana", "Dehradun", 3939393939, 'F'),
("1hk14CS90", "Jason", "Manipal", 4040404040, 'M'),
("1hk14CS91", "Kirana", "Hyderabad", 4949494949, 'F'),
("1hk14CS92", "Lucky", "Chennai", 454545545, 'F');

create table semsec:-

create table semsec
(ssid int not null,
sem int,
section char(1),
PRIMARY key(ssid)
);

Insert values in table semsec:-

insert into semsec (ssid, sem, section) values (1,2,'A'), (2,2,'B'), (3,2,'C'), (4,4,'A'),
(5,4,'B'), (6,4,'C'), (7,6,'A'), (8,6,'B'), (9,6,'C'), (10,8,'A'), (11,8,'B'), (12,8,'C');

create table class:-

create table class(
usn varchar(20) primary key,
ssid int,
foreign key(ssid) references semsec(ssid) on delete cascade,
foreign key(usn) references student(usn) on delete cascade
);

Insert values in table class:-

insert into class(usn,ssid) values
("1hk17CS01",'1'), ("1hk17CS02",'1'), ("1hk17CS03",'1'), ("1hk17CS04",'2'),("1hk17CS05",'2'),
("1hk17CS06",'2'),
("1hk17CS07",'3'),("1hk17CS08",'3'),("1hk17CS09",'3');

insert into class(usn,ssid) values

("1hk16CS41",'4'), ("1hk16CS42",'4'), ("1hk16CS43",'4'),("1hk16CS44",'4'),("1hk16CS45",'5'),
("1hk16CS46",'5'),
("1hk16CS47",'6'),("1hk16CS48",'6'),("1hk16CS49",'6'),("1hk16CS50",'6');

CSE @ HKBKCE 54 2022-23

 18CSL58 DBMS LAB

insert into class(usn,ssid) values ("1hk15CS61",'7'),("1hk15CS62",'7'),("1hk15CS63",'7'),

("1hk15CS64",'8'),("1hk15CS65",'8'),
("1hk15CS67",9),("1hk15CS68",9),("1hk15CS69",9),("1hk15CS70",9),
("1hk15CS71",9),("1hk15CS72",9),("1hk14CS81",10),("1hk14CS82",10),("1hk14CS83",10),
("1hk14CS84",10),("1hk14CS85",11),("1hk14CS86",11),("1hk14CS87",11), ("1hk14CS88",12),
("1hk14CS89",12),("1hk14CS90",12),("1hk14CS91",12),
("1h k14CS92",12);
create table subject:-
create table subject(
subcode varchar(20) primary key,
title varchar(30),
sem int,
Credits int
);

Insert values in table subject:-

insert into subject(subcode,title,sem,Credits) values ("15PCD23","PCD",2,4),
("15CHE21","CHEM",2,4), ("15ELN22","Basic Electronics",2,4),
("15MAT24","Maths",2,4), ("15CS42","SE",4,4), ("15CS43","DAA",4,4),
("15CS44","MP",4,4), ("15CS46","DC",4,4), ("15CS61","Cryptography",6,4),
("15CS62","CGV",6,4), ("15CS63","SS",6,4), ("15CS64","OS",6,4),
("15CS81","IOT",8,4), ("15CS82","Big Data Analytics",8,4), ("15CS834","SMS",8,4),
("15CS86","Seminar",8,4);

create table iamarks:-

create table iamarks( usn
varchar(20), subcode
varchar(20),ssid int,
test1 int,
test2 int,
test3 int,
final int,
primary key(usn,subcode,ssid),
foreign key(usn) references student(usn) on delete cascade,
foreign key(subcode) references subject(subcode) on delete cascade,
foreign key(ssid) references semsec(ssid) on delete cascade
);

Insert values in table iamarks:-

insert into iamarks(usn,subcode,ssid,test1,test2,test3)
values("1hk17CS01","15PCD23",1,10,12,14), ("1hk17CS01","15CHE21",1,11,12,13),
("1hk17CS01","15ELN22",1,13,14,15),("1hk17CS01","15MAT24",1,16,17,18),
("1hk16CS41","15CS42",4,19,20,19),("1hk16CS41","15CS43",4,20,20,20),
("1hk16CS41","15CS44",4,7,9,10),("1hk16CS41","15CS46",4,10,15,20),
("1hk15CS61","15CS61",7,8,12,16),("1hk15CS61","15CS62",7,9,13,17),
("1hk15CS61","15CS63",7,10,14,18),("1hk15CS61","15CS64",7,11,15,19),
("1hk14CS81","15CS81",10,16,14,20),("1hk14CS81","15CS82",10,20,12,13),
("1hk14CS81","15CS834",10,15,16,20),("1hk14CS81","15CS86",10,20,19,18);

CSE @ HKBKCE 55 2022-23

 18CSL58 DBMS LAB

insert into iamarks(usn,subcode,ssid,test1,test2,test3)

values("1hk14CS82","15CS81",10,12,15,18), ("1hk14CS82","15CS82",10,13,20,12),
("1hk14CS82","15CS834",10,15,16,10),("1hk14CS82","15CS86",10,12,9,8);

insert into iamarks(usn,subcode,ssid,test1,test2,test3)

values("1hk14CS83","15CS81",10,2,5,8),("1hk14CS83","15CS82",10,3,12,2),("1hk14CS
83","15CS834",10,5,6,10),("1hk14CS83","15CS86",10,2,19,18);

insert into iamarks(usn,subcode,ssid,test1,test2,test3)

values("1hk14CS84","15CS81",10,12,15,8),("1hk14CS84","15CS82",10,13,0,1),
("1hk14CS84","15CS834",10,15,6,1),("1hk14CS84","15CS86",10,2,19,8);

insert into iamarks(usn,subcode,ssid,test1,test2,test3)

values("1hk14CS85","15CS81",11,12,5,11),("1hk14CS85","15CS82",11,13,2,13),
("1hk14CS85","15CS834",11,14,16,19),("1hk14CS85","15CS86",11,2,14,8),
("1hk14CS86","15CS81",11,12,14,13),("1hk14CS86","15CS82",11,3,2,5),
("1hk14CS86","15CS834",11,4,6,9) ,("1hk14CS86","15CS86",11,13,17,18),
("1hk14CS87","15CS81",11,13,15,19),("1hk14CS87","15CS82",11,11,2,20),
("1hk14CS87","15CS834",11,11,10,6),("1hk14CS87","15CS86",11,12,4,15);

insert into iamarks(usn,subcode,ssid,test1,test2,test3)

values("1hk14CS88","15CS81",12,9,13,19),("1hk14CS88","15CS82",12,1,2,20),
("1hk14CS88","15CS834",12,10,11,12),("1hk14CS88","15CS86",12,13,14,15),
("1hk14CS89","15CS81",12,19,3,9),("1hk14CS89","15CS82",12,13,12,20),
("1hk14CS89","15CS834",12,16,17,18),("1hk14CS89","15CS86",12,3,11,15),
("1hk14CS90","15CS81",12,16,13,19) ,("1hk14CS90","15CS82",12,20,2,20);

insert into iamarks(usn,subcode,ssid,test1,test2,test3)

values("1hk14CS90","15CS834",12,6,7,20),("1hk14CS90","15CS86",12,6,12,5),
("1hk14CS91","15CS81",12,19,10,20),("1hk14CS91","15CS82",12,2,20,20),
("1hk14CS91","15CS834",12,20,12,15),("1hk14CS91","15CS86",12,12,5,20),
( "1hk14CS92","15CS81",12,9,10,20),("1hk14CS92","15CS82",12,20,19,19),
("1hk14CS92","15CS834",12,20,15,13),("1hk14CS92","15CS86",12,20,20,20);

CSE @ HKBKCE 56 2022-23

18CSL58 DBMS LAB

Table: Student

CSE @ HKBKCE 57 2022-23

18CSL58 DBMS LAB

Table: Semsec

Table: Subject

CSE @ HKBKCE 58 2022-23

18CSL58 DBMS LAB

Table: Class

CSE @ HKBKCE 59 2022-23

18CSL58 DBMS LAB

Table: iamarks

CSE @ HKBKCE 60 2022-23

18CSL58 DBMS LAB

CSE @ HKBKCE 61 2022-23

18CSL58 DBMS LAB

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

select s.usn,sname,address,phone,gender from student s,semsec m,class c where

m.ssid=c.ssid and c.usn=s.usn and sem=4 and section='C';

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

select sem,section,count(case when gender='m' then 1 end) as male_cnt,

count(case when gender='f' then 1 end) as female_cnt from student st,class
c,semsec s where s.ssid=c.ssid and c.usn=st.usn group by sem,section;

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

create view test1_mark as select s.subcode as Subject_Code, title as

Subject_Name, test1 as Test1_Marks from iamarks i,subject s
where s.subcode=i.subcode and usn ='1hk15CS61';

CSE @ HKBKCE 62 2022-23

 18CSL58 DBMS LAB

select * from test1_mark;

Expected output:-

15CS61|Cryptography|8
15CS63|SS |10

CSE @ HKBKCE 63 2022-23

18CSL58 DBMS LAB

4 Calculate the FinalIA (average of best two test marks) and update thecorresponding
table for all students.

create view finalia as select usn, subcode, max(avg(test1+test2)/2,

avg(test1+test3)/2, avg(test2+test3)/2) as finalia from iamarks GROUP BY
usn, subcode;

select * from finalia;

UPDATE iamarks i set final=(SELECT finalia from finalia f where i.usn=f.usn and
i.subcode=f.subcode);(Syntax error near i)

60 rows affected

CSE @ HKBKCE 64 2022-23

18CSL58 DBMS LAB

Select * from iamarks;

CSE @ HKBKCE 65 2022-23

18CSL58 DBMS LAB

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 i.usn,s.sem,s.section,i.subcode, CASE

WHEN final >=17 AND final <=20 THEN 'Outstanding'
WHEN final >=12 AND final <=16 THEN 'Average'
ELSE 'Weak' END AS CAT FROM iamarks i, class c, semsec s where i.usn=c.usn
and c.ssid=s.ssid and s.sem=8 and s.section in('A','B','C');

CSE @ HKBKCE 66 2022-23

18CSL58 DBMS LAB

CSE @ HKBKCE 67 2022-23

18CSL58 DBMS LAB

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

CSE @ HKBKCE 68 2022-23

 18CSL58 DBMS LAB

create table Employee:-

create table Employeee (ssn int not null PRIMARY KEY , name varchar(25), address varchar(25), sex
varchar(10), salary int, superssn int, dno int, FOREIGN KEY (superssn) REFERENCES
Employeee(ssn) );

create table department:-

create table departments(dno int not null PRIMARY KEY , dname varchar(25), mgrssn int(25),
mgrstrtdate date, FOREIGN key (mgrssn) REFERENCES employeee(ssn) );

alter table employee:-

alter table employeee add FOREIGN key (dno) REFERENCES department (dno);

table Employee:-
INSERT INTO `employee` (`SSN`, `NAME`, `ADDRESS`, `SEX`, `SALARY`, `DNO`) VALUES
('23412356', 'JENNIFER', 'PARIS', 'FEMALE','700000', '4');
INSERT INTO `employee` (`SSN`, `NAME`, `ADDRESS`, `SEX`, `SALARY`, `DNO`) VALUES
('23456781', 'JAMES', 'NEWYORK', 'MALE','300000', '4'); INSERT INTO `employee` (`SSN`, `NAME`,
`ADDRESS`, `SEX`, `SALARY`, `DNO`) VALUES ('33344555', 'FRANKLIN',
'CALIFORNIA','MALE', '600000', '5');
INSERT INTO `employee` (`SSN`, `NAME`, `ADDRESS`, `SEX`, `SALARY`, `DNO`) VALUES
('67891234', 'JOYCE', 'WASHINGTON','FEMALE', '400000', '5');
INSERT INTO `employee` (`SSN`, `NAME`, `ADDRESS`, `SEX`, `SALARY`, `DNO`) VALUES
('123456789', 'JOHN', 'TEXAS', 'MALE','300000', '5'); INSERT INTO `employee` (`SSN`, `NAME`,
`ADDRESS`, `SEX`, `SALARY`, `DNO`) VALUES ('888666555', 'AHMAD', 'CALIFORNIA','MALE',
'700000', '4');
INSERT INTO `employee` (`SSN`, `NAME`, `ADDRESS`, `SEX`, `SALARY`, `DNO`) VALUES
('984600445', 'MARK', 'WASHINGTON','MALE', '800000', '5');
INSERT INTO `employee` (`SSN`, `NAME`, `ADDRESS`, `SEX`, `SALARY`, `DNO`) VALUES
('56789012', 'ALEENA', 'LONDON', 'FEMALE', '700000', '5');
INSERT INTO `employee` (`SSN`, `NAME`, `ADDRESS`, `SEX`, `SALARY`, `DNO`) VALUES
('89012345', 'ALICE', 'STAFFORD', 'FEMALE', '1200000', '5');
UPDATE `employee` SET `superssn` = '984600445' WHERE `ssn` = 23412356;
UPDATE `employee` SET `superssn` = '67891234' WHERE `ssn` = 23456781;
UPDATE `employee` SET `superssn` = '123456789' WHERE `ssn` = 33344555;
UPDATE `employee` SET `superssn` = '89012345' WHERE `ssn` = 56789012;
UPDATE `employee` SET `superssn` = '888666555' WHERE `ssn` = 67891234;
UPDATE `employee` SET `superssn` = '56789012' WHERE `ssn` = 89012345;
UPDATE `employee` SET `superssn` = '33344555' WHERE `ssn` = 123456789;
UPDATE `employee` SET `superssn` = '23456781' WHERE `ssn` = 888666555;
UPDATE `department` SET `mgrssn` = '888666555' WHERE `dno` = 1;
UPDATE `department` SET `mgrssn` = '123456789' WHERE `dno` = 4;
UPDATE `department` SET `mgrssn` = '33344555' WHERE `dno` = 5;

table department:-
INSERT INTO department values(1,’ADMINISTRATION’,888666555,’1992-06-25’);
INSERT INTO department values(4,’RESEARCH’,23456781,’1990-04-23’);
INSERT INTO department values(5,’ACCOUNTS’,333445,’1998-05-22’);
CSE @ HKBKCE 69 2022-23
 18CSL58 DBMS LAB

create table dlocation:-

create table dlocation( dno int not null , dloc varchar(25) not null , primary key ( dno,dloc), FOREIGN
KEY (dno) REFERENCES department(dno) );

table dlocation:-
INSERT INTO `dlocation` (`DNO`, `DLOC`) VALUES ('1', 'WASHINGTON'); INSERT INTO
`dlocation` (`DNO`, `DLOC`) VALUES ('4', 'CALIFORNIA'); INSERT INTO `dlocation` (`DNO`,
`DLOC`) VALUES ('5', 'NEW YORK'); INSERT INTO `dlocation` (`DNO`, `DLOC`) VALUES ('5',
'WASHINGTON');

create table project:-

create table project (pno int not null , pname varchar(25) , plocation varchar(25),dno int, primary key
(pno), FOREIGN KEY (dno) REFERENCES department(dno) );

table project:-
INSERT INTO project (PNAME, PNO, PLOCATION, DNO) VALUES ('PRODUCTA', 1, 'HOUSTON',
5);
INSERT INTO project (`PNAME`, `PNO`, `PLOCATION`, `DNO`) VALUES ('PRODUCTB', 2,
'WASHINGTON', 5);
INSERT INTO project (`PNAME`, `PNO`, `PLOCATION`, `DNO`) VALUES ('PRODUCTC', 3,
'CALIFORNIA', 5);
INSERT INTO project (`PNAME`, `PNO`, `PLOCATION`, `DNO`) VALUES
('COMPUTERIZATION',10,'NEW YORK',4);
INSERT INTO project (`PNAME`, `PNO`, `PLOCATION`, `DNO`) VALUES ('IOT',20,'PARIS',1);
INSERT INTO project (`PNAME`, `PNO`, `PLOCATION`, `DNO`) VALUES
('REORGANIZATION',30,'STAFFORD',4);

create table works_on:-

create table works_on (ssn int not null , pno int not null , hours int not null , primary key (ssn, pno),
FOREIGN KEY (pno) REFERENCES project(pno) );

table works_on:-
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('123456789', '1', '33');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('123456789', '2', '8');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('984600445', '3', '40');
INSERT INTO `works_on`(`SSN`, `PNO`, `HOURS`) VALUES ('984600445', '1', '50');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('888666555', '1', '45');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('888666555', '2', '54');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('33344555', '2', '10');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('33344555', '3', '10');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('33344555', '10', '10');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('33344555', '20', '10');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('67891234', '30', '20');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('67891234', '10', '20');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('23412356', '30', '12');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('23412356', '20', '14');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('23456781', '20', '20');
INSERT INTO `works_on` (`SSN`, `PNO`, `HOURS`) VALUES ('23456781', '1', '34');
CSE @ HKBKCE 70 2022-23
18CSL58 DBMS LAB

Table: Employee

CSE @ HKBKCE 71 2022-23

18CSL58 DBMS LAB

Table: Department

Table: Dlocation

Table: Project

CSE @ HKBKCE 72 2022-23

18CSL58 DBMS LAB

Table: Works_on

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

select distinct p.pno from project p, department d, employee e where p.dno=d.dno and
d.mgrssn=e.ssn and lname="scott" union select distinct p.pno from project p, works_on
w,employee e where w.pno=p.pno and w.ssn=e.ssn and lname="scott";

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

select fname, lname, 0.1*salary from employee e, works_on w, project

p where e.ssn=w.ssn and p.pno=w.pno and p.pname="iot";

CSE @ HKBKCE 73 2022-23

18CSL58 DBMS LAB

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

select sum(salary) as SUM, max(salary) as MAX, min(salary) as MIN, avg(salary) as AVG

from employee e, department d where e.dno=d.dno and dname="accounts";

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

select name from employee where not exists ( ( select Pnumber from project where
Dnum=5) except ( select pno from works_on where ssn=essn) );

name
AHMAD

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 as D_NO,count(*) as No_Of_Employees from department d, employee e
where d.dno=e.dno and e.salary>600000 and e.dno in (select dno from employee group by
dno having count(*)>5) group by d.dno

CSE @ HKBKCE 74 2022-23