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 4: Simple Queries Due date: Tuesday, October 16, 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 can be addressed with a simple SELECT statement (i.e., a SELECT statement without grouping, aggregation and nested subqueries) and/or with UNION, MINUS and INTERSECT state- ments. 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 all students who attend 3d grade. For each student list first and last name and the classroom number. 2. For each student in 4th grade, report their teacher. Output first and last name of the student and first and last name of the teacher. 3. For each students whose first name is ’JODY’ report their first and last name, grade, classroom and the last name of the teacher. 4. Find all students taught by ’ALFREDA SUGAI’ . Output first and last names of students. 5. Find all students who study in classrooms with numbers between 103 and 108 (inclusive). Output the first and last name of the student, their grade and classroom. Output the list ordered by classroom and within each classroom – by student’s last name.within each classroom – by student’s last name....
View Full Document
This note was uploaded on 05/19/2008 for the course CSC 365 taught by Professor Dekhtyar during the Spring '08 term at Cal Poly.
- Spring '08