Welcome to Database Laboratory
Welcome to Database Laboratory
Problem Statement
Identify primary keys and foreign keys for following database. Create tables and execute queries for given statements.
employee(eid,ename,salary)
assignment(projectid,eid)
project(projectid,project_name,manager)
manager(eid,ename)
i) Alter table to add address in employee table.
ii) Display employee name and projects on which they are working/
iii)Display projectid, projectname and their managers.
iv) Create view of employees working on 'Bank Management' project.
v) Print names of employees whose salary is greater than 40000
vi) Update salary of each employee with increase of Rs.2000
2. Identify primary keys and foreign keys for following database. Create tables and execute queries for given statements.
employee(eid, ename, salary)
assignment(projectid,eid)
project(projectid,project_name,manager)
manager(eid,ename)
i)Modify eid to use auto_increment
ii) Display Employees working in both projects 'Bank Management' and 'Content Management'.
iii) Display average salary of organization.
iv) Display employees who do not work on 'Bank Management' Project.
v) Delete employee whose id is 5.
vi) Display employee having highest salary in oraganization.
3. Identify primary keys and foreign keys for following database. Create tables and execute queries for given statements.
supplier(supplierid,sname,saddress)
parts(part_id,part_name,color);
catalog(supplierid,part_id,cost);
1)Find name of supplier who supply ‘green’ parts.
2)find name of suppliers who supply both blue and green parts.
3)Find supplier who supply all parts.
4)Fid total cost of red parts.
5) Find supplier who supply green parts with minimum cost.
6)Update color of part having part_id = 4 and supplier_id = 2.
4. Identify primary keys and foreign keys for following database. Create tables and execute queries for given statements.
emp(eid,ename,street,city);
works(eid,company_name,salary);
company(company_name,city);
manages(eid,manager_id);
1)Update company of employee name = ‘Prashant’ from ‘Infosys’ to ‘TCS’.
2) Display names & cities of all employees who work for 'Infosys'
3) Display names & Street address & of all employees who work in TCS cities and earn more than 20000.
4) Find all employees in database who do not work for 'Infosys'.
5) Find company wise total salary.
6) Find names of all employees who work for 'Accenture'.
5. Design Mongo DB Schema for Restaurant having RestaurantId, Name, Cuisine, Score, Address
Write a MongoDB query to display all the documents in the collection restaurants.
Write a MongoDB query to display all the documents in the collection restaurants with fields Name, Cuisine, Score and exclude Id and Address
Write a MongoDB query to find the restaurants who achieved a score more than 80.
Write a MongoDB query to find the restaurant having highest score.
Write a MongoDB query to find the count of restaurants for each cuisine.
6. Write a PL/SQL procedure using cursor that will merge the data available in newly created table N_RollCall with the data available in the O_RollCall. If the data in the first table already exists in the second table then that data should be skipped.
7. Write a Stored Procedure namely proc_Grade for the categorization of student. If marks scored by students in examination is <=1500 and marks>=990 then student will be placed in distinction category if marks scored are between 989 and900 category is first class, if marks 899 and 825 category is Higher Second Class Write a PL/SQL block for using procedure created with above requirement. Stud_Marks(Rollno,name, total_marks) Result(Roll,Name, Class)
8. Write a database trigger on library table. The System should keep track of the records that are being updated or deleted. The old value of updated or deleted records should be added in Library_Audit Table.
9 Design Mongo DB Schema for Restaurant having RestaurantId, Name, Cuisine, Score, Address
Write a MongoDB query to display all the documents in the collection restaurants.
Write a MongoDB query to display all the documents in the collection restaurants with fields Name, Cuisine, Score and exclude Id and Address
Write a MongoDB query to find the restaurants who achieved a score more than 80.
Write a MongoDB query to find the restaurant having highest score.
Write a MongoDB query to find the count of restaurants for each cuisine.
10. Create Customer(Cid, CustName, City), Product(Pid, ProductName, Qty) and Order(Oid, Cid, Pid, Qty) tables.
Insert data into tables.
Write a trigger to update count of product in Product table when customer successfully paced order for particular product.
Hint: Order will be placed when its entry will be inserted into order table.
11. Design and Develop MongoDB Queries & use aggregation and indexing
Create ‘zipcode’ collection with city,state & population
Insert atleast 10 records with different variations.
Execute following queries.
i)Display records from collection.
ii)Display total population statewise.
iii) Display total population statewise where population > 20000.
iv)Create index on state.
v) Display all index for collection.
12. Design and Develop MongoDB Queries.
Create ‘student’ collection with name,class,age,grade.
Insert atleast 10 records with different variations.
Execute following queries.
i)Display records from collection.
ii)Display students from S.Y. class only.
iii) Display S.Y. Student having distinction using $and.
iv) Display Students having distinction from S.Y. or T.Y. or Final Year using $and , $or
v) Display students having age atleast 30 and more.
13. Design and Develop MongoDB Queries & use aggregation and indexing
Create ‘zipcode’ collection with city,state & population
Insert atleast 10 records with different variations.
Execute following queries.
i)Display records from collection.
ii)Display total population statewise.
iii) Display total population statewise where population > 20000.
iv)Display average populations for cities in each state.
v) Display smallest and largest cities by population for each state.
14. Create Customer(Cid, CustName, City), Product(Pid, ProductName, Qty) and Order(Oid, Cid, Pid, Qty) tables.
Insert data into tables.
Write a trigger to update count of product in Product table when customer successfully paced order for particular product.
Hint: Order will be placed when its entry will be inserted into order table.
15. Write a database trigger on library table. The System should keep track of the records that are being updated or deleted. old value of updated or deleted records should be added in Library_Audit Table.
16. Identify primary keys and foreign keys for following database. Create tables and execute queries for given statements.
employee(eid,ename,salary)
assignment(projectid,eid)
project(projectid,project_name,manager)
manager(eid,ename)
i)Modify eid to use auto_increment
ii) Display Employees working in both projects 'Bank Management' and 'Content Management'.
iii) Display average salary of organization.
iv) Display employees who do not work on 'Bank Management' Project.
v) Delete employee whose id is 5.
vi) Display employee having highest salary in oraganization.
17. Identify primary keys and foreign keys for following database. Create tables and execute queries for given statements.
supplier(supplierid,sname,saddress)
parts(part_id,part_name,color);
catalog(supplierid,part_id,cost);
1)Find name of supplier who supply ‘green’ parts.
2)find name of suppliers who supply both blue and green parts.
3)Find supplier who supply all parts.
4)Fid total cost of red parts.
5) Find supplier who supply green parts with minimum cost.
6)Update color of part having part_id = 4 and supplier_id = 2.
18. Design and Develop MongoDB Queries
Create ‘users’ collection with name,age and status.
Insert atleast 10 records with different variations.
Execute following queries.
i)Display first five records from collection.
ii)Update status as “rejected” if age is less than 18.
iii)Delete the record of user whose name is ‘Akshata’l
iv)Delete records having age greater than 50
v)Display users having age less than 40 and status = ‘paid’.
19. Write a Procedure code using cursor that will merge the data available in newly created table N_RollCall with the data available in the O_RollCall. If the data in the first table already exists in the second table then that data should be skipped.
20. Design and Develop MongoDB Queries & use aggregation and indexing
Create ‘zipcode’ collection with city,state & population
Insert atleast 10 records with different variations.
Execute following queries.
i)Display records from collection.
ii)Display total population statewise.
iii) Display total population statewise where population > 20000.
iv)Display average populations for cities in each state.
v) Display smallest and largest population for each state.cities by
21 Write a Procedure code using cursor that will create the list of customer’s name from Customer table.
22. Create Customer(Cid, CustName, City), Product(Pid, ProductName, Qty) and Order(Oid, Cid, Pid, Qty) tables.
Insert data into tables.
Write a trigger to update count of product in Product table when customer successfully paced order for particular product.
Hint: Order will be placed when its entry will be inserted into order table.
23. Design Mongo DB Schema for Restaurant having RestaurantId, Name, Cuisine, Score, Address
Write a MongoDB query to display all the documents in the collection restaurants.
Write a MongoDB query to display all the documents in the collection restaurants with fields Name, Cuisine, Score and exclude Id and Address
Write a MongoDB query to find the restaurants who achieved a score more than 80.
Write a MongoDB query to find the restaurant having highest score.
Write a MongoDB query to find the count of restaurants for each cuisine.