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?

Answers

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?

Answers

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?

Answers

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?

Answers

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?

Answers

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?

Answers

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?

Answers

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?

Answers

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?

Answers

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 ________________________________

Answers

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?

Answers

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?

Answers

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

Answers

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?

Answers

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?

Answers

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?

Answers

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?

Answers

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.

Answers

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?

Answers

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?

Answers

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 Cancel reply