07 Oct Python programming with Databases

 07 Oct Python programming with Databases

LAB 1: Create a table named Books with the following columns: 1) BookID (INT, Primary Key, Auto Increment) 2) Title (VARCHAR) 3) Author (VARCHAR) 4) Genre (VARCHAR) 5) YearPublished (INT) After creating the table, write a SQL query to insert a new book with the following details: 1. Title: “The Alchemist" 2. Author: "Paulo Coelho" 3. Genre: "Fiction" 4. Year Published: 1988 Write a SQL query to retrieve all books Constraint The table should be created from scratch. The BookiD should be auto-incremented. All columns must be non-empty when inserting a new book.

CREATE TABLE Books (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    Genre VARCHAR(100) NOT NULL,
    YearPublished INT NOT NULL
);

INSERT INTO Books (Title, Author, Genre, YearPublished)
VALUES ('The Alchemist', 'Paulo Coelho', 'Fiction', 1988);

SELECT * FROM Books;

LAB 2: Create Table and Retrieve Books Published After 2000 Question Text: Create a table named Books with the following columns: BookiD (INT, Primary Key, Auto Increment) Title (VARCHAR) Author (VARCHAR) Genre (VARCHAR) YearPublished (INT) After creating the table, insert the following books: “The Alchemist" | "Paulo Coelho" | "Fiction" | 1988 “The Da Vinci Code” | "Dan Brown" | "Fiction" | 2003 Write a SQL query to retrieve all books published after the year 2000. Constraint The table should be created from scratch. The query should return the BookiD, Title, Author, and YearPublished columns.

CREATE TABLE Books (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    Genre VARCHAR(100) NOT NULL,
    YearPublished INT NOT NULL
);

INSERT INTO Books (Title, Author, Genre, YearPublished)
VALUES 
('The Alchemist', 'Paulo Coelho', 'Fiction', 1988),
('The Da Vinci Code', 'Dan Brown', 'Fiction', 2003);

SELECT BookID, Title, Author, YearPublished
FROM Books
WHERE YearPublished > 2000;

LAB 3: Create Table and Update a Book's Genre Question Text: Create a table named Books with the following columns: BookID (INT, Primary Key, Auto Increment) Title (VARCHAR) Author (VARCHAR) Genre (VARCHAR) YearPublished (INT) After creating the table, insert the following book: "The Alchemist" | "Paulo Coelho" | "Fiction" | 1988 Write a SQL query to retrieve the book with BookID = 1 Write a SQL query to update the genre of the book with BookID = 1to "Adventure". Write a SQL query to retrieve the book with BookID = 1 It should output the book details with updated genre value

CREATE TABLE Books (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    Genre VARCHAR(100) NOT NULL,
    YearPublished INT NOT NULL
);

INSERT INTO Books (Title, Author, Genre, YearPublished)
VALUES ('The Alchemist', 'Paulo Coelho', 'Fiction', 1988);

SELECT* FROM Books WHERE BookID = 1;

UPDATE Books
SET Genre = 'Adventure'
WHERE BookID = 1;

LAb 4: Create Table and Delete a Book Question Text: Create a table named Books with the following columns: BooklD (INT, Primary Key, Auto Increment) Title (VARCHAR) Author (VARCHAR) Genre (VARCHAR) YearPublished (INT) After creating the table, insert the following books: “The Alchemist” | “Paulo Coelho" | “Fiction” | 1988 *1984" | "George Orwell" | “Dystopian” | 1949 Write a SQL query to retrieve all books Write a SQL query to delete the book with BookiD = 2. Write a SQL query to retrieve all books. It should return only one row with BookiD = 1. Constraint The table should be created from scratch. Ensure that the book with BookiD = 2 exists before attempting to delete it.

CREATE TABLE Books (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    Genre VARCHAR(100) NOT NULL,
    YearPublished INT NOT NULL
);

INSERT INTO Books (Title, Author, Genre, YearPublished)
VALUES 
('The Alchemist', 'Paulo Coelho', 'Fiction', 1988),
('1984', 'George Orwell', 'Dystopian', 1949);

SELECT * FROM Books WHERE BookID = 2;

DELETE FROM Books WHERE BookID = 2;