LLO 2.1 Create Database schema for given application

1. How to Start Working with DB Browser for SQLite

Step 1: Download & Install

Go to https://sqlitebrowser.org/
Download the version suitable for your OS (Windows, macOS, Linux)
Install the software following the installer instructions

Step 2: Create or Open a Database

To create a new database:
- Open DB Browser for SQLite
- Click “New Database” button
- Choose a location & give a file name (e.g., mydatabase.db)
- Click Save
To open an existing database:
- Click “Open Database” button
- Browse to your .db or .sqlite file and open it

Step 3: Create Tables and Insert Data

After creating/opening a database:
- Go to the “Execute SQL” tab
- Write your SQL commands (like CREATE TABLE, INSERT INTO, etc.)
- Click “Execute All” to run the SQL commands
Or use the “Database Structure” tab to create tables via UI (click “Create Table” button)

2. How to View Tables

Viewing Tables and Data

Switch to the “Database Structure” tab:
- You’ll see a list of all tables in the left pane
- Click on a table name to select it
Then go to the “Browse Data” tab:
- Select the table you want to view from the dropdown list at the top
- The data inside the selected table will be displayed in a spreadsheet-like grid
- You can edit the data here if needed (and save changes)

Extra Tips

To run queries, always use the “Execute SQL” tab
After running a query like SELECT * FROM tablename;, results will appear below
Use “Write Changes” button after making any edits to save changes to the database file
Use “Rollback Changes” to undo unsaved changes

Create Database for the given application
Create tables for the given application
Assign Primary key for created table
Modify the table as per application needs

Step-by-step Guide using DB Browser for SQLite

1) Create Database for the given application

Steps:

Open DB Browser for SQLite.
Click File > New Database.
Choose the location to save your database file and provide a file name (e.g., engineering_app.db).
Click Save.
The database is now created and open in DB Browser.

2) Create tables for the given application

Let's say the application is Student Management System for engineering students with two tables:

Students (StudentID, Name, Branch, Year, Email)
Courses (CourseID, CourseName, Credits)

Steps:

Go to Database Structure tab.
Click New Table.
Enter the table name (e.g., Students).
Add columns with data types:
- StudentID - INTEGER
- Name - TEXT
- Branch - TEXT
- Year - INTEGER
- Email - TEXT
Click Add Field for each column.
Click OK to create the table.
Repeat for Courses table with:
- CourseID - INTEGER
- CourseName - TEXT
- Credits - INTEGER

3) Assign Primary Key for created table

Steps:

When adding columns, select the column (e.g., StudentID) and tick the PK checkbox to assign it as the primary key.
For the Courses table, assign CourseID as Primary Key the same way.
If tables already created without PK, you will have to modify the table (discussed in Step 4) because SQLite does not allow direct primary key addition after creation.

4) Modify the table as per the application needs

Let's say you want to add a column PhoneNumber to Students and change data type of Email (although SQLite is flexible, just to simulate modification).

Steps:

Go to Execute SQL tab.
Use the SQL command to add a column:

ALTER TABLE Students ADD COLUMN PhoneNumber TEXT;

To modify columns (SQLite does not support direct MODIFY COLUMN), you need to:
- Create a new temporary table with desired structure,
- Copy data,
- Drop old table,
- Rename new table.

Example SQL to modify Email data type (simulate by recreating table):

-- Step 1: Create a new table with modified structure
CREATE TABLE Students_new (
  StudentID INTEGER PRIMARY KEY,
  Name TEXT,
  Branch TEXT,
  Year INTEGER,
  Email VARCHAR(255),
  PhoneNumber TEXT
);

-- Step 2: Copy data from old table
INSERT INTO Students_new (StudentID, Name, Branch, Year, Email)
SELECT StudentID, Name, Branch, Year, Email FROM Students;

-- Step 3: Drop old table
DROP TABLE Students;

-- Step 4: Rename new table
ALTER TABLE Students_new RENAME TO Students;

Complete SQL Code Summary

-- Create Students table with Primary Key
CREATE TABLE Students (
  StudentID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Branch TEXT NOT NULL,
  Year INTEGER NOT NULL,
  Email TEXT
);

-- Create Courses table with Primary Key
CREATE TABLE Courses (
  CourseID INTEGER PRIMARY KEY,
  CourseName TEXT NOT NULL,
  Credits INTEGER NOT NULL
);

-- Add PhoneNumber column to Students
ALTER TABLE Students ADD COLUMN PhoneNumber TEXT;

-- Modify Students table to change Email column data type (simulate by recreate)
CREATE TABLE Students_new (
  StudentID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Branch TEXT NOT NULL,
  Year INTEGER NOT NULL,
  Email VARCHAR(255),
  PhoneNumber TEXT
);

INSERT INTO Students_new (StudentID, Name, Branch, Year, Email, PhoneNumber)
SELECT StudentID, Name, Branch, Year, Email, PhoneNumber FROM Students;

DROP TABLE Students;

ALTER TABLE Students_new RENAME TO Students;

Summary:

Use DB Browser to visually create and design database and tables.
Always assign Primary Key when creating tables.
Use ALTER TABLE to add columns.
To modify column types, recreate the table with the required structure.

Practice Code:

Ready-to-Execute SQL Script

-- 1. Create Students table with Primary Key
CREATE TABLE IF NOT EXISTS Students (
  StudentID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Branch TEXT NOT NULL,
  Year INTEGER NOT NULL,
  Email TEXT
);

-- 2. Create Courses table with Primary Key
CREATE TABLE IF NOT EXISTS Courses (
  CourseID INTEGER PRIMARY KEY,
  CourseName TEXT NOT NULL,
  Credits INTEGER NOT NULL
);

-- 3. Add PhoneNumber column to Students table
ALTER TABLE Students ADD COLUMN PhoneNumber TEXT;

-- 4. Modify Students table to change Email column data type
-- (SQLite does not support MODIFY COLUMN directly,
-- so we recreate the table with the new structure)

-- Step 4.1: Create a new Students_new table with Email column as VARCHAR(255)
CREATE TABLE IF NOT EXISTS Students_new (
  StudentID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Branch TEXT NOT NULL,
  Year INTEGER NOT NULL,
  Email VARCHAR(255),
  PhoneNumber TEXT
);

-- Step 4.2: Copy all data from old Students table to Students_new
INSERT INTO Students_new (StudentID, Name, Branch, Year, Email, PhoneNumber)
SELECT StudentID, Name, Branch, Year, Email, PhoneNumber FROM Students;

-- Step 4.3: Drop the old Students table
DROP TABLE Students;

-- Step 4.4: Rename Students_new table to Students
ALTER TABLE Students_new RENAME TO Students;

Practice SQL Script: Library Management System

Tables:

Books (BookID, Title, Author, YearPublished)
Members (MemberID, Name, Email, Phone)
BorrowRecords (RecordID, BookID, MemberID, BorrowDate, ReturnDate)

Ready-to-Execute SQL

-- Create Books table
CREATE TABLE IF NOT EXISTS Books (
  BookID INTEGER PRIMARY KEY,
  Title TEXT NOT NULL,
  Author TEXT NOT NULL,
  YearPublished INTEGER
);

-- Create Members table
CREATE TABLE IF NOT EXISTS Members (
  MemberID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Email TEXT UNIQUE,
  Phone TEXT
);

-- Create BorrowRecords table with Foreign Keys
CREATE TABLE IF NOT EXISTS BorrowRecords (
  RecordID INTEGER PRIMARY KEY,
  BookID INTEGER NOT NULL,
  MemberID INTEGER NOT NULL,
  BorrowDate TEXT NOT NULL,
  ReturnDate TEXT,
  FOREIGN KEY (BookID) REFERENCES Books(BookID),
  FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);

-- Add a new column to Members table
ALTER TABLE Members ADD COLUMN MembershipDate TEXT;

-- Insert sample data into Books
INSERT INTO Books (Title, Author, YearPublished) VALUES
('Engineering Mathematics', 'B.S. Grewal', 2012),
('Data Structures and Algorithms', 'Narasimha Karumanchi', 2015),
('Database System Concepts', 'Silberschatz', 2016);

-- Insert sample data into Members
INSERT INTO Members (Name, Email, Phone, MembershipDate) VALUES
('Rajesh Kumar', 'rajesh@example.com', '9876543210', '2023-01-10'),
('Anita Sharma', 'anita@example.com', '9123456780', '2023-02-15');

-- Insert sample BorrowRecords
INSERT INTO BorrowRecords (BookID, MemberID, BorrowDate, ReturnDate) VALUES
(1, 1, '2023-07-01', '2023-07-15'),
(2, 2, '2023-07-05', NULL);

What you can do next:

Query all books borrowed by a particular member.
Update the ReturnDate when a book is returned.
Delete a record when a book is lost or membership is cancelled.
Add more columns, like FineAmount or BookCategory.
Practice joins using the above tables.

Practice SQL Script: Employee Management System

Tables:

Employees (EmployeeID, Name, Department, Salary, JoiningDate)
Departments (DepartmentID, DepartmentName, Location)
Projects (ProjectID, ProjectName, DepartmentID, StartDate, EndDate)

Ready-to-Execute SQL Code

-- Create Departments table
CREATE TABLE IF NOT EXISTS Departments (
  DepartmentID INTEGER PRIMARY KEY,
  DepartmentName TEXT NOT NULL,
  Location TEXT NOT NULL
);

-- Create Employees table
CREATE TABLE IF NOT EXISTS Employees (
  EmployeeID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Department INTEGER NOT NULL,
  Salary REAL NOT NULL,
  JoiningDate TEXT NOT NULL,
  FOREIGN KEY (Department) REFERENCES Departments(DepartmentID)
);

-- Create Projects table
CREATE TABLE IF NOT EXISTS Projects (
  ProjectID INTEGER PRIMARY KEY,
  ProjectName TEXT NOT NULL,
  DepartmentID INTEGER NOT NULL,
  StartDate TEXT NOT NULL,
  EndDate TEXT,
  FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Insert sample data into Departments
INSERT INTO Departments (DepartmentName, Location) VALUES
('Computer Science', 'New Delhi'),
('Electrical', 'Mumbai'),
('Mechanical', 'Bangalore');

-- Insert sample data into Employees
INSERT INTO Employees (Name, Department, Salary, JoiningDate) VALUES
('Amit Singh', 1, 60000, '2021-05-15'),
('Neha Verma', 2, 55000, '2020-11-10'),
('Rohit Sharma', 1, 70000, '2022-01-20');

-- Insert sample data into Projects
INSERT INTO Projects (ProjectName, DepartmentID, StartDate, EndDate) VALUES
('AI Research', 1, '2023-01-01', NULL),
('Smart Grid', 2, '2022-06-15', '2023-06-15'),
('Robotics Development', 3, '2023-03-10', NULL);

Ideas to practice further:

Write a query to find all employees working in the Computer Science department.
Write a query to get all ongoing projects (where EndDate is NULL).
Update the salary of an employee.
Delete a project that has ended.
Add a new column Email to Employees and update emails.

Practice SQL Script: College Examination System

Tables:

Students (StudentID, Name, Branch, Year, Email)
Subjects (SubjectID, SubjectName, Branch, Credits)
Exams (ExamID, SubjectID, ExamDate)
Results (ResultID, StudentID, ExamID, MarksObtained, Grade)

Ready-to-Execute SQL Script

-- Create Students table
CREATE TABLE IF NOT EXISTS Students (
  StudentID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Branch TEXT NOT NULL,
  Year INTEGER NOT NULL,
  Email TEXT UNIQUE
);

-- Create Subjects table
CREATE TABLE IF NOT EXISTS Subjects (
  SubjectID INTEGER PRIMARY KEY,
  SubjectName TEXT NOT NULL,
  Branch TEXT NOT NULL,
  Credits INTEGER NOT NULL
);

-- Create Exams table
CREATE TABLE IF NOT EXISTS Exams (
  ExamID INTEGER PRIMARY KEY,
  SubjectID INTEGER NOT NULL,
  ExamDate TEXT NOT NULL,
  FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID)
);

-- Create Results table
CREATE TABLE IF NOT EXISTS Results (
  ResultID INTEGER PRIMARY KEY,
  StudentID INTEGER NOT NULL,
  ExamID INTEGER NOT NULL,
  MarksObtained INTEGER NOT NULL CHECK(MarksObtained BETWEEN 0 AND 100),
  Grade TEXT,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
  FOREIGN KEY (ExamID) REFERENCES Exams(ExamID)
);

-- Insert sample data into Students
INSERT INTO Students (Name, Branch, Year, Email) VALUES
('Rahul Verma', 'Computer Science', 3, 'rahul.verma@example.com'),
('Sneha Gupta', 'Electrical', 2, 'sneha.gupta@example.com');

-- Insert sample data into Subjects
INSERT INTO Subjects (SubjectName, Branch, Credits) VALUES
('Data Structures', 'Computer Science', 4),
('Circuit Analysis', 'Electrical', 3);

-- Insert sample data into Exams
INSERT INTO Exams (SubjectID, ExamDate) VALUES
(1, '2023-07-20'),
(2, '2023-07-22');

-- Insert sample data into Results
INSERT INTO Results (StudentID, ExamID, MarksObtained, Grade) VALUES
(1, 1, 85, 'A'),
(2, 2, 78, 'B');

Practice Ideas:

Query to find all exams taken by a student.
Calculate average marks for a subject.
Update grades based on marks.
Delete a student record and see cascading effects.
Add a column for Remarks in Results table.

Practice SQL Script: Hospital Management System

Tables:

Patients (PatientID, Name, Age, Gender, Contact)
Doctors (DoctorID, Name, Specialization, Phone)
Appointments (AppointmentID, PatientID, DoctorID, AppointmentDate, Status)
Prescriptions (PrescriptionID, AppointmentID, Medicine, Dosage, Duration)

Ready-to-Execute SQL Code

-- Create Patients table
CREATE TABLE IF NOT EXISTS Patients (
  PatientID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Age INTEGER NOT NULL,
  Gender TEXT CHECK(Gender IN ('Male', 'Female', 'Other')),
  Contact TEXT
);

-- Create Doctors table
CREATE TABLE IF NOT EXISTS Doctors (
  DoctorID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Specialization TEXT NOT NULL,
  Phone TEXT
);

-- Create Appointments table
CREATE TABLE IF NOT EXISTS Appointments (
  AppointmentID INTEGER PRIMARY KEY,
  PatientID INTEGER NOT NULL,
  DoctorID INTEGER NOT NULL,
  AppointmentDate TEXT NOT NULL,
  Status TEXT CHECK(Status IN ('Scheduled', 'Completed', 'Cancelled')),
  FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
  FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);

-- Create Prescriptions table
CREATE TABLE IF NOT EXISTS Prescriptions (
  PrescriptionID INTEGER PRIMARY KEY,
  AppointmentID INTEGER NOT NULL,
  Medicine TEXT NOT NULL,
  Dosage TEXT NOT NULL,
  Duration TEXT NOT NULL,
  FOREIGN KEY (AppointmentID) REFERENCES Appointments(AppointmentID)
);

-- Insert sample data into Patients
INSERT INTO Patients (Name, Age, Gender, Contact) VALUES
('Anil Kumar', 45, 'Male', '9876543210'),
('Sita Devi', 32, 'Female', '9123456789');

-- Insert sample data into Doctors
INSERT INTO Doctors (Name, Specialization, Phone) VALUES
('Dr. Mehta', 'Cardiology', '9988776655'),
('Dr. Singh', 'Neurology', '9876541230');

-- Insert sample data into Appointments
INSERT INTO Appointments (PatientID, DoctorID, AppointmentDate, Status) VALUES
(1, 1, '2023-07-25 10:00', 'Scheduled'),
(2, 2, '2023-07-26 14:00', 'Completed');

-- Insert sample data into Prescriptions
INSERT INTO Prescriptions (AppointmentID, Medicine, Dosage, Duration) VALUES
(2, 'Paracetamol', '500mg twice daily', '5 days');

Practice ideas for you:

Query all upcoming appointments for a doctor.
Update appointment status when completed.
Delete a patient and cascade delete related appointments.
Add a new column for patient email.
Write a query to show prescriptions given during completed appointments.

Practice SQL Script: Vehicle Rental System

Tables:

Customers (CustomerID, Name, Contact, Email)
Vehicles (VehicleID, Model, Type, RentalPricePerDay)
Rentals (RentalID, CustomerID, VehicleID, RentalStartDate, RentalEndDate, TotalAmount)

Ready-to-Execute SQL Script

-- Create Customers table
CREATE TABLE IF NOT EXISTS Customers (
  CustomerID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Contact TEXT,
  Email TEXT UNIQUE
);

-- Create Vehicles table
CREATE TABLE IF NOT EXISTS Vehicles (
  VehicleID INTEGER PRIMARY KEY,
  Model TEXT NOT NULL,
  Type TEXT NOT NULL,
  RentalPricePerDay REAL NOT NULL
);

-- Create Rentals table
CREATE TABLE IF NOT EXISTS Rentals (
  RentalID INTEGER PRIMARY KEY,
  CustomerID INTEGER NOT NULL,
  VehicleID INTEGER NOT NULL,
  RentalStartDate TEXT NOT NULL,
  RentalEndDate TEXT NOT NULL,
  TotalAmount REAL NOT NULL,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
  FOREIGN KEY (VehicleID) REFERENCES Vehicles(VehicleID)
);

-- Insert sample data into Customers
INSERT INTO Customers (Name, Contact, Email) VALUES
('Aman Singh', '9876543210', 'aman.singh@example.com'),
('Priya Sharma', '9123456780', 'priya.sharma@example.com');

-- Insert sample data into Vehicles
INSERT INTO Vehicles (Model, Type, RentalPricePerDay) VALUES
('Honda City', 'Car', 1500),
('Royal Enfield', 'Bike', 800),
('Suzuki Swift', 'Car', 1200);

-- Insert sample data into Rentals
INSERT INTO Rentals (CustomerID, VehicleID, RentalStartDate, RentalEndDate, TotalAmount) VALUES
(1, 1, '2023-07-01', '2023-07-05', 7500),
(2, 2, '2023-07-03', '2023-07-04', 1600);

Practice Suggestions:

Query to find all rentals by a particular customer.
Calculate the total amount for rentals longer than 3 days.
Update rental end date and recalculate the total amount.
Add a column for PaymentStatus to Rentals table.
Delete a vehicle and check what happens to rentals.

Practice SQL Script: University Hostel Management System

Tables:

Students (StudentID, Name, Branch, Year, Phone)
Hostels (HostelID, HostelName, Location, TotalRooms)
Rooms (RoomID, HostelID, RoomNumber, Capacity)
Allocations (AllocationID, StudentID, RoomID, AllocationDate, LeavingDate)

Ready-to-Execute SQL Script

-- Create Students table
CREATE TABLE IF NOT EXISTS Students (
  StudentID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Branch TEXT NOT NULL,
  Year INTEGER NOT NULL,
  Phone TEXT
);

-- Create Hostels table
CREATE TABLE IF NOT EXISTS Hostels (
  HostelID INTEGER PRIMARY KEY,
  HostelName TEXT NOT NULL,
  Location TEXT NOT NULL,
  TotalRooms INTEGER NOT NULL
);

-- Create Rooms table
CREATE TABLE IF NOT EXISTS Rooms (
  RoomID INTEGER PRIMARY KEY,
  HostelID INTEGER NOT NULL,
  RoomNumber TEXT NOT NULL,
  Capacity INTEGER NOT NULL,
  FOREIGN KEY (HostelID) REFERENCES Hostels(HostelID)
);

-- Create Allocations table
CREATE TABLE IF NOT EXISTS Allocations (
  AllocationID INTEGER PRIMARY KEY,
  StudentID INTEGER NOT NULL,
  RoomID INTEGER NOT NULL,
  AllocationDate TEXT NOT NULL,
  LeavingDate TEXT,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
  FOREIGN KEY (RoomID) REFERENCES Rooms(RoomID)
);

-- Insert sample data into Students
INSERT INTO Students (Name, Branch, Year, Phone) VALUES
('Aakash Patel', 'Mechanical', 2, '9876543210'),
('Divya Nair', 'Civil', 3, '9123456789');

-- Insert sample data into Hostels
INSERT INTO Hostels (HostelName, Location, TotalRooms) VALUES
('Azad Hostel', 'North Campus', 100),
('Tagore Hostel', 'South Campus', 80);

-- Insert sample data into Rooms
INSERT INTO Rooms (HostelID, RoomNumber, Capacity) VALUES
(1, 'A101', 2),
(1, 'A102', 3),
(2, 'B201', 2);

-- Insert sample data into Allocations
INSERT INTO Allocations (StudentID, RoomID, AllocationDate, LeavingDate) VALUES
(1, 1, '2023-06-01', NULL),
(2, 3, '2023-06-10', '2023-07-10');

Practice ideas:

Query all students allocated in a particular hostel.
Find rooms that are fully occupied.
Update the leaving date when a student vacates.
Add a column FeesPaid to Allocations table.
Delete allocation records of students who left.

Practice SQL Script: Online Shopping System

Tables:

Customers (CustomerID, Name, Email, Phone)
Products (ProductID, ProductName, Price, StockQuantity)
Orders (OrderID, CustomerID, OrderDate, TotalAmount)
OrderDetails (OrderDetailID, OrderID, ProductID, Quantity, Price)

Ready-to-Execute SQL Script

-- Create Customers table
CREATE TABLE IF NOT EXISTS Customers (
  CustomerID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Email TEXT UNIQUE,
  Phone TEXT
);

-- Create Products table
CREATE TABLE IF NOT EXISTS Products (
  ProductID INTEGER PRIMARY KEY,
  ProductName TEXT NOT NULL,
  Price REAL NOT NULL,
  StockQuantity INTEGER NOT NULL
);

-- Create Orders table
CREATE TABLE IF NOT EXISTS Orders (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER NOT NULL,
  OrderDate TEXT NOT NULL,
  TotalAmount REAL NOT NULL,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Create OrderDetails table
CREATE TABLE IF NOT EXISTS OrderDetails (
  OrderDetailID INTEGER PRIMARY KEY,
  OrderID INTEGER NOT NULL,
  ProductID INTEGER NOT NULL,
  Quantity INTEGER NOT NULL,
  Price REAL NOT NULL,
  FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert sample data into Customers
INSERT INTO Customers (Name, Email, Phone) VALUES
('Karan Mehta', 'karan.mehta@example.com', '9876543210'),
('Anjali Singh', 'anjali.singh@example.com', '9123456780');

-- Insert sample data into Products
INSERT INTO Products (ProductName, Price, StockQuantity) VALUES
('Laptop', 50000, 10),
('Smartphone', 15000, 25),
('Headphones', 2000, 50);

-- Insert sample data into Orders
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount) VALUES
(1, '2023-07-20', 52000),
(2, '2023-07-21', 15000);

-- Insert sample data into OrderDetails
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES
(1, 1, 1, 50000),
(1, 3, 1, 2000),
(2, 2, 1, 15000);

Practice ideas:

Query total orders placed by each customer.
Find products that are out of stock.
Update stock quantity after an order is placed.
Calculate total sales for a product.
Delete an order and its order details.

Practice SQL Script: Restaurant Management System

Tables:

Customers (CustomerID, Name, Phone, Email)
MenuItems (MenuItemID, ItemName, Price, Category)
Orders (OrderID, CustomerID, OrderDate, TotalAmount)
OrderDetails (OrderDetailID, OrderID, MenuItemID, Quantity, Price)

Ready-to-Execute SQL Script

-- Create Customers table
CREATE TABLE IF NOT EXISTS Customers (
  CustomerID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Phone TEXT,
  Email TEXT UNIQUE
);

-- Create MenuItems table
CREATE TABLE IF NOT EXISTS MenuItems (
  MenuItemID INTEGER PRIMARY KEY,
  ItemName TEXT NOT NULL,
  Price REAL NOT NULL,
  Category TEXT NOT NULL
);

-- Create Orders table
CREATE TABLE IF NOT EXISTS Orders (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER NOT NULL,
  OrderDate TEXT NOT NULL,
  TotalAmount REAL NOT NULL,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Create OrderDetails table
CREATE TABLE IF NOT EXISTS OrderDetails (
  OrderDetailID INTEGER PRIMARY KEY,
  OrderID INTEGER NOT NULL,
  MenuItemID INTEGER NOT NULL,
  Quantity INTEGER NOT NULL,
  Price REAL NOT NULL,
  FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
  FOREIGN KEY (MenuItemID) REFERENCES MenuItems(MenuItemID)
);

-- Insert sample data into Customers
INSERT INTO Customers (Name, Phone, Email) VALUES
('Suresh Kumar', '9876543210', 'suresh.kumar@example.com'),
('Anita Joshi', '9123456780', 'anita.joshi@example.com');

-- Insert sample data into MenuItems
INSERT INTO MenuItems (ItemName, Price, Category) VALUES
('Paneer Butter Masala', 250, 'Main Course'),
('Garlic Naan', 50, 'Bread'),
('Gulab Jamun', 100, 'Dessert');

-- Insert sample data into Orders
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount) VALUES
(1, '2023-07-22', 350),
(2, '2023-07-22', 150);

-- Insert sample data into OrderDetails
INSERT INTO OrderDetails (OrderID, MenuItemID, Quantity, Price) VALUES
(1, 1, 1, 250),
(1, 2, 2, 100),
(2, 3, 1, 100),
(2, 2, 1, 50);

Practice ideas:

Query orders made by a specific customer.
Calculate total sales for each menu category.
Update price of a menu item.
Delete an order and verify related order details.
Add a column for special instructions in OrderDetails.

LLO 2.1 Create Database schema for given application

1. How to Start Working with DB Browser for SQLite

Step 1: Download & Install

Step 2: Create or Open a Database

Step 3: Create Tables and Insert Data

2. How to View Tables

Viewing Tables and Data

Extra Tips

Step-by-step Guide using DB Browser for SQLite

1) Create Database for the given application

2) Create tables for the given application

3) Assign Primary Key for created table

4) Modify the table as per the application needs

Complete SQL Code Summary

Summary:

Ready-to-Execute SQL Script

Practice SQL Script: Library Management System

Tables:

Ready-to-Execute SQL

What you can do next:

Practice SQL Script: Employee Management System

Tables:

Ready-to-Execute SQL Code

Ideas to practice further:

Practice SQL Script: College Examination System

Tables:

Ready-to-Execute SQL Script

Practice Ideas:

Practice SQL Script: Hospital Management System

Tables:

Ready-to-Execute SQL Code

Practice ideas for you:

Practice SQL Script: Vehicle Rental System

Tables:

Ready-to-Execute SQL Script

Practice Suggestions:

Practice SQL Script: University Hostel Management System

Tables:

Ready-to-Execute SQL Script

Practice ideas:

Practice SQL Script: Online Shopping System

Tables:

Ready-to-Execute SQL Script

Practice ideas:

Practice SQL Script: Restaurant Management System

Tables:

Ready-to-Execute SQL Script

Practice ideas:

Popular posts from this blog

Technology and Innovation by Atul Kalukhe