assigment.md

-- create a table "libraryBooks" CREATE TABLE libraryBooks ( accessionNumber INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, department TEXT NOT NULL, purchaseDate date NOT NULL, price INTEGER NOT NULL );

-- create a table "issuedBooks" CREATE TABLE issuedBooks ( accessionNumber INTEGER PRIMARY KEY, borrower TEXT NOT NULL, foreign KEY (accessionNumber) references librarybooks(accessionNumber) ); -- insert some values in "libraryBooks" INSERT INTO libraryBooks VALUES (1110, "Database system Concepts", "Deep", "CS", "05-09-2019", 3000); INSERT INTO libraryBooks VALUES (1111, "Discrete Maths", "Ritu", "Maths", "01-08-2020", 250); INSERT INTO libraryBooks VALUES (1112, "HeadStrat With Python", "Navathe", "CS", "04-06-2020", 2000); INSERT INTO libraryBooks VALUES (1211, "Everything is illusion", "patrick", "philo", "01-01-2019", 1000); INSERT INTO libraryBooks VALUES (1114, "Automate with Python", "Jayant", "CS", "01-12-2018", 300);

-- insert some values in "issuedBooks" INSERT INTO issuedBooks VALUES (1110, "john"); INSERT INTO issuedBooks VALUES (1101, "steve"); INSERT INTO issuedBooks VALUES (1102, "mark"); INSERT INTO issuedBooks VALUES (1114, "george"); INSERT INTO issuedBooks VALUES (1104, "carle");

-- Detele the record of book titled "Database system Concepts" DELETE from libraryBooks WHERE title = "Database system Concepts"; SELECT * FROM libraryBooks;

-- Change the department of the book titled "Discrete Maths" to "CS" update libraryBooks set department = "CS" WHERE title = "Discrete Maths"; SELECT * FROM libraryBooks;

-- List all books that belong to "CS" SELECT * FROM libraryBooks WHERE department = "CS";

-- List all books that belong to "CS" and author is "Navathe" SELECT * FROM libraryBooks WHERE department = "CS" and author = "Navathe";

-- Ques: f SELECT * FROM libraryBooks l, issuedBooks i where l.accessionNumber = i.accessionNumber and l.department= "CS";

-- Ques: g SELECT * FROM libraryBooks WHERE price < 500 and purchaseDate between '01-11-2018' and '01-01-2019';

-- Ques: h DELETE from libraryBooks WHERE accessionNumber = 1211;