SQL Examples

create table students (
        id integer, 
        lname varchar2(20), 
        fname varchar2(20),
        primary key(id));
create table courses (
        id integer primary key,
        title varchar2(12)
        );
create table register (
        student_id integer,
        course_id integer,
        primary key (student_id, course_id),
        foreign key (student_id) references students(id) on delete cascade,
        foreign key (course_id) references courses(id) on delete cascade);
      
insert into students (id,lname,fname)
        values (1,'Smith','Bob');
insert into students (id,lname,fname)
        values (2,'Jones','Chuck');
select * from students;
alter table students add email varchar2(80) unique
alter table students drop column abcd
delete from students 
        where id=2;


update students
        set fname='Bill'
        where id=1;
      
select * from register;
select s.lname,s.fname,c.title
        from students s, courses c, register r
        where s.id=r.student_id
        and c.id=r.course_id;
select s.lname,s.fname,c.title
        from students s, courses c, register r
        where s.id=r.student_id
        and c.id=r.course_id
        and c.id=1;
create or replace view register_view as
        select s.lname,s.fname,c.title
        from students s, courses c, register r
        where s.id=r.student_id
        and c.id=r.course_id;
create sequence student_id_seq
        increment by 1
        start with 1001
create or replace trigger student_insert
        before insert on students
        for each row
        begin
        select student_id_seq.nextval
        into :new.id
        from dual;
        end;