Databases and SQL for Data Science with Python Quiz Answers

Get Databases and SQL for Data Science with Python Quiz Answers for All Modules

Databases and SQL for Data Science with Python Module 01 Quiz: Basic SQL Graded Quiz Answers

Q1. Assume an INSTRUCTOR table exists with columns including FIRSTNAME, LASTNAME, and others. What would be the most likely result set for the query:

sqlCopy codeSELECT DISTINCT FIRSTNAME FROM INSTRUCTOR

Answer:

• LEON, PAUL, JOE

Explanation: The DISTINCT keyword ensures the query returns only unique FIRSTNAME values. Duplicate entries are removed, leaving a list of unique first names in the result set.

---

Q2. sqlCopy codeUPDATE INSTRUCTOR SET LASTNAME = 'Brewster' WHERE LASTNAME = 'Smith'


Answer:

• Changes the last name of all instructors named ‘Smith’ to ‘Brewster.’

Explanation: The UPDATE statement modifies the LASTNAME column for rows where LASTNAME is ‘Smith.’ Rows not meeting this condition remain unchanged.

---

Q3. What would occur if you executed a DELETE FROM statement on a table without the WHERE clause?

Answer:

• The command would remove all entries in the table, leaving it empty but still present in the database.

Explanation: Executing DELETE FROM without a WHERE clause deletes all rows in the table but does not remove the table structure itself. To delete the table, a DROP TABLE command is required.

---

Q4. What is the expected result of the following SQL statement?

sqlCopy codeSELECT COUNT(DISTINCT FIRSTNAME) FROM INSTRUCTOR

Answer:

• The number of unique FIRSTNAME entries in the INSTRUCTOR table.

Explanation: COUNT(DISTINCT FIRSTNAME) calculates the total number of unique entries in the FIRSTNAME column by counting distinct values.

---

Q5. Considering the execution of the following SQL statement, what would be the expected output?

sqlCopy codeSELECT * FROM INSTRUCTOR WHERE LASTNAME='Smith' LIMIT 5

Answer:

• The first 5 entries in the INSTRUCTOR table where LASTNAME is ‘Smith.’

Explanation: The LIMIT 5 clause restricts the result set to the first 5 rows that match the condition WHERE LASTNAME='Smith'. The order is based on the default row storage in the database unless explicitly sorted.

Databases and SQL for Data Science with Python Module 02 Relational DB Concepts and Tables Graded Quiz Answers

Q1. Which of the following statements about a database is/are correct?

Answer:

• A database is a logically coherent collection of data with some inherent meaning.

Explanation: A database is defined as a structured and logically organized collection of data with meaning. The other options are incorrect because data can be added, queried, and modified, and while SQL is common, it is not the only query language used.

---

Q2. Attributes of an entity become ________ in a table.

Answer:

• Columns

Explanation: Attributes of an entity represent the properties or characteristics of that entity, and they translate into columns in a relational database table.

---

Q3. The CREATE TABLE statement is a ________.

Answer:

• DDL statement

Explanation: CREATE TABLE is a Data Definition Language (DDL) statement used to define and create the structure of a database table.

---

Q4. Which command is used for removing a table and all its data from the database?

Answer:

• DROP table command

Explanation: The DROP command permanently removes a table and all its data from the database. In contrast, TRUNCATE removes all rows but keeps the table structure intact.

---

Q5. What would be the correct syntax to add a column ‘ID’ that contains 7-character alphanumeric values to a database table ‘Employees’ using MySQL?

Answer:

• ALTER TABLE Employees ADD ID char(7)

Explanation: The correct syntax to add a column in MySQL is ALTER TABLE [table_name] ADD [column_name] [data_type]. In this case, char(7) specifies a fixed-length alphanumeric column with a size of 7 characters.

Databases and SQL for Data Science with Python Module 03 Refining Your Results Graded Quiz Answers

Q1. You want to select the author’s lastname from a table, but you only remember that it starts with the letter J. Which of the following queries uses the correct string pattern?

Answer:

• SELECT lastname FROM author WHERE lastname LIKE ‘J%’

Explanation: The LIKE operator with % is used in SQL to match patterns. Here, J% matches any lastname starting with “J” followed by zero or more characters.

---

Q2. In SQL, which of the following will be the correct way to sort a result set in descending order?

Answer:

• SELECT * FROM TABLE_NAME ORDER BY ID DESC

Explanation: The ORDER BY clause specifies the sort order for the result set, and DESC ensures that the data is sorted in descending order.

---

Q3. What is the role of the HAVING clause in SQL queries in MySQL?

Answer:

• Restricts the result set for a query using GROUP BY clause.

Explanation: The HAVING clause filters the result of a GROUP BY operation. It is used to apply conditions on aggregated data, which the WHERE clause cannot do.

---

Q4. Which of the choices best describe the function of the following SQL query?

sqlCopy codeSELECT * FROM employees ORDER BY emp_name LIMIT 5;

Answer:

• Retrieves all the columns of the top 5 rows of the table, sorted alphabetically based on emp_names

Explanation: The query sorts the employees table alphabetically by emp_name and retrieves the first 5 rows with all columns.

---

Q5. Which of the following SQL statements lists the number of customers in each country, showing only the countries with more than five customers?

Answer:

• SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

Explanation: The query uses GROUP BY to group customers by country and the HAVING clause to filter results where the count of CustomerID is greater than 5. The other queries either contain errors or incorrect conditions.

Graded Quiz: Functions, Multiple Tables, and Sub-queries Quiz Answers

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

Answer:

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

Explanation: This query retrieves all employees from the EMPLOYEES table whose DEP_ID matches the maximum department ID from the DEPARTMENTS table. The subquery correctly calculates the maximum DEPT_ID_DEP value.

---

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

Answer:

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

Explanation: This query uses the correct join condition (E.DEP_ID = D.DEPT_ID_DEP) to link employees to their departments, retrieving their first name (F_NAME) and department name (DEP_NAME).

---

Q3. 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”.

Answer:

• SELECT SUM(Cost) AS Total_Cost FROM PetRescue

Explanation: The SUM function calculates the total of all values in the Cost column, and the alias AS Total_Cost renames the result column to “Total_Cost.”

---

Q4. Which of the following queries correctly calculates the total number of days an employee has lived, using their date of birth (‘DOB’) and the current date, in MySQL?

Answer:

• SELECT DATEDIFF(CURRENT_DATE, DOB) FROM Employees

Explanation: The DATEDIFF function calculates the difference in days between two dates (CURRENT_DATE and DOB), returning the total number of days an employee has lived.

---

Q5. You have a record of a set of medicines called ‘MEDS’. Their date of expiry is exactly 1 year after their date of manufacturing. The name of the medicines is available as NAME and their date of manufacturing is available as a column DOM. Which of the commands will generate an output that contains the name of the medicines and also displays their date of expiry as a column DOE?

Answer:

• SELECT NAME, DATE_ADD(DOM, INTERVAL 1 YEAR) AS DOE FROM MEDS

Explanation: The DATE_ADD function in MySQL adds an interval to a date. Here, INTERVAL 1 YEAR adds one year to the DOM column, and the result is aliased as DOE. The query outputs both the name and the calculated expiry date.

Databases and SQL for Data Science with Python Module 04 Accessing databases using Python Graded Quiz Answers

Q1. Which of the following statements establishes the connection between a Jupyter Notebook SQL extension and an SQLite database ‘EMP.db’?

Answer:

• %sql sqlite:///EMP.db

Explanation: The correct syntax for connecting the SQL magic extension in Jupyter Notebook to an SQLite database file is %sql sqlite:///EMP.db. The triple slashes (///) indicate the absolute or relative file path.

---

Q2. Which two of the following can be stated as uses of cell magic in Jupyter Notebooks?

Answer:

• Coding in Jupyter notebook using a programming language other than Python
• Timing a complete cell block as per requirement

Explanation: Cell magics in Jupyter allow users to execute a cell in a different language (e.g., SQL, R) or perform tasks like timing cell execution (%%time).

---

Q3. What would be the outcome of the following Python code?

pythonCopy codeimport sqlite3
import pandas as pd
conn = sqlite3.connect('HR.db')
data = pd.read_csv('./employees.csv')
data.to_sql('Employees', conn)

Answer:

• The CSV file is read and converted into an SQL table ‘Employees’ under the HR database

Explanation: The to_sql method converts a Pandas DataFrame (data) into an SQL table named Employees within the SQLite database (HR.db) connected via conn.

---

Q4. What would be the correct way to query a database table using Python? Choose the 2 correct options.

Answer:

• out = pandas.read_sql(query_statement, connection_object)
• cursor = connection.execute(query_statement); out = cursor.fetchall()

Explanation:

• pandas.read_sql allows querying a database directly into a Pandas DataFrame.
• Using a database connection’s execute method with fetchall() retrieves query results into a Python list or tuple format.

---

Q5. Which of the following statements would you use to perform a statistical analysis of data in a Pandas DataFrame df?

Answer:

• df.describe()

Explanation: The df.describe() method provides a summary of statistical metrics (e.g., mean, median, standard deviation) for numerical columns in a Pandas DataFrame.

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

Q4. 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 IN (10, 25);
• SELECT Title, Price FROM Book WHERE Price BETWEEN 10 and 25;
• SELECT Title, Price FROM Book WHERE Price 10 to 25;
• SELECT Title, Price FROM Book WHERE Price >= 10 and Price <= 25;

Databases and SQL for Data Science with Python Module 05 Graded Quiz on Assignment Answers

Q1. What is the total number of crimes recorded in the CRIME table?

Answer:

• 533

Explanation: The total number of rows in the CRIME table represents the number of crimes recorded, which is 533 based on the options provided.

---

Q2. Which of the following is the correct query to list community areas (name and number) with per capita income less than 11000?

Answer:

• SELECT COMMUNITY_AREA_NUMBER, COMMUNITY_AREA_NAME FROM CENSUS_DATA WHERE PER_CAPITA_INCOME<11000

Explanation: This query selects the required columns from the CENSUS_DATA table and filters rows where the per capita income is less than 11000 using the WHERE clause.

---

Q3. When you list all case numbers for crimes involving a minor, how many rows of data are retrieved?

Answer:

• 4

Explanation: Based on the dataset, crimes involving a minor result in 4 rows of case numbers.

---

Q4. Which of the following can be used as a query for identifying all kidnapping crimes involving a child?

Answer:

• SELECT * FROM CHICAGO_CRIME_DATA WHERE PRIMARY_TYPE = "KIDNAPPING" AND DESCRIPTION LIKE “%CHILD%”

Explanation: This query identifies rows where the PRIMARY_TYPE is “KIDNAPPING” and the DESCRIPTION contains the word “CHILD” using the LIKE operator with the % wildcard.

---

Q5. Which two of the following clauses did you use to get the unique list of the types of crimes recorded in schools?

Answer:

• DISTINCT
• LIKE

Explanation:

• The DISTINCT clause is used to remove duplicate entries in the result set.
• The LIKE clause helps filter rows that match patterns related to crimes in schools.

---

Q6. What was the average safety score for middle schools?

Answer:

• 49.62

Explanation:
The average safety score for middle schools is calculated from the dataset, resulting in a value of 49.62.

---

Q7. What would you add to the following query to list five community areas with the highest % of households below the poverty line?

Query:
SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA ___________;

Answer:

• ORDER BY PERCENT_HOUSEHOLDS_BELOW_POVERTY DESC LIMIT 5

Explanation: To retrieve the top 5 community areas, the ORDER BY clause sorts the rows in descending order by the percentage of households below poverty, and LIMIT 5 restricts the result to the top 5.

---

Q8. Which community area number has the most criminal incidents (most crime-prone)?

Answer:

• 25.0

Explanation: Community area 25 has the highest number of recorded crimes in the dataset.

---

Q9. Which of the following would be the correct way to use a sub-query to find the name of the community area with the highest hardship index?

Answer:

• SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA WHERE HARDSHIP_INDEX IN (SELECT MAX(HARDSHIP_INDEX) FROM CENSUS_DATA);

Explanation: This query uses a subquery to find the maximum hardship index and matches it to retrieve the corresponding community area name.

---

Q10. What is the name of the community with the most number of crimes?

Answer:

• Austin

Explanation: The community with the highest number of crimes in the dataset is Austin.

Databases and SQL for Data Science with Python Module 06 Views, Stored Procedures and Transactions Quiz Answers

Q1. A stored procedure can:

Answer:

• All of the above

Explanation: A stored procedure can:

1. Be written in different languages depending on the database system.
2. Accept parameters as input.
3. Return results after executing logic.

---

Q2. What does ACID stand for?

Answer:

• Atomic, Consistent, Isolated, Durable

Explanation: ACID properties are essential for transaction reliability in databases:

1. Atomicity: Transactions are all-or-nothing.
2. Consistency: Transactions leave the database in a valid state.
3. Isolation: Transactions do not interfere with each other.
4. Durability: Completed transactions remain permanent even in case of failure.

---

Q3. 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 table?

Answer:

• CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME) AS SELECT EMP_ID, F_NAME, L_NAME FROM EMPLOYEES;

Explanation: The CREATE VIEW statement creates a new view, and the AS SELECT clause specifies the data to include in the view. Columns from the EMPLOYEES table (EMP_ID, F_NAME, L_NAME) are renamed to EMP_ID, FIRSTNAME, and LASTNAME in the view.

---

Q4. Which of the following SQL statements will create a view that lists only the employees in department number 7?

Answer:

• CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME) AS SELECT EMP_ID, F_NAME, L_NAME FROM EMPLOYEES WHERE DEP_ID = 7;

Explanation: This query creates a view (EMP_VIEW) that includes only employees where DEP_ID = 7, using a WHERE clause to filter the data.

---

Q5. 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?

Answer:

• ROLLBACK

Explanation: The ROLLBACK command undoes all changes made during a transaction, ensuring the database remains consistent in case of a failure. This is essential for maintaining integrity during operations like money transfers.

Databases and SQL for Data Science with Python Module 06 JOIN Statements Quiz Answers

Q1. An INNER JOIN returns only the rows that match. (T/F)

Answer:

• True

Explanation: An INNER JOIN retrieves only the rows where there is a match between the tables based on the specified condition.

---

Q2. A LEFT OUTER JOIN displays all the rows from the right table, and combines matching rows from the left table. (T/F)

Answer:

• False

Explanation: A LEFT OUTER JOIN includes all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns of the right table.

---

Q3. 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)

Answer:

• True

Explanation: SQL requires the type of OUTER JOIN to be specified explicitly (e.g., LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN). Simply using “OUTER JOIN” is not valid syntax.

---

Q4. Which of the following are valid types of JOINs?

Answer:

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

Explanation: The valid types of JOINs include INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. “FULL LEFT JOIN” is not valid.

---

Q5. A FULL JOIN returns only the rows that match. (T/F)

Answer:

• False

Explanation: A FULL JOIN includes all rows from both tables, with NULLs for columns where there is no match.

---

Q6. Which of the following is true about INNER JOINS?

Answer:

• Return relevant entries from multiple tables based on corresponding columns between them.

Explanation: INNER JOIN retrieves rows that have matching values in the specified columns across the tables being joined.

---

Q7. Consider the following query:

sqlCopy codeSELECT COLUMN1, COLUMN2
FROM TABLE1 A
LEFT JOIN TABLE2 B ON
A.COLUMN_NAME = B.COLUMN_NAME

What is the expected output?

Answer:

• All relevant entries from TABLE1 are retained, along with a few entries from TABLE2 with matching entries in COLUMN_NAME for TABLE1 and TABLE2

Explanation: A LEFT JOIN ensures all rows from the left table (TABLE1) are included in the result set, and only the matching rows from the right table (TABLE2) are included. NULLs are used for non-matching entries.

---

Q8. In MySQL, how is a FULL OUTER JOIN implemented?

Answer:

• Using a UNION of LEFT and RIGHT JOINS

Explanation: MySQL does not support FULL OUTER JOIN directly. To achieve it, a UNION of LEFT JOIN and RIGHT JOIN is used.

---

Q9. For Joins to work, which of the following is a valid statement about the columns with corresponding values?

Answer:

• The columns in the two tables may have the same or different names.

Explanation: The columns used in JOINs do not need to have the same name but must have comparable data types and values.

---

Q10. If the rows in the joined tables do not match, the result set of the full outer join contains _____ values for every column of the table that lacks a matching row.

Answer:

• NULL

Explanation: When there is no matching row in one table during a FULL OUTER JOIN, the missing values are represented by NULL.

All Quiz Answers of multiple Specializations or Professional Certificate programs:

Course 1: What is Data Science?

Course 2: Tools for Data Science

Course 3: Data Science Methodology

Course 4: Python for Data Science, AI & Development

Course 5: Python Project for Data Science

Course 6: Databases and SQL for Data Science with Python

Course 7: Data Analysis with Python