Interested in a PLAGIARISM-FREE paper based on these particular instructions?...with 100% confidentiality?

Order Now

Questions: 1 (1 mark SQL – other statements in basic SQL) a) Create a view to check the hotel name, room number and the guest names who booked the room; b) Select everything from the view created above; c) Drop the above view; d) Create a view to check the hotel number and hotel names and the number of rooms they have. e) Create a view to see the pair of room types and prices. f) Create a view to see the distinct pair of room types and prices. g) Create an index of room types and prices on the room table; h) Drop the above index; i) Create an index of the guest names; j) Create a new table including hotel information (i.e., hotel names and numbers) from the room table; k) Backup the room table; l) Insert the staff numbers and names as the guest numbers and names into the guest table. If not successful, why? 2. a) Declare a cursor of the room table containing all hotel numbers and hotel names; b) Open the above cursor and use it to generate the hotel numbers and names one by one; c) Release the above cursor; d) Create a stored procedure to update the room prices in hotel Hilton by 6% and in other hotels by 4%. e) Use the above procedure to update the room prices. 3. a) Write an SQL transaction to increase the price of all rooms in hotel Hilton by 5%, and commit this transaction. SQL – transact SQL b) Write an SQL transaction to increase the price of all rooms in hotel Hilton by 5%, but withdraw this transaction. Compare the results with the ones obtained from a). c) Write a nested SQL transaction to first increase the price of all rooms in hotel Hilton by 5% and then give a discount of $100 for all standard rooms. d) Write a nested SQL transaction to first give a discount of $100 for all standard rooms and then increase the price of all rooms in hotel Hilton by 5%. Compare the results with the ones obtained from c). e) Write a nested SQL transaction to first give a discount of $100 for all standard rooms and then increase the price of all rooms in hotel Hilton by 5%, but withdraw the first sub-transaction. Compare the results with the ones obtained from c) and d). 4 a) Grant the privileges to the tutor by executing the following statement [YOUR_DATABASE_NAME] GO CREATE USER [USER_NAME_FOR_YI] FOR LOGIN [30023658] GO EXEC sp_addrolemember N’db_datareader’, N’USER_NAME_FOR_YI’ GO Note: Your database name should be your student id. This MUST be replaced by your own database name. 30023658 is my login name and USER_NAME_FOR_YI is the user name you will create for me. However, due to the restriction that this SQL server does not allow different user names, you need to use a different name for me. In case that there is a conflict, just try to use another name.

Questions:

1 (1 mark SQL – other statements in basic SQL)

a) Create a view to check the hotel name, room number and the guest names who booked the room;

b) Select everything from the view created above;

c) Drop the above view; d) Create a view to check the hotel number and hotel names and the number of rooms they have.

e) Create a view to see the pair of room types and prices.

f) Create a view to see the distinct pair of room types and prices.

g) Create an index of room types and prices on the room table;

h) Drop the above index;

i) Create an index of the guest names;

j) Create a new table including hotel information (i.e., hotel names and numbers) from the room table; k) Backup the room table;

l) Insert the staff numbers and names as the guest numbers and names into the guest table. If not successful, why?

2.

a) Declare a cursor of the room table containing all hotel numbers and hotel names;

b) Open the above cursor and use it to generate the hotel numbers and names one by one;

c) Release the above cursor;

d) Create a stored procedure to update the room prices in hotel Hilton by 6% and in other hotels by 4%. e) Use the above procedure to update the room prices.

3.

a) Write an SQL transaction to increase the price of all rooms in hotel Hilton by 5%, and commit this transaction. SQL – transact SQL

b) Write an SQL transaction to increase the price of all rooms in hotel Hilton by 5%, but withdraw this transaction. Compare the results with the ones obtained from a).

c) Write a nested SQL transaction to first increase the price of all rooms in hotel Hilton by 5% and then give a discount of $100 for all standard rooms.

d) Write a nested SQL transaction to first give a discount of $100 for all standard rooms and then increase the price of all rooms in hotel Hilton by 5%. Compare the results with the ones obtained from c).

e) Write a nested SQL transaction to first give a discount of $100 for all standard rooms and then increase the price of all rooms in hotel Hilton by 5%, but withdraw the first sub-transaction. Compare the results with the ones obtained from c) and d).

4

a) Grant the privileges to the tutor by executing the following statement

[YOUR_DATABASE_NAME] GO CREATE USER [USER_NAME_FOR_YI] FOR LOGIN [30023658] GO EXEC sp_addrolemember N’db_datareader’, N’USER_NAME_FOR_YI’ GO

Note: Your database name should be your student id. This MUST be replaced by your own database name. 30023658 is my login name and USER_NAME_FOR_YI is the user name you will create for me. However, due to the restriction that this SQL server does not allow different user names, you need to use a different name for me. In case that there is a conflict, just try to use another name.

Interested in a PLAGIARISM-FREE paper based on these particular instructions?...with 100% confidentiality?

Order Now