1.
CREATE TABLE tbl_UserProfile ( UserID Number(20) PRIMARY KEY AUTO_INCREMENT,
FullName VARCHAR(255) NOT NULL, Rec_Address VARCHAR(255), Email VARCHAR(100)
UNIQUE NOT NULL, MobileNumber VARCHAR(15), Password VARCHAR(30) NOT NULL, Role
VARCHAR(50));
2. INSERT INTO tbl_UserProfile (FullName, Rec_Address, Email, MobileNumber, Password, Role)
VALUES
('Govinda Sharma', 'Shahdara Delhi', 'govinda@example.com', '8447654789',
'Govinda@123', 'Customer'),
('Mayank', 'Karkaduma Delhi', 'Mayank@example.com', '8765434521', 'Mayank@123',
'Admin'),
('Urvashi Sharma', 'New Delhi', 'Urvashi@example.com', '4578963214', 'Urvashi@123',
'Customer'),
('Mukul Nagar', 'Bangalore', 'Mukul213@example.com', '8965471236', 'Password@987',
'Customer'),
('Akash Sharma', 'Noida Uttar Pradesh', 'akash54@gmail.com', '8965471236', 'akash21@12',
'Customer');
3. SELECT
CASE
WHEN EXISTS (
SELECT 1
FROM tbl_UserProfile
WHERE UserID = 1
AND Password = 'Govinda@123'
) THEN TRUE
ELSE FALSE
END AS IsValid,
UserID AS UserName,
Password,
Role
FROM tbl_UserProfile
WHERE UserID = 1
AND Password = 'Govinda@123';
4. CREATE TABLE tbl_Booking (
Booking_ID VARCHAR(20) PRIMARY KEY,
User_ID Number(20) NOT NULL,
Rec_Name VARCHAR(255) NOT NULL,
Rec_Address VARCHAR(255) NOT NULL,
Rec_Pin VARCHAR(10) NOT NULL,
Rec_Mobile VARCHAR(15) NOT NULL,
Par_Weight_Gram INT NOT NULL,
Par_Contents_Description TEXT,
Par_Delivery_Type VARCHAR(50),
Par_Packing_Preference VARCHAR(50),
Par_PickupTime DATETIME,
Par_DropoffTime DATETIME,
Par_ServiceCost DECIMAL(10, 2),
Par_PaymentTime DATETIME,
Par_Status VARCHAR(20) NOT NULL,
FOREIGN KEY (User_ID) REFERENCES tbl_UserProfile(UserID)
);
5. SELECT
FullName AS Name,
Rec_Address AS Address,
MobileNumber AS ContactDetails
FROM tbl_UserProfile
WHERE UserID = '2';
6. INSERT INTO tbl_Booking (
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
)
VALUES (
3,
'RaghuRam Rajan',
'Janpath New Delhi',
'110051',
'8756932145',
960,
'Gaming Pc along with mouse',
'standard',
'secure',
'2025-03-05 12:05:20',
'2025-03-02 10:12:30',
2000,
'2025-03-05 10:20:56'
);
7. SELECT
Booking_ID,
Rec_Name AS 'Receiver Name',
Rec_Address AS 'Receiver Address',
Rec_Pin AS 'Receiver Pin',
Rec_Mobile AS 'Receiver Mobile',
Par_Weight_Gram AS 'Parcel Weight (g)',
Par_Contents_Description AS 'Parcel Contents Description',
Par_Delivery_Type AS 'Parcel Delivery Type',
Par_Packing_Preference AS 'Parcel Packing Preference',
Par_PickupTime AS 'Parcel Pickup Time',
Par_DropoffTime AS 'Parcel Dropoff Time',
Par_ServiceCost AS 'Parcel Service Cost',
Par_PaymentTime AS 'Parcel Payment Time'
FROM tbl_Booking
WHERE Booking_ID = ‘PARCEL-21654’;
8. SELECT
b.Booking_ID,
u.FullName,
u.Rec_Address AS Address,
b.Rec_Name,
b.Rec_Address,
b.Par_PickupTime AS 'Date of Booking',
b.Par_Status
FROM tbl_Booking b
JOIN tbl_UserProfile u ON b.User_ID = u.UserID
WHERE b.User_ID = 2
ORDER BY b.Par_PickupTime DESC
LIMIT 10;
9. SELECT
b.Booking_ID,
u.FullName,
u.Rec_Address AS Address,
b.Rec_Name,
b.Rec_Address,
b.Par_PickupTime AS 'Date of Booking',
b.Par_Status,
b.Par_PickupTime,
b.Par_DropoffTime
FROM tbl_Booking b
JOIN tbl_UserProfile u ON b.User_ID = u.UserID
WHERE b.Booking_ID = 'PARCEL-21654’;
10. UPDATE tbl_Booking
SET
Par_PickupTime = '2025-03-06 21:25:40',
Par_DropoffTime = '2025-03-09 22:25:50'
WHERE Booking_ID = 'PARCEL-21654';
11. SELECT
b.Booking_ID,
u.FullName,
u.Rec_Address AS Address,
b.Rec_Name,
b.Rec_Address,
b.Par_PickupTime AS 'Date of Booking',
b.Par_Status
FROM tbl_Booking b
JOIN tbl_UserProfile u ON b.User_ID = u.UserID
WHERE b.Booking_ID = 'PARCEL-21654';
12. UPDATE tbl_Booking
SET Par_Status = 'Out For Delivery’
WHERE Booking_ID = 'PARCEL-21654';
13. SELECT
u.UserID AS 'Customer ID',
b.Booking_ID,
b.Par_PickupTime AS 'Booking Date',
b.Rec_Name AS 'Receiver Name',
b.Rec_Address AS 'Delivered Address',
b.Par_ServiceCost AS 'Amount',
b.Par_Status AS 'Status'
FROM tbl_Booking b
JOIN tbl_UserProfile u ON b.User_ID = u.UserID
ORDER BY b.Par_PickupTime DESC;
14. SELECT
u.UserID AS 'Customer ID',
b.Booking_ID,
b.Par_PickupTime AS 'Booking Date',
b.Rec_Name AS 'Receiver Name',
b.Rec_Address AS 'Delivered Address',
b.Par_ServiceCost AS 'Amount',
b.Par_Status AS 'Status'
FROM tbl_Booking b
JOIN tbl_UserProfile u ON b.User_ID = u.UserID
WHERE u.UserID = 2
AND b.Par_PickupTime BETWEEN '2025-03-10' AND '2025-03-12'
ORDER BY b.Par_PickupTime DESC;
