Question 1

Based on the code shown, which query finds how many items are in category TOY???Item (ItemNum, Description, OnHand, Category, Storehouse, Price )
A) SELECT SUM(*) FROM Item WHERE Category='TOY' ;
B) SELECT COUNT(*) FROM Item WHERE Category='TOY' ;
C) SELECT COUNT FROM Item WHERE Category='TOY' ;
D) SELECT COUNT* FROM Item WHERE Category='TOY' ;

Accepted Answer

The correct query is "SELECT COUNT(*) FROM Item WHERE Category='TOY' ;". The COUNT function is used to return the number of rows that match the given condition in the WHERE clause. In this case, we want to count the number of items that belong to the category "TOY". Therefore, the correct answer is option B.

Question 2

Words that are part of the SQL language are called ____________________ words.

Accepted Answer

The answer of Words that are part of the SQL...

Question 3

Based on the code shown, which query lists the descriptions of all items that are located in Storehouse3 and for which there are more than 20 units on hand???Item (ItemNum, Description, OnHand, Category, Storehouse, Price )
A) SELECT Description FROM Item WHERE Storehouse='3' AND OnHand>20 ;
B) SELECT Description FROM Item WHERE Storehouse='3' OR OnHand>20 ;
C) SELECT Description FROM Item WHERE Storehouse='3' ;
D) SELECT Description FROM Customer WHERE Storehouse='3' AND OnHand>20 ;

Accepted Answer

The query needs to specify both the Storehouse and the OnHand condition, which is only satisfied in option A. Option B uses OR instead of AND, which will include any item that is either located in Storehouse3 or has more than 20 units on hand, regardless of whether both conditions are met. Option C only specifies the location but not the OnHand condition, while option D selects from the wrong table (Customer instead of Item).

Question 4

SQL is the standard language for relational ___________________ manipulation.

Accepted Answer

The answer of SQL is the standard language for relational...

Question 5

In an SQL query, after the word _______________ , you list the fields you want to display in the query result.

Accepted Answer

The answer of In an SQL query, after the word...

Question 6

Based on the code shown, which query deletes any row in the OrderLine table in which the item number is MT03???OrderLine (OrderNum, ItemNum, NumOrdered, QuotedPrice)?Item (ItemNum, Description, OnHand, Category, Storehouse, Price )
A) DELETE FROM OrderLine WHERE ItemNum='MT03' ;
B) DELETE FROM OrderLine WHERE ItemNum=MT03 ;
C) REMOVE FROM OrderLine WHERE ItemNum='MT03' ;
D) REMOVE FROM OrderLine WHERE ItemNum=MT03 ;

Accepted Answer

The correct syntax to delete a row from a table in MySQL is to use the DELETE statement, followed by the table name and the WHERE clause to specify the condition for deleting. In this case, the condition is that the ItemNum column should have the value 'MT03'. Since ItemNum is a string column, it should be enclosed in single quotes. Therefore, only option A satisfies this requirement and is the correct choice. Options B, C, and D either have syntax errors or use incorrect keywords and are not valid MySQL statements.

Question 7

The ____________________ data type stores integers in the range -32768 to 32767.

Accepted Answer

The answer of The ____________________ data type stores integers in...

Question 8

Based on the code shown, which query lists the name of every student whose postal code is 10113???Student (StudentID, FirstName, LastName, Street, City, State, PostalCode)
A) SELECT FirstName, LastName WHERE PostalCode='10113' ;
B) SELECT FirstName, LastName FROM Student WHERE PostalCode='10113' ;
C) SELECT FirstName, LastName FROM Student WHERE PostalCode='10113'
D) SELECT * FROM Student WHERE PostalCode='10113' ;

Accepted Answer

The correct syntax for selecting specific columns from a table is to use the keyword "SELECT" followed by a list of column names separated by commas, followed by the keyword "FROM" followed by the table name. The correct query to select the first name and last name of every student whose postal code is 10113 should be: SELECT FirstName, LastName FROM Student WHERE PostalCode='10113';

Question 9

You use the SQL ____________________ command to create a table by describing its layout.

Accepted Answer

The answer of You use the SQL ____________________ command to...

Question 10

Based on the code shown, which query lists the complete student table???Student (StudentID, FirstName, LastName, Street, City, State, PostalCode)
A) SELECT Student ;
B) SELECT & FROM Student ;
C) SELECT * FROM Student ;
D) SELECT LastName, FirstName, Street, City, State, PostalCode FROM Student

Accepted Answer

Option C is the correct query to list the complete student table because it uses the wildcard character (*) to select all the columns in the table. The other options are incorrect because:
- Option A is invalid syntax as it only specifies the table name without any columns to select.
- Option B is also invalid syntax as the ampersand symbol (&) has no meaning in SQL.
- Option D only selects specific columns in the table, not the entire table.

Question 11

Based on the code shown, which query lists the number and name of all customers that are either represented by sales rep 30 or that currently have orders on file, or both???Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )
A) SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum='30' UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer ;
B) SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum='30' UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer, Orders WHERE Customer.CustomerNum=Orders.CustomerNum ;
C) SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum='30' WHERE Customer.CustomerNum=Orders.CustomerNum ;
D) SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum='30' UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer, Orders ;

Accepted Answer

The query in option B selects the CustomerNum and CustomerName from the Customer table where RepNum is equal to 30 and then uses the UNION operator to combine it with a select statement that again selects the CustomerNum and CustomerName from the Customer table where there are corresponding records in the Orders table (which means they currently have orders on file). This satisfies the condition of (a) customers represented by sales rep 30 and (b) customers that currently have orders on file, or both. Option A only selects customers represented by sales rep 30, while option C has a syntax error (WHERE should be replaced with AND or OR) and only considers customers represented by sales rep 30 that also have orders on file. Option D selects all customers and orders, which may include irrelevant records.

Question 12

Use the ____________________ data type for fields that contain letters and other special characters, and for fields that contain numbers that will not be used for arithmetic.

Accepted Answer

The answer of Use the ____________________ data type for fields...

Question 13

By using the word ____________________ in a query after a computation, you can assign a name to the computed field.

Accepted Answer

The answer of By using the word ____________________ in a...

Question 14

Based on the code shown, which query changes the postal code of the student with ID 11433 to 14455???Student (StudentID, FirstName, LastName, Street, City, State, PostalCode)
A) UPDATE Student SET PostalCode='14455' ;
B) UPDATE Student SET PostalCode WHERE StudentID='11433' ;
C) UPDATE Student IN PostalCode='14455' WHERE StudentID='11433' ;
D) UPDATE Student SET PostalCode='14455' WHERE StudentID='11433' ;

Accepted Answer

The correct query to change the postal code of the student with ID 11433 to 14455 is the option D: "UPDATE Student SET PostalCode='14455' WHERE StudentID='11433'". This query uses the UPDATE statement to specify the table to be modified, the SET keyword to indicate the column to be updated (PostalCode), and its new value ('14455'). The WHERE clause is used to filter the specific record based on the StudentID column, which is used to identify the student to be updated. Option A updates all records with the same PostalCode value, Option B has a syntax error, and Option C has a syntax error and an incorrect use of the IN keyword.

Question 15

Based on the code shown, which query lists the descriptions of all items that are not in Storehouse 3???Item (ItemNum, Description, OnHand, Category, Storehouse, Price )
A) SELECT Description FROM Customer WHERE NOT Storehouse='3' ;
B) SELECT Description FROM Item WHERE Storehouse='4' ;
C) SELECT Description FROM Item WHERE Storehouse>'3' ;
D) SELECT Description FROM Item WHERE NOT Storehouse='3' ;

Accepted Answer

The query in choice D uses the NOT operator to select all descriptions of items that are not in Storehouse 3.

Question 16

Use the ___________ operator when an exact match won't work.

Accepted Answer

The answer of Use the ___________ operator when an exact...

Question 17

When you connect simple conditions using the ____________________ operator, the compound condition will be true whenever any of the simple conditions are true.

Accepted Answer

The answer of When you connect simple conditions using the...

Question 18

One common restriction placed on table and column names by DBMSs is that names must start with a(n) ____________________.

Accepted Answer

The answer of One common restriction placed on table and...

Question 19

Based on the code shown, which query finds the name of the student whose ID is 1167???Student (StudentID, FirstName, LastName, Street, City, State, PostalCode)
A) SELECT FirstName, LastName FROM Course WHERE StudentID='1167' ;
B) SELECT FirstName, LastName FROM * WHERE StudentID='1167' ;
C) SELECT FirstName, LastName FROM Student WHERE StudentID='1167' ;
D) SELECT FirstName, LastName FROM Customer WHERE StudentID='1167' ;

Accepted Answer

The query needs to be performed on the "Student" table, which contains the columns "StudentID", "FirstName", and "LastName". Option A queries on a "Course" table which may not contain the required information, and Option B uses an asterisk, which is not necessary and can lead to errors. Option D queries on a "Customer" table, which is not relevant to the given requirement.

Question 20

Based on the code shown, which query lists the descriptions of all items that are located in Storehouse 3 or for which there are more than 20 units on hand, or both???Item (ItemNum, Description, OnHand, Category, Storehouse, Price )
A) SELECT Description FROM Customer WHERE Storehouse='3' AND OnHand>20 ;
B) SELECT Description FROM Item WHERE Storehouse='3' OR OnHand>20 ;
C) SELECT Description FROM Item WHERE Storehouse='3' ;
D) SELECT Description FROM Item WHERE OnHand>20 ;

Accepted Answer

The query "SELECT Description FROM Item WHERE Storehouse='3' OR OnHand>20" will list the descriptions of all items that are located in Storehouse 3 or for which there are more than 20 units on hand, or both. Choice A only lists items that meet both conditions, while choice C only lists items in Storehouse 3. Choice D only lists items with more than 20 units on hand.

Quiz 3: The Relational Model 2: SQL

Based on the code shown, which query finds how many items are in category TOY???Item (ItemNum, Description, OnHand, Category, Storehouse, Price )

Words that are part of the SQL language are called ____________________ words.

Based on the code shown, which query lists the descriptions of all items that are located in Storehouse3 and for which there are more than 20 units on hand???Item (ItemNum, Description, OnHand, Category, Storehouse, Price )

SQL is the standard language for relational ___________________ manipulation.

In an SQL query, after the word _______________ , you list the fields you want to display in the query result.

Based on the code shown, which query deletes any row in the OrderLine table in which the item number is MT03???OrderLine (OrderNum, ItemNum, NumOrdered, QuotedPrice) ?Item (ItemNum, Description, OnHand, Category, Storehouse, Price )

The ____________________ data type stores integers in the range -32768 to 32767.

Based on the code shown, which query lists the name of every student whose postal code is 10113???Student (StudentID, FirstName, LastName, Street, City, State, PostalCode)

You use the SQL ____________________ command to create a table by describing its layout.

Based on the code shown, which query lists the complete student table???Student (StudentID, FirstName, LastName, Street, City, State, PostalCode)

Based on the code shown, which query lists the number and name of all customers that are either represented by sales rep 30 or that currently have orders on file, or both???Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

Use the ____________________ data type for fields that contain letters and other special characters, and for fields that contain numbers that will not be used for arithmetic.

By using the word ____________________ in a query after a computation, you can assign a name to the computed field.

Based on the code shown, which query changes the postal code of the student with ID 11433 to 14455???Student (StudentID, FirstName, LastName, Street, City, State, PostalCode)

Based on the code shown, which query lists the descriptions of all items that are not in Storehouse 3???Item (ItemNum, Description, OnHand, Category, Storehouse, Price )

Use the ___________ operator when an exact match won't work.

When you connect simple conditions using the ____________________ operator, the compound condition will be true whenever any of the simple conditions are true.

One common restriction placed on table and column names by DBMSs is that names must start with a(n) ____________________.

Based on the code shown, which query finds the name of the student whose ID is 1167???Student (StudentID, FirstName, LastName, Street, City, State, PostalCode)

Based on the code shown, which query lists the descriptions of all items that are located in Storehouse 3 or for which there are more than 20 units on hand, or both???Item (ItemNum, Description, OnHand, Category, Storehouse, Price )