Skip to main content

Activity 2.5

1

Producing Reports

Using the solution to Activity 1.5, found at the end of Section 1, develop a command file to create the student table and insert records into that table. Use SQL to display the entered rows.

Report 1: Produce a report of courses starting with the characters 'C002' and their corresponding units. Include the course numbers and names and the unit numbers and names. Sort the report by course number.

Report 2: Extend the above report to include the department name that is responsible for the course.

Report 1

SELECT course.course_number, course_name, unit_number, unit_name

FROM course, unit

WHERE course.course_number = unit.course_number

AND course.course_number LIKE 'C002%'

ORDER BY course..course_number;

Output from the SELECT course.course_number, course_name, unit_number, unit_name[D]

Report 2:

SELECT course.course_number, course_name, dept_name, unit_number, unit_name

FROM course, unit, department

WHERE course.course_number = unit.course_number

AND course.dept_number = department.dept_number

AND course.course_number LIKE 'C002%'

ORDER BY course.course_number;

Output from the SELECT course.course_number, course_name, dept_name, unit_number, unit_name FROM course, unit, department WHERE course.course_number  = unit.course_number AND course.dept_number = department.dept_number AND course.course_number LIKE 'C002%' ORDER BY course.course_number; command

Check your answer

Next: A Complex Example