Table Of Contents

• Create Practice Database
• Questions
• Answers

Creating and Using a Practice Database

1. Create a Database

Use the following query to create a practice database. You can replace PracticeDB with any name of your choice.

CREATE DATABASE PracticeDB;

2. Select the Database

After creating the database, switch to it by using the following query. Make sure to use the same database name you specified earlier.

USE PracticeDB;

1. Library Management

Create a table to store information about books in a library.

Books Table

• BookID (Primary Key)
• Title
• Author
• PublishedYear (Ensure PublishedYear is not null)
• Genre

View Query

2. Employee Management

Create a table to store employee details for a company.

Employees Table

• EmployeeID (Primary Key)
• Name
• Age (Age should always be greater than 18)
• Department
• Salary (Salary should not be negative) View Query

3. Online Courses Platform

Create tables for an online course platform to store courses and enrollments.

Courses Table

• CourseID (Primary Key)
• CourseName
• Instructor
• Price

Enrollments Table

• EnrollmentID (Primary Key)
• StudentName
• CourseID (Foreign Key references Courses.CourseID)

View Queries

4. E-Commerce

Create a database table to track product inventory.

Products Table

• ProductID (Primary Key)
• ProductName
• Category
• Quantity (Ensure Quantity is always greater than or equal to 0)
• Price

View Query

5. Social Media Platform

Design tables for a basic social media platform. Requirements:

Users Table

• UserID (Primary Key)
• Username
• Email (Unique)
• JoinDate

Posts Table

• PostID (Primary Key)
• Content
• UserID (Foreign Key references Users.UserID)
• PostDate

View Queries

6. Airline Reservations

Create tables to handle flight and passenger information for an airline reservation system.

Flights Table

• FlightID (Primary Key)
• Source
• Destination
• DepartureTime
• ArrivalTime

Passengers Table

• PassengerID (Primary Key)
• Name
• FlightID (Foreign Key references Flights.FlightID)
• SeatNumber

View Queries

7. School Database

Create tables for a school database.

Students Table:

• StudentID (Primary Key)
• Name
• GradeLevel
• DateOfBirth

Subjects Table

• SubjectID (Primary Key)
• SubjectName

Enrollments Table

• StudentID (Foreign Key references Students.StudentID)
• SubjectID (Foreign Key references Subjects.SubjectID)
• Composite Primary Key: StudentID and SubjectID

View Queries

8. Bank System

Create a table to manage customer accounts in a bank.

Accounts Table

• AccountID (Primary Key)
• CustomerName
• AccountType (Savings or Current)
• Balance (Ensure Balance is always non-negative)

View Query

9. Movie Database

Design tables for storing movie and rating details.

Movies Table

• MovieID (Primary Key)
• Title
• Director
• ReleaseYear

Ratings Table

• RatingID (Primary Key)
• MovieID (Foreign Key references Movies.MovieID)
• UserID
• Rating (between 1 and 5)

View Queries

10. Hospital Management

Create tables for managing patient and doctor information.

Doctors Table

• DoctorID (Primary Key)
• Name
• Specialization

Patients Table

• PatientID (Primary Key)
• Name
• Age
• DoctorID (Foreign Key references Doctors.DoctorID)

View Queries

Answers

1. Library Management

CREATE TABLE books(
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(100),
    PublishedYear YEAR NOT NULL,
    Genre VARCHAR (100)
);

2. Employee Management

CREATE TABLE employees(
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT CHECK (Age > 18),
    Department VARCHAR(100),
    Salary INT
);

3. Online Courses Platform

CREATE TABLE courses(
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Instructor VARCHAR(100),
    Price LONG
);

CREATE TABLE enrollments(
    EnrollmentID INT PRIMARY KEY,
    StudentName VARCHAR(100),
    CourseID INT REFERENCES courses(CourseID)
);

4. E-Commerce

CREATE TABLE products(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Category VARCHAR(100),
    Quantity INT CHECK (Quantity >= 0),
    Price INT
);

5. Social Media Platform

CREATE TABLE users(
    UserID INT PRIMARY KEY,
    Username VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    JoinDate DATE
);

CREATE TABLE posts(
    PostID INT PRIMARY KEY,
    Content VARCHAR(255),
    UserID INT REFERENCES users(UserID),
    PostDate DATE
);

6. Airline Reservations

CREATE TABLE flights(
    FlightID INT PRIMARY KEY,
    Source VARCHAR(100),
    Destination VARCHAR(100),
    DepartureTime DATE,
    ArrivalTime DATE
);

CREATE TABLE passengers(
    PassengerID INT PRIMARY KEY,
    Name VARCHAR(100),
    FlightID INT REFERENCES flights(FlightID),
    SeatNumber INT
);

7. Students Table

CREATE TABLE students(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    GradeLevel VARCHAR(100),
    DateOfBirth DATE
);

CREATE TABLE subjects(
    SubjectID INT PRIMARY KEY,
    SubjectName VARCHAR(100)
);

CREATE TABLE enrollments_2(
    StudentID INT REFERENCES students(StudentID),
    SubjectID INT REFERENCES subjects(SubjectID),
    PRIMARY KEY(StudentID, SubjectID)
);

8. Bank System

CREATE TABLE accounts(
    AccountID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    AccountType VARCHAR(10) CHECK (AccountType IN ('Savings', 'Current')),
    Balance INT CHECK (Balance >= 0) 
);

9. Movie Database

CREATE TABLE movies(
    MovieID INT PRIMARY KEY,
    Title VARCHAR(100),
    Director VARCHAR(100),
    ReleaseYear DATE
);

CREATE TABLE ratings(
    RatingID INT PRIMARY KEY,
    MovieID INT REFERENCES movies(MovieID),
    UserID INT, 
    Rating INT CHECK(Rating BETWEEN 1 AND 5)
);

10. Hospital Management

CREATE TABLE doctors(
    DoctorID INT PRIMARY KEY,
    Name VARCHAR(100),
    Specialization VARCHAR(100)
);

CREATE TABLE patients(
    PatientID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    DoctorID INT REFERENCES doctors(DoctorID)
);

You can contribute to this blog post on GitHub by adding more examples or improving the ones that are already there.