DBMS File
DBMS File
DBMS File
PRACTICAL FILE
(2022-2025)
SUBMITTED BY:
UJJVALADITYA PANDEY
ENROLLMENT NO.:
00821001922
UNDER THE GUIDANCE OF
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.
UJJVALADITYA PANDEY
Create the customer table having following entities make cust_id field as a primary
key.
cust_id Name Address Doj
1 Ram6 Delhi '30-aug-85'
2 Sham Rohtak '30-jun-86'
3 Rohan Gujrat '30-may-87'
4 Rajan Rohtak '30-may-88'
View cust_id and name from customer table whose doj is ’30-may-88’
View all the data of customer table whose doj is greater then ‘30-may-87’
View all the records of customer table whose name is rohan or doj is ‘30-may-87’
View all the data of customer table whose doj is between '30-may-85' and '17-
nov-86';
View all the records of customer table where the address can be delhi, gujrat,
sirsa.
Update the address of customer table whose cust_id=4 with new address ‘sirsa’
View all the records of customer whose name starting with char ‘r’.
View all the records of customer whose name starting and ending letter is
anything but these two letters ‘am’ should be in between the name.
View all the data of customer table & their address should be in ascending order.
ORACLE ASSIGNMENT -2
SELECT *
FROM emp
WHERE salary = (SELECT MAX(salary) FROM emp);
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
Retrieve the data from two different tables with help of union command.
(NOTE: FOR THIS COMMAND BOTH THE TABLE MUST HAVE SAME
NUMBER OF FIELDS AND MUST HAVE SAME DATATYPE)
CREATE TABLE student (
stud_id NUMERIC(10),
stud_name VARCHAR(50)
);
Delete all the data from student table using truncate command
Create the table name PO having field 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(255),
score INT
);
Create the table name RI having field id, name. In this table name should be
ram. (Use Check Command)
CREATE TABLE RI (
id INT CHECK (id > 0),
name VARCHAR(255) CHECK (name = 'ram')
);
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 inner join.
SELECT *
FROM supplier
INNER JOIN customer ON supplier.s_id = customer.s_id;
SELECT *
FROM supplier
RIGHT JOIN customer ON supplier.s_id = customer.s_id;
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);
From the above data find the total salary of rakesh & saurav using group by
command
SELECT *
FROM Math
WHERE name = 'raju';
Retrieve the data from the math table where the total salary of
any person is greater than 10000 using having command.
Select all the data from math table where name having null values.
SELECT *
FROM math
WHERE name IS NULL;
Select all the data from math table where name having not null values.
SELECT *
FROM math
WHERE name IS NOT NULL;
DESCRIBE math;
Select the value from both student & s_id table using Union Command.
Use the minus command in the above tables student & S_id & Show the result.