SQL Example Music Database
export NLS_CHARACTERSET=AL32UTF8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
CREATE TABLESPACE MUSIC_128K
DATAFILE 'F:\MIKE\MUSIC128K01.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
CREATE TABLESPACE MUSIC_4M
DATAFILE 'F:\MIKE\MUSIC4M01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
CREATE TABLESPACE MUSIC_INDEX_128K
DATAFILE 'F:\MIKE\MUSIC_INDEX_128K01.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
rem create user
create user music identified by music
default tablespace music_128K temporary tablespace temp;
rem will not work in 9i create user musicapp identified by musicapp default tablespace temp temporary tablespace temp;
create user musicapp identified by musicapp
default tablespace music_128K temporary tablespace temp;
rem create privileges
grant dba to music;
grant connect to musicapp;
rem connect as owner of tables you are about to create
connect music/music
rem create tables
drop table album;
drop table song;
drop table artist;
drop table band;
drop table tour;
create table album
(
band varchar2(20),
name varchar2(30),
year number
);
create table song
(
name varchar2(40),
album varchar2(30),
track number
);
create table artist
(
name varchar2(20),
band varchar2(20),
job varchar2(10),
birthdate date
);
create table band
(
name varchar2(20),
year number
);
create table tour
(
name varchar2(20),
band varchar2(20),
year number
);
rem pk indexes
drop index IXPK_ALBUM;
drop index IXPK_SONG;
create unique index IXPK_ALBUM on ALBUM
(NAME) tablespace music_index_128k;
create unique index IXPK_ARTIST on ARTIST
(NAME) tablespace music_index_128k;
create unique index IXPK_BAND on BAND
(NAME) tablespace music_index_128k;
create unique index IXPK_SONG on SONG
(NAME) tablespace music_index_128k;
create unique index IXPK_TOUR on TOUR
(NAME) tablespace music_index_128k;
rem fk indexes
create index IXF01_ALBUM on ALBUM
(BAND) tablespace music_index_128k;
create index IXF01_ARTIST on ARTIST
(BAND) tablespace music_index_128k;
create index IXF01_SONG on SONG
(ALBUM) tablespace music_index_128k;
create index IXF01_TOUR on TOUR
(BAND) tablespace music_index_128k;
rem pk constraint
alter table album add primary key (name);
alter table artist add primary key (name);
alter table band add primary key (name);
alter table song add primary key (name);
alter table tour add primary key (name);
rem fk constraint
alter table album drop constraint fk_album_band;
alter table artist drop constraint fk_artist_band;
alter table song drop constraint fk_song_album;
alter table tour drop constraint fk_tour_band;
ALTER TABLE ALBUM ADD (CONSTRAINT FK_ALBUM_BAND FOREIGN KEY (BAND) REFERENCES "BAND");
ALTER TABLE ARTIST ADD (CONSTRAINT FK_ARTIST_BAND FOREIGN KEY (BAND) REFERENCES "BAND");
ALTER TABLE SONG ADD (CONSTRAINT FK_SONG_ALBUM FOREIGN KEY (ALBUM) REFERENCES "ALBUM");
ALTER TABLE TOUR ADD (CONSTRAINT FK_TOUR_BAND FOREIGN KEY (BAND) REFERENCES "BAND");
rem insert data into band
insert into band values('U2', 1976);
insert into band values('Guns and Roses', 1985);
insert into band values('Nirvana', '1986');
rem insert data into album
insert into album values('U2', 'Boy', 1980);
rem 11-17-80
insert into album values('U2', 'October', 1981);
rem 10-13-81
insert into album values('U2', 'War', 1983);
rem 02-28-83
insert into album values('U2', 'The Unforgetable Fire', 1984);
rem 10-01-84
insert into album values('U2', 'The Joshua Tree', 1987);
rem 03-09-87
insert into album values('U2', 'Rattle and Hum', 1988);
rem 10-10-88
insert into album values('U2', 'Achtung Baby', 1991);
rem 11-18-91
insert into album values('U2', 'Zooropa', 1993);
rem 07-05-93
insert into album values('U2', 'Pop', 1997);
rem 03-03-97
insert into album values('U2', 'All That You Cant Leave Behind', 2000);
rem 10-30-00
insert into album values('Guns and Roses', 'Use Your Illusion I', 1991);
rem insert data into song
insert into song values('I Will Follow', 'Boy', 1);
insert into song values('Gloria', 'October', 1);
insert into song values('Sunday Bloody Sunday', 'War', 1);
insert into song values('New Years Day', 'War', 3);
insert into song values('Pride', 'The Unforgetable Fire', 2);
insert into song values('Where The Steets Have No Name', 'The Joshua Tree', 1);
insert into song values('I Still Havent Found What Im Looking For', 'The Joshua Tree', 2);
insert into song values('With or Without You', 'The Joshua Tree', 3);
insert into song values('Bullet The Blue Sky', 'The Joshua Tree', 4);
insert into song values('Desire', 'Rattle and Hum', 3);
insert into song values('One', 'Achtung Baby', 3);
insert into song values('Until The End of The World', 'Achtung Baby', 4);
insert into song values('Mysterious Ways', 'Achtung Baby', 8);
insert into song values('Numb', 'Zooropa', 3);
insert into song values('Lemon', 'Zooropa', 4);
insert into song values('Stay', 'Zooropa', 5);
insert into song values('Do You Feel Loved?', 'Pop', 2);
insert into song values('Walk On', 'All That You Cant Leave Behind', 4);
insert into song values('Kite', 'All That You Cant Leave Behind', 5);
rem insert data into artist
insert into artist values('Bono', 'U2', 'Vocal', '10-MAY-1960');
insert into artist values('The Edge', 'U2', 'Guitar', '8-AUG-1961');
insert into artist values('Adam', 'U2', 'Bass', '13'MAR-1960');
insert into artist values('Larry Mullen Jr.', 'U2', 'Drums', '31-OCT-1961');
insert into artist values('AXL', 'Guns and Roses', 'Vocal', '6-FEB-1962');
insert into artist values('SLash', 'Guns and Roses', 'Guitar', '23-JUL-1965');
insert into artist values('Kurt Cobain', 'Nirvana', 'Guitar', '20-Feb-1967');
rem insert data into tour
insert into tour values('Conspiracy of Hope Tour', 'U2', 1986);
insert into tour values('The Joshua Tree Tour', 'U2', 1987);
insert into tour values('The Lovetown Tour', 'U2', 1989);
insert into tour values('Zoo TV', 'U2', 1992);
insert into tour values('Zooropa', 'U2', 1993);
insert into tour values('Popmart', 'U2', 1997);
insert into tour values('Elevation', 'U2', 2001);
insert into tour values('Use Your Illusion', 'Guns and Roses', 1991);
insert into tour values('Chinese Democracy', 'Guns and Roses', 2002);
rem functions
create or replace function count_albums_func(my_var in varchar2)
return number is
x number;
begin
dbms_output.put_line('Count_albums');
select count(*) into x from album where artist = my_var;
dbms_output.put_line(x);
return(x);
end;
/
rem SQL> execute :x := count_albums_func('U2');
rem Count_albums
rem 9
rem PL/SQL procedure successfully completed.
rem SQL> print x
rem X
rem ----------
rem 9
create or replace function what_day_is_it_func
return date is
x date;
begin
dbms_output.put_line('What Day Is It?');
select sysdate into x from dual;
dbms_output.put_line(x);
return(x);
end;
/
create or replace procedure count_albums_proc(my_var in varchar2)
as
cursor my_cursor is select count(*) from album where band = my_var;
x number;
begin
open my_cursor;
fetch my_cursor into x;
close my_cursor;
dbms_output.put_line('Count_albums');
dbms_output.put_line('my_var = '||my_var);
dbms_output.put_line(x);
end;
/
rem SQL> execute count_albums_proc('U2');
rem Count_albums
rem my_var = U2
rem 10
create or replace procedure what_day_is_it_proc as
x date;
begin
dbms_output.put_line('What Day Is It?');
select sysdate into x from dual;
dbms_output.put_line(x);
end;
/
rem SQL> set serveroutput on
rem SQL> execute what_day_is_it_proc
rem What Day Is It?
rem 25-JUN-01
----------------------------------------------------------------------
rem errors:
rem create tablespace
rem If you don't have enough space:
rem ORA-27044: unable to write the header block of file
rem OSD-04008: WriteFile() failure, unable to write to file
rem O/S-Error: (OS 112) There is not enough space on the disk.
rem SQL> insert into song values('With or Without You', 'The Joshua Tree', 3);
rem ORA-00001: unique constraint (MUSIC.IXPK_SONG) violated
rem SQL> insert into song values('Where The Steets Have No Name', 'The Joshua Tree2', 3)
rem ORA-02291: integrity constraint (MUSIC.FK_SONG_ALBUM) violated - parent key not found