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
andfirst_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
withbook_id
=1,author_id
=4,title
=NULL - A new row will be added to
books
withbook_id
=1,author_id
=4,title
=”Native Son” - A new row will be added to
authors
withid
=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 theCREATE 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