Scribd
Upload
3 views10 pages

SQL Queries for Customer and Policy Analysis

The document contains a SQL assignment with ten questions focused on retrieving and manipulating data from a database. Each question is accompanied by the corresponding SQL code to execute the required operations, such as selecting customers, listing policies, counting claims, and updating records. The assignment covers various SQL functionalities including SELECT, JOIN, GROUP BY, and UPDATE statements.

Uploaded by

re43634
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
3 views10 pages

SQL Queries for Customer and Policy Analysis

The document contains a SQL assignment with ten questions focused on retrieving and manipulating data from a database. Each question is accompanied by the corresponding SQL code to execute the required operations, such as selecting customers, listing policies, counting claims, and updating records. The assignment covers various SQL functionalities including SELECT, JOIN, GROUP BY, and UPDATE statements.

Uploaded by

re43634
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

SQL ASSIGNMENT

Question 1: Show all customers from 'Mumbai'

CODE :
SELECT * FROM Customers
WHERE City = 'Mumbai' ;
Question 2: List all active Health policies with a premium over ₹10,000

CODE:
SELECT * FROM Policies
WHERE PolicyType = 'Health'
AND PremiumAmount > 10000
AND IsActive = 'Yes';
Question 3: Display all claims where claim amount exceeds payout amount

CODE:
SELECT * FROM Claims
WHERE ClaimAmount > PayoutAmount;
Question 4: Count total claims per policy type

CODE:
SELECT [Link], COUNT([Link]) AS TotalClaims
FROM Claims c
JOIN Policies p ON [Link] = [Link]
GROUP BY [Link];
Question 5: List of customers who have more than one active policy

CODE:
SELECT [Link], [Link], COUNT([Link]) AS ActivePolicies
FROM Customers c
JOIN Policies p ON [Link] = [Link]
WHERE [Link] = 'Yes'
GROUP BY [Link], [Link]
HAVING COUNT([Link]) > 1;
Question 6: Get all customers who submitted claims but received no payout

CODE:
SELECT DISTINCT [Link], [Link]
FROM Customers cu
JOIN Policies p ON [Link] = [Link]
JOIN Claims c ON [Link] = [Link]
WHERE [Link] IS NULL OR [Link] = 0;
Question 7: Show top 3 cities with highest total claim amounts

CODE:
SELECT [Link], SUM([Link]) AS TotalClaimAmount
FROM Claims c
JOIN Policies p ON [Link] = [Link]
JOIN Customers cu ON [Link] = [Link]
GROUP BY [Link]
ORDER BY TotalClaimAmount DESC
LIMIT 3;
Question 8: Update claim status to 'Rejected' if payout is NULL and claim is older than
1 year

CODE:
UPDATE Claims
SET ClaimStatus = 'Rejected'
WHERE (PayoutAmount IS NULL OR PayoutAmount = 0)
AND ClaimDate < CURDATE() - INTERVAL 1 YEAR;
Question 9: Delete policies that are inactive and have no claims

CODE:
DELETE FROM Policies
WHERE IsActive = 'No'
AND PolicyID NOT IN (SELECT DISTINCT PolicyID FROM Claims);
Question 10: Generate a summary report displaying Customer Name, Policy Type, Total
Premium, Total Claims, Total Payout

CODE:
SELECT [Link], [Link], SUM([Link]) AS TotalPremium,
COUNT([Link]) AS TotalClaims,
SUM(IFNULL([Link], 0)) AS TotalPayout
FROM Customers cu
JOIN Policies p ON [Link] = [Link]
LEFT JOIN Claims c ON [Link] = [Link]
GROUP BY [Link], [Link];

THANK YOU
GOWTHAMI N
2412788
3BCOMIF B

You might also like