Checkpoints 4-6: Quiz Questions

Checkpoint 4

1. Which of the following are valid keywords in a basic select statement?

a) SELECT, FROM, and WHERE ← Correct

b) EXTRACT, FROM

c) GET, FROM, and WHERE

d) SELECT, IN

Explanation:
Aside from the SELECT keyword, the statement must also contain the FROM keyword to specify the table or set of tables to retrieve data from. Select statements may also contain optional clauses such as WHERE, GROUP BY, HAVING, etc.

Objective
Remember: List the keywords in a basic select statement

Understand: Describe the structure of an SQL query

Covered in:
SELECT statements

2. Which of the following is not a possible SELECT statement? (You are not referring to a specific table so assume that the column names and table names exist)
a) SELECT column_name FROM table_name;

b) SELECT * FROM table_name;

c) SELECT DISTINCT column_name FROM table_name;

d) SELECT ONLY column_name FROM table_name; ← Correct

Explanation: SELECT column_name FROM table_name; will select a specific column from a table and is a valid SQL query. SELECT * FROM table_name will select all columns from a table and is a valid SQL query. SELECT DISTINCT column_name FROM table_name will select only distinct columns from a table and is a valid SQL query. This can be helpful when a column has entries that contain identical values. SELECT ONLY column_name FROM table_name is an invalid SQL query and no such query exists.

Objective
Understand: Describe the structure of an SQL query Remember: List the order of operators

Covered in:
SELECT statements

3. Which of the following are valid comparison operators?

a) >

b) LIKE

c) !=

d) All of the above ← Correct

Explanation: Comparison operators are used to compare values in relation to each other. The > operator checks of the first operand to the left of the ">" is greater than the second operand to the right. The LIKE operator is used with the wildcard * symbol to compare for matching values. The != operator checks if two values are not equal to each other.

Objective
Remember: List the different comparison operators

Understand: Explain how the different comparison operators work

Covered in:
SELECT statements

4. Which query selects ALL fields from the customers table?

a) SELECT * FROM customers; ← Correct

b) SELECT first_name FROM customers;

c) SELECT customer_id, first_name FROM customers;

d) SELECT ALL FROM customers;

Explanation: The SELECT clause has a special operator, *, that returns ALL of the columns from the table (or set of tables) being queried. Note that if a WHERE clause is included, the resulting set only returns the rows that satisfy the specified conditions.

Objective Remember: Write the options for the SELECT keyword (*, DISTINCT, column)

Covered in: SELECT statements

5. Which of the following is the correct syntax to query a customers table for customers with the last name “Smith”?

a) SELECT * FROM customers WHERE last_name = 'Smith'; ← Correct

b) SELECT * FROM customers WHERE last_name == 'Smith';

c) SELECT * FROM customers WHERE last_name === 'Smith';

d) SELECT * FROM customers WHERE last_name LIKE 'Smith';

Explanation:
In contrast to many programming languages, SQL uses a single = symbol to mean equal to instead of ==. In the query above, the = is used to query the Customers table for customers with the last name equivalent to "Smith". The = symbol may also be used to check the equality of other data types, for instance, numbers. Note that whenever equality is checked for between strings, quotes must be used.

Objective
Remember: Write the options for the SELECT keyword (*, DISTINCT, column) Apply: Use the different comparison operators in a query

Covered in:
SELECT statements

6. Referring to the relational schema below, which query will return the serial_number and model of any computer that is not been purchased?

computer(serial_number, manufacturer, model, color)

purchase(serial_number, ssn)

person(ssn, first_name, last_name, address)

a) SELECT * FROM computer;

b) SELECT c.serial_number, c.model FROM computer c;

c) SELECT c.serial_number, c.model FROM computer c WHERE c.serial_number NOT IN ( SELECT p.serial_number FROM purchase p); ← Correct

d) SELECT c.serial_number, c.manufacturer FROM computer c, purchase p WHERE c.serial_number = p.serial_number;

Explanation:
The question asks for the serial_number and model of any computer that has not been purchased. Therefore, the first step is to select serial_number and model from the computer table. This brings us to SELECT c.serial_number, c.model FROM computer c. Next, there needs to be a WHERE clause in order to see which computers have not been purchased. WHERE c.serial_number NOT IN (SELECT p.serial_number FROM purchase p) will return the serial_number’s from the own table of all serial_number’s that are not contained within the table computer. In other words, this means that only serial_number’s that are unique to the computer table will be returned. If the serial_number is associated with both the computer table and the own table then that means some person has purchased that computer, while if the serial_number is only associated with the computer table than no person has purchased that computer.

Objective
Understand: Explain how the logical operators AND, OR, and NOT are used

Apply: Use the different logical operators in a query

Covered in:
SELECT statements

---

Checkpoint 5

1. Which of the following is NOT an aggregate function?

a) SUM

b) COUNT

c) DISTINCT ← Correct

d) MAX

Explanation: The DISTINCT keyword is not used to aggregate (combine) data. Rather, it is used to return only unique rows of selected data. In other words, for any set of duplicate rows, only one copy of each set will be returned.

Objective Remember: List the different aggregate functions (SUM, AVG, COUNT, etc.)

Covered in: Aggregate Functions

2. Referring to the transactions table below, what will the following query return?

SELECT   SUM(cost), customer_id       
FROM     transactions        
GROUP BY customer_id
HAVING   SUM(cost) > 50;

trans_id	customer_id	cost	date
00122	00001	$100.00	07-10-2017
00092	00001	$24.00	04-19-2015
00103	00003	$25.00	07-10-2017
00004	00002	$75.00	03-18-2017

a) The record: “200”

b) The records: “100, 00001”, “50, 00001”, “75, 00002”, “25, 00003”

c) The records: “150, 00001”, “75, 00002” ← Correct

d) None of the above

Explanation:
The above SQL statement retrieves the sum of costs grouped by customer_id from the transactions table. Since there are two transactions with customer_id of “00001”, the costs from those two transactions are summed together. Lastly, the HAVING clause specifies that query only return records with a total cost of transactions greater than $50.

Objective
Remember: List ordering and grouping clauses and their options (GROUP BY, ORDER BY [ASC/DESC], LIMIT/OFFSET, HAVING)

Analyze: Determine the effects of aggregate functions on a query and construct the return set

Apply: Execute ordering, grouping, and aggregate functions

Covered in:
Aggregate Functions

3. Which of the following selects all records from a table named "customers" where the "last_name" starts with the letter "A" in ascending order?

a) SELECT * FROM Customers WHERE last_name = 'A%' ORDER BY last_name;

b) SELECT * FROM Customers WHERE last_name LIKE '%A%' ORDER BY last_name ASCN;

c) SELECT * FROM Customers WHERE last_name LIKE 'A%' ORDER BY last_name ASC; ← Correct

d) SELECT * FROM Customers WHERE last_name = 'A%' SORT BY last_name;

Explanation: The LIKE operator is used to find matching values. WHERE last_name LIKE 'A%' finds all matching records with last_name starting with "A." Lastly, ORDER BY last_name ASC sorts the result set by the last_name field, alphabetically, in ascending order.

Objective Apply: Write your own queries to deliver these results.

Covered in: Aggregate Functions

Checkpoint 6

1. Referring to the CREATE TABLE statement below, why is ID a primary key?

CREATE TABLE students (
    id 		     INT NOT NULL,
    last_name 	     VARCHAR(50) NOT NULL,
    first_name	     VARCHAR(50) NOT NULL,
    gpa		     FLOAT NOT NULL,
    PRIMARY KEY (id)
);

a) Because last_name is NOT NULL.

b) Because if a table contains an id element, than id must always be a primary key.

c) Because it is good practice.

d) Because each student entry from students needs to be uniquely identifiable by an id. ← Correct

Explanation: A benefit from using primary keys is that each primary key gets an index. What this means is that there can only be one distinct value for each primary key element. In other words, every id in the students table must be unique. This is also known as the uniqueness constraint. In a practical application, this would allow for each student in the students database to have a unique id to be identified by.

Objective: Remember: Define primary key and foreign key

Covered in: Joins

2. Which of the following is a difference between primary key and foreign key?

a) A primary key can be null, while a foreign key cannot.

b) A primary key can be duplicate, while a foreign key must always be unique.

c) A primary key must always be unique, while a foreign key can be duplicate. ← Correct

d) None of the above

Explanation: One main difference between primary key and foreign key is that a foreign key can be duplicate, while a primary key must always be unique. Primary key does not have to be explicitly defined as UNIQUE.

Objective: Remember: Define primary key and foreign key

Covered in: Joins

3. Which of the following is not a characteristic of a JOIN clause?

a) Combines multiple tables into one.

b) Creates and drops multiple tables ← Correct

c) Creates a new table with information from the joined tables.

d) Retrieve data from two or more tables.

Explanation: A JOIN clause combines multiple tables into one. This is helpful for pulling related data from multiple tables. A JOIN creates a new table which has some information from each table.

Objective: Understand: Explain how joins work

Covered in: Joins

4. Which of the following is not a valid type of join?

a) BETWEEN JOIN ← Correct b) INNER JOIN c) RIGHT JOIN d) OUTER JOIN

Explanation: Valid types of JOINS include: INNER, RIGHT, LEFT, OUTER, NATURAL, CROSS, and SELF JOINS.

Objective Remember: List the different types of joins

Covered in: Joins

5. Referring to the listed tables, what does the following query return?

SELECT first_name, last_name 
FROM   customers, transactions
WHERE  customers.customer_id = transactions.customer_id
AND    transactions.cost > 50;

customers:

customer_id	first_name	last_name
00001	Jane	Smith
00002	Bob	Adams
00003	Mike	Johnson
00004	Jenn	Nelson

transactions:

trans_id	customer_id	cost	date
00122	00001	$100.00	07-10-2017
00092	00001	$24.00	04-19-2015
00103	00003	$25.00	07-10-2017
00004	00002	$75.00	03-18-2017

a) The records: “Jane, Smith” and “Jenn, Nelson” ← Correct

b) The records: “Jane, Smith”, “Jenn, Nelson”, and “Bob Adams”

c) The records: “00001, Jane, Smith” and “00004, Jenn, Nelson”

d) None of the above

Explanation: This SQL statement joins the customers and transactions tables together by the customer_id field. For every matching customer_id in the transactions table, the first and last names of those customers are returned if they have a transaction with a cost greater than $50.

Objective Apply: Use different types of joins to retrieve data

Covered in: Joins

6. Which SQL statement would result in the following data set from the customers and transactions tables?

result:

first_name	last_name	trans_id
Bob	Adams	84
Jane	Smith	92
Jane	Smith	122
Jenn	Nelsons	(null)
Mike	Johnson	103

customers:

customer_id	first_name	last_name
00001	Jane	Smith
00002	Bob	Adams
00003	Mike	Johnson
00004	Jenn	Nelson

transactions:

trans_id	customer_id	cost	date
00122	00001	$100.00	07-10-2017
00092	00001	$24.00	04-19-2015
00103	00003	$25.00	07-10-2017
00004	00002	$75.00	03-18-2017

a) SELECT c.first_name, c.last_name, t.trans_id FROM customers c INNER JOIN transactions t ON c.customer_id = t.customer_id;

b) SELECT c.first_name, c.last_name, t.trans_id FROM customers c OUTER JOIN transactions t ON c.customer_id = t.customer_id;

c) SELECT c.first_name, c.last_name, t.trans_id FROM customers c RIGHT JOIN transactions t ON c.customer_id = t.customer_id;

d) SELECT c.first_name, c.last_name, t.trans_id FROM customers c LEFT JOIN transactions t ON c.customer_id = t.customer_id; ← Correct

Explanation: In the result set, we see that there is data from both the customers table and the transactions table. Note that the queries are using aliases ("c" represents the customers table and "t" represents for the transactions table). Specifically, the first_name and last_name fields are from the customers table. While the trans_id field is from the transactions table. The biggest clue that the SQL statement used a LEFT JOIN is that we see a record with a NULL trans_id. The LEFT JOIN in our query means that we are return all records from the table on the left table (i.e, customers), whether or not there is a match of that customer_id in the right table (i.e, transactions). Since there is no record in the transactions table for Jenn Nelson, the result will simply have a NULL for the corresponding trans_id.

Objective: Remember: Define aliasing Apply: Use different types of joins to retrieve data

Covered in: Joins