Exercices corriges SQL S1 14 EN .pdf

Nom original: Exercices-corriges-SQL-S1-14-EN.pdf
Titre: Database - SQL
Auteur: Abdallah EL Asmar

Ce document au format PDF 1.5 a été généré par Microsoft® Office Word 2007, et a été envoyé sur fichier-pdf.fr le 25/01/2014 à 12:01, depuis l'adresse IP 212.28.x.x. La présente page de téléchargement du fichier a été vue 1211 fois.
Taille du document: 353 Ko (6 pages).
Confidentialité: fichier public

Aperçu du document

Exercises SQL

Abdallah EL Asmar

SQL
Exercise 1
Consider the following database:
Building (Building-nb, Building-name, #Street-nb)
Street (Street-nb, Street-name, #City-nb)
City (City-nb, City-name)
N.B:

- No repeated values for the City-name field.
- A street of one city can have the name of another city; for example, in the city
« Tripoli » we can have a street named « Beirut ».

Formulate the following queries in SQL:
a) Display the city names that don’t have any street with the name « Independence »
Select City-name
From City
Where City-nb NOT IN (Select City-nb
From Street
Where Street-name = ‘Independence’);
b) Display the street names that are used at least in two cities.
Select Street-name
From Street
Group by Street-name
Having count (distinct City-nb) &gt;= 2 ;
Another solution:
Select S1. Street-name
From Street S1, Street S2
Where S1.Street-name = S2.Street-name
And S1.City-nb &lt;&gt; S2.City-nb;
c) For each street has a name of a city, display the street name and the name of the city that
the street exists in.
Select Street-name, City-name
From Street S, City C
Where S.City-nb = C.City-nb
And Street-name IN (Select City-name
From City) ;

1

Exercises SQL

Abdallah EL Asmar

Another solution:
Select S.Street-name, C1.City-name
From Street S, City C1, City C2
Where S.City-nb = C1.City-nb
And S.Street-name = C2.City-name;
d) For all “Beirut” city streets that have no repeated name in others cities, display the street
number and the street name.
Select Street-nb, Street-name
From City C, Street S
Where C.City-nb = S.City-nb
And City-name = ‘Beirut’
And Street-name NOT IN (Select Street-name
From City C1, Street S1
Where C1.City-nb = S1.City-nb
And City-name &lt;&gt; ‘Beirut’);
Another solution:
Select Street-nb, Street-name
From City C, Street S
Where C.City-nb = S.City-nb
And City-name = ‘Beirut’
And Street-name IN (Select Street-name
From City
Group by Street-name
Having count (distinct City-nb) = 1);
e) Display the building names that are used at least once in each city.
Select Building-name
From Building B, Street S
Where B.Street-nb = S.Street-nb
Group by Building-name
Having count (distinct City-nb) = ( Select count(*)
From City);

2

Exercises SQL

Abdallah EL Asmar

f) Display the building name(s) that is (are) the most repeated.
Select Building-name
From Building
Group by Building-name
Having count (*) = (Select max (count(*))
From Building
Group by Building-name);
Another solution:
Select Building-name
From Building
Group by Building-name
Having count (*) &gt;= ALL (Select count (*)
From Building
Group by Building-name);

g) Display each couple of city names that have at least one same building name of a same
street name.
Select Distinct C1.City-name, C2.City-name
From City C1, City C2, Street S1, Street S2, Building B1, Building B2
Where C1.City-nb = S1.City-nb
And S1.Street-nb = B1.Street-nb
And C2.City-nb = S2.City-nb
And S2.Street-nb = B2.Street-nb
And B1.Building-name = B2. Building-name
And C1.City-nb &lt;&gt; C2.City-nb;

3

Exercises SQL

Abdallah EL Asmar

Exercice 2
Consider the following schema for an airline database, (underlined attributes are primary keys,
attributes preceded by # are foreign keys):
Flights (flightID, sourceCity, destinationCity)
Departures (#flightID, date, planeType)
Bookings (#pID, #(flightID, date), seatNumber)
N.B. The Bookings table has 2 foreign keys : the first is pID, the second is (flightID, date)
Express the following queries in SQL:
a) List the number of departures for each plane type.
SELECT planeType, count(*) “Number of departures”
FROM Departures
Group by planeType;
b) List the passengers (pName, pAddress) that have, at least, two bookings.
FROM Passengers P, Bookings B
Where P.pID = B.pID
Having count(*) &gt;= 2);
Remark: Two different passengers may have the same values of pName and
pAddress, then we must use pID with Group By in order to assure that each
group concern only one passenger; pName and pAddress don’t have any effect
on the Group By, (for each value of pID correspond only one value of pName and
pAddress) but we must use it with Group By in order to use it with Select.
We can use a nested query:
FROM Passengers
Where pID IN (select pID
from Bookings
Group by pID
Having count(*) &gt;= 2);

4

Exercises SQL

Abdallah EL Asmar

c) List the passengers (pName, pAddress) that have, at least, two bookings in the same date.
FROM Passengers P, Bookings B1, Bookings B2
Where P.pID = B1.pID and B1.pID = B2.pID
and B1.date = B2.date
And B1.flightID &lt;&gt; B2.flightID ;
Another solution:
FROM Passengers
Where pID IN (select pID
from Bookings
Group by pID, date
Having count(*) &gt;= 2);
Remark : Each group concern the bookings of one passenger at one date.
d) Find the cities that have direct (non-stop) flights to both London and NewYork
SELECT DISTINCT sourceCity
FROM Flights
WHERE destinationCity = “London”
AND sourceCity IN (SELECT sourceCity
FROM Flights
WHERE destinationCity = “NewYork”)
Remark: Could also be done with a self join on Flights, or …, just can’t use
a simple selection with “AND” in the Where clause --- this would return
no tuples.
SELECT DISTINCT F1.sourceCity
FROM Flights F1, Flights F2
WHERE F1.sourceCity = F2.sourceCity
AND F1.destinationCity = “London”
AND F2.destinationCity = “NewYork”;

5

Exercises SQL

Abdallah EL Asmar

e) Find the pID of all passengers who have a seat booked on a plane of type “747” from Beirut to
London. Do not return any duplicate values.
SELECT DISTINCT B.pID
FROM Flights F, Departures D, Bookings B
WHERE B.flightID = D.flightID
AND B.date = D.date
AND F.flightID = D.flightID
AND F.sourceCity = “Beirut”
AND F.destinationCity = “London”
AND D.planeType = “747”
Remark: Since key of Departures is flightID and date, you need both of
these to do the join and find out what type of plane the passenger is
booked on.
f)

Find the pName of all passengers who have a seat booked on at least one plane of every type.
SELECT pName
FROM Passengers P, Departures D, Bookings B
WHERE B.flightID = D.flightID
AND B.date = D.date
AND P.pID = B.pID
Group by P.pID, P.pName
Having COUNT(DISTINCT D.planeType) =
(SELECT COUNT (DISTINCT D.planeType)
FROM Departures D)

Good luck
Abdallah ELAsmar

6

Sur le même sujet..

Ce fichier a été mis en ligne par un utilisateur du site. Identifiant unique du document: 00218347.

Pour plus d'informations sur notre politique de lutte contre la diffusion illicite de contenus protégés par droit d'auteur, consultez notre page dédiée.