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

Queries

Let us now examine a few examples of queries. First, let consider simple queries,
mostly deriving results from single database tables. These will increase your grasp
of the basic query statements in SQL. After that, we will go over a few examples of
more advanced queries where the results need to be obtained from more than one
table. There you will become familiar with queries within queries. The technique of
using subqueries enables a complex query to be broken down into simple queries
to produce the desired result.
Do not assume that the examples here form a complete set of all possible types
of queries. The intention here is to provide you with a good introduction to SQL
queries. Any standard SQL reference guide will provide you with a more compre-
hensive coverage of all types of queries.

Simple The queries in these examples are based on the data model for a univer-
sity we considered in earlier chapters. As you are already familiar with this data
model, let us use it to illustrate the following query types. Go back and review the
following figures from earlier chapters:

Figure 7-22 ERD: university teaching aspects

Figure 9-22 University ERD: transformation into relational data model

1. Find the name of the course with course number 10971.
SELECT CourseDesc
FROM COURSE
WHERE CourseNo = '10971'
2. Find the names of students with computer science major.
SELECT StudntName
FROM STUDENT
WHERE Major = 'Computer Science'
3. List all data for faculty member John Saunders.
SELECT *
FROM FACULTY
WHERE FacltyName = 'John Saunders'
4. Find all the majors for which students have enrolled.
SELECT DISTINCT Major
FROM STUDENT
5. Find the maximum and minimum price of prescribed textbooks.
SELECT MAX (Price), MIN (Price)
FROM TEXTBOOK
6. Add 5 points to minimum score for mid-term examination.
SELECT MinScore, (MinScore+5)
FROM EXAMTYPE
WHERE TypeId = 'MIDTERM'