SPECIAL IMPLEMENTATION CONSIDERATIONS - Database Design and Development: An Essential Guide for IT Professionals - page 397

7. Print the total number of faculty members in the English department.
SELECT “The total number of faculty members in”, Department, “is”
COUNT (FACULTY)
FROM FACULTY
WHERE Department = 'English'
8. Find the number of credits for the course with course number 10971.
SELECT Credits
FROM COURSE
WHERE CourseNo = '10971'
9. List students by social security number in a given class with total score
greater than given number.
SELECT SocSecNo, ClassNo, SUM (Score)
FROM GRADE
WHERE ClassNo = '11223344'
GROUP BY SocSecNo, ClassNo
ORDER BY SocSecNo, ClassNo
HAVING SUM (Score) > 60
10. Find the students who have not yet declared a major.
SELECT SocSecNo, StudntName
FROM STUDENT
WHERE Major IS NULL
11. Find the names and phone numbers of faculty members with specialization
in data systems.
SELECT FacltyName, Phone
FROM FACULTY
WHERE Speczn LIKE 'Data%'
Advanced These examples of advanced queries are based on the relational data
model for a medical center as shown in Figure 13-11. In these queries, observe how
subqueries are used to obtain the desired results.
1. List the services billed to patient whose number is 224466.
SELECT SERVICE.ServiceCode, ServiceDesc
FROM SERVICE, BILLING
WHERE SERVICE.ServiceCode = BILLING.ServiceCode and
PatientNo = 224466
2. List the services billed to patient whose number is 224466. (Alternative
query.)
SELECT SERVICE.ServiceCode, ServiceDesc
FROM SERVICE
WHERE ServiceCode =
(SELECT ServiceCode FROM BILLING
WHERE PatientNo = 224466)