John Sullivan

DataBase Implementation Using SQL

The following steps describe how I implemented my database using the QUEL and SQL languages in Ingres. QUEL and SQL are different languages. You can execute SQL and QUEL interchangeably (Slide #2 of SQL tutorial). I had tested this and thought I had fo und out otherwise. If you type -- Ingres Yourdatabase -- you can only use retrieve commands. However, if you type -- SQL Yourdatabase -- you can use the select statement. This is meant to clarify some of the difficulties I encountered, and hopefully help others.

Step Number:

  1. At the Unix prompt I typed: telnet alpha
  2. I created the database "profdb" using the Unix command:

    3. createdb profdb

  3. The database was initialized with the following command:

    4. sysmod profdb

    This program will convert the system relations to their "best" structure for use in Ingres according to Robert Epstein. Epstein is the developer of the Ingres tutorial for the course.

  4. Next the relations, as well as the queries, of the database were created in Word, and saved as text files. Creating these queries was just faster, for editing purposes, than using PICO. This process is pain stakingly slow (it reminds me of the days of punch cards). They were then FTP- ed to my Unix account. I did this in modules to see if they would work. When the first program worked I moved to the next and so on. I saved these input files under two different names. One that could be executed in Unix and one that could be marked in HTML. There are two reasons the output was then redirected to a file. One was that I could save my output, and the other was once it was saved I could mark it up with HTML for displaying on the web. The command is:

    5. ingres profdb < profinput1 > profouput1

    or SQL profdb sqloutput1

    Note: the below did not work with print statements included.

    Ingres profdb < profinput1 > profoutput1

  5. I also noticed that there was some variation from project to project in the Student Showroom. When I tried to execute my first query it would not run with the SQL command (The print command is an Ingres command). I was about to rewrite the create statements to a different format I saw some students using, but decided to use Ingres (noted above).

Module One

After executing steps 1 - 3 above, I created the structure of the Profdb and printed out the null valued tables. Profinput1 contains create and print commands. The SQL equivalent would be create and select * commands. Click below for sample.

profinput1

sqlinput1

profoutput1

sqloutput1

Module Two: Relations recreated and populated

Next, I had to destroy the tables so that they could be recreated and populated. I did this manually by going into Ingres with the Ingres Profdb command and then typed: 

SQL				Ingres
drop course			destroy course
drop student			destroy student
drop assignment			destroy assignment
drop clippings 			destroy clippings
drop grading			destroy grading
drop stud_course		destroy stud_course
\g				\g

profinput2

profoutput2

Module Three (Adding more tuples to your database)

You do not need to use the drop or destroy command to add records. I went into the database and added more records manually. I found this to be faster. A tuple was added with the append command and \g. I then used the range, retrieve, & \g commands to check for data entry errors. The SQL command is select * from yourdatabase & \g. I created a mod3input and mod3output file so that these commands and the output could be viewed.

By mistake I noticed I created the same record twice in two relations. The command used to delete a specific record is: 

	
range of g is grading
delete g
where g.studssn="111-22-3333"
\g

Module Four:

Producing Student Lists by Course Number

Click the following input and output files:

mod4input

mod4output 

	
QUEL
range of s is student
range of r is stud_course		
retrieve (r.coursenum, s.lname, s.fname) 
where r.studssn = s.studssn
and  r.coursenum = "21-261"
\g


SQL
select coursenum,lname,fname
from student, stud_course
where student.studssn=stud_course.studssn
and stud_course.coursenum="21-261"

Module Five

Students and Grades

The next listing is retrieving from two relations student grades. Click the following files for more detail.

mod5input

mod5output 

QUEL
range of s is student
range of g is grade
retrieve (s.lname, s.fname, g.assignnum, g.assigngrade)
where s.studssn = g.studssn
\g


SQL 
select lname,fname,assignnum,assigngrade
from student, grade
where student.studssn=grade.studssn
\g

Module Six

Clippings by Course Click the following files for more detail:

mod6input

mod6output 

QUEL
range of c is clippings
range of r is course
retrieve (r.coursenum, c.cliptopic, c.clipauthor, c.clipdate)
where r.coursenum = s.coursenum
\g


SQL
select coursenum,cliptopic,clipauthor,clipdate
from course.coursnum,clippings.cliptopic, 
clippings.clipauthor,clippings.clipdate;
where course.coursenum=clippings.coursenum
\g

Module Seven

Students in Trouble (Grades lower than 70)

Click the following files for more detail

mod7input

mod7output 

range of s is student
range of g is grades
retrieve (g.coursenum, s.lname, s.fname)
where s.studssn = g.studssn
and g.assigngrade < 70
\g


SQL
select coursenum,lname,fname
from grades.coursenum, student.lname,student.fname;
where grades.studssn=student.studssn;
and grades.assigngrade <70
\g

Return to previous page