Chapter 3 – Space Flashcard
Flashcard maker : Kaitlynn Baldwin
You can save the results of a query as a table by including the ____ clause in the query.
What SQL code will list the name of every student whose postal code is 10113.
SELECT FirstName, LastName FROM Student WHERE PostalCode=’10113′ ;
The ____ function calculates the number of entries in a table.
What SQL code will find the name of the student whose ID is 1167.
SELECT FirstName, LastName FROM Student WHERE StudentID=’1167′ ;
What SQL code will list the descriptions of all items that are not in Storehouse 3.
SELECT Description FROM Item WHERE NOT Storehouse=’3′ ;
To add new data to a table, use the ____ command.
What SQL code will list the number, name, and available credit for all customers with credit limits that exceed their balances.
SELECT CustomerNum, CustomerName, CreditLimit-Balance AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;
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.
SELECT Description FROM Item WHERE Storehouse=’3′ OR OnHand>20 ;
What SQL code will list the number, name, street, and credit limit of all customers. Order the customers by name within descending credit limit.
SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer ORDER BY CreditLimit DESC, CustomerName ;
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.
SELECT Description FROM Item WHERE Storehouse=’3′ AND OnHand>20 ;
Many versions of SQL require you to end a command with a ____.
What SQL code will list the complete student table.
SELECT * FROM Student ;
The basic form of an SQL retrieval command is ____.
In versions of SQL other than Access, the ____ is used as a wildcard to represent any collection of characters.
percent sign (%)
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.
SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ;
When rows are grouped, ____.
one line of output is produced for each group
To use a wildcard, include the ____ operator in the WHERE clause.
What SQL code will change the postal code of the student with ID 11433 to 14455.
UPDATE Student SET PostalCode=’14455′ WHERE StudentID=’11433′ ;
What SQL code will find how many items are in category TOY.
SELECT COUNT(*) FROM Item WHERE Category=’TOY’ ;
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.
When you use a name containing a space in Access SQL, you must ____.
enclose it in square brackets
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.
SELECT RepNum, COUNT(*), AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ;
What SQL code will list the number, name, and complete address of every customer located on a street that contains the letters “Oxford”.
SELECT CustomerNum, CustomerName, Street, City, State, PostalCode FROM Customer WHERE Street LIKE “%Oxford%” ;
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.
SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum=’30’ UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer, Orders WHERE Customer.CustomerNum=Orders.CustomerNum ;
What SQL code will delete any row in the OrderLine table in which the item number is MT03.
DELETE FROM OrderLine WHERE ItemNum=’MT03′ ;