Test 2 BD
Information for test 2 in week 11 (only for groups E1, E2, E3)
- E1: 12-13 in C309
- E2, E3: 13-14 in C309
You will receive a drawing with the database schema (tables, fields, types of the fields) so you don't have to memorize it.
The test will target labs 6 to 10 inclusively. Elements from the first labs such as JOINS, filterings, orderings etc. will still be used.
-
The test will NOT include:
- SQL*Plus specific commands
- substitution variables
- DDL statements
- transactions
Common mistakes to avoid
1. The order of the clauses is always like this: JOINS, WHERE, GROUP BY (+ HAVING), ORDER BY-- incorrect SELECT SUM(s.scholarship) FROM students s GROUP BY s.groupno WHERE s.year = 2;
-- correct SELECT SUM(s.scholarship) FROM students s WHERE s.year = 2 GROUP BY s.groupno;
2. WHERE vs HAVING. WHERE filters rows, HAVING filters groups. We cannot use WHERE with an aggregation function-- incorrect SELECT s.id_stud FROM students s join grades g on s.id_stud=g.id_stud WHERE AVG(value) > 7;
-- still incorrect SELECT s.id_stud FROM students s join grades g on s.id_stud=g.id_stud WHERE AVG(value) > 7 GROUP BY s.id_stud;
-- correct SELECT s.id_stud FROM students s join grades g on s.id_stud=g.id_stud GROUP BY s.id_stud HAVING AVG(value) > 7;
3. DISTINCT keyword can be used with aggregation functions What is the difference between the two sql queries below?SELECT COUNT(DISTINCT scholarship) AS "# scholarship types" FROM students;
SELECT DISTINCT COUNT(scholarship) AS "# scholarship types" FROM students;
4. If some unaggregated fields appear in the SELECT clause, in a HAVING subinterrogation or in an ORDER BY clause and do not appear in the GROUP BY clause, the query will not function because it doesn't know how to aggregate those fields.-- incorrect SELECT s.lname, s.fname, AVG(g.value) FROM students s join grades g on s.id_stud=g.id_stud GROUP BY s.id_stud;
-- incorrect SELECT s.lname, s.fname, AVG(g.value) FROM students s JOIN grades g ON s.id_stud = g.id_stud GROUP BY s.lname, s.fname, s.id_stud HAVING AVG(g.value) > (SELECT MIN(AVG(g2.value)) FROM students s2 JOIN grades g2 ON s2.id_stud = g2.id_stud WHERE s.id_stud != s2.id_stud and s.year = s2.year GROUP BY s2.id_stud);
We have 3 options to resolve the error that will appear:
- a. We add these fields in the GROUP BY clause
- b. We use an aggregation function over each field (e.g., year becomes SUM(year))
- c. We eliminate those fields if we don't need them
-- correct (version a.)
SELECT s.lname, s.fname, AVG(g.value) FROM students s join grades g on s.id_stud=g.id_stud
GROUP BY s.id_stud, s.lname, s.fname;
-- correct (version a.)
SELECT s.lname, s.fname, AVG(g.value) FROM students s JOIN grades g ON s.id_stud = g.id_stud
GROUP BY s.lname, s.fname, s.id_stud, s.year
HAVING AVG(g.value) >
(SELECT MIN(AVG(g2.value)) FROM students s2 JOIN grades g2 ON s2.id_stud = g2.id_stud
WHERE s.id_stud != s2.id_stud and s.year = s2.year GROUP BY s2.id_stud);
5. Almost all exercises can be solved using subinterrogations in the
WHERE and/or HAVING clauses. One exception will be when making rankings
(there we need to use an subinterrogation in the FROM clause).
Try to avoid using subinterrogations in the SELECT, JOIN, GROUP BY, ORDER BY clauses
as they usually overcomplicate things.
6. To make rankings we use ROWNUM. In the subinterrogation we need to ORDER the data and in the bigger interrogation(s) we can filter using ROWNUM. Be careful to use the correct comparison symbol when using ROWNUM.
--- correct
SELECT * FROM
(SELECT * FROM students ORDER BY lname, fname)
WHERE ROWNUM <= 4;
--- this doesn't select any rows
SELECT * FROM
(SELECT * FROM students ORDER BY lname, fname)
WHERE ROWNUM > 4;
--- if we want to take a range (version 1)
SELECT * FROM
(SELECT * FROM students ORDER BY lname, fname)
WHERE ROWNUM <= 10
MINUS
SELECT * FROM
(SELECT * FROM students ORDER BY lname, fname)
WHERE ROWNUM < 5;
--- if we want to take a range (version 2)
SELECT lname, fname FROM(
SELECT lname, fname, ROWNUM as rn FROM
(SELECT lname, fname FROM students ORDER BY lname, fname)
WHERE ROWNUM <= 10 ORDER BY lname, fname DESC
) WHERE rn >= 5;
7. We cannot use classical comparison operators (<, =, <=, >, <>, >) for a subinterrogation returning more than one row.
--- wrong
SELECT fname, lname
FROM students
WHERE id_stud =
(SELECT id_stud
FROM grades
WHERE value=10)
ORDER BY lname, fname;
--- correct
SELECT fname, lname
FROM students
WHERE id_stud IN
(SELECT id_stud
FROM grades
WHERE value=10)
ORDER BY lname, fname;
8. When using uncorrelated queries comparing multiple fields, the number of fields in the outer interrogation
and in the subinterrogation must be the same and the order must be the same.
-- correct
SELECT DISTINCT year, groupno, fname, lname, value
FROM students s, grades n
WHERE s.id_stud=n.id_stud and (groupno,year,value) IN
(SELECT groupno,year, MAX(value)
FROM students s, grades n
WHERE s.id_stud=n.id_stud
GROUP BY groupno, year)
ORDER BY year, groupno;
9. DML operations: insert, update, delete