sql for data science with r coursera answers week 3

Refining your Results

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 SORT BY Lastname;
  • SELECT * FROM Employees ORDER BY Lastname DESC;
  • SELECT * FROM Employees ORDER BY Lastname;
  • SELECT * FROM Employees GROUP BY Lastname;

2. Which of the following keyword should be used in order to set a filtering condition, when using GROUPBY clause?

  • WHERE
  • HAVING
  • SELECT
  • ORDER BY

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 IF Country (‘Australia’, ‘Canada’, ‘India’);
  • SELECT * FROM Author WHERE Country BETWEEN(‘Australia’, ‘Canada’, ‘India’);
  • SELECT * FROM Author WHERE Country LIST (‘CA’, ‘IN’);
  • SELECT * FROM Author WHERE Country IN (‘Australia’, ‘Canada’, ‘India’);

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

  • SELECT Title, Price FROM Book WHERE Price 10 to 25;
  • SELECT Title, Price FROM Book WHERE Price >= 10 and Price <= 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?

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

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

9. When querying a table called Author that contains a list of authors and their country of residence, which of the following queries will return the number of authors from each country?

  • SELECT Country, count(Country) FROM Author GROUP BY Country
  • SELECT Distinct(Country) FROM Author
  • SELECT Country, count(Country) FROM Author
  • SELECT Country, distinct(Country) FROM Author GROUP BY Country

10. You want to retrieve a list of books that have between 450 and 600 pages. Which clause would you add to the following SQL statement:

SELECT Title, Pages FROM Book ________________________________

  • WHERE Pages = 450
  • WHERE Pages 450 – 600
  • IF Pages >= 450 and Pages <= 600
  • WHERE Pages >= 450 and pages <= 600

Functions, Multiple Tables, and Sub-queries

Practice Quiz

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

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

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

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

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

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

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

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

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

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

Graded Quiz: Functions, Sub-Queries, Multiple Tables

16. Which of the following will retrieve the LOWEST value of SALARY in a table called EMPLOYEES?

  • SELECT SALARY FROM EMPLOYEES WHERE MINIMUM(SALARY) = SALARY
  • SELECT MAX(SALARY) FROM EMPLOYEES
  • SELECT LOWEST(SALARY) FROM EMPLOYER
  • SELECT MIN(SALARY) FROM EMPLOYEES

17. Which of the following queries will return the first name of the employee who earns the highest salary?

  • SELECT MAX(SALARY) FROM EMPLOYEES
  • SELECT FIRST_NAME FROM EMPLOYEES WHERE SALARY = ( SELECT MAX(SALARY) FROM EMPLOYEES )
  • SELECT FIRST_NAME, MAX(SALARY) FROM EMPLOYEES GROUP BY F_NAME
  • SELECT FIRST_NAME FROM EMPLOYEES WHERE SALARY IS HIGHEST

18. 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 = ( SELECT DEPT_ID_DEP FROM DEPARTMENTS WHERE DEPT_ID_DEP IS MAX )
  • SELECT * FROM EMPLOYEES WHERE DEP_ID = MAX(DEP_ID)

19. 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 E.F_NAME, D.DEP_NAME FROM EMPLOYEES, DEPARTMENTS
  • 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 E, DEPARTMENTS D WHERE E.DEPT_ID_DEP = D.DEP_ID
  • SELECT F_NAME, DEP_NAME FROM EMPLOYEES, DEPARTMENTS WHERE DEPT_ID_DEP = DEP_ID

20. 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