A Complex Example
Using the knowledge that you have gained so far you can create some very clever and complex selections from the database. For example (a convoluted example):
Create a report of all female students studying for an HNC computing. Show the department name in which they are enrolled. Also show all the units that they can study in that course. Limit your report to those students who were born in 1966. Furthermore, only select those students whose forenames contain the letter 'e' and whose surnames are in the first half of the alphabet (ie starts with the letters A-M). Sort the results by student_number, course_number and unit_number.
SELECT student_number, student.title, student .forename, student .surname, student .date_of_birth, department .dept_name, course.course_number, course.course_name, unit.unit_number, unit.unit_name
FROM student, department, course, unit
WHERE student.course_number = course.course_number
AND student.dept_number = department.dept_number
AND course.course_number = unit.course_number
AND student.gender = 'F'
AND student.date_of_birth >= '01-Jan-1966'
AND student.date_of_birth <= '31-Dec-1966'
AND student.upper(forename) LIKE '%E%'
AND student.surname <='M%'
ORDER BY student_number, course_number, unit_number;
Try it if you wish. If entered correctly you should find the student Miss Denise Blanc studying for an HNC in Computing.
To complete this section on DML we shall now look at the SQL UPDATE and DELETE commands.