CS 4660 SQL lab

Directions:
Type your answers and submit any files you create as well as a log of your session via the digital drop box.

Part 1: Getting started with SQLPlus

We will use the "cs1db" database. You will probably have to modify an Oracle file so it knows where to find cs1db.
in "c:/program files/orawin95/network/admin/tnsnames.ora"
change entry "labdb" to read "cs1db"
the Host line to be (Host = pizza.mcs.csuhayward.edu)
{sid=labdb} to read {sid=cs1db}.

Start a log of your work by clicking on the File menu, then the Spool option, and finally the "Spool File ..." option. Note that SQLPLus doesn't like spaces in the name of the spool file -- saving to the root directory of the C: drive is the easiest way to handle it. Please include your name in the name of the spool file, like "JohnSmithSQL_Lab" (the file type is "lst").

Create a schema (in this case, a part of the database) which allows you to define tables. You are limited to just one schema which goes by your login name (the default). The command is:
create schema authorization <your userid>;

Note that the SQL commands will require a semicolon, while SQLPlus commands do not. You'll notice this because if you don't enter the semicolon, you will get a prompt with the next line number. If you make a typing mistake, you can enter the SQLPlus
edit
command, which will open up an editor (probably Notepad) with the contents of the current command buffer. You can edit it and save the modifications, then exit the editor. When you get the prompt in the SQLPlus window, enter
run
to execute the statement.

Define your tables

We will use the Company example. Part of the example has been done for you in the accompanying CreateCompany.sql file. You can run this file by typing
@ c:\CreateCompany

in sqlplus (assuming that's where you downloaded the file). The default directory is c:\Program Files\ORAWIN95\PLUS33. Note that the database is not complete. Create the Dept_Locations table and insert the data from the Elmasri example (see the accompanying text file). You will probably find it easier to create a file with a ".sql" extension and load and execute commands in the file using @.

If you should want to clear the tables for some reason, see the Updates and constraints section below.

Understand and Write queries

Start with executing the first 5 queries -- write a single sentence describing what each query does. The 4th query is an example of testing if a record is an element of another table and demonstrates how the tables behave like sets. The 5th query shows how when the result of an aggregate query is a single value, you can treat it as such. Then write queries to answer the following 3 questions.
  1. select * from Dept_Locations;
  2. select DName from Department, Dept_Locations
    where Department.DNumber = Dept_Locations.DNumber and DLocation = 'Houston';
  3. create table Temp as
    select DName, Count(*) as NumLoc
    from Department, Dept_Locations
    where Department.DNumber = Dept_Locations.DNumber
    group by DName;
    select * from Temp;
  4. select FName, MInit, LName
    from Employee
    where SSN in
    (select MgrSSN from Department);
  5. select FName, MInit, LName
    from Employee
    where BDate =
    (select Min(BDate) from Employee);
  6. Find the names of the projects that John B. Smith works on.
  7. Find the names of employees who do not have dependents
  8. Find the name of the department with the most locations. You can use the Temp table just created above.

Updates and constraints

You probably wouldn't want to keep the Temp table around after finishing your session. (You should know why -- we'll talk about views later on to help.) You can delete it using SQL's drop table command.
drop table Temp;

[Note: If you want to drop a table and find you cannot because there are other records that reference records in this table, you will need to "cascade" the effects as follows:
drop table Department cascade constraints; You don't have to do this step, of course :)]

You can also modify existing tables using SQL's alter table command. There is an example of this in CreateCompany.sql where the foreign key constraint on DNo is added. (You should understand why we couldn't put it in the definition of Employee. Marginal hint: C++ programmers will understand this better than those who only know Java.) Add an attribute Certified to the Employee table by entering the following:
alter table Employee add Certified char(1) check (Certified in ('Y', 'N'));

You will also update one of the records to include a value for this new attribute.
update Employee set Certified = 'Y' where SSN = 123456789;

Wrapping it up

Discuss your answers to the last 3 queries with a fellow student. It can be educational to see what other approaches are possible.

If you type quit or exit in SQLPlus, it will exit the program and save the logfile. Turn in your logfile via the digital drop box.