spool week7_insert_output.
txt
set echo on
-- 7.1
-- DDL for Student-Unit-Enrolment model
-- Place DROP commands at head of schema file
drop table enrolment purge;
drop table student purge;
drop table unit purge;
-- Create Tables
-- Here using both table and column constraints
--
create table student (
stu_nbr number(8) not null,
stu_lname varchar2(50) not null,
stu_fname varchar2(50) not null,
stu_dob date not null,
constraint pk_student primary key (stu_nbr),
constraint ck_stu_nbr check (stu_nbr > 10000000));
create table unit (
unit_code char(7) not null,
unit_name varchar2(50) not null constraint uq_unit_name unique,
constraint pk_unit primary key (unit_code));
create table enrolment (
stu_nbr number(8) not null,
unit_code char(7) not null,
enrol_year number(4) not null,
enrol_semester char(1) not null,
enrol_mark number(3),
enrol_grade char(3),
constraint pk_enrolment primary key (stu_nbr, unit_code, enrol_year, enrol_semester),
constraint enrol_sem_value check (enrol_semester in ('1', '2', '3')),
constraint fk_enrolment_student foreign key (stu_nbr)
references student (stu_nbr),
constraint fk_enrolment_unit foreign key (unit_code)
references unit (unit_code));
set echo off
spool off
-- 7.1
-- DDL for Student-Unit-Enrolment model
-- Place DROP commands at head of schema file
drop table enrolment purge;
drop table student purge;
drop table unit purge;
-- Create Tables
-- Here using both table and column constraints
create table student (
stu_nbr number(8) not null,
stu_lname varchar2(50) not null,
stu_fname varchar2(50) not null,
stu_dob date not null,
constraint pk_student primary key (stu_nbr),
constraint ck_stu_nbr check (stu_nbr > 10000000));
create table unit (
unit_code char(7) not null,
unit_name varchar2(50) not null constraint uq_unit_name unique,
constraint pk_unit primary key (unit_code));
create table enrolment (
stu_nbr number(8) not null,
unit_code char(7) not null,
enrol_year number(4) not null,
enrol_semester char(1) not null,
enrol_mark number(3),
enrol_grade char(3),
constraint pk_enrolment primary key (stu_nbr, unit_code, enrol_year, enrol_semester),
constraint enrol_sem_value check (enrol_semester in ('1', '2', '3')),
constraint fk_enrolment_student foreign key (stu_nbr)
references student (stu_nbr),
constraint fk_enrolment_unit foreign key (unit_code)
references unit (unit_code));
set echo off
spool off
*/
-- 7.3.1
-- Basic INSERT statement
--
===============================================================
=
insert into student values (11111111,'Bloggs','Fred','01-Jan-1990');
insert into student values (11111112,'Nice','Nick','10-Oct-1994');
insert into student values (11111113,'Wheat','Wendy','05-May-1990');
insert into student values (11111114,'Sheen','Cindy','25-Dec-1996');
insert into unit values ('FIT5132','Introduction to Databases');
insert into unit values ('FIT5016','Project');
insert into unit values ('FIT5111','Student''s Life');
insert into unit values ('FIT9999','FIT Last Unit');
insert into enrolment values (11111111,'FIT5132',2013,'1',35,'N');
insert into enrolment values (11111111,'FIT5016',2013,'1',61,'C');
insert into enrolment values (11111111,'FIT5132',2013,'2',42,'N');
insert into enrolment values (11111111,'FIT5111',2013,'2',76,'D');
insert into enrolment values (11111111,'FIT5132',2014,'2',null,null);
insert into enrolment values (11111112,'FIT5132',2013,'2',83,'HD');
insert into enrolment values (11111112,'FIT5111',2013,'2',79,'D');
insert into enrolment values (11111113,'FIT5132',2014,'2',null,null);
insert into enrolment values (11111113,'FIT5111',2014,'2',null,null);
insert into enrolment values (11111114,'FIT5111',2014,'2',null,null);
commit;
-- 7.3.2
-- Using sequences for INSERT
--
===============================================================
=
-- Create sequence
create sequence student_seq start with 11111115 increment by 1;
select * from cat;
-- cat refers to your Oracle catalogue, the objects which you own
-- Insert Mickey Mouse
insert into student values (student_seq.nextval, 'Mouse', 'Mickey', '01-Feb-1980');
select * from student;
-- Add an enrolment
insert into enrolment values (student_seq.currval, 'FIT5132', 2016, '2', null, null);
commit;
select * from enrolment;
-- 7.3.3
-- Advanced Insert
--
===============================================================
=
insert into student values (student_seq.nextval, 'Last', 'First', '01-Jan-1999');
insert into enrolment values (student_seq.currval,
(select unit_code from unit where unit_name = 'Introduction to Databases'),
2016, '2', null, null);
commit;
select * from student;
select * from enrolment;
-- 7.3.4
-- Create table and Insert data from a single SQL statement
--
===============================================================
=
create table FIT5111_STUDENT
as select *
from enrolment
where unit_code = 'FIT5111';
--Check if the table exist
select * from cat;
--List the contents of the table
select * from fit5111_student;
set echo off
spool off
/*
Databases Week 7 Tutorial Sample Solution
week7_altertable.sql
-- 7.4
-- Changing a tables structure
--
===============================================================
=
alter table unit
add (credit_points number(2,0) default 6);
insert into unit values ('FIT9111', 'Unit FIT9111', 12);
commit;
select * from unit;
set echo off
spool off
