Name: 
 

A114/I111 Midterm Preparation Exam (Part One)



Multiple Choice
Identify the letter of the choice that best completes the statement or answers the question.
 

1. 

Which of the following will actually describe the layout of a table to the DBMS?
a.
SELECT TABLE
c.
CREATE TABLE
b.
DROP TABLE
d.
DELETE FROM TABLE
 

2. 

The basic form of an SQL retrieval command is
a.
SELECT-WHERE-FROM
c.
SELECT-WHERE
b.
CREATE-SELECT-FROM
d.
SELECT-FROM-WHERE
 

3. 

Which of the following symbols are used to list all of the fields in a SELECT query?
a.
&
c.
*
b.
#
d.
@
 

4. 

The comparison operator used to check inequality is
a.
=.
c.
<=.
b.
<>.
d.
==.
 

5. 

What type of condition is formed by connecting two or more simple conditions?
a.
compound
c.
conditional
b.
complex
d.
conducive
 

6. 

Which operator should be used to connect a compound condition when both simple conditions need to be true in order for the compound condition to be true?
a.
NOR
c.
AND
b.
OR
d.
NOT
 

7. 

Which operator should be used to connect a compound condition when only one of the simple conditions needs to be true in order for the compound condition to be true?
a.
AND
c.
NOR
b.
OR
d.
NOT
 

8. 

Using which operator negates the evaluated condition?
a.
AND
c.
NOR
b.
OR
d.
NOT
 

9. 

Which operator is used to return all values that contain a certain collection of characters?
a.
IN
c.
AND
b.
LIKE
d.
OR
 

10. 

Which operator could be used in place of a number of comparisons with the OR operator?
a.
AND
c.
IN
b.
BETWEEN
d.
NOR
 

11. 

In order to sort the results of a query, which clause should be used?
a.
SORT BY
c.
ORDER BY
b.
SELECT BY
d.
GROUP BY
 

12. 

In order to sort in a high-to-low order, follow the sort key with the word
a.
ASC.
c.
DESCENDING.
b.
DESC.
d.
DESCRIPTIVE.
 

13. 

SQL has built-in functions which are also called
a.
accumulative.
c.
affluent.
b.
assimilated.
d.
aggregate.
 

14. 

Which function will give you a total number of rows returned by the query?
a.
COUNT
c.
MAX
b.
SUM
d.
MIN
 

15. 

Which function should be used to calculate the total of the customers balances?
a.
COUNT
c.
MAX
b.
SUM
d.
MIN
 

16. 

In a nested query, which query is evaluated first?
a.
the innermost query
b.
the outermost query
c.
both are evaluated simultaneously
d.
whichever query is selected to execute first by the user
 

17. 

Which clause would be used to create groups of records?
a.
SELECT
c.
ORDER BY
b.
SORT BY
d.
GROUP BY
 

18. 

What happens when rows are grouped?
a.
the totals appear with the rows
b.
the rows appear in order
c.
one line of output is produced for each group
d.
no output is produced
 

19. 

Into which clause are the conditions placed to join two or more tables together in a query?
a.
SELECT
c.
WHERE
b.
JOIN
d.
GROUP BY
 

20. 

In which clause should the tables involved in a query be listed?
a.
SELECT
c.
FROM
b.
WHERE
d.
ASKING
 

21. 

When two or more tables are joined, how many times must the keyword WHERE be used?
a.
once
c.
once for each table
b.
twice
d.
not used at all
 

22. 

Which operator/keyword produces results containing all rows that are in either the first query, the second query, or both?
a.
DIFFERENCE
c.
MULTIPLE
b.
MINUS
d.
UNION
 

23. 

In order to make changes to existing data in a table, you would use which SQL command?
a.
DELETE
c.
SELECT
b.
CHANGE
d.
UPDATE
 

24. 

Which keyword is used with the UPDATE command to specify the field to be changed and the new value?
a.
CHANGE
c.
MODIFY
b.
DELETE
d.
SET
 

25. 

Which keyword is used to add a new record to a table?
a.
UPDATE
c.
SET
b.
INSERT
d.
DELETE
 

26. 

Which keyword is used to remove data from a table?
a.
INSERT
c.
DROP
b.
DELETE
d.
SELECT
 

27. 

Which keyword is used to create a table from a query?
a.
UPDATE
c.
INTO
b.
INSERT
d.
DELETE
 
 
one_files/i0290000.jpg
 

28. 

Please refer to the above figure. Which query lists the complete Part table?
a.
SELECT PartNum, Description from PART;
b.
SELECT * from Part;
c.
SELECT all from Part;
d.
SELECT PartNum from Part;
 

29. 

Please refer to the above figure. Which query will list the part descriptions of parts with 20 or more units on hand located in warehouse 3?
a.
SELECT Description FROM Part WHERE Warehouse = '3' and OnHand < 20;
b.
SELECT Description FROM Part WHERE Warehouse = '3' and OnHand >= 20;
c.
SELECT Description FROM Part WHERE Warehouse = '3' or OnHand >= 20;
d.
SELECT Description FROM Part WHERE Warehouse = '3' and OnHand > 20;
 

30. 

Please refer to the above figure. Which query will list the descriptions of all parts that are not stored in warehouse 3?
a.
SELECT Description from Part WHERE Warehouse = '3';
b.
SELECT Description from Part WHERE Warehouse <> '3';
c.
SELECT * from Part WHERE NOT Warehouse = '3';
d.
SELECT * from Part WHERE Warehouse =’3’
 

31. 

Please refer to the above figure. Which query lists the descriptions of all parts that are in warehouse number 3?
a.
SELECT Description from Part WHERE Warehouse = '3';
b.
SELECT Description from Part WHERE Warehouse > '3';
c.
SELECT * from Part WHERE Warehouse = '3';
d.
SELECT Description from Part WHERE partNum = '3';
 

32. 

Please refer to the above figure. Which query lists how many parts are in item class HW?
a.
SELECT COUNT(*) FROM Part WHERE Class = 'HW';
b.
SELECT* FROM Part WHERE Class = 'HW';
c.
SELECT COUNT FROM Part WHERE Class = 'HW';
d.
SELECT COUNT(*) FROM Part;
 
 
one_files/i0350000.jpg
 

33. 

Please refer to the above figure. Which query lists the name of every customer with exactly a 10000 credit limit?
a.
SELECT CustomerName from Customer WHERE CreditLimit < 10000;
b.
SELECT CustomerName from Customer WHERE CreditLimit > 10000;
c.
SELECT CustomerName from Customer WHERE CreditLimit = 10000;
d.
SELECT CustomerName from Customer WHERE CreditLimit != 10000;
 

34. 

Please refer to the above figure. Which query displays the customer name for customer number 148?
a.
SELECT CustomerName from Customer WHERE CustomerNum is 148;
b.
SELECT CustomerName from Customer WHERE CustomerNum = 148;
c.
SELECT * from Customer WHERE CustomerNum is 148;
d.
SELECT * from Customer WHERE CustomerNum = 148;
 

35. 

Please refer to the above figure.  Which query lists only the customer number for all customers where their credit limits exceed their balances?
a.
SELECT CustomerNum, CustomerName from Customer WHERE CreditLimit > Balance;
b.
SELECT CustomerNum from Customer WHERE Balance > CreditLimit;
c.
SELECT * from Customer WHERE CreditLimit > Balance;
d.
SELECT CustomerNum from Customer WHERE CreditLimit > Balance;
 

36. 

Please refer to the above figure. Which query lists the customer number of every customer that has a credit limit of $7,500, $10,000, or $15,000?
a.
SELECT CustomerNum FROM Customer WHERE CreditLimit (7500, 10000, 15000);
b.
SELECT CustomerNum FROM Customer WHERE CreditLimit IN (7500, 10000, 15000);
c.
SELECT CustomerNum FROM Customer WHERE CreditLimit IS (7500, 10000, 15000);
d.
SELECT CustomerNum FROM Customer WHERE CreditLimit = (7500, 10000, 15000);
 

37. 

Please refer to the above figure. Which query would list the customer number of every customer located in a city that contains the letters "Gro"?
a.
SELECT CustomerNum FROM Customer WHERE City LIKE '%Gro%';
b.
SELECT CustomerNum FROM Customer WHERE City = '%Gro%';
c.
SELECT CustomerNum FROM Customer WHERE City LIKE 'Gro';
d.
SELECT CustomerNum FROM Customer WHERE City LIKE ‘@Gro@’;
 

38. 

Please refer to the above figure. Which query would list the customer number and name of the customers sorted by customer number?
a.
SELECT CustomerNum, CustomerName FROM Customer SORT BY CustomerNum;
b.
SELECT CustomerNum, CustomerName FROM Customer ORDER BY CustomerName;
c.
SELECT CustomerNum, CustomerName FROM Customer ORDER BY CustomerNum;
d.
SELECT CustomerName FROM Customer ORDER BY CustomerNum;
 

39. 

Please refer to the above figure. Which query will list the customer number and available credit (using the title AvailCred) for all customers?
a.
SELECT * as AvailCred FROM Customer;
b.
SELECT CustomerNum, CreditLimit - Balance FROM Customer;
c.
SELECT CustomerNum, CreditLimit as AvailCred FROM Customer;
d.
SELECT CustomerNum, CreditLimit - Balance as AvailCred FROM Customer;
 

40. 

Please refer to the above figure. Which query will list the customer number and name of all customers in descending order by name?
a.
SELECT CustomerNumber, CustomerName FROM Customer ORDER BY CustomerName;
b.
SELECT CustomerNumber, CustomerName FROM Customer ORDER BY CustomerName DESC;
c.
SELECT CustomerNumber, CustomerName FROM Customer ORDER BY Customer DESC;
d.
SELECT CustomerName FROM Customer ORDER BY CustomerName DESC;
 

41. 

Please refer to the above figure. Which query will list the number of customers and the total of their balances?
a.
SELECT COUNT, SUM (Balance) FROM Customer;
b.
SELECT SUM (Balance) FROM Customer;
c.
SELECT COUNT (Balance) FROM Customer;
d.
SELECT COUNT (*), SUM (Balance) FROM Customer;
 
 
one_files/i0450000.jpg
 

42. 

Please refer to the above figure. Which query will list the name of the customer along with their sales rep's last name of every customer with a credit limit greater than $10,000?
a.
SELECT CustomerName, LastName FROM Customer, Rep WHERE Rep.RepNum = Customer.RepNum AND CreditLimit > 10000;
b.
SELECT CustomerName, LastName FROM Customer, Rep WHERE RepNum = RepNum AND CreditLimit > 10000;
c.
SELECT CustomerName, LastName FROM Customer, Rep WHERE Rep.RepNum > Customer.RepNum;
d.
SELECT CustomerName, LastName FROM Customer, WHERE Rep.RepNum = Customer.RepNum AND CreditLimit > 10000;
 

43. 

Please refer to the above figure. Which query will list the name of the customer along with their sales rep's last name of every customer with a credit limit of $10,000?
a.
SELECT CustomerName, LastName FROM Customer, Rep WHERE Rep.RepNum = Customer.RepNum AND CreditLimit = 10000;
b.
SELECT CustomerName, LastName FROM Customer, Rep WHERE RepNum = RepNum AND CreditLimit = 10000;
c.
SELECT CustomerName, LastName FROM Customer, Rep WHERE Rep.RepNum = Customer.RepNum;
d.
SELECT CustomerName, LastName FROM Customer, Rep WHERE Rep.RepNum = Customer.RepNum OR CreditLimit = 10000;
 

44. 

Please refer to the above figure. Which query will list the name of the customer along with the last name of their sales rep?
a.
SELECT CustomerName, Rep.RepNum, FROM Customer, Rep WHERE Rep.RepNum = Customer.RepNum;
b.
SELECT * FROM Customer, Rep WHERE Rep.RepNum = Customer.RepNum;
c.
SELECT CustomerName, LastName FROM Customer, Rep WHERE Rep.RepNum = Customer.RepNum;
d.
SELECT CustomerName, LastName FROM Customer, Rep WHERE RepNum = RepNum;
 



 
Check Your Work     Reset Help