Databases Tasks for Practical Class 4

Selecting Data and Performing Operations Using Logical Operators in PostgreSQL

Variant 1: Online Bookstore Database

Scenario: Managing an online bookstore’s inventory, including books, authors, and categories.

Database Schema:

Table Name: books
Column Names and Data Types:
- book_id SERIAL PRIMARY KEY
- title VARCHAR(100)
- author_name VARCHAR(100)
- category VARCHAR(50)
- price DECIMAL(5,2)
- publication_year INTEGER
Data to Insert:

[
    ('The Lord of the Rings', 'J.R.R. Tolkien', 'Fantasy', 29.99, 1954),
    ('Pride and Prejudice', 'Jane Austen', 'Classic', 12.50, 1813),
    ('The Hitchhiker''s Guide to the Galaxy', 'Douglas Adams', 'Science Fiction', 15.75, 1979),
    ('To Kill a Mockingbird', 'Harper Lee', 'Classic', 14.20, 1960),
    ('Dune', 'Frank Herbert', 'Science Fiction', 22.00, 1965),
    ('1984', 'George Orwell', 'Dystopian', 11.99, 1949),
    ('Jane Eyre', 'Charlotte Brontë', 'Classic', 10.00, 1847),
    ('Foundation', 'Isaac Asimov', 'Science Fiction', 18.50, 1951),
    ('The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 13.00, 1925),
    ('Brave New World', 'Aldous Huxley', 'Dystopian', 16.00, 1932)
]

Practice Exercises:

Select books that are priced less than $15.00 and belong to the ‘Classic’ category.
Select books written by ‘Jane Austen’ or published after the year 1970.
Select books that are not in the ‘Science Fiction’ category.
Select books that are in the ‘Classic’ or ‘Dystopian’ categories AND priced greater than $12.00.
Select books published before 1960 and priced greater than $14.00.
Select books that are either ‘Science Fiction’ books priced under $20 OR ‘Classic’ books published before 1900.
Select books that are NOT (‘Fantasy’ OR ‘Dystopian’) and priced less than $15.

Variant 2: Employee Management Database

Scenario: Managing employee information within a company, including departments and salaries.

Database Schema:

Table Name: employees
Column Names and Data Types:
- employee_id SERIAL PRIMARY KEY
- first_name VARCHAR(50)
- last_name VARCHAR(50)
- department VARCHAR(50)
- salary INTEGER
- hire_year INTEGER
Data to Insert:

[
    ('Alice', 'Smith', 'Sales', 60000, 2020),
    ('Bob', 'Johnson', 'Marketing', 55000, 2021),
    ('Charlie', 'Brown', 'IT', 70000, 2019),
    ('Diana', 'Davis', 'Sales', 62000, 2022),
    ('Eve', 'Miller', 'HR', 58000, 2023),
    ('Frank', 'Wilson', 'IT', 75000, 2020),
    ('Grace', 'Moore', 'Marketing', 53000, 2018),
    ('Hank', 'Taylor', 'HR', 60000, 2022),
    ('Ivy', 'Anderson', 'Sales', 65000, 2019),
    ('Jack', 'Thomas', 'IT', 80000, 2021)
]

Practice Exercises:

Select employees who have a salary greater than $65000 and belong to the ‘IT’ department.
Select employees who belong to the ‘Sales’ department or were hired before 2021.
Select employees who are not in the ‘Marketing’ department.
Select employees who are in the ‘HR’ or ‘Sales’ departments AND have a salary less than $61000.
Select employees hired in 2020 or later with a salary of exactly $60000.
Select employees from ‘IT’ or ‘HR’ departments who were hired before 2022 AND have a salary greater than $55000.
Select employees who are NOT in the ‘Sales’ department and have a salary less than $70000.

Variant 3: Music Streaming Service Database

Scenario: Managing songs in a music streaming service, including genre and duration.

Database Schema:

Table Name: songs
Column Names and Data Types:
- song_id SERIAL PRIMARY KEY
- title VARCHAR(100)
- artist VARCHAR(100)
- genre VARCHAR(50)
- duration_seconds INTEGER
- release_year INTEGER
Data to Insert:

[
    ('Bohemian Rhapsody', 'Queen', 'Rock', 355, 1975),
    ('Imagine', 'John Lennon', 'Pop', 187, 1971),
    ('Stairway to Heaven', 'Led Zeppelin', 'Rock', 482, 1971),
    ('Like a Rolling Stone', 'Bob Dylan', 'Rock', 367, 1965),
    ('Hotel California', 'Eagles', 'Rock', 391, 1976),
    ('Billie Jean', 'Michael Jackson', 'Pop', 294, 1982),
    ('Smells Like Teen Spirit', 'Nirvana', 'Grunge', 301, 1991),
    ('Hey Jude', 'The Beatles', 'Pop', 431, 1968),
    ('Wonderwall', 'Oasis', 'Britpop', 258, 1995),
    ('Yesterday', 'The Beatles', 'Pop', 125, 1965)
]

Practice Exercises:

Select songs that have a duration longer than 300 seconds and belong to the ‘Rock’ genre.
Select songs by ‘The Beatles’ or released after 1980.
Select songs that are not in the ‘Pop’ genre.
Select songs that are in the ‘Rock’ or ‘Grunge’ genres AND have a duration less than 360 seconds.
Select songs released in the 1970s (between 1970 and 1979 inclusive) that are longer than 350 seconds.
Select songs that are either ‘Pop’ songs released before 1970 OR ‘Rock’ songs by ‘Queen’.
Select songs that are NOT (‘Pop’ OR ‘Britpop’) and have a duration less than 400 seconds.

Variant 4: Online Course Platform Database

Scenario: Managing courses on an online learning platform, including categories and credits.

Database Schema:

Table Name: courses
Column Names and Data Types:
- course_id SERIAL PRIMARY KEY
- course_name VARCHAR(100)
- instructor VARCHAR(100)
- category VARCHAR(50)
- credits INTEGER
- difficulty_level VARCHAR(50)
Data to Insert:

[
    ('Introduction to Python', 'Dr. Smith', 'Computer Science', 3, 'Beginner'),
    ('Calculus I', 'Prof. Jones', 'Mathematics', 4, 'Beginner'),
    ('Data Structures and Algorithms', 'Dr. Smith', 'Computer Science', 4, 'Intermediate'),
    ('Linear Algebra', 'Prof. Jones', 'Mathematics', 3, 'Intermediate'),
    ('Web Development Basics', 'Dr. Williams', 'Computer Science', 3, 'Beginner'),
    ('Organic Chemistry', 'Dr. Brown', 'Chemistry', 4, 'Advanced'),
    ('Probability and Statistics', 'Prof. Green', 'Mathematics', 4, 'Intermediate'),
    ('Advanced Algorithms', 'Dr. Smith', 'Computer Science', 4, 'Advanced'),
    ('Physical Chemistry', 'Dr. Brown', 'Chemistry', 4, 'Advanced'),
    ('Discrete Mathematics', 'Prof. Green', 'Mathematics', 3, 'Beginner')
]

Practice Exercises:

Select courses that have 4 credits and belong to the ‘Computer Science’ category.
Select courses taught by ‘Prof. Jones’ or with ‘Beginner’ difficulty level.
Select courses that are not in the ‘Mathematics’ category.
Select courses that are in the ‘Computer Science’ or ‘Chemistry’ categories AND have ‘Advanced’ difficulty level.
Select courses with 3 credits and ‘Beginner’ difficulty.
Select courses that are either ‘Mathematics’ courses with 4 credits OR ‘Computer Science’ courses taught by ‘Dr. Williams’.
Select courses that are NOT (‘Beginner’ OR ‘Intermediate’) difficulty and have 4 credits.

Variant 5: Hotel Booking Database

Scenario: Managing hotels and their information, including city and star rating.

Database Schema:

Table Name: hotels
Column Names and Data Types:
- hotel_id SERIAL PRIMARY KEY
- hotel_name VARCHAR(100)
- city VARCHAR(50)
- star_rating INTEGER
- price_per_night DECIMAL(6,2)
- availability INTEGER
Data to Insert:

[
    ('Grand Hotel', 'New York', 5, 350.00, 20),
    ('City Center Inn', 'London', 3, 180.50, 15),
    ('Ocean View Resort', 'Miami', 4, 250.75, 10),
    ('Mountain Lodge', 'Denver', 3, 150.20, 8),
    ('Sunset Suites', 'Los Angeles', 4, 280.90, 12),
    ('Royal Plaza', 'Paris', 5, 400.00, 5),
    ('Bay Breeze Hotel', 'San Francisco', 3, 200.00, 25),
    ('Alpine Retreat', 'Zurich', 4, 300.50, 18),
    ('Harbor Lights Inn', 'Boston', 3, 170.80, 30),
    ('Desert Oasis Hotel', 'Phoenix', 4, 230.40, 22)
]

Practice Exercises:

Select hotels in ‘New York’ with a star rating of 4 or higher.
Select hotels in ‘London’ or with a price per night less than $200.
Select hotels that are not located in ‘Paris’.
Select hotels in ‘Miami’ or ‘Los Angeles’ AND with a star rating of 4.
Select hotels with availability greater than 15 and a price per night less than $200.
Select hotels that are either in ‘San Francisco’ with availability over 20 OR in ‘Denver’ with a star rating of 3.
Select hotels that are NOT in ‘New York’ and have a price per night greater than $250.

Variant 6: Social Media Posts Database

Scenario: Managing social media posts, including author and hashtags.

Database Schema:

Table Name: posts
Column Names and Data Types:
- post_id SERIAL PRIMARY KEY
- author_name VARCHAR(50)
- content TEXT
- hashtags VARCHAR(100)
- likes INTEGER
- post_date DATE
Data to Insert:

[
    ('User1', 'Enjoying a sunny day! #sunshine #happy', '#sunshine #happy', 150, '2024-03-10'),
    ('User2', 'Just finished reading a great book. #reading #books', '#reading #books', 80, '2024-03-09'),
    ('User3', 'Exploring new coffee shops in town. #coffee #cafe', '#coffee #cafe', 120, '2024-03-08'),
    ('User1', 'Coding all night long. #coding #programming', '#coding #programming', 95, '2024-03-07'),
    ('User4', 'Delicious homemade pizza! #pizza #food', '#pizza #food', 200, '2024-03-06'),
    ('User2', 'Thinking about my next travel destination. #travel #vacation', '#travel #vacation', 70, '2024-03-05'),
    ('User5', 'Morning workout done! #fitness #gym', '#fitness #gym', 110, '2024-03-04'),
    ('User3', 'Attending a tech conference. #tech #conference', '#tech #conference', 130, '2024-03-03'),
    ('User4', 'Movie night at home. #movie #film', '#movie #film', 160, '2024-03-02'),
    ('User5', 'Enjoying the weekend vibes. #weekend #relax', '#weekend #relax', 180, '2024-03-01')
]

Practice Exercises:

Select posts with more than 100 likes and containing the hashtag ‘#coffee’.
Select posts by ‘User1’ or containing the hashtag ‘#travel’.
Select posts that do not contain the hashtag ‘#coding’.
Select posts containing the hashtags ‘#food’ or ‘#fitness’ AND with more than 140 likes.
Select posts from ‘2024-03-05’ or later with likes less than 100.
Select posts that are either by ‘User4’ and have more than 150 likes OR by ‘User3’ and contain the hashtag ‘#tech’.
Select posts that are NOT by ‘User2’ and have more than 100 likes.

Submission Instructions

Create a new Google Document. Example
Submit Your Google Doc Link: Google Sheet