DBMS File

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 32

DATABASE MANAGEMENT SYSTEM LAB

PRACTICAL FILE

SUBMITTED IN THE PARTIAL FULFILLMENT OF THE DEGREE OF

BACHELOR OF BUSINESS ADMINISTRATION(CAM)

(2022-2025)

SUBMITTED BY:
UJJVALADITYA PANDEY
ENROLLMENT NO.:
00821001922
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)
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.

UJJVALADITYA PANDEY

Enroll. no.: 00821001922


ORACLE ASSIGNMENT 1

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

CREATE TABLE customer (


cust_id INT PRIMARY KEY,
Name VARCHAR(50),
Address VARCHAR(100),
Doj DATE
);

INSERT INTO customer (cust_id, Name, Address, Doj) VALUES


(1, 'Ram', 'Delhi', '1985-08-30'),
(2, 'Sham', 'Rohtak', '1986-06-30'),
(3, 'Rohan', 'Gujarat', '1987-05-30'),
(4, 'Rajan', 'Rohtak', '1988-05-30');
 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

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 = '1988-05-30';

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

SELECT * FROM customer WHERE Doj > '1987-05-30';


 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 = '1987-05-30';

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

SELECT * FROM customer WHERE Doj BETWEEN '1985-05-30' AND '1986-11-


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

SELECT * FROM customer WHERE NOT (Doj BETWEEN '1985-05-30' AND


'1986-11-17');

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

 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%';

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

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

ORACLE ASSIGNMENT -2

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


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

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(*) AS total_records
FROM emp;
 Calculate the total salary from emp table

SELECT SUM(salary) AS total_salary


FROM emp;

 Calculate the average of salary from emp table.

SELECT AVG(salary) AS average_salary


FROM emp;

 Calculate the maximum salary from emp table.

SELECT MAX(salary) AS max_salary


FROM emp;
 Calculate the minimum salary from emp table

SELECT MIN(salary) AS 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(255);

 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


MODIFY COLUMN name VARCHAR(100);

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

SELECT id, name FROM emp


UNION
SELECT stud_id, stud_name FROM student;

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

CREATE TABLE stud (


id NUMERIC(10) UNIQUE,
name VARCHAR(50)
);

INSERT INTO stud (id, name) VALUES


(1, 'UJJVAL'),
(2, 'NAMAN'),
(3, 'HARSHIT');

SELECT * FROM stud;


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

INSERT INTO supplier (s_id, s_name, address) VALUES


(1, 'Jesse Pinkman', '9809 Margo Street, Albuquerque'),
(2, 'Walter White', '3828 Piermont Dr, Albuquerque'),
(3, 'Gus Fring', '1213 Jefferson St NE, Albuquerque');

SELECT * FROM supplier;

 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,
FOREIGN KEY (s_id) REFERENCES supplier(s_id)
);

INSERT INTO customer (c_id, c_name, address, s_id) VALUES

(101, 'UJJVAL', 'NOIDA', 1),


(102, 'TAYLOR SWIFT', 'NEW YORK', 2),
(103, 'KANYE WEST', 'LAS VEGAS', 3);
SELECT * FROM customer

 Retrieve all the data from customer and supplier tables.

ALTER TABLE Customer


DROP COLUMN c_id;

SELECT c_name, address, s_id FROM customer


UNION ALL
SELECT s_name, address, s_id FROM supplier;
 Show the name field records with any other alias name.

SELECT c_name AS Celebrity FROM customer;

 Delete all the data from student table using truncate command

TRUNCATE TABLE student;


ORACLE ASSIGNMENT -3

 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 a view of supplier with the new name supp.

CREATE VIEW supp AS


SELECT * FROM supplier;

SELECT * FROM supp;

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

CREATE TABLE math (


name VARCHAR(255),
salary INT
);
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 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
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 name, SUM(salary) AS total_salary


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

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

 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 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’);

CREATE TABLE student (


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

CREATE TABLE s_id (


stud_id NUMERIC(10),
name VARCHAR(40)
);

INSERT INTO s_id VALUES (1, 'ram');


INSERT INTO s_id VALUES (2, 'raman');
INSERT INTO s_id VALUES (3, 'amit');

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

SELECT id, name FROM student


UNION
SELECT stud_id, name FROM s_id;

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

SELECT id, name FROM student


MINUS
SELECT stud_id, name FROM s_id;
 Use the Intersect command in the above tables student & S_id & Show the result.

SELECT name FROM student


INTERSECT
SELECT name FROM s_id;

You might also like