Deck 3: The Relational Model 2: SQL

Question 1

You can use the GROUP BY clause and the ORDER BY clause in the same SELECT statement.

Answer 1

True

Question 2

The rules for naming tables and columns are the same in every version of SQL.

Answer 2

False

Question 3

Preceding a condition by the NOT operator reverses the truth or falsity of the original condition.

Answer 3

True

Question 4

In SQL,you use the SORT BY clause to display results in a desired order.

Question 5

The IN operator can be used instead or the OR operator in phrasing certain conditions.

Question 6

There are two versions of the "not equal to" operator: <> and !=.

Question 7

When a query involves a character field,such as CustomerName,you must enclose the value to which the field is being compared in percent symbols (%).

Question 8

Fields will appear in the query results in the order in which they are listed in the SELECT clause.

Question 9

The BETWEEN operator is an essential feature of SQL.

Question 10

When rows are grouped,one line of output is produced for each group.

Question 11

Instead of listing all the field names in the SELECT clause,you can use the * symbol.

Question 12

In SQL,you join tables by entering the appropriate conditions in the WHERE clause.

Question 13

In a SELECT statement,the WHERE clause is mandatory.

Question 14

Most relational DBMSs use a version of SQL as a data manipulation language.

Question 15

When you enter numbers in an SQL command,you do not type commas or dollar signs.

Question 16

CHAR data types are numbers without a decimal part.

Question 17

You can combine values in character fields as well as number fields.

Question 18

A WHERE and a HAVING clause cannot be included in the same query.

Question 19

To create numbers with decimals in Access,you can use the CURRENCY,NUMBER,or DECIMAL data types.

Question 20

The HAVING clause is to groups what the WHERE clause is to rows.

Question 21

To add new data to a table,use the ____ command.

A)ADDTO
B)DELETE
C)INSERT
D)SELECT

Question 22

In MySQL,the ____ is used as a wildcard to represent any collection of characters.

A)asterisk (*)
B)underscore (_)
C)percent sign (%)
D)question mark (?)

Question 23

To use a wildcard,include the ____ operator in the WHERE clause.

A)AS
B)UNION
C)BETWEEN
D)LIKE

Question 24

Based on the code above,list the number,name,credit limit,and balance for all customers with credit limits that exceed their balances.

A)SELECT CustomerNum,CustomerName,CreditLimit,Balance FROM Customer ;
B)SELECT CustomerNum,CustomerName,CreditLimit,Balance FROM Customer WHERE CreditLimit>Balance ;
C)SELECT CustomerNum,CustomerName,Balance FROM Customer WHERE CreditLimit>Balance ;
D)SELECT CustomerNum,CustomerName FROM Customer WHERE CreditLimit>Balance ;

Question 25

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.

A)&
B)*
C)$
D)#

Question 26

When you use a name containing a space in Access SQL,you must ____.

A)enclose it in square brackets
B)enclose it in quotation marks
C)precede it by an asterisk
D)precede it by a question mark

Question 27

In Access SQL,the ____ is used as a wildcard to represent any individual character.

A)asterisk (*)
B)underscore (_)
C)percent sign (%)
D)question mark (?)

Question 28

Based on the code above,list the number,name,street,and credit limit of all customers. Order the customers by name within descending credit limit.

A)SELECT CustomerNum,CustomerName,Street,CreditLimit FROM Customer SORT BY CreditLimit DESC,CustomerName ;
B)SELECT CustomerNum,CustomerName,Street,CreditLimit FROM Customer SORT BY CreditLimit ASC,CustomerName ;
C)SELECT CustomerNum,CustomerName,Street,CreditLimit FROM Customer ORDER BY CreditLimit DESC,CustomerName ;
D)SELECT CustomerNum,CustomerName,Street,CreditLimit FROM Customer ORDER BY CreditLimit ASC,CustomerName ;

Question 29

When rows are grouped,____.

A)the totals appear with the rows
B)the rows appear in order
C)no output is produced
D)one line of output is produced for each group

Question 30

You can save the results of a query as a table by including the ____ clause in the query.

A)INTO
B)INSERT
C)UPDATE
D)DELETE

Question 31

Based on the code above,list the number,name,and balance of all customers with balances greater than or equal to $1,000 and less than or equal to $5,000.

A)SELECT CustomerNum,CustomerName,Balance FROM Customer WHERE Balance BETWEEN 1000 AND 5000 ;
B)SELECT CustomerNum,CustomerName,Balance FROM Customer WHERE Balance > 1000 ;
C)SELECT CustomerName,Balance FROM Customer WHERE Balance BETWEEN 1000 AND 5000 ;
D)SELECT CustomerNum,CustomerName FROM Customer WHERE Balance BETWEEN 1000 AND 5000 ;

Question 32

Based on the code above,list the number,name,and complete address of every customer located on a street that contains the letters "Oxford".

A)SELECT CustomerNum,CustomerName,Street,City,State,Zip FROM Customer WHERE Street LIKE "?Oxford" ;
B)SELECT CustomerNum,CustomerName,Street,City,State,Zip FROM Customer WHERE Street LIKE "%Oxford%" ;
C)SELECT CustomerNum,CustomerName,Street,City,State,Zip FROM Customer WHERE Street LIKE "@Oxford@" ;
D)SELECT CustomerNum,CustomerName,Street,City,State,Zip FROM Customer WHERE Street LIKE "Oxford" ;

Question 33

The ____ function determines the largest value in a given column.

A)LGE
B)LARGE
C)MAX
D)MAXIMUM

Question 34

When a subquery is used,____ is(are) evaluated first.

A)the subquery query
B)the outer query
C)both a and b simultaneously
D)whichever query is selected to execute first by the user

Question 35

Based on the code above,list the number,name,and available credit for all customers with credit limits that exceed their balances.

A)SELECT CustomerNum,CustomerName AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;
B)SELECT CustomerNum,CustomerName,CreditLimit AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;
C)SELECT CustomerNum,CustomerName,Balance AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;
D)SELECT CustomerNum,CustomerName,CreditLimit-Balance AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;

Question 36

Based on the code above,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.

A)SELECT RepNum,AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ;
B)SELECT RepNum,COUNT(*),AVG(Balance) FROM Part GROUP BY RepNum ORDER BY RepNum ;
C)SELECT RepNum,COUNT(*),AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ;
D)SELECT RepNum,COUNT(*) FROM Customer GROUP BY RepNum ORDER BY RepNum ;

Question 37

Many versions of SQL require you to end a command with a ____.

A)comma (,)
B)period (.)
C)colon (:)
D)semicolon (;)

Question 38

In order to make changes to existing data in a table,you would use the ____ command.

A)DELETE
B)CHANGE
C)SELECT
D)UPDATE

Question 39

The basic form of an SQL retrieval command is ____.

A)SELECT-WHERE-FROM
B)CREATE-SELECT-FROM
C)SELECT-WHERE
D)SELECT-FROM-WHERE

Question 40

The ____ clause can be used to create groups of records.

A)SELECT
B)ORDER BY
C)SORT BY
D)GROUP BY

Question 41

In Access,you can combine values in character fields into a single computed field by using the ____________________ operator.

Question 42

A simple condition includes the field name,a(n) ____________________ operator,and either another field name or a value.

Question 43

Based on the code above,delete any row in the OrderLine table in which the part number is BV06.

A)DELETE FROM OrderLine WHERE PartNum='BV06' ;
B)DELETE FROM OrderLine WHERE PartNum=BV06 ;
C)DELETE FROM OrderLine WHERE PartNum='BV06'
D)DELETE FROM OrderLine WHERE PartNum='BV6' ;

Question 44

SQL was developed under the name ____________________ at the IBM San Jose research facilities.

Question 45

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.

Question 46

The ____________________ data type stores integers,but uses less space than INTEGER.

Question 47

Based on the code above,list the name of every student whose Zip code is 10113.

A)SELECT FirstName,LastName WHERE Zip='10113' ;
B)SELECT FirstName,LastName FROM Student WHERE Zip='10113' ;
C)SELECT FirstName,LastName FROM Student WHERE Zip='10113'
D)SELECT * FROM Student WHERE Zip='10113' ;

Question 48

Based on the code above,list the complete student table.

A)SELECT Student ;
B)SELECT & FROM Student ;
C)SELECT * FROM Student ;
D)SELECT LastName,FirstName,Street,City,State,Zip FROM Student

Question 49

Based on the code above,change the Zip code of the student with ID 11433 to 14455.

A)UPDATE Student SET Zip='14455' ;
B)UPDATE Student SET Zip WHERE StudentID='11433' ;
C)UPDATE Student IN Zip='14455' WHERE StudentID='11433' ;
D)UPDATE Student SET Zip='14455' WHERE StudentID='11433' ;

Question 50

Based on the code above,find how many parts are in item class HW.

A)SELECT SUM(*) FROM Part WHERE Class='HW' ;
B)SELECT COUNT(*) FROM Part WHERE Class='HW' ;
C)SELECT COUNT FROM Part WHERE Class='HW' ;
D)SELECT COUNT* FROM Part WHERE Class='HW' ;

Question 51

In MySQL,you use the ____________________ function to concatenate fields.

Question 52

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

Question 53

Based on the code above,list the descriptions of all parts that are not in Warehouse 3.

A)SELECT Description FROM Customer WHERE NOT Warehouse='3' ;
B)SELECT Description FROM Part WHERE Warehouse='4' ;
C)SELECT Description FROM Part WHERE Warehouse>'3' ;
D)SELECT Description FROM Part WHERE NOT Warehouse='3' ;

Question 54

As you are working in MySQL,the most recent command you entered is stored in a special area of memory called the ____________________.

Question 55

Based on the code above,find the name of the student whose ID is 1167.

A)SELECT FirstName,LastName FROM Course WHERE StudentID='1167' ;
B)SELECT FirstName,LastName FROM Part WHERE StudentID='1167' ;
C)SELECT FirstName,LastName FROM Student WHERE StudentID='1167' ;
D)SELECT FirstName,LastName FROM Customer WHERE StudentID='1167' ;

Question 56

Based on the code above,list the descriptions of all parts that are located in Warehouse 3 and for which there are more than 20 units on hand.

A)SELECT Description FROM Part WHERE Warehouse='3' AND OnHand>20 ;
B)SELECT Description FROM Part WHERE Warehouse='3' OR OnHand>20 ;
C)SELECT Description FROM Part WHERE Warehouse='3' ;
D)SELECT Description FROM Customer WHERE Warehouse='3' AND OnHand>20 ;

Question 57

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

Question 58

One common restriction placed on table and column names by DBMSs is that names can contain only letters,numbers,and ____________________.

Question 59

Based on the code above,list the descriptions of all parts that are located in Warehouse 3 or for which there are more than 20 units on hand,or both.

A)SELECT Description FROM Customer WHERE Warehouse='3' AND OnHand>20 ;
B)SELECT Description FROM Part WHERE Warehouse='3' OR OnHand>20 ;
C)SELECT Description FROM Part WHERE Warehouse='3' ;
D)SELECT Description FROM Part WHERE OnHand>20 ;

Question 60

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

Question 61

To qualify a field name,precede the name of the field with the name of the table,followed by a(n) ____________________.

Question 62

Describe how to construct a detailed query in a step-by-step fashion.

Question 63

When you need to sort data on two fields,the more important sort key is called the ____________________ sort key.

Question 64

The two tables involved in a union must have the same structure,or be ____________________.

Question 65

SQL has built-in functions,which are also called ____________________ functions.

Question 66

The ____________________ of two tables is a table containing all rows that are in either the first table,the second table,or both.

Question 67

In SQL,you sort data using the ____________________ clause.

Question 68

Discuss the restriction concerning the structure of two tables involved in a union.

Question 69

What are some common restrictions place on table and column names by DBMSs?

Question 70

When you place one query inside another,the inner query is called a(n)____________________.

Question 71

Many versions of SQL,including MySQL,use the ____________________ as a wildcard to represent any individual character.

Question 72

When you need to sort data on two fields,the less important key is called the ____________________ sort key.

Question 73

Describe five data types that you will often encounter when working with databases.

Question 74

How does the format for dates in queries in Access differ from other implementations of SQL?