DATABASE MANAGEMENT SYSTEM LAB

PRACTICAL FILE

SUBMITTED IN THE PARTIAL FULFILLMENT OF THE

DEGREE OF

BACHELOR OF BUSINESS ADMINISTRATION(CAM)

(2022-2025)

SUBMITTED BY:
DEEPAK YADAV
ENROLLMENT NO.:
01913401922
UNDER THE GUIDANCE OF

Dr. Seema Nath Jain

(Principal, BBA CAM 2nd Shift)

IDEAL INSTITUTE OF MANAGEMENT & TECHNOLOGY

(AFFILIATED TO GURU GOBIND SINGH INDRAPRASTHA UNIVERSITY, DELHI)

1
 ACKNOWLEDGEMENT

I am preparing this practical file of Database Management System

Lab for the program of Bachelor of Business Administration (CAM) for
Ideal Institute of Management & Technology, Affiliated to Guru Gobind
Singh Indraprastha University.

It has been a great challenge but a plenty of learning and opportunities to

gain a huge amount of knowledge on the way of preparing this file. I
could not have completed my work without the constant guidance of Dr.
Seema Nath Jain, my faculty, who helped me along the way and was
always prepared to give me feedback and guidelines whenever I needed
it.

DEEPAK YADAV

Enroll. no.: 01913401922

2
 ORACLE ASSIGNEMENT -1

● Create the customer table having following entities make cust_id field as
a primary key.
cust_id Name Address Doj

1 Ram Delhi '30-aug-85'

2 Sham Rohtak '30-jun-86'

3 Rohan Gujrat '30-may-87'

4 Rajan Rohtak '30-may-88'

● View all the data of customer table

SELECT * FROM customer;

● Retrieve only first two records from the customer table.

SELECT * FROM customer LIMIT 2;

● View all the data of customer table whose address is sirsa

SELECT * FROM customer WHERE Address = 'Sirsa';

● View all the data of customer table whose address is not Sirsa

3
 SELECT * FROM customer WHERE Address <> ‘Sirsa’;

● View cust_id and name from customer table whose doj is ’30-may-88’

SELECT cust_id, name FROM customer WHERE Doj = '30-may-88';

● View all the data of customer table whose doj is greater then ‘30-may-87’

SELECT * FROM customer WHERE Doj > '30-may-87';

● View all the records of customer table whose address is rohtak and
cust_id=2

SELECT * FROM customer WHERE Address = 'Rohtak' AND cust_id = 2;

● View all the records of customer table whose name is rohan or doj is ‘30-
may-87’

SELECT * FROM customer WHERE name = 'Rohan' OR Doj = '30-may-87';

● View all the data of customer table whose doj is between '30-may-85' and
'17-nov-86';

SELECT * FROM customer WHERE Doj BETWEEN '30-may-85' AND '17-nov-

86';

● View all the data of customer table whose doj is not between '30-may-85'
and '17-nov-86';

4
 SELECT * FROM customer WHERE Doj NOT BETWEEN '30-may-85' AND
'17-nov-86';

● View all the records of customer table where the address can be delhi,
gujrat, sirsa.

SELECT * FROM customer WHERE Address IN ('Delhi', 'Gujarat', 'Sirsa');

● Update the address of customer table whose cust_id=4 with new address
‘sirsa’

UPDATE customer SET Address = 'Sirsa' WHERE cust_id = 4;

● Update the name & address of customer table whose cust_id=2 with new
name & address.

UPDATE customer SET name = 'NewName', Address = 'NewAddress'

WHERE cust_id = 2;

● View all the records of customer whose name starting with char ‘r’.

SELECT * FROM customer WHERE name LIKE 'R%';

● View all the records of customer whose name starting with Any letter but
second and third letter is ‘am’.

SELECT * FROM customer WHERE name LIKE '_am%';

5
● View all the records of customer whose name starting and ending letter is
anything but this two letter ‘am’ should be in between the name.

SELECT * FROM customer WHERE name LIKE '%am%';

● View all the data of customer table & their address should be in ascending
order.

SELECT * FROM customer ORDER BY Address ASC;

● View all the data of customer table thier address should be in descending
Order

SELECT * FROM customer ORDER BY Address DESC;

● Retrieve the unique address from the customer table.

SELECT DISTINCT Address FROM customer

● Delete the record of customer table whose name is ram.

DELETE FROM customer WHERE name = 'Ram';

● Delete all the record of customer table

DELETE FROM customer;

 Delete the customer table

DROP TABLE customer;

6
 ORACLE ASSIGNMENT -2

● Create table emp(id numeric(10), salary numeric(10), name varchar(60));

● Insert into emp values (1, 5000, ‘ram’);

● Insert into emp values (2, 6000, ‘sham’);
● Insert into emp values (3, 5000, ‘mohan’);
● Insert into emp values (4, 5000, ‘karan’);

● Count the total number of records in the emp table.

SELECT COUNT(*) FROM emp;

● Calculate the total salary from emp table

SELECT SUM(salary) FROM emp;

● Calculate the average of salary from emp table

SELECT AVG(salary) FROM emp;

● Calculate the maximum salary from emp table.

SELECT MAX(salary) FROM emp;

● Calculate the minimum salary from emp table

7
 SELECT MIN(salary) FROM emp;

● Retrieve all the records of emp table whose salary is maximum.

SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

● Change the case of name field in upper letter.

UPDATE emp SET name = UPPER(name);

● Change the case of name field in lower letter.

UPDATE emp SET name = LOWER(name);

● Add the column of address in the emp table using alter command

ALTER TABLE emp ADD COLUMN address VARCHAR(100);

● Delete the column of address in the emp table

ALTER TABLE emp DROP COLUMN address;

● Change the Data Type of name column in the emp table.

ALTER TABLE emp ALTER COLUMN name VARCHAR(100);

● Retrieve the data from two different tables with help of union command.
8
 (NOTE: FOR THIS COMMAND BOTH THE TABLE MUST HAVE SAME
NUMBER OF FIELDS AND MUST HAVE SAME DATATYPE)

SELECT id, salary, name FROM emp

UNION
SELECT id, salary, name FROM another_table;

● Create table stud id numeric(10), name varchar(50). Make id as unique

key

CREATE TABLE stud (id NUMERIC(10) PRIMARY KEY, name VARCHAR(50));

● Create table supplier s_id numeric(10), s_name varchar(50), address

varchar(50). Make s_id as primary key

CREATE TABLE supplier (s_id NUMERIC(10) PRIMARY KEY, s_name

VARCHAR(50), address VARCHAR(50));

● Create table customer c_id numeric(10), c_name varchar(50), address

varchar(50), s_id numeric(10). Make s_id as primary key and s_id as
foreign key.

CREATE TABLE customer (c_id NUMERIC(10), c_name VARCHAR(50),

address VARCHAR(50), s_id NUMERIC(10), PRIMARY KEY (c_id), FOREIGN
KEY (s_id) REFERENCES supplier(s_id));

● Retrieve all the data from customer and supplier tables.

SELECT * FROM customer;

SELECT * FROM supplier;

9
● Show the name field records with any other alias name

SELECT name AS alias_name FROM table_name;

● Delete all the data from student table using truncate command

TRUNCATE TABLE stud;

10
 ORACLE ASSIGNMENT -3

● Create the table name PO having fild id, name, score. In this table id
should be greater then 0. (Use check command)
CREATE TABLE PO (
id INT CHECK (id > 0),
name VARCHAR(50),
score INT
);

● Create the table name RI having fild id, name. In this table name should
be ram. (Use Check Command)
CREATE TABLE RI (
id INT,
name VARCHAR(50) CHECK (name = 'ram')
);

● Use the table’s supplier & customer from the assignment no 2 and show
the working of inner join.

SELECT *
FROM supplier
INNER JOIN customer ON supplier.s_id = customer.s_id;

11
● Use the table’s supplier & customer from the assignment no 2 and show
the working of left join.

SELECT *
FROM supplier
LEFT JOIN customer ON supplier.s_id = customer.s_id;

● Use the table’s supplier & customer from the assignment no 2 and show
the working of right join.

SELECT *
FROM supplier
RIGHT JOIN customer ON supplier.s_id = customer.s_id;

● Use the table’s supplier & customer from the assignment no 2 and show
the working of full outer join.

SELECT *
FROM supplier
FULL OUTER JOIN customer ON supplier.s_id = customer.s_id;

● Use the table’s supplier & customer from the assignment no 2 and show
the working of self join.

SELECT *
FROM table_name AS t1
JOIN table_name AS t2 ON t1.column_name = t2.column_name;

● Create a view of supplier with the new name supp.

12
 CREATE VIEW supp AS
SELECT * FROM supplier;

● Insert the values in the supp table

INSERT INTO supp VALUES (1, 'Supplier1', 'Address1');

● Delete any data from supp table.

DELETE FROM supp WHERE s_id = 1;

● Drop the view supp

DROP VIEW supp;

● Create the table of name math having field name & salary.
Insert into math values('rakesh',5000);
Insert into math values('rakesh',3000);
Insert into math values('saurav',4000);
Insert into math values('saurav',7000);

13
 ● From the above data find the total salary of rakesh & saurav using group
by command

SELECT name, SUM(salary) as total_salary

FROM math
GROUP BY name;

● Retrieve the data from the math table where name of person is ‘raju’
using having command.

SELECT *
FROM math
HAVING name = 'raju';

● Retrieve the data from the math table where the total salary of
any person is greater then 10000 using having command.

SELECT name, SUM(salary) as total_salary

FROM math
GROUP BY name
HAVING SUM(salary) > 10000;

(Note: The WHERE clause places conditions on the selected columns, whereas
the HAVING clause places conditions on groups created by the GROUP BY
clause. The HAVING clause must follow the GROUP BY clause in a query and
must also precede the ORDER BY clause if used. )

● Select all the data from math table where name having null values.

SELECT *
FROM math
WHERE name IS NULL;

14
● Select all the data from math table where name having not null values.

SELECT *
FROM math
WHERE name IS NOT NULL;

● Show the schema of math table.

DESCRIBE math;

● Rename the math table to new name math1

ALTER TABLE math RENAME TO math1;

● Create table student(id numeric(10), name varchar(40));

● Create table s_id(stud_id numeric(10), name varchar(40));

Insert into student values(1, ‘raju’);

Insert into student values(2, ‘ram’);
Insert into student values(3, ‘raman’);
Insert into student values(4, ‘rohit’);

Insert into s_id values(1, ‘ram’);

Insert into s_id values(2, ‘raman’);
Insert into s_id values(3, ‘amit’);

15
● Select the value from both student & s_id table using Union Command.

SELECT * FROM student

UNION
SELECT * FROM s_id;

● Use the minus command in the above tables student & S_id & Show the
result.

SELECT * FROM student

MINUS
SELECT * FROM s_id;

● Use the Interect command in the above tables student & S_id & Show the
result.

SELECT * FROM student

INTERSECT
SELECT * FROM s_id;

16