Database Reference
In-Depth Information
To retrieve information from multiple tables, subqueries and the JOIN clause were used.
Several examples were given to demonstrate how to use subqueries to obtain data selected in an
intersection, diference, union, and product. A subquery was used in an INSERT statement to
copy data from a table to a new table with the same structure. You also learned how to join tables
so that you can retrieve information from multiple tables. A join can be classiied as an inner join,
left outer join, right outer join, full outer join, or self-join. A number of examples were given to
show how to use various joins. A couple of self-join examples were used to help you better under-
stand the concept and application of the self-join.
You have also learned how to use built-in functions to perform calculations. We have discussed
commonly used aggregate functions such as COUNT, SUM, MAX, MIN, and AVG. Scalar func-
tions including GETDATE, DATEDIFF, ROUND, and RANK were also introduced. As a query
gets more and more complicated, it is important to write more eicient code and execute the code
in a more eicient way. In the next chapter, you will write SQL code as programming units such
as stored procedures, triggers, and views. You will learn how to reuse previously created stored
procedures and functions.
Review Questions
To answer the following questions, you need to use the data from Figures 6.1 through 6.13. For
each question below, show the result by executing the query in the New Query editor.
1. Execute an SQL statement to display all the columns in the table CLASSROOM.
2. Execute an SQL statement to display the columns FirstName and LastName in the table
STUDENT where the student has the irst name Bruce and last name Cox.
3. Execute an SQL statement to display all the students who have the grades between C and A.
4. Execute an SQL statement to display the last names of the faculty members who teach either
VB or Database.
5. Execute an SQL statement to display the irst names and last names of the students with the
last names starting with A, B, or C.
6. Execute an SQL statement to display the student id(s) of the student(s) who have not enrolled
in either the ISC3321 class or the ISC4301 class.
7. Execute an SQL statement to display the last names of the students whose ids are between
11 and 15.
8. Execute an SQL statement to display the classroom capacity that is less than or equal to half
that of the classroom 103.
9. Execute an SQL statement to display the names of the students who have enrolled in the VB
classes in a descending order.
10. Execute an SQL statement to display the student's last name and his/her faculty advisor's
name for the student whose id is 10 with the UNION operator.
11. Execute an SQL statement to display the faculty members' name(s) who advise the student(s)
who has/have the last name Cox.
12. Execute an SQL statement to display the irst name(s) and last name(s) of the student(s)
whose faculty advisor is Smith and who is/are enrolled in the VB class.
13. Execute a nested query statement to display the student name(s) and course name(s) from
which the student(s) received an A grade.
