An example of some work I have done with Oracle SQL*Plus.

In this example, I create a model database of the type that may be used for a video rental store. I first show the code for creating the tables, then show a series of queries of the database, and finally show a report created using the database.

The database consists of six tables:

1) DIRECTOR, which contains a unique director number (the primary key), the director's name, the year born and the year deceased (if applicable). See the code for creating this table here (cdirector.sql), and the final table description and contents here.

2) STAR, which contains a unique star number (the primary key), the star's name, the birth-place of the star, the year born and the year deceased (if applicable). See the code for creating this table here (cstar.sql), and the final table description and contents here.

3) MOVIE, which contains a unique movie number (the primary key), the name of the movie, the year made, the type of movie (suspense, drama, etc.), the critic's rating, the rating (PG, R, etc.), the number of Academy Award nominations, the number of Academy Awards won, and the director number (a foreign key, see 1). See the code for creating this table here (cmovie.sql), and the final table description and contents here.

4) MOVSTAR, which contains two columns, one for movie number and the other for star number (both foreign keys). This table is used to relate the movie information to that of the stars. See the code for creating this table here (cmovstar.sql), and the final table description and contents here.

5) MEMBER, which contains a unique member number (the primary key), the member's name, the member's address, the city, the state, the number of times the member has rented a tape, the bonus points accumulated by the member, and finally the date the member joined. See the code for creating this table here (cmember.sql), and the final table description and contents here.

6) TAPE, which contains a unique tape number (the primary key), the movie number of the movie on the tape (a foreign key), the date the tape was purchased, the number of times the tape has been rented and the member number of the member currently renting the tape (if it is currently rented, this is another foreign key). See the code for creating this table here (ctape.sql), and the final table description and contents here

These 6 scripts are run by a master script, which is shown below:

set echo off
set feedback off
drop table movstar;
prompt Dropped table movstar.;
drop table star;
prompt Dropped table star.;
drop table tape;
prompt Dropped table tape.;
drop table movie;
prompt Dropped table movie.;
drop table director;
prompt Dropped table director.;
drop table member;
prompt Dropped table member.;

@cdirector.sql
@cstar.sql
@cmovie.sql
@cmovstar.sql
@cmember.sql
@ctape.sql

set feedback on
set echo on

=========================================================
1
For each tape, list the tape number and purchase date
along with the number and title of the movie on the
tape (result)

select tpnumb,purdate, t.mvnumb, mvtitle
2 from tape t, movie m
3 where t.mvnumb = m.mvnumb
4 /
=========================================================
2
For each movie of type comedy, list the movie number
and title along with the number and name of the
director of the movie (result).

select mvnumb, mvtitle, m.dirnumb, d.dirname
2 from movie m, director d
3 where d.dirnumb = m.dirnumb
4 /
=========================================================
3
For each tape that has been rented at least five
times, list the tape number and purchase date along
with the number and title of the movie on the tape (result).

select tpnumb, purdate,t.mvnumb, mvtitle
2 from tape t, movie m
3 where t.mvnumb = m.mvnumb
4 and tmsrent>=5
5 /
=========================================================
4
For each tape that is currently rented, list the tape
number and the movie number as well as the number
and name of the member who is currently renting the tape (result).

select tpnumb, mvnumb, t.mmbnumb, m.mmbname
2 from tape t, member m
3 where nvl(t.mmbnumb,0) != 0
4 and t.mmbnumb = m.mmbnumb
5 /
=========================================================
5
Find the numbers and names of all directors who have
directed at least one comedy. First, use keywork 'in'
then use keyword 'exists' (result).

select dirnumb, dirname
2 from director
3 where dirnumb in (select dirnumb
4 from movie
5 where mvtype = 'comedy')
6 /
=========================================================
6
Find the numbers and names of all directors who have never directed
a comedy (result).

select dirnumb, dirname
2 from director
3 where dirnumb not in (select dirnumb
4 from movie
5 where mvtype = 'comedy')
6 /
=========================================================
7
For each movie, list the title of the movie together with the names
and birthplaces of all the stars who appeared in the movie.(result)

break on mvtitle;

select mvtitle, starname, brthplce
2 from movie m, star s
3 where s.starnumb in (select starnumb
4 from movstar
5 where mvnumb = m.mvnumb)
6 order by mvtitle;
=========================================================
8
List the numbers and names of all stars in the database who have
appeared in at least one comedy. Do this both with and without
using a subquery (result).

select distinct s.starnumb, starname
2 from star s, movstar ms, movie m
3 where s.starnumb = ms.starnumb
4 and ms.mvnumb = m.mvnumb
5 and m.mvtype = 'comedy';

select starnumb, starname
2 from star
3 where starnumb in (select starnumb
4 from movstar
5 where mvnumb in (select mvnumb
6 from movie
7 where mvtype = 'comedy'));

=========================================================
9
List the numbers and names of all stars who have appeared
in any movies directed by Alfted Hitchcock.(result)

select starnumb, starname
2 from star
3 where starnumb in (select starnumb
4 from movstar
5 where mvnumb in (select mvnumb
6 from movie
7 where dirnumb = (select dirnumb
8 from director
9 where dirname like '%Hitchcock%')));
=========================================================
10
List the numbers and names of any pairs of movies that
are of the same type and have the same director.(result)

select m1.mvnumb, m1.mvtitle, m1.mvtype, m1.dirnumb
2 from movie m1
3 where m1.mvtype in (select m2.mvtype
4 from movie m2
5 where m1.mvtype = m2.mvtype
6 and m1.mvtitle != m2.mvtitle
7 and m1.dirnumb = m2.dirnumb)
8 order by dirnumb, mvtype;
=========================================================
11
For each movie, list the title, director's name, and
the name of all the stars who apeared in the movie (result).

select m.mvtitle, d.dirname, starname
2 from movie m, director d, star
3 where m.dirnumb = d.dirnumb
4 and starname in (select starname
5 from star
6 where starnumb in (select starnumb
7 from movstar ms
8 where ms.mvnumb = m.mvnumb))
9 order by m.mvtitle;
=========================================================
12
List the tape number and movie number for all tapes on
which the movie is a comedy and that are currently
rented by Mark Peterson.
(modified to accept arguments for movie type and member name) (result)

define Member_name = 'Peterson';
define movie_type = 'comedy';
set verify off;

select t.tpnumb, t.mvnumb
2 from tape t
3 where t.mmbnumb = (select mmbnumb
4 from member
5 where mmbname like initcap(lower('&Member_name%')))
6 and t.mvnumb in (select mvnumb
7 from movie
8 where mvtype like lower(substr('&movie_type%',1,6)));
=========================================================
13
List the tape number and movie number for all tapes on
which the movie is a comedy or that are rented by
Mark Peterson(modified to accept arguments for movie type and member name) (result).

select t.tpnumb, t.mvnumb
2 from tape t
3 where t.mmbnumb = (select mmbnumb
4 from member
5 where mmbname like initcap(lower('&Member_name%')))
6 or t.mvnumb in (select mvnumb
7 from movie
8 where mvtype like lower(substr('&movie_type%',1,6)));
=========================================================
14
List the tape number and movie number for all tapes on which
the movie is a comedy but are curently rented by somone other
than Mark Peterson
(modified to accept arguments for movie type and member name) (result)

select t.tpnumb, t.mvnumb
2 from tape t
3 where t.mmbnumb != (select mmbnumb
4 from member
5 where mmbname like initcap(lower('&Member_name%')))
6 and t.mvnumb in (select mvnumb
7 from movie
8 where mvtype like lower(substr('&movie_type%',1,6)));
=========================================================
15
List the movie number and title for all movies that were
nominated for more Academy Awards than any movie directed
by Woody Allen. Use either the word ALL or the word
ANY in the command. (result)

select m.mvnumb, m.mvtitle
2 from movie m
3 where m.noms > ALL (select noms
4 from movie
5 where dirnumb = (select dirnumb
6 from director
7 where dirname like '%Allen%'));



select m.mvnumb, m.mvtitle
2 from movie m
3 where m.noms > ANY (select noms
4 from movie
5 where dirnumb = (select dirnumb
6 from director
7 where dirname like '%Allen%'));