CS2101 Lab1

lab1.sql

-- 1
CREATE TABLE book (
  title VARCHAR(256) NOT NULL,
  format CHAR(9) NOT NULL,
  pages INT,
  authors VARCHAR(256),
  publisher VARCHAR(256),
  year DATE,
  edition INT,
  ISBN10 CHAR(10) NOT NULL UNIQUE,
  ISBN13 CHAR(14) PRIMARY KEY
);

-- 2
DROP TABLE book;

-- 3
CREATE TABLE book (
  title VARCHAR(256) NOT NULL,
  format CHAR(9) CHECK(format = 'paperback' OR format='hardcover'),
  pages INT,
  authors VARCHAR(256),
  publisher VARCHAR(256),
  year DATE,
  edition INT,
  ISBN10 CHAR(10) NOT NULL UNIQUE,
  ISBN13 CHAR(14) PRIMARY KEY
);

-- 4
ALTER SESSION SET NLS_DATE_FORMAT='YYYY';
INSERT INTO book VALUES ('Introduction to Databases', 'paperback', 168, 'Stephane Bressan', 'McGraw-Hill Education (Asia) (January 2005)', '2005', 1, '0071246509', '978-0071246507');

-- 5
INSERT INTO book VALUES ('An Introduction to Database Systems', 'paperback', 123, 'C.J. Date', 'McGraw-Hill', '2005', 1, '0071246508', '978-0123456789');
INSERT INTO book VALUES ('Database Design and Relational Theory: Normal Forms and All That Jazz (Theory in Practice)', 'paperback', 456, 'C.J.Date', 'Marvel', '2006', 1, '0071246510', '978-0071246508');
INSERT INTO book VALUES ('Database in Depth: Relational Theory for Practitioners', 'paperback', 789, 'C.J. Date', 'McGraw-Hill', '2014', 2, '0071246511', '978-0071246509');
INSERT INTO book VALUES ('Introduction to Random Database', 'hardcover', 1680, 'C.J. Date', 'DC', '1999', 1, '0071246512', '978-0071246510');
INSERT INTO book VALUES ('Date on Database: Writings 2000-2006', 'paperback', 230, 'C.J. Date', 'Apress', '2005', 3, '0071246513', '978-0071246511');

-- 6
SELECT * FROM book;

-- 7 Modify all books authored by C.J. Date to mention the author’s first name (find the author’s first name from the Web.) 
UPDATE book SET authors='Christopher J. Date' WHERE authors='C.J. Date';

-- 8
DELETE FROM book WHERE authors LIKE 'C% J. Date';

-- 9 Find the title, format, number of pages, authors, publisher, year, edition, ISBN-10 and ISBN-13 of the books
SELECT title, format, pages, authors, publisher, year, edition, ISBN10, ISBN13 from book;

-- 10 Find all titles of the books
SELECT title from book;

-- 11 Find the authors of the book called "Introduction to Databases".
SELECT authors FROM book WHERE title='Introduction to Databases';

-- 12 Add a language attribute to all books. Set the default language to English.
ALTER TABLE book ADD language VARCHAR(32) DEFAULT 'English';

-- 13 Delete the table book
DROP TABLE book;