This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: . . Fall 2007 CSC/CPE 365: Database Systems Alexander Dekhtyar . . Lab 6: Complex Queries Due date: Tuesday, November 6, midnight ! . Lab Assignment Assignment Preparation This is an individual lab. Each student has to complete all work required in the lab, and submit all required materials exactly as specified in this assignment. The assignment will involve writing SQL queries for different information needs (quesions asked in English) for each of the five course datasets. The Task You are to write and debug (to ensure correct output) the SQL queries that return information as requested in each of the information needs outlined below. The information needs may be quite complex and to address them, the use of aggregation, grouping, nested queries or their combinations may be required. For this assignment, you will prepare one SQL script for each database. In addition to SQL statements you may need to include some SQL*plus formatting instructions to ensure that your output looks good. In particular, every row of every resulting table must be printed in a single line. If that means changing the size of the line - do it. Similarly, there should not be awkward pagination of the answers - change page size as needed. STUDENT dataset For STUDENT dataset, write an SQL script containing SQL statements answering the following information requests. 1 1. Find the total number of students in the third grade. Report just the number. 2. Find the total number of classrooms in which the fourth grade students are taught. Report just the number. 3. For each classroom, report the number of students studying in it. 4. For each grade, report the number of students studying in it. 5. Find the grade with the largest number of students. Report the grade and the number of students in it. 6. Find the classroom with the largest number of students. Report the classroom and the teacher (first name, last name)....
View Full Document
- Spring '08