Tag Archives: Select (SQL)

The long way round?


This chart represents several constituent comp...
Image via Wikipedia

This SQL works for the problem posed below:

SELECT X.name, X.journeys, X.cname
FROM
(SELECT P.name, COUNT(T.trip_no) AS journeys, C.name AS cname
FROM
Passenger P, Trip T, Company C, Pass_in_trip PIT
WHERE
P.ID_psg = PIT.ID_psg AND PIT.trip_no = T.trip_no
AND T.ID_comp = C.ID_comp
AND
NOT EXISTS (SELECT * FROM
Trip, Pass_in_trip
WHERE Pass_in_trip.ID_psg = P.ID_psg AND Pass_in_trip.trip_no = Trip.trip_no
AND Trip.ID_comp <> C.ID_comp)
GROUP BY P.name, C.name
) X WHERE X.journeys =
(SELECT MAX(Y.travels) FROM
(SELECT COUNT(T2.trip_no) AS travels
FROM
Passenger P2, Trip T2, Company C2, Pass_in_trip PIT2
WHERE
P2.ID_psg = PIT2.ID_psg AND PIT2.trip_no = T2.trip_no
AND T2.ID_comp = C2.ID_comp
AND
NOT EXISTS (SELECT * FROM
Trip, Pass_in_trip
WHERE Pass_in_trip.ID_psg = P2.ID_psg AND Pass_in_trip.trip_no = Trip.trip_no
AND Trip.ID_comp <> C2.ID_comp)
GROUP BY P2.name, C2.name) Y)

Though it seems very long winded and I have seen other ways, involving OUTER JOIN suggested on the internet.

Another SQL puzzle


Time And Date
Image by mobology via Flickr

Once more from http://www.sql-ex.ru which seems to have a phase-of-the-moon related availability.

Database is defined as:

Database schema consists of 4 tables:
Company(ID_comp, name)
Trip(trip_no, ID_comp, plane, town_from, town_to, time_out, time_in)
Passenger(ID_psg, name)
Pass_in_trip(trip_no, date, ID_psg, place)
Company table has ID and name of the company, which transports passengers. Trip table has information about trips: trip number, company ID, plane type, departure city, arrival city, departure time, and arrival time. The passenger table has passenger’s ID and passenger’s name. Pass_in_trip table has information about the flights: trip number, departure date (day), passenger’s ID and his place during the flight. We should note that,
– Any trip is being accomplished every day; duration of a flight is less than a calendar-day (24 hours);
– Time and date are considered comparatively one time zone;
– The departure time and the arrival time are given to within a minute;
– There can be the passengers bearing the same names (for example, Joe Bloggs);
– The place during the flight is a number followed by a letter; the number defines the row number, the letter (a – d) – the place in the row (from the left to the right) in the alphabetical order;
– Relationships and restrictions are shown in the data schema.

And the question is:

Among the clients which only use a single company, find the different passengers who have flown more often than others. Result set: passenger name, number of trips, and company name.

So, I have this SQL:

SELECT P.name, COUNT(T.trip_no) AS journeys, C.name
FROM
Passenger P, Trip T, Company C, Pass_in_trip PIT
WHERE
P.ID_psg = PIT.ID_psg AND PIT.trip_no = T.trip_no
AND T.ID_comp = C.ID_comp
AND
NOT EXISTS (SELECT * FROM
Passenger, Trip, Company, Pass_in_trip
WHERE Pass_in_trip.ID_psg = P.ID_psg AND Pass_in_trip.trip_no = Trip.trip_no
AND Trip.ID_comp <> C.ID_comp)
GROUP BY P.name, C.name

Which correctly, as far as I can see, outputs the names of clients who use only one company, the number of trips they have made and the company name.

But how do I limit that to the MAX?

I have tried adding “HAVING MAX(COUNT(T.trip))” but the RDMS objects – telling me I am trying to evaluate something that does not give a Boolean answer as a Boolean.

Let’s try that again, shall we?


This query works for the previously mentioned SQL problem:

SELECT K.BATTLE
FROM
(SELECT S.name AS SHIP, B.name AS BATTLE, C.country AS COUNTRY
FROM
Ships S, Classes C, Battles B, Outcomes O
WHERE
S.class = C.class AND O.battle = B.name AND O.ship = S.name) K CROSS JOIN
(SELECT S.name AS SHIP, B.name AS BATTLE, C.country AS COUNTRY
FROM
Ships S, Classes C, Battles B, Outcomes O
WHERE
S.class = C.class AND O.battle = B.name AND O.ship = S.name) J
WHERE K.COUNTRY = J.COUNTRY AND K.BATTLE = J.BATTLE
GROUP BY K.BATTLE
HAVING COUNT(K.BATTLE) >=9

But it’s still not perfect, because it would record a positive answer if a single ship from 9 countries took part in a battle or two ships from three countries (something that certainly took place in the Pacific).

So I cheated and looked for clues – and this works properly:

SELECT K.BATTLE
FROM
(SELECT S.name AS SHIP, B.name AS BATTLE, C.country AS COUNTRY
FROM
Ships S, Classes C, Battles B, Outcomes O
WHERE
S.class = C.class AND O.battle = B.name AND O.ship = S.name) K
GROUP BY K.BATTLE
HAVING COUNT(K.COUNTRY) >=3

Solving an old SQL puzzle


HMS Belfast
Image by stewartmorris via Flickr

Ages ago I was puzzled by an online SQL test (sadly no longer online, but here’s the text):

DESCRIPTION

The database of naval ships that took part in World War II is under consideration. The database has the following relations:
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)
Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, the class name does not coincide with any ship name in the database.
The Classes relation includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country where the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The Ships relation includes the ship name, its class name, and launch year. The Battles relation covers the name and date of a battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed – OK) is in the Outcomes relation. Note: the Outcomes relation may include the ships not included in the Ships relation.

PROBLEM

Point out the battles in which at least three ships from the same country took part.

Well, I now think I can solve it:

SELECT BETA.name
FROM (SELECT S.name, C.country
FROM Ships S, Classes C WHERE S.class = C.class) ALPHA,
(SELECT B.name, O.ship
FROM Battles B, Outcomes O WHERE B.name = O.battle) BETA
WHERE
ALPHA.name = BETA.ship
GROUP BY BETA.name
HAVING COUNT (
SELECT * FROM
(SELECT S.name, C.country
FROM Ships S, Classes C WHERE S.class = C.class) X,
(SELECT B.name, O.ship
FROM Battles B, Outcomes O WHERE B.name = O.battle) Y
WHERE
X.name = ALPHA.name AND
Y.name = BETA.name AND
X.name = B.ship AND
A.country = ALPHA.country
)> 2

SELECT BETA.BATTLE
FROM (SELECT S.NAME, C.COUNTRY
FROM SHIPS S, CLASSES C WHERE S.CLASS = C.CLASS) ALPHA,
(SELECT B.NAME, O.SHIP
FROM BATTLES B, OUTCOMES O WHERE B.NAME = O.BATTLE) BETA
WHERE
ALPHA.SHIP = BETA.SHIP
GROUP BY BETA.BATTLE
HAVING COUNT (
SELECT * FROM
(SELECT S.NAME, C.COUNTRY
FROM SHIPS S, CLASSES C WHERE S.CLASS = C.CLASS) X,
(SELECT B.NAME, O.SHIP
FROM BATTLES B, OUTCOMES O WHERE B.NAME = O.BATTLE) Y
WHERE
X.BATTLE = ALPHA.BATTLE AND
Y.BATTLE = BETA.BATTLE AND
A.COUNTRY = ALPHA.COUNTRY)
)> 2

Sadly as the original is not online I cannot test this and it is so complex I am far from 100% confident there is no mistake, but….

Solving a relational query: part 4


As Paul Rubin pointed out the SQL in the last post was broken – here’s some that, I think (please do not let it be wrong a second time!), by relying on SQL’s coercion of a tuple to a scalar, would work (Paul proposed an even more ambitions way of doing this in a single join):

SELECT DISTINCT U.NAME, U.EMAIL
FROM USER U, BID B, LOT L
WHERE U.USER_ID = B.BIDDER_ID AND
B.AMOUNT > 100 AND B.LOT_ID = L.LOT_ID
AND (SELECT COUNT(*) FROM
BID, LOT WHERE
BID.LOT_ID = B.LOT_ID AND BID.BIDDER_ID = U.USER_ID
AND BID.LOT_ID = LOT.LOT_ID) > 1

Still no clue as to how to do this with relational algebra though.

Relational division again


Relational divisionTake the same relational database tables as before:

RESTAURANT(RT_ID, NAME, TYPE, LOCATION)
PROXIMITY(RT1_ID, RT2_ID, DISTANCE)
USER(U_ID, NAME, EMAIL)
REVIEW(U_ID, RT_ID, RT_DATE, RATING, COMMENT)

Find the names of the reviewers who reviewed all the restaurants in Earlsfield.

In relational algebra:

\Pi _{NAME} ( USER \Join REVIEW ) \div \Pi _{RT\_ID} ( \sigma _{LOCATION = 'Earlsfield'} (RESTAURANT))

But what about SQL? As I wrote of relational division when I started the blog:

one has to implement a “double not exists” query

In this case that means “find the name of any reviewer for whom there is no Earlsfield restaurant not reviewed by that reviewer”:

SELECT NAME
FROM USER
WHERE NOT EXISTS (
SELECT *
FROM RESTAURANT
WHERE LOCATION=’Earlsfield’ AND
NOT EXISTS (
SELECT *
FROM REVIEW
WHERE REVIEW.U_ID = USER.U_ID AND
REVIEW.RT_ID = RESTAURANT.RT_ID))

At least, I hope so! I am finding this all quite heavy going – and have ordered a book – SQL and Relational Theory: How to Write Accurate SQL Code – in the hope that it will help makes things clearer. Any other recommendations gratefully received.

Update: It does work, amazingly enough.