Database Practice Test 1 Part 2 Review

Which SQL query will list every product with every location?

• SELECT product FROM products CROSS JOIN location FROM locations;

• SELECT product FROM products LEFT OUTER JOIN location FROM locations ON products.product = locations.location;

• SELECT product FROM products FULL OUTER JOIN location FROM locations ON products.product = locations.location;

• SELECT product FROM products INNER JOIN location FROM locations ON products.product = locations.location;

Answer explanation

The correct choice is 'SELECT product FROM products CROSS JOIN location FROM locations;' because a CROSS JOIN produces a Cartesian product, listing every product with every location without any conditions.

Which of the following SQL statements correctly defines a composite key?

• CREATE TABLE Signups ( studentID varchar(5) UNIQUE NOT NULL, classID varchar(5) UNIQUE NOT NULL, signupdate DATETIME, PRIMARY KEY (studentID, classID) );

• CREATE TABLE Signups ( studentID varchar(5) UNIQUE NOT NULL, classID varchar(5) UNIQUE NOT NULL, signupdate DATETIME, COMPOSITE KEY (studentID, classID) );

• CREATE TABLE Signups ( studentID varchar(5) UNIQUE NOT NULL, classID varchar(5) UNIQUE NOT NULL, signupdate DATETIME, PRIMARY KEY studentID, PRIMARY KEY classID );

• CREATE TABLE Signups ( studentID varchar(5) UNIQUE NOT NULL, classID varchar(5) UNIQUE NOT NULL, signupdate DATETIME, PRIMARY KEYS (studentID, classID) );

Answer explanation

The correct choice defines a composite key using 'PRIMARY KEY (studentID, classID)', which combines both columns. The other options either use incorrect syntax or attempt to define multiple primary keys, which is not allowed.

Which SQL statement retrieves customers with sales of at least $50,000 from Los Angeles, California, Nevada, or Arizona?

• SELECT lastname, firstname FROM customers WHERE sales >=50000 OR ((city='Los Angeles' AND state='California') OR state IN ('Nevada','Arizona'));

• SELECT lastname, firstname FROM customers WHERE sales >50000 AND ((city='Los Angeles' AND state='California') OR state IN ('Nevada','Arizona'));

• SELECT lastname, firstname FROM customers WHERE sales >=50000 AND ((city='Los Angeles' AND state='California') OR state IN ('Nevada','Arizona'));

• SELECT lastname, firstname FROM customers WHERE sales >50000 OR ((city='Los Angeles' AND state='California') AND state IN ('Nevada','Arizona'));

Answer explanation

The correct SQL statement uses 'AND' to ensure both conditions are met: sales must be at least $50,000, and the customer must be from Los Angeles, California, or from Nevada or Arizona.

What SQL statement grants execution permissions on a stored procedure?

• GRANT EXECUTE ON sp_current_sales TO inside_sales;

• GRANT EXECUTE ON sp_current_sales TO GROUP inside_sales;

• GRANT EXECUTE ON sp_current_sales TO inside_sales WITH GRANT OPTION;

Answer explanation

The correct SQL statement to grant execution permissions on a stored procedure is 'GRANT EXECUTE ON sp_current_sales TO inside_sales;'. This allows the user 'inside_sales' to execute the procedure.

Which SQL query correctly calculates the sales tax for customers with orders?

• SELECT firstname, lastname, subtotal, subtotal * 1.05 AS sales_tax FROM customers INNER JOIN orders ON customers.customerid = orders.customerid;

• SELECT firstname, lastname, subtotal, subtotal * 1.05 AS sales_tax FROM customers INNER JOIN orders ON customers.customerid = orders.customerid;

• SELECT firstname, lastname, subtotal, subtotal 1* .05 AS sales_tax FROM customers LEFT OUTER JOIN orders ON customers.customerid = orders.customerid;

Answer explanation

The correct query calculates sales tax by multiplying the subtotal by 1.05. The first option uses INNER JOIN, ensuring only customers with orders are included, making it the right choice. The other options contain errors.

What is the correct SQL query to create a phone directory with unique numeric IDs?

• CREATE TABLE phone_directory (id INT UNIQUE, name VARCHAR(255), phone_number VARCHAR(15));

• CREATE TABLE phone_directory (id INT, name VARCHAR(255), phone_number VARCHAR(15));

• CREATE TABLE phone_directory (id INT PRIMARY KEY, name VARCHAR(255), phone_number VARCHAR(15));

• CREATE TABLE phone_directory (id INT NOT NULL, name VARCHAR(255), phone_number VARCHAR(15));

Answer explanation

The correct choice is 'CREATE TABLE phone_directory (id INT UNIQUE, name VARCHAR(255), phone_number VARCHAR(15));' because it ensures that the 'id' field is unique, preventing duplicate numeric IDs in the phone directory.

Which is a major difference between a stored procedure and a function?

• A) A function must be compiled every time it is called.

• B) A stored procedure must be compiled every time it is called.

• C) Functions cannot be called from a stored procedure, but a stored procedure can be called from a function.

• D) A function can have both input/output parameters, but a stored procedure can only have input parameters.

Answer explanation

The correct choice is A. Functions are typically compiled each time they are called, while stored procedures are compiled once and can be executed multiple times without recompilation.

Evaluate the following table and query: ID | LastName | Salary 1 | Gomez | 83000 2 | Parker | 38000 3 | Smith | 70000 4 | Tang | 68000 Query: SELECT MAX(Salary) FROM employee; What is the output?

• A) 83000

• B) 1

• C) Gomez

• D) 38000

Answer explanation

The query SELECT MAX(Salary) retrieves the highest salary from the table. The maximum salary listed is 83000, which belongs to Gomez. Therefore, the correct answer is A) 83000.

What is the purpose of the `JOIN` statement in SQL?

• To delete records from a table

• To insert new records into a table

• To update records in a table

• To combine rows from two or more tables, based on a related column between them

Answer explanation

The `JOIN` statement in SQL is used to combine rows from two or more tables based on a related column. This allows for retrieving data that is spread across multiple tables, making it essential for relational database queries.

What type of join returns all records from the left table and the matched records from the right table?

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

• FULL JOIN

Answer explanation

A LEFT JOIN returns all records from the left table and only the matched records from the right table. If there is no match, NULL values are returned for columns from the right table, making it the correct choice.