Database Practice Test 1 Part 2 Review
Authored by Susan Fudge
Information Technology (IT)
11th Grade
Used 1+ times
AI Actions
Add similar questions
Adjust reading levels
Convert to real-world scenario
Translate activity
More...
Content View
Student View
10 questions
Show all answers
1.
MULTIPLE CHOICE QUESTION
30 sec • 1 pt
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.
2.
MULTIPLE CHOICE QUESTION
30 sec • 1 pt
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.
3.
MULTIPLE CHOICE QUESTION
30 sec • 1 pt
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.
4.
MULTIPLE CHOICE QUESTION
30 sec • 1 pt
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.
5.
MULTIPLE CHOICE QUESTION
30 sec • 1 pt
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.
6.
MULTIPLE CHOICE QUESTION
30 sec • 1 pt
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.
7.
MULTIPLE CHOICE QUESTION
30 sec • 1 pt
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.
Access all questions and much more by creating a free account
Create resources
Host any resource
Get auto-graded reports
Continue with Google
Continue with Email
Continue with Classlink
Continue with Clever
or continue with
Microsoft
%20(1).png)
Apple
Others
Already have an account?
