DBMS Sample questions

dbms_pracs_sample_1.sql

/* Drop old tables */ 

drop table works_on; 
drop table project; 
drop table employee;
drop table dept; 

/* Create table */ 

create table dept(
	d_no varchar(6), 
	d_name varchar(20), 
	no_ppl int, 
	check(no_ppl > 0), 
	check(d_no like 'D%'), 
	primary key(d_no)
);

create table employee(
	ssn varchar(6), 
	d_no varchar(6), 
	name varchar(20), 
	address varchar(20), 
	sal int, 
	check(sal > 0),
	check(ssn like 'S%'),  
	check(d_no like 'D%'), 
	primary key(ssn), 
	foreign key(d_no) references dept(d_no), 
	check(no_ppl > 0)
);

create table project(
	pname varchar(20), 
	pnum varchar(6), 
	ploc varchar(20), 
	d_no varchar(6), 
	primary key(pnum), 
	check(pnum like 'P%'), 
	check(d_no like 'D%'),
	foreign key(d_no) references dept(d_no)
);

create table works_on(
	essn varchar(6), 
	pnum varchar(6), 
	hrs int, 
	check(essn like 'S%'), 
	check(p_no like 'P%'),
	check(hrs > 0), 
	foreign key(essn) references employee(ssn), 
	foreign key(pnum) references project(pnum)
);

/* Q(1)
/* Insertion */ 

/* dept */ 

insert into dept values('D01', 'Marketing', 1);
insert into dept values('D02', 'HR', 2);
insert into dept values('D03', 'Public Relations', 1);
insert into dept values('D04', 'Business Development', 1);
insert into dept values('D05', 'PR', 2); 
 
/* employee */ 

insert into employee values('S1', 'D01', 'Akash', 'Mumbai', 15000);
insert into employee values('S2', 'D02', 'Ajay', 'Bangalore', 17000);
insert into employee values('S3', 'D04', 'Vijay', 'Mumbai', 5000);
insert into employee values('S4', 'D02', 'Ram', 'Delhi', 20000);
insert into employee values('S5', 'D03', 'Hari', 'Lucknow', 30000);
insert into employee values('S6', 'D05', 'Bruce', 'Mumbai', 40000); 
insert into employee values('S7', 'D05', 'Lee', 'Delhi', 60000); 

/* project */ 

insert into project values('Batman','P1','Mumbai','D01');
insert into project values('Cyclone','P2','Lucknow','D03');
insert into project values('Darvin','P3','Delhi','D02');
insert into project values('Fireball','P4','Mumbai','D04');
insert into project values('Skyrim', 'P5', 'Mumbai', 'D05'); 


/* works_on */ 
insert into works_on values('S1', 'P1', 2);
insert into works_on values('S2', 'P3', 3);
insert into works_on values('S4', 'P4', 5);
insert into works_on values('S3', 'P3', 6);
insert into works_on values('S6', 'P5', 2); 
insert into works_on values('S7', 'P5', 2); 


/* Q(2) add check constraint */ 

alter table project add constraint ploc_check check(ploc in ('Mumbai', 'Delhi')); 

/* Q(3) - Remove the above added constraint ,add dummy data, run query */ 
alter table project drop constraint ploc_check; /* fix */ 
insert into project values('Rhino','P8','Stafford','D03');
insert into project values('Icebolt','P9','Stafford','D02');
select pnum as PROJECT_NUMBER,d_no as DEPT_NUMBER from project where ploc = 'Stafford';  

/* Q(4) */ 
select employee.name,project.d_no from employee,project where employee.d_no = project.d_no and project.d_no = 'D05';

/* 
+-------+------+
| name  | d_no |
+-------+------+
| Bruce | D05  |
| Lee   | D05  |
+-------+------+
*/

dbms_pracs_sample_2.sql

/*
Consider the following relations for an order processing
databse application
Customer ( Cust# , Cname, City )
Order ( Order# ,Odate, Cust#, Ord_amt )
Order_item ( Order#, item#, Qty )
Item ( Item#, Unit_price )

1. Create tables using check constraint for city value has
to be any one of the following Chennai, Mumbai or
Delhi. Cname must be in capital letters. Hence then
insert appropriate records.
Minimum 4 records in each table. Appropriate Integrity
constraint must be enforced.

2. Find the custnames who have placed an order more
than worth rupees 10000.

3. Find the maximum order placed from Chennai based
customers.

*/

drop table order_item; 
drop table item; 
drop table ordr; 
drop table customer; 

create table customer(
	cust_id varchar(6) not null, 
	cust_name varchar(20) not null, 
	cust_city varchar(20), 
	primary key(cust_id), 
	check(cust_id like 'C%')
	); 

create table ordr (
	ord_id varchar(6) not null, 
	ord_date date,
	cust_id varchar(6) not null, 
	ord_amt int, 
	check(cust_id like 'C%'), 
	check(ord_amt > 0),
	check(ord_id like 'O%'), 
	primary key(ord_id), 
	foreign key(cust_id) references customer(cust_id)
	);

create table item(
	item_id varchar(6) not null, 
	price numeric(8,2), 
	check(price > 0), 
	check(item_id like 'I%'), 
	primary key(item_id)
);

create table order_item(
	ord_id varchar(6) not null, 
	item_id varchar(6) not null, 
	qty int, 
	check(qty > 0), 
	check(order_id like 'O%'), 
	check(item_id like 'I%'), 
	foreign key(ord_id) references ordr(ord_id),
	foreign key(item_id) references item(item_id) 
);

/* Add constraint on city */ 
alter table customer add constraint city_check check(cust_city in ('Chennai','Mumbai','Delhi'));

/* Insertion */ 
insert into item values('I01', 20); 
insert into item values('I02', 100); 
insert into item values('I03', 200); 
insert into item values('I04', 300); 
insert into item values('I05', 400); 
insert into item values('I06', 500); 
insert into item values('I07', 550); 
insert into item values('I08', 570); 
insert into item values('I09', 580); 
insert into item values('I10', 600); 

insert into customer values('C101', 'Ram', 'Mumbai'); 
insert into customer values('C102', 'Ajay', 'Delhi'); 
insert into customer values('C103', 'Vijay', 'Mumbai'); 
insert into customer values('C104', 'Suresh', 'Delhi'); 
insert into customer values('C105', 'Ramesh', 'Chennai'); 
insert into customer values('C106', 'Akash', 'Mumbai'); 
insert into customer values('C107', 'Bruce', 'Chennai'); 

insert into ordr values('O1','2015-03-15','C101', 5000); 
insert into ordr values('O2','2015-04-12','C102', 10000); 
insert into ordr values('O3','2015-05-13','C103', 7000); 
insert into ordr values('O4','2015-06-10','C104', 50000); 
insert into ordr values('O5','2015-07-20','C104', 65000);
insert into ordr values('O6','2015-08-21','C105', 55000); 
insert into ordr values('O7','2015-09-17','C107', 5000); 
insert into ordr values('O8','2015-09-18','C105', 3000);

insert into order_item values('O1', 'I01', 10); 
insert into order_item values('O1', 'I02', 20); 
insert into order_item values('O2', 'I03', 30); 
insert into order_item values('O2', 'I04', 15); 
insert into order_item values('O3', 'I05', 12); 
insert into order_item values('O4', 'I05', 36); 
insert into order_item values('O5', 'I06', 42); 
insert into order_item values('O6', 'I06', 58); 
insert into order_item values('O7', 'I10', 88); 
 

/* Queries */ 

/* Names of all customers who have placed an order worth >= 10000 */ 
select distinct customer.cust_name from customer,ordr where ordr.cust_id = customer.cust_id and ordr.ord_amt >= 10000; 
 
/* Maximum order placed by chennai based customer */ 

select max(T.ord_amt) from (select distinct customer.cust_name,ordr.ord_amt,customer.cust_city from customer,ordr where ordr.cust_id = customer.cust_id and customer.cust_city = 'Chennai') as T; 

dbms_pracs_sample_3.sql

/*

Consider the following relations
Employee (SSN, Name, Address, Salary, Dno, Ph_Num)
(Note Ph_num is the Multivalued attribute)
Dependent ( Essn, Dependent_name, Bdate )
Department ( Dname,Dnumber,MGRSSN )

 1. Create tables using check constraint for salary column in
range of 10000 to 30000
 and insert appropriate records. Minimum 4 records in
each table. Appropriate
 integrity constraint must be enforced.

2. Retrieve the names of all employees who have no
dependents.

3. List the names of managers who have at least one
dependent.

4. List the phone numbers of all employees for any
particular department

*/ 

drop table dependent; 
drop table department; 
drop table employee; 

create table employee(
	ssn varchar(6) not null, 
	name varchar(20) not null, 
	address varchar(20),
	sal numeric(8,2), 
	dno varchar(6), 
	phno varchar(12),  

	primary key(ssn), 
	check(sal > 0), 
	check(dno like 'D%'), 
	check(ssn like 'SSN%'),
	check(phno > 0),
	check(sal > 10000 and sal < 30000)
);

create table department(
	dname varchar(20) not null, 
	dno varchar(6) not null, 
	mgrssn varchar(6) not null, 
	check(dno like 'D%'), 
	check(mgrssn like 'SSN%'),
	foreign key(mgrssn) references employee(ssn),
	primary key(dno)
);

create table dependent(
	ssn varchar(6), 
	check(ssn like 'SSN%'), 
	dep_name varchar(20), 
	bdate date, 
	foreign key(ssn) references employee(ssn)
);



/* Add for key for dno */ 
alter table department add constraint fk_ssn foreign key(mgrssn) references employee(ssn); 


/* Insertion */ 
insert into employee values('SSN1', 'Ajay', 'Mumbai', 12000, 'D1',   9988776655); 
insert into employee values('SSN2', 'Vijay', 'Delhi', 13000, 'D2',  9932126655); 
insert into employee values('SSN3', 'Suresh', 'Bangalore', 11000, 'D3', 9988712355); 
insert into employee values('SSN4', 'Ramesh', 'Mumbai', 15000, 'D4', 9988734555); 
insert into employee values('SSN5', 'John', 'Chennai', 20000, 'D2',   9459283655); 
insert into employee values('SSN6', 'Alan', 'Delhi', 29000, 'D2',   9193843655); 
insert into employee values('SSN7', 'Bruce', 'Mumbai', 23000, 'D3',  8747383655); 
insert into employee values('SSN8', 'Lee', 'Bangalore', 19000, 'D4',    7293936652); 

insert into department values('Marketing','D1','SSN1');
insert into department values('HR', 'D2','SSN6'); 
insert into department values('PR', 'D3', 'SSN7'); 
insert into department values('Business Development', 'D4', 'SSN8');
insert into department values('Logistics', 'D5', 'SSN2');

insert into dependent values('SSN1', 'Max', '1992-05-15'); 
insert into dependent values('SSN3', 'Dwayne', '1991-02-5');
insert into dependent values('SSN4', 'Chris', '1990-01-10');
insert into dependent values('SSN6', 'Steve', '1988-08-8');
insert into dependent values('SSN7', 'Matt', '1994-07-9');
insert into dependent values('SSN8', 'Jim', '1990-01-10');
insert into dependent values('SSN8', 'Raj', '1988-09-18');
insert into dependent values('SSN3', 'Kartik', '1994-02-19');

/* List the names of managers who have at least one
dependent. */ 

select employee.name, employee.ssn from employee where employee.ssn in (select distinct mgrssn from department,dependent where department.mgrssn = dependent.ssn);
/* 

+-------+------+
| name  | ssn  |
+-------+------+
| Ajay  | SSN1 |
| Alan  | SSN6 |
| Bruce | SSN7 |
| Lee   | SSN8 |
+-------+------+
4 rows in set (0.00 sec)

*/

/* List of employees with no dependent */ 

select ssn,name from employee where ssn not in (select distinct ssn from dependent);

/*
+------+-------+
| ssn  | name  |
+------+-------+
| SSN2 | Vijay |
| SSN5 | John  |
+------+-------+

*/

/* List phone numbers of all employees of a particular department */ 
select employee.phno from employee;