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 FirstName.

• The HAVING clause is incorrectly used instead of WHERE.

• The COUNT function is used incorrectly in the SELECT clause.

• The SQL statement is correct and has no issues.

Answer explanation

The SQL statement is missing a GROUP BY clause for FirstName. When using aggregate functions like COUNT, all selected non-aggregated columns must be included in the GROUP BY clause.

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 is used to insert multiple rows at once?

• 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 is 'INSERT INTO table_name(column1, column2) VALUES (value1a, value1b), (value2a, value2b);' as it allows inserting multiple rows in a single statement using comma-separated value sets.

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 FROM orderdetails WHERE orderdetailid = @orderdetailID END

Answer explanation

The correct choice initializes the OUTPUT parameter @orderID with the result of the SELECT statement, allowing the orderID to be retrieved after inputting orderdetailID. The other options either lack proper assignment or syntax.

What does the CREATE PROCEDURE clause do in the given SQL statement?

• Initializes a stored procedure

• Deletes a stored procedure

• Modifies a stored procedure

• Initializes the stored procedure and ensures the orderID is returned.

Answer explanation

The CREATE PROCEDURE clause initializes a stored procedure, which is a set of SQL statements that can be executed as a single unit. The correct choice also indicates that it ensures the orderID is returned, which is part of its functionality.

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, while the other options are incorrect in syntax or function.

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

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

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

• To delete the record with the name 'Benjamin'

• To select the record with the name 'Kayden'

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.

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 locationID in the salesterritory table, ensuring that it corresponds to valid entries in the locations table.