Creating queries using the GROUP BY and ORDER BY clauses. Creating queries with the HAVING clause.

Variant 1: Online Bookstore Sales

Schema:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    book_title VARCHAR(255),
    genre VARCHAR(255),
    quantity INTEGER,
    price_per_unit NUMERIC(10, 2)
);

INSERT INTO orders (order_id, customer_id, order_date, book_title, genre, quantity, price_per_unit) VALUES
(1, 101, '2024-01-15', 'The Mystery of the Lost Key', 'Mystery', 2, 15.99),
(2, 102, '2024-01-20', 'Space Odyssey 2045', 'Science Fiction', 1, 24.99),
(3, 101, '2024-02-05', 'Cooking with Passion', 'Cookbook', 3, 12.50),
(4, 103, '2024-02-10', 'The Mystery of the Lost Key', 'Mystery', 1, 15.99),
(5, 102, '2024-02-15', 'Learn Python in 30 Days', 'Programming', 2, 19.99),
(6, 104, '2024-02-20', 'Space Odyssey 2045', 'Science Fiction', 1, 24.99),
(7, 101, '2024-03-01', 'The History of Rome', 'History', 1, 9.99),
(8, 103, '2024-03-05', 'Cooking with Passion', 'Cookbook', 2, 12.50),
(9, 105, '2024-03-10', 'Quantum Physics for Beginners', 'Science', 1, 29.99),
(10, 104, '2024-03-12', 'Business Strategy Masterclass', 'Business', 1, 39.99);

Tasks:

  1. List all the unique genres in the orders table, ordered alphabetically.
  2. List all book titles that have the word “the” (case-insensitive) in them, ordered by title.
  3. List the genres from the orders table which have titles containing the word “of”, ordered alphabetically.
  4. List all book titles, ordered by book title DESC.
  5. Find genres containing letter ‘e’, considering only books with price_per_unit higher than 16.00. Order result by genre.
  6. List all distinct book titles, ordered by the length of the title (shortest to longest).
  7. List all genres that end with the letter “e”, ordered alphabetically.
  8. Show all book titles that contain the letter “a” and the letter “s”, ordered by title.
  9. List genres where the book titles contain digits, ordered alphabetically.
  10. List book titles that start with ‘The’ and contain ‘Mystery’, ordered by price_per_unit DESC.
Variant 2: University Course Enrollment

Schema:

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    course_name VARCHAR(255),
    department VARCHAR(255),
    credits INTEGER,
    grade CHAR(1)
);
INSERT INTO courses (course_id, course_name, department, credits, grade) VALUES
(101, 'Introduction to Programming', 'Computer Science', 3, 'A'),
(102, 'Calculus I', 'Mathematics', 4, 'B'),
(103, 'World History', 'History', 3, 'A'),
(104, 'Linear Algebra', 'Mathematics', 3, 'C'),
(105, 'Data Structures and Algorithms', 'Computer Science', 4, 'A'),
(106, 'Probability and Statistics', 'Statistics', 3, 'B'),
(107, 'Introduction to Programming', 'Computer Science', 3, 'B');

Tasks:

  1. List all the unique departments in the courses table, ordered alphabetically.
  2. List the course names that contain the word “and”, ordered by course name.
  3. List the departments that offer courses with names containing “Pro”, ordered alphabetically.
  4. List the course names with a grade of ‘A’, ordering by name DESC.
  5. Find course names that end with ‘s’, considering only courses with credits equal to 3. Order result alphabetically.
  6. List all distinct department names, ordered by the length of the department name (longest to shortest).
  7. List departments that start with the letter “C”, ordered alphabetically.
  8. Show course names containing the letter ‘i’ at least twice, ordered by course name.
  9. List departments where course names include word ‘to’, ordered alphabetically.
  10. Find course names starting with ‘I’ and having grade ‘B’, order by credits.
Variant 3: Employee Project Management

Schema:

CREATE TABLE projects (
    project_id INTEGER PRIMARY KEY,
    project_name VARCHAR(255),
    department VARCHAR(255),
    start_date DATE,
    end_date DATE,
    budget NUMERIC(12, 2)
);
INSERT INTO projects (project_id, project_name, department, start_date, end_date, budget) VALUES
(101, 'Website Redesign', 'IT', '2024-01-15', '2024-03-15', 50000.00),
(102, 'New Marketing Campaign', 'Marketing', '2024-02-01', '2024-04-30', 75000.00),
(103, 'Database Migration', 'IT', '2024-03-01', '2024-05-31', 100000.00),
(104, 'Financial Reporting System', 'Finance', '2024-01-01', '2024-06-30', 120000.00),
(105, 'New Database Design', 'IT', '2024-04-01', '2024-06-01', 90000.00);

Tasks:

  1. List all unique departments in the projects table, ordered in descending alphabetical order.
  2. List the project names that start with “New”, ordered by project name.
  3. List departments that have projects with names containing “Data”.
  4. List project names, ordering by project name.
  5. List the project names that have a budget greater than 60000, and that end with ‘n’, ordering the result alphabetically.
  6. List all distinct department names, ordered by the length of the department name (shortest to longest).
  7. List project names that contain the word “New” or the word “System”, ordered by project name.
  8. Show departments having projects which names contain letter ‘b’ and letter ‘e’, ordered alphabetically.
  9. List departments where project names contain space, ordered alphabetically.
  10. List project names where start date is later than ‘2024-02-01’ and name contains ‘Data’, ordering by budget DESC.
Variant 4: Music Streaming Service

Schema:

CREATE TABLE songs (
    song_id INTEGER PRIMARY KEY,
    song_name VARCHAR(255),
    artist_name VARCHAR(255),
    genre VARCHAR(255),
    album_name VARCHAR(255),
    duration_seconds INTEGER
);

INSERT INTO songs (song_id, song_name, artist_name, genre, album_name, duration_seconds) VALUES
(1, 'Come Together', 'The Beatles', 'Rock', 'Abbey Road', 259),
(2, 'Something', 'The Beatles', 'Rock', 'Abbey Road', 182),
(3, 'So What', 'Miles Davis', 'Jazz', 'Kind of Blue', 556),
(4, 'Blue in Green', 'Miles Davis', 'Jazz', 'Kind of Blue', 337),
(5, 'Stairway to Heaven', 'Led Zeppelin', 'Rock', 'Led Zeppelin IV', 482),
(6, 'Black Dog', 'Led Zeppelin', 'Rock', 'Led Zeppelin IV', 296),
(7, 'Blank Space', 'Taylor Swift', 'Pop', '1989', 231),
(8, 'Shake It Off', 'Taylor Swift', 'Pop', '1989', 219),
(9, 'Alright', 'Kendrick Lamar', 'Hip Hop', 'To Pimp a Butterfly', 219),
(10, 'King Kunta', 'Kendrick Lamar', 'Hip Hop', 'To Pimp a Butterfly', 234);

Tasks:

  1. List all the unique genres from the songs table, ordered alphabetically.
  2. List all album names, ordered by album name in descending order.
  3. List the genres of songs with names containing “Blue”.
  4. List song names that have artist name ‘The Beatles’, ordering by song name DESC.
  5. Find the album names containing the word “of”, ordered alphabetically.
  6. List all distinct artist names, ordered by the length of the artist name (shortest to longest).
  7. List genres that contain the letter “o” and the letter “p”, ordered alphabetically.
  8. Show song names which contain character ‘k’ at least twice, ordered by the song name.
  9. List artist names where song name contains ‘So’, ordered alphabetically.
  10. List song names which have duration_seconds greater than 250 and contain word ‘Black’, order by duration_seconds.
Variant 5: Library Management System

Schema:

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title VARCHAR(255),
    author VARCHAR(255),
    genre VARCHAR(255),
    publication_year INTEGER,
  	loaned BOOLEAN
);
INSERT INTO books (book_id, title, author, genre, publication_year, loaned) VALUES
(1, 'The Lord of the Rings', 'J.R.R. Tolkien', 'Fantasy', 1954, TRUE),
(2, 'Pride and Prejudice', 'Jane Austen', 'Romance', 1813, TRUE),
(3, '1984', 'George Orwell', 'Dystopian', 1949, TRUE),
(4, 'To Kill a Mockingbird', 'Harper Lee', 'Fiction', 1960, FALSE),
(5, 'The Catcher in the Rye', 'J.D. Salinger', 'Fiction', 1951, TRUE);

Tasks:

  1. List unique genres from the books table in descending alphabetical order.
  2. List the book titles published before 1950, ordered by publication year.
  3. List the genres of books with titles containing the word “the”, ordered alphabetically.
  4. List author names, ordering by author name.
  5. List all books titles that are currently loaned (loaned is TRUE), and title ends with ‘e’, ordering by title.
  6. List all distinct author names, ordered by the length of the author name (longest to shortest).
  7. List genres that start with the letter “F” or the letter “R”, ordered alphabetically.
  8. Show book titles that contain the letter “a” and are currently loaned, ordered by title.
  9. List genres of books, which titles contain ‘and’, ordered by genre.
  10. List titles of books published after 1950 and with author name starting with ‘J’, order by publication year.
Variant 6: E-commerce Product Catalog

Schema:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(255),
    category_name VARCHAR(255),
    price NUMERIC(10, 2),
    stock_quantity INTEGER
);
INSERT INTO products (product_id, product_name, category_name, price, stock_quantity) VALUES
(1, 'Laptop', 'Electronics', 1200.00, 50),
(2, 'T-Shirt', 'Clothing', 25.00, 200),
(3, 'The Hitchhiker''s Guide to the Galaxy', 'Books', 15.00, 100),
(4, 'Coffee Maker', 'Home & Kitchen', 75.00, 75),
(5, 'Smartphone', 'Electronics', 800.00, 100),
(6, 'Jeans', 'Clothing', 50.00, 150),
(7, 'A Brief History of Time', 'Books', 20.00, 80),
(8, 'Blender', 'Home & Kitchen', 100.00, 60);

Tasks:

  1. List all unique category names from the products table in descending alphabetical order.
  2. List product names that have a price greater than $50, ordering by product name.
  3. List the category names that have products with names containing “of”.
  4. List product names from category ‘Electronics’, ordering by product name DESC.
  5. Find product names that have price less than 1000 and containing the word ‘the’, ordered by name.
  6. List all distinct category names, ordered by the length of the category name (shortest to longest).
  7. List category names containing ‘c’ and ‘n’ ordered alphabetically.
  8. List all product names that start with the letter ‘B’, ordered by product name.
  9. List category names where product names contain character ‘p’ at least twice, ordered by category name.
  10. Find product names with stock_quantity higher than 80 and containing ‘er’, ordered by price.

Submission Instructions

Playful GIF