Scribd
Upload
13 views2 pages

7 Ex SQL II

This document provides instructions and questions for writing SQL queries against sample databases. It includes 9 questions about a sailors database with tables for sailors, boats, and reservations. It also includes 2 questions about an employees database and asks to write supporting views for each.

Uploaded by

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

7 Ex SQL II

This document provides instructions and questions for writing SQL queries against sample databases. It includes 9 questions about a sailors database with tables for sailors, boats, and reservations. It also includes 2 questions about an employees database and asks to write supporting views for each.

Uploaded by

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

Introduction to Databases Fall-Winter 2008/09

Exercises Michail Kazimianec


Werner Nutt
Andrej Taliun

SQL Queries (II)

You are asked to write SQL queries that answer the questions listed below. Instruc-
tions on how to access the PostgreSQL server can be found on the first exercise
sheet with SQL queries.

Queries Over the Sailors Database


Consider the database of a sailing club with the following three tables:

Sailor(id: integer, name: string, rating: integer, age: integer)

Boat(id: integer, name: string, colour: string)

Reservation(sid: integer, bid: integer, day: date)

A script sailors.sql, which creates a small sailors database, can be found on


the course web site on the page with the exercises.
For each of the following queries in English, write two SQL queries that are
equivalent, but essentially different:

1. Select, for each boat, the sailor who made the highest number of reserva-
tions for that boat.

2. List, for every boat, the number of times it has been reserved, including
those boats that have never been reserved (list the id and the name).

3. List those sailors who have reserved every red boat (list the id and the name).

4. List those sailors who have reserved only red boats.

5. For which boat are there the most reservations?

6. Select all sailors who have never reserved a red boat.

1
7. For each sailor, who has at least ten reservations for red boats, increase the
rating by 1.

8. Create a table
LastReservation(sid: integer, bid: integer, day: date,
sname: string, bname: string)

that contains for every sailor the most recent date when the sailor has made
a reservation and the boat he/she has reserved. As a prefix for the table
name use your surname.

• Write an insert statement that fills the table with the information that
can be inferred from the current state of the database.

9. Find the sailors who have made, for each boat colour, at least one reserva-
tion for a boat with that colour.

Views to Support Queries


Write SQL queries that capture the two English queries over the employees database.
Write for each query a supporting view.

1. Which is the department with the lowest maximal salary?

2. For each job type, find the department that has the highest number of em-
ployees with that type of job.

You might also like