databases and sql for data science with python coursera week 6 quiz answers
Practice Quiz
1. Which of the following statements about views is correct?
- A view is an independent copy of a single table’s structure, including the data.
- When you define a view, only the definition of the view is stored, not the data that it represents.
- A view can only represent data from a single table.
- You cannot change data in the base tables through a view.
2. Which of the following SQL statements will create a view that lists the job name and minimum and maximum salaries for jobs with a pay range of 50000 to 100000?
CREATE VIEW JobSalaryRanges(Job, StartingSalary, MaximumSalary)
AS SELECT JOB_TITLE, MIN_SALARY, MAX_SALARY
FROM JOBS
WHERE MIN_SALARY > 50000 AND MAX_SALARY < 100000;
CREATE VIEW JobSalaryRanges(Job, StartingSalary, MaximumSalary)
FROM JOBS
WHERE MIN_SALARY > 50000 AND MAX_SALARY < 100000;
CREATE VIEW JobSalaryRanges(Job, StartingSalary, MaximumSalary)
AS SELECT JOB_TITLE, MIN_SALARY, MAX_SALARY
FROM JOBS
WHERE SALARY > 50000 AND SALARY < 100000;
CREATE VIEW
AS SELECT JOB_TITLE, MIN_SALARY, MAX_SALARY
FROM JOBS
WHERE MIN_SALARY > 50000 AND MAX_SALARY < 100000;
3. The benefits of stored procedures include which of the following?
- Reuse of code
- Improvement in performance
- Reduction in network traffic
- All of the above
4. Which of the following can a stored procedure use?
- Input parameters
- Input and output parameters
- Output parameters
- A stored procedure cannot accept parameters.
5. What does ACID stand for?
- Atomic, Consistent, Isolated, Durable
- Asynchronous, Complete, Individual, Direct
- Alternative, Creative, Isolated, Durable
- Atomic, Consistent, Initiated, Duplicated
6. You usually create a join between…?
- Any column in either table.
- The primary key in one table to the foreign key in another table.
- The primary keys in each table.
- The foreign keys in each table.
7. Which type of join returns all of the rows that an inner join returns and also all of the rows in the second table that do not have a match in the first table?
- Left inner join
- Right outer join
- Full outer join
- Left outer join
8. Which of the following statements correctly uses an INNER JOIN?
- CREATE INNER JOIN BETWEEN EMPLOYEES e AND DEPARTMENTS d ON e.DEP_ID = d.DEP_ID
- SELECT * FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.DEP_ID = d.DEP_ID
- SELECT * FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON DEP_ID
- SELECT * FROM EMPLOYEES INNER JOIN DEPARTMENTS ON DEP_ID = DEP_ID
9. Which of the following are three valid types of outer join?
- Left outer join, right outer join, left/right outer join
- Left outer join, right outer join, full outer join
- Left outer join, right outer join, total outer join
- Left outer join, right outer join, both outer join
10. Which type of join would you use to select all the rows from both tables?
- Left outer join
- Right outer join
- Full outer join
- Total outer join
Graded Quiz: Views, Stored Procedures and Transactions
11. A stored procedure can:
- Be written in different languages
- Accept information
- Return results
- All of the above
12. What does ACID stand for?
- Atomic, Consistent, Isolated, Durable
- Alternative, Creative, Isolated, Durable
- Atomic, Consistent, Initiated, Duplicated
- Asynchronous, Complete, Individual, Direct
13. Which of the following SQL statements will create a view named EMP_VIEW with an employee’s First name, last name, and ID, based on the EMPLOYEES tables?
CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)
AS SELECT EMP_ID, F_NAME, L_NAME
FROM EMPLOYEES;
CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)
FROM EMPLOYEES;
CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)
AS SELECT EMP_ID, F_NAME, L_NAME;
NEW VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)
AS SELECT EMP_ID, F_NAME, L_NAME
FROM EMPLOYEES;
14. Which of the following SQL statements will create a view that lists only the employees in department number 7?
CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)
AS SELECT EMP_ID, F_NAME, L_NAME
FROM EMPLOYEES
WHERE DEP_ID = 7;
CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)
AS SELECT EMP_ID, F_NAME, L_NAME
WHERE DEP_ID = 7;
CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)
AS SELECT EMP_ID, F_NAME, L_NAME
FROM EMPLOYEES
IF DEP_ID = 7;
CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)
WHERE DEP_ID = 7
AS SELECT EMP_ID, F_NAME, L_NAME
FROM EMPLOYEES;
15. You are developing an application that helps users transfer money from one bank account to another. In tests, the source account is debited, but the target account is not credited. Which of the following SQL commands undoes all the changes made during the transfer to leave the database in a stable state?
- ROLLBACK
- COMMIT
- BEGIN
- DROP
Graded Quiz: JOIN operations
16. An INNER JOIN returns only the rows that match. (T/F)
- True
- False
17. A LEFT OUTER JOIN displays all the rows from the right table, and combines matching rows from the left table. (T/F)
- True
- False
18. When using an OUTER JOIN, you must explicitly state what kind of OUTER JOIN you want - a LEFT JOIN, a RIGHT JOIN, or a FULL JOIN. (T/F)
- True
- False
19. Which of the following are valid types of JOINs?
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- FULL LEFT JOIN
- All of the above
20. A FULL JOIN returns only the rows that match. (T/F)
- True
- False