Chapter 3 – Space Flashcard
Unlock all answers in this set
Unlock answersquestion
You can save the results of a query as a table by including the ____ clause in the query.
answer
Into
question
What SQL code will list the name of every student whose postal code is 10113.
answer
SELECT FirstName, LastName FROM Student WHERE PostalCode='10113' ;
question
The ____ function calculates the number of entries in a table.
answer
COUNT
question
What SQL code will find the name of the student whose ID is 1167.
answer
SELECT FirstName, LastName FROM Student WHERE StudentID='1167' ;
question
What SQL code will list the descriptions of all items that are not in Storehouse 3.
answer
SELECT Description FROM Item WHERE NOT Storehouse='3' ;
question
To add new data to a table, use the ____ command.
answer
INSERT
question
What SQL code will list the number, name, and available credit for all customers with credit limits that exceed their balances.
answer
SELECT CustomerNum, CustomerName, CreditLimit-Balance AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;
question
What SQL code 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.
answer
SELECT Description FROM Item WHERE Storehouse='3' OR OnHand>20 ;
question
What SQL code will list the number, name, street, and credit limit of all customers. Order the customers by name within descending credit limit.
answer
SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer ORDER BY CreditLimit DESC, CustomerName ;
question
What SQL code will list the descriptions of all items that are located in Storehouse3 and for which there are more than 20 units on hand.
answer
SELECT Description FROM Item WHERE Storehouse='3' AND OnHand>20 ;
question
Many versions of SQL require you to end a command with a ____.
answer
semicolon (;)
question
What SQL code will list the complete student table.
answer
SELECT * FROM Student ;
question
The basic form of an SQL retrieval command is ____.
answer
SELECT-FROM-WHERE
question
In versions of SQL other than Access, the ____ is used as a wildcard to represent any collection of characters.
answer
percent sign (%)
question
What SQL code will list the number, name, and balance of all customers with balances greater than or equal to $2,000 and less than or equal to $5,000.
answer
SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ;
question
When rows are grouped, ____.
answer
one line of output is produced for each group
question
To use a wildcard, include the ____ operator in the WHERE clause.
answer
LIKE
question
What SQL code will change the postal code of the student with ID 11433 to 14455.
answer
UPDATE Student SET PostalCode='14455' WHERE StudentID='11433' ;
question
What SQL code will find how many items are in category TOY.
answer
SELECT COUNT(*) FROM Item WHERE Category='TOY' ;
question
When used after the word SELECT, the ____ symbol indicates that you want to include all fields in the query results in the order in which you described them to the DBMS when you created the table.
answer
*
question
When you use a name containing a space in Access SQL, you must ____.
answer
enclose it in square brackets
question
What SQL code will for each sales rep, list the rep number, the number of customers assigned to the rep, and the average balance of the rep's customers. Group the records by rep number and order the records by rep number.
answer
SELECT RepNum, COUNT(*), AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ;
question
What SQL code will list the number, name, and complete address of every customer located on a street that contains the letters "Oxford".
answer
SELECT CustomerNum, CustomerName, Street, City, State, PostalCode FROM Customer WHERE Street LIKE "%Oxford%" ;
question
What SQL code will list the number and name of all customers that are either represented by sales rep 30 or that currently have orders on file, or both.
answer
SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum='30' UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer, Orders WHERE Customer.CustomerNum=Orders.CustomerNum ;
question
What SQL code will delete any row in the OrderLine table in which the item number is MT03.
answer
DELETE FROM OrderLine WHERE ItemNum='MT03' ;
