CockroachDB for Python Developers Final Exam Answers – Cockroach University

Question 1: Use the IMPORT statement to import data from a CSV file into a new table called final_exam_users.

• The table columns should be:
  • id (UUID, primary key)
  • last_name (string)
  • first_name (string) 
• The data is in https://cockroach-university-public.s3.amazonaws.com/10000final_exam_user_data.csv
• The CSV file uses the | symbol as its delimiter.

To demonstrate that you have imported the table correctly, run the following query to determine how many users have the last name “Salazar” and a first name that begins with the letter “D”?:

SELECT COUNT(*) 
FROM final_exam_users 
WHERE last_name = 'Salazar' AND first_name LIKE 'D%';

Enter the answer (the value in the count column) below.

ANSWER: 2

Question 2 : How do you create a CockroachDB cluster with CockroachCloud?

• Create an account at cockroachlabs.cloud and use the “Create Cluster” button
• Install CockroachDB and run cockroach start
• Connect to the CockroachDB SQL shell and run CREATE CLUSTER
• Contact your account manager at Cockroach Labs

Question 3: : Imagine you have a table with the following schema:

CREATE TABLE final_exam_users (
    id UUID PRIMARY KEY,
    last_name STRING,
    first_name STRING
)

The table has three indexes:

• An index on last_name
• An index on last_name, first_name
• An index on first_name

Consider the following queries:

SELECT * FROM final_exam_users 
WHERE last_name = 'Khan';

SELECT * FROM final_exam_users 
WHERE last_name = 'Khan' AND first_name = 'Sean';

SELECT * FROM final_exam_users 
WHERE first_name = 'Sean';

Which index could you eliminate and still answer the above queries efficiently?

• The index on just the last_name column
• The index on both the last_name and first_name columns
• The index on just the first_name column

Question 4: Using the IMPORT statement below, create and populate the final_json_data table.

Answer: 5024858.67

Question 5: Assuming this executes successfully within a transaction, what will the code snippet do? (Choose all that apply.)

• Updates an existing location history row
• Updates an existing vehicle row
•  Updates an existing location history row
• Resets the vehicle associated with a location history
• Finds all the location history entries for a vehicle
• Finds a vehicle by its ID
• Adds a new vehicle row
• Adds a new location history row

Question 6: Covered queries improve performance by using data stored in an index. This allows them to avoid join operations between the index and the primary table.

You already have an index on region and hire_date. What columns can you include with STORING to avoid performing a join? (Choose all that apply)

• id
• hire_date
• region
• level
• salary
• A join cannot be avoided with this query

Question 7: Imagine you have a table called authors, which you create and populate as follows: 

CREATE TABLE authors (
    id INT PRIMARY KEY, 
    name STRING);

INSERT INTO authors VALUES (1, 'Toni Morrison'),
                           (2, 'James Tiptree'),
                           (3, 'Richard Wright');

You then add a second table called books:

CREATE TABLE books (
	book_id INT PRIMARY KEY, 
	author_id INT REFERENCES authors(id), 
	title STRING);

You use INSERT INTO to add a row to the books table:

INSERT INTO books VALUES (1, 4, 'Native Son');

Which of the following will occur? (Check all that apply.)

• A new row will be added to books with book_id=1, author_id=4, title=NULL
• A new row will be added to books with book_id=1, author_id=4, title=”Native Son”
• A new row will be added to authors with id=4, book_id=1, name=NULL
• An error will occur

Question 8: Imagine you have a table created as follows:

CREATE TABLE brands (
    brand_id UUID PRIMARY KEY,
    make STRING,
    model STRING
);

Which of the following class definitions correctly models the table in SQLAlchemy?

Answer: 3rd Option

Class Brand(Base):
    __tablename__ = 'brands'
    brand_id = Column(UUID)
    make = Column(String)
    model = Column(String)
    PrimaryKeyConstraint(brand_id)

    def __repr__(self):
        return ("(brand_id='{0}')".format(self.brand_id))

Question 9: Imagine you have a table created with the following schema:
CREATE TABLE customers ( id UUID PRIMARY KEY, name STRING, address STRING, state STRING, zip STRING );
You want to optimize the following query:
EXPLAIN SELECT id, zip FROM customers WHERE state='NY';
Which of the CREATE INDEX statements below would allow this query to avoid a full table scan (choose any that apply):



CREATE INDEX ON customers(state);

CREATE INDEX ON customers(zip);

CREATE INDEX ON customers(id);

CREATE INDEX ON customers(state,zip);-

CREATE INDEX ON customers(id,zip);

CREATE INDEX ON customers(id,zip,state);

Question 10: Which of the query plans below correspond to this query:
EXPLAIN ANALYZE SELECT * FROM vehicles WHERE battery < 20;


Answer: 4th Option