Z Se mean ep BCA:208- LAB V; Database Management System Lab 1 Scheme of Examination:- ea fection Will be of 3 hours duration, The distribution of practical marks Program | (Oracle) « 20 Program 2 (Oracle) 4 20 Program 3 (Oracle) . 20 Viva : 5 [Practical Copy + Practical Sessional} - 1s Total - 100 2 Inevery program there should be comment for each coded line or block of code 3 practical files should contain printed programs with name of author, date, path of program, unit no. and printed output. 4 All the following programs or a similar type of programs should be prepared List of Practical 1. Using the following database, Colleges (cname, city, address, phone, aftate) Staffs ( sid, sname, saddress, contacts) Staffloins ( sid, cname, dept, DOJ, post, salary) Teachings (sid, class, paperid,fsession, tsession) Subjects ( paperid, subject, paperno, papername) Write SQL statements for the following — Create the above tables with the given specifications and constraints Insert about 10 rows as are appropriate to solve the following queries. List the names of the teachers teaching computer subjects. List the names and cities of all staff working in your college. List the names and cities of al staff working in your college who earn more than 15,000 Find the staffs whose names stat with “M” or R’ and ends with “A” and/or 7 characters long. Find the staffs whose date of joining is 2005, Modify the database so that staff NI now works in C2 College. List the names of subjects, which T1 teaches in this session or all sessions. FFind the classes that TI do not teach at present session, a. Find the colleges who have most number of staff. . Find the staffs that car a higher salary who carn greater than average salary of their college. Find the colleges whose average salary is more than average salary of C2 Find the college that has the smallest payroll. Find the colleges where the total salary is greater than the average salary of all colleges. List maximum, average, minimum salary of each college List the names of the teachers, departments teaching in more than one department Acquire details of staffs by name in a college or each college, Find the names of staff that eam more than each staff of C2 College. Give all principals a 10% rise in salary unless their salary becomes greater than 20,000 in ‘such case give 5% rise, ©. Find al staff that do not work in same cites as the colleges they work ™ : ty hye. ati s ti aN We Ce Be )£ List names of em i i i i clitoral eng, ante mrtg myo ew aig ny & Create a view having fields sname, ename, dept, DOJ, and post ». Sekt VW coming of cme average sry and oa airy of al staf in at & Select the colleges having highest and lowest average salary using above views: 4. List the staff namnes ofa department using above views, i 2 Create the following database, Enrollment (enrolino, name, gender, DOB, address, phone) Admission (admno, enrollno, course, yearsom, date, crame) Colleges (cname, city, address, phone, aftlate) FeeStructure (course, vearsem, fee) Payment (billno, admno, amount, pdate, purpose) Create the above tables with the given specifications and constraints, Insert about 10 rows as are appropriate to solve the following queries. Get full detail ofall students who took admission this year class wise Get detail of students who took admission in Bhilai colleges, Caleulate the total amount of fees collected in this session i) By your college ii) by each college iii) by all colleges a. List the students who have not payed fill fee 1) in your college ii) in all colleges b. List the number of admissions in your class in every year. ¢. List the students in the session who are not in the colleges in the same city as they live paese in 4. List the students in colleges in your city and also live in your city. 3. Create the following database, Subjects ( paperid, subject, paper, papermame) Test (paperid, date, time, max, min) Score (follno, paperid, marks, attendence) Students (admno, rolino, class, yearsem) Create the above tables with the given specifications and constraints. Insert about 10 rows as are appropriate to solve the following queries List the students who were present in a paper of a subject. , List all roll numbers who have passed in frst division, List all students in BCA-II who have scored higher than average i)in your college i) in every college g. List the highest score, average and minimum score in BCA-II i) in your college i) in every college meane 4, Using the following database Colleges (cname, city, address, phone, afdate) Stafis ( sid, sname, saddress, contacts) StafBloins (sid, cname, dept, DOS, post, salary) Teachings ( sid, class, paperid, fsession, tsession) ‘Subjects ( paperid, subject, paperno, papername) Write SQL statements for the following ~ a b ©, a € 5, Create the above tables with the given specifications and constraints Insert about 10 rows as are appropriate to solve the following queries List the names of the teachers teaching computer subjects. List the names and cities of all staff working in your college. List the names and cities ofall staff working in your college who cam more than 15,000 Using the following database A én Me A om 2 LES OE Big Sem Ro arin salt °F z ae =eeoe ermearse Colleges (cname, city, address, phone, afitate) Staff ( sid, sname, contacts) Sufi i rae, dep, DOM, post salay) ‘Teachings (sid, class, paperi, fession,tsssion) Subjects ( paperid, subject, paperno, papername) Find the stafs whose names start with ‘Mot ‘R’ and ends with ‘A’ and/or 7 characters long. Find the staffs. whose date of joining is 2008, Modify the database so that staff N1 now works in C2 college. List the names of subjects which T1 teaches in this session or all sessions. Using the following database Colleges (cname, city, address, phone, afdate) Staffs ( sid, sname, saddress, contacts) Staffloins ( sid, ename, dept, DOJ, post, salary) Teachings ( sid, class, paperid, fsession, tsession) ‘Subjects ( paperid, subject, papemo, papername) Find the classes that T| do not teach at present session. Find the college who have most number of staff F Find the staffS who cam a higher salary who eam greater than average salary of their college. Find the colleges whose average salary is more than average salary of C2 Find the college that has the smallest payroll, Find the colleges where the total salary is greater than the average salary of all colleges. List maximum, average, minimum salary of cach college Using the following database Colleges (cname, city, address, phone, afdate) Staffs (sid, sname, saddress, contacts) Staffloins (sid, coame, dept, DOI, post, salary) Teachings (sid, class, paperid, fSession, tsession) ‘Subjects ( paperid, subject, paperno, papername) Find the classes that TI do not teach at present session. List the names of the teachers, departments teaching in more than one departments. ‘Acquire details of staffs by name in a college or each college, Find the names of staff who earn more than each staff of C2 college. Give all principals a 10% rise in salary unless their salary becomes greater than 20,000 in such case give 5% rise. Find all staff who donot work in same cities as the colleges they work. List names of employees in ascending order according to salary who are working in yout college or all colleges Using the following database Colleges (cname, city, address, phone, afdate) Staffs sid, sname, saddress, contacts) Staffloins (sid, cname, dept, DOJ, post, salary) Teachings sid, class, paperid, Esession, tsession) Subjects paperid, subject, paperno, papemam) Find the lasses that T'! do not teach at present session Create a view having fields sname, cname, dept, DOJ, and post Create a view consisting of ename, average salary and total salary of all sta in that college. Select the colleges having highest and lowest average salary using above views. List the staff names of a department using above views, hye— Bevo Boo HNa paese poe eeese » Enrollment (enrolino, name, gender, DOB, address, phone) Admission (admuo, enrollno, course, yenrsem, date, cname) Colleges (cname, city, address, phone, aftate) FeeStructure (courso, yearsem, fee) Payment (billno, admno, amount, pdate, purpose) Create the above tables with the given specifications and constraints. Insert about 10 rows as are appropriate to solve the following queries. ‘Get full detail of all students who took admission this year classwise Get detail of students who took admission in Bhilai colleges. Caloulate the total amount of fees collected in this session i) by your college ii) by cach college iii) by all colleges Enrollment (enrollno, name, gender, DOB, address, phone) Admission (admno, enrolino, course, yearsem, date, cname) Colleges (cname, city, address, phone, afilate) FeeStructure (course, vearsem, fee) Payment (billno, admno, amount, pdate, purpose) List the students who have not payed full fee i) in your college ii) in al colleges List the number of admissions in your class in every year. oe List the students in the session who are not in the colleges in the same city as they live in. List the students in colleges in your city and also live in your city ‘Subjects ( paperid, subject, paper, papemame) ‘Test (paperid, date, time, max, min) Score (rolino, paperid, marks, attendence) Students (admno, rolino, class, yearsem) Create the above tables with the given specifications and constraints. Tnsert about 10 rows as are appropriate to solve the following queries. List the students who were present in a paper of a subject. List all rol! numbers who have passed in first division. List all students in BCA-II who have scored higher than average i) in yout college ii) in every college List the highest score, average and minimum score in BCA-II i) in your college i) in every college de Oo ye“ Ye Fem — ZEN GN hie. eae AS ¢ uv 9