Deck 7: Introduction to Structured Query Language (SQL)

Question 1

You can select partial table contents by naming the desired fields and by placing restrictions on the rows to be included in the output.

Answer 1

True

Question 2

String comparisons are made from left to right.

Answer 2

True

Question 3

The CHECK constraint is used to define a condition for the values that the attribute domain cannot have.

Answer 3

False

Question 4

SQL is considered difficult to learn; its command set has a vocabulary of more than 300 words.

Question 5

A database language enables the user to perform complex queries designed to transform the raw data into useful information.

Question 6

To list the contents of a table,you must use the DISPLAY command.

Question 7

The ANSI prescribes a standard SQL-the current fully approved version is known as SQL-07.

Question 8

A database language enables the user to create database and table structures to perform basic data management chores.

Question 9

The COMMIT command does not permanently save all changes.In order to do that,you must use SAVE.

Question 10

SQL requires the use of the ADD command to enter data into a table.

Question 11

Any changes made to the contents of a table are not physically saved on disk until you use the SAVE command.

Question 12

Only numeric data types can be added and subtracted in SQL.

Question 13

The ANSI SQL standards are also accepted by the ISO.

Question 14

Since computers identify all characters by their numeric codes,mathematical operators cannot be used to place restrictions on character-based attributes.

Question 15

Data type selection is usually dictated by the nature of the data and by the intended use.

Question 16

Although SQL commands can be grouped together on a single line,complex command sequences are best shown on separate lines,with space between the SQL command and the command's components.

Question 17

You cannot insert a row containing a null attribute value using SQL.

Question 18

All SQL commands must be issued on a single line.

Question 19

Oracle users can use the Access QBE (query by example)query generator.

Question 20

If you have not yet used the COMMIT command to store the changes permanently in the database,you can restore the database to its previous condition with the ROLLBACK command.

Question 21

To list all the contents of the PRODUCT table,you would use ____.

A) LIST * FROM PRODUCT;
B) SELECT * FROM PRODUCT;
C) DISPLAY * FROM PRODUCT;
D) SELECT ALL FROM PRODUCT;

Question 22

You can create a logical (virtual)table by using the MAKE VIEW command.

Question 23

In Oracle,the ____ command is used to change the display for a column,for example,to place a $ in front of a numeric value.

A) DISPLAY
B) FORMAT
C) CHAR
D) CONVERT

Question 24

ANSI-standard SQL allows the use of special operators in conjunction with the WHERE clause.

Question 25

Date procedures are often more software-specific than other SQL procedures.

Question 26

The SQL command that modifies an attribute's values in one or more table's rows is ____.

A) INSERT
B) SELECT
C) COMMIT
D) UPDATE

Question 27

The SQL command that lets you insert rows into a table is ____.

A) INSERT
B) SELECT
C) COMMIT
D) UPDATE

Question 28

To join tables,simply enumerate the tables in the FROM clause of the SELECT statement.The DBMS will create a Cartesian product of every table in the FROM clause.To get the correct results,you need to select the rows in which the common attributes do not match.

Question 29

The SQL command that lets you select attributes from rows in one or more tables is ____.

A) INSERT
B) SELECT
C) COMMIT
D) UPDATE

Question 30

When joining three or more tables,you need to specify a join condition for one pair of tables.

Question 31

The SQL command that lets you permanently save data changes is ____.

A) INSERT
B) SELECT
C) COMMIT
D) UPDATE

Question 32

Some RDBMSs,such as Microsoft Access,automatically make the necessary conversions to eliminate case sensitivity.

Question 33

Most SQL implementations yield case-insensitive searches.

Question 34

SQL character data format(s)is(are)____.

A) CHAR and VARCHAR
B) VARCHAR only
C) Alphanumeric
D) CHAR only

Question 35

UPDATE tablename
*****
[WHERE conditionlist];
The ____ command replaces the ***** in the syntax of the UPDATE command,shown above.

A) SET columnname = expression
B) columnname = expression
C) expression = columnname
D) LET columnname = expression

Question 36

COUNT is designed to tally the number of non-null "values" of an attribute,and is often used in conjunction with the DISTINCT clause.

Question 37

SQL allows the use of logical restrictions on its inquiries such as OR,AND,and NOT.

Question 38

The most recent fully approved version of standard SQL prescribed by the ANSI is ____.

A) SQL-99
B) SQL-2003
C) SQL-4
D) SQL-07

Question 39

An example of a command you would use when making changes to a PRODUCT table is ____.

A) CHANGE PRODUCT
SET P_INDATE = '18-JAN-2004'
WHERE P_CODE = '13-Q2/P2';
B) ROLLBACK PRODUCT
SET P_INDATE = '18-JAN-2004'
WHERE P_CODE = '13-Q2/P2';
C) EDIT PRODUCT
SET P_INDATE = '18-JAN-2004'
WHERE P_CODE = '13-Q2/P2';
D) UPDATE PRODUCT
SET P_INDATE = '18-JAN-2004'
WHERE P_CODE = '13-Q2/P2';

Question 40

The conditional LIKE must be used in conjunction with wildcard characters.

Question 41

The ____ command is used to restore the table's contents to their previous values.

A) COMMIT; RESTORE;
B) COMMIT; BACKUP;
C) COMMIT; ROLLBACK;
D) ROLLBACK;

Question 42

Which query uses the correct SQL syntax to list the table contents for either V_CODE = 21344 or V_CODE = 24288?

A) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE = 21344
OR V_CODE <= 24288
B) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE = 21344
OR V_CODE => 24288
C) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE = 21344
OR V_CODE > 24288
D) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE = 21344
OR V_CODE = 24288

Question 43

Which of the following is used to select partial table contents?

A) SELECT
FROM
BY ;
B) LIST
FROM
BY ;
C) SELECT
FROM
WHERE ;
D) LIST
FROM
WHERE ;

Question 44

To delete a row from the PRODUCT table,use the ____ command.

A) COMMIT
B) DELETE
C) ERASE
D) KILL

Question 45

The special operator used to check for similar character strings is ____.

A) BETWEEN
B) IS NULL
C) LIKE
D) IN

Question 46

Which query will list all the rows in which the inventory stock dates occur on or after January 20,2010?

A) SELECT P_DESCRIPT,P_QOH,P_MIN,P_PRICE,P_INDATE
FROM PRODUCT
WHERE P_INDATE >= '20-JAN-2010'
B) SELECT P_DESCRIPT,P_QOH,P_MIN,P_PRICE,P_INDATE
FROM PRODUCT
WHERE P_INDATE >= $20-JAN-2010$
C) SELECT P_DESCRIPT,P_QOH,P_MIN,P_PRICE,P_INDATE
FROM PRODUCT
WHERE P_INDATE <= '20-JAN-2010'
D) SELECT P_DESCRIPT,P_QOH,P_MIN,P_PRICE,P_INDATE
FROM PRODUCT
WHERE P_INDATE >= {20-JAN-2010}

Question 47

The special operator used to check whether an attribute value is within a range of values is ____.

A) BETWEEN
B) NULL
C) LIKE
D) IN

Question 48

Which query will output the table contents when the value of V_CODE is not equal to 21344?

A) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE <> 21344;
B) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE <= 21344;
C) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;
D) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE => 21344;

Question 49

When you issue the DELETE FROM tablename command without specifying a WHERE condition,____.

A) no rows will be deleted
B) the first row will be deleted
C) the last row will be deleted
D) all rows will be deleted

Question 50

The ____ command would be used to delete the table row where the P_CODE is 'BRT-345'.

A) DELETE FROM PRODUCT
WHERE P_CODE = 'BRT-345';
B) REMOVE FROM PRODUCT
WHERE P_CODE = 'BRT-345';
C) ERASE FROM PRODUCT
WHERE P_CODE = 'BRT-345';
D) ROLLBACK FROM PRODUCT
WHERE P_CODE = 'BRT-345';

Question 51

Which query will use the given columns and column aliases from the PRODUCT table to determine the total value of inventory held on hand and display the results in a column labeled TOTVALUE?

A) SELECT P_DESCRIPT,P_QOH,P_PRICE,P_QOH*P_PRICE AS TOTVALUE
FROM PRODUCT;
B) SELECT P_DESCRIPT,P_QOH,P_PRICE,P_QOH=P_PRICE AS TOTVALUE
FROM PRODUCT;
C) SELECT P_DESCRIPT,P_QOH,P_PRICE,P_QOH/P_PRICE AS TOTVALUE
FROM PRODUCT;
D) SELECT P_DESCRIPT,P_QOH,P_PRICE,P_QOH-P_PRICE AS TOTVALUE
FROM PRODUCT;

Question 52

Which query will output the table contents when the value of P_PRICE is less than or equal to 10?

A) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE P_PRICE <> 10;
B) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE P_PRICE <= 10;
C) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE P_PRICE => 10;
D) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE P_PRICE = 10;

Question 53

The ____ special operator is used to check whether an attribute value is null.

A) BETWEEN
B) IS NULL
C) LIKE
D) IN

Question 54

Which query will use the given columns and column aliases from the PRODUCT table to determine the total value of inventory held on hand?

A) SELECT P_DESCRIPT,P_QOH,P_PRICE,P_QOH/P_PRICE
FROM PRODUCT;
B) SELECT P_DESCRIPT,P_QOH,P_PRICE,P_QOH=P_PRICE
FROM PRODUCT;
C) SELECT P_DESCRIPT,P_QOH,P_PRICE,P_QOH*P_PRICE
FROM PRODUCT;
D) SELECT P_DESCRIPT,P_QOH,P_PRICE,P_QOH-P_PRICE
FROM PRODUCT;

Question 55

Which query will output the table contents when the value of the character field P_CODE is alphabetically less than 1558-QW1?

A) SELECT P_CODE,P_DESCRIPT,P_QOH,P_MIN,P_PRICE
FROM PRODUCT
WHERE P_CODE <'1558-QW1';
B) SELECT P_CODE,P_DESCRIPT,P_QOH,P_MIN,P_PRICE
FROM PRODUCT
WHERE P_CODE = [1558-QW1]
C) SELECT P_CODE,P_DESCRIPT,P_QOH,P_MIN,P_PRICE
FROM PRODUCT
WHERE P_CODE = (1558-QW1)
D) SELECT P_CODE,P_DESCRIPT,P_QOH,P_MIN,P_PRICE
FROM PRODUCT
WHERE P_CODE = {1558-QW1}

Question 56

Which query will output the table contents when the value of V_CODE is equal to 21344?

A) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE <> 21344;
B) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE <= 21344;
C) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;
D) SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE
FROM PRODUCT
WHERE V_CODE => 21344;

Question 57

Some RDBMSs,such as Oracle,automatically ____ data changes when issuing data definition commands.

A) COMMIT
B) ROLLBACK
C) UNSAVE
D) UPDATE

Question 58

A(n)____ is an alternate name given to a column or table in any SQL statement.

A) alias
B) data type
C) stored function
D) trigger

Question 59

The special operator used to check whether a subquery returns any rows is ____.

A) BETWEEN
B) EXISTS
C) LIKE
D) IN

Question 60

A(n)____ is a query that is embedded (or nested)inside another query.

A) alias
B) operator
C) subquery
D) view

Question 61

A(n)____________________ is a group of database objects,such as tables and indexes,that are related to each other.

Question 62

DATE()and SYSDATE are special functions that return today's date in MS Access and ____________________,respectively.

Question 63

A table can be deleted from the database by using the ____ command.

A) DROP TABLE
B) DELETE TABLE
C) MODIFY TABLE
D) ERASE TABLE

Question 64

Which query is used to list a unique value for V_CODE,where the list will produce only a list of those values that are different from one another?

A) SELECT ONLY V_CODE
FROM PRODUCT;
B) SELECT UNIQUE V_CODE
FROM PRODUCT;
C) SELECT DIFFERENT V_CODE
FROM PRODUCT;
D) SELECT DISTINCT V_CODE
FROM PRODUCT;

Question 65

Using the ____________________ command,SQL indexes can be created on the basis of any selected attribute.

Question 66

The SQL aggregate function that gives the number of rows containing non-null values for the given column is ____.

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

Question 67

The SQL aggregate function that gives the total of all values for a selected attribute in a given column is ____.

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

Question 68

In the SQL environment,the word ____________________ covers both questions and actions.

Question 69

The ____ command is used with the ALTER TABLE command to modify the table by deleting a column.

A) DROP
B) REMOVE
C) DELETE
D) ERASE

Question 70

The basic SQL vocabulary has fewer than ____________________ words.

Question 71

The SQL query to output the contents of the EMPLOYEE table sorted by last name,first name,and initial is ____.

A) SELECT EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_AREACODE,EMP_PHONE
FROM EMPLOYEE
LIST BY EMP_LNAME,EMP_FNAME,EMP_INITIAL;
B) SELECT EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_AREACODE,EMP_PHONE
FROM EMPLOYEE
ORDER BY EMP_LNAME,EMP_FNAME,EMP_INITIAL;
C) SELECT EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_AREACODE,EMP_PHONE
FROM EMPLOYEE
DISPLAY BY EMP_LNAME,EMP_FNAME,EMP_INITIAL;
D) SELECT EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_AREACODE,EMP_PHONE
FROM EMPLOYEE
SEQUENCE BY EMP_LNAME,EMP_FNAME,EMP_INITIAL;

Question 72

The query used to list the P_CODE,P_DESCRIPT,P_INDATE,and P_PRICE fields from the PRODUCT table in ascending order by P_PRICE is ____.

A) SELECT P_CODE,P_DESCRIPT,P_INDATE,P_PRICE
FROM PRODUCT
SEQUENCE BY P_PRICE;
B) SELECT P_CODE,P_DESCRIPT,P_INDATE,P_PRICE
FROM PRODUCT
LIST BY P_PRICE;
C) SELECT P_CODE,P_DESCRIPT,P_INDATE,P_PRICE
FROM PRODUCT
ORDER BY P_PRICE;
D) SELECT P_CODE,P_DESCRIPT,P_INDATE,P_PRICE
FROM PRODUCT
ASCENDING BY P_PRICE;

Question 73

If your integer values are relatively small,use ____________________ instead of INT.

Question 74

With the exception of the ____________________ process,most RDBMS vendors use SQL that deviates little from the ANSI standard SQL.

Question 75

The query to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME,V_AREACODE,V_PHONE and V_CONTACT fields from the VENDOR table,where the values of V_CODE match and the output is ordered by the price is ____.

A) SELECT P_DESCRIPT,P_PRICE,V_NAME,V_CONTACT,V_AREACODE,V_PHONE
FROM PRODUCT,VENDOR
WHERE PRODUCT.V_CODE <> VENDOR.V_CODE;
ORDER BY P_PRICE;
B) SELECT P_DESCRIPT,P_PRICE,V_NAME,V_CONTACT,V_AREACODE,V_PHONE
FROM PRODUCT,VENDOR
WHERE PRODUCT.V_CODE => VENDOR.V_CODE;
ORDER BY P_PRICE;
C) SELECT P_DESCRIPT,P_PRICE,V_NAME,V_CONTACT,V_AREACODE,V_PHONE
FROM PRODUCT,VENDOR
WHERE PRODUCT.V_CODE <= VENDOR.V_CODE;
ORDER BY P_PRICE;
D) SELECT P_DESCRIPT,P_PRICE,V_NAME,V_CONTACT,V_AREACODE,V_PHONE
FROM PRODUCT,VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
ORDER BY P_PRICE;

Question 76

In a 1:M relationship,you must always create the table for the ____________________ side first.

Question 77

____________________ words are words used by SQL to perform specific functions.

Question 78

The SQL aggregate function that gives the average for the specific column is ____.

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

Question 79

U.S.state abbreviations are always two characters,so ____________________(2)is a logical choice for the data type representing a state column.

Question 80

The query to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME,V_AREACODE,V_PHONE,and V_CONTACT fields from the VENDOR table where the values of V_CODE match is ____.

A) SELECT P_DESCRIPT,P_PRICE,V_NAME,V_CONTACT,V_AREACODE,V_PHONE
FROM PRODUCT,VENDOR
WHERE PRODUCT.V_CODE <> VENDOR.V_CODE;
B) SELECT P_DESCRIPT,P_PRICE,V_NAME,V_CONTACT,V_AREACODE,V_PHONE
FROM PRODUCT,VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
C) SELECT P_DESCRIPT,P_PRICE,V_NAME,V_CONTACT,V_AREACODE,V_PHONE
FROM PRODUCT,VENDOR
WHERE PRODUCT.V_CODE <= VENDOR.V_CODE;
D) SELECT P_DESCRIPT,P_PRICE,V_NAME,V_CONTACT,V_AREACODE,V_PHONE
FROM PRODUCT,VENDOR
WHERE PRODUCT.V_CODE => VENDOR.V_CODE;