Database Systems Chapter 7 - Introduction to Structured Query Language (SQL) - BCIS 56

Index

A common practice is to create a(n) _____ on any field that is used as a search key, in comparison operations in a conditional expression, or when a user wants to list rows in a specific order.

True

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

Boolean

A specialty field in mathematics, known as _____ algebra, is dedicated to the use of logical operators.

DROP TABLE

A table can be deleted from the database bu using the ___ command.

Wildcard

A(n) _____ character is a symbol that can be used as a general substitute for other characters or commands.

subquery

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

alias

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

True

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

Performing operations within parentheses

According to the rules of precedence, which of the following computations should be completed first?

False

All SQL commands must be issued on a single line.

ALTER TABLE

All changes in a table structure are made using the _____ command, followed by a keyword that produces the specific changes a user wants to make.

True

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.

False

An alias cannot be used when a table is required to be joined to itself in a recursive query.

Recursive

An alias is especially useful when a table must be joined to itself in a(n) _____ query.

UPDATE PRODUCT SET P_INDATE = '18-JAN-2004' WHERE P_CODE = '13-Q2/P2';

An example of a command a user would use when making changes to a PRODUCT table is _____.

False

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

True

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

True

Entity integrity is enforced automatically when the primary key is specified in the CREATE TABLE command sequence.

--1. SQL is a data definition language (DDL). It includes commands to create database objects such as tables, indexes, and views, as well as commands to define access rights to those databases objects. --2. SQL is a data manipulation language (DML). It includes commands to insert, update, delete, and retrieve data within the database tables.

Explain the two SQL functions.

Drop table, for example: DROP TABLE AUTHORS; (This command will not be executed if the Authors table is on the one side of any relationship.)

How can a table be deleted from the database? Provide an example.

True

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.

FORMAT

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

One

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

Attribute names

In an INSERT command, a user can indicate just the attributes that have required values by listing the _____ inside parentheses after the table name.

Query

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

False

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

False

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

True

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

False

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

False

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

True

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

True

String comparisons are made from left to right.

True

The ANSI SQL standards are also accepted by the ISO.

False

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

False

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

True

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

COUNT

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

SELECT c COMMIT

The SQL command that allows a user to list the contents of a table is _____.

Commit

The SQL command that allows a user to permanently save data changes is _____.

restricts the selection of grouped rows based on a condition.

The SQL data manipulation command HAVING:

SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;

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

ROLLBACK;

The _____ command is used to restore the database to its previous condition.

DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345';

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

Select

The _____ command, coupled with appropriate search conditions, is an incredibly powerful tool that enables a user to transform data into information.

DEFAULT

The _____ constraint assigns a value to an attribute when a new row is added to a table.

True

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

SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; ORDER BY PRODUCT.P_PRICE;

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 _____.

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

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 _____.

EXISTS

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

between

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

LIKE

The special operator used to check whether an attribute value matches a given string pattern is _____.

Drop index

To delete an index, one must use the _____ command.

Readable

To make SQL code more____ , most SQL programmers use one line per column (attribute) definition.

SET columnname=expression

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

% and _. '%' is used when replacing an unspecified number of characters; for instance, 'S%' could indicate either Smith or Sanchez. '-' is used to replace only one character; 'J_n' could represent either Jon or Jan.

What are the wildcard characters that are used with the LIKE command? Provide one or more examples of each.

A subquery is the query-within-a-query inside a nested query. It is used when the outer query depends on data processed by the inner query. Because of this, he RDBMS always processes the subquery first.

What is a subquery? When is it used? Does the RDBMS deal with subqueries any differently from normal queries?

all rows will be deleted

When a user issues the DELETE FROM tablename command without specifying a WHERE condition, _____.

SELECT <column(s)> FROM <Table name> WHERE <Conditions>;

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

SELECT DISTINCT V_CODE FROM PRODUCT;

Which of the following queries 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?

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288;

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

SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= '20-JAN-2016';

Which of the following queries will list all the rows in which the inventory stock dates occur on or after January 20, 2016?

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;

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

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344;

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

SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE <'1558-QW1';

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

SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE FROM PRODUCT;

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

Reserved

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