SQL Queries and Data Modeling

Aggregate Functions

How many hotels are there?

SELECT COUNT(*) FROM Hotel;

What is the average price of a room?

SELECT AVG(price) FROM Room;

What is the total revenue per night from all double rooms?

SELECT SUM(price) FROM Room WHERE type = 'D';

How many different guests have made bookings for August?

SELECT COUNT(DISTINCT guestNo) FROM Booking WHERE (dateFrom <= DATE'2004-08-01' AND dateTo >= DATE'2004-08-01') OR (dateFrom >= DATE'2004-08-01' AND dateFrom <= DATE'2004-08-31');

Subqueries and Joins

List the price and type of all rooms at the Grosvenor Hotel.

SELECT price, type FROM Room WHERE hotelNo = (SELECT hotelNo FROM Hotel WHERE hotelName = 'Grosvenor Hotel');

List all guests currently staying at the Grosvenor Hotel.

SELECT * FROM Guest WHERE guestNo = (SELECT guestNo FROM Booking WHERE dateFrom <= CURRENT_DATE AND dateTo >= CURRENT_DATE AND hotelNo = (SELECT hotelNo FROM Hotel WHERE hotelName = 'Grosvenor Hotel'));

List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied.

SELECT r.* FROM Room r LEFT JOIN (SELECT g.guestName, h.hotelNo, b.roomNo FROM Guest g, Booking b, Hotel h WHERE g.guestNo = b.guestNo AND b.hotelNo = h.hotelNo AND hotelName= 'Grosvenor Hotel' AND dateFrom <= CURRENT_DATE AND dateTo >= CURRENT_DATE) AS XXX ON r.hotelNo = XXX.hotelNo AND r.roomNo = XXX.roomNo;

List the number of rooms in each hotel.

SELECT hotelNo, COUNT(roomNo) AS count FROM Room GROUP BY hotelNo;

List the number of rooms in each hotel in London.

SELECT hotelNo, COUNT(roomNo) AS count FROM Room r, Hotel h WHERE r.hotelNo = h.hotelNo AND city = 'London' GROUP BY hotelNo;

SQL Queries

List full details of all hotels.

SELECT * FROM Hotel;

List full details of all hotels in London.

SELECT * FROM Hotel WHERE city = 'London';

List the names and addresses of all guests in London, alphabetically ordered by name.

SELECT guestName, guestAddress FROM Guest WHERE address LIKE '%London%' ORDER BY guestName;

Strictly speaking, this would also find rows with an address like: '10 London Avenue, New York'.

List all double or family rooms with a price below £40.00 per night, in ascending order of price.

SELECT * FROM Room WHERE price < 40 AND type IN ('D', 'F') ORDER BY price;

(Note, ASC is the default setting).

List the bookings for which no dateTo has been specified.

SELECT * FROM Booking WHERE dateTo IS NULL;

Database Management

Three distinct levels that data items can be Described:

External, 2- conceptual, and 3- internal level

Two kinds of data independence: logical and physical.

Physical Data Independence

– Refers to immunity of conceptual schema to changes in the internal schema.

– Internal schema changes (e.g. using different file organizations, storage structures/devices).

– Should not require change to conceptual or external schemas.

Logical Data Independence

– Refers to immunity of external schemas to changes in conceptual schema.

– Conceptual schema changes (e.g. addition/removal of entities).

– Should not require changes to external schema or rewrites of application programs.

Objective of three levels:

All users should be able to access same data.

◆Users should not need to know physical database storage details.

◆A user’s view is immune to changes made in other views.

◆DBA should be able to change conceptual structure of database without affecting all users.

◆DBA should be able to change database storage structures without affecting the users’ views

◆Internal structure of database should be unaffected by changes to physical aspects of storage.

Data Definition Language (DDL)

– Allows the DBA or user to describe and name entities, attributes, and relationships

required for the application

– plus any associated integrity and security constraints.

Data Manipulation Language (DML)

– Provides basic data manipulation operations on data held in the database.

Procedural DML

– allows user to tell system exactly how to manipulate data.

Non-Procedural DML

– allows user to state what data is needed rather than how it is to be retrieved.

 Data Model comprises:

a structural part: consisting of a set of rules according to which databases can be constructed

a manipulative part: defining the types of operation that are allowed on the data

possibly a set of integrity rules: ensures data is accurate.

Purpose

To represent data in an understandable way.

◆Categories of data models include: – Object-based –Record-based – Physical.

Functions of a DBMS

◆Data Storage, Retrieval, and Update. ◆A User-Accessible Catalog(metadata) ◆Transaction Support (a series of changes by single user; ensure all are made or none are made) ◆Concurrency Control Services (ensure database update correctly when multiple user are updating concurrently). ◆Recovery Services (recovering damaged database). ◆Authorization Services (only authorized user can access the database). ◆Support for Data Communication (allow user to access database over network)

◆Integrity Services (ensure the consistency and correctness of the data by setting some constrain).

* Relational Model Terminology

◆A relation is a table with columns and rows.

– Has specific characteristics

– Only applies to logical structure of the database, not the physical structure.

◆Relational Database is a collection of normalized relations with distinct relation names.

◆Attribute is a named column of a relation. ◆Degreeis the number of attributes in a relation. ◆Domain is the set of allowable values for one or more attributes.◆Tuple is a row of a relation. ◆Cardinality is the number of tuples in a relation.

* Purpose of Views

◆Provides powerful and flexible security

mechanism by hiding parts of database from certain users. ◆Permits users to access data in a customized way, so that same data can be seen by different users in different ways, at same time. ◆Can simplify complex operations on base relations.

---A superkey is an attribute, or set of attributes, that identifies tuples of a relation uniquely, ◆A candidate key is a minimal superkey. ◆A primary key is the candidate key chosen for use in identification of tuples. A relation must always have a primary key. ◆A foreign key is an attribute, or set of attributes, within one relation that is the candidate key of another relation.

SQL is a transform-oriented language with 2 major components:

– A DDL for defining database structure. – A DML for retrieving and updating data.

Example: 1)CREATE TABLE Staff(staffNoVARCHAR(5), lName VARCHAR(15), salary DECIMAL(7,2));

2) INSERT INTO Staff VALUES ('SG16', 'Brown', 8300); 3) SELECT staffNo, lName, salary FROM Staff WHERE salary > 10000;----------

***SQL statement consists of reserved words and user defined words.– Reserved words are a fixed part of SQL and must bespelt exactly as required and cannot be split acrosslines.– User-defined words are made up by user andrepresent names of various database objectssuch asrelations, columns, views.

*** Upper-case letters represent reserved words.      -Lower-case letters represent user-definedwords.             - | indicates a choice among alternatives.

- Curly braces indicate a required element.        -Square brackets indicate an optional element.            -… indicates optional repetition (0 or more).

*** FROM Specifies table(s) to be used.            WHEREFilters rows.        GROUP BY Forms groups of rows with same column value.

HAVING Filters groups subject to some condition.          SELECT Specifies which columns are to appear in output.           ORDER BY Specifies the order of the output.

***COUNT returns number of values in specified column.      SUM returns sum of values in specified column.      AVG returns average of values in specified column.                  MIN returns smallest value in specified column.                      MAX returns largest value in specified column.