NEW
Font size
WorksheetsDatabase Practice Test 1 Part 2 Review
Total questions: 10
Worksheet time: 5mins
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;
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) );
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'));
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;
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;
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));
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.
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
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
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
