Bài giảng Cơ sở dữ liệu (Database) - SQL Nâng cao - Vũ Hải

Cơ sở dữ liệu – Database EE4253 Vũ Hải 2016 International Research Institute MICA, Hanoi University of Science and Technology 1 SQL Nâng cao 2Resource: IFNULL, NVL, ISNULL • IFNULL(var1,var2): MySQL, • ISNULL(var1,var2): SQL Server • NVL(var1,var2): Oracle. 3 COALESCE • COALESCE ("expression 1", "expressions 2", ...) • Trả về biểu thức khác NULL đầu tiên trong số các tham số đầu vào. 4 NULLIF • NULLIF ("expression 1", "expressions 2") – If "expression 1 = expression 2 " THEN return "NULL" ELSE return "expression 1" 5 LIMIT • LIMIT: MySQL • [SQL Statement ] LIMIT [N]; 6 TOP • Microsoft SQL Server • TOP [N] • TOP [M] PERCENT 7 CASE • CASE ("column_name") WHEN "value1"/"condition1" THEN "result1" WHEN "value2"/"condition1" THEN "result2" ... [ELSE "resultN"] END 8 • Subquery đơn giản • Subquery có tương quan Subquery 9 Find the sales of all stores in the West region Division in SQL • Sailors, Boats, Reserves • Find names of sailors who’ve reserved all boats • SELECT S.sname FROM Sailors S WHERE NOT EXISTS (SELECT B.bid FROM Boats B WHERE NOT EXISTS ( SELECT R.bid FROM Reserves R WHERE R.bid=B.bid AND R.sid=S.sid)) 10 Sailors S such that . there is no boat B that doesn’t have a Reserves tuple showing S reserved B SQL Quiz 11 SQL Quiz 1. Which SQL statement would you use to select all columns from the BOOK_INFORMATION table? • a) SELECT BOOK_ID + PRICE FROM BOOK_INFORMATION; b) SELECT * FROM BOOK_INFORMATION; c) SELECT ALL FROM BOOK_INFORMATION; d) SELECT ALL COLUMNS FROM BOOK_INFORMATION; 12 SQL Quiz • 2. Which SQL statement would you use to select all books that has a price higher than 20? • a) SELECT BOOK_ID FROM BOOK_INFORMATION HAVING PRICE > 20; b) SELECT BOOK_ID FROM BOOK_INFORMATION ONLY PRICE > 20; c) SELECT BOOK_ID FROM BOOK_INFORMATION WHERE BOOK_ID > 20; d) SELECT BOOK_ID FROM BOOK_INFORMATION WHERE PRICE > 20; 13 SQL Quiz • 3. Which SQL statement would you use to select all books whose title starts with 'A'? • a) SELECT BOOK_ID, BOOK_TITLE FROM BOOK_INFORMATION WHERE BOOK_TITLE LIKE 'A'; b) SELECT BOOK_ID, BOOK_TITLE FROM BOOK_INFORMATION WHERE BOOK_TITLE IN 'A'; c) SELECT BOOK_ID, BOOK_TITLE FROM BOOK_INFORMATION WHERE BOOK_TITLE LIKE 'A%'; d) SELECT BOOK_ID, BOOK_TITLE FROM BOOK_INFORMATION WHERE BOOK_TITLE LIKE '%A'; 14 SQL Quiz • 4. Which SQL statement allows you to sort all books by price, from the highest price to lowest price? • a) SELECT BOOK_ID, BOOK_TITLE, PRICE FROM BOOK_INFORMATION ORDER BY PRICE DESC; b) SELECT BOOK_ID, BOOK_TITLE, PRICE FROM BOOK_INFORMATION SORT BY PRICE DESC; c) SELECT BOOK_ID, BOOK_TITLE, PRICE FROM BOOK_INFORMATION SORT BY PRICE ASC; d) SELECT BOOK_ID, BOOK_TITLE, PRICE FROM BOOK_INFORMATION ORDER BY PRICE ASC; 15 SQL Quiz • 5. Which SQL statement allows you to insert the following piece of data into BOOK_INFORMATION? BOOK_ID=20 BOOK_TITLE='1KEYDATA SQL TUTORIAL' PRICE=15 • a) ADD INTO BOOK_INFORMATION WITH (20,'1KEYDATA SQL TUTORIAL',15); b) INSERT INTO BOOK_INFORMATION USING (20,'1KEYDATA SQL TUTORIAL',15); c) INSERT INTO BOOK_INFORMATION VALUES (20,'1KEYDATA SQL TUTORIAL',15); d) ADD INTO BOOK_INFORMATION VALUES (20,'1KEYDATA SQL TUTORIAL',15); 16 SQL Quiz • 6. Which SQL statement lets you remove the table BOOK_INFORMATION from the database? • a) DROP BOOK_INFORMATION; b) DELETE TABLE BOOK_INFORMATION; c) TRUNCATE TABLE BOOK_INFORMATION; d) DROP TABLE BOOK_INFORMATION; 17 SQL Quiz • 7. Which SQL statement would you use to delete the row for BOOK_ID=15? • a) TRUNCATE TABLE BOOK_INFORMATION WHERE BOOK_ID = 15; b) DELETE FROM BOOK_INFORMATION WHERE BOOK_ID = 15; c) DROP BOOK_INFORMATION WHERE BOOK_ID = 15; d) TRUNCATE BOOK_INFORMATION WHERE BOOK_ID = 15; 18 SQL Quiz • 8. Which SQL statement will you use to change the price for the BOOK titled 'ELEMENTARY SCHOOL GUIDE' to 20? • a) UPDATE BOOK_INFORMATION SET PRICE = 20 WHERE BOOK_TITLE = 'ELEMENTARY SCHOOL GUIDE'; b) UPDATE TABLE BOOK_INFORMATION SET PRICE = 20 WHERE BOOK_TITLE = 'ELEMENTARY SCHOOL GUIDE'; c) UPDATE BOOK_INFORMATION CHANGE PRICE = 20 WHERE BOOK_TITLE = 'ELEMENTARY SCHOOL GUIDE'; d) UPDATE TABLE BOOK_INFORMATION SET PRICE = 20 WHERE BOOK_ID = 'ELEMENTARY SCHOOL GUIDE'; 19 SQL Quiz • 9. Which SQL statement allows you to find the highest price from the table BOOK_INFORMATION? • a) SELECT BOOK_ID, BOOK_TITLE, MAX(PRICE) FROM BOOK_INFORMATION; b) SELECT MAX(PRICE) FROM BOOK_INFORMATION; c) SELECT MAXIMUM(PRICE) FROM BOOK_INFORMATION; d) SELECT PRICE FROM BOOK_INFORMATION ORDER BY PRICE DESC; 20 SQL Quiz • 10. Which SQL statement allows you to find all books priced between 15 and 20? • a) SELECT BOOK_ID, BOOK_TITLE, PRICE FROM BOOK_INFORMATION WHERE PRICE IS BETWEEN 15 AND 20; b) SELECT BOOK_ID, BOOK_TITLE, PRICE FROM BOOK_INFORMATION HAVING PRICE IS BETWEEN 15 AND 20; c) SELECT BOOK_ID, BOOK_TITLE, PRICE FROM BOOK_INFORMATION WHERE PRICE BETWEEN 15 AND 20; d) SELECT BOOK_ID, BOOK_TITLE, PRICE FROM BOOK_INFORMATION HAVING PRICE BETWEEN 15 AND 20; 21 SQL Quiz • 11. Which SQL statement lets you find the sales amount for each store? • a) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES; b) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES ORDER BY STORE_ID; c) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID; d) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES HAVING UNIQUE STORE_ID; 22 SQL Quiz • 12. Which SQL statement lets you list all stores whose total sales amount is over 5000? • a) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000; b) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SALES_AMOUNT > 5000; c) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SUM(SALES_AMOUNT) > 5000 GROUP BY STORE_ID; d) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SALES_AMOUNT > 5000 GROUP BY STORE_ID; 23 SQL Quiz • 13. Which SQL statement is the correct one to use to find the earliest date STORE_ID 10 had a sales amount greater than 0? • a) SELECT MAX(SALES_DATE) FROM SALES WHERE STORE_ID = 10 AND SALES_AMOUNT > 0; b) SELECT SALES_DATE FROM SALES WHERE STORE_ID = 10 AND SALES_AMOUNT > 0; c) SELECT MIN(SALES_DATE) FROM SALES WHERE STORE_ID = 10 OR SALES_AMOUNT > 0; d) SELECT MIN(SALES_DATE) FROM SALES WHERE STORE_ID = 10 AND SALES_AMOUNT > 0; 24 SQL Quiz • 14. Which SQL statement lets you find the total number of stores in the SALES table? • a) SELECT COUNT(STORE_ID) FROM SALES; b) SELECT COUNT(DISTINCT STORE_ID) FROM SALES; c) SELECT DISTINCT STORE_ID FROM SALES; d) SELECT COUNT(STORE_ID) FROM SALES GROUP BY STORE_ID; 25 SQL Quiz • 15. Which SQL statement allows you to find the total sales amount for Store ID 25 and the total sales amount for Store ID 45? • a) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25,45) GROUP BY STORE_ID; b) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING STORE_ID IN (25,45); c) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25,45); d) SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID = 25 AND STORE_ID = 45 GROUP BY STORE_ID; 26 SQL Quiz • 16. What is the result of the following SQL statement: SELECT COUNT(DISTINCT STUDENT_ID) FROM EXAM_RESULTS; • a) 3 b) 4 c) 5 d) 6 27 SQL Quiz • 17. What SQL statement do we use to find the average exam score for EXAM_ID = 1? • a) SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS; b) SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1; c) SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID; d) SELECT COUNT(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1; 28 SQL Quiz • 18. Which SQL statement do we use to find out how many students took each exam? • a) SELECT COUNT(DISTINCT Stduetn_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; b) SELECT EXAM_ID, MAX(STUDENT_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; c) SELECT EXAM_ID, COUNT(DISTINCT STUDENT_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; d) SELECT EXAM_ID, MIN(STUDENT_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; 29 SQL Quiz • 19. What SQL statement do we use to print out the record of all students whose last name starts with 'L'? • a) SELECT * FROM EXAM_RESULTS WHERE LAST_NAME LIKE 'L%'; b) SELECT * FROM EXAM_RESULTS WHERE LAST_NAME LIKE 'L'; c) SELECT * FROM EXAM_RESULTS WHERE LAST_NAME = 'L'; d) SELECT * FROM EXAM_RESULTS WHERE LAST_NAME 'L'; 30 SQL Quiz • 20. What is the result of the following SQL statement: SELECT MAX(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1 AND FIRST_NAME LIKE '%E%'; • a) 90 b) 85 c) 100 d) 78 31 SQL Quiz • 21. What SQL statement do we use to print out the records of all students whose first name or last name ends in 'A'? • a) SELECT * FROM EXAM_RESULTS WHERE FIRST_NAME LIKE '%A' OR LAST_NAME LIKE '%A'; b) SELECT * FROM EXAM_RESULTS WHERE FIRST_NAME LIKE 'A' OR LAST_NAME LIKE 'A'; c) SELECT * FROM EXAM_RESULTS WHERE FIRST_NAME LIKE 'A%' OR LAST_NAME LIKE 'A%'; d) SELECT * FROM EXAM_RESULTS WHERE FIRST_NAME LIKE '%A%' OR LAST_NAME LIKE '%A%'; 32 SQL Quiz • 22. What SQL statement do we use to find the name of all students who scored better than 90 on the second exam (EXAM_ID = 2)? • a) SELECT FIRST_NAME, LAST_NAME FROM EXAM_RESULTS WHERE EXAM_ID = 2 OR Exam_SCORE > 90; b) SELECT FIRST_NAME, LAST_NAME FROM EXAM_RESULTS WHERE EXAM_ID = 2 AND Exam_SCORE > 90; c) SELECT FIRST_NAME, LAST_NAME FROM EXAM_RESULTS WHERE Exam_SCORE > 90; d) SELECT FIRST_NAME, LAST_NAME FROM EXAM_RESULTS WHERE EXAM_ID = 2 HAVING Exam_SCORE > 90; 33 SQL Quiz • 23. What SQL statement do we use to find the name of all students who scored better than 180 on all the Exams? • a) SELECT FIRST_NAME, LAST_NAME, SUM(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY FIRST_NAME, LAST_NAME; b) SELECT FIRST_NAME, LAST_NAME, SUM(EXAM_SCORE) FROM EXAM_RESULTS HAVING SUM(EXAM_SCORE) > 180; c) SELECT FIRST_NAME, LAST_NAME, SUM(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY FIRST_NAME, LAST_NAME HAVING SUM(EXAM_SCORE) > 180; d) SELECT FIRST_NAME, LAST_NAME, SUM(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_SCORE > 180 GROUP BY FIRST_NAME, LAST_NAME; 34 SQL Quiz • 24. How many records does the following SQL statement generate? SELECT * FROM EXAM_RESULTS WHERE LAST_NAME LIKE '%N%' AND EXAM_SCORE > 88; • a) 4 b) 3 c) 1 d) No Result 35 SQL Quiz • 25. How many records does the following SQL statement return? SELECT * FROM EXAM_RESULTS WHERE STUDENT_ID <= 12 AND EXAM_SCORE > 85; • a) 5 b) 4 c) 3 d) 2 36 37