Database Reference
In-Depth Information
he condition
S.StudentID
<>
T.StudentID
is used to prevent the same stu-
dent ID from being counted twice. Highlight the code and click
Run
. he result of
the query is shown in Figure 6.58.
10. Example 9: his example shows another way to display the last names of the faculty
members who advise more than one student. Enter the following query:
SELECT F.LastName, COUNT(S.StudentID) As [Number of Advisees]
FROM FACULTY F JOIN STUDENT S ON F.FacultyID
=
S.FacultyID
GROUP BY F.LastName
HAVING COUNT(S.StudentID)
>
1
Highlight the code and click
Run
. he result of the query is shown in Figure 6.59.
When GROUP BY is used, HAVING replaces the WHERE clause.
Figure 6.59
Use HAVING clause with GROUP BY.
6.6 Summary
his chapter covers topics of retrieving information from the data stored in a database. At the
beginning, we examined the data stored in the tables of the database Class_Registration. hen,
through examples, you learned how to query information from database tables by using a combi-
nation of the three basic statements: SELECT, FROM, and WHERE. You learned how to query
database tables with the logical operators AND, OR, NOT, IN, EXISTS, NOT EXISTS, IS
NULL, DISTINCT, BETWEEN, and LIKE. Examples were also used to show how to use the
comparison operators and arithmetic operators to query information from a database. To better
display retrieved data, the DISTINCT, ORDER BY, HAVING, GROUP BY, and TOP operators
were used to organize data outputs.
