Frist Link To The DBMS Lab Programs(Previous Post)
1. Create a database with name “Hotel” 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. Practice other standard SQL commands for creating, modifying, displaying data of tables.
9. List full details of all hotels.
10. List full details of all hotels in London.
11. List the names and addresses of all guests living in London, alphabetically ordered by name.
12. List all double or family rooms with a price below £40.00 per night, in ascending order of price.
13. List the bookings for which no dateTo has been specified.
14. How many hotels are there?
15. What is the average price of a room?
16. What is the total revenue per night from all double rooms?
17. How many different guests have made bookings for August?
18. List the price and type of all rooms at the Grosvenor Hotel.
19. List all guests currently staying at the Grosvenor Hotel.
20. List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room.
21. What is the total income from bookings for the Grosvenor Hotel today?
22. List the rooms that are currently unoccupied at the Grosvenor Hotel.
23. What is the lost income from unoccupied rooms at the Grosvenor Hotel?
24. List the number of rooms in each hotel.
25. List the number of rooms in each hotel in London.
26. What is the average number of bookings for each hotel in August?
27. What is the most commonly booked room type for each hotel in London?
28. What is the lost income from unoccupied rooms at each hotel today?
29. Insert rows into each of these tables.
30. Update the price of all rooms by 5%.
31. Demonstrate that queries written using the UNION operator and same can be rewritten using the OR.
32. Apply the syntax for inserting data into a table.
33. Create a view containing the cheapest hotels in the world.
34. Create the Hotel table using the integrity enhancement features of SQL.
35. Create a database trigger for the following situations:
(a) The price of all double rooms must be greater than £100.
(b) The price of double rooms must be greater than the price of the highest single room.
(c) A booking cannot be for a hotel room that is already booked for any of the specified dates.
(d) A guest cannot make two bookings with overlapping dates.
(e) Maintain an audit table with the names and addresses of all guests who make bookings for hotels in London (do not store duplicate guest details).
Given relation schemas are
Sailors (sid : integer, sname : string, rating : integer, age : real) Boats (bid : integer, bname : string, color : string)
Reserves (sid : integer , bid : integer, day : date)
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. Practice other standard SQL commands for creating, modifying, displaying data of tables.
9. List full details of all hotels.
10. List full details of all hotels in London.
11. List the names and addresses of all guests living in London, alphabetically ordered by name.
12. List all double or family rooms with a price below £40.00 per night, in ascending order of price.
13. List the bookings for which no dateTo has been specified.
14. How many hotels are there?
15. What is the average price of a room?
16. What is the total revenue per night from all double rooms?
17. How many different guests have made bookings for August?
18. List the price and type of all rooms at the Grosvenor Hotel.
19. List all guests currently staying at the Grosvenor Hotel.
20. List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room.
21. What is the total income from bookings for the Grosvenor Hotel today?
22. List the rooms that are currently unoccupied at the Grosvenor Hotel.
23. What is the lost income from unoccupied rooms at the Grosvenor Hotel?
24. List the number of rooms in each hotel.
25. List the number of rooms in each hotel in London.
26. What is the average number of bookings for each hotel in August?
27. What is the most commonly booked room type for each hotel in London?
28. What is the lost income from unoccupied rooms at each hotel today?
29. Insert rows into each of these tables.
30. Update the price of all rooms by 5%.
31. Demonstrate that queries written using the UNION operator and same can be rewritten using the OR.
32. Apply the syntax for inserting data into a table.
33. Create a view containing the cheapest hotels in the world.
34. Create the Hotel table using the integrity enhancement features of SQL.
35. Create a database trigger for the following situations:
(a) The price of all double rooms must be greater than £100.
(b) The price of double rooms must be greater than the price of the highest single room.
(c) A booking cannot be for a hotel room that is already booked for any of the specified dates.
(d) A guest cannot make two bookings with overlapping dates.
(e) Maintain an audit table with the names and addresses of all guests who make bookings for hotels in London (do not store duplicate guest details).
Given relation schemas are
Sailors (sid : integer, sname : string, rating : integer, age : real) Boats (bid : integer, bname : string, color : string)
Reserves (sid : integer , bid : integer, day : date)
36. Find the names and ages of all sailors.
37. Find all sailors with a rating above 7.
38. Find the names of sailors who have reserved boat 103.
39. Find the sids of sailors who have reserved a red boat.
40. Find the names of sailors who have reserved a red boat.
41. Find the colors of boats reserved by Lubber.
42. Find the names of sailors who have reserved at least one boat.
43. Find the names of sailors who have reserved at least two boats.
44. Compute increments for the ratings of persons who have sailed two different boats on the same day.
45. Find the ages of sailors whose name begins and ends with B and has at least three characters.
46. Find the names of sailors who have reserved a red or a green boat.
47. Find the names of sailors who have reserved a red and a green boat.
48. Find the sids of all sailors who have reserved red boats but not green boats.
49. Find all sids of sailors who have a rating of 10 or have reserved boat 104.
50. Find the names of sailors who have not reserved a red boat.
51. Find sailors whose rating is better than some sailor called Horatio.
52. Find sailors whose rating is better than every sailor called Horatio.
53. Find the names of sailors who have reserved all boats.
54. Find the names of sailors who have reserved at least two boats.
55. Find the names of sailors who have reserved all boats called Interlake.
56. Find sailors who have reserved all red boats.
57. Find the sailor name, boat id, and reservation date for each reservation.
58. Find the sids of sailors with age over 20 who have not reserved a red boat.
59. Find the average age of all sailors.
60. Find the average age of sailors with a rating of 10.
61. Find the name and age of the oldest sailor.
62. Count the number of different sailor names.
63. Find the names of sailors who are older than the oldest sailor with a rating of 10.
64. Find the sailors with the highest rating.
65. Find the age of the youngest sailor for each rating level.
66. Find age of the youngest sailor who is eligible to vote for each rating level with at least 2 such sailors.
67. Find the average age of sailors for each rating level that has at least two sailors.
68. For each red boat, find the number of reservations for this boat.
69. Find the average age of sailors who are of voting age (i.e., at least 18 years old) for each rating level that has at least two sailors.
70. Delete the records of sailors who have rating 8 (deleting some rows in a table).
71. Loading data which is present in the text into the table.
Note
Recommended to use open source database software like MySQl, MongoDB, PostgreSQL, etc… In practical examination, students have to
37. Find all sailors with a rating above 7.
38. Find the names of sailors who have reserved boat 103.
39. Find the sids of sailors who have reserved a red boat.
40. Find the names of sailors who have reserved a red boat.
41. Find the colors of boats reserved by Lubber.
42. Find the names of sailors who have reserved at least one boat.
43. Find the names of sailors who have reserved at least two boats.
44. Compute increments for the ratings of persons who have sailed two different boats on the same day.
45. Find the ages of sailors whose name begins and ends with B and has at least three characters.
46. Find the names of sailors who have reserved a red or a green boat.
47. Find the names of sailors who have reserved a red and a green boat.
48. Find the sids of all sailors who have reserved red boats but not green boats.
49. Find all sids of sailors who have a rating of 10 or have reserved boat 104.
50. Find the names of sailors who have not reserved a red boat.
51. Find sailors whose rating is better than some sailor called Horatio.
52. Find sailors whose rating is better than every sailor called Horatio.
53. Find the names of sailors who have reserved all boats.
54. Find the names of sailors who have reserved at least two boats.
55. Find the names of sailors who have reserved all boats called Interlake.
56. Find sailors who have reserved all red boats.
57. Find the sailor name, boat id, and reservation date for each reservation.
58. Find the sids of sailors with age over 20 who have not reserved a red boat.
59. Find the average age of all sailors.
60. Find the average age of sailors with a rating of 10.
61. Find the name and age of the oldest sailor.
62. Count the number of different sailor names.
63. Find the names of sailors who are older than the oldest sailor with a rating of 10.
64. Find the sailors with the highest rating.
65. Find the age of the youngest sailor for each rating level.
66. Find age of the youngest sailor who is eligible to vote for each rating level with at least 2 such sailors.
67. Find the average age of sailors for each rating level that has at least two sailors.
68. For each red boat, find the number of reservations for this boat.
69. Find the average age of sailors who are of voting age (i.e., at least 18 years old) for each rating level that has at least two sailors.
70. Delete the records of sailors who have rating 8 (deleting some rows in a table).
71. Loading data which is present in the text into the table.
Note
Recommended to use open source database software like MySQl, MongoDB, PostgreSQL, etc… In practical examination, students have to
- Create database
- Create tables with their integrity constraints.
- Insert the data into tables and then execute the queries.
- Answer any six queries from ten queries given by the examiner.
For more info visit Degreecsa.blogspot.in
No comments:
Post a Comment