One possible query is the following:
SELECT *
FROM Student
WHERE Sname LIKE '%Jones'
AND Major NOT IN ( 'Math', 'Computer Science' )
AND ( Dorm = 'Williams' OR Dorm = 'Schoelkopf' );
The results of a query can be sorted, too. All one need do is add the ORDER BY clause. For instance:
SELECT Sname
FROM Student
WHERE Major = 'Computer Science'
ORDER BY Sname;
150 DATABASE [CHAP. 8
This will report the CS majors in alphabetical order by name. To sort in reverse alphabetical order, add the
word DESC (descending) at the end of the query.
What would this query report?
SELECT Major FROM Student;
It would return one line for each student, and each line would contain a major field of study. If 300 students
major in computer science, there will be 300 lines containing the words ???Computer Science???. Such a report is
probably not what one had in mind. Probably one is interested in a list of the different major fields of study of
the students. One can get such a report by adding the word DISTINCT to the SELECT query:
SELECT DISTINCT Major FROM Student;
What if the information in which one is interested is spread among several tables? Such a query requires
a JOIN of the tables. There is more than one way to specify a JOIN, and there are different types of JOINs for
different situations.
Pages:
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419