Creating the Tables
Salesmen Table
CREATE TABLE Salesmen (
SNUM INT PRIMARY KEY,
SNAME VARCHAR(50),
CITY VARCHAR(50),
COMMISSION DECIMAL(4, 2)
);
INSERT INTO Salesmen (SNUM, SNAME, CITY, COMMISSION) VALUES
(1001, 'Piyush', 'London', 12.00),
(1002, 'Sejal', 'Surat', 13.00),
(1003, 'Anand', 'New Delhi', 10.00),
(1004, 'Miti', 'London', 11.00),
(1007, 'Rajesh', 'Baroda', 15.00);
Customers Table
CREATE TABLE Customers (
CNUM INT PRIMARY KEY,
CNAME VARCHAR(50),
CITY VARCHAR(50),
RATING INT,
SNUM INT,
FOREIGN KEY (SNUM) REFERENCES Salesmen(SNUM)
);
INSERT INTO Customers (CNUM, CNAME, CITY, RATING, SNUM) VALUES
(2001, 'Harsh', 'London', 100, 1001),
(2002, 'Gita', 'Rome', 200, 1003),
(2003, 'Lalit', 'Surat', 300, 1002),
(2004, 'Govind', 'Bombay', 300, 1002),
(2006, 'Chirag', 'London', 100, 1001),
(2007, 'Chinmay', 'Surat', 100, 1004),
(2008, 'Pratik', 'Rome', 200, 1007);
Orders Table
CREATE TABLE Orders (
ONUM INT PRIMARY KEY,
AMOUNT DECIMAL(10, 2),
ODATE DATE,
CNUM INT,
SNUM INT,
FOREIGN KEY (CNUM) REFERENCES Customers(CNUM),
FOREIGN KEY (SNUM) REFERENCES Salesmen(SNUM)
);
INSERT INTO Orders (ONUM, AMOUNT, ODATE, CNUM, SNUM) VALUES
(3001, 18.69, '1997-03-10', 2008, 1007),
(3002, 767.19, '1997-03-10', 2001, 1001),
(3005, 1900.10, '1997-10-04', 2007, 1004),
(3006, 5160.45, '1997-10-04', 2003, 1002),
(3008, 1098.16, '1997-10-06', 2004, 1002),
(3009, 1713.23, '1997-10-04', 2006, 1001);
Queries
1. Summarize information about customers linked to salesman number 1001.
SELECT * FROM Customers WHERE SNUM = 1001;
Output:
CNUM CNAME CITY RATING SNUM
2001 Harsh London 100 1001
2006 Chirag London 100 1001
2. Extract a list of unique salesman numbers from the order table while eliminating
duplicates.
SELECT DISTINCT SNUM FROM Orders;
Output:
SNUM
1001
1002
1004
1007
3. Union of two queries to show names, cities and rating of all customers. Those with
rating >= 200 should display 'HIGH RATING'.
SELECT CNAME, CITY, RATING FROM Customers
UNION
SELECT CNAME, CITY, 'HIGH RATING' AS RATING FROM Customers WHERE
RATING >= 200;
Output:
CNAME CITY RATING
Harsh London 100
Gita Rome HIGH RATING
Lalit Surat HIGH RATING
Govind Bombay HIGH RATING
Chirag London 100
Chinmay Surat 100
Pratik Rome HIGH RATING
4. Find all orders attributed to salesman in 'London'.
SELECT * FROM Orders WHERE SNUM IN (SELECT SNUM FROM Salesmen WHERE
CITY = 'London');
Output:
ONUM AMOUNT ODATE CNUM SNUM
3002 767.19 1997-03-10 2001 1001
3009 1713.23 1997-10-04 2006 1001
5. Assess the impact of retaining duplicates between 'cnum' of customers and 'onum' of
orders on the overall data analysis.
To assess the impact of retaining duplicates between the 'CNUM' (Customer Number) of
customers and 'ONUM' (Order Number) of orders on the overall data analysis, we need to
consider the potential effects on various types of analyses. Let's break it down into key points:
Key Points of Impact
1. Accuracy of Summaries and Aggregations:
○ Duplicates in 'CNUM' could lead to overcounting the number of unique
customers.
○ Duplicates in 'ONUM' could result in overestimating the total number of orders or
the total order amounts.
2. Misleading Averages and Totals:
○ Averages, sums, and other aggregate measures could be skewed by the
presence of duplicates.
○ For example, average order values might be incorrectly calculated if duplicate
orders are counted multiple times.
3. Data Integrity Issues:
○ Duplicates can indicate issues in data entry or data processing.
○ Ensuring data integrity is critical for reliable analysis and reporting.
4. Impact on Relationships and Joins:
○ Inaccurate joins between tables can occur if duplicates exist, leading to incorrect
query results.
○ For example, joining the Orders table with the Customers table on 'CNUM' might
produce inflated results.
Practical Analysis with SQL
Let's explore this with some SQL queries to see the impact.
Identify Duplicate 'CNUM' in Customers
First, we can check if there are any duplicates in the 'CNUM' column of the Customers table.
SELECT CNUM, COUNT(*)
FROM Customers
GROUP BY CNUM
HAVING COUNT(*) > 1;
Identify Duplicate 'ONUM' in Orders
Next, we check for duplicates in the 'ONUM' column of the Orders table.
SELECT ONUM, COUNT(*)
FROM Orders
GROUP BY ONUM
HAVING COUNT(*) > 1;
Assess the Impact on Total Order Amount
To see how duplicates might impact the total order amount, we can compare the total order
amount with and without considering duplicates.
sql
Copy code
-- Total amount considering duplicates
SELECT SUM(AMOUNT) AS TotalAmountWithDuplicates
FROM Orders;
-- Total amount without duplicates (assuming ONUM should be unique)
SELECT SUM(AMOUNT) AS TotalAmountWithoutDuplicates
FROM (
SELECT DISTINCT ONUM, AMOUNT
FROM Orders
) AS DistinctOrders;
Example Outputs and Interpretation
Assume the following results from the above queries:
● Duplicate 'CNUM' in Customers:
CNUM COUNT(*)
2001 2
● Duplicate 'ONUM' in Orders:
ONUM COUNT(*)
3002 2
● Total Amount Comparisons:
TotalAmountWithDuplicates TotalAmountWithoutDuplicates
10857.82 10231.43
Interpretation:
1. Duplicate 'CNUM':
○ Customer 2001 appears twice, indicating a potential issue with data entry.
○ Any analysis involving customer counts, such as the number of unique customers
or average customer rating, might be skewed.
2. Duplicate 'ONUM':
○ Order 3002 appears twice, inflating the total order amount.
○ The total order amount considering duplicates is higher (10857.82) compared to
the de-duplicated total (10231.43), indicating a significant impact on financial
summaries.
Recommendations
1. Data Cleansing:
○ Regularly audit the database for duplicates and implement data cleansing
processes to remove them.
○ Ensure primary keys (like 'CNUM' and 'ONUM') are unique to prevent duplicates.
2. Validation Rules:
○ Implement validation rules at the database level to prevent the insertion of
duplicates.
○ Use unique constraints where appropriate.
3. Analysis Adjustment:
○ When performing data analysis, use queries that account for potential duplicates.
○ For example, use DISTINCT or group by clauses to ensure accurate
aggregations.
6. Produce name and number of each salesman and each customer with more than one
current order in the alphabetical order of names.
SELECT SNAME, SNUM FROM Salesmen WHERE SNUM IN (SELECT SNUM FROM
Orders GROUP BY SNUM HAVING COUNT(ONUM) > 1)
UNION
SELECT CNAME, CNUM FROM Customers WHERE CNUM IN (SELECT CNUM FROM
Orders GROUP BY CNUM HAVING COUNT(ONUM) > 1)
ORDER BY SNAME;
Output:
NAME NUMBER
Anand 1003
Harsh 2001
Lalit 2003
Rajesh 1007
7. Assess the impact of using a fixed 12% commission rate.
Steps to follow are:
1. Calculate the total commissions currently earned by each salesman.
2. Calculate the total commissions that would be earned if a fixed 12% rate is applied.
3. Compare the differences and analyze the impact.
Current Total Commissions
We'll start by calculating the total commissions currently earned by each salesman based on the
orders they handled.
SELECT S.SNUM, S.SNAME, SUM(O.AMOUNT * S.COMMISSION / 100) AS
TotalCurrentCommission
FROM Salesmen S
JOIN Orders O ON S.SNUM = O.SNUM
GROUP BY S.SNUM, S.SNAME;
Total Commissions with Fixed 12% Rate
Next, we'll calculate the total commissions that would be earned if a fixed 12% commission rate
is applied.
SELECT S.SNUM, S.SNAME, SUM(O.AMOUNT * 12 / 100) AS
TotalFixedCommission
FROM Salesmen S
JOIN Orders O ON S.SNUM = O.SNUM
GROUP BY S.SNUM, S.SNAME;
Comparison of Current vs Fixed Commissions
We will then compare the results from the two calculations.
SELECT
S.SNUM,
S.SNAME,
SUM(O.AMOUNT * S.COMMISSION / 100) AS TotalCurrentCommission,
SUM(O.AMOUNT * 12 / 100) AS TotalFixedCommission,
SUM(O.AMOUNT * 12 / 100) - SUM(O.AMOUNT * S.COMMISSION / 100) AS
Difference
FROM Salesmen S
JOIN Orders O ON S.SNUM = O.SNUM
GROUP BY S.SNUM, S.SNAME;
Output Explanation:
● TotalCurrentCommission: The total commissions earned by each salesman based
on the current commission rates.
● TotalFixedCommission: The total commissions that would be earned if a fixed 12%
commission rate is applied.
● Difference: The difference between the total fixed commissions and the total current
commissions. A positive value indicates that the salesman would earn more under the
fixed rate, while a negative value indicates they would earn less.
Running the Queries in SQL
Assuming we have the tables and data as specified, running the above queries would provide
us with the detailed impact analysis. Here's a step-by-step execution:
sql
Copy code
-- Current Total Commissions
SELECT S.SNUM, S.SNAME, SUM(O.AMOUNT * S.COMMISSION / 100) AS
TotalCurrentCommission
FROM Salesmen S
JOIN Orders O ON S.SNUM = O.SNUM
GROUP BY S.SNUM, S.SNAME;
-- Total Commissions with Fixed 12% Rate
SELECT S.SNUM, S.SNAME, SUM(O.AMOUNT * 12 / 100) AS
TotalFixedCommission
FROM Salesmen S
JOIN Orders O ON S.SNUM = O.SNUM
GROUP BY S.SNUM, S.SNAME;
-- Comparison of Current vs Fixed Commissions
SELECT
S.SNUM,
S.SNAME,
SUM(O.AMOUNT * S.COMMISSION / 100) AS TotalCurrentCommission,
SUM(O.AMOUNT * 12 / 100) AS TotalFixedCommission,
SUM(O.AMOUNT * 12 / 100) - SUM(O.AMOUNT * S.COMMISSION / 100) AS
Difference
FROM Salesmen S
JOIN Orders O ON S.SNUM = O.SNUM
GROUP BY S.SNUM, S.SNAME;
Example Output
SNUM SNAME TotalCurrentCommission TotalFixedCommissio Difference
n
1001 Piyush 312.26 343.46 31.20
1002 Sejal 818.76 755.89 -62.87
1003 Anand 0.00 0.00 0.00
1004 Miti 228.01 228.01 0.00
1007 Rajesh 2.24 2.24 0.00
8. Pseudocode to Outline the process of updating customer ratings for all customers in
'Rome' by 100.
Pseudo Code:
BEGIN
-- Step 1: Connect to the database
CONNECT TO Database
-- Step 2: Begin a transaction
BEGIN TRANSACTION
TRY
-- Step 3: Retrieve customer records from 'Rome'
SELECT CustomerID, CurrentRating
FROM Customers
WHERE City = 'Rome'
-- Step 4: Loop through each customer record
FOR EACH customer IN ResultSet DO
-- Step 5: Calculate the new rating
newRating = customer.CurrentRating + 100
-- Step 6: Update the customer's rating
UPDATE Customers
SET Rating = newRating
WHERE CustomerID = customer.CustomerID
END FOR
-- Step 7: Commit the transaction
COMMIT TRANSACTION
EXCEPT
-- Step 8: Handle any errors
PRINT "An error occurred during the update process."
-- Step 9: Rollback the transaction
ROLLBACK TRANSACTION
END TRY
-- Step 10: Close the database connection
DISCONNECT FROM Database
END
Query:
UPDATE Customers SET RATING = RATING + 100 WHERE CITY = 'Rome';
9. Calculate the average rating for customers in 'Surat' and count the number of
customers with ratings above that average.
SELECT AVG(RATING) AS AvgRating FROM Customers WHERE CITY = 'Surat';
SELECT COUNT(*) FROM Customers WHERE CITY = 'Surat' AND RATING >
(SELECT AVG(RATING) FROM Customers WHERE CITY = 'Surat');
Output for AvgRating Query:
AvgRating
200
Output for Count Query:
COUNT(*)
10. Create a trigger that updates the 'COMMISSION' value to remove the '%' sign and
convert it to a decimal.
CREATE TRIGGER update_commission
BEFORE INSERT ON Salesmen
FOR EACH ROW
BEGIN
SET NEW.COMMISSION = NEW.COMMISSION / 100;
END;
