delete_rows.sql

Advanced SQL For this exercise, we will use the library schema. It includes information about book

--DROP TABLE author4 ;
--DROP TABLE hold4 ;
DROP TABLE TITLE4;
DROP TABLE MEMBER4;
DROP TABLE BOOK4;

CREATE TABLE TITLE4
 (callnumber  char(8) NOT NULL,
  isbn        char(8),
  year        date,
  name        char(14),
  authorfname char(14),
  authorlname char(14),
  PRIMARY KEY (callnumber)
);

CREATE TABLE MEMBER4
 (libid       char(6) NOT NULL,
  fname       char(14),
  lname       char(14),
  address     char(20),
  phonenumber char(10),
  PRIMARY KEY (libid)
);

CREATE TABLE BOOK4
 (book_id       char(8) NOT NULL,
  edition       char(10),
  status        char(5),
  borrowerid    char(6),
  borrowduedate date,
  callnumber    char(8),
  PRIMARY KEY (book_id)
);

/*
#
#
# *************************************************************
#
# *** Load values ***
#
#
# 3 rows in title4
#
*/

INSERT INTO TITLE4 VALUES
 ('AB123456', '12345687', TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'Discrete Maths', 'Shadow', 'Jones');
INSERT INTO TITLE4 VALUES
 ('CD456756', '45454598', TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'Intro to CS', 'Kelvin', 'Smith');
INSERT INTO TITLE4 VALUES
 ('EF121221', 'A4245678', TO_DATE('1998/01/01', 'YYYY/MM/DD'), 'Intro to CE', 'Stella', 'Potter');

/*
#
# 2 rows in MEMBER4
#
*/
INSERT INTO MEMBER4 VALUES
 ('123423', 'Jessy', 'Owens', '35 Side Pgh 15255', '4126874534');
INSERT INTO MEMBER4 VALUES
 ('A34565', 'Diana', 'Emily', '25 Side Pgh 15245', '4123456452');

/*
#
# 2 rows in BOOK4
#
*/
INSERT INTO BOOK4 VALUES
 ('Q76.98.1', 'first', 'good', 'A34565', TO_DATE('1997-10-10', 'YYYY/MM/DD'), 'AB123456');
INSERT INTO BOOK4 VALUES
 ('Q76.95.6', 'third', 'ok', '123423', TO_DATE('1998-06-06', 'YYYY/MM/DD'), 'EF121221');

DELETE FROM TITLE4;
DELETE FROM BOOK4;
DELETE FROM MEMBER4;