Module 7.5.2 Quiz - Introduction to Structured Queries Answers
1. Which two SQL statements are used to create and change a database schema? (Choose two.)
- ALTER
- CREATE
- DELETE
- INSERT
- SELECT
Explanation: ALTER and CREATE are Data Definition Language (DDL) statements while DELETE, INSERT, and SELECT are Data Manipulation Language (DML) statements.
2. Refer to the exhibit. You need to get information about customer purchases that is stored in the two tables. You would like to see data from the OrderID, CustomerName, OrderAmount, and OrderDate columns. To obtain this information, how would you complete the following partial SQL query?
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount, Orders.OrderDate
FROM Orders
INNER JOIN _______ ON _____________ = _____________;
- INNER JOIN Orders ON Orders.OrderID=Orders.CustomerID;
- INNER JOIN Customers ON Customer.CustomerID=Customers.CustomerID;
- INNER JOIN Orders ON Orders.CustomerID=Customers.CustomerID;
- INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Explanation: An INNER JOIN returns the rows from both tables that contain the same information in the column or columns specified in the ON clause. To complete the INNER JOIN clause, first specify the name of the additional table, which is the Customers Table in this example. Then specify which columns must contain the same information in order for the row to be returned. In this example, you are joining the two tables on the data in the CustomerID column, which contains the same information in both tables.
3. In combination with primary and foreign keys, which SQL statement is the most common method for joining fields from multiple tables?
- BETWEEN
- LIKE
- JOIN
- WHERE
Explanation: The most customary practice for joining fields from multiple tables is to use the JOIN statement in conjunction with the primary and foreign keys.
4. Refer to the exhibit. Which type of SQL JOIN operation would return all the rows from Table 1 and Table 2?
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- INNER JOIN
Explanation: A FULL JOIN returns all rows from Table 1 and Table 2. Values that are not met by the join condition are replaced with a NULL value.
5. Which JOIN clause would join the two tables Orders and Customers, using the CustomerID field in both tables as the relationship between the two tables?
SELECT *
FROM Orders
LEFT JOIN Customers ON ____________ = ______________;
- ON Customers.CustomerID=Orders.CustomerID
- ON Orders.CustomerID=Customers.CustomerID
- ON Orders.CustomerID=Orders.CustomerID
- ON Customers.CustomerID=Customers.CustomerID
Explanation: The correct JOIN clause would begin with table 1 (the same tables as in the FROM clause) and the CusterID field followed by table 2 and the CustomerID field.
ON Orders.CustomerID=Customers.CustomerID
6. Which type of key uniquely defines each table row in a relational database?
- foreign key
- primary key
- principal key
- JOIN key
Explanation: The primary key uniquely identifies a row (record) in a table. The primary key can be a single attribute (column) or a combination of multiple attributes.
7. What is the function of a foreign key in a relational database table?
- to define a relationship with a primary key in a different table
- to uniquely identify a row within the table
- to create a many-to-one relationship within the table
- to add additional unique columns to the data table
Explanation: A foreign key is a column or a set of columns in one table that links to the primary key in another table.
8. Review the columns and descriptions in each table, Movie, and Award. Data from the two tables need to be combined to extract the title and cost of movies that won awards. Can a NATURAL JOIN be used to accomplish the desired result?
Movie table:
MovieID- primary key
Title- name of the movie
Date- date the movie was released
Cost- total cost of production
Award table:
AwardID- primary key
MovieID- foreign key
Category- award category
Name- name of the award
Date: day the award was received
- Yes, because both tables have two common columns.
- Yes, because both tables have the MovieID column.
- No, because the Date column contains different data types in each table.
- No, because an OUTER JOIN should be used to extract all of the information.
Explanation: A NATURAL JOIN will implicitly join the two tables on any columns in both tables that have the same name and data type. A natural join will not be successful in this case because the DATE column does not have the same meaning in both tables. Care must be taken if a natural join is used to ensure that columns with the same name and data type contain the same information.
9. What is the result of the query shown in the example?
UPDATE Review
SET Comment = 'This is the new comment'
WHERE MovieId = 3456 AND AuthorName = 'PuzoFan76';
- The comment made by PuzoFan76 for the movie with the ID 3456 will be modified to say, “This is a new comment.”
- A new comment will be added to the movie with the ID 3456 for the author PuzoFan76.
- All of the comments for the movie with the ID 3456 will be modified to say, “This is a new comment.”
- A new row will be added to the table Review with the MovieId= 3456 and the AuthorName =PuzoFan76.
Explanation: The UPDATE command will modify an or multiple attributes in an existing record. You must use the INSERT command to add a new record to the table. If no WHERE clause is specified, the UPDATE command will update the attribute in all records in the table.
10. What are two types of data that benefit from NoSQL data storage? (Choose two.)
- data with flexible formats that can change over time
- high volumes of unstructured data
- data easily organized in tables of columns and rows
- data that conforms to a structured schema
- form data requiring input validation
Explanation: NoSQL databases are designed for high volumes of unstructured data and for data, such as documents, that have flexible formats that may change over time. Disadvantages to NoSQL databases include difficulty validating input fields against existing data and the lack of application support.
