Database Practice Test 1 Review

Which SQL statement correctly retrieves a list of customers from Utah, sorted by last name?

• SELECT lastname, firstname FROM customers WHERE state = 'UT' ORDER BY lastname;

• SELECT lastname, firstname FROM customers ORDER BY lastname WHERE state = 'UT';

• SELECT lastname, firstname FROM customers WHERE state = 'UT' GROUP BY lastname;

• SELECT lastname, firstname FROM customers ORDER BY lastname;

Answer explanation

The correct SQL statement is 'SELECT lastname, firstname FROM customers WHERE state = 'UT' ORDER BY lastname;'. It filters customers from Utah and sorts them by last name, which is the requirement.

Which of the following statements about the GROUP BY clause are true?

• Yes, No, No

• No, Yes, No

• Yes, Yes, No

• No, No, Yes

Answer explanation

The correct choice is 'No, Yes, No' because the GROUP BY clause is used to group rows that have the same values in specified columns, and it can be used with aggregate functions. The other options do not accurately reflect this.

What is the issue with the following SQL statement? SELECT LastName, FirstName, COUNT(OrderID) FROM Customers GROUP BY LastName HAVING COUNT(OrderID) >= 5;

• The SQL statement is missing a GROUP BY clause for all selected columns.

• The HAVING clause is incorrectly used instead of WHERE.

• The COUNT function is used incorrectly with non-aggregated columns.

• The SQL statement is correct and has no issues.

Answer explanation

The SQL statement is missing a GROUP BY clause for all selected columns. Since both LastName and FirstName are selected, they should be included in the GROUP BY clause to avoid ambiguity in the results.

What would cause a runtime error in the following SQL statement? sql CopyEdit UPDATE Customers SET Status = 'Gold' IF TotalAmount > 10000;

• The object type needs to be specified within the UPDATE statement.

• The IF clause needs to be at the beginning of the statement.

• The IF clause needs to be a WHERE clause.

• The statement needs semicolons.

Answer explanation

The SQL statement incorrectly uses an IF clause. In SQL, conditions for updates should be specified using a WHERE clause to filter which records to update. Thus, 'IF TotalAmount > 10000' should be 'WHERE TotalAmount > 10000'.

Which SQL statement allows inserting multiple rows at once using the VALUES keyword?

• INSERT INTO table_name VALUES (value1, value2, value3,...);

• INSERT INTO table_name SELECT (column1, column2, column3);

• INSERT INTO table_name(column1, column2) VALUES (value1a, value1b), (value2a, value2b);

• INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...);

Answer explanation

The correct choice, 'INSERT INTO table_name(column1, column2) VALUES (value1a, value1b), (value2a, value2b);', allows inserting multiple rows at once using the VALUES keyword, which is not possible with the other options.

A quality control specialist wants to retrieve an orderID after inputting an orderdetailID. Which of the following SQL statements correctly creates the required stored procedure?

• CREATE PROCEDURE uspGetOrderID @orderdetailIID int, @orderID int OUTPUT BEGIN SELECT @orderID = orderid FROM orderdetails WHERE orderdetailid = @orderdetailID END

• CREATE PROCEDURE uspGetOrderID @orderdetailIID int, @orderID int OUTPUT AS BEGIN SELECT orderid FROM orderdetails WHERE orderdetailid = @orderdetailID RETURN @orderID END

• CREATE PROCEDURE uspGetOrderID @orderdetailIID int AS BEGIN SELECT orderid FROM orderdetails WHERE orderdetailid = @orderdetailID END

• CREATE PROCEDURE uspGetOrderID @orderdetailIID int, @orderID int OUTPUT AS BEGIN SELECT @orderID = orderid FROM orderdetails WHERE orderdetailid = @orderdetailID END

Answer explanation

The correct choice initializes the OUTPUT parameter @orderID with the result of the SELECT statement. It uses the correct syntax with 'BEGIN' and assigns the value directly, ensuring the orderID is retrieved properly.

What is the correct SQL statement to create a stored procedure that retrieves an order ID based on the order detail ID?

• CREATE PROCEDURE uspGetOrderID @orderdetailID int, @orderID int OUTPUT AS BEGIN SELECT @orderID = orderid FROM orderdetails WHERE orderdetailid = @orderdetailID END

• CREATE PROCEDURE uspGetOrderID @orderdetailID int, @orderID int OUTPUT AS BEGIN SELECT orderid FROM orderdetails WHERE orderdetailid = @orderdetailID END

• CREATE PROCEDURE uspGetOrderID @orderdetailID int, @orderID int OUTPUT AS BEGIN SELECT @orderID = orderid FROM orderdetails WHERE orderdetailid = @orderdetailID RETURN @orderID END

• CREATE PROCEDURE uspGetOrderID @orderdetailID int, @orderID int OUTPUT AS BEGIN SELECT @orderID = orderid FROM orderdetails WHERE orderdetailid = @orderdetailID END

Answer explanation

The correct SQL statement assigns the retrieved order ID to the output parameter @orderID. The other options either do not assign the value or incorrectly use RETURN, which is not suitable for output parameters.

A list of students volunteering at a Science Fair is stored in the Students table. If Benjamin is no longer attending and is replaced by Kayden, which SQL statement correctly updates the table?

• REPLACE INTO Students (FirstName) VALUES ('Kayden') WHERE FirstName = 'Benjamin';

• UPDATE Students SET FirstName = 'Kayden' WHERE FirstName = 'Benjamin';

• MODIFY Students SET FirstName = 'Kayden' WHERE FirstName = 'Benjamin';

Answer explanation

The correct SQL statement is 'UPDATE Students SET FirstName = 'Kayden' WHERE FirstName = 'Benjamin';' because it updates the existing record for Benjamin to Kayden. The other options are incorrect syntax or do not perform the required update.

What is the purpose of the following SQL statement? INSERT INTO Students (FirstName) VALUES ('Kayden') WHERE FirstName = 'Benjamin';

• To add a new record with the name 'Kayden'

• To update the name 'Benjamin' to 'Kayden'

• To delete the record with the name 'Benjamin'

• To select the record with the name 'Benjamin'

Answer explanation

The SQL statement attempts to insert 'Kayden' into the Students table, but the WHERE clause is incorrectly used. It suggests updating 'Benjamin' to 'Kayden', which is not valid in an INSERT statement. Thus, it implies an update.

What is being enforced using this SQL statement? ALTER TABLE salesterritory ADD CONSTRAINT fk_location FOREIGN KEY (locationID) REFERENCES locations(locationID);

• Primary key

• Foreign key

• Referential integrity

• Compound key

Answer explanation

The SQL statement enforces referential integrity by creating a foreign key constraint on the locationID in the salesterritory table, ensuring that it corresponds to valid entries in the locations table.