DBMS

1)	Student Database:
Student (Sid, Sname, Scity, Gender)
Course (Cid, Cname, Credits)
Reg_for (Sid, Cid)

A.	Find the details of students those who have registered for DBMS.
B.	Find the details of students those who have registered for at least 2 courses.
C.	Find the details of course for which more than 2 students have registered.
D.	To increase the credits of courses with current credits above 4, by 2 & remaining courses by 1.
E.	To display number of credits (total) student wise.
Student
Sid	sname	Scity	gender
1	Amit	Mumbai	M
2	Sachin	Navi Mumbai	M
3	Neetu 	Ahemdabad	F
4	Muskan	Delhi	F
5	Shital	Chennai	M
6	Naveen	Punjab	M
7	Ashwini	Kolkata	F

Course
Cid	Cname	Credit
10	COA	4
11	CG	3
12	DBMS	5
13	AOA	5

Reg_for
Sid	cid
1	11
1	12
2	10
2	11
2	12
3	13
4	12
4	13
4	11
5	10
6	10
6	11
6	12
6	13
7	12
7	13

2)	Create a trigger to set Cid of Reg_for table to new Cid when Cid of Course table is modified/updated.

ANSWERS:
1.)	SQL Queries:
a.)	Select * from Studentt
where sid IN (Select sid from Reg_for
		  where cid IN (Select cid from Course
				where Cname = 'DBMS'));

b.)	select * from Studentt
where sid IN (Select sid from Reg_for
		   group by sid
		   having count(cid)>1);

c.)	select * from Course
where cid IN (Select cid from Reg_for
		  group by cid
		  having count(sid)>2);

d.)	update Course
set Credits = Credits+2
where Credits > 4;
update Course
set Credits = Credits+1
where Credits <= 4;

e.)	Select Reg_for.sid, sum(Course.Credits) AS NumberOfCredits from Course
join Reg_for
on Reg_for.cid = Course.cid
group by Reg_for.sid;


2.)	create Trigger update_trigger
After Update on Course
FOR EACH ROW
when (new.cid is NOT NULL)
BEGIN
	update Reg_for
	set cid = :new.cid
	where cid = :old.cid;
END;


1)	Student Database:
Student (Sid, Sname, Scity, Gender)
Course (Cid, Cname, Credits)
Reg_for (Sid, Cid)

A.	Display the details of courses in which girls have registered.
B.	To count number of girl students.
C.	To display number of students course wise.
D.	To display the list of students those who have registered for course with cid=12, in addition to any other course.
E.	To find the details of courses in which a student from Mumbai has registered.
Student
Sid	sname	Scity	gender
1	Amit	Mumbai	M
2	Sachin	Navi Mumbai	M
3	Neetu 	Ahemdabad	F
4	Muskan	Delhi	F
5	Shital	Chennai	M
6	Naveen	Punjab	M
7	Ashwini	Kolkata	F

Course
Cid	Cname	Credit
10	COA	4
11	CG	3
12	DBMS	5
13	AOA	5

Reg_for
Sid	cid
1	11
1	12
2	10
2	11
2	12
3	13
4	12
4	13
4	11
5	10
6	10
6	11
6	12
6	13
7	12
7	13

2)	Create a trigger to set Sid of Reg_for table to new Sid when Sid of Student table is modified/updated.

ANSWERS:
1.)	SQL Queries:
a.)	Select * from Course
where cid IN (Select cid from Reg_for
		  where sid IN (Select sid from Studentt
				where Gender = 'F'));

b.)	Select count(sid) from Studentt
where Gender = ‘F’;

c.)	select cid, count(distinct sid) AS NumberOfStudents from Reg_for
group by cid;

d.)	select sid,sname from Studentt
where sid IN(select sid from Reg_for
		 where cid IN (select cid from Reg_for
			           where sid IN(select sid from Reg_for
					     group by sid
					     having count(sid)>1)) and cid = 12);
e.)	Select * from Course
where cid IN (Select cid from Reg_for
		  where sid IN (Select sid from Studentt
				where scity = 'Mumbai'));


2.)	create Trigger update_sid_trigger
After Update on Studentt
FOR EACH ROW
when (new.sid is NOT NULL)
BEGIN
	update Reg_for
	set sid = :new.sid
	where sid = :old.sid;
END;


1)	Emp (eid, ename, esal, ecity)
Project (pid, pname, plocation, pleaderid)
Works_on (eid, pid)

A.	To find the details of all the employees those who are working on a project whose project leader is employee with eid=2.
B.	To find the details of emps those who are not working on any project.
C.	To find the details of projects on which emp from Mumbai is working.
D.	To increase the salary of those who are working on project “BIGDATA” by 20%.
E.	To display the details of project on which no employee is working.
Emp
Eid	Ename	Esal	Ecity
1	Amit	50000	Mumbai
2	Sachin	30000	Pune
3	Neetu 	20000	Pune
4	Muskan	60000	Mumbai
5	Shital	55000	Delhi
6	Naveen	45000	Murbad
7	Ashwini	59000	Chennai
8	Ritika 	67000	hyderabad

Project
Pid	pname	plocation	Pleaderid
10	NETWORK	Mumbai	3
11	BIG DATA	Delhi	2
12	CLOUD	USA	2
13	ANDROID	UAE	3
14	IP	NY	NULL

Works_on
Eid	Pid
1	10
1	11
1	13
2	10
2	13
3	10
3	13
5	13
6	11
6	13
7	12
8	12
8	11

2)	Create a trigger to set Eid of Works_on table to new Eid when Eid of Employee table is modified/updated.

ANSWERS:
1.)	SQL Queries:
a.)	select * from Emp
where eid IN (select eid from Works_on
	   	    where pid in (Select pid from Project
				where pleaderid = 2));

b.)	select * from Emp
where eid NOT IN (select eid from Works_on
 		             where pid is not null);
OR (we can write only)

select * from Emp
where eid NOT IN (select eid from Works_on);
c.)	select * from Project
where pid IN (select pid from Works_on
	   	    where eid in (Select eid from Emp
				where ecity = 'Mumbai'));

d.)	update Emp
set esal = esal + 0.20
where eid in (select eid from Works_on
		  where pid in (select pid from Project
				where pname = 'BIG DATA'));

e.)	select * from Project
where pid NOT IN (select pid from Works_on
 		             where eid is not null);

OR (we can write only)

select * from Project
where pid NOT IN (select pid from Works_on);

2.)	create Trigger update_eid_trigger
After Update on Emp
FOR EACH ROW
when (new.eid is NOT NULL)
BEGIN
	update Works_on
	set eid = :new.eid
	where eid = :old.eid;
END;



1)	Emp (eid, ename, esal, ecity)
Project (pid, pname, plocation, pleaderid)
Works_on (eid, pid)

A.	To find details of employees those who are working on atleast all the projects whose project leader is an employee with id=3.
B.	To display employee details in the sorted order of their salary & name.
C.	To find top 5 employees as per their salary (top 5 earners).
D.	To find employee details with a city name starting with ‘m’, with second last letter as ‘A’ and consisting of 6 characters only.
E.	To find eid, ename of those with salary above the average salary of employees.

Emp
Eid	ename	Esal	Ecity
1	Amit	50000	Mumbai
2	Sachin	30000	Pune
3	Neetu 	20000	Pune
4	Muskan	60000	Mumbai
5	Shital	55000	Delhi
6	Naveen	45000	Murbad
7	Ashwini	59000	Chennai
8	Ritika 	67000	Hyderabad

Project
Pid	pname	plocation	Pleaderid
10	NETWORK	Mumbai	3
11	BIG DATA	Delhi	2
12	CLOUD	USA	2
13	ANDROID	UAE	3
14	IP	NY	NULL

Works_on
Eid	Pid
1	10
1	11
1	13
2	10
2	13
3	10
3	13
5	13
6	11
6	13
7	12
8	12
8	11

2)	Create a trigger to set Pid of Works_on table to new Pid when Pid of Project table is modified/updated.

ANSWERS:
1.)	SQL Queries:
a.)	select * from Emp e
where NOT EXISTS ((Select pid from Project
			  where pleaderid = 3)except(select pid from Works_on w
						        where e.eid = w.eid));

b.)	select * from Emp
order by ename,esal DESC;

c.)	select * from (select * from Emp
		    order by esal DESC)
where ROWNUM <= 5;

d.)	select * from Emp
where ecity LIKE 'm___A_';

e.)	select eid,ename from Emp
where esal > (select AVG(esal) from Emp);

2.)	create Trigger update_eid_trigger
After Update on Project
FOR EACH ROW
when (new.pid is NOT NULL)
BEGIN
	update Works_on
	set pid = :new.pid
	where pid = :old.pid;
END;






1)
Table Salesperson

ID	Name	Age	Salary
1	Abe	61	140000
2	Bob	34	44000
5	Chris	34	40000
7	Dan	41	52000
8	Ken	57	115000
11	Joe	38	38000


Table Customer

ID	Name	City	Industry Type
4	Samsonic	pleasant	J
6	Panasung	oaktown	J
7	Samony	jackson	B
9	Orange	Jackson	B

Table Orders
(Note: Date in MM/DD/YYYY format)

Order_Number	order_date	cust_id	salesperson_id	Amount
10	8/2/1996	4	2	540
20	1/30/1999	4	8	1800
30	7/14/1995	9	1	460
40	1/29/1998	7	2	2400
50	2/3/1998	6	7	600
60	3/2/1998	6	7	720
70	5/6/1998	9	7	150

Create all the above three tables with following constraints:
Create PRIMARY KEYS on each of these three tables
Create FOREIGN KEYS for establishing referential integrity between various tables

A.	Retrieve Id, Name and Age columns from SalesPerson table as “Sales Person ID”, “Sales Person Name” and “Age in Years” respectively

B.	Retrieve Id and Name (in capital letters) as single column with ‘-‘ in between and with column heading as ‘Id and Name’ along with  Age  field from Sales Person table(Use Concatenation Operator)

C.	Insert one sales person row in SalesPerson table for which Salary value is not available

D.	Retrieve information of all sales persons for whom Salary value is available

E.	Retrieve information of all sales persons having salary in the range of 90000 to 150000 both included

2)	Create view to find Sales Person ID wise total order amount having total order amount greater than 1500 in descending order of total order amount


ANSWERS:

1.)	SQL Queries:
a.)	select ID as "Sales Person ID",Name As "Sales Person Name",Age As "Age In Years" from Salesperson;

b.)	select  (convert(varchar(12),Id)+'_'+ upper (Name)) as "ID and Name",Age from Salesperson;
      … [NOTE: use || in place of + in mysql or oracle]

c.)	insert into Salesperson(ID,Name,Age)
values (101,'Saneet',36);

d.)	Select * from Salesperson
where salary is not null;

e.)	Select * from Salesperson
where 90000 <= salary and salary <= 150000;

2.)	Create view Total_order_amt AS
select top 100 percent Salesperson_id,Sum(Amount) AS Total_Amount
from Orders
group by salesperson_id
having sum(amount) >1500
order by Total_Amount DESC;


1)
Table Salesperson

ID	Name	Age	Salary
1	Abe	61	140000
2	Bob	34	44000
5	Chris	34	40000
7	Dan	41	52000
8	Ken	57	115000
11	Joe	38	38000


Table Customer

ID	Name	City	Industry Type
4	Samsonic	pleasant	J
6	Panasung	oaktown	J
7	Samony	jackson	B
9	Orange	Jackson	B

Table Orders
(Note: Date in MM/DD/YYYY format)

Order_Number	order_date	cust_id	salesperson_id	Amount
10	8/2/1996	4	2	540
20	1/30/1999	4	8	1800
30	7/14/1995	9	1	460
40	1/29/1998	7	2	2400
50	2/3/1998	6	7	600
60	3/2/1998	6	7	720
70	5/6/1998	9	7	150

Create all the above three tables with following constraints:
Create PRIMARY KEYS on each of these three tables
Create FOREIGN KEYS for establishing referential integrity between various tables

A.	Retrieve information of all sales persons having 3rd character as ‘r’ in their name

B.	Retrieve information of all customers who reside in ‘Jackson’ city with City in small letters. Comparison should be case insensitive

C.	Retrieve information about orders for salesperson_ID 7 and cust_ID 6 for amount greater than 650

D.	Retrieve information about orders received in the year 1998 in ascending order of month

E.	Find Industry Type wise number of customers

2)	Create view to find Sales Person ID wise total order amount having total order amount greater than 1500 in descending order of total order amount

ANSWERS:

1.)	SQL Queries:
a.)	select * from Salesperson
where Name LIKE '__r%';

b.)	select * from Customer
where lower(city) = 'jackson';

c.)	select * from Orders
where salesperson_id = 7 and cust_id = 6 and Amount > 650;

d.)	select * from Orders
where order_date like '%1998'
order by order_date ASC;

e.)	Select Industry_type, count(ID) AS NoofCustomers from Customer
group by Industry_type;


2.)	Create view Total_order_amount As
select Top 100 percent Salesperson_id,Sum(Amount) as Total_amount from Orders
group by salesperson_id
having sum(amount)>1500
order by Total_amount DESC;




1)

Table Salesperson

ID	Name	Age	Salary
1	Abe	61	140000
2	Bob	34	44000
5	Chris	34	40000
7	Dan	41	52000
8	Ken	57	115000
11	Joe	38	38000


Table Customer

ID	Name	City	Industry Type
4	Samsonic	pleasant	J
6	Panasung	oaktown	J
7	Samony	Jackson	B
9	Orange	Jackson	B

Table Orders
(Note: Date in MM/DD/YYYY format)

Order_Number	order_date	cust_id	salesperson_id	Amount
10	8/2/1996	4	2	540
20	1/30/1999	4	8	1800
30	7/14/1995	9	1	460
40	1/29/1998	7	2	2400
50	2/3/1998	6	7	600
60	3/2/1998	6	7	720
70	5/6/1998	9	7	150

Create all the above three tables with following constraints:
Create PRIMARY KEYS on each of these three tables
Create FOREIGN KEYS for establishing referential integrity between various tables
A.	Retrieve Id, Name (in capital letters) and Age columns from SalesPerson table of all sales person having salary greater than or equal to 50000

B.	Insert one customer row in Customer table for which Industry Type value is not available

C.	Retrieve information of all Customers for whom Industry Type value is not available

D.	Retrieve information of all sales persons having last character as ‘n’ in their name

E.	Retrieve information of all customers having ID less than 6 and IndustryType not ‘b’;
Note lower ‘b’ (Comparison should be case insensitive)

2)	Create view to retrieve information about orders for salesperson_ID 2  or 7 without using OR operator in the ascending order of Salesperson_ID and descending order of amount

ANSWERS:

1.)	SQL Queries:
a.)	select ID,upper(Name)AS Name,Age from Salesperson
where Salary >=50000;

b.)	insert into customer(ID,Name,City)
values (10,'Naman','Mumbai');

c.)	Select * from Customer
where Industry_Type is null;

d.)	Select * from Salesperson
where Name like '%n';

e.)	Select * from customer
where ID < 6 and industry_type <>'b';

2.)	Create view order_by_2or3 As
select Top 100 * from orders
where Salesperson_id IN ((select ID from Salesperson
				 where ID = 2)union(select ID from Salesperson
							where ID = 7))
order by Salesperson_id,Amount DESC;



1)
Table Salesperson

ID	Name	Age	Salary
1	Abe	61	140000
2	Bob	34	44000
5	Chris	34	40000
7	Dan	41	52000
8	Ken	57	115000
11	Joe	38	38000


Table Customer

ID	Name	City	Industry Type
4	Samsonic	pleasant	J
6	Panasung	oaktown	J
7	Samony	jackson	B
9	Orange	Jackson	B

Table Orders
(Note: Date in MM/DD/YYYY format)

Order_Number	order_date	cust_id	salesperson_id	Amount
10	8/2/1996	4	2	540
20	1/30/1999	4	8	1800
30	7/14/1995	9	1	460
40	1/29/1998	7	2	2400
50	2/3/1998	6	7	600
60	3/2/1998	6	7	720
70	5/6/1998	9	7	150

Create all the above three tables with following constraints:
Create PRIMARY KEYS on each of these three tables
Create FOREIGN KEYS for establishing referential integrity between various tables

A.	Retrieve information of all sales persons having salary either 40000 or 44000 or 52000 without using OR operator

B.	Retrieve information about orders received from year 1995 to 1996 in the ascending order of year

C.	Find the number of customers having Industry Type NULL / not available

D.	Find Sales Person ID wise total order amount

E.	Find Customer ID wise total order amount in descending order of total order amount

2)	Create a view to retrieve the information of a sales persons having age less than 40 and earning minimum salary

ANSWERS:

1.)	SQL Queries:
a.)	select * from Salesperson
where ID IN ((select ID from Salesperson
		      where salary = 40000)union(select ID from Salesperson
			 where salary = 44000)union(select id from Salesperson
							    where salary = 52000));

b.)
c.)	Select * from Customer
where Industry_Type is null;

d.)	select Salesperson_id,Sum(Amount) as TotalOrderAmount from Orders
group by salesperson_id;

e.)	select Cust_id,Sum(Amount) as TotalOrderAmount from Orders
group by cust_id
order by TotalOrderAmount DESC;


2.)	create view min_Salary As
select * from Salesperson
where age < 40 and salary = (select min(salary) from Salesperson);






1)
Create the following Tables:

LOCATION
Location_ID	Regional_Group
122	NEW YORK
123	DALLAS
124	CHICAGO
167	BOSTON

DEPARTMENT
Department_ID	Name	Location_ID
10	ACCOUNTING	122
20	RESEARCH	124
30	SALES	123
40	OPERATIONS	167

JOB
Job_ID	Function
667	CLERK
668	STAFF
669	ANALYST


EMPLOYEE
EMPLOYEE_ID	LAST_NAME	FIRST_
NAME	MIDDLE_NAME	JOB_ID	MANAGER_ID	HIREDATE	SALARY	COMM	DEPARTMENT_ID
7369	SMITH	JOHN	Q	667	7902	17-DEC-84	800	NULL	20
7499	ALLEN	KEVIN	J	670	7698	20-FEB-85	1600	300	30
7505	DOYLE	JEAN	K	671	7839	04-APR-85	2850	NULL	30
7506	DENNIS	LYNN	S	671	7839	15-MAY-85	2750	NULL	30
7507	BAKER 	LESLIE	D	671	7839	10-JUN-85	2200	NULL	40

7521	WARK	CYNTHIA	D	670	7698	22-FEB-85	1250	500	30

Create all the above three tables with following constraints:
Create PRIMARY KEYS on each of these three tables
Create FOREIGN KEYS for nestablishing referential integrity between various tables

A.	Modify LOCATION table structure to change regional _group column width to 15.

B.	List out employee_id, last name, first name and annual salary for all the employees

C.	List out the employees whose name length is 4 and start with “S”

D.	List out the employee details according to their last_name in ascending order and salaries in descending order

E.	List out the distinct jobs in Sales and Accounting Departments

2)	Create View to display details of all the employees’ with their designations (jobs)


ANSWERS:

1.)	SQL Queries:

a.)

b.)	select eid,last_name,first_name,Salary*12 As AnnualSalary from Employee;

c.)	select * from Employee
where last_name like 'S___' or first_name like 'S___' or middle_name like 'S___';

d.)	select * from Employee
order by last_name, Salary DESC;

e.)	select distinct E.Job_ID, D.Name from Employee E, Department D
where D.D_ID = E.D_ID and D.Name = 'Sales' and d.Name = 'Accounting';

2.)	create view DesignationOfEmployee AS
select E.*, J.Functioned_As from Employee E, Job J
where E.job_id = J.Job_ID;














1)
Create the following Tables:

LOCATION
Location_ID	Regional_Group
122	NEW YORK
123	DALLAS
124	CHICAGO
167	BOSTON

DEPARTMENT
Department_ID	Name	Location_ID
10	ACCOUNTING	122
20	RESEARCH	124
30	SALES	123
40	OPERATIONS	167

JOB
Job_ID	Function
667	CLERK
668	STAFF
669	ANALYST


EMPLOYEE
EMPLOYEE_ID	LAST_NAME	FIRST_
NAME	MIDDLE_NAME	JOB_ID	MANAGER_ID	HIREDATE	SALARY	COMM	DEPARTMENT_ID
7369	SMITH	JOHN	Q	667	7902	17-DEC-84	800	NULL	20
7499	ALLEN	KEVIN	J	670	7698	20-FEB-85	1600	300	30
7505	DOYLE	JEAN	K	671	7839	04-APR-85	2850	NULL	30
7506	DENNIS	LYNN	S	671	7839	15-MAY-85	2750	NULL	30
7507	BAKER 	LESLIE	D	671	7839	10-JUN-85	2200	NULL	40

7521	WARK	CYNTHIA	D	670	7698	22-FEB-85	1250	500	30

Create all the above three tables with following constraints:
Create PRIMARY KEYS on each of these three tables
Create FOREIGN KEYS for establishing referential integrity between various tables

A.	Display the employees who are working in Sales department

B.	Display the employee who got the maximum salary.

C.	List out the distinct jobs in Sales and Accounting Departments

D.	Update employee’s salaries, who are working as Clerk on the basis of 10%.

E.	List out the employees who earn more than every employee in department 30

2)	Create a trigger to set Department_ID of Employee table to new department_ID when Department_ID of Department table is modified/updated.

ANSWERS:

1.)	SQL Queries:
a.)	Select E.eid,E.first_name,E.last_name  from Employee E,Department D
where E.D_ID = D.D_ID and d.Name = 'Sales';

b.)	Select E.eid,E.first_name,E.last_name from Employee E
where E.Salary = (Select max(Salary) from Employee);

c.)	Same as the (e) of the above question.

d.)	update Employee
set Salary = salary + 0.10
where job_id in (select j.Job_ID from Job j
			where j.Functioned_As = 'Clerk');

e.)	select * from Employee
where salary > (select max(salary) from Employee E
				where E.D_ID = 30
				group by E.D_ID);


2.)	create Trigger update_D_ID_trigger
After Update on Department
FOR EACH ROW
when (new.D_id is NOT NULL)
BEGIN
	update Employee
	set D_id = :new.D_id
	where D_id = :old.D_id;
END;