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