Chapter 2 SQL (76-114)

106) Explain why it is important to learn SQL.

Answer: 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 to embed SQL commands within program applications.

105) What is SQL?

Answer: Structured Query Language (SQL) is used to create, modify 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 a data sublanguage.

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

Answer: 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.

76) 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? A) AND B) EXISTS C) OR D) IN

D) IN

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

D) MIN

94) In an SQL query, which SQL keyword is used to implement a subquery? A) GROUP BY B) HAVING C) ORDER BY D) SELECT

D) SELECT

80) 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?

D) SELECT * FROM EMPLOYEE WHERE Name LIKE 'S%';

91) Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following is not a valid ANSI SQL command? A) SELECT * FROM EMPLOYEE WHERE Name LIKE 'Ja%'; B) SELECT COUNT(*) FROM EMPLOYEE WHERE Salary < 30000; C) SELECT HireDate, COUNT(*) FROM EMPLOYEE GROUP BY HireDate; D) SELECT HireDate, COUNT(*) FROM EMPLOYEE WHERE Salary < 30000;

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

93) 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 A) SELECT * FROM SALESREP, CUSTOMER WHERE Balance > 400; B) SELECT DISTINCT RepName, CustName FROM SALESREP, CUSTOMER WHERE Balance > 400; C) SELECT * FROM SALESREP, CUSTOMER WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo AND Balance > 400; D) SELECT RepName, CustName FROM SALESREP, CUSTOMER WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo AND Balance > 400;

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

82) In an SQL query, which built-in function is used to total numeric columns? A) AVG B) COUNT C) MAX D) SUM

D) SUM

88) Assuming the "Quantity" column of an ORDER table contains integer data, what does COUNT(Quantity) compute? A) The number of rows in the ORDER table B) The number of non-zero values in the Quantity column C) The number of distinct values in the Quantity column D) The number of non-null values in the Quantity column

D) The number of non-null values in the Quantity column

99) In an SQL query, which SQL keyword is used to specify the column names to be used in a join? A) FROM B) JOIN C) SELECT D) WHERE

D) WHERE

101) 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? A) LEFT JOIN B) RIGHT JOIN C) INCLUDE D) Both A and B are correct

D) Both A and B are correct

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

D) Both A and B are correct

83) In an SQL query, which built-in function is used to compute the average value of numeric columns? A) AVG B) MEAN C) AVERAGE D) SUM

A) AVG

98) In an SQL query, which SQL keyword is used to specify the names of tables to be joined? A) FROM B) HAVING C) JOIN D) WHERE

A) FROM

89) 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? A) GROUP BY B) ORDER BY C) SELECT D) SORT BY

A) GROUP BY

103) 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? A) INTERSECT B) UNION C) EXCEPT D) UNION ALL

A) INTERSECT

77) In an SQL query, which of the following symbols is used by ANSI SQL to represent a single unspecified character? A) _ (underscore) B) ? (question mark) C) * (asterisk) D) % (percent)

A) _ (underscore)

95) When one SQL query is embedded in another SQL query, this is referred to as a ________. A) subquery B) join C) WHERE Query D) subset query

A) subquery

110) The following database will be used in this question: ** look at doc Explain the use of the GROUP BY keyword. Include an example based on the CUSTOMER table from the General Sales database.

Answer: The GROUP BY keyword is used in conjunction with the built-in functions of SQL. The GROUP BY keyword is given a column that records are to be grouped on. Records in the result table are then collected into groups based on the value of the grouping column. The built-in function is then performed on the records of each group separately. For example, SELECT AVG(Balance) FROM CUSTOMER GROUP BY SalesRepNo; is a query that returns the average balance of all customers associated with a specific sales representative.

109) The following database will be used in this question: ** look at doc Explain the use of the SQL keyword LIKE. Include an example based on the CUSTOMER table from the General Sales database; use at least one wildcard.

Answer: The LIKE keyword is used in the WHERE clause of an SQL query to select rows based on partial values. Using wildcard characters that can represent one or more unspecified characters, the LIKE operator can search for a given string of characters within a column value. The ANSI wildcard "%" represents zero or more unspecified characters, and the wildcard " " (underscore) represents any single unspecified character. For example, SELECT * FROM CUSTOMER WHERE CustName LIKE 'H%'; is a query that returns all information in the table CUSTOMER about customers whose CustName starts with the letter H.

108) The following database will be used in this question: *** couldn't put the tables in, look at the doc Explain the use of the of SQL keyword SELECT. Include an example based on the CUSTOMER table in the General Sales database.

Answer: The SQL keyword SELECT forms the basis for all SQL querying. Although SELECT technically specifies columns to be used in a query, it is always the first SQL keyword or command in an SQL phrase that includes at least the SQL keyword FROM and often the SQL keyword WHERE. The SQL keyword FROM is used to specify which tables are used in the query, while the SQL keyword WHERE is used to specify conditions or constraints imposed on the query. For example, SELECT * FROM CUSTOMER WHERE CustNo = 8590; is a query that returns all information in the table CUSTOMER about the customer whose customer number is 8590.

78) In an SQL query, which of the following symbols is used by Microsoft Access to represent a single unspecified character? A) _ (underscore) B) ? (question mark) C) * (asterisk) D) % (percent)

B) ? (question mark)

87) In an SQL query, which built-in function is used to compute the number of rows in a table? A) AVG B) COUNT C) MAX D) SUM

B) COUNT

79) In an SQL query, which SQL keyword is used to sort the result table by the values in one or more columns? A) GROUP BY B) ORDER BY C) SELECT D) SORT BY

B) ORDER BY

92) Based on the tables below, which of the following SQL commands would return the average customer balance grouped by SalesRepNo? *** couldn't add table, look at doc A) SELECT AVG (Balance) FROM CUSTOMER WHERE SalesRepNo; B) SELECT AVG (Balance) FROM CUSTOMER GROUP BY SalesRepNo; C) SELECT AVG (Balance) FROM CUSTOMER, SALESREP WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo; D) SELECT AVG (Balance) FROM CUSTOMER ORDER BY SalesRepNo;

B) SELECT AVG (Balance) FROM CUSTOMER GROUP BY SalesRepNo;

102) 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? A) INTERSECT B) UNION C) UNION ALL D) JOIN

B) UNION

100) Regarding the interchangeability of subqueries and joins, ________. A) a join can always be used as an alternative to a subquery, and a subquery can always be used as an alternative to a join B) 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 C) a join can always be used as an alternative to a subquery, and a subquery can sometimes be used as an alternative to a join D) a join can sometimes be used as an alternative to a subquery, and a subquery can always be used as an alternative to a join

B) 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

104) 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? A) INTERSECT B) UNION C) EXCEPT D) UNION ALL

C) EXCEPT

97) When one SQL query is embedded in another SQL query, the top-level SQL query can still contain an SQL ________ clause. A) FROM or SORT BY B) UNIQUE IN C) GROUP BY D) WHERE IN

C) GROUP BY

90) In an SQL query, which SQL keyword is used with GROUP BY to select groups meeting specified criteria? A) AND B) WHERE C) HAVING D) IN

C) HAVING

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

C) MAX

81) 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? A) SELECT * FROM EMPLOYEE WHERE Name IN ['S']; B) SELECT EmpNo FROM EMPLOYEE WHERE Name LIKE 'S'; C) SELECT * FROM EMPLOYEE WHERE Name LIKE 'S*'; D) SELECT * FROM EMPLOYEE WHERE Name LIKE 'S%';

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

96) 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. A) SELECT B) FROM C) WHERE D) HAVING

C) WHERE

111) Given the table CUSTOMER(CustID, Name, PhoneNumber, AccountBalance), write the standard SQL query to retrieve the Name and PhoneNumber of customers with a balance greater than 50.

SELECT Name, PhoneNumber

112) 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

113) 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 "<".

114) Distinguish between the HAVING and WHERE clause.

The HAVING and 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. AACSB: Information Technology; Analytical Thinking