1.
As an administrator I should be able to create the table named Underwriter in Database
StarProtect to store the below details
UnderwriterId of type integer which will be autogenerated
Name of type varchar
DOB of type Date
JoiningDate of type Date
DefaultPassword – varchar
Solution –
CREATE TABLE StarProtect.Underwriter (
UnderwriterId INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
DOB DATE NOT NULL,
JoiningDate DATE NOT NULL,
DefaultPassword VARCHAR(255) NOT NULL
);
2. Write SQL Query to Insert at least 2 records into the table named Underwriter
Solution –
INSERT INTO StarProtect.Underwriter (Name, DOB, JoiningDate, DefaultPassword)
VALUES
('John Doe', '1985-05-15', '2023-01-10', 'Password123'),
('Jane Smith', '1990-08-22', '2023-02-15', 'Password456');
3. As an underWriter, I should be able to create a table named Insurance for storing Insurance
details for a Vehicle in Database StarProtect:
PolicyNo: AutoIncremented field integer value
VehicleNo: varchar with a space and Alphanumeric value
VehicleType: varchar (2-wheeler/4 -wheeler)
CustomerName: varchar
EngineNo: Integer
ChasisNo: Integer
PhoneNo: Integer consisting of 10 digits.
Type (Full Insurance/ThirdParty): varchar
PremiumAmt:. - Auto-calculated form type of insurance
FromDate: Date
ToDate: Autocalculated form FromDate + 365 days
UnderwriterId: Auto captured by the system with the UnderWriter Id
Solution –
CREATE TABLE StarProtect.Insurance (
PolicyNo INT AUTO_INCREMENT PRIMARY KEY,
VehicleNo VARCHAR(50) NOT NULL,
VehicleType VARCHAR(20) CHECK (VehicleType IN ('2-wheeler', '4-wheeler')),
CustomerName VARCHAR(255) NOT NULL,
EngineNo INT NOT NULL,
ChasisNo INT NOT NULL,
PhoneNo CHAR(10) NOT NULL,
Type VARCHAR(20) CHECK (Type IN ('Full Insurance', 'ThirdParty')),
PremiumAmt DECIMAL(10, 2) NOT NULL, -- Assuming it’s a monetary value
FromDate DATE NOT NULL,
ToDate DATE NOT NULL,
UnderwriterId INT NOT NULL,
FOREIGN KEY (UnderwriterId) REFERENCES StarProtect.Underwriter(UnderwriterId)
);
4. As an UnderWriter, write SQL query to insert the insurance details into the above created
table.
For each underWriter insert at least 2 records.
Solution –
INSERT INTO StarProtect.Insurance (VehicleNo, VehicleType, CustomerName, EngineNo, ChasisNo,
PhoneNo, Type, PremiumAmt, FromDate, ToDate, UnderwriterId)
VALUES
('ABC1234', '4-wheeler', 'Alice Johnson', 123456, 654321, '9876543210', 'Full Insurance', 5000.00,
'2024-01-01', DATE_ADD('2024-01-01', INTERVAL 365 DAY), 1),
('XYZ5678', '2-wheeler', 'Bob Smith', 223456, 754321, '8765432109', 'ThirdParty', 2000.00, '2024-
01-01', DATE_ADD('2024-01-01', INTERVAL 365 DAY), 1),
('LMN4321', '4-wheeler', 'Charlie Brown', 323456, 854321, '7654321098', 'Full Insurance', 6000.00,
'2024-01-10', DATE_ADD('2024-01-10', INTERVAL 365 DAY), 2),
('PQR9876', '2-wheeler', 'Diana Prince', 423456, 954321, '6543210987', 'ThirdParty', 2500.00,
'2024-01-10', DATE_ADD('2024-01-10', INTERVAL 365 DAY), 2);
5. Write a query in SQL to filter the below details by using the PolicyNo:
VehicleNo: varchar with a space and Alphanumeric value
VehicleType: varchar (2-wheeler/4 -wheeler)
CustomerName: varchar
EngineNo: Integer
ChasisNo: Integer
PhoneNo: Integer consisting of 10 digits.
Type (Full Insurance/ThirdParty): varchar
PremiumAmt:. - Auto-calculated form type of insurance
FromDate: Date
ToDate: Autocalculated form FromDate + 365 days
UnderwriterId: Auto captured by the system with the UnderWriter Id
Solution –
SELECT
VehicleNo,
VehicleType,
CustomerName,
EngineNo,
ChasisNo,
PhoneNo,
Type,
PremiumAmt,
FromDate,
ToDate,
UnderwriterId
FROM
StarProtect.Insurance
WHERE
PolicyNo = @PolicyNo; -- Replace @PolicyNo with the actual PolicyNo you want to filter by
6. As an administrator, write SQL query to view the number of vehicles registered by an
Underwriter with the below details mentioned below:
PolicyNo: AutoIncremented field integer value
VehicleNo: varchar with a space and Alphanumeric value
VehicleType: varchar (2-wheeler/4 -wheeler)
CustomerName: varchar
EngineNo: Integer
ChasisNo: Integer
PhoneNo: Integer consisting of 10 digits.
Type (Full Insurance/ThirdParty): varchar
PremiumAmt:. - Auto-calculated form type of insurance
FromDate: Date
ToDate: Autocalculated form FromDate + 365 days
UnderwriterId: Auto captured by the system with the UnderWriter Id
Solution –
SELECT
UnderwriterId,
COUNT(PolicyNo) AS NumberOfVehiclesRegistered
FROM
StarProtect.Insurance
GROUP BY
UnderwriterId;
7. Write a query in SQL to find the insurance details of vehicle whose to date field is a past
date i.e. insurance policy has expired.
Solution –
SELECT
PolicyNo,
VehicleNo,
VehicleType,
CustomerName,
EngineNo,
ChasisNo,
PhoneNo,
Type,
PremiumAmt,
FromDate,
ToDate,
UnderwriterId
FROM
StarProtect.Insurance
WHERE
ToDate < CURDATE(); -- Checks if ToDate is less than the current date
8. Write a query in SQL to delete the Underwriter by id.
Solution –
DELETE FROM StarProtect.Underwriter
WHERE UnderwriterId = @UnderwriterId; -- Replace @UnderwriterId with the actual UnderwriterId
to delete
