Deck 3: The Relational : SQL

Question 1

In a SELECT statement,the WHERE clause is mandatory.​

Answer 1

False

Question 2

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

Answer 2

False

Question 3

The IN operator provides a concise way of phrasing certain conditions.​

Answer 3

True

Question 4

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

Question 5

When you connect simple conditions using the AND operator,all the simple conditions must be false for the compound condition to be true.​

Question 6

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

Question 7

The BETWEEN operator is an essential feature of SQL.​

Question 8

​You can combine values in character fields.

Question 9

SQL uses commands to create tables,update tables,and retrieve data from tables.​

Question 10

CHAR data types are numbers without a decimal part.​

Question 11

For each pair of tables to be joined,a condition must be included indicating how the tables are related.​

Question 12

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

Question 13

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

Question 14

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

Question 15

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

Question 16

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

Question 17

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

Question 18

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

Question 19

A simple condition includes the field name,a comparison operator,and either another field name or a value.​

Question 20

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

Question 21

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 22

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

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

Question 23

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

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

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 25

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

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

Question 26

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

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

Question 27

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

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

Question 28

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

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

Question 29

The ____ function calculates the number of entries in a table.​

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

Question 30

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

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 31

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, 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 32

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

A) ​AGGREGATE
B) ​SORT BY
C) ​ORDER BY
D) ​GROUP BY

Question 33

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 34

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 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 35

In versions of SQL other than Access,the ____ is used as a wildcard to represent any collection of characters.​

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

Question 36

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 37

​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

Question 38

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 39

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) ​ampersand
C) ​#
D) ​$

Question 40

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

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

Question 41

A field whose values you derive from existing fields is called a(n)____________________ field.​

Question 42

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

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 43

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 * WHERE StudentID='1167' ;
C) ​SELECT FirstName, LastName FROM Student WHERE StudentID='1167' ;
D) ​SELECT FirstName, LastName FROM Customer WHERE StudentID='1167' ;

Question 44

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

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 45

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

Question 46

Based on the code above,delete any row in the OrderLine table in which the item number is MT03.​

A) ​DELETE FROM OrderLine WHERE ItemNum='MT03' ;
B) ​DELETE FROM OrderLine WHERE ItemNum=MT03;
C) ​DELETE FROM OrderLine WHERE ItemNum='MT03'
D) ​DELETE FROM OrderLine WHERE ItemNum='MT3' ;

Question 47

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

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 48

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

A) ​SELECT Student ;
B) ​SELECT "ampersand" FROM Student ;
C) ​SELECT * FROM Student ;
D) ​SELECT LastName, FirstName, Street, City, State, PostalCode FROM Student

Question 49

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

Question 50

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

Question 51

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

Question 52

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

Question 53

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

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 54

Based on the code above,find how many items are in category TOY.​

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 55

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

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 56

​One common restriction placed on table and column names by DBMSs is that names cannot exceed ____________________ characters.

Question 57

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

Question 58

​Based on the code above,list the descriptions of all items that are not in Storehouse 3.Based on the code above,list the descriptions of all items that are not in Storehouse 3.

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 59

One common restriction placed on table and column names by DBMSs is that names must start with a(n)____________________.​

Question 60

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 61

How are compound conditions formed?​

Question 62

In Access,you use the ____________________ operator to concatenate fields.​

Question 63

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

Question 64

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

Question 65

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

Question 66

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

Question 67

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

Question 68

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

Question 69

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

Question 70

In SQL,you sort data using the ____________________ clause.​

Question 71

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

Question 72

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

Question 73

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

Question 74

Many versions of SQL other than Access use the ____________________ as a wildcard to represent any individual character.​

Question 75

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