Scribd
Upload
59 views5 pages

PMS SQL

The document outlines SQL commands for managing a database related to a parcel management system. It includes creating a database and tables for users and booking information, inserting dummy data, and executing various queries for user management, booking details, and status updates. Additionally, it describes user stories that detail the functionalities required for developers and officers within the system.

Uploaded by

yashharode9890
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
59 views5 pages

PMS SQL

The document outlines SQL commands for managing a database related to a parcel management system. It includes creating a database and tables for users and booking information, inserting dummy data, and executing various queries for user management, booking details, and status updates. Additionally, it describes user stories that detail the functionalities required for developers and officers within the system.

Uploaded by

yashharode9890
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

-- Show Databases

show databases;

-- Create Database
create database if not exists PMS_Database;

-- Use Database
use PMS_Database;

-- Story 1: As a developer, I want to create tables so that I can store user details.
create table Users(
User_Id int(20) primary key check(User_id>=0 and Length(User_id)>=5 and
Length(User_id)<=20),
FullName varchar(100) not null,
Address varchar(255) not null,
Email varchar(255) unique not null,
MobileNumber varchar(20) not null check(length(MObileNumber)=10),
Password varchar(255) NOT NULL,
Role varchar(50) NOT NULL CHECK(Role IN ('Officer','User'))
);

-- drop table users;

-- See Table schema


select * from Users;

-- Story 2: As a developer I should be able to add user and store user data to the table
-- insert into Users values(?,?,?,?,?,?,?);

-- Dummy values
insert into Users values(10001,"Yash
Harode","Indore","yash@gmail.com",8877994455,"Yash@123","user"),
(10002,"Vrdhan","Indore","vardhan@gmail.com",8877994455,"Vardhan@123","user"),
(10003,"Mohit","Indore","mohit@gmail.com",8877994455,"Mohit@123","user"),
(10004,"Yash","Indore","yashc@gmail.com",8877994455,"Yashc@123","user"),
(10005,"Krisnanshu","Indore","kris@gmail.com",8877994455,"Kris@123","user"),
(10006,"Dipak","Indore","dipak@gmail.com",8877994455,"Dipak@123","user"),
(10007,"Rohit","Indore","rohit@gmail.com",8877994455,"Rohit@123","user"),
(10008,"Ayushman","Indore","ayushman@gmail.com",8877994455,"Ayushman@123","user"),
(10000,"Anubhav","Indore","anubhav@gmail.com",8877994455,"Anubhav@123","Officer")
;
-- Story 3: As a developer I should be able to login based on the roles. Input ID and Password.
-- select User_Id, Password, Role from Users where User_Id=? and password=?;
select User_Id, Password, Role from Users where User_Id=10000 and
password="Anubhav@123";

-- Story 4: As a developer I need to create a table to store the booking information


create table Booking_Information(
Booking_ID int primary key check(length(Booking_ID)=6),
User_ID int,
Rec_Name varchar(100) NOT NULL,
Rec_Address varchar(255) NOT NULL,
Rec_Pin int NOT NULL check(length(Rec_Pin)=6),
Rec_Mobile varchar(20) not null check(length(Rec_Mobile)=10),
Par_Weight_Gram int not null check(Par_Weight_Gram>0),
Par_Contents_Description varchar(255) not null,
Par_Delivery_Type varchar(50) not null check(Par_Delivery_Type in ('standard','express')),
Par_Packing_Preference varchar(50) not null check(Par_Packing_Preference in ('Standard
Packaging', 'Custom Packaging', 'Eco-friendly Packaging', 'Fragile Item handling')) ,
Par_PickupTime datetime ,
Par_DropoffTime datetime ,
Par_ServiceCost int not null check(Par_ServiceCost>0),
Par_PaymentTime datetime default current_timestamp(),
Par_Status varchar(50) null check(Par_Status in ('Picked up','In Transit','Delivered','Returned')),
foreign key (User_Id) references Users(User_Id)
);

select * from booking_information;

-- Story 5: As a developer I should be able to view the necessary details of user based on
userID
select FullName, Address, Email, MobileNumber from Users where User_id=1;

-- Storry 6: As a developer I need to store the booking information to the corresponding table
-- insert into Booking_Information (Booking_ID,User_ID ,Rec_Name ,Rec_Address ,Rec_Pin
,Rec_Mobile ,Par_Weight_Gram ,Par_Contents_Description ,Par_Delivery_Type
,Par_Packing_Preference ,Par_PickupTime ,Par_DropoffTime ,Par_ServiceCost
,Par_PaymentTime ,Par_Status) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);

-- Dummy Data
insert into Booking_Information (Booking_ID,User_ID ,Rec_Name ,Rec_Address ,Rec_Pin
,Rec_Mobile ,Par_Weight_Gram ,Par_Contents_Description ,Par_Delivery_Type
,Par_Packing_Preference ,Par_PickupTime ,Par_DropoffTime ,Par_ServiceCost
,Par_PaymentTime ,Par_Status) values
(100011,10001,"Sagar","Pandhurna",480334,"7788778844",500,"Youtube Play
Button","standard","Custom Packaging","2025-02-21 06:30:30","2025-02-23
06:30:30",2000,current_timestamp(),"Picked up"),
(100012,10001,"Chinmay","Pune",454545,"7788778844",5999,"Spaces","express","Standard
Packaging","2025-02-24 06:30:30","2025-02-24
06:30:30",2500,current_timestamp(),"Delivered"),
(100021,10002,"Nilesh","Lahore",303003,"7788778844",822,"Protien","express","Eco-friendly
Packaging","2025-02-23 06:30:30","2025-02-23 06:30:30",2000,current_timestamp(),"Picked
up"),
(100031,10003,"Amitesh","Bhopal",789456,"7788778844",520,"Dumbell","standard","Custom
Packaging","2025-02-22 06:30:30","2025-02-22 06:30:30",2000,current_timestamp(),"Picked
up"),
(100041,10004,"Pavan","Pandhurna",480334,"7788778844",1020,"Massage
Oil","standard","Fragile Item handling","2025-02-22 06:30:30","2025-02-22
06:30:30",2000,current_timestamp(),"Picked up")
;

truncate table booking_information;


-- Stroy 7: As a developer I should be able to view the invoice for the booking done
-- select
Booking_ID,Rec_Name,Rec_Address,Rec_Pin,Rec_Mobile,Par_Weight_Gram,Par_Contents_D
escription,Par_Delivery_Type,Par_Packing_Preference,Par_PickupTime,Par_DropoffTime,Par_
ServiceCost,Par_PaymentTime from Booking_Information where booking_id=?;
select
Booking_ID,Rec_Name,Rec_Address,Rec_Pin,Rec_Mobile,Par_Weight_Gram,Par_Contents_D
escription,Par_Delivery_Type,Par_Packing_Preference,Par_PickupTime,Par_DropoffTime,Par_
ServiceCost,Par_PaymentTime from Booking_Information where booking_id=100011;

-- Story 8: As a developer I need to view the latest ten bookings done by the customer sorted in
descending order of booking
-- Select Booking_ID,u.Fullname,u.Address, Rec_Name, b.Rec_Address,
Date(Par_PaymentTime) as Date_of_Booking,Par_Status from booking_information as b Inner
Join Users as u on u.User_Id=b.User_ID where u.user_Id=? order by Date(Par_PaymentTime)
desc limit 10;
Select Booking_ID,u.Fullname,u.Address, Rec_Name, b.Rec_Address,
Date(Par_PaymentTime) as Date_of_Booking,Par_Status from booking_information as b Inner
Join Users as u on u.User_Id=b.User_ID where u.user_Id=10001 order by
Date(Par_PaymentTime) desc limit 10;

-- Story 9: As an officer I should be able to search the booking details based on Booking ID
-- select Booking_ID, u.FullName, u.Address, Rec_Name, Rec_Address, Date
(Par_PaymentTime) as Date_Of_Booking, Par_Status, Par_PickupTime,Par_DropoffTime from
booking_information as b Inner Join Users as u on u.User_Id=b.User_ID where Booking_Id=?;
select Booking_ID, u.FullName, u.Address, Rec_Name, Rec_Address, Date
(Par_PaymentTime) as Date_Of_Booking, Par_Status, Par_PickupTime,Par_DropoffTime from
booking_information as b Inner Join Users as u on u.User_Id=b.User_ID where
Booking_Id=100021;

-- Story 10: As an officer I should be able to update the Pick and Drop Date time based on
booking ID
-- update booking_Information set Par_PickupTime=? where Booking_Id=?;
update booking_Information set Par_PickupTime="2025-02-28 06:30:30" where
Booking_Id=100011;

-- update booking_Information set Par_DropoffTime=? where Booking_Id=?;


update booking_Information set Par_DropoffTime="2025-03-01 06:30:30" where
Booking_Id=100012;

-- Stroy 11: "Prepare SQL Query to retrieve the delivery status of the Parcel based on the
Booking ID and display the below items
-- select Booking_ID, u.FullName, u.Address, Rec_Name, Rec_Address, Date
(Par_PaymentTime) as Date_Of_Booking, Par_Status from booking_information as b Inner Join
Users as u on u.User_Id=b.User_ID where Booking_Id=?;
select Booking_ID, u.FullName, u.Address, Rec_Name, Rec_Address, Date
(Par_PaymentTime) as Date_Of_Booking, Par_Status from booking_information as b Inner Join
Users as u on u.User_Id=b.User_ID where Booking_Id=100012;

-- story 12: As an officer I should be able to update the Delivery Status of the Parcel
-- update booking_Information set Par_Status=? where Booking_Id=?;
update booking_Information set Par_Status="Delivered" where Booking_Id=100012;

-- Story 13: As a Customer, I should be able to view the booking details of specific user
-- select u.User_Id,Booking_ID, u.FullName, Date (Par_PaymentTime) as Booking_Date ,
Rec_Name, Rec_Address, Par_ServiceCost, Par_Status from booking_information as b Inner
Join Users as u on u.User_Id=b.User_ID where u.User_Id=?;
select u.User_Id,Booking_ID, u.FullName, Date (Par_PaymentTime) as Booking_Date ,
Rec_Name, Rec_Address, Par_ServiceCost, Par_Status from booking_information as b Inner
Join Users as u on u.User_Id=b.User_ID where u.User_Id=10001;

-- Story 14: As an officer I should be able to view the booking history of a particular user with in
date range in descending order of the Booking Date
-- "Prepare and SQL query to retrieve the below booking history information
-- Based on the Customer ID and Date Range"
-- select User_ID,Booking_ID,Date(Par_PaymentTime) as Booking_Date, Rec_Name,
Rec_Address, Par_ServiceCost, Par_Status from Booking_Information where User_ID=? and
Date(Par_PaymentTime) between ? and ? order by Date(Par_PaymentTime) desc;
select User_ID,Booking_ID,Date(Par_PaymentTime) as Booking_Date, Rec_Name,
Rec_Address, Par_ServiceCost, Par_Status from Booking_Information where User_ID=10001
and Date(Par_PaymentTime) between "2025-02-25" and "2025-02-28" order by
Date(Par_PaymentTime) desc;

You might also like