Skip to main content

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.

Next: UPDATE Statement