INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS
PRACTICAL RECORD
PAPER CODE :
Name of the student :
University Roll No. :
Course : BCA
PRACTICAL DETAILS
Date of Date of
[Link]. Experiment Name Remarks
Performance Checking
1. Create a table “PRODUCTS” with following
structures:
2. Consider following table and write SQL Commands
for the following:
3. Create table EMP and DEPT.
4. Insert the values into EMP table.
5. Considering EMP table write the queries.
PRACTICAL - 1
Aim: 1) Create a table “PRODUCTS” with the below mentioned structure:
Product ID NUMBER(11)
Supplier ID NUMBER(11)
Category ID NUMBER(11)
Quantity Per Unit VARCHAR2(20)
Unit Price NUMBER(11)
Units In Stock NUMBER(11)
Units On Order NUMBER(11)
Product ID should be the Primary Key.
PRACTICAL - 2
2) Consider the following tables:
WORKS(Pname,Cname,Salary)
LIVES(Pname,Street,City)
LOCATED_IN(Cname,City)
MANAGER(Pname,Mgername)
Where Pname=Person name, Cname= Company name and Mgrname = Manager name.
Write the SQL for the following:
a) List the names of the people who work for the company Wipro along with the cities
they live in.
b) Find the people who work for the company “Infosys” with a salary more than Rs.
50000/-. List the names of the people , along with the street and city
addresses.
c) Find the names of the persons who live and work in the same city.
d) Find the names of the persons who dod not work for “Infosys”.
e) Find the persons whose salaries are more than that of all of the “Oracle” employees.
f) Find the names of the companies that are located in every city where the company
“Infosys” is located.
Ans:
a) List the names of the people who work for Wipro along with the cities they live in
SELECT [Link], [Link]
FROM WORKS W, LIVES L
WHERE [Link] = [Link]
AND [Link] = 'Wipro';
b) Find the people who work for Infosys with a salary more than Rs. 50000
List the names along with street and city addresses
SELECT [Link], [Link], [Link]
FROM WORKS W, LIVES L
WHERE [Link] = [Link]
AND [Link] = 'Infosys'
AND [Link] > 50000;
c) Find the names of the persons who live and work in the same city
SELECT DISTINCT [Link]
FROM WORKS W, LIVES L, LOCATED_IN LI
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link];
d) Find the names of the persons who do not work for Infosys
SELECT DISTINCT Pname
FROM WORKS
WHERE Pname NOT IN
(
SELECT Pname
FROM WORKS
WHERE Cname = 'Infosys'
);
e) Find the persons whose salaries are more than all Oracle employees
SELECT Pname
FROM WORKS
WHERE Salary > ALL
(
SELECT Salary
FROM WORKS
WHERE Cname = 'Oracle'
);
f) Find the names of the companies that are located in every city where Infosys is located
SELECT DISTINCT Cname
FROM LOCATED_IN L1
WHERE NOT EXISTS
(
SELECT City
FROM LOCATED_IN
WHERE Cname = 'Infosys'
AND City NOT IN
(
SELECT City
FROM LOCATED_IN L2
WHERE [Link] = [Link]
)
);
PRACTICAL 3
Aim: Create table EMP and DEPT.
EMP Table
DEPT Table
PRACTICAL 4
AIM: Insert the values into EMP table.
(a) Insert into EMP
PRACTICAL 5
AIM: Considering EMP table write the queries.
(a) Find out the number of employees having “manager” as job.
(b) Display only the jobs with maximum salary greater than or equal to 3000.
(c) Find all those employees whose job does not start with ‘M’.
(d) Find the names of employees who are managers and their date of joining
is after “02-JAN-2006”.
(e) For describing the structure of the EMP table and DEPT table.
(f) For getting the average salary of employees from EMP table.
(g) For displaying the current date and give the column a name “DATE”.
(h) For converting the name of employee into uppercase where the
employee’s name is “Santy”.
(i) Create a sequence with name SEQ_EMP, which will generate numbers
from 1 to 99 in ascending order with an interval of 1. The sequence must
start from 1 after generating the number 99.
(j) Displaying the names of the employees who have an a and e in their
names.