QUESTIONS

1. Explain why it would be preferable to use a DATE data type to store date data
instead of a character data type.

Because DBMS will not recognize the numeric value of the digits if they are entered in character
data type.

2. Explain why the following command would create an error and what changes could
be made to fix the error:
SELECT V_CODE, SUM(P_QOH) FROM PRODUCT;

The command would generate an error because an aggregate function is applied to the P_QOH
attribute but V_CODE is neither in an aggregate function or in a GROUP BY. This can be fixed
by either.
1) placing V_CODE in an appropriate aggregate function based on the data that is being
requested by the user.
2) adding a GROUP BY clause to group by values of V_CODE.
3) removing the V_CODE attribute from the SELECT clause.
4) removing the Sum aggregate function from P_QOH.

3. What is a cross join? Give an example of its syntax.

A CROSS JOIN is identical to the PRODUCT relational operator. The CROSS JOIN is also
known as the Cartesian product of two tables.
Syntax examples are: SELECT * FROM CUSTOMER CROSS JOIN AGENT;

4. What three join types are included in the outer join classification?

An OUTER JOIN is a type of JOIN operation that yields all rows with matching values in the
join columns as well as all unmatched rows. The SQL standard prescribes three different types of
join operations: LEFT [OUTER] JOIN RIGHT [OUTER] JOIN FULL [OUTER] JOIN.
1. The LEFT [OUTER] JOIN will yield all rows with matching values in the join columns,
plus all of the unmatched rows from the left table.
2. The RIGHT [OUTER] JOIN will yield all rows with matching values in the join
columns, plus all of the unmatched rows from the right table.
3. The FULL [OUTER] JOIN will yield all rows with matching values in the join columns,
plus all the unmatched rows from both tables named in the FROM clause.
5. Using tables named T1 and T2, write a query example for each of the three join
types you described in Question 4. Assume that T1 and T2 share a common column
named C1.

LEFT OUTER JOIN example:

SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1;
RIGHT OUTER JOIN example:
SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.C1 = T2.C1;
FULL OUTER JOIN example:
SELECT * FROM T1 FULL OUTER JOIN T2 ON T1.C1 = T2.C1;

6. What is a recursive join?

A recursive join is a join in which a table is joined to itself.

7. Rewrite the following WHERE clause without the use of the IN special operator:
WHERE V_STATE IN (‘TN’, ‘FL’, ‘GA’)

WHERE V_STATE = 'TN' OR V_STATE = 'FL' OR V_STATE = 'GA'

8. Explain the difference between an ORDER BY clause and a GROUP BY clause.

An ORDER BY clause has no impact on which rows are returned by the query, it simply sorts
those rows into the specified order. A GROUP BY clause does impact the rows that are returned
by the query. A GROUP BY clause gathers rows into collections that can be acted on by
aggregate functions.

9. Explain why the following two commands produce different results: SELECT
DISTINCT COUNT (V_CODE) FROM PRODUCT; SELECT COUNT (DISTINCT
V_CODE) FROM PRODUCT;

The difference is in the order of operations. The first command executes the Count function to
count the number of values in V_CODE (say the count returns "14" for example) including
duplicate values, and then the Distinct keyword only allows one count of that value to be
displayed (only one row with the value "14" appears as the result). The second command applies
the Distinct keyword to the V_CODEs before the count is taken so only unique values are
counted.
10. What is the difference between the COUNT aggregate function and the SUM
aggregate function?

COUNT returns the number of values without regard to what the values are. SUM adds the
values together and can only be applied to numeric values.

11. In a SELECT query, what is the difference between a WHERE clause and a HAVING
clause?

Both a WHERE clause and a HAVING clause can be used to eliminate rows from the results of a
query. The differences are meanwhile the WHERE clause eliminates rows before any grouping
for aggregate functions occurs while the HAVING clause eliminates groups after the grouping
has been done, and the WHERE clause cannot contain an aggregate function but the HAVING
clause can.

12. What is a subquery, and what are its basic characteristics?

A subquery is a query that is located inside another query. The first SQL statement is known as
the outer query, the second is known as the inner query or subquery. The inner query or subquery
is normally executed first. The output of the inner query is used as the input for the outer query.
A subquery is normally expressed inside parenthesis and can return zero, one, or more rows and
each row can have one or more columns.
A subquery can appear in many places in a SQL statement:
1. As part of a FROM clause
2. To the right of a WHERE conditional expression
3. To the right of the IN clause
4. In a EXISTS operator
5. To the right of a HAVING clause conditional operator
6. In the attribute list of a SELECT clause

13. What are the three types of results that a subquery can return?

A subquery that can return can be a single value (one row, one column), a list of values (many
rows, one column), or a virtual table (many rows, many columns).

14. What is a correlated subquery? Give an example.

A correlated subquery is a subquery that executes once for each row in the outer query. This
process is similar to the typical nested loop in a programming language. Contrast this type of
subquery to the typical subquery that will execute the innermost subquery first, and then
the next outer query … until the last outer query is executed. That is, the typical subquery will
execute in serial order, one after another, starting with the innermost subquery. In contrast, a
correlated subquery will run the outer query first, and then it will run the inner subquery
once for each row returned in the outer subquery. An example can be:
SELECT INV_NUMBER, P_CODE, LINE_UNITS
FROM LINE LS
WHERE LS.LINE_UNITS > (SELECT AVG(LINE_UNITS) FROM LINE LA
WHERE LA.P_CODE = LS.P_CODE)

15. Explain the difference between a regular subquery and a correlated subquery.

A regular, or uncorrelated subquery, executes before the outer query. It executes only once and
the result is held for use by the outer query. A correlated subquery relies in part on the outer
query, usually through a WHERE criteria in the subquery that references an attribute in the
outer query. Therefore, a correlated subquery will execute once for each row evaluated by the
outer query; and the correlated subquery can potentially produce a different result for each row in
the outer query.

16. What does it mean to say that SQL operators are set-oriented?

The description of SQL operators as set-oriented means that the commands work over entire
tables at a time, not row-by-row.

17. The relational set operators UNION, INTERSECT, and EXCEPT (MINUS) work
properly only when the relations are union-compatible. What does union-compatible mean,
and how would you check for this condition?

Union compatibility means that the relations yield attributes with identical names and compatible
data types. That is, the relation A(c1,c2,c3) and the relation B(c1,c2,c3) have union compatibility
if both relations have the same number of attributes, and corresponding attributes in the
relations have data types. Compatible data types do not require that the attributes be exactly
identical -- only that they are comparable. However, the DBMS does not understand the
meaning of the business domain so it must work with a more concrete understanding of
the data in the corresponding columns. Thus, it only considers the data types.

18. What is the difference between UNION and UNION ALL? Write the syntax for
each.
UNION yields unique rows. In other words, UNION eliminates duplicate rows. On the other
hand, a UNION ALL operator will yield all rows of both relations, including duplicates. Notice
that for two rows to be duplicated, they must have the same values in all columns.
An example of the difference between UNION and UNION ALL:
A (ID, Name) with rows (1, Lake, 2, River, and 3, Ocean) and B (ID, Name) with rows (1, River,
2, Lake, and 3, Ocean).
Given this description:
SELECT * FROM A UNION SELECT * FROM B
will yield:
ID Name
1 Lake
2 River
3 Ocean
1 River
2 Lake
while
SELECT * FROM A UNION ALL SELECT * FROM B
will yield:
ID Name
1 Lake
2 River
3 Ocean
1 River
2 Lake
3 Ocean

19. Suppose you have two tables: EMPLOYEE and EMPLOYEE_1. The EMPLOYEE
table contains the records for three employees: Alice Cordoza, John Cretchakov,
and Anne McDonald. The EMPLOYEE_1 table contains the records for employees
John Cretchakov and Mary Chen. Given that information, list the query output for
the UNION query.

The query output will be:

Alice Cordoza
John Cretchakov
Anne McDonald
Mary Chen
20. Given the employee information in Question 19, list the query output for the
UNION ALL query.

The query output will be:

Alice Cordoza
John Cretchakov
Anne McDonald
John Cretchakov
Mary Chen

21. Given the employee information in Question 19, list the query output for the
INTERSECT query.

The query output will be:

John Cretchakov

22. Given the employee information in Question 19, list the query output for the
EXCEPT (MINUS) query of EMPLOYEE to EMPLOYEE_1.

This question can yield two different answers. If you use

SELECT * FROM EMPLOYEE MINUS SELECT * FROM EMPLOYEE_1
the answer is:
Alice Cordoza
Ann McDonald
If you use
SELECT * FROM EMPLOYEE_1 MINUS SELECT * FROM EMPLOYEE
the answer is:
Mary Chen

23. Suppose a PRODUCT table contains two attributes, PROD_CODE and VEND_
CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL,
123, respectively. The VENDOR table contains a single attribute, VEND_CODE,
with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in
the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.)
Given that information, what would be the query output for:
a. A UNION query based on the two tables?
125,124,123,126
b. A UNION ALL query based on the two tables?ç
125,124,124,123,123,124,125,126
c. An INTERSECT query based on the two tables?
123,124,125
d. An EXCEPT (MINUS) query based on the two tables?
If you use PRODUCT MINUS VENDOR, the output will be NULL
If you use VENDOR MINUS PRODUCT, the output will be 126

24. Why does the order of the operands (tables) matter in an EXCEPT (MINUS) query
but not in a UNION query?

MINUS queries are analogous to algebraic subtraction – it results in the value that existed in the
first operand that is not in the second operand. UNION queries are analogous to algebraic
addition – it results in a combination of the two operands. (These analogies are not perfect,
obviously, but they are helpful when learning the basics.) Addition and UNION have the
commutative property (a + b = b + a), while subtraction and MINUS do not (a – b ≠ b – a).

25. What MS Access and SQL Server function should you use to calculate the number
of days between your birth date and the current date?

SELECT DATE()-#25-JAN-1999#

26. What Oracle function should you use to calculate the number of days between your
birth date and the current date?

SELECT SYSDATE – TO_DATE‟25-JAN-1999,’DD-MON-YYYY)

FROM DUAL;

27. What string function should you use to list the first three characters of a company’s
EMP_LNAME values? Give an example using a table named EMPLOYEE. Provide
examples for Oracle and SQL Server.

In Oracle, you use the SUBSTR function as illustrated next:

SELECT SUBSTR(EMP_LNAME, 1, 3) FROM EMPLOYEE;
In SQL Server, you use the SUBSTRING function as shown:
SELECT SUBSTRING(EMP_LNAME, 1, 3) FROM EMPLOYEE;

28. What two things must a SQL programmer understand before beginning to craft a
SELECT query?

Crafting SELECT Queries

The SQL language is both simple and complex. Each clause and function on its own is simple
and performs a well-defined task. However, because of the flexibility of the SQL language,
combining the appropriate clauses and functions to satisfy an information request can become
rather complex. When Attempting to craft a query, the following are useful suggestions to keep
in mind.
Know Your Data
Real-world databases are messy. Table and attribute names are often cryptic, confusing, and
non-standardized. Tables may not have appropriate constraints enforced, and, in some cases, may
not even have a defined primary key! Finding tables of related data that do not have a foreign
key to implement that relationship is not uncommon. Most database systems remain in service in
an organization for decades. As the business changes, grows, contracts, merges, and splits over
many years, the internal systems must be adapted and changed. These changes often involve
compromises that become institutionalized within the system.
Know the Problem
It is important to understand the question you are attempting to answer. Information reporting
requests will come from a range of sources. Some requests are one-time events, and some will
become a part of on-going operations within an application or data analysis process. Information
requests are often ambiguous and subject to multiple interpretations, even if the person making
the request does not realize it.