Scribd Logo
Upload

Welcome to Scribd!

  • 17 views

    Dbms Assignment-1 Baarth

    Uploaded by

    b29012005

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Dbms Assignment-1 Baarth

    Uploaded by

    b29012005
    17 views6 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    17 views6 pages

    Dbms Assignment-1 Baarth

    Uploaded by

    b29012005

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 6

    ASSIGNMENT-1 IMPLEMENTATION M BARATH RAJ

    917722IT012

    Step 1: Uploading the Dataset:


    Before executing queries, must create the database and tables, and then insert the dataset
    into these tables. This step typically involves:

    Creating a Database:
    CREATE DATABASE Bookstore;

    Creating Tables:
    CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    AuthorName VARCHAR(100),
    Country VARCHAR(50)
    );

    CREATE TABLE Books (


    BookID INT PRIMARY KEY,
    Title VARCHAR(255),
    AuthorID INT,
    Price FLOAT,
    YearPublished YEAR,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
    );
    ASSIGNMENT-1 IMPLEMENTATION M BARATH RAJ
    917722IT012

    Step 2: Inserting Data into the tables:


    1) Insert data into Authors table
    INSERT INTO Authors (AuthorID, AuthorName, Country) VALUES
    (1, 'Margaret Atwood', 'Canada'),
    (2, 'George Orwell', 'United Kingdom'),
    (3, 'Haruki Murakami', 'Japan'),
    (4, 'Chimamanda Ngozi Adichie', 'Nigeria');

    2) Insert data into Book table


    INSERT INTO Books (BookID, Title, AuthorID, Price, YearPublished) VALUES
    (1, 'The Handmaid\'s Tale', 1, 15.95, 1985),
    (2, '1984', 2, 12.99, 1949),
    (3, 'Kafka on the Shore', 3, 18.00, 2002),
    (4, 'Half of a Yellow Sun', 4, 14.95, 2006),
    (5, 'Oryx and Crake', 1, 13.95, 2003),
    (6, 'Animal Farm', 2, 9.99, 1945),
    (7, '1Q84', 3, 19.99, 2009),
    ASSIGNMENT-1 IMPLEMENTATION M BARATH RAJ
    917722IT012

    (8, 'Americanah', 4, 20.00, 2013);

    Step 3: Executing Queries


    Simple Queries:
    1) Select all books published after 2000:
    SELECT * FROM Books WHERE YearPublished > 2000;

    2) Find all authors from 'United States':


    SELECT * FROM Authors WHERE Country = 'United States';
    ASSIGNMENT-1 IMPLEMENTATION M BARATH RAJ
    917722IT012

    3) List all books with their authors' names:


    SELECT Books.Title, Authors.AuthorName FROM Books JOIN Authors ON
    Books.AuthorID = Authors.AuthorID;

    4) Count the number of books for each author:


    SELECT AuthorID, COUNT(BookID) AS NumberOfBooks FROM Books
    GROUP BY AuthorID;

    5) Find books cheaper than $20:


    SELECT Title, Price FROM Books WHERE Price < 20;
    ASSIGNMENT-1 IMPLEMENTATION M BARATH RAJ
    917722IT012

    Complex Queries:
    1) Find authors with more than 3 books:
    SELECT Authors.AuthorName, COUNT(Books.BookID) AS
    NumberOfBooks
    FROM Authors
    JOIN Books ON Authors.AuthorID = Books.AuthorID
    GROUP BY Authors.AuthorID
    HAVING COUNT(Books.BookID) > 3;

    2) List the average book price for each author:


    SELECT Authors.AuthorName, AVG(Books.Price) AS AveragePrice
    FROM Authors
    JOIN Books ON Authors.AuthorID = Books.AuthorID
    GROUP BY Authors.AuthorID;

    3) Find the most expensive book for each country:


    SELECT Authors.Country, MAX(Books.Price) AS MaxPrice
    FROM Authors
    JOIN Books ON Authors.AuthorID = Books.AuthorID
    GROUP BY Authors.Country;
    ASSIGNMENT-1 IMPLEMENTATION M BARATH RAJ
    917722IT012

    4) List all books and their authors for a specific year (e.g., 2010):
    SELECT Books.Title, Authors.AuthorName
    FROM Books
    JOIN Authors ON Books.AuthorID = Authors.AuthorID
    WHERE Books.YearPublished = 2010;

    5) Find the average price of books for each author and only display authors
    who have published more than one book.
    SELECT a.AuthorName, COUNT(b.BookID) AS NumberOfBooks,
    AVG(b.Price) AS AveragePrice
    FROM Authors a
    JOIN Books b ON a.AuthorID = b.AuthorID
    GROUP BY a.AuthorName
    HAVING COUNT(b.BookID) > 1;

    You might also like