Scribd
Upload
65 views18 pages

Lab Report No 7 & 9: Introduction To Aggregation, Group By, and Joins, Views and Triggers

This document is a lab report submitted by Farah Amber containing the results of tasks completed for labs 7 and 9 on database concepts. Lab 7 focuses on aggregation, grouping, joins and views while lab 9 covers views and triggers. For each lab, conceptual schemas are defined and tasks are listed involving writing SQL queries to retrieve, filter, group and aggregate data from tables in the database. Triggers are also created to log changes made to tables.

Uploaded by

faraherh amber
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
65 views18 pages

Lab Report No 7 & 9: Introduction To Aggregation, Group By, and Joins, Views and Triggers

This document is a lab report submitted by Farah Amber containing the results of tasks completed for labs 7 and 9 on database concepts. Lab 7 focuses on aggregation, grouping, joins and views while lab 9 covers views and triggers. For each lab, conceptual schemas are defined and tasks are listed involving writing SQL queries to retrieve, filter, group and aggregate data from tables in the database. Triggers are also created to log changes made to tables.

Uploaded by

faraherh amber
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 18

LAB REPORT NO 7 & 9

Introduction to Aggregation,
Group By, and JOINs,
Views and Triggers

Submitted By

Farah Amber
2017-EE-104
SECTION : C

Submitted to:

Sir .Umer Shahid


EE436L: Database Engineering

Date Submitted: 13 Dec ,2020

University of Engineering and Technology , Lahore

2017-EE-104 (Farah Amber)


1|Page
Lab 7
Conceptual schema diagram :

Relational Schema:

2017-EE-104 (Farah Amber)


2|Page
TASK 01 :
List the names and addresses of all guests in London, alphabetically ordered by name.

Task 02:
Display the names of all the hotels along with the number of rooms present in each of
them.

TASK NO 3:
Display the AVG price of each hotel situated in London.

2017-EE-104 (Farah Amber)


3|Page
TASK NO 4:
Display the most expensive double, single and family rooms respectively.

TASK NO 5:
Display hotelname, cityname along with distinct number of room types available in each
of them.

2017-EE-104 (Farah Amber)


4|Page
TASK NO 6:
Display the name and city of the hotel where guests from London are staying. The list
should not contain any hotel twice.

TASK NO 7:
Display the name, city of all the hotels along with the number of reservations it has, in
descending order.

2017-EE-104 (Farah Amber)


5|Page
TASK NO 8:
Display the names of all the guests who have not provided with the end date of their
reservations.

TASK NO 9:
Display the HotelName and RoomNo which was reserved in either year 2003 or 2004,
also display the Guest No of the respective guest.

2017-EE-104 (Farah Amber)


6|Page
TASK NO 10:
Display the name of the hotel and city which has not been reserved.

TASK NO 11:
How many different guests have made bookings till May, 2015?

2017-EE-104 (Farah Amber)


7|Page
TASK NO 12:
What is the total revenue per night from all double rooms?

# insertion to verify the queries

2017-EE-104 (Farah Amber)


8|Page
TASK NO 13:
How many different guests have made bookings for August?

TASK NO 14:
List the price and type of all rooms at the ‘Avari’ Hotel in Lahore.

2017-EE-104 (Farah Amber)


9|Page
TASK NO 15:
List all guests currently staying at the ‘Marriott’ Hotel. (‘system date’ is used to
determine current date)

TASK NO 16:
What is the total income from bookings for the ‘Hotel Inn’ Hotel today?

2017-EE-104 (Farah Amber)


10 | P a g e
TASK NO 17:
List the rooms which are currently unoccupied at the ‘Hotel Inn’ Hotel.

TASK NO 18:
What is the lost income from unoccupied rooms at the ‘Hotel Inn’ Hotel?

2017-EE-104 (Farah Amber)


11 | P a g e
TASK NO 19:
What is the lost income from unoccupied rooms at each hotel today?

TASK NO 20:
For each hotel with more than two different types of rooms, what is the lost income from
unoccupied rooms?

2017-EE-104 (Farah Amber)


12 | P a g e
LAB NO 9
TASK NO 1:
Use views to list the details of all rooms at the Marriott Hotel, including the name of the
guest(s) staying in the room, if the room is occupied and the prices of the rooms.

TASK NO 2:
Use View to List the names and addresses of all guests in London, alphabetically
ordered by name.

2017-EE-104 (Farah Amber)


13 | P a g e
TASK NO 3:
Use View to List the bookings for which no dateTo has been specified. (VIEW3).

TASK NO 4:
Insert a new tuple in VIEW2 by using “INSERT INTO VIEW2” command and check if
new data has updated the original table or not.
ANSWER:
We can not insert without the primary key .if I consider the primary key in view 2 , then
following as shown below:
2017-EE-104 (Farah Amber)
14 | P a g e
If consider the primary key then ,Yes, it updated the original table
TASK NO 5:

2017-EE-104 (Farah Amber)


15 | P a g e
Update the price of all rooms in VIEW1 and check if update operation is performed on original
database or not.

Yes. It updated the original database , as seen from the 3 rd row. price is updated there.
TASK NO 6:

2017-EE-104 (Farah Amber)


16 | P a g e
Create a trigger to audit the changes of the employee table. First, create an employee
table with appropriate attributes. Then, create an employeeAudit table that keeps the
changes in the employee table. Add appropriate attributes in the table.

2017-EE-104 (Farah Amber)


17 | P a g e
2017-EE-104 (Farah Amber)
18 | P a g e

You might also like