databases and sql for data science with python coursera week 3 quiz answers

Practice Quiz

1. You want to retrieve a list of employees in alphabetical order of Lastname from the Employees table. Which SQL statement should you use?

  • SELECT * FROM Employees ORDER BY Lastname DESC;
  • SELECT * FROM Employees GROUP BY Lastname;
  • SELECT * FROM Employees ORDER BY Lastname;
  • SELECT * FROM Employees SORT BY Lastname;

2. Which keyword is used to set a condition for a GROUP BY clause?

  • HAVING
  • SELECT
  • ORDER BY
  • WHERE

3. You want to retrieve a list of authors from Australia, Canada, and India from the table Authors. Which SQL statement is correct?

  • SELECT * FROM Author WHERE Country LIST (‘CA’, ‘IN’);
  • SELECT * FROM Author WHERE Country BETWEEN(‘Australia’, ‘Canada’, ‘India’);
  • SELECT * FROM Author WHERE Country IN (‘Australia’, ‘Canada’, ‘India’);
  • SELECT * FROM Author IF Country (‘Australia’, ‘Canada’, ‘India’);

4. You want to retrieve a list of books priced above $10 and below $25 from the table Book. What are the two ways you can specify the range?

  • SELECT Title, Price FROM Book WHERE Price >= 10 and Price <= 25;
  • SELECT Title, Price FROM Book WHERE Price 10 to 25;
  • SELECT Title, Price FROM Book WHERE Price BETWEEN 10 and 25;
  • SELECT Title, Price FROM Book WHERE Price IN (10, 25);

5. You want to retrieve Salary information for an employee called Ed from the Employee table. You write the following statement:

SELECT Firstname, Lastname, Salary FROM Employees

You see all the employees listed, and it’s hard to find Ed’s information. Which clause should you add to reduce the number of rows returned?

  • GROUP BY Firstname = ‘Ed’;
  • WHERE Firstname = ‘Ed’;
  • ORDER BY Firstname;
  • WHERE Employees = ‘Ed’;

Graded Quiz: Refining Your Results

6. You want to select author's last name from a table, but you only remember the author’s last name starts with the letter B, which string pattern can you use?

  • SELECT lastname from author where lastname like ‘B#’
  • SELECT lastname from author where lastname like ‘B%’
  • SELECT lastname from author where lastname like ‘B$’
  • None of the above

7. In a SELECT statement, which SQL clause controls how the result set is displayed?

  • ORDER BY clause
  • ORDER IN clause
  • ORDER WITH clause

8. Which of the following can be used in a SELECT statement to restrict a result set?

  • HAVING
  • WHERE
  • DISTINCT
  • All of the above

Practice Quiz

9. Which of the following statements about built-in database functions is correct?

  • Built-in database functions may increase network bandwidth consumed.
  • Built-in database functions may increase processing time.
  • Built-in database functions must be called from a programming language like Python.
  • Built-in database functions reduce the amount of data that is retrieved.

10. Which of the following SQL queries would return the day of the week each dog was rescued?

  • SELECT RescueDate From PetRescue WHERE Animal = ‘Dog’;
  • SELECT DAYOFWEEK(RescueDate) From PetRescue WHERE Animal = ‘Dog’;
  • SELECT DAY(RescueDate) From PetRescue WHERE Animal = ‘Dog’;
  • SELECT DAYOFWEEK(RescueDate) From PetRescue;

11. What is the result of the following query: SELECT (Current_Date – RescueDate) FROM PetRescue

  • Returns today’s date.
  • Returns how long it has been since each rescue.
  • Returns the rescue date for each rescue.
  • Returns the current date and rescue date columns.

12. Which of the following queries will return the employees who earn less than the average salary?

  • SELECT * FROM Employees WHERE Salary < AVG(Salary)
  • SELECT * FROM Employees WHERE Salary < (SELECT AVG(Salary))
  • SELECT AVG(Salary) FROM Employees WHERE Salary < AVG(Salary)
  • SELECT * FROM Employees WHERE Salary < (SELECT AVG(Salary) FROM Employees);

13. What are the three ways to work with multiple tables in the same query?

  • Sub-queries, Implicit joins, JOIN operators
  • Sub-queries, APPEND, JOIN operators
  • Built-in functions, implicit joins, JOIN operators
  • Sub-queries, Implicit joins, normalization.

Graded Quiz: Functions, Sub-Queries, Multiple Tables

14. Which of the following queries will return the data for employees who belong to the department with the highest value of department ID.

  • SELECT * FROM EMPLOYEES WHERE DEPT_ID_DEP =

    MAX ( SELECT DEPT_ID_DEP FROM DEPARTMENTS )

  • SELECT * FROM EMPLOYEES WHERE DEP_ID =

    ( SELECT MAX(DEPT_ID_DEP) FROM DEPARTMENTS )

  • SELECT * FROM EMPLOYEES WHERE DEP_ID = MAX(DEP_ID)

  • SELECT * FROM EMPLOYEES WHERE DEP_ID =

    ( SELECT DEPT_ID_DEP FROM DEPARTMENTS WHERE DEPT_ID_DEP IS MAX )

15. A DEPARTMENTS table contains DEP_NAME, and DEPT_ID_DEP columns and an EMPLOYEES table contains columns called F_NAME and DEP_ID. We want to retrieve the Department Name for each Employee. Which of the following queries will correctly accomplish this?

  • SELECT F_NAME, DEP_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPT_ID_DEP = D.DEP_ID
  • SELECT D.F_NAME, E.DEP_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE DEPT_ID_DEP = DEP_ID
  • SELECT F_NAME, DEP_NAME FROM EMPLOYEES, DEPARTMENTS WHERE DEPT_ID_DEP = DEP_ID
  • SELECT E.F_NAME, D.DEP_NAME FROM EMPLOYEES, DEPARTMENTS

16. You are writing a query that will give you the total cost to the Pet Rescue organization of rescuing animals. The cost of each rescue is stored in the Cost column. You want the result column to be called “Total_Cost”. Which of the following SQL queries is correct?

  • SELECT SUM(Cost) FROM PetRescue
  • SELECT SUM(Cost) AS Total_Cost FROM PetRescue
  • SELECT SUM(Total_Cost) From PetRescue
  • SELECT Total_Cost FROM PetRescue

Leave a Reply