[Main Page]
[Literature]
[Software]
[People]
[Conferences]
[Courses]
[Web-DB]
UNIVERSITY OF PITTSBURGH
School of Information Sciences
INFSCI 2710 - Database Management
(Spring 2000, CRN 20727)
Homework Assignment #5: Advanced Algebra, SQL*Plus
Reading Assignment
Please read the following chapter from the book by Elmasri and Navathe
(3rd Edition):
- Chapter 7, "The Relational Model, relational Constraints,
and the Relational Algebra" (43 pages).
You do not have to read 7.4.7 "Division",
7.5.1 "Aggregate Functions and Grouping",
7.5.2 "Recursive Closure Operations".
- Section 8.2,
"Basic Queries in SQL" (10 pages).
Exercise 5 (12 Points)
Consider Oracle's example database
with information about employees and departments
(the tables are printed on slides 2-5 and 2-9):
- DEPT(DEPTNO, DNAME, LOC)
- EMP(EMPNO, ENAME, JOB, MGRo->EMP,
HIREDATE, SAL, COMMo, DEPTNO->DEPT)
Please formulate the following queries in relational algebra:
- a)
- Which department has no employees?
Print number and name of the department.
- b)
- Who are the managers of the research and the sales department?
Print their employee number and name.
Write the following queries in SQL.
You must run them in SQL*Plus
and submit a printout showing the queries and their output.
The easiest way to get everything in a file
is to the command "SPOOL file" in SQL*Plus
before you start with the queries,
and "SPOOL OFF" when you are done.
Then "file.lst" will contain the transcript of your session.
- c)
- Print all employee names with four letters.
- d)
- Print name, number, salary, and department of all employees
who work in department 10 or 30
and earn less than $1500.
Make sure that both conditions are really satisfied.
Finally,
do the following exercise with SQL*Plus:
- e)
- Enter three incorrect SQL queries,
for which SQL*Plus prints three different error messages.
Please explain what the error is
(in 1-2 sentences, maybe handwritten on the printout).
This exercise is worth 4 points.
Due Date:
Please submit your solution on next Wednesday (February 16)
before the lecture.
Stefan Brass
(sbrass@sis.pitt.edu),
February 9, 2000
Original URL:
http://www2.sis.pitt.edu/~sbrass/db00_2/h5_sql.html
[HTML 3.2 Checked]