Chapter 3 - SQL

UNION

A ______________ query combines the records of two separate queries. ________________ queries require that the two queries have the same field names and datatypes.

first

An SQL query may contain an inner query is called a subquery. The subquery is evaluated ________, and is generally used to limit the records that are selected by the outer query.

IN

Another SQL operator, _______, provides a concise way of creating a condition to match a specific list of criteria.

AS

Computed fields, also called calculated fields, are created in SQL queries by including the expression, followed by the word ______, followed by the name of the computed field.

GROUP BY HAVING

Grouping is accomplished in SQL by using the _____________ clause. To restrict the rows to be displayed when they are previously grouped, use the _________ clause.

SELECT FROM WHERE

In SQL SELECT queries, fields are listed in the __________ clause, tables are listed in the _________ clause, and conditions are listed in the ______ clause.

apostrophes quotation

In WHERE conditions, character values must be enclosed in _____________ (some versions of SQL also use _____________ marks).

WHERE

Joining tables can be accomplished in SQL by using a _______________ condition that identifies the common field upon which two tables in a one-to-many relationship are connected.

automatically updated

Only the tables store the data. You can create as many queries (logical views) as desired knowing that any updates to the data are ______________ ________________ when each SELECT query is run.

logical view view

SELECT queries are often referred to as a ________ ______or merely a ________ of the data.

aggregate

SQL has the built-in (also called ___________)functions such as COUNT, SUM, AVG, MAX, and MIN to calculate statistics on groups of records.

ORDER BY

Sorting is accomplished using the ___________ clause

INSERT

The SQL _________ command is used to append a new row to a table.

INTO

The SQL ____________ clause issued in a SELECT clause to create a new table that is a copy rather than a view of the data.

DELETE

The SQL ____________ command is used to delete existing records.

UPDATE

The SQL _____________ command issued to change existing data in a field.

UNION

The _____ operator allows you to select the records that match each condition separately, and then combine them into a single view. a. COMBINE b. MATCH c. UNION d. MERGE

SELECT-FROM-WHERE

The basic form of an SQL query that selects data from the database is ________________________.

sort key

The field on which the records are sorted is called the _____________.

True

The fundamental features of SQL and SQL concepts are the same across all relational database management systems, but systems use different versions of SQL that have slightly different syntax and key words. T or F

data-definition language (DDL)

The part of SQL that is used for creating and altering database objects is called

LIKE

To select records that match a pattern, use wildcard characters such as the asterisk(*) to represent the pattern. To use a wildcard in the criterion for an SQL query, use the _____________ operator in the WHERE clause.

CREATE TABLE

Use the ____________ ____________ SQL DDL command to describe and create a new table by defining its fields and datatypes.

major sort key primary sort key

When the data is sorted in more than one field, the more important field is called the ______________ or ______________. The less important field is called the minor sort key or secondary sort key.

alias

When using the SQL AS keyword to rename a field, the new field name is called an alias.

ORDER BY LastName, FirstName

Which SQL clause sorts the selected records in ascending order by FirstName within LastName? a. ORDER BY FirstName, LastName b. ORDER BY FirstName, LastName ASC c. ORDER BY LastName, FirstName d. ORDER BY LastName & FirstName

WHERE Customers.CustID = Orders.CustID

Which SQL clause would join a Customers table with an Orders table assuming that they are related in a one-to-many relationship on a field called CustID in both tables? a. WHERE Customers.CustID = Orders.CustID b. WHERE Customers = Orders AND CustID = CustID c. WHERE Customers.CustID & Orders.CustID d. WHERE IN Customers, Orders (CustID)

WHERE

Which SQL command would be used to join the TaskMastList and ProjectLineItems tables? a. CONNECT b. COMBINE c. LINK d. WHERE

FROM

Which SQL key word identifies the tables that are involved in a SELECT query? a. FROM b. HAVING c. TABLE d. WHERE

SELECT

Which SQL keyword starts an SQL statement to select fields and records from one or more related tables? a. LIST b. SELECT c. SORT d. SUM

OR

Which SQL keyword would be used to find all TaskMasterList records with a CategoryID fieldvalue of Testing or Support? a. AND b. ORDER c. && d. OR

INSERT INTO

Which SQL keyword(s) start an SQL statement to append a new record to a table? a. ADD b. APPEND c. CONCATENATE d. INSERT INTO

BETWEEN

Which SQL operator can be rewritten with >= AND <= operators? a. BETWEEN b. CONTAINS c. IN d. WITH

IN

Which SQL operator provides a concise way of creating a condition to match a specific list of criteria? a. BETWEEN b. CONTAINS c. IN d. NOT

SELECT Salary * 0.1 AS Bonus FROM Employees;

Which SQL statement calculates the Bonus field in the Employees table as 10 percent of Salary? a. SELECT Salary * 0.1 AS Bonus FROM Employees; b. SELECT Bonus AS Salary * 0.1 FROM Employees; c. SELECT * FROM Employees WHERE Bonus = Salary * 0.1; d. SELECT Salary * 0.1 = Bonus FROM Employees;

SELECT LastName, Salary FROM Employees WHERE Salary < (SELECT Salary FROM Employees WHERE EmployeeID = '72');

Which SQL statement lists the last names and salaries of employees who have a Salary field value less than Amir Nasser, EmployeeID 72? a. SELECT LastName, Salary FROM Employees WHERE Salary < . UNION SELECT LastName, Salary FROM Employees WHERE EmployeeID = '72'; b. SELECT LastName, Salary FROM Employees WHERE Salary < UPDATE SET EmployeeID = '72'; c. SELECT LastName, Salary FROM Employees HAVING Salary < EmployeeID = '72'; d. SELECT LastName, Salary FROM Employees WHERE Salary < (SELECT Salary FROM Employees WHERE EmployeeID = '72');

||

Which of the following is not a valid comparison operator? a. = b. <= c. || d. <>

DECIMAL

Which of the following is not an Access SQL datatype? a. TEXT b. SHORT c. LONG d. DECIMAL

SUBTOTAL

Which of the following is not an aggregate function used to calculate information on groups of records? a. SUM b. MIN c. COUNT d. SUBTOTAL

*

Which symbol is a wild card representing any number of characters? a. * b. <> c. & d. #