DBMS Lab-1 (BS406)
Consider the relational schema for part of the Dream Home case study is:
Branch (branchNo, street, city, postcode)
Staff (staffNo, fName, IName, position, sex, DOB, salary, branchNo)
Property For Rent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)
Client (clientNo, fName, IName, telNo, prefType, maxRent, eMail)
Private Owner (ownerNo, fName, IName, address, telNo, eMail, password)
Viewing (clientNo, propertyNo, viewDate, comment)
Registration (clientNo, branchNo, staffNo, dateJoined)
1. Create a database with name “DreamHome” and now create all the tables listed above with constraints.
2. Insert a new row into the table supplying data for all columns.
3. Modify data in the database using UPDATE
4. Delete data from the database using DELETE
5. Changing a table definition using ALTER
6. Removing a table using DROP
7. Removing rows in table using TRUNCATE
8. Create an index and removing an index
9. Practice other standard SQL commands for creating, modifying, displaying data of tables.
10. List full details of all staff.
11. List all staff with a salary greater than £10000.
12. List the property numbers of all properties that have been viewed.
13. Produce a list of salaries for all staff, showing only the staffNo, fName, IName, and salary details.
14. List all cities where there is either a branch office or a property for rent.
15. List all cities where there is a branch office but no properties for rent.
16. List all cities where there is both a branch office and at least one property for rent.
17. List the names and comments of all clients who have viewed a property for rent.
18. Produce a status report on property viewings.
19. List complete details of all staff who work at the branch in Glasgow.
20. List the addresses of all branch offices in London or Glasgow
21. List all staff with a salary between £20,000 and £30,000.
22. Identify all clients who have viewed all properties with three rooms.
23. How many properties cost more than £350 per month to rent?
24. How many different properties were viewed in May 2013?
25. Find the total number of Managers and the sum of their salaries.
26. Find the minimum, maximum, and average staff salary.
27. Find the number of staff working in each branch and the sum of their salaries.
28. List all managers and supervisors.
29. Find all owners with the string ‘Glasgow’ in their address.
30. List the details of all viewings on property PG4 where a comment has not been supplied.
31. Produce a list of salaries for all staff, arranged in descending order of salary.
32. Produce an abbreviated list of properties arranged in order of property type.
33. Find the number of staff working in each branch and the sum of their salaries.
34. For each branch office with more than one member of staff, find the number of staff working in each branch and the sum of their salaries.
35. List the staff who work in the branch at ‘163 Main St’.
36. List all staff whose salary is greater than the average salary, and show by how much their salary is greater than the average.
37. List the properties that are handled by staff who work in the branch at ‘163 Main St’.
38. Find all staff whose salary is larger than the salary of at least one member of staff at branch B003.
39. Find all staff whose salary is larger than the salary of every member of staff at branch B003
40. List the names of all clients who have viewed a property, along with any comments supplied.
41. For each branch office, list the staff numbers and names of staff who manage properties and the properties that they manage.
42. For each branch, list the staff numbers and names of staff who manage properties, including the city in which the branch is located and the properties that the staff manage.
43. Find the number of properties handled by each staff member, along with the branch number of the member of staff.
44. List all branch offices and any properties that are in the same city.
45. List all properties and any branch offices that are in the same city.
46. List the branch offices and properties that are in the same city along with any unmatched branches or properties.
47. Find all staff who work in a London branch office.
48. Construct a list of all cities where there is either a branch office or a property.
49. Construct a list of all cities where there is both a branch office and a property.
50. Create a view so that the manager at branch B003 can see the details only for staff who work in his or her branch office.
51. Create a view of the staff details at branch B003 that excludes salary information, so that only managers can access the salary details for staff who work at their branch.
52. Create a view of staff who manage properties for rent, which includes the branch number they work at, their staff number, and the number of properties they manage.
53. Removing a view using DROP VIEW
54. Give the user with authorization identifier Manager all privileges on the Staff table.
55. Give users Personnel and Director the privileges SELECT and UPDATE on column salary of the Staff table.
56. Revoke the privilege SELECT on the Branch table from all users.
57. Revoke all privileges you have given to Director on the Staff table.
58. Demonstrate exceptions in PL/SQL
59. Demonstrate cursors in PL/SQL
60. Write PL/SQL queries to create procedures.
61. Write PL/SQL queries to create functions.
62. Write PL/SQL queries to create package.
63. Write PL/SQL queries to create triggers.
64. Write PL/SQL queries using recursion.
Consider the relational schema for part of the Hotel case study is: Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress)
Countinue to remaining questions click here
For more info visit Degreecsa.blogspot.in
No comments:
Post a Comment