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… Read More The long way round?
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:… Read More Another SQL puzzle
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… Read More Let’s try that again, shall we?
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… Read More Solving an old SQL puzzle
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… Read More Solving a relational query: part 4
Take 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: But what about SQL? As I wrote of relational division when I started the blog: one… Read More Relational division again