Databases and SQL for Data Science with Python #ditkhitacontre

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.

A WHERE clause enables you to list the column and data to be updated.

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.

A WHERE clause to limit the results to films released in 2019.

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

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

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

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

Q3. The primary key of __________ uniquely identifies each row in a table. A customer A name A relation table A database

A relation table

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

A results set

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

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

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

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

All of the above

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

All of the above

Q3. Which of the following can be used in a SELECT statement to restrict a result set? HAVING WHERE DISTINCT All of the above

All of the above

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.

An ibm_db.exec_immediate function that includes connection information and a SQL statement to create the table.

Q6. What does ACID stand for? Alternative, Creative, Isolated, Durable Asynchronous, Complete, Individual, Direct Atomic, Consistent, Isolated, Durable Atomic, Consistent, Initiated, Duplicated

Atomic, Consistent, Isolated, Durable

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

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

Both of the above

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.

Built-in database functions reduce the amount of data that is retrieved.

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

CREATE, ALTER, DROP

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

CREATE, SELECT, INSERT, UPDATE, DELETE

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

CREATE, SELECT, INSERT, UPDATE, DELETE

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

DB API such as ibm_db API

Q4. The CREATE TABLE statement is a.... DML statement DDL statement Both of the above

DDL statement

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

DELETE FROM CUSTOMERS WHERE COUNTRY = 'ITALY'

Q3. The CREATE TABLE statement is a... DML statement DDL statement Both of the above

DML statement

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.

Due to its proprietary nature, database access from Python is not available for many databases.

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

F

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

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

False

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

Full outer join

Q2. Which keyword is used to set a condition for a GROUP BY clause? SELECT WHERE ORDER BY HAVING

HAVING

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

Hostname or IP address

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) WITH 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');

INSERT INTO Instructor(ins_id, lastname, firstname, city, country) WITH VALUES(4, 'Doe', 'John', 'Sydney', 'AU');

Q4. Which of the following can a stored procedure use? Output parameters Input parameters Input and output parameters A stored procedure cannot accept parameters.

Input and output parameters

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

Left inner join

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

Left outer join, right outer join, full outer join

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

ORDER BY clause

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');

SELECT * FROM Author WHERE Country BETWEEN('Australia', 'Canada', 'India');

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 )

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

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;

SELECT * FROM Employees ORDER BY Lastname;

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

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

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

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

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';

SELECT DAYOFWEEK(RescueDate) From PetRescue WHERE Animal = 'Dog';

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

SELECT F_NAME, DEP_NAME FROM EMPLOYEES, DEPARTMENTS WHERE DEPT_ID_DEP = DEP_ID

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

SELECT MIN(SALARY) FROM EMPLOYER

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

SELECT PRODUCT_NAME FROM PRODUCTS WHERE UNIT_PRICE = (SELECT MAX(UNIT_PRICE) FROM PRODUCTS)

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

SELECT SUM(Cost) AS Total_Cost FROM PetRescue

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;

SELECT Title, Price FROM Book WHERE Price IN (10, 25); SELECT Title, Price FROM Book WHERE Price BETWEEN 10 and 25;

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

SELECT lastname from author where lastname like 'B%'

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.

Sub-queries, Implicit joins, JOIN operators

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.

The commit() method is used to commit any pending transaction to the database.

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.

The primary key uniquely identifies each row in a table.

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

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

True

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

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

True

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

True

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

True

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

WHERE Age >=30 AND Age <=50

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';

WHERE Firstname = 'Ed';

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.

When you define a view, only the definition of the view is stored, not the data that it represents.

Q2. Attributes of an entity become ________ in a table. rows columns constraints keys

columns

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

connect('database name', 'username', 'password')