Summary so far • SQL is based on relational algebra. Database Usage – Operations over relations • Operations for: (and Construction) – Selection of rows ( � ) – Projection of columns ( � ) SQL Queries and Relational Algebra – Combining tables Views • Cartesian product (x) • Join, natural join ( ⋈ ⋈ C , ⋈ ⋈ ) ⋈ ⋈ ⋈ ⋈ Subqueries SELECT course, period FROM Lectures • Subqueries is a term referring to a query used WHERE weekday = ’Mon’ inside another query: course period room weekday hour SELECT teacher FROM GivenCourses NATURAL JOIN TDA357 2 room1 Mon 8 (SELECT course, period TDA357 2 room1 Thu 8 FROM Lectures WHERE weekday = ’Mon’) TDA357 4 room3 Tue 8 WHERE period = 2; TDA357 4 room3 Thu 13 • Beware the natural join!! TIN090 1 room4 Mon 8 • ”List all teachers who have lectures on Mondays in period 2” What does this query mean? TIN090 1 room3 Thu 13 • SQL is a language where any query can be written in lots of different ways… SELECT teacher FROM GivenCourses NATURAL JOIN SELECT course, period (SELECT course, period FROM Lectures FROM Lectures WHERE weekday = ’Mon’ WHERE weekday = ’Mon’) WHERE period = 2; course period course period room weekday hour TDA357 2 TDA357 2 room1 Mon 8 TIN090 1 TIN090 1 room4 Mon 8 course period teacher #students TDA357 2 Niklas Broberg 130 TDA357 4 Rogardt Heldal 135 TIN090 1 Devdatt Dubashi 95 1
Result SELECT teacher FROM GivenCourses NATURAL JOIN (SELECT course, period FROM Lectures teacher WHERE weekday = ’Mon’) WHERE period = 2; Niklas Broberg course period teacher #students TDA357 2 Niklas Broberg 130 TIN090 1 Devdatt Dubashi 95 Renaming attributes Renaming relations • Sometimes we want to give new names to • Name the result of a subquery to be able attributes in the result of a query. to refer to the attributes in it. – To better understand what the result models • Alias existing relations (tables) to make are. referring to it simpler, or to disambiguate. – In some cases, to simplify queries SELECT L.course, weekday, hour, room FROM Lectures L, GivenCourses G, Rooms SELECT * WHERE L.course = G.course FROM Courses NATURAL JOIN AND L.period = G.period (SELECT course as code, period, teacher AND room = name FROM GivenCourses); AND nrSeats < nrStudents; List all lectures that are scheduled in rooms with too What does this query mean? few seats. Renaming in Relational Algebra Quiz! • Renaming = Given a relation, give a new name Write a query that lists all courses that are to it, and (possibly) to its attributes given in more than one period, with different teachers. ρ A(X) ( R ) SELECT A.course – Rename R to A, and the attributes of R to the names FROM GivenCourses A, GivenCourses B specified by X (must match the number of attributes). WHERE A.course = B.course – Leaving out X means attribute names stay the same. AND A.teacher <> B.teacher AND A.period <> B.period; – Renaming the relation is only necessary for subqueries. – � = rho = greek letter r = r ename 2
Sequencing • Example: WITH DBLectures AS • Easier to handle subqueries separately when (SELECT room, hour, weekday queries become complicated. FROM Lectures – Example: � X ( R 1 ⋈ ⋈ C R 2 ) could be written as WHERE course = ’TDA357’ ⋈ ⋈ R 3 := R 1 x R 2 AND period = 2) R 4 := � C (R 3 ) SELECT weekday FROM DBLectures R := π X (R 4 ) WHERE room = ’VR’; – In SQL: WITH R 3 AS (SELECT * FROM R 1 , R 2 ), Lists the days when the Databases course has What does this query mean? lectures in room VR during period 2. R 4 AS (SELECT * FROM R 3 WHERE C) SELECT X FROM R 4 ; Creating views Example: • A view is a ”virtual table”, or ”persistent CREATE VIEW DBLectures AS SELECT room, hour, weekday query” – a relation defined in the database FROM Lectures using data contained in other tables. WHERE course = ’TDA357’ AND period = 2; CREATE VIEW viewname AS query SELECT weekday • For purposes of querying, a view works FROM DBLectures just like a table. The main difference is that WHERE room = ’VR’; you can’t perform modifications on it – its contents is defined by other tables. The WHERE clause Testing for membership • Specify conditions over rows . • Test whether or not a tuple is a member of some relation. • Can involve – constants tuple [NOT] IN subquery {or literal set} – attributes in the row – simple value functions (e.g. ABS, UPPER) SELECT course List all courses that – subqueries FROM GivenCourses take place in the first or fourth periods. WHERE period IN (1,4); • Lots of nice tests to make… 3
Quiz! Testing for existence List all courses given by a teacher who also • Test whether or not a relation is empty. gives the Databases course (TDA357). [NOT] EXISTS subquery (You must use IN…) e.g. List all courses that have lectures. SELECT code, name SELECT course FROM Courses FROM GivenCourses WHERE EXISTS WHERE teacher IN (SELECT * (SELECT teacher FROM Lectures FROM GivenCourses WHERE course = code); WHERE course = ’TDA357’); Note that code is in scope here since it is an attribute in the row being tested in the outer ”WHERE” clause. This is called a correlated query. Quiz! Ordinary comparisons List all courses that are not given in the • Normal comparison operators like =, <, <>, second period. (You must use EXISTS…) but also the special BETWEEN. value1 BETWEEN value2 AND value3 SELECT code FROM Courses List all courses that SELECT course WHERE NOT EXISTS take place in the (SELECT * FROM GivenCourses second or third periods. FROM GivenCourses WHERE period BETWEEN 2 AND 3; WHERE course = code – Same thing as AND period = 2); value2 <= value1 AND value1 <= value3 Comparisons with many rows Quiz! • Two operators that let us compare with all List the course(s) with the fewest number of the values in a relation at the same time. students (in any period). (You must use ANY or ALL…) tuple op ANY subquery {or literal set} tuple op ALL subquery {or literal set} SELECT course FROM GivenCourses SELECT course List all courses that WHERE nrStudents <= ALL FROM GivenCourses take place in the first or (SELECT nrStudents fourth periods. WHERE period = ANY (1,4); FROM GivenCourses); 4
String comparisons Quiz! • Normal comparison operators like < use List all courses that have anything to do with lexicographical order. databases (i.e. have the word Database in their name). – ’foo’ < ’fool’ < ’foul’ • Searching for patterns in strings: SELECT * string LIKE pattern FROM Courses WHERE name LIKE ’ % Database % ’; – Two special pattern characters: • _ (underscore) matches any one character. • % matches any (possibly empty) sequence of characters. The NULL symbol Comparing values with NULL • Special symbol NULL means either • The logic of SQL is a three-valued logic – TRUE, FALSE and UNKNOWN. – we have no value, or – we don’t know the value • Comparing any value with NULL results in UNKNOWN. • A row is selected if all the conditions in the • Use with care! WHERE clause are TRUE for that row, i.e. – Comparisons and other operations won’t not FALSE or UNKNOWN . work. – May take up unnecessary space. Three-valued logic Unintuitive result • Rules for logic with unknowns: UNKNOWN SELECT * FROM Rooms – true AND unknown = unknown WHERE nrSeats > 10 – false AND unknown = false UNKNOWN OR nrSeats <= 10; Rooms UNKNOWN – true OR unknown = true name nrSeats – false OR unknown = unknown VR NULL We don’t know – unknown AND/OR unknown = unknown the value 5
Don’t expect the ”usual” results Arithmetic in queries • Laws of three-valued logic are not the • We allow arithmetic operations in queries. same as those for two-valued logic. SELECT weekday, hour, room, course, • Some laws hold, like commutativity of nrSeats – nrStudents as nrFreeSeats FROM Rooms, AND and OR. (Lectures NATURAL JOIN GivenCourses) • Others do not: WHERE name = room; p OR NOT p = true • Not just arithmetic, but rather any operations on values. – Oracle has lots of pre-defined functions. Constants Quiz! • Constants can be used in projections. What will the result of this query be? Courses SELECT code, name, SELECT 1 code name ’Database course’ as comment FROM Courses; FROM Courses TDA357 Databases WHERE name LIKE ’ % Database % ’; TIN090 Algorithms code name comment 1 For each row in Courses that passes the test (all TDA357 Databases Databases course 1 rows since we have no test), project the value 1. 1 – Beware of keywords… Aggregation Quiz! • Aggregation functions are functions that List the room(s) with the highest number of produce a single value over a relation. seats, and its number of seats. – SUM, MAX, MIN, AVG, COUNT… SELECT name, MAX(nrSeats) FROM Rooms; MAX actually has SELECT MAX(nrSeats) Rooms as an implicit FROM Rooms; NOT correct! argument! Error when trying to execute, why is it so? SELECT COUNT(*) FROM Lectures WHERE room = ’VR’; 6
Recommend
More recommend
