| 
| 
Table Name: DIRECTOR
| DIRECTOR_ID | DIRECTOR_FNAME | DIRECTOR_LNAME | DATE_LAST_MOVIE_RELEASED | EMAIL_ADDRESS |
| DR0010 | John | Scott | 10-JUN-97 | scottjaemail.com |
| DR0011 | Ralph | Mckinse | 05-JAN-03 | mckra07alsp.com |
| DR0012 | Adrian | McGraw | 03-AUG-00 | mcgawaemail.com |
| DR0013 | Nicole | Drew | 10-JAN-03 | |
| DR0014 | Rupert | Connery | 06-DEC-02 | rconnajade.com |
| DR0015 | Sidney | Shewmaker | 15-AUG-00 | sidneyshewashew.com |
| DR0016 | William | Tipper | 03-AUG-00 | tipperarodeo.com |
| DR0017 | Geoffrey | Greene | 16-NOV-02 | geogreenashell.com |
| DR0018 | Mario | Lopez | 12-DEC-02 | lopezmaramails.com |
In the queries shown below identify the errors:
2. SELECT DIRECTOR_LNAME DIRECTOR LAST NAME FROM DIRECTOR;
3. SELECT DIRECTOR_LNAME 'DIRECTOR LAST NAME' FROM DIRECTOR;
4. SELECT DIRECTOR_LNAME DIRECTOR_FNAME FROM DIRECTOR;
5. SELECT DIRECTOR_LNAME FROM DIRECTORS;
6. SELECT DIRECTOR_ID, EMAIL_ADDRESS
FROM DIRECTOR
WHERE EMAIL_ADDRESS=NULL;
7. SELECT DIRECTOR_LNAME FROM DIRECTOR
WHERE DIRECTOR_LNAME LIKE S%;
8. SELECT DIRECTOR_LNAME FROM DIRECTOR
WHERE DATE_LAST_MOVIE_RELEASED='12-12-02';
9. SELECT DIRECTOR_LNAME FROM DIRECTOR
WHERE DIRECTOR_FNAME=Mario;
10.SELECT * FROM DIRECTOR
GROUP BY SUBSTR(DATE_LAST_MOVIE_RELEASED,8,2);
11. SELECT COUNT(*) FROM DIRECTOR
GROUP BY SUBSTR(DATE_LAST_MOVIE_RELEASED,8,2)
WHERE SUBSTR(DATE_LAST_MOVIE_RELEASED,8,2)=03;
12. SELECT COUNT(*) FROM DIRECTOR
GROUP BY SUBSTR(DATE_LAST_MOVIE_RELEASED,8,2)
HAVING SUBSTR(DATE_LAST_MOVIE_RELEASED,8,2)=03;
What is the output of following queries:
13. SELECT DIRECTOR_FNAME, DIRECTOR_LNAME
FROM DIRECTOR
ORDER BY 2;
14. SELECT COUNT(*) FROM DIRECTOR;
15. SELECT LENGTH(DIRECTOR_FNAME)
FROM DIRECTOR
WHERE DIRECTOR_LNAME='LOPEZ';
16. SELECT TRUNC(DATE_LAST_MOVIE_RELEASED,'YEAR')
FROM DIRECTOR
WHERE DIRECTOR_FNAME='Sidney';
17. SELECT DIRECTOR_FNAME
FROM DIRECTOR
WHERE TO_CHAR(DATE_LAST_MOVIE_RELEASED,'YY')=(SELECT TO_CHAR(DATE_LAST_MOVIE_RELEASED,'YY') FROM DIRECTOR WHERE EMAIL='rconnajade.com');
18. SELECT EMAIL_ADDRESS
FROM DIRECTOR
WHERE DIRECTOR_FNAME='Geoffrey'
UNION
SELECT EMAIL_ADDRESS
FROM DIRECTOR
WHERE EMAIL_ADDRESS IS NULL;
19. SELECT * FROM DIRECTOR
ORDER BY SUBSTR(DATE_LAST_MOVIE_RELEASED,8,2) DESC;
20.SELECT * FROM DIRECTOR
WHERE LENGTH(DIRECTOR_FNAME) BETWEEN 3 AND 6;
Solutions to SQL Quiz
1. A column name and asterisk cannot appear together.
2. The column alias DIRECTOR LAST NAME must be enclosed in double quotes.
3. The column alias 'DIRECTOR LAST NAME' must be enclosed in double quotes.
4. If you were retrieving two separate columns DIRECTOR_LNAME and DIRECTOR_FNAME they should have been separated by a comma. In the query, DIRECTOR_FNAME will become the alias for DIRECTOR_LNAME.
5. The name of the table is incorrect.
6. When comparing for a null value the IS operator should be used:
WHERE EMAIL_ADDRESS IS NULL;
7. The pattern used in the LIKE must be enclosed in single quotes. The correct statement is:
SELECT DIRECTOR_LNAME FROM DIRECTOR
WHERE DIRECTOR_LNAME LIKE 'S%';
8. The date format used is incorrect. Dates are format sensitive and the default date format is 'DD-MON-YY'.The correct statement is:
SELECT DIRECTOR_LNAME FROM DIRECTOR
WHERE DATE_LAST_MOVIE_RELEASED='12-DEC-02';
9. The value used in comparison is a character string, and must be enclosed in single quotes.The correct statement is :
SELECT DIRECTOR_LNAME FROM DIRECTOR
WHERE DIRECTOR_FNAME='Mario';
10.The column list (*) cannot be used when using a GROUP BY clause, and there are many columns in the table.
SELECT * FROM DIRECTOR
GROUP BY SUBSTR(DATE_LAST_MOVIE_RELEASED,8,2);
11. A HAVING clause cannot appear without a GROUP BY clause.
12. The HAVING clause must reference an aggregate function. The condition is incorrect.
What is the output of following queries:
13. The output will be sorted in ascending order of the second column in the column list namely DIRECTOR_LNAME.
14. A total count of the number of rows in the table will be displayed. i.e. 9
15. No rows will be retrieved. The data in the table is case-sensitive. LOPEZ should have been written as Lopez for data to be retrieved.
16. 01-JAN-00
17. The firstname of directors who released their last movie in the same year that Sidney released his last movie. The output will have the names: Rupert, Geoffrey, Mario
18. The email address of Geoffrey is displayed: geogreenashell.com
19. The output will be sorted on the last two characters of the DATE_LAST_MOVIE_RELEASED column. It will be sorted using the rules of sorting character data, not as a numeric value representing the year.
20. Will retrieve the details of the directors whose firstname are made of either 3, 4, 5, or 6 letters.
