Tag: SQL
-
Distribution, commutation and association in database queries
Another reminder note. The RDBMS query engine relies on these laws to build efficient queries: Commutation: Numbers may travel (commute) or in relational algebra Association: Numbers may freely associate or in relational algebra Distribution: Operators can be distributed or, in relational algebra A more general statement of these laws: Commutative law: Associative law: Distributive law:…
-
Inconsistent analysis (unrepeatable read)
I did warn that this blog, might be used to remind myself of things: and this is one. Inconsistent analysis is a problem with databases when an aggregate function is used on data that is being updated by another transaction eg T1 T2 =SUM(accounts(4 .. 7)) account(1)…
-
The long way round?
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…
-
Another SQL puzzle
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:…
-
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…
-
Solving an old SQL puzzle
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…
-
Solving a relational query: part 5
Found: the relational algebra to tackle the problem outlined in part 3. This is based on a solution to a similar problem found in Database Management Systems, Third Edition, though, unfortunately, that book prints a solution that is not relational algebra at all, as it relies on the order in which results are returned (relational…
-
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…
-
Solving a relational query: part 3
This is starting to depress me, now, because I thought I was full on top of all this material until I tried this question. So, going back to the database specified in part 1 – I now need to find the relational algebra (not the SQL, I have done that) for: “find the name and…
-
Solving a relational query: part 2
With thanks to Professor Paul A. Rubin, I think this may be the way to solve this: This joins LOT to a relation with one attribute (LOT_ID) made up from tuples where LOT_ID should only be returned where no bids have been made that are equal or greater than the reserve price. Very difficult part…
cartesian product ... stuff about computing mostly 