Test 1 BD
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:
1. UPPER
2. LOWER
3. TRIM, LTRIM, RTRIM
4. RPAD, LPAD
5. LENGTH
6. SUBSTR
7. CONCAT
8. REPLACE
9. TRANSLATE
10. DECODE
11. NVL
12. TO_DATE
13. TO_CHAR (only with date arguments, such as TO_CHAR (SYSDATE, 'Day, Month, DD, YYYY'))
14. CURRENT_DATE, SYSDATE
15. MONTHS_BETWEEN
16. ADD_MONTHS
17. LAST_DAY
18. NEXT_DAY
19. NULLIF
20. ROUND (only with numeric arguments), TRUNC
21. LIKE
22. CEIL, FLOOR (only with numeric arguments)
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 functionSELECT 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;