Information for test 1 (only for groups E1, E2, E3)

The test will start at 8:30 on Monday for E1, at 8:30 on Friday for E3 and at 10:00 on Friday for E2.

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 NOT include:
• SQL*Plus specific commands
• DDL and DML commands different from SELECT (such as CREATE, INSERT, DELETE, etc.)

For the test, focus on remembering how to use these functions:

Common mistakes to avoid

        1. Use parentheses to get the correct order for the AND, OR and NOT operators. 

-- equivalent with year = 1 OR (year= 3 and scholarship IS NOT NULL)
SELECT * FROM students WHERE year = 1 OR year = 3
and scholarship IS NOT NULL;

vs.

SELECT * FROM students WHERE (year= 1 OR year= 3)
and scholarship IS NOT NULL;


        2. Operator LIKE

-- correct
SELECT * FROM students WHERE lname LIKE '%i%' OR lname LIKE '%p%';

vs. 

-- wrong
SELECT * FROM students WHERE lname LIKE '%i%' OR  LIKE '%p%';  
SELECT * FROM students WHERE lname (LIKE '%i%' OR  LIKE '%p%');  
SELECT * FROM students WHERE lname LIKE ('%i%' OR  '%p%');
SELECT * FROM students WHERE lname LIKE '%i%' OR  '%p%'; 


        3. Double vs single quotes 
        Doubles quotes " " are only used for aliases.
        For operations on strings, we always use single quotes ''.
        

        4. Set operators: UNION, MINUS, INTERSECT 
        (Uniquely) print the student ids for the students who took only grades >= 7.
        

-- correct
SELECT id_stud FROM students NATURAL JOIN grades WHERE value > = 7 
MINUS 
SELECT id_stud FROM students NATURAL JOIN  grades WHERE value < 7;



-- wrong
-- students with at least one grade >= 7
SELECT DISTINCT id_stud FROM students 
NATURAL JOIN grades WHERE value > = 7;

SELECT DISTINCT id_stud FROM students 
NATURAL JOIN grades WHERE value = SOME(7,8,9,10);

SELECT DISTINCT id_stud FROM students 
NATURAL JOIN grades WHERE value IN(7,8,9, 10);
etc.


        5. WHERE 


--correct
SELECT * FROM students WHERE year= 1 AND scholarship IS NOT NULL;


-- syntax error
SELECT * FROM students WHERE year= 1, scholarship IS NOT NULL;


        6. Syntax of an SQL query:

        An sql query without subqueries and aggregations has at most a DISTINCT clause,
        at most a WHERE clause and at most an ORDER BY clause.
        The order between these clauses is fixed, 
        for e.g., we cannot have a WHERE clause before the JOIN(s).


SELECT [DISTINCT | ALL] {* | [col_expr [AS <new_name>]][,...]}
FROM <table_name> [alias] [, ...]
[JOINS]
[WHERE condition]
[ORDER BY exp1 [ASC/DESC] [,exp2...]] 

       
        
        7. TO_CHAR function 


SELECT TO_CHAR(dob, 'yyyy'), TO_CHAR(dob, 'MM'), TO_CHAR(dob, 'DD'), 
TO_CHAR(dob, 'Month'), TO_CHAR(dob, 'Day') FROM students;


        8. Comparison with NULL
			  correct: IS NULL or IS NOT NULL
			  wrong: != NULL or = NULL or == NULL 
        


        9. Arithmetic operators and NULL


-- NULL values remain NULL  
SELECT scholarship + 10 FROM students;



-- to modify the NULL values
SELECT NVL(scholarship, 0) + 10 FROM students;


        10. DECODE. The last field from the DECODE function is the default value
        that is used if no 'if' is true. 


SELECT DECODE(scholarship, 250, scholarship + 100, 350, scholarship + 200, scholarship + 300) FROM students;


        11. NVL cannot be used with different types of arguments 
        (e.g., a numeric value and a string)


-- wrong
SELECT NVL(scholarship, 'String') from students;

	    	
        12. For NATURAL JOIN you cannot use table aliases.


-- wrong ("ORA-25155: column used in NATURAL join cannot have qualifier")
SELECT fname, lname, value, s.id_stud FROM students s NATURAL JOIN grades WHERE fname='Ioana';

        
        13. For other types of JOINs you must always use the aliases if you defined them


-- wrong
SELECT fname, lname, value, students.id_stud FROM students s JOIN grades g ON s.id_stud=g.id_stud;


        14. JOINs. 
        

        14.1 Difference between JOIN and OUTER JOINs. 
        

        14.2 Difference between LEFT and RIGHT JOIN and FULL OUTER JOIN.
        

        14.3 SELF JOIN.
        

        14.4 If you have to join more than two tables, 
        be careful to choose for each connection the right type of join.
        

        14.5 The joins from profs to grades/courses/students is always done 
        by using the didactic table as intermediary.
        

        14.6 NATURAL JOIN
        Natural Join places an equality condition between the columns with the same name. 
        Use with caution! 


-- this won't return any rows
SELECT * from students NATURAL JOIN grades NATURAL JOIN courses;