SQL Practice Questions Assignment

[Pankaj-Str]

Database Schema

Use the following tables for all questions:

Authors (author_id, first_name, last_name, birth_year, nationality)
Books (book_id, title, author_id, publication_year, genre, price, stock)
Customers (customer_id, first_name, last_name, email, join_date)
Orders (order_id, customer_id, book_id, order_date, quantity, total_amount)

Basic Questions (CREATE and INSERT)

1. Create the Authors table with appropriate data types and constraints. Make author_id the primary key and ensure first_name and last_name cannot be null.

2. Create the Books table with:

   • A primary key for book_id
   • A foreign key referencing Authors table
   • A constraint ensuring price is greater than 0
   • A default value of 0 for stock

3. Insert three authors into the Authors table with different nationalities.

4. Insert five books into the Books table, ensuring at least two books are by the same author.

Intermediate Questions (SELECT and WHERE)

5. Write a query to find all books priced between $15 and $25.

6. Find all books with either "Python" or "Java" in their title.

7. List all authors who were born after 1980 AND are either American or British.

8. Display all customers who joined in 2023 but haven't placed any orders.

Advanced Questions (JOIN and GROUP BY)

9. Show the total number of books sold for each author, including authors who haven't sold any books.

10. Find the top 3 customers who have spent the most money on books, showing their name and total spending.

11. List all books along with their authors' names where the book's price is above the average book price.

12. Show each genre and the number of books in that genre, but only for genres with more than 3 books.

Expert Level Questions (Complex Operations)

13. Update the prices of all books in a specific genre by increasing them by 10%, but only if the current stock is less than 5.

14. Delete all orders that are more than a year old AND have a total_amount less than $10.

15. Find authors who have written books in at least 3 different genres.

16. Create a summary showing for each month of 2023:

    • Total number of orders
    • Total revenue
    • Best-selling book (by quantity)
    • Number of unique customers

NULL and Constraints Questions

17. Find all books where either the genre is NULL or the price is NULL, but not both.

18. List customers who have placed orders but have NULL email addresses.

19. Show all authors who have no books in the Books table.

Pattern Matching and Ordering Questions

20. Find all books whose titles start with 'The' and end with either 'Guide' or 'Tutorial'.

21. List authors' full names (combined first and last name) ordered by last name, then first name.

22. Show the top 5 months with the highest average order amounts.

Bonus Challenge Questions

23. Write a query to identify potential duplicate customer records based on similar names and email patterns.

24. Create a customer ranking system based on:

    • Total money spent (50% weight)
    • Number of orders (30% weight)
    • Account age (20% weight)

25. Find books that have never been ordered together in the same order (pairs of books that have never appeared together).

Requirements for Submission:

1. All queries must run without errors
2. Include comments explaining your approach
3. For update/delete operations, include a SELECT statement to verify the changes
4. Use appropriate aliases for better readability
5. Follow SQL best practices for formatting and naming conventions

Scoring Guide:

• Basic Questions (1-4): 20%
• Intermediate Questions (5-8): 20%
• Advanced Questions (9-12): 25%
• Expert Level Questions (13-16): 20%
• Remaining Questions (17-25): 15%

[sho786aib]

-- Q.1 Create the Authors table with appropriate data types and constraints. Make author_id the primary key and ensure first_name and last_name cannot be null.
CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_year INT,
    nationality VARCHAR(50)
);
select * from Authors;
-- Q.2 Create the Books table with:
-- A primary key for book_id
-- A foreign key referencing Authors table
-- A constraint ensuring price is greater than 0
-- A default value of 0 for stock

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    publication_year INT,
    genre VARCHAR(50),
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INT DEFAULT 0,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);
select * from Books;
-- Q.3 Insert three authors into the Authors table with different nationalities.
INSERT INTO Authors (author_id, first_name, last_name, birth_year, nationality)
VALUES 
(1, 'Alice', 'Walker', 1944, 'American'),
(2, 'James', 'Smith', 1985, 'British'),
(3, 'Maria', 'Lopez', 1975, 'Spanish');
-- Q.4 Insert five books into the Books table, ensuring at least two books are by the same author.
INSERT INTO Books (book_id, title, author_id, publication_year, genre, price, stock)
VALUES 
(101, 'Learning Python', 1, 2020, 'Programming', 22.99, 10),
(102, 'Mastering Java', 1, 2021, 'Programming', 19.99, 5),
(103, 'Data Science Basics', 2, 2022, 'Science', 25.50, 7),
(104, 'Modern Art Explained', 3, 2019, 'Art', 17.75, 3),
(105, 'Java for Beginners', 2, 2023, 'Programming', 14.99, 0);
-- Q.5 Write a query to find all books priced between $15 and $25.
SELECT * FROM Books
WHERE price BETWEEN 15 AND 25;

-- Q.6 Find all books with either "Python" or "Java" in their title.
SELECT * FROM Books
WHERE title LIKE '%Python%' OR title LIKE '%Java%';

-- Q.7 List all authors who were born after 1980 AND are either American or British.
SELECT * FROM Authors
WHERE birth_year > 1980
AND nationality IN ('American', 'British');
-- Q.8 Display all customers who joined in 2023 but haven't placed any orders.
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    join_date DATE NOT NULL
);
select * from customers;
INSERT INTO Customers (customer_id, first_name, last_name, email, join_date)
VALUES 
(201, 'John', 'Doe', 'john.doe@example.com', '2023-01-10'),
(202, 'Emma', 'Clark', 'emma.clark@example.com', '2023-02-05'),
(203, 'Mike', 'Brown', 'mike.brown@example.com', '2022-11-25'),
(204, 'Lily', 'Adams', 'lily.adams@example.com', '2023-07-18'),
(205, 'David', 'Wang', 'david.wang@example.com', '2021-09-30');

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    book_id INT,
    order_date DATE NOT NULL,
    quantity INT CHECK (quantity > 0),
    total_amount DECIMAL(10, 2) CHECK (total_amount >= 0),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (book_id) REFERENCES Books(book_id)
);
select * from Orders;
INSERT INTO Orders (order_id, customer_id, book_id, order_date, quantity, total_amount)
VALUES 
(301, 201, 101, '2023-03-01', 1, 22.99),
(302, 203, 104, '2023-04-15', 2, 35.50),
(303, 205, 102, '2023-05-10', 1, 19.99),
(304, 201, 105, '2023-07-21', 1, 14.99),
(305, 205, 103, '2024-01-05', 1, 25.50);

SELECT * FROM Customers
WHERE YEAR(join_date) = 2023
AND customer_id NOT IN (
    SELECT DISTINCT customer_id FROM Orders
);

-- Q.9 Show the total number of books sold for each author, including authors who haven't sold any books.
SELECT 
    a.author_id,
    a.first_name,
    a.last_name,
    COALESCE(SUM(o.quantity), 0) AS total_books_sold
FROM 
    Authors a
LEFT JOIN 
    Books b ON a.author_id = b.author_id
LEFT JOIN 
    Orders o ON b.book_id = o.book_id
GROUP BY 
    a.author_id, a.first_name, a.last_name
ORDER BY 
    total_books_sold DESC;
-- Q.10 Find the top 3 customers who have spent the most money on books, showing their name and total spending.
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    SUM(o.total_amount) AS total_spent
FROM 
    Customers c
JOIN 
    Orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.first_name, c.last_name
ORDER BY 
    total_spent desc
LIMIT 3;


-- Q.11 List all books along with their authors' names where the book's price is above the average book price.
SELECT
b.title,
a.first_name,
a.last_name
FROM
books b
JOIN
authors a on b.author_id=a.author_id
WHERE
b.price>(select avg(price) from books);
   
-- Q.12 Show each genre and the number of books in that genre, but only for genres with more than 3 books.
SELECT 
    genre,
    COUNT(*) AS book_count
FROM 
    Books
GROUP BY 
    genre
HAVING 
    COUNT(*) > 3;

[Calvindefvfdvbg]

use calvin15;
-- Q.1 Create the Authors table with appropriate data types and constraints. Make author_id the primary key and ensure first_name and last_name cannot be null.
CREATE TABLE Authors6 (
    author_id INT PRIMARY KEY,
    first_name VARCHAR(70) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_year INT,
    nationality VARCHAR(50)
);
select * from Authors6;
-- Q.2 Create the Books table with:
-- A primary key for book_id
-- A foreign key referencing Authors table
-- A constraint ensuring price is greater than 0
-- A default value of 0 for stock

CREATE TABLE Books6 (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    publication_year INT,
    genre VARCHAR(50),
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INT DEFAULT 0,
    FOREIGN KEY (author_id) REFERENCES Authors6(author_id)
);
select * from Books6;
-- Q.3 Insert three authors into the Authors table with different nationalities.
INSERT INTO Authors6 (author_id, first_name, last_name, birth_year, nationality)
VALUES 
(1, 'Alice', 'Walker', 1944, 'American'),
(2, 'James', 'Smith', 1985, 'British'),
(3, 'Maria', 'Lopez', 1975, 'Spanish');
-- Q.4 Insert five books into the Books table, ensuring at least two books are by the same author.
INSERT INTO Books6 (book_id, title, author_id, publication_year, genre, price, stock)
VALUES 
(101, 'Learning Python', 1, 2020, 'Programming', 22.99, 10),
(102, 'Mastering Java', 1, 2021, 'Programming', 19.99, 5),
(103, 'Data Science Basics', 2, 2022, 'Science', 25.50, 7),
(104, 'Modern Art Explained', 3, 2019, 'Art', 17.75, 3),
(105, 'Java for Beginners', 2, 2023, 'Programming', 14.99, 0);
-- Q.5 Write a query to find all books priced between $15 and $25.
SELECT * FROM Books6
WHERE price BETWEEN 15 AND 25;
-- Q.6 Find all books with either "Python" or "Java" in their title.
SELECT * FROM Books6
WHERE title LIKE '%Python%' OR title LIKE '%Java%';
-- Q.7 List all authors who were born after 1980 AND are either American or British.
SELECT * FROM Authors6
WHERE birth_year > 1980
AND nationality IN ('American', 'British');
-- Q.8 Display all customers who joined in 2023 but haven't placed any orders.
CREATE TABLE Customers1 (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    join_date DATE NOT NULL
);
select * from customers1;
INSERT INTO Customers1 (customer_id, first_name, last_name, email, join_date)
VALUES 
(201, 'John', 'Doe', 'john.doe@example.com', '2023-01-10'),
(202, 'Emma', 'Clark', 'emma.clark@example.com', '2023-02-05'),
(203, 'Mike', 'Brown', 'mike.brown@example.com', '2022-11-25'),
(204, 'Lily', 'Adams', 'lily.adams@example.com', '2023-07-18'),
(205, 'David', 'Wang', 'david.wang@example.com', '2021-09-30');
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    book_id INT,
    order_date DATE NOT NULL,
    quantity INT CHECK (quantity > 0),
    total_amount DECIMAL(10, 2) CHECK (total_amount >= 0),
    FOREIGN KEY (customer_id) REFERENCES Customers1(customer_id),
    FOREIGN KEY (book_id) REFERENCES Books6(book_id)
);
select * from Orders;
INSERT INTO Orders (order_id, customer_id, book_id, order_date, quantity, total_amount)
VALUES 
(301, 201, 101, '2023-03-01', 1, 22.99),
(302, 203, 104, '2023-04-15', 2, 35.50),
(303, 205, 102, '2023-05-10', 1, 19.99),
(304, 201, 105, '2023-07-21', 1, 14.99),
(305, 205, 103, '2024-01-05', 1, 25.50);
SELECT * FROM Customers
WHERE YEAR(join_date) = 2023
AND customer_id NOT IN (
    SELECT DISTINCT customer_id FROM Orders
);
-- Q9.Show the total number of books sold for each author, including authors who haven't sold any books.
SELECT 
    a.author_id,
    a.first_name,
    a.last_name,
    COALESCE(SUM(o.quantity), 0) AS total_books_sold
FROM 
    Authors6 a
LEFT JOIN 
    Books6 b ON a.author_id = b.author_id
LEFT JOIN 
    Orders o ON b.book_id = o.book_id
GROUP BY 
    a.author_id, a.first_name, a.last_name
ORDER BY 
    total_books_sold DESC;
    
    -- Q.10 Find the top 3 customers who have spent the most money on books, showing their name and total spending.
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    SUM(o.total_amount) AS total_spent
FROM 
    Customers1 c
JOIN 
    Orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.first_name, c.last_name
ORDER BY 
    total_spent desc
LIMIT 3;

-- Q.11 List all books along with their authors' names where the book's price is above the average book price.
SELECT
b.title,
a.first_name,
a.last_name
FROM
books6 b
JOIN
authors6 a on b.author_id=a.author_id
WHERE
b.price>(select avg(price) from books6);

-- Q.12 Show each genre and the number of books in that genre, but only for genres with more than 3 books.
SELECT 
    genre,
    COUNT(*) AS book_count
FROM 
    Books6
GROUP BY 
    genre
HAVING 
    COUNT(*) > 3;
    

[MinalKulkarni07]

create database Book_Store;   -- creating a database to store the tables
use Book_Store; -- using this database for the following operations

-- Q1. 
-- creating Authors table to store Authors details
create table Authors(
Author_ID int primary key,       -- primary key to ensure, no two author have the same id.  
First_Name varchar(50) not null,    -- the purpose of not null here is,  
Last_Name varchar(50) not null,     -- that the the value should not be empty.
Birth_Year int,
Nationality varchar(50)
);
select * from Authors;

-- Q2.
-- creating Books table to store Books details
create table Books(
Book_ID int primary key,
Title varchar(50),
Author_ID int,
Publicaton_Year int,
Genre varchar(50),
Price decimal(10, 2) check(Price > 0),
Stock int default 0,
foreign key (Author_ID) references Authors(Author_ID) -- using foreign key to link Authors_ID to Authors table
);
select * from Books;

-- Q3.
-- inserting data in the table 
insert into Authors(Author_ID,First_Name,Last_Name,Birth_Year,Nationality)
Values
(1,'Daniel','Franklin',1988,'France'),
(2,'Amelia','Patric',1992,'Switzerland'),
(3,'Sudha','Murthy',1950,'India');
select * from Authors; -- selecting table to show table containts

-- Q4.
insert into Books(Book_ID,Title,Author_ID,Publicaton_Year,Genre,Price,Stock)
Values
(1011,'Hack Your Habits',2,2013,'Productivity',22.35,7),
(1012,'Life of Human',3,2020,'Philosophy',20.45,10),
(1013,'Brains at Work',1,2015,'Psychology',23.48,8),
(1014,'Minimalist Hustle',1,2017,'Self-help',25,12),
(1015,'Rain Between the Pages',2,2012,'Literary Fiction',17.83,10);

select * from Books;

-- Q5.
-- finding the books whose prices range between $15 and $25
select * from Books    -- "select" used to select columns from books table 
where Price between 15 and 25; 
-- "where" and "between" == filter the results to only include books to match given condition  

-- Q6.
-- finding books with either "Python" or "Java" in their titles
select * from Books where Title like '%Python%' or 'Java';

-- here in this table,"Python" or "Java" does not exist so, 
-- finding books with either "Human" or "Rain" in their titles
select * from Books where Title like '%Human%' or '%Rain%';
-- "like" == for pattern matching in "where" clauses


-- Q7.
-- listing authors who were born after 1980 and either American or British
select * from Authors where Birth_Year > 1980 and Nationality in ('Ameriva','Britain');

-- here in this table,American or British does not exist so, 
-- listing authors who were born after 1980 and either indian or french
select * from Authors where Birth_Year > 1980 and Nationality in ('India','France');
-- "and" used when all conditions must be true
-- "in" used to check if a values is equal to any value in a list

[Sahilgupta46]

-- Q1 
-- Create the Authors table with appropriate data types and constraints. Make author_id the primary key and ensure first_name and last_name cannot be null.

Create database Sahil03;
Use Sahil03;

Create table Authors (
Authors_Id int primary key, 
First_Name varchar(30) not null,
Last_Name varchar (30) not null,
Birth_Year int,
Nationality varchar (30)
);
select * from Authors;

alter table Authors add Birth_Year int;

-- Q2
-- creating Books table to store Books details.

Create table Books (
Book_Id int primary key,
Title varchar (30),
Authors_Id int,
Publication_Year int,
Genre varchar (30),
Price decimal (10,2) check (Price > 0),
Stock int default 0,
Foreign key (Authors_Id) references Authors (Authors_Id)
);
select * from Books;

-- Q3.
-- inserting data in the table.
insert into Authors (Authors_Id, First_Name, Last_Name, Nationality, Birth_Year)
Values
(1,'Ganesh','Saroj','France',1969),
(2,'Ritik','Gupta','London',1989),
(3,'Vijay','Verma','India',1947);

select * from Authors; 

-- Q4.
insert into Books (Book_Id, Title, Authors_Id, Publication_Year, Genre, Price, Stock)
Values
(1011, 'Hack Your Habits', 2, 2013, 'Productivity', 30, 7),
(1012, 'Life of Human', 3, 2020, 'Philosophy', 20, 10),
(1013, 'Brains at Work', 1, 2015, 'Psychology', 40, 8),
(1014, 'Minimalist Hustle', 1, 2017, 'Self-help', 25, 12),
(1015, 'Rain Between the Pages', 2, 2012, 'Literary Fiction', 50, 10);

select * from Books;

-- Q5
-- Write a query to find all books priced between $15 and $25.

select * from Books where Price between 15 and 25;
select * from Books where Stock between 5 and 10; 
select * from Books where Stock >5 and Stock <10;

-- Q6
-- finding books with either "Python" or "Java" in their titles.
select * from Books where Title like 'Python%' or 'Java';
-- here in this table,"Python" or "Java" does not exist so, 
-- finding books with either "Human" or "Rain" in their titles.
select * from Books where Title like '%Human%' or '%Rain%';

-- Q7
-- List all authors who were born after 1980 AND are either American or British.
select * from Authors where Birth_Year > 1980 and Nationality in ('American' , 'British');

-- here in this table,American or British does not exist so, 
-- listing authors who were born after 1980 and either indian or french
select * from Authors where Birth_Year > 1980 and Nationality in ('India','France');

-- Q8.
-- Display all customers who joined in 2023 but haven't placed any orders.

CREATE TABLE Customers (
    Customer_id INT PRIMARY KEY,
    First_name VARCHAR(30) ,
    Last_name VARCHAR(30) ,
    Email VARCHAR(30) UNIQUE ,
    Join_Date DATE 
);
select * from Customers;
Insert into Customers (Customer_id, First_name, Last_name, Email, Join_Date)
Values
(1, 'Ritik', 'Gupta', 'ritik.@gmail.com', '2023-01-10'),
(2, 'Vijay', 'Verma', 'vijay.@gmail.com', '2023-02-05'),
(3, 'Abhi', 'Gupta', 'abhi.@gmail.com', '2022-11-25'),
(4, 'Ganesh', 'Saroj', 'ganesh.@gmail.com', '2023-07-18'),
(5, 'Imran', 'Khan', 'imran.@gmail.com', '2021-09-30');

Create table Orders (
Order_Id int Primary key,
Customer_id int, 
Book_Id int, 
Quantity int check (Quantity > 0),
Total_Amount decimal(18,2) check (Total_Amount > 0),
Foreign key (Customer_id) References Customers (Customer_id),
Foreign key (Book_Id) References Books (Book_Id));

select * from Orders;
insert into Orders ( Order_Id, Customer_id, Book_Id, Quantity, Total_Amount)
values
(101, 1, 1011, 5, 55),
(102, 2, 1012, 4, 95),
(103, 3, 1013, 8, 50),
(104, 4, 1014, 6, 90),
(105, 5, 1015, 3, 30);

select * from Customers where Year(Join_Date) = 2023
and Customer_id not in (
	select Distinct Customer_id from Orders);