Databases and SQL for Data Science with Python Quiz Answers – 100% Correct

All Weeks Databases and SQL for Data Science with Python Quiz Answers

Lab Assessment 01

Quiz 01: Databases

Q1. Which of the following statements are correct about databases:

• A database is a repository of data
• There are different types of databases – Relational, Hierarchical, No SQL, etc.
• A database can be populated with data and be queried
• All of the above

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

• A database is a logically coherent collection of data with some inherent meaning
• Data can only be added and queried from a database but not modified
• Only SQL can be used to query data in a database
• All of the above

Q3. Select the correct statement below about database services or database instances:

• Database services are logical abstractions for managing workloads in a database
• An instance of the cloud database operates as a service that handles all application requests to work with the data in any of the databases managed by that instance
• The database service instance is the target of the connection requests from applications
• All of the above

Q4. The 5 basic SQL commands are…

• CREATE, SELECT, INSERT, UPDATE, DELETE
• SELECT, COPY, PASTE, INSERT, ALTER
• CREATE, INSERT, RETRIEVE, MODIFY, DELETE
• None of the above

Q5. A database stores data in tabular form only. True or False?

• True
• False

Q6. What is the function of a WHERE clause in an UPDATE statement?

• A WHERE clause enables you to specify a new table to receive the updates.
• A WHERE clause is never used with an UPDATE statement.
• A WHERE clause enables you to specify which rows will be updated.
• A WHERE clause enables you to list the column and data to be updated.

Q7. Which of the following statements would you use to add a new instructor to the Instructor table.

• ADD INTO Instructor(ins_id, lastname, firstname, city, country)

VALUES(4, ‘Doe’, ‘John’, ‘Sydney’, ‘AU’);

• INSERT INTO Instructor(ins_id, lastname, firstname, city, country)

VALUES(4, ‘Doe’, ‘John’, ‘Sydney’, ‘AU’);

• UPDATE Instructor(ins_id, lastname, firstname, city, country)

WITH VALUES(4, ‘Doe’, ‘John’, ‘Sydney’, ‘AU’);

• SELECT Instructor(ins_id, lastname, firstname, city, country)

FROM VALUES(4, ‘Doe’, ‘John’, ‘Sydney’, ‘AU’);

Q8. You are working on a Film database, with a FilmLocations table. You want to retrieve a list of films that were released in 2019. You run the following query but find that all the films in the FilmLocations table are listed.

SELECT Title, ReleaseYear, Locations FROMFilmLocations;

What is missing?

• Nothing, the query is correct.
• A WHERE clause to limit the results to films released in 2019.
• A DISTINCT clause to specify a distinct year.
• A LIMIT clause to limit the results to films released in 2019.

Quiz 02: Basic SQL

Q1. The primary key of a relational table uniquely identifies each row in a table. True or False?

• True
• False

Q2. The INSERT statement cannot be used to insert multiple rows in a single statement. (T/F)

• True
• False

Q3. The SELECT statement is called a Query, and the output we get from executing the query is called a ResultSet.

• True
• False

Q4. The CREATE TABLE statement is a….

• DML statement
• DDL statement
• Both of the above

Q5. What are the basic categories of the SQL language based on functionality?

• Data Definition Language
• Data Manipulation Language
• Both of the above

Week 02 – Practice Quiz 01

Q1. What is the function of a primary key?

• The primary key is used to grant access to a table.
• The primary key enables you to add data to columns.
• The primary key uniquely identifies each row in a table.
• The primary key is used to identify any rows in the table that contain NULL values.

Q2. True or False: Data Manipulation Language statements like INSERT, SELECT, UPDATE, and DELETE are used to read and modify data.

• True
• False

Q3. Data Definition Language (or DDL) statements are used to define, change, or delete database objects such as tables. Which of the following statements are all DDL statements?

• INSERT and UPDATE
• SELECT and DELETE
• SELECT, INSERT, UPDATE
• CREATE, ALTER, DROP

Q4. Which of the following queries will change the data type of an existing column (phone) to the varchar data type?

• ALTER TABLE author ALTER COLUMN phone SET TYPE VARCHAR(20);
• ALTER COLUMN phone SET DATA TYPE VARCHAR(20);
• ALTER TABLE author ALTER COLUMN phone SET DATA TYPE VARCHAR(20);
• ALTER TABLE author ALTER COLUMN phone DATA TYPE = VARCHAR(20);

Q5. The five basic SQL commands are:

• CREATE, INSERT, RETRIEVE, MODIFY, DELETE
• SELECT, COPY, PASTE, INSERT, ALTER
• CREATE, SELECT, INSERT, UPDATE, DELETE
• None of the above

Graded Quiz: Relational DB Concepts and Tables

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

• A database is a logically coherent collection of data with some inherent meaning
• Data can only be added and queried from a database, but not modified.
• Only SQL can be used to query data in a database.
• All of the above

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

• rows
• columns
• constraints
• keys

Q3. The CREATE TABLE statement is a…

• DML statement
• DDL statement
• Both of the above

Week 03 – Practice Quiz 01

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

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

• SELECT
• WHERE
• ORDER BY
• HAVING

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;

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

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

Graded Quiz: Refining Your Results

Q1. You want to select the 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

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

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

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

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

• Built-in database functions may increase processing time.
• Built-in database functions may increase network bandwidth consumed.
• 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.

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

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

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

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

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

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

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

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

Graded Quiz: Functions, Sub-Queries, Multiple Tables

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

( SELECT DEPT_ID_DEP FROM DEPARTMENTS WHERE DEPT_ID_DEP IS MAX )

• 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 )

Q2. A DEPARTMENTS table contains DEP_NAME, and DEPT_ID_DEP columns and an EMPLOYEES table contain 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 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, DEPARTMENTS WHERE DEPT_ID_DEP = DEP_ID

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

Week 04 – Practice Quiz

Q1. Which API do you use to connect to a database from Python?

• REST API
• Watson API
• DB API such as ibm_db API
• Census API

Q2. In the ibm_db API, what is the commit() method used for?

• The commit() method is used to reverse any transactions that fail.
• The commit() method is used to close a database connection.
• The commit() method is used to open a database for connection.
• The commit() method is used to commit any pending transaction to the database.

Q3. True or false: Resources used by the ibm_db API are released automatically when the program ends. There is no need to specifically close the connection.

• False
• True

Q4. To create a table from Python, you would use…

• An ibm_db.exec_immediate function that includes a SQL statement to create the table.
• An ibm_db.exec_immediate function that includes connection information and a SQL statement to create the table.
• You cannot create a table from Python.
• An ibm_db.exec_immediate function that includes connection information.

Q5. Which of the following is a correct example of the connect function?

• connect(‘database name’, ‘username’, ‘password’)
• connect(‘database port’, ‘username’, ‘password’)
• connect(‘username’, ‘password’)
• connect(‘database name’, ‘username’, ‘database type’)

Graded Quiz: Database access from Python

Q1. The ibm_db API provides a variety of useful Python functions for accessing and manipulating data in an IBM data server like Db2. Is this statement True or False?

• True
• False

Q2. A Dataframe represents a tabular, spreadsheet-like data structure containing an ordered collection of columns, each of which can be a different value type. Indicate whether the following statement is True or False:

A pandas data frame in Python can be used for storing the result set of a SQL query.

• True
• False

Q3. Which of the following statement(s) about Python is NOT correct (i.e. False)?

• The Python ecosystem is very rich and provides easy to use tools for data science.
• Due to its proprietary nature, database access from Python is not available for many databases.
• There are libraries and APIs available to access many of the popular databases from Python.
• Python is a popular scripting language for connecting and accessing databases.

Final Exam Quiz Answers

Q1. The SELECT statement is called a query, and the output we get from executing the query is called what?

• The index
• The table
• A results set
• The database

Q2. Which of the following SQL statements will delete the customers where the Country is Italy?

• DELETE FROM CUSTOMERS WHERE COUNTRY = ‘ITALY’
• DELETE FROM CUSTOMERS WHERE COUNTRY IS ‘ITALY’
• DELETE COUNTRY ‘ITALY’ FROM CUSTOMERS
• DELETE ‘ITALY’ FROM CUSTOMERS

Q3. The primary key of __________ uniquely identifies each row in a table.

• A customer
• A name
• A relational table
• A database

Q4. What are the basic categories of the SQL language based on functionality?

• Data Definition Language
• None of the above
• Both of the above
• Data Manipulation Language

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

• SELECT City, distinct(City) FROM Author GROUP BY City
• SELECT City, count(City) FROM Author GROUP BY City
• SELECT distinct(City) FROM Author
• SELECT City, count(City) FROM Author

Q6. You want to retrieve a list of employees by first name and last name for a company that are between the ages of 30 and 50. Which clause would you add to the following SQL statement: SELECTFirst_Name,Last_Name, Age FROM Company

• WHERE Age < 30
• WHERE Age >=30 AND Age <=50
• IF Age >=30 AND Age <=50
• WHERE Age > 30

Q7. Which of the following queries will retrieve the HIGHEST value of SALARY in a table called EMPLOYEES?

• SELECT MAX(SALARY) FROM EMPLOYEES
• SELECT SALARY FROM EMPLOYEES WHERE MAX
• SELECT MIN(SALARY) FROM EMPLOYER
• SELECT MOST(SALARY) FROM EMPLOYEES

Q8. Which of the following queries will retrieve the PRODUCT NAME that has the highest price?

• SELECT PRODUCT_NAME FROM PRODUCTS WHERE UNIT_PRICE = (SELECT MAX(UNIT_PRICE) FROM PRODUCTS)
• SELECT PRODUCT_NAME FROM PRODUCTS WHERE UNIT_PRICE = MAX
• SELECT PRODUCT_NAME FROM PRODUCTS WHERE UNIT_PRICE IS HIGHEST
• SELECT MAX(UNIT_PRICE) FROM PRODUCTS

Q9. A database cursor is a control structure that enables traversal over the records in a database. (T/F)

• False
• True

Q10. To query data from tables in database a connection to the database needs to be established. Which of the following is NOT required to establish a connection with a relational database from a Python notebook?

• Table name
• Hostname or IP address
• Database Name
• Username and Password

Week 06 – Practice Quiz

Q1. Which of the following statements about views is correct?

• You cannot change data in the base tables through a view.
• 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.
• A view is an independent copy of a single table’s structure, including the data.

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

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;

• CREATE VIEW JobSalaryRanges(Job, StartingSalary, MaximumSalary)

FROM JOBS

WHERE MIN_SALARY > 50000 AND MAX_SALARY < 100000;

Q3. The benefits of stored procedures include which of the following?

• Reuse of code
• All of the above
• Improvement in performance
• Reduction in network traffic

Q4. Which of the following can a stored procedure use?

• Output parameters
• Input parameters
• Input and output parameters
• A stored procedure cannot accept parameters.

Q6. What does ACID stand for?

• Alternative, Creative, Isolated, Durable
• Asynchronous, Complete, Individual, Direct
• Atomic, Consistent, Isolated, Durable
• Atomic, Consistent, Initiated, Duplicated

Practice Quiz Answers

Q1. You usually create a join between…?

• The primary keys in each table.
• Any column in either table.
• The primary key in one table to the foreign key in another table.
• The foreign keys in each table.

Q2. 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 outer join
• Right outer join
• Full outer join
• Left inner join

Q3. Which of the following statements correctly uses an INNER JOIN?

• SELECT * FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.DEP_ID = d.DEP_ID
• CREATE INNER JOIN BETWEEN EMPLOYEES e AND DEPARTMENTS d ON e.DEP_ID = d.DEP_ID
• SELECT * FROM EMPLOYEES INNER JOIN DEPARTMENTS ON DEP_ID = DEP_ID
• SELECT * FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON DEP_ID

Q3. Which of the following are three valid types of outer join?

• Left outer join, right outer join, full outer join
• Left outer join, right outer join, left/right outer join
• Left outer join, right outer join, both outer join
• Left outer join, right outer join, total outer join

Q5. Which type of join would you use to select all the rows from both tables?

• Total outer join
• Right outer join
• Full outer join
• Left outer join

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