“Jnana Gangotri" Campus, Hospet Rd, near Allipura, Ballari, 583104

ARTIFICIAL INTELLEGENCE AND MACHINE LEARNING

Semester : IV (AIML)

Subject : DBMS LAB

Subject Code : 22AI43

Faculty Name : Mrs. Parvathi /Mrs. Amrutha H

/Mrs. Vijayalaxmi/Mrs. Manikeshwari

Designation : [Link]
 22AI43

INTRODUCTION TO SQL
Introduction to SQL
SQL stands for “Structured Query Language” and can be pronounced as “SQL” or “sequel
– (Structured English Query Language)”. It is a query language used for accessing and modifying
information in the database. IBM first developed SQL in 1970s. Also it is an ANSI/ISO standard. It
has become a Standard Universal Language used by most of the relational database management
systems (RDBMS). Some of the RDBMS systems are: Oracle, Microsoft SQL server, Sybase etc.
Most of these have provided their own implementation thus enhancing its feature and making it a
powerful tool. Few of the SQL commands used in SQL programming are SELECT Statement,
UPDATE Statement, INSERT INTO Statement, DELETE Statement, WHERE Clause, ORDER
BY Clause, GROUP BY Clause, ORDER Clause, Joins, Views, GROUP Functions, Indexes etc.

SQL 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, RENAME, 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

Dept. of AIML, BITM Page 1

 22AI43

security to database objects. These commands are GRANT and REVOKE.

Data Definition Language (DDL)

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

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

SQL Data Types:

char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.

Varchar2(size) Variable-length character string. Max size is specified in parenthesis.

number(size)
Number value with a max number of column digits specified in parenthesis.
or int
Date Date value in ‘dd-mon-yy’. Eg., ’07-jul-2004’

number(size,d) or Number value with a maximum number of digits of "size" total, with a maximum
real number of "d" digits to the right of the decimal.

Dept. of AIML, BITM Page 2

 22AI43
SQL Integrity Constraints:

Integrity Constraints are used to apply business rules for the database [Link] 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 a column or combination of columns which uniquely
identifies each row in the table.

Syntax to define a Primary key at column level:

Column_namedatatype [CONSTRAINT constraint_name] PRIMARY

KEY

Syntax to define a Primary key at table level:

[CONSTRAINT constraint_name] PRIMARY KEY(column_name1,

column_name2,..)

 column_name1, column_name2 are the names of the columns which define theprimary 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 anothertable.
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.

Dept. of AIML, BITM Page 3

DBMS Lab Manual 22AI43
Syntax to define a Foreign key at column level:

[CONSTRAINT constraint_name] REFERENCES

referenced_table_name(column_name)

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES

referenced_table_name(column_name);

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 a
distinct 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 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)

Dept. of AIML Page 4

DBMS Lab Manual 22AI43
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_namedatatype;

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

ALTER TABLE employee ADD experience number(3);

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

For Example: To modify the column salary in the employee table, the query would be like

ALTER TABLE employee MODIFY salary number(15,2);

Syntax to add PRIMARY KEY constraint

ALTER TABLE table_nameADD CONSTRAINT constraint_name PRIMARY KEY

column_name;

Dept. of AIML Page 5

 DBMS Lab Manual 22AI43

Syntax to drop PRIMARY KEY constraint

ALTER TABLE table_nameDROP PRIMARY KEY;

The DROP TABLE Statement

DROP TABLE statement is used to delete a
table. DROP TABLE table_name;

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:

TRUNCATE TABLE table_name;

Data Manipulation Language (DML):

The SELECT Statement

The SELECT statement is used to select data from a [Link] result is stored in a
result table, called the result-set.

SELECT 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 [Link]
DISTINCT keyword can be used to return only distinct (different) values.

SELECT DISTINCT Syntax:

SELECT DISTINCT
column_name(s)FROM
table_name;

Dept. of AIML Page 6

DBMS Lab Manual 22AI43

The WHERE clause is used to extract only those records that fulfill a specifiedcriterion.

WHERE 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 clausesort the records in ascending order by default.
 If you want to sort the records in a descending order, you can use the
DESC keyword.
ORDER BY 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.

GROUP BY Syntax;

Dept. of AIML Page 7

DBMS Lab Manual 22AI43
SELECT
column_name(s)FROM
table_name

WHERE column_name operator

valueGROUP BY
column_name(s);
Group functions Meaning
AVG([DISTINCT|ALL],N]) Returns average value of n
COUNT(*|[DISTINCT|ALL]expr) Returns the number of rows in the
query. When you specify expr, this
function considers rows where expr is
not null.
When you specify the asterisk (*), this function
Returns all rows, including duplicates and
nulls.
You can count either all rows, or only distinct
values of expr.
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

The HAVING clause

The HAVING clause can be used to restrict the display of grouped rows. The result of the
grouped query is passed on to the HAVING clause for output filtration.

HAVING Syntax;

SELECT
column_name(s)FROM
table_name

WHERE column_name operator

valueGROUP BY column_name(s)

Page 8
Dept. of AIML
DBMS Lab Manual 22AI43
HAVING condition;

Page 9
Dept. of AIML
DBMS Lab Manual 22AI43
The INSERT INTO Statement
The INSERT INTO statement is used to insert a new row in a table.
SQL INSERT INTO Syntax:

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_nameVALUES (value1, value2, value3,...);

OR

INSERT INTO table_nameVALUES(&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.

SQL UPDATE 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. SQL DELETE Syntax:

Dept. of AIML Page 10

DBMS Lab Manual 22AI43
DELETE FROM table_name
WHERE
some_column=some_value;

Transaction Control language(TCL)

Transaction Control Language (TCL) commands are used to manage transactions in
[Link] are used to manage the changes made by DML statements. It also allows
statements to be grouped together into logical transactions

Commit command

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

Following is Commit command's syntax,

commit;

Rollback command

This command restores the database to last commited state. It is also use with savepoint
command to jump to a savepoint in a transaction.

Following is Rollback command's 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.

Following is savepoint command's syntax,

savepoint savepoint_name;

Dept. of AIML Page 11

 DBMS Lab Manual 22AI43
Data Control Language (DCL)

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.
Privileges are of two types,

 System : creating session, table etc are all types of system privilege.
 Object : any command or query to work on tables comes under object privilege.

DCL defines two commands,

 Grant : Gives user access privileges to database.
 Revoke : Take back permissions from user.

To Allow a User to create Session

grant create session to username;

To Allow a User to create Table

grant create table to username;

To provide User with some Space on Tablespace to store Table

alter user username quota unlimited on system;

To Grant all privilege to a User

grant sysdba to username

To Grant permission to Create any Table

grant create any table to username

STORED PROCEDURES in SQL:

The SQL Server Stored procedure is used to save time to write code again and again by
storing the same in database and also get the required output by passing parameters.

Page 12
Dept. of AIML
 DBMS Lab Manual 22AI43

Syntax
Following is the basic syntax of Stored procedure creation.

STORED PROCEDURES in SQL:

The SQL Server Stored procedure is used to save time to write code again and again by
storing the same in database and also get the required output by passing parameters.

Syntax
Following is the basic syntax of Stored procedure creation.

Create procedure <procedure_Name>

As

Begin
<SQL Statement>
End

Go

Example

Consider the CUSTOMERS table having the following records.

1 Ramesh 32 Ahmedabad 2000.00

2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

Following command is an example which would fetch all records from the CUSTOMERS table
in Testdb database.

Dept. of AIML Page 13

DBMS Lab Manual 22AI43

CREATE PROCEDURE SelectCustomerstabledata

AS

SELECT * FROM [Link]

GO

The above command will produce the following output.

1 Ramesh 32 Ahmedabad 2000.00

2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

SQL TRIGGERS
Triggers are stored programs, which are automatically executed or fired when some events
occur. Triggers are, in fact, written to be executed in response to any of the following events −

 A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

 A database definition (DDL) statement (CREATE, ALTER, or DROP).

 A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or

SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which the event is
associated.

Dept. of AIML Page 14

 DBMS Lab Manual 22AI43

Benefits of Triggers:
Triggers can be written for the following purposes −
 Generating some derived column values automatically

 Enforcing referential integrity

 Event logging and storing information on table access

 Auditing

 Synchronous replication of tables

 Imposing security authorizations

 Preventing invalid transactions

Dept. of AIML Page 15

DBMS Lab Manual 22AI43

Creating Triggers

The syntax for creating a trigger is :

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS

n] [FOR EACH ROW]

WHEN (condition)

DECLARE

Declaration-statements

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

Where,
 CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an
existing trigger with the trigger_name.

Dept. of AIML Page 16

DBMS Lab Manual 22AI43
 {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will
be executed. The INSTEAD OF clause is used for creating trigger on a view.

 {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.

 [OF col_name] − This specifies the column name that will be updated.

 [ON table_name] − This specifies the name of the table associated with the trigger.

 [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old
values for various DML statements, such as INSERT, UPDATE, and DELETE.

 [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be
executed for each row being affected. Otherwise the trigger will execute just once
when the SQL statement is executed, which is called a table level trigger.

 WHEN (condition) − This provides a condition for rows for which the trigger would
fire. This clause is valid only for row-level triggers.

Select * from customers;

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+ + + + + +

Example To start with, we will be using the CUSTOMERS table we had created and used in the
previous chapters −

Dept. of AIML Page 17

DBMS Lab Manual 22AI43

The following program creates a row-level trigger for the customers table that would fire for
INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This
trigger will display the salary difference between the old values and new values

Example To start with, we will be using the CUSTOMERS table we had created and used in
the previous chapters −
The following program creates a row-level trigger for the customers table that would fire for
INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This
trigger will display the salary difference between the old values and new values

CREATE OR REPLACE TRIGGER

display_salary_changes BEFORE DELETE OR INSERT

OR UPDATE ON customersFOR EACH ROW

WHEN ([Link]> 0)DECLARE

sal_diff number;

BEGIN

sal_diff := :[Link] - :[Link];

dbms_output.put_line('Old salary: ' || :[Link]);

dbms_output.put_line('New salary: ' || :[Link]);

dbms_output.put_line('Salary difference: ' || sal_diff);

END;

When the above code is executed at the SQL prompt, it produces the following result −

Dept. of AIML Page 18

 DBMS Lab Manual 22AI43

Trigger created.

Old salary:
New salary: 7500
Salary difference:

The following points need to be considered here −

 OLD and NEW references are not available for table-level triggers, rather you can
use them for record-level triggers.
 If you want to query the table in the same trigger, then you should use the AFTER
keyword, because triggers can query the table or change it again only after the
initial changes are applied and the table is back in a consistent state.
 The above trigger has been written in such a way that it will fire before any
DELETE or INSERT or UPDATE operation on the table, but you can write
your trigger on a single or multiple operations, for example BEFORE
DELETE, which will fire whenever a record will be deleted using the DELETE
operation on the table.

Triggering a Trigger
Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT
statement, which will create a new record in the table –

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

When a record is created in the CUSTOMERS table, the above create

trigger, display_salary_changes will be fired and it will display the following result −

Dept. of AIML Page 19

 DBMS Lab Manual 22AI43

Because this is a new record, old salary is not available and the above result comes as null.
Let us now perform one more DML operation on the CUSTOMERS table. The UPDATE
statement will update an existing record in the table −

UPDATE customers

SET salary = salary + 500

WHERE id = 2;

When a record is updated in the CUSTOMERS table, the above create

trigger, display_salary_changes will be fired and it will display the following result −

Old salary: 1500

New salary: 2000
Salary difference: 500

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.

[Link] AIML,BITM Page 20

 DBMS Lab Manual 22AI43

EXPERIMENT 1: ORDER DATABASE

OBJECTIVES:

 To create and insert values into a table

 To understand the working of aggregate functions (count, avg, sum etc).
 To create a view for a given table.

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)

Step 1: SCHEMA DIAGRAM

[Link] AIML,BITM Page 21

 DBMS Lab Manual 22AI43

Step 2: ER DIAGRAM

Table Creation
SQL> CREATE TABLE salesman
(SALESMAN_ID INTEGER PRIMARY KEY,

NAME VARCHAR(20),
CITY VARCHAR(20),
COMMISSION VARCHAR(20));

SQL> CREATE TABLE customer

( CUSTOMER_ID INTEGER PRIMARY KEY,

CUST_NAME VARCHAR(20),
CITY VARCHAR(20),
GRADE INTEGER,
SALESMAN_ID INTEGER,
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE SET
NULL);

[Link] AIML,BITM Page 22

 DBMS Lab Manual 22AI43

SQL> CREATE TABLE orders

( ORDER_NO INTEGER PRIMARY KEY,

PURCHASE_AMOUNT DECIMAL(10,2),
ORDER_DATE DATE,
CUSTOMER_ID INTEGER,
SALESMAN_ID INTEGER,
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID)ON
DELETE CASCADE,
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON
DELETE CASCADE);
--Inserting records into SALESMAN table

SQL> INSERT INTO salesman VALUES (1000,'RAHUL','BANGALORE','20%');

SQL> INSERT INTO salesman VALUES
(2000,'ANKITA','BANGALORE','25%');

SQL> INSERT INTO salesman VALUES (3000,'SHARMA','MYSORE','30%');

SQL> INSERT INTO salesman VALUES (4000,'ANJALI','DELHI','15%');
SQL> INSERT INTO salesman VALUES (5000,'RAJ','HYDERABAD','15%');

SQL> SELECT * FROM salesman;

salesman_id salesman_name city commission

1000 RAHUL BANGALORE 20
2000 ANKITA BANGALORE 25
3000 SHARMA MYSORE 30
4000 ANJALI DELHI 15

5000 RAJ HYDERABAD 15

SQL> INSERT INTO customer VALUES (1,'ADYA','BANGALORE',100,1000);

SQL> INSERT INTO customer VALUES (2,'BANU','MANGALORE',300,1000);
SQL> INSERT INTO customer VALUES (3,'CHETHAN','CHENNAI',400,2000);
SQL> INSERT INTO customer VALUES (4,'DANISH','BANGALORE',200,2000);

[Link] AIML,BITM Page 23

 DBMS Lab Manual 22AI43

SQL> INSERT INTO customer VALUES (5,'ESHA','BANGALORE',400,3000);

SQL> INSERT INTO customer VALUES (6,'KAVAN','GOA',500,3000);

SQL> SELECT * FROM customer;

customer_id cust_name city grade salesman_id

1 ADYA BANGALORE 100 1000
2 BANU MANGALORE 300 1000
3 CHETHAN CHENNAI 400 2000
4 DHANISH BANGALORE 200 2000
5 ESHA BANGALORE 400 3000
6 KAVAN GOA 500 3000

SQL> INSERT INTO order1 VALUES (201,5000,'02-JUN-2020',1,1000);

SQL> INSERT INTO order1 VALUES (202,450,'09-APR-2020',1,2000);
SQL> INSERT INTO order1 VALUES (203,1000,'15-MAR-2020',3,2000);
SQL> INSERT INTO order1 VALUES (204,3500,'09-JUL-2020',4,3000);
SQL> INSERT INTO order1 VALUES (205,550,'05-MAY-2020',2,2000);

SQL> SELECT * FROM order1;

order_num purchase_amount order_date customer_id salesman_id

201 5000 02-JUN-2020 1 1000
202 450 09-APR-2020 1 2000
203 1000 15-MAR-2020 3 2000
204 3500 09-JUL- 2020 4 3000
205 550 05-MAY-2020 2 2000

[Link] AIML,BITM Page 24

 DBMS Lab Manual 22AI43

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

SQL> SELECT grade,COUNT(DISTINCT customer_id)

FROM customer

GROUP BY grade

HAVING grade > (SELECT avg(grade)

FROM customer
WHERE city='BANGALORE');

grade COUNT(DISTINCT customer_id)

400 2
300 1
500 1

2. Find the name and numbers of all salesman who had more than one customer.
SQL> SELECT SALESMAN_ID, NAME
FROM SALESMAN S
WHERE (SELECT COUNT(*)
FROM CUSTOMER C
WHERE C.SALESMAN_ID=S.SALESMAN_ID) > 1;

SALESMAN_ID NAME
1000 RAHUL
2000 ANKITA

3. List all the salesman and indicate those who have and don’t have customers
intheir cities (Use UNION operation.)
SQL> SELECT S.SALESMAN_ID, [Link], C.CUST_NAME, [Link]

FROM SALESMAN S, CUSTOMER C

WHERE [Link]=[Link]

UNION
SELECT S.SALESMAN_ID,[Link],'NO MATCH',[Link]

[Link] AIML,BITM Page 25

 DBMS Lab Manual 22AI43

FROM SALESMAN S
WHERE CITY NOT IN
(SELECT CITY
FROM CUSTOMER)
ORDER BY 1 ASC;

salesman_id salesman_name customer_name commission

1000 RAHUL ADYA 20%
1000 RAHUL DANISH 20%
1000 RAHUL ESHA 20%
2000 ANKITA ADYA 25%
2000 ANKITA DANISH 25%
2000 ANKITA ESHA 25%
3000 SHARMA NO MATCH 30%
4000 ANJALI NO MATCH 15%
5000 RAJ NO MATCH 15%

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

SQL> CREATE VIEW V_SALESMAN AS

SELECT O.ORDER_DATE,S.SALESMAN_ID,[Link]
FROM SALESMAN S,ORDERS O
WHERE S.SALESMAN_ID=O.SALESMAN_ID

AND O.PURCHASE_AMOUNT=

(SELECT MAX (PURCHASE_AMOUNT)

FROM ORDERS C

WHERE C.ORDER_DATE=O.ORDER_DATE);

[Link] AIML,BITM Page 26

 DBMS Lab Manual 22AI43

SQL> SELECT * FROM SALESMAN;

order_date salesman_id name

02-JUN-2020 1000 RAHUL
09-APR-2020 2000 ANKITA
15-MAR-2020 2000 ANKITA
09-JUL-2020 3000 SHARMA
05-MAY-2020 2000 ANKITA

5. Demonstrate the DELETE operation by removing salesman with id 1000. All

hisorders must also be deleted.

SQL> DELETE FROM salesman

WHERE salesman_id=1000;

SQL> SELECT * FROM salesman;

salesman_id salesman_name city commission

2000 ANKITA BANGALORE 25%
3000 SHARMA MYSORE 30%
4000 ANJALI DELHI 15%
5000 RAJ HYDERABAD 15%

[Link] AIML,BITM Page 27

 DBMS Lab Manual 22AI43

EXPERIMENT 2: CRICKET DATABASE

OBJECTIVES:

 To create and insert values into a table

 To understand the working of aggregate functions (min, count, avg, sum etc).
 To demonstrate creation of tables, applying the nested query concepts.

Consider the following schema for a Cricket Database:

TEAM( tid, tname, coach, captain_pid ,
city) PLAYER( pid, pname, age, tid)
STADIUM(sid, sname, pincode, city)
MATCH(mid, mdate, time, sid, team1_id, team2_id, winning_team_id, man_of_match, pid)

Step 1: SCHEMA DIAGRAM

[Link] AIML,BITM Page 28

 DBMS Lab Manual 22AI43

Step 2: ER DIAGRAM

Table Creation

SQL> CREATE TABLE team(

tid INT PRIMARY KEY,

tname VARCHAR(20),
coach VARCHAR(20),
captain_pid INT,
city VARCHAR(20));

SQL> CREATE TABLE player(

pid INT PRIMARY KEY,
pname VARCHAR(20),
age INT,
tid INT REFERENCES team(tid));

[Link] AIML,BITM Page 29

 DBMS Lab Manual 22AI43

SQL> CREATE TABLE stadium(

sid INT PRIMARY KEY,
sname VARCHAR(20),
picode NUMBER(8),
city VARCHAR(20),
area VARCHAR(20));

SQL> CREATE TABLE match(

mid INT PRIMARY KEY,
mdate DATE,
time VARCHAR(6),
sid INT REFERENCES stadium(sid),
team1_id INT REFERENCES team(tid),
team2_id INT REFERENCES team(tid),
winning_team_id INT REFERENCES team(tid),
man_of_match INT REFERENCES player(pid),
pid INT REFERENCES
player(pid));

SQL> INSERT INTO team VALUES(123,'rcb','sunil',1,'bangalore');

SQL> INSERT INTO team VALUES(124,'csk','laxman',6,'chenai');
SQL> INSERT INTO team VALUES(125,'royal','singh',4,'rajasthan');
SQL> INSERT INTO team VALUES(127,'dare','sehwag',5,'delhi');

SQL) SELECT * FROM team;

tid tname coach captain_pid city

123 rcb sunil 1 bangalore

124 csk laxman 6 chenai

125 royal singh 4 rajasthan

127 dare sehwag 5 delhi

[Link] AIML,BITM Page 30

 DBMS Lab Manual 22AI43

SQL> INSERT INTO player

VALUES(1,'sachin',33,123); SQL> INSERT INTO
player VALUES(2,'dravid',32,123); SQL> INSERT
INTO player VALUES(3,'dhoni',30,123); SQL> INSERT
INTO player VALUES(4,'raina',30,123); SQL> INSERT
INTO player VALUES(5,'kohli',23,123);

SQL) SELECT * FROM player;

pid pname age tid

1 sachin 33 123

2 dravid 32 123

3 dhoni 30 123

4 raina 30 123

5 kohli 23 123

SQL> INSERT INTO stadium VALUES(111,'chinnaswamy',57001,'bangalore','mg');

SQL> INSERT INTO stadium VALUES(222,'kotla',58001,'delhi','aaa');
SQL> INSERT INTO stadium VALUES(333,'internationsl',59001,'chennai','ddd');
SQL> INSERT INTO stadium VALUES(444,'ksca',51001,'bangalore','ttt');
SQL> INSERT INTO stadium VALUES(555,'csca',55001,'cochin','nnn');

SQL) SELECT * FROM stadium;

sid sname picode city area

111 chinnaswamy 57001 bangalore mg

222 kotla 58001 delhi aaa

333 internationsl 59001 chennai ddd

444 ksca 51001 bangalore ttt

555 csca 55001 cochin nnn

[Link] AIML,BITM Page 31

 DBMS Lab Manual 22AI43

SQL> INSERT INTO match VALUES(1,'10-jan-2017','10am',111,123,124,123,1,1);

SQL> INSERT INTO match VALUES(102,'9-jan-2017','9am',222,124,127,127,5,3);
SQL> INSERT INTO match VALUES(103,'15-jan-2017','12pm',111,125,127,127,5,3);
SQL> INSERT INTO match VALUES(104,'19-jan-2017','8pm',111,125,123,123,1,1);

SQL) SELECT * FROM match;

man_of
mid mdate time sid team1_id team2_id winning_team_id pid
_match
1 10-jan-2017 10am 111 123 124 123 1 1
102 9-jan-2017 9am 222 124 127 127 5 3
103 15-jan-2017 12pm 111 125 127 127 5 3
104 19-jan-2017 8pm 111 125 123 123 1 1

1. Display the youngest player (in terms of age) Name, Team name, age in which he belongs of
the tournament.
SQL> SELECT pname, tname,
age FROM player p,team t
WHERE [Link]=[Link] AND
age =(SELECT min(age) FROM player);

pname tname age

kohli rcb 23

2. List the details of the stadium where the maximum number of matches were
played. SQL> SELECT *
FROM stadium
WHERE sid in (select sid from match
GROUP BY sid having count (sid)=(select max(count(sid))
FROM match;
GROUP BY sid));

sid count([Link]) sname

111 3 chinnaswamy

[Link] AIML,BITM Page 32

 DBMS Lab Manual 22AI43

3. List the details of the player who is not a captain but got the man_of _match award at least in
two matches.
SQL> SELECT * FROM player
WHERE pid NOT IN(SELECT captain_pid FROM team)
AND pid IN(SELECT man_of_match FROM match
GROUP BY man_of_match
HAVING COUNT(man_of_match)>=2);

pid pname age tid

3 dhoni 30 123

4. Display the Team details who won the maximum

matches. SQL> SELECT *
FROM team
WHERE tid IN
(SELECT winning_team_id FROM match
GROUP BY winning_team_id HAVING count
(winning_team_id)= (SELECT max(count(winning_team_id))
FROM match
GROUP BY winning_team_id));

tid tname coach captain id city

123 rcb sunil 1 bangalore

5. Display the team name where all its won matches played in the same stadium.

SQL> SELECT tname

FROM team
WHERE tid IN
(SELECT winning_team_id FROM match
GROUP BY (winning_team_id)
HAVING COUNT(*) IN
(SELECT count(winning_team_id) FROM match
GROUP BY winning_team_id);

winning_team_id tname sid sname

123 rcb 111 chinnaswamy

[Link] AIML,BITM Page 33

 DBMS Lab Manual 22AI43

[Link] AIML,BITM Page 34

 DBMS Lab Manual 22AI43

EXPERIMENT 3: MOVIE DATABASE

OBJECTIVES:

 To create and insert values into a table

 To understand the working of JOIN operations (inner, outer).

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)

Step 1: SCHEMA DIAGRAM

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

[Link] AIML,BITM Page 35

 DBMS Lab Manual 22AI43

Step 2: ER Diagram

Table Creation

SQL> CREATE TABLE ACTOR (

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

SQL> CREATE TABLE DIRECTOR(

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

SQL> CREATE TABLE MOVIES(

MOV_ID NUMBER (4),

MOV_TITLE VARCHAR (25),

MOV_YEAR NUMBER (4),

MOV_LANG VARCHAR (12),

[Link] AIML,BITM Page 36

 DBMS Lab Manual 22AI43
DIR_ID NUMBER (3),

[Link] AIML,BITM Page 37

 DBMS Lab Manual 22AI43

PRIMARY KEY (MOV_ID),

FOREIGN KEY (DIR_ID) REFERENCES DIRECTOR (DIR_ID));

SQL> CREATE TABLE MOVIE_CAST(

ACT_ID NUMBER (3),
MOV_ID NUMBER (4),
ROLE VARCHAR (10),
PRIMARY KEY (ACT_ID, MOV_ID),
FOREIGN KEY (ACT_ID) REFERENCES ACTOR (ACT_ID),
FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));
SQL> CREATE TABLE RATING(

MOV_ID NUMBER (4),

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

SQL> INSERT INTO actor VALUES(301,’ANUSHKA’,’F');

SQL> INSERT INTO actor
VALUES(302,’PRABHAS’,’M'); SQL> INSERT INTO actor
VALUES(303,’PUNITH’,’M’);

SQL> INSERT INTO actor VALUES(304,’JERMY’,’M’);

SQL> SELECT * FROM actor;

act_id act_name act_gender

301 ANUSHKA F
302 PRABHAS M
303 PUNITH M
304 JERMY M

[Link] AIML,BITM Page 38

 DBMS Lab Manual 22AI43

[Link] AIML,BITM Page 39

 DBMS Lab Manual 22AI43

SQL> INSERT INTO director VALUES(60,’RAJAMOULI’, 8751611001);

SQL> INSERT INTO director VALUES(61,’HITCHCOCK’, 7766138911);
SQL> INSERT INTO director VALUES(62,’FARAN’, 9986776531);
SQL> INSERT INTO director VALUES(63,’STEVEN’,8989776530);

SQL> SELECT * FROM director;

dir_id dir_name dir_phone

60 RAJAMOULI 8751611001
61 HITCHCOCK 7766138911
62 FARAN 9986776531
63 STEVEN 8989776530

SQL> INSERT INTO Movies VALUES(1001,'BAHUBALI-2', 2017, 'TELAGU', 60);

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

SQL> SELECT * FROM movies;

mov_id mov_title mov_yea mov_lang dir_id1

r
1001 BAHUBALI-2 2017 TELUGU 60
1002 BAHUBALI-1 2015 TELUGU 60
1003 AKASH 2008 KANNADA 61
1004 WAR HORSE 20011 ENGLISH 63

SQL> INSERT INTO movie_cast VALUES(301, 1002,'HEROINE');

SQL> INSERT INTO movie_cast VALUES(301, 1001,'HEROINE');
SQL> INSERT INTO movie_cast VALUES(303, 1003, 'HERO');
SQL> INSERT INTO movie_cast VALUES(303, 1002, 'GUEST');
SQL> INSERT INTO movie_cast VALUES(304, 1004, 'HERO');

SQL> SELECT * FROM movie_cast;

[Link] AIML,BITM Page 40

 DBMS Lab Manual 22AI43

act_id1 mov_id1 Role

301 1002 HEROINE
302 1001 HEROINE
303 1003 HERO
303 1002 GUEST
304 1004 HERO

SQL> INSERT INTO rating VALUES(1001, 4);

SQL> INSERT INTO rating VALUES(1002, 2);
SQL> INSERT INTO rating VALUES(1003, 5);
SQL> INSERT INTO rating VALUES(1004, 4);
SQL> SELECT * FROM rating;

mov_id11 rev_stars
1001 4
1002 2
1003 5
1004 4

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

SQL> SELECT MOV_TITLE

FROM MOVIES

WHERE DIR_ID IN ( SELECT DIR_ID

FROM DIRECTOR

WHERE DIR_NAME = 'HITCHCOCK');

MOV_TITLE
AKASH

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

[Link] AIML,BITM Page 41

 DBMS Lab Manual 22AI43

SQL> SELECT MOV_TITLE,ACT_NAME,ROLE

FROM MOVIE
JOIN MOVIE_CAST
ON MOVIE_CAST.MOV_ID=MOVIE.MOV_ID
JOIN ACTOR
ON MOVIE_CAST.ACT_ID=ACTOR.ACT_ID
WHERE ACTOR.ACT_ID IN

(SELECT ACT_ID
FROM MOVIE_CAST
GROUP BY ACT_ID HAVING COUNT(*)>=2);

MOV_TITLE ACT_NAME ROLE

BAHUBALI-1 ANUSHKA HEROINE
BAHUBALI-2 ANUSHKA HEROINE
AKASH PUNITH HERO

BAHUBALI-1 PUNITH GUEST

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

SQL> SELECT ACT_NAME,MOV_TITLE,MOV_YEAR

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

ACT_NAME MOV_TITLE MOV-YEAR

ANUSHKA BAHUBALI-2 2017

[Link] AIML,BITM Page 42

 DBMS Lab Manual 22AI43

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.
SQL> SELECT MOV_TITLE,MAX(REV_STARS)
FROM MOVIES
INNER JOIN RATING USING(MOV_ID)
GROUP BY MOV_TITLE
HAVING MAX(REV_STARS)>0
ORDER BY MOV_TITLE;

MOV_TITLE MAX(REV_STARTS)
AKASH 5
BAHUBALI-1 2
BAHUBALI-2 4
WAR HORSE 4

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

SQL> UPDATE RATING SET REV_STARS=5
WHERE MOV_ID IN(SELECT MOV_ID
FROM MOVIES
WHERE DIR_ID IN(SELECT DIR_ID
FROM DIRECTOR
WHERE DIR_NAME='STEVEN'));

MOV_ID REV_STARS
1001 4
1002 2
1003 5
1004 5

[Link] AIML,BITM Page 43

 DBMS Lab Manual 22AI43

EXPERIMENT 4: COLLEGE DATABASE

OBJECTIVES:

 To create and insert values into a table

 To understand the sub queries & demonstrate view.

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)

Step 1: SCHEMA DIAGRAM

[Link] AIML,BITM Page 44

 DBMS Lab Manual 22AI43

Step 2: ER DIAGRAM

Table Creation

SQL> CREATE TABLE STUDENT(

USN VARCHAR(10) PRIMARY

KEY, SNAME VARCHAR(25),

ADDRESS VARCHAR(25),

PHONE INTEGER,

GENDER CHAR(1));

SQL> CREATE TABLE SEMSEC(

SSID VARCHAR(5) PRIMARY

KEY, SEM INTEGER,

SEC CHAR(1));

SQL> CREATE TABLE CLASS(

USN VARCHAR(10) PRIMARY KEY,

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

[Link] AIML,BITM Page 45

 DBMS Lab Manual 22AI43

SQL> CREATE TABLE SUBJECT(

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

SQL> CREATE TABLE IAMARKS(

USN VARCHAR(10),
SUBCODE VARCHAR(8),
SSID VARCHAR(5),
TEST1 INTEGER,
TEST2 INTEGER,
TEST3 INTEGER,
FINALIA INTEGER,
PRIMARY KEY(SUBCODE,USN,SSID),
FOREIGN KEY(USN) REFERENCES STUDENT(USN),
FOREIGN KEY(SUBCODE) REFERENCES SUBJECT(SUBCODE),
FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID));
SQL> INSERT INTO Student VALUES('3BR13CS020','ANAND','BELAGAVI', 123423,'M');

SQL> INSERT INTO Student VALUES ('3BR13CS062','BABIITHA','BENGALURU',431238,'F');

SQL> INSERT INTO Student VALUES('3BR15CS101','CHETHAN','BENGALURU', 534234,'M');

SQL> INSERT INTO Student VALUES ('3BR14CS010','EESHA','BENGALURU', 345456,'F');

SQL> INSERT INTO Student VALUES ('3BR14CS032','GANESH','BENGALURU',574532,'M');

SQL> INSERT INTO Student VALUES ('3BR14CS025','HARISH','BENGALURU', 235464,'M');

SQL> INSERT INTO Student VALUES ('3BR15CS011','ISHA','TUMKUR', 764343,'F');

SQL> INSERT INTO Student VALUES ('3BR15CS029','JOEY','DAVANGERE', 235653,'M');

SQL> INSERT INTO Student VALUES ('3BR15CS045','KAVYA','BELLARY', 865434,'F');

SQL> INSERT INTO Student VALUES ('3BR15CS091','MALINI','MANGALURU',235464,'F');

SQL> INSERT INTO Student VALUES ('3BR16CS045','NEEL','KALBURGI', 856453,'M');

SQL>INSERT INTO Student VALUES('3BR16CS088','PARTHA','SHIMOGA', 234546,'M');

SQL> INSERT INTO Student VALUES('3BR16CS122','REEMA','CHIKAMAGALUR', 853333,'F');

[Link] AIML,BITM Page 46

 DBMS Lab Manual 22AI43

SQL> SELECT * FROM Student;

USN SNAME ADDRESS PHONE GENDER

3BR13CS020 ANAND BELAGAVI 123423 M
3BR13CS062 BABIITHA BENGALURU 431238 F
3BR15CS101 CHETHAN BENGALURU 534234 M
3BR14CS010 EESHA BENGALURU 345456 F
3BR14CS032 GANESH BENGALURU 574532 M
3BR14CS025 HARISH BENGALURU 235464 M
3BR15CS011 ISHA TUMKUR 764343 F
3BR15CS029 JOEY DAVANGERE 235653 M
3BR15CS045 KAVYA BELLARY 865434 F
3BR15CS091 MALINI MANGALURU 235464 F
3BR16CS045 NEEL KALBURGI 856453 M
3BR16CS088 PARTHA SHIMOGA 234546 M
3BR16CS122 REEMA CHIKAMAGALUR 853333 F

SQL>INSERT INTO SEMSEC VALUES ('CSE8A',

8,'A'); SQL>INSERT INTO SEMSEC VALUES ('CSE8B',
8,'B'); SQL>INSERT INTO SEMSEC VALUES ('CSE8C',
8,'C'); SQL>INSERT INTO SEMSEC VALUES ('CSE7A',
7,'A'); SQL>INSERT INTO SEMSEC VALUES ('CSE7B',
7,'B'); SQL>INSERT INTO SEMSEC VALUES ('CSE7C',
7,'C'); SQL>INSERT INTO SEMSEC VALUES ('CSE6B',
6,'B'); SQL>INSERT INTO SEMSEC VALUES ('CSE6C',
6,'C'); SQL>INSERT INTO SEMSEC VALUES ('CSE5A',
5,'A'); SQL>INSERT INTO SEMSEC VALUES ('CSE5B',
5,'B'); SQL>INSERT INTO SEMSEC VALUES ('CSE5C',
5,'C'); SQL>INSERT INTO SEMSEC VALUES ('CSE4A',
4,'A'); SQL>INSERT INTO SEMSEC VALUES ('CSE4B',
4,'B'); SQL>INSERT INTO SEMSEC VALUES ('CSE4C',
4,'C'); SQL>INSERT INTO SEMSEC VALUES ('CSE3A',
3,'A'); SQL>INSERT INTO SEMSEC VALUES ('CSE3B',
3,'B'); SQL>INSERT INTO SEMSEC VALUES ('CSE3C',
3,'C');
[Link] AIML,BITM Page 47
 DBMS Lab Manual 22AI43

[Link] AIML,BITM Page 48

 DBMS Lab Manual 22AI43
SQL>INSERT INTO SEMSEC VALUES ('CSE2A',
2,'A'); SQL>INSERT INTO SEMSEC VALUES ('CSE2B',
2,'B'); SQL>INSERT INTO SEMSEC VALUES ('CSE2C',
2,'C'); SQL>INSERT INTO SEMSEC VALUES ('CSE1A',
1,'A'); SQL>INSERT INTO SEMSEC VALUES ('CSE1B',
1,'B'); SQL>INSERT INTO SEMSEC VALUES ('CSE1C',
1,'C');

SSID SEM SEC

CSE8A 8 A
CSE8B 8 B
CSE8C 8 C
CSE7A 7 A
CSE7B 7 B
CSE7C 7 C
CSE6B 6 B
CSE6C 6 C
CSE5A 5 A
CSE5B 5 B
CSE5C 5 C
CSE4A 4 A
CSE4B 4 B
CSE4C 4 C
CSE3A 3 A
CSE3B 3 B
CSE3C 3 C
CSE2A 2 A
CSE2B 2 B
CSE2C 2 C
CSE1A 1 A
CSE1B 1 B
CSE1C 1 C

SQL>INSERT INTO Class VALUES('3BR13CS020','CSE8A');

SQL>INSERT INTO Class VALUES('3BR13CS062','CSE8A');
SQL> INSERT INTO Class VALUES('3BR13CS066','CSE8B');

SQL> INSERT INTO Class VALUES('3BR15CS101','CSE8C');

SQL> INSERT INTO Class VALUES('3BR14CS010','CSE7A');

SQL> INSERT INTO Class VALUES('3BR14CS025','CSE7A');

SQL>INSERT INTO Class VALUES('3BR14CS032','CSE7A');

SQL>INSERT INTO Class VALUES('3BR15CS011','CSE4A');

[Link] AIML,BITM Page 49
 DBMS Lab Manual 22AI43
SQL> INSERT INTO Class VALUES('3BR15CS029','CSE4A');

SQL> INSERT INTO Class VALUES('3BR15CS045','CSE4B');

SQL> INSERT INTO Class VALUES('3BR15CS091','CSE4C');

SQL> INSERT INTO Class VALUES('3BR16CS045','CSE3A');

SQL> INSERT INTO Class VALUES('3BR16CS088','CSE3B');

SQL> INSERT INTO Class VALUES('3BR16CS122','CSE3C');

SQL> SELECT * FROM Class;

USN SSID

3BR13CS020 CSE8A
3BR13CS062 CSE8A
3BR13CS066 CSE8B
3BR15CS101 CSE8C
3BR14CS010 CSE7A
3BR14CS025 CSE7A
3BR14CS032 CSE7A
3BR15CS011 CSE4A
3BR15CS029 CSE4A
3BR15CS045 CSE4B
3BR15CS091 CSE4C
3BR16CS045 CSE3A
3BR16CS088 CSE3B
3BR16CS122 CSE3C

[Link] AIML,BITM Page 50

 DBMS Lab Manual 22AI43

SQL> INSERT INTO SUBJECT VALUES ('21CS53','DBMS', 5, 3);

SQL>INSERT INTO SUBJECT VALUES ('10CS82','SSM', 8, 4);

SQL>INSERT INTO SUBJECT VALUES ('10CS83','NM', 8, 4);

SQL>INSERT INTO SUBJECT VALUES ('10CS84','CC', 8, 4);

SQL>INSERT INTO SUBJECT VALUES ('10CS85','PW', 8, 4);

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

SQL>INSERT INTO SUBJECT VALUES ('10CS72','ECS', 7, 4);

SQL>INSERT INTO SUBJECT VALUES ('10CS73','PTW', 7, 4);

SQL>INSERT INTO SUBJECT VALUES ('10CS74','DM', 7, 4);

SQL>INSERT INTO SUBJECT VALUES ('10CS75','JAVA', 7, 4);

SQL>INSERT INTO SUBJECT VALUES ('10CS76','SAN', 7, 4);

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

SQL>INSERT INTO SUBJECT VALUES ('15CS52','CN', 5, 4);

SQL>INSERT INTO SUBJECT VALUES ('15CS53','DBMS', 5, 4);

SQL>INSERT INTO SUBJECT VALUES ('15CS54','ATC', 5, 4);

SQL>INSERT INTO SUBJECT VALUES ('15CS55','JAVA', 5, 3);

SQL>INSERT INTO SUBJECT VALUES ('15CS56','AI', 5, 3);

SQL>INSERT INTO SUBJECT VALUES ('15CS41','M4', 4, 4);

SQL>INSERT INTO SUBJECT VALUES ('15CS42','SE', 4, 4);

SQL>INSERT INTO SUBJECT VALUES ('15CS43','DAA', 4, 4);

SQL>INSERT INTO SUBJECT VALUES ('15CS44','MPMC', 4, 4);

SQL>INSERT INTO SUBJECT VALUES ('15CS45','OOC', 4, 3);

SQL>INSERT INTO SUBJECT VALUES ('15CS46','DC', 4, 3);

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

SQL>INSERT INTO SUBJECT VALUES ('15CS32','ADE', 3, 4);

SQL>INSERT INTO SUBJECT VALUES ('15CS33','DSA', 3, 4);

SQL>INSERT INTO SUBJECT VALUES ('15CS34','CO', 3, 4);

[Link] AIML,BITM Page 51

 DBMS Lab Manual 22AI43
SQL>INSERT INTO SUBJECT VALUES ('15CS35','USP', 3, 3);

SQL>INSERT INTO SUBJECT VALUES ('15CS36','DMS', 3, 3);

SQL> SELECT * FROM Subject;

SUB CODE SUB_TITLE SEM CREDITS

21CS53 DBMS 5 3
10CS82 SSM 8 4
10CS83 NM 8 4
10CS84 CC 8 4
10CS85 ML 8 4
10CS71 ACA 7 4
10CS72 WEB 7 4
10CS73 BIG DATA 7 4
10CS74 DM 7 4
10CS75 JAVA 7 4
10CS76 SAN 7 4
15CS51 ME 5 4
15CS52 CN 5 4
15CS53 DBMS 5 4
15CS54 ATC 5 4
15CS55 CORE JAVA 5 3
15CS56 AI 5 3
15CS41 M4 4 4
15CS42 SE 4 4
15CS43 DAA 4 4
15CS44 MPMC 4 4
15CS45 OOPS 4 3
15CS46 DC 4 3
15CS31 M3 3 4
15CS32 ADE 3 4
15CS33 DSA 3 4
15CS34 CO 3 4
15CS35 USP 3 3
15CS36 DMS 3 3

[Link] AIML,BITM Page 52

 DBMS Lab Manual 22AI43
SQL> INSERT INTO IAmarks VALUES('3BR21CS007','21CS53','CSE5A', 16, 18, 15,17);
SQL> INSERT INTO IAmarks VALUES('3BR13CS062','10CS82','CSE8C', 12, 19, 14,15);
SQL> INSERT INTO IAmarks VALUES('3BR15CS011','10CS83','CSE8C', 19, 15, 20,18);

SQL> INSERT INTO IAmarks VALUES('3BR14CS025','10CS84','CSE8C', 20, 16, 19,19);

SQL> INSERT INTO IAmarks VALUES('3BR16CS088','10CS85','CSE8C', 15, 15, 12,14);

SQL> SELECT * FROM IAmarks;

USN SUBCODE SSID Test1 Test2 Test3 Final-IA

3BR21CS007 21CS53 CSE5A 16 18 15 17
3BR13CS062 10CS82 CSE8C 12 19 14 15
3BR15CS011 10CS83 CSE8C 19 15 20 18
3BR14CS025 10CS84 CSE8C 20 16 19 19
3BR16CS088 10CS85 CSE8C 15 15 12 14

[Link] AIML,BITM Page 53

 DBMS Lab Manual 22AI43

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

SQL> SELECT S.*, [Link], [Link]

FROM STUDENT S, SEMSEC SS, CLASS
C WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = 4 AND
[Link]='C';
USN SNAME ADDRESS PHONE GENDER SEM SEC

3BR15CS091 MALINI MANGALORE 235464 F 4 C

2. Compute the total number of male and female students in each semester and
in eachsection.
SQL> SELECT [Link], [Link], [Link], COUNT([Link]) AS COUNT
FROM STUDENT S, SEMSEC SS, CLASS C
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY [Link], [Link], [Link]
ORDER BY SEM;

SEM SEC GENDER COUNT

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

[Link] AIML,BITM Page 54

 DBMS Lab Manual 22AI43

[Link] AIML,BITM Page 55

 DBMS Lab Manual 22AI43

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

SQL> CREATE VIEW STUDENT_TEST1_MARKS_V
AS SELECT TEST1, SUBCODE
FROM IAMARKS
WHERE USN = '3BR15CS011';

View created.
SQL> select * from STUDENT_TEST1_MARKS_V;

TEST1 SUBCODE

19 10CS83
4. Calculate the FinalIA (average of best two test marks) and update the
correspondingtable for all students.
SQL> UPDATE IAMARKS SET FINALIA=
(GREATEST(TEST1+TEST2,TEST2+TEST3,TEST3+TEST1))/2;

4 rows updated.

SQL> SELECT * FROM IAMARKS;

USN Subcode SSID Test1 Test2 Test3 FinalIA

3BR13CS062 10CS82 CSE8C 12 19 14 17
3BR15CS011 10CS83 CSE8C 19 15 20 20
3BR14CS025 10CS84 CSE8C 20 16 19 20
3BR16CS088 10CS85 CSE8C 15 15 12 15

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.

[Link] AIML,BITM Page 56

 DBMS Lab Manual 22AI43

[Link] AIML,BITM Page 57

 DBMS Lab Manual 22AI43

SQL>SELECT [Link],[Link],[Link],[Link],[Link], [Link],

(CASE
WHEN [Link] BETWEEN 17 AND 20 THEN
'OUTSTANDING' WHEN [Link] BETWEEN 12 AND 16
THEN 'AVERAGE' ELSE 'WEAK'
END) AS CAT
FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = 8;

USN SNAME ADDRESS PHONE GENDER SUBCODE CAT

3BR13CS062 BABITHA BENGALURU 43123 F 10CS82 OUTSTANDING
3BR15CS011 ISHA TUMKUR 764343 F 10CS83 OUTSTANDING
3BR14CS025 HARISH BENGALURU 235464 M 10CS84 OUTSTANDING

[Link] AIML,BITM Page 58

 DBMS Lab Manual 22AI43

EXPERIMENT 5: VOTER DATABASE

OBJECTIVES:

 To create and insert values into a table

 To understand the sub queries & JOIN operations.

Consider the schema for Voter Database:

CONSTITUENCY(cons_id, csname, csstate,

no_of_voters) PARTY(pid, pname, psymbol)
CANDIDATES(cand_id, phone_no, age, state, name,
pid) CONTEST(cons_id, cand_id)
VOTER(vid, vname, vage, vaddr, cons_id, cand_id)

Step 1: SCHEMA DIAGRAM

[Link] AIML,BITM Page 59

 DBMS Lab Manual 22AI43

Step 2: ER DIAGRAM

SQL> CREATE TABLE constituency(

cons_id NUMBER(20) PRIMARY KEY,
csname VARCHAR(20),
csstate VARCHAR(20),
no_of_voters NUMBER(10));

SQL> CREATE TABLE party(

pid NUMBER(20) PRIMARY KEY,
pname VARCHAR(20),
psymbol VARCHAR(10));

SQL> CREATE TABLE candidates(

cand_id NUMBER(12) PRIMARY KEY,
phone_no NUMBER(10),
age NUMBER(2),

[Link] AIML,BITM Page 60

 DBMS Lab Manual 22AI43

state VARCHAR(20),
name VARCHAR(20),
pid INT REFERENCES party(pid));

SQL> CREATE TABLE contest(

cons_id number(20) REFERENCES CONSTITUENCY(CONS_ID),
CAND_ID number(12) references candidates(cand_id),
PRIMARY KEY(cons_id,cand_id));

SQL> CREATE TABLE voter(

vid NUMBER(20) PRIMARY KEY,
vname VARCHAR(20),
vage NUMBER(5),
vaddr VARCHAR(20),
cons_id NUMBER(20) REFERENCES
constituency(cons_id), cand_id NUMBER(12)
REFERENCES candidates(cand_id));

SQL> INSERT INTO constituency VALUES(111,'rajajinagar','karnataka',4);

SQL> INSERT INTO constituency VALUES(444,'anagar','kerala',1);
SQL> INSERT INTO constituency
VALUES(555,'tnagar','karnataka',4); SQL> INSERT INTO
constituency VALUES(999,'anagar','kerala',1); SQL> INSERT INTO
constituency VALUES(333,'anagar','kerala',1);

SQL> INSERT INTO party VALUES(898,'bjp','lotus');

SQL> INSERT INTO party VALUES(899,'congress','hand');

SQL> INSERT INTO candidates VALUES(198,999999999,23,'kerala','raksha',898);

SQL> INSERT INTO candidates
VALUES(199,888888888,21,'karnataka','rakhi',899);

SQL> INSERT INTO contest VALUES(111,198);

[Link] AIML,BITM Page 61

 DBMS Lab Manual 22AI43
SQL> INSERT INTO contest VALUES(444,198);
SQL> INSERT INTO contest
VALUES(444,199);

[Link] AIML,BITM Page 62

 DBMS Lab Manual 22AI43

SQL> INSERT INTO voter

VALUES(901,'prasad',19,'kanakpura',111,198); SQL> INSERT INTO
voter VALUES(903,'aa',19,'kanakpura',444,199); SQL> INSERT INTO
voter VALUES(904,'dd',21,'ramnagar',444,199); SQL> INSERT INTO
voter VALUES(905,'tt',21,'mandya',444,199);

1. List the details of the candidates who are contesting from more than one constituency which are
belongs to different states.
SQL> SELECT c.cand_id,[Link],count(c.cons_id)
FROM contest c,candidates cd
WHERE c.cand_id=cd.cand_id
GROUP BY([Link],c.cand_id)
HAVING count(c.cons_id)>1;

cand_id name count(c.cons_id)

198 raksha 2

2. Display the state name having maximum number of

constituencies. SQL> SELECT * from (select
csstate,count(cons_id)
FROM constituency
GROUP BY csstate
ORDER BY count(cons_id) desc) resultset
WHERE rownum=1;

csstate count(cons_id)
kerala 3

3. Create a stored procedure to insert the tuple into the voter table by checking the voter age. If
voter’s age is at least 18 years old, then insert the tuple into the voter else display the “Not an
eligible voter msg”.

SQL> CREATE PROCEDURE agechecking ( id IN NUMBER,age IN NUMBER)

AS
BEGIN
[Link] AIML,BITM Page 63
 DBMS Lab Manual 22AI43
IF age>18 THEN

[Link] AIML,BITM Page 64

 DBMS Lab Manual 22AI43

INSERT INTO voter(vid,vage) VALUES (id,age);

ELSE
dbms_output.put_line('age should be high');
END IF;
END agechecking;
/

Procedure created.

SQL> SET SERVEROUTPUT ON;

SQL> EXEC agechecking (25,21);
PL/SQL procedure successfully completed.
SQL> EXEC agechecking (20,15);
age should be high

PL/SQL procedure successfully completed. SQL>

SELECT * FROM voter;

VID VNAME VAGE VADDR CONS_ID CAND_ID
901 prasad 19 kanakpura 111 198
903 aa 19 kanakpura 444 199
444 199
904 dd 21 ramnagar
444 199
905 tt 21 mandya

25 21

4. Display the constituency name, state and number of voters in each state in descending order
using rank() function
SQL> SELECT csname, csstate, no_of_voters,
RANK () OVER (PARTITION BY csstate ORDER BY no_of_voters DESC)
AS Rank_No
FROM constituency;

CSNAME CSSTATE NO_OF_VOTERS RANK_NO

rajajinagar karnataka 5 1
tnagar karnataka 4 2
anagar kerala 1 1

[Link] AIML,BITM Page 65

 DBMS Lab Manual 22AI43

5. Create a TRIGGER to UPDATE the count of “Number_of_voters” of the respective constituency

in “CONSTITUENCY” table, AFTER inserting a tuple into the “VOTERS” table.
SQL> CREATE TRIGGER count
AFTER INSERT ON voter
FOR EACH ROW
BEGIN
UPDATE constituency
SET no_of_voters = no_of_voters + 1
WHERE cons_id=:new.cons_id;
END count;

Trigger created.

SQL> SELECT * FROM constituency;

CONS_ID CSNAME CSSTATE NO_OF_VOTERS

111 rajajinagar karnataka 5
444 anagar kerala 1
555 tnagar karnataka 4

SQL> INSERT INTO voter VALUES(399,'nagesh',30,'mandya',111,199);

1 row created.

SQL> SELECT * FROM constituency;

CONS_ID CSNAME CSSTATE NO_OF_VOTERS

111 rajajinagar karnataka 6
444 anagar kerala 1
555 tnagar karnataka 4

[Link] AIML,BITM Page 66

[Document title]