Databases Reference
In-Depth Information
8. In a general way, describe how to write an SQL
SELECT command to accomplish a relational Select
operation.
9. In a general way, describe how to write an SQL
SELECT
16. Name the five SQL built-in functions and describe
the purpose of each.
17. Explain
the
difference
between
the
SUM
and
COUNT built-in functions.
18. Describe the purpose of the GROUP BY clause.
Why must the attribute in the GROUP BY clause
also appear in the SELECT clause?
19. Describe the purpose of the HAVING clause. How
do you decide whether to place a row-limiting
predicate in the WHERE clause or in the HAVING
clause?
20. How do you construct a Join operation in an SQL
SELECT statement?
21. What is a subquery in an SQL SELECT statement?
22. Describe the circumstances in which a subquery
must
be used.
23. What is a relational query optimizer? Why are they
important?
24. How do relational query optimizers work?
25. What information does a relational query optimizer
use in making its decisions?
26. What are some of the ways that relational query
optimizers can handle joins?
command
to
accomplish
a
relational
Project operation.
10. In a general way, describe how to write an SQL
SELECT command to accomplish a combination of
a relational Select operation and a relational Project
operation.
11. What is the purpose of the WHERE clause in SQL
SELECT commands?
12. List and describe some of the common operators
that can be used in the WHERE clause.
13. Describe the purpose of each of the following
operators in the WHERE clause:
a. AND
b. OR
c. BETWEEN
d. IN
e. LIKE
14. What is the purpose of the DISTINCT operator?
15. What is the purpose of the ORDER BY clause?
EXERCISES
1. Consider the following relational database that
Best Airlines uses to keep track of its mechanics,
their skills, and their airport locations. Mechanic
number (MECHNUM), airport name (AIRNAME),
and skill number are all unique fields. SIZE is
an airport's size in acres. SKILLCAT is a skill
category, such as an engine skill, wing skill, tire
skill, etc. YEARQUAL is the year that a mechanic
first qualified in a particular skill; PROFRATE is the
mechanic's proficiency rating in a particular skill.
SKILL Table
SKILLNUM
SKILLNAME
SKILLCAT
QUALIFICATION Table
MECHNUM
SKILLNUM
YEARQUAL
PROFRATE
−−−−−−−
−−−−−−−
Write SQL SELECT commands to answer the
following queries.
a. List the names and ages of all the mechanics.
b. List the airports in California that are at least
20 acres in size and have been open since 1935.
Order the results from smallest to largest airport.
c. List the airports in California that are at least 20
acres in size or have been open since 1935.
d. Find the average size of the airports in California
that have been open since 1935.
MECHANIC Table
MECHNUM
MECHNAME AGE
SALARY
AIRNAME
−−−−−−
AIRPORT Table
AIRNAME
CITY
STATE
SIZE
YEAROPENED
Search WWH ::
Custom Search