SQL > SQL Quiz

Take this 25-question, multiple-choice quiz to see if you have a good understanding of SQL.

Questions 1-10 use the following table:

Table BOOK_INFORMATION

Column Name
BOOK_ID
BOOK_TITLE
PRICE

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;


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;


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';


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;


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);


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;


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;


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';


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;


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;


Questions 11 - 15 uses the following table:

Table SALES

Column Name
STORE_ID
SALES_DATE
SALES_AMOUNT

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;


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;


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;


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;


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;


Questions 16-25 use the following table:

Table EXAM_RESULTS

STUDENT_IDFIRST_NAMELAST_NAMEEXAM_IDEXAM_SCORE
10LAURALYNCH190
10LAURALYNCH285
11GRACEBROWN178
11GRACEBROWN2 72
12JAY JACKSON195
12JAYJACKSON292
13WILLIAMBISHOP170
13WILLIAMBISHOP2100
14CHARLESPRADA285

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


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;


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;


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';


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


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%';


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;


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;


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


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




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact