SQL Skill Test - Workana

JavaScript isn't enabled in your browser, so this file can't be opened. Enable and reload.

Email *

Workana Profile link

Please enter your full name *

MULTIPLE CHOICE QUESTIONS

1. Which of the following comparison operators could be used in an outer query to filter records using a subquery that returns multiple rows? *

IN operator

ANY operator

ALL operator

All of the above

2. Which of the following is true about Primary Key? *

Primary key can take a value more than once

A table must have a primary key

Primary key can take null values

None of these

3. Write a query to extract the following data from students_table: *

SELECT name, major, avg(gpa) FROM students_table GROUP BY major;

SELECT name, major, avg(gpa) FROM students_table GROUP BY name;

SELECT A.name, A.major, B.avg_gpa FROM students_table AS A JOIN (SELECT major, avg(gpa) as avg_gpa from students_table group by major) as B WHERE A.major = B.major;

SELECT A.name, A.major, B.avg_gpa FROM students_table AS A JOIN (SELECT major, avg(gpa) as avg_gpa from students_table group by name) as B WHERE A.name = B.name;

4. How will you list all majors with more than 1 student enrolled in them? *

SELECT major FROM students_table GROUP BY major WHERE COUNT(*) > 1;

SELECT major FROM students_table GROUP BY major HAVING COUNT(*) > 1;

SELECT major FROM students_table WHERE COUNT(*) > 1 GROUP BY major;

SELECT major FROM majors_table WHERE students_table.COUNT(*) > 1;

5. Which of the following statements is correct about the query *

Query will correctly display the required information

The existing query will run without errors but will not display the desired information

If WHERE is replaced with HAVING, the query will return an Error

The query will return an Error in its current form

6. Which of the following could be a possible issue with the output of the query? *

The query will extract the FIRST name of the users instead of last name

The query will extract the MIDDLE name of the users instead of last name

The query will extract the FIRST and LAST name of some of the users

The query will extract the LAST and MIDDLE name of some of the users

7. Based on the following queries, which of the following statements is NOT correct? *

Queries 2 and 3 will display the same result

Queries 1 and 4 will display the same result

Queries 1 and 2 will display the same result

Queries 2 and 4 will display different results

8. Which of these queries will return “No”? *

SELECT CASE WHEN (2 IN (1, 2, 3, 4, NULL)) THEN “Yes” ELSE “No” END AS return_value;

SELECT CASE WHEN (2 NOT IN (1, 3, 4, NULL)) THEN “No” ELSE “Yes” END AS return_value;

SELECT CASE WHEN (2 IN (1, 3, 4, NULL)) THEN “Yes” ELSE “No” END AS return_value;

None

9. Which of the following queries will select all the information about the student with the 3rd highest gpa from students_table in MySQL? *

SELECT TOP 1 * FROM (SELECT TOP 3 * FROM students_table ORDER BY gpa DESC) AS A ORDER BY gpa ASC;

SELECT * FROM (SELECT * FROM students_table ORDER BY gpa DESC LIMIT 1) AS A ORDER BY gpa ASC LIMIT 3;

SELECT TOP 3 * FROM (SELECT TOP 1 * FROM students_table ORDER BY gpa DESC) AS A ORDER BY gpa ASC;

SELECT * FROM (SELECT * FROM students_table ORDER BY gpa DESC LIMIT 3) AS A ORDER BY gpa ASC LIMIT 1;

10. Which of the following clauses can have a subquery? *

WHERE

HAVING

FROM

All of the above

WRITE QUERIES FOR THE FOLLOWING QUESTIONS

User the tables below to answer question 11

11. Write a query to calculate the total handling cost of each order using the tables above (in MySQL) *

Use the tables Problem, Expert, Problem_rating to answer questions 12-14

12. Write a query to count the number of problems asked in the last 24 hours (in MySQL) *

13. Write a single query to extract the two following data at once: (1) Percentage of problems that are unrated (any problem that does not exist in the problem_rating table is unrated) (2) Percentage of problems that were rated 4 or 5 stars (in MySQL) *

14. Write a query to output the email of the expert with second highest average rating (in MySQL) *

Following is a schema of a grocery store with 4 tables. Use this schema to answer the question 15-17

15. Use the schema and 4 tables above to write a query to calculate the total amount spent by each customer. The output of the query should have two columns - customer name and total amount spent (in MySQL) *

16. Use the schema and 4 tables above to write a query to list top 3 selling products along with revenue generated by these products. Output of the query should have two columns - product name and revenue (in MySQL) *

17. Use the schema and 4 tables above to write a query to output the quantity of milk sold by date (in gallons). Output should have two columns - date and gallons of milk sold (in MySQL) *

Submit

Clear form

Never submit passwords through Google Forms.

This form was created inside of Got It, Inc.. Report Abuse

Forms