db

something 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_fortable to new Cid when Cid of Coursetable is modified/updated.

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

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

c.) select * from Course wherecid 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 joinReg_for onReg_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 updateReg_for setcid = :new.cid wherecid = :old.cid; END;

Q2 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_fortable to new Sid when Sid ofStudent table is modified/updated.

ANSWERS: 1.) SQL Queries: a.) Select * from Course wherecid IN (Select cid from Reg_for wheresid 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 wheresid IN(select sid from Reg_for group by sid having count(sid)>1)) and cid = 12); e.) Select * from Course wherecid IN (Select cid from Reg_for wheresid 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 updateReg_for setsid = :new.sid wheresid = :old.sid; END;

Q3 1Emp (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_ontable to new Eid when Eid of Employee table is modified/updated.

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

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

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

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

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

OR (we can write only)

select * from Project wherepid 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 updateWorks_on seteid = :new.eid whereeid = :old.eid; END;

Q4 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_ontable 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 wherepleaderid = 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 whereecity LIKE 'm___A_';

e.) select eid,ename from Emp whereesal> (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 updateWorks_on setpid = :new.pid wherepid = :old.pid; END;

Q5 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

1) 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 selecttop 100 percent Salesperson_id,Sum(Amount) AS Total_Amount from Orders group by salesperson_id havingsum(amount) >1500 order by Total_Amount DESC;

Q6 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

1) 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 wheresalesperson_id = 7 and cust_id = 6 and Amount > 650;

d.) select * from Orders whereorder_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)
Q7 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 whereIndustry_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 selectTop 100 * from orders whereSalesperson_id IN ((select ID from Salesperson where ID = 2)union(select ID from Salesperson where ID = 7)) order by Salesperson_id,Amount DESC;

1) Q8 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 whereIndustry_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) Q9 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 wherelast_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 whereE.job_id = J.Job_ID;

1) Q10 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 2 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 whereE.Salary = (Select max(Salary) from Employee);

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

d.) update Employee set Salary = salary + 0.10 wherejob_id in (select j.Job_ID from Job j wherej.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 updateEmployee setD_id = :new.D_id whereD_id = :old.D_id; END;