Module 10: Database & SQL

If we had the following SQL statement in the code: sql = "insert into book values (?, ?, ?, ?, ?)" What must be provided along with this SQL statement?

A tuple must be provided. It must contain five items. The order of the values must follow the order used in the original create table statement.

# assume we have a db_cursor objecti = int(input("Enter the isbn you wish to update: "))p = float(input("Enter the new price: "))sql = "update book set price = ? where isbn = ?" When we call execute for this sql statement, what must we provide regarding a tuple?

A tuple must be provided. It must contain the price value first and then the isbn value second.

When we call execute on a sql statement that contains question marks ? , what must be we do when we call excecute?

Pass in a tuple that contains the values. There should be one value for each ? in the SQL statement.

The SQL command for the R (retrieve or read) action is:

SELECT

When we wrote a Python program that worked with a database, we performed "sql binding". What is it?

When we call execute and we bind the values in a tuple with the ? in the SQL statement.

# assume we have a db_cursor objecti = int(input("Enter the isbn you wish to update: "))p = float(input("Enter the new price: "))sql = "update book set price = ? where isbn = ?" Which of these statements should appear next in the code?

tuple_of_values = (p, i)db_cursor.execute(sql, tuple_of_values)

Which of these SQL commands are considered to be "DML"? (check all that apply)

update select insert delete

Assume I have a database table named book with columns: isbn, title, author, price. Assume I would like to write a Python application that will ask the user to enter a new price and the isbn and the program will update the price for that particular isbn. What is the best SQL statement I should include in the code?

update book set price = ? where isbn = ?

The SQL command for the U (update) action is:

UPDATE

What is true about a for loop:

-it can be used with a tuple to iterate over a tuple - it can be used with a list to iterate over a list - it can be used with any type that is considered to be "iterable" because we can iterate over that type.

# assume we have a db_cursor objecti = int(input("Enter the isbn you wish to update: "))p = float(input("Enter the new price: "))sql = "update book set price = ? where isbn = ?" What should be the next step we perform in our code?

Execute the SQL statement using the db_cursor object, providing a tuple with 2 values. No need to read the result set. Then do a commit at some point to save the data to the database.

# assume we have a db_cursor object in our codesql = "select * from book"What should be the next step we perform in our code?

Execute the SQL statement using the db_cursor object. No need to provide a tuple of values. The result set will be returned. Then read the result set using a for loop.

The SQL command for the C (create) action is:

INSERT

After we excecute a SQL statement, when must we get back a result set and loop through that result set? (check all that apply)

If the SQL statement is for an select command.

The result set that is returned from database is similar to a list, meaning that we can iterate over the result set using a for loop.

True

When we used sqlite3, we called execute(). execute is:

a method

Which of these SQL commands are considered to be "DDL"? (check all that apply)

create drop

After which of these database commands should do a commit? (check all that apply)

delete insert update