415

In an SQL query, which of the following symbols is used by ANSI SQL to represent all the columns in a single table?

* (asterisk)

In an SQL query, which built-in function is used to compute the average value of numeric columns?

AVG

In an SQL query, which SQL keyword must be used to remove duplicate rows from the result table?

DISTINCT

When making an SQL query, we are using SQL as a(n) ________.

DML

In an SQL query, which SQL keyword is used to determine if a column value is equal to any one of a set of values?

IN

Suppose your company stores EMPLOYEE and CUSTOMER data in separate tables. If you want to find all employees who are also customers, which SQL keyword would you most likely use?

INTERSECT

In an SQL query, which built-in function is used to obtain the largest value of numeric columns?

MAX

In an SQL query, which built-in function is used to obtain the smallest value of numeric columns?

MIN

Explain why it is important to learn SQL.

Most modern DBMS products support SQL as a standardized data language. These products usually provide graphical tools to perform the tasks associated with SQL, but there are some tasks that cannot be performed using these graphical tools. SQL is text-oriented, and SQL code must be written in order to embed SQL commands within program applications.

In an SQL query, which SQL keyword is used to sort the result table by the values in one or more columns?

ORDER BY

Regarding the interchangeability of subqueries and joins, ________.

a join can sometimes be used as an alternative to a subquery, and a subquery can sometimes be used as an alternative to a join

The SQL built-in function ADDUP totals values in numeric columns.

False

The SQL built-in function MOST obtains the largest value in a numeric column.

False

The SQL keyword WHERE is used to specify the table(s) that contain(s) the data to be retrieved.

False

The SQL wildcard character "#" indicates a single, unspecified character in a specific location in an SQL query.

False

The SQL wildcard character "%" represents a series of one or more unspecified characters.

False

The WHERE clause contains the condition that specifies which columns are to be selected.

False

The built-in function SUM can be used with any column.

False

The columns to be obtained by an SQL command are listed after the FROM keyword.

False

To exclude one or more values using a condition, the SQL OUT keyword must be used.

False

To have SQL automatically eliminate duplicate rows from a result, use the keyword DISTINCT with the FROM keyword.

False

To obtain all columns, use an asterisk (*) wildcard character instead of listing all the column names.

False

Two or more tables are joined by giving the table names in the WHERE clause and specifying the equality of the respective column names as a condition in the GROUP BY clause.

False

In an SQL query, which SQL keyword is used with built-in functions to group together rows that have the same value in a specified column or columns?

GROUP BY

The SQL keyword SELECT is used to specify the columns to be listed in the query results.

True

The clause SELECT COUNT (*) results in a table with a single row and a single column.

True

The condition in WHERE clauses can refer to a set of values by using the IN operator.

True

The names of tables to be joined in an SQL query are listed in the FROM clause.

True

The result of an SQL SELECT operation can contain duplicate rows.

True

The rows of the result table can be sorted by the values in one or more columns.

True

To refer to a set of values in a condition, the values are placed inside parentheses ( ) and separated by commas.

True

To refer to a set of values needed for a condition, use the SQL IN operator.

True

To remove duplicate rows from the result of a query, specify the SQL DISTINCT keyword.

True

To sort the rows of the result table, the ORDER BY clause is specified.

True

When people use the term join they normally mean an equijoin.

True

When two conditions must both be true for the rows to be selected, the conditions are separated by the SQL AND keyword.

True

Suppose tables EMPLOYEE and CUSTOMER both store address information, and you want to send a letter to all employees and customers of your company to make a major announcement. Which SQL keyword would you most likely use here

UNION

An ad-hoc query is ________.

a question that can be answered from the database using SQL

When one SQL query is embedded in another SQL query, the top level SQL query can still contain an SQL ________ clause.

GROUP BY

In an SQL query, which SQL keyword is used with GROUP BY to select groups meeting specified criteria?

HAVING

In an SQL query, which of the following symbols is used by Microsoft Access to represent a single unspecified character?

? (question mark)

In an SQL query, which SQL keyword is used to link two conditions that both must be true for the rows to be selected?

AND

In an SQL query, the built-in functions SUM and AVG work with columns containing data of which of the following data types?

Both Integer and Numeric are correct

In an SQL query of two tables, which SQL keyword indicates that we want data from all the rows of one table to be included in the result, even if the row does not correspond to any data in the other table?

Both LEFT JOIN and RIGHT JOIN

In an SQL query, which built-in function is used to compute the number of rows in a table?

COUNT

Given a table with the structure: EMPLOYEE ( EmpNo, Name, Salary, HireDate), which of the following would find all employees whose name begins with the letter "S" using standard SQL?

ELECT * FROM EMPLOYEE WHERE Name LIKE 'S%';

Suppose your company stores EMPLOYEE and CUSTOMER data in separate tables. If you want to find all customers who are not also employees, which SQL keyword would you most likely use?

EXCEPT

In an SQL query, which SQL keyword is used to specify the names of tables to be joined?

FROM

In an SQL query, which SQL keyword is used to specify the table(s) to be used?

FROM

A WHERE clause can contain only one condition.

False

An ad-hoc SQL query is typically written within an application program.

False

Arithmetic in SQL statements is limited to the operations provided by the built-in functions.

False

In addition to being a data sublanguage, SQL is also a programming language, like Java or C#.

False

Only two tables can be queried by using a subquery.

False

SQL can only query a single table.

False

SQL is only a data manipulation language (DML).

False

SQL stands for Standard Query Language.

False

SQL statements end with a colon.

False

SQL, although very popular, has never become a national standard.

False

Sorting is specified by the use of the SORT BY phrase.

False

The Microsoft Access wildcard character "_" (underscore) indicates a single, unspecified character in a specific location in a Microsoft Access SQL query.

False

In an SQL query, which SQL keyword actually begins the query?

SELECT

In an SQL query, which SQL keyword is used to implement a subquery?

SELECT

Given a table with the structure: EMPLOYEE ( EmpNo, Name, Salary, HireDate), which of the following would find all employees whose name begins with the letter "S" using Microsoft Access?

SELECT * FROM EMPLOYEE WHERE Name LIKE 'S*';

Based on the tables below, which of the following ANSI SQL commands would return the average customer balance grouped by SalesRepNo? GENERAL SALES DATABASE: SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/2005 734 Smith 02/03/2007 345 Chen 01/25/2004 434 Johnson 11/23/2004 CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345

SELECT AVG (Balance) FROM CUSTOMER GROUP BY SalesRepNo;

Given a table with the structure: EMPLOYEE ( EmpNo, Name, Salary, HireDate), which of the following is not a valid ANSI SQL command?

SELECT HireDate, COUNT(*) FROM EMPLOYEE WHERE Salary < 30000;

Given the table CUSTOMER(CustID, Name, PhoneNumber, AccountBalance), write the standard SQL query to retrieve the Name and PhoneNumber of customers whose name begins with 'S'

SELECT Name, PhoneNumber FROM CUSTOMER WHERE Name LIKE 'S%';

Based on the tables below, which of the following commands in ANSI SQL would return only the name of the sales representative and the name of the customer for each customer that has a balance greater than 400? GENERAL SALES DATABASE: SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/2005 734 Smith 02/03/2007 345 Chen 01/25/2004 434 Johnson 11/23/2004 CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345

SELECT RepName, CustName FROM SALESREP, CUSTOMER WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo AND Balance > 400;

In an SQL query, which of the following symbols is used by ANSI SQL to represent a single unspecified character?

_ (underscore)

What are SQL Built-in Functions?

SQL Built-in Functions are functions that manipulate the results of an SQL SELECT statement. The built-in functions for standard SQL are COUNT, SUM, AVG, MAX, and MIN. The COUNT function counts the number of rows in the result. The SUM function totals the values in a number-oriented field. The AVG function calculates the mean of the values in a number-oriented field. The MAX function determines the highest value, and the MIN function determines the lowest value, in a number-oriented field or any other field whose values can be compared using "<".

In an SQL query, which built-in function is used to total numeric columns?

SUM

What is SQL?

Structured Query Language (SQL) is used to create and use databases, tables, and relationships. SQL is divided into two categories: SQL statements for database definition and SQL statements for database processing (querying and updating). The database definition commands are referred to as a data definition language (DDL), and the database query and update commands are referred to as a data manipulation language (DML). SQL was developed by IBM, and is endorsed as a national standard by the American National Standards Institute (ANSI). Although a newer standard, SQL3, exists, the most widely implemented version of SQL is the ANSI SQL-92 standard. SQL is not a full-featured programming language, but rather it is considered to be a data sublanguage.

Briefly describe subqueries and joins. Explain when each is not an acceptable alternative for the other.

Subqueries and joins are both methods for retrieving data from multiple tables. Subqueries involve nesting one SELECT statement within another. The nested SELECT is used as part of a condition in the WHERE clause of the first SELECT statement. The nested SELECT statement can return a set of records from one table, which are then used in a logical operator within the parent SELECT query. A join combines records from each table into concatenated records containing the fields of both tables. The records are concatenated based on matching values in similar columns in the two tables. Subqueries cannot be used in situations where the results to be displayed include attributes from more than one table. Joins cannot be used as an alternative to a correlated subquery.

Distinguish between the HAVING clause and the WHERE clause.

The HAVING clause and the WHERE clause differ in that the WHERE clause is used to identify rows that satisfy a stated condition. The HAVING clause is used to identify groups, which have been created by the GROUP BY clause, that satisfy a stated condition. In cases when the WHERE clause and the HAVING clause are both allowed to appear in the same SELECT statement, the WHERE clause is implemented to execute before the HAVING clause.

Assuming the "Quantity" column of an ORDER table contains integer data, what does COUNT(Quantity) compute?

The number of non-null values in the Quantity column

A SELECT statement used in a WHERE clause is called a subquery.

True

A WHERE clause can contain another SELECT statement enclosed in parentheses.

True

A nested SELECT statement (one that appears within the WHERE clause of another SQL statement) is called a subquery and must be enclosed in parentheses.

True

An alternative to combining tables by a subquery is to use a join.

True

An asterisk (*) following the SELECT verb means that all columns are to be displayed.

True

Business Intelligence (BI) systems typically store their data in data warehouses.

True

Columns can be sorted in descending sequence by using the SQL DESC keyword.

True

SQL commands can be embedded in application programs.

True

SQL includes a data definition language, a data manipulation language, and SQL/Persistent stored modules.

True

SQL is not a complete programming language. Rather it is a data sublanguage.

True

SQL was developed by IBM in the late 1970s.

True

The American National Standards Institute (ANSI) maintains the standards for SQL.

True

The SQL WHERE clause contains the condition that specifies which rows are to be selected.

True

The SQL built-in function AVG computes the average of values in numeric columns.

True

The SQL built-in function COUNT computes the number of rows in a query.

True

The SQL built-in function MIN obtains the smallest value in a numeric column.

True

The SQL keyword FROM is used to specify the table to be used.

True

The SQL keyword GROUP BY instructs the DBMS to group together those rows that have the same value in a column.

True

The SQL keyword LIKE is used in SQL expressions to select partial string values.

True

In an SQL query, which SQL keyword is used to specify the column names to be used in a join?

WHERE

In an SQL query, which SQL keyword is used to state the condition that specifies which rows are to be selected?

WHERE

When one SQL query is embedded in another SQL query to simulate a join, the second SQL query is embedded in the ________ of the first query.

WHERE

A database extracted from the operational database for BI purposes typically ________.

contains just part of the operational database

SQL is a ________.

data sublanguage

When one SQL query is embedded in another SQL query, this is referred to as a

subquery