Databases and SQL for Data Science with Python

Databases and SQL for Data Science with Python | Coursera IBM

Proficiency in SQL is essential for professionals in data-related roles such as Data Scientists, Data Analysts, and Data Engineers because databases store a vast amount of global data, and SQL serves as a robust means to interact with and extract data from these databases.

Throughout this course, you will gain comprehensive knowledge of SQL, starting from fundamental SELECT statements to advanced topics like JOINs. You will learn to write basic SQL commands including SELECT, INSERT, UPDATE, and DELETE, manipulate result sets using WHERE, COUNT, DISTINCT, and LIMIT clauses, and distinguish between Data Manipulation Language (DML) and Data Definition Language (DDL). Additionally, you will practice creating, altering, dropping, and loading tables, utilizing string patterns and ranges, and organizing result sets with ORDER and GROUP BY clauses along with database functions.

Moreover, the course covers building sub-queries, querying data from multiple tables, and accessing databases using Jupyter notebooks with SQL and Python for data science tasks. You will also delve into advanced SQL concepts like Stored Procedures, Views, ACID Transactions, and Inner & Outer JOINs through practical labs and projects.

Hands-on experience will be emphasized, involving constructing SQL queries, working with actual cloud-based databases, and utilizing real-world data science tools. The culmination of the course will involve analyzing multiple real-world datasets to showcase your SQL proficiency in a final project.

Enroll Course

Notice!
Always refer to the module on your course for the most accurate and up-to-date information.

Attention!
If you have any questions that are not covered in this post, please feel free to leave them in the comments section below. Thank you for your engagement.

Graded Quiz: Basic SQL

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

SELECT DISTINCT FIRSTNAME FROM INSTRUCTOR

LEON, PAUL, JOE
LEON KATSNELSON, PAUL ZIKOPOLOUS, JOE SANTARCANGELO
LEON, PAUL, LEON, JOE
LEON, LEON, PAUL, PAUL

2. UPDATE INSTRUCTOR SET LASTNAME = 'Brewster' WHERE LASTNAME = 'Smith'

Updates all rows in the table to have the last name 'Brewster.'
Updates all rows in the table to have the last name 'Smith.'
Changes the last name of all instructors named 'Smith' to 'Brewster.'
Changes the last name of the instructor named 'Brewster' to 'Smith.'

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

The command would result in an error.
The command would only delete the first entry in the table.
The command would remove all entries in the table, leaving it empty but still present in the database.
The command would delete the table from the database.

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

SELECT COUNT(DISTINCT FIRSTNAME) FROM INSTRUCTOR

The statement would throw an error.
Only the distinct FIRSTNAME entries.
The count of unique entries along with the distinct FIRSTNAME entries.
The number of unique FIRSTNAME entries in the INSTRUCTOR table.

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

SELECT * FROM INSTRUCTOR WHERE LASTNAME='Smith' LIMIT 5

The first 5 rows from the INSTRUCTOR table.
The last 5 rows from the INSTRUCTOR table.
The last 5 entries in the INSTRUCTOR table where LASTNAME is 'Smith.'
The first 5 entries in the INSTRUCTOR table where LASTNAME is 'Smith.'

Relational DB Concepts and Tables

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

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

columns
constraints
rows
keys

3. The CREATE TABLE statement is a ________.

DML statement
DDL statement
DQL statement
All of the above

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

ALTER table command
CREATE command
DROP table command
TRUNCATE table command

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

ALTER Employees TABLE ADD ID char
ALTER TABLE COLUMN Employees ID char(7)
ALTER TABLE Employees ADD ID char(7)
ALTER Employees ADD COLUMN ID varchar(7)

Graded Quiz: Refining Your Results

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

SELECT lastname from author where lastname like ‘J*’
SELECT lastname from author where lastname like ‘J$’
SELECT lastname from author where lastname like ‘J%’
SELECT lastname from author where lastname like ‘J#’

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

SELECT ID FROM TABLE_NAME ORDER BY ID
SELECT * FROM TABLE_NAME ORDER BY ID
SELECT ID FROM TABLE_NAME ORDER BY ID DESC
SELECT * FROM TABLE_NAME ORDER BY ID DESC

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

Acts as an alternative to WHERE clause in SQL queries.
It may not necessarily organize the result set in a specific order.
Check whether data records meet the specified condition is met or not.
Restricts the result set for a query using GROUP BY clause.

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

SELECT * FROM employees ORDER BY emp_name LIMIT 5;

Retrieves the entire contents of the table, sorted alphabetically based on emp_names
Retrieves the top 5 emp_names ordered alphabetically.
Retrieves all the columns of the top 5 rows of the table, sorted reverse alphabetically based on emp_names
Retrieves all the columns of the top 5 rows of the table, sorted alphabetically based on emp_names

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

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

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

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

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

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

4. Which of the following is the correct syntax for calculating an employee’s age, in YYYY-MM-DD format, with respect to the current date, in MySQL? Assume the date of birth is available as a column ‘DOB’ in the table named ‘Employees’.

SELECT (CURRENT_DATE – DOB) FROM Employees
SELECT DATEDIFF(CURRENT_DATE, DOB) FROM Employees
SELECT FROM_DAYS(DATEDIFF(CURRENT_DATE, DOB)) FROM Employees
SELECT FROM_DAYS(DATE_SUB(CURRENT_DATE, DOB) FROM Employees

5. 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 name of the medicines and also displays their date of expiry as a column ‘DOE’? Assume use of MySQL.

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

Graded Quiz: Accessing databases using Python

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

%sql sqlite:///EMP.db
%sql
sqlite:///EMP.db
%sql sqlite:/EMP.db
%sql sqlite3://EMP.db

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

Coding in Jupyter notebook using a programming language other than Python
Converting Jupyter notebook’s default programming language to a desired one.
Timing a complete cell block as per requirement.
Load an SQL database to a jupyter notebook

3. What would be the outcome of the following python code

import sqlite3

import pandas as pd

conn = sqlite3.connect(‘HR.db’)

data = pd.read_csv(‘./employees.csv’)

data.to_sql(‘Employees’, conn)

The csv file is read and converted into an SQL table ‘Employees’ under the HR database
The CSV file is converted to an SQL file
The code throws a syntax error message.
CSV file is saved to the HR.db file created by the code.

4. What would be the correct way to query a database table using python? Assume that output in any form is acceptable. Choose the 2 correct options.

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

5. Which of the following statements would you use to perform a statistical analysis of data in a pandas dataframe ‘df’?

df.describe()
df.head()
df.tail()
df.info()

Graded Quiz on Assignment

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

53
533
433
555

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

SELECT COMMUNITY AREA NUMBER, COMMUNITY_AREA_NAME FROM CENSUS_DATA WHERE PER_CAPITA_INCOME<11000
SELECT COMMUNITY_AREA_NUMBER, COMMUNITY_AREA_NAME WHERE CENSUS_DATA FROM PER_CAPITA_INCOME<11000
SELECT COMMUNITY_AREA_NUMBER, COMMUNITY_AREA_NAME FROM CENSUS_DATA WHERE PER_CAPITA_INCOME<11000
SELECT COMMUNITY_AREA_NUMBER, COMMUNITY_AREA_NAME FROM CENSUS_DATA WHERE PER_CAPITA_INCOME>11000

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

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

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

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

COUNT
AVERAGE
DISTINCT
LIKE

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

48.0
46.42
49.62
49.52

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

SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA ___________;

ORDER BY PERCENT_HOUSEHOLDS_BELOW_POVERTY DESC LIMIT 5
ORDER BY PERCENT_HOUSEHOLDS_BELOW_POVERTY DESC
ORDER BY PERCENT_HOUSEHOLDS_BELOW_POVERTY DESC LIMIT
ORDER BY PERCENT_HOUSEHOLDS_BELOW_POVERTY LIMIT 5

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

23.0
25.0
20.0
36.0

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

SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA WHERE HARDSHIP_INDEX IN (SELECT MOST(HARDSHIP_INDEX) FROM CENSUS_DATA);
SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA WHERE HARDSHIP_INDEX AS (SELECT MAX(HARDSHIP_INDEX) FROM CENSUS_DATA);
SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA WHERE HARDSHIP_INDEX IN (SELECT MAX(HARDSHIP_INDEX) FROM CENSUS_DATA);
SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA WHERE HARDSHIP_INDEX IN (SELECT HARDSHIP_INDEX FROM CENSUS_DATA;

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

Riverdale
Austin
Englewood
Fuller Park

Final Exam

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

The table
A results set
The index
The database

2. Which of the following SQL statements will delete the customers with IDs of B8 and B9?

DELETE CUSTOMER_ID IS (‘B8’, ‘B9’) FROM CUSTOMERS
DELETE (‘B8’, ‘B9’) FROM CUSTOMERS
DELETE FROM CUSTOMERS WHERE CUSTOMER_ID IN (‘B8’, ‘B9’)
DELETE FROM CUSTOMERS WHERE CUSTOMER_ID IS (‘B8’, ‘B9’)

3. What does the primary key of a relational table do?

The primary key uniquely identifies each column in a table.
The primary key uniquely identifies each row in a table.
The primary key uniquely identifies each attribute in a table.
The primary key uniquely identifies each relation in a table.

4. The basic categories of the SQL commands based on functionality are Data Definition Language (DDL) and _________.

Data Update Language (DUL)
Data Manipulation Language (DML)
Data Entry Language (DEL)
Data Input Language (DIL)

5. When querying a table called Representative that contains a list of representatives and the state that they represent, which of the following queries will return the number of representatives from each state?

SELECT State, COUNT(State) FROM Representative
SELECT State, DISTINCT(State) FROM Representative GROUP BY State
SELECT DISTINCT(State) FROM Representative
SELECT State, COUNT(State) FROM Representative GROUP BY State

6. You want to retrieve a list of employees with 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:

SELECT First_Name, Last_Name, Age FROM Company

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

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

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

8. Which of the following queries will retrieve the PRODUCT NAME that has the LOWEST price?

SELECT PRODUCT_NAME FROM PRODUCTS WHERE UNIT_PRICE = (SELECT MIN(UNIT_PRICE) FROM PRODUCTS)
SELECT PRODUCT_NAME FROM PRODUCTS WHERE UNIT_PRICE IS LOWEST
SELECT MIN(UNIT_PRICE) FROM PRODUCTS
SELECT PRODUCT_NAME FROM PRODUCTS WHERE UNIT_PRICE = MIN

9. A ____________ is a control structure that enables traversal over the records in a database.

Import
Primary key
Database cursor
Connection

10. You are provided with a python statement “df.to_sql(‘Sample’, conn)”. What do ‘df’, ‘Sample’ and ‘conn’ refer to?

df – data frame; Sample – sample information; conn – connection variable
df – data format; Sample – sample information; conn – data connector
df – data frame; Sample – table name ; conn – connection variable
df – data format; Sample – table name ; conn – data connector

TeamsCloud

Databases and SQL for Data Science with Python

Graded Quiz: Basic SQL

Relational DB Concepts and Tables

Graded Quiz: Refining Your Results

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

Graded Quiz: Accessing databases using Python

Graded Quiz on Assignment

Final Exam

Post a Comment

Introduction to Cybersecurity Tools & Cyber Attacks | Coursera Quiz Answers

IBM Cybersecurity Analyst Professional Certificate Assessment Exam

Project Lifecycle, Information Sharing, and Risk Management | Coursera Quiz Answers

Cybersecurity Roles, Processes & Operating System Security | Coursera Quiz Answers

TeamsCloud