Deck 3: The Relational Model 2: SQL

Question 1

SQL uses an on-screen form to create tables, update tables, and retrieve data from tables.

Answer 1

False

Question 2

You can use the SQL CREATE TABLE command to insert rows into a table.

Answer 2

False

Question 3

Microsoft Access uses the != version of the "not equal to" operator.

Answer 3

False

Question 4

A compound condition includes either or both of the AND and OR operators.

Question 5

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

Question 6

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

Question 7

Fields will appear in alphabetical order in the query results of a SELECT clause.

Question 8

The BETWEEN operator is exclusive; it includes all numbers between the lower and higher numbers, but excludes the lower and higher numbers themselves.

Question 9

You use the WHERE clause on groups and the HAVING clause on rows.

Question 10

Computed fields are existing fields in the database that you can perform computations with.

Question 11

A valid name for a table might be tbl$Student.

Question 12

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

Question 13

Preceding a condition by the NOT operator reverses the result of the original condition.

Question 14

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

Question 15

When you connect simple conditions using the AND operator, only one of the simple conditions must be true for the compound condition to be true.

Question 16

There is no difference between the COUNT function and the SUM function.

Question 17

The two tables involved in a join operation must have the same structure.

Question 18

In a SELECT statement, the WHERE clause is mandatory.

Question 19

CHAR data types can be used to store numbers that will not be used in calculations.

Question 20

You can combine values in character fields using the & operator.

Question 21

What clause do you use in a query to save the results of the query as a table?

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

Question 22

While not always required, how should you end an SQL command?

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

Question 23

When you use a name containing a space in Access SQL, what must you do to specify the table or column name?

A) enclose it in quotation marks
B) enclose it in square brackets
C) enclose it in asterisks
D) enclose it in question marks

Question 24

What command do you use to make changes to existing data in a table?

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

Question 25

What command do you use to add new data to a table?

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

Question 26

Which part of the SQL query specifies the table or tables that contain the data you wish to display in the query results?

A) SELECT clause
B) WHERE clause
C) WITHIN clause
D) FROM clause

Question 27

Which query will return the number of rows where the State field is 'AZ' from the table Customers?

A) SELECT FROM Customers WHERE State='AZ' COUNT(*);
B) SELECT COUNT(*) FROM Customers WHERE State='AZ';
C) SELECT FROM Customers COUNT(*) WHERE State='AZ';
D) SELECT COUNT(*) WHERE State='AZ' FROM Customers;

Question 28

In versions of SQL other than Access, which character is used as a wildcard to represent any collection of characters?

A) asterisks (*)
B) percent sign (%)
C) underscore (_)
D) hash tag (#)

Question 29

How many lines of output are produced when rows are grouped?

A) one line for each group member
B) two lines for each group
C) one line for each group
D) no output is produced

Question 30

Which query will return the number of rows where the State field is 'AZ' from the table Customers and provide a total of the Payment field?

A) SELECT COUNT(*) WHERE State='AZ' SUM(Payment) FROM Customers;
B) SELECT FROM Customers COUNT(Payment) SUM(*) WHERE State='AZ';
C) SELECT FROM Customers SUM(Payment) WHERE State='AZ' COUNT(*);
D) SELECT COUNT(*), SUM(Payment) FROM Customers WHERE State='AZ';

Question 31

Based on the code shown, which query lists the number, name, and available credit for all customers with credit limits that exceed their balances???Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

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 32

When used after the word SELECT, which 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 33

Which function calculates the number of entries in a table?

A) COUNT
B) SUM
C) MAX
D) MIN

Question 34

Based on the code shown, which query lists the number, name, and complete address of every customer located on a street that contains the letters "Oxford"???Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

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

Question 35

Based on the code shown, for each sales rep, which query lists the rep number, the number of customers assigned to the rep, and the average balance of the rep's customers? The records are grouped by rep number and ordered by rep number.??Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

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 36

Based on the code shown, which query lists 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???Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

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

Question 37

Which operator do you use in the WHERE clause when you want to include a wildcard?

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

Question 38

Based on the code shown, which query lists the number, name, credit limit, and balance for all customers with credit limits that exceed their balances???Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

A) SELECT CustomerNum, CustomerName FROM Customer WHERE CreditLimit>Balance ;
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, CreditLimit, Balance FROM Customer ;

Question 39

In Access SQL, which character is used as a wildcard to represent any individual character?

A) underscore (_)
B) hash tag (#)
C) asterisks (*)
D) question mark (?)

Question 40

Based on the code shown, which query lists the number, name, street, and credit limit of all customers? The records are ordered by customer name within descending credit limit.??Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

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 41

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' ;

Question 42

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

Question 43

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 ;

Question 44

SQL is the standard language for relational ___________________ manipulation.

Question 45

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

Question 46

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 ;

Question 47

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

Question 48

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' ;

Question 49

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

Question 50

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

Question 51

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 ;

Question 52

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 53

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

Question 54

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' ;

Question 55

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' ;

Question 56

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

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 must start with a(n) ____________________.

Question 59

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' ;

Question 60

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 ;

Question 61

In a DBMS such as Oracle or SQL Server, the _______________ command reverses changes to a database.

Question 62

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

Question 63

The ____________ clause is to groups as the WHERE clause is to rows.

Question 64

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

Question 65

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

Question 66

Use the ____________________ clause to sort data.

Question 67

How are compound conditions formed?

Question 68

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

Question 69

When you place one query inside another, the inner query is called a ________________ and is evaluated first.

Question 70

____________________ means creating groups of records that share some common characteristics.

Question 71

Describe how to construct a complex join query in a systematic fashion.

Question 72

SQL has built-in functions, which are also called ____________________ functions to calculate the number of entries, or the sum or average of all entries.

Question 73

Use the ___________________ clause to indicate grouping.

Question 74

Use the _____________ function to calculate the number of rows that match the query.

Question 75

Use the word ____________ in the query to specify that data should be sorted in high to low order.