34
ASSIGNMENT 4
1. Create tables as required with the necessary attributes , primary key , foreign key and
other constraints so that you can write the following SQLs and get proper output . Table
for Salesman
Query:
CREATE TABLE ARG_Salesman (
SalesmanID NUMBER PRIMARY KEY,
SalesmanName VARCHAR2(100) NOT NULL,
City VARCHAR2(100),
Commission NUMBER(5,2) CHECK (Commission BETWEEN 0 AND 100)
);
DESC ARG_Salesman
INSERT INTO ARG_Salesman VALUES (1, 'John Doe', 'New York', 15);
INSERT INTO ARG_Salesman VALUES (2, 'Jane Smith', 'Los Angeles', 10);
INSERT INTO ARG_Salesman VALUES (3, 'Alice Johnson', 'Chicago', 12);
INSERT INTO ARG_Salesman VALUES (4, 'Bob Brown', 'Houston', 14);
INSERT INTO ARG_Salesman VALUES (5, 'Charlie Davis', 'Phoenix', 13);
CREATE TABLE ARG_Customer (
CustomerID NUMBER PRIMARY KEY,
CustomerName VARCHAR2(100) NOT NULL,
City VARCHAR2(100),
SalesmanID NUMBER,
FOREIGN KEY (SalesmanID) REFERENCES ARG_Salesman(SalesmanID)
);
Name Null? Type
-------------- -------- --------------
35
CUSTOMERID NOT NULL NUMBER
CUSTOMERNAME NOT NULL VARCHAR2(100)
CITY VARCHAR2(100)
SALESMANID NUMBER
INSERT INTO ARG_Customer VALUES (1, 'Acme Corp', 'New York', 1);
INSERT INTO ARG_Customer VALUES (2, 'Globex Inc', 'Los Angeles', 2);
INSERT INTO ARG_Customer VALUES (3, 'Initech', 'Chicago', 3);
INSERT INTO ARG_Customer VALUES (4, 'Umbrella Corp', 'Houston', 4);
INSERT INTO ARG_Customer VALUES (5, 'Hooli', 'Phoenix', 5);
SELECT * FROM ARG_Customer;
Table for Orders
Query:
-- Creating the ARG_Orders table
CREATE TABLE ARG_Orders (
OrderID NUMBER PRIMARY KEY,
OrderDate DATE,
OrderAmount NUMBER(10,2) CHECK (OrderAmount > 0),
CustomerID NUMBER,
FOREIGN KEY (CustomerID) REFERENCES ARG_Customer(CustomerID)
);
-- Describing the ARG_Orders table
DESC ARG_Orders;
Name Null? Type
------------ -------- --------------
ORDERID NOT NULL NUMBER
ORDERDATE DATE
ORDERAMOUNT NOT NULL NUMBER(10,2)
CUSTOMERID NUMBER
-- Inserting data into ARG_Orders
INSERT INTO ARG_Orders VALUES (1, TO_DATE('2024-08-01', 'YYYY-MM-DD'),
1500, 1);
INSERT INTO ARG_Orders VALUES (2, TO_DATE('2024-08-02', 'YYYY-MM-DD'),
750, 2);
INSERT INTO ARG_Orders VALUES (3, TO_DATE('2024-08-03', 'YYYY-MM-DD'),
1800, 3);
36
INSERT INTO ARG_Orders VALUES (4, TO_DATE('2024-08-04', 'YYYY-MM-DD'),
2100, 4);
INSERT INTO ARG_Orders VALUES (5, TO_DATE('2024-08-05', 'YYYY-MM-DD'),
900, 5);
-- Query to select all data from ARG_Orders
SELECT * FROM ARG_Orders;
(a) Write a SQL statement to prepare a list with salesman name, customer name and their
cities for the salesmen and customer who belongs to the same city.
Query:
SELECT s.SalesmanName, c.CustomerName, c.City
FROM ARG_Salesman s, ARG_Customer c
WHERE s.City = c.City;
(b) Write a SQL statement to make a list with order no, purchase amount, customer name
and their cities for those orders which order amount between 500 and 2000.
Query:
SELECT o.OrderID, o.OrderAmount,
37
(SELECT c.CustomerName FROM ARG_Customer c WHERE c.CustomerID =
o.CustomerID) AS CustomerName,
(SELECT c.City FROM ARG_Customer c WHERE c.CustomerID = o.CustomerID) AS City
FROM ARG_Orders o
WHERE o.OrderAmount BETWEEN 500 AND 2000;
(c) Write a SQL statement to know which salesman is working for which customer.
Query:
SELECT c.CustomerName,
(SELECT s.SalesmanName FROM ARG_Salesman s WHERE s.SalesmanID = c.SalesmanID)
AS SalesmanName
FROM ARG_Customer c;
(d) Write a SQL statement to find the list of customers who appointed a salesman for their
jobs who gets a commission from the company is more than 12%.
Query:
38
SELECT c.CustomerName
FROM ARG_Customer c
WHERE c.SalesmanID IN (
SELECT s.SalesmanID
FROM ARG_Salesman s
WHERE s.Commission > 12
);
(e) Write a SQL statement to find the list of customers who appointed a salesman for their
jobs who does not live in the same city where their customer lives, and gets a
commission is above 12%
Query:SELECT c.CustomerName
FROM ARG_Customer c
WHERE c.SalesmanID IN (
SELECT s.SalesmanID
FROM ARG_Salesman s
WHERE s.Commission > 12 AND c.City <> s.City
);
(f)Write a SQL statement to find the details of a order i.e. order number, order date, amount
of order, which customer gives the order and which salesman works for that customer and
how much commission he gets for an order.
Query:
SELECT o.OrderID,
o.OrderDate,
o.OrderAmount,
(SELECT c.CustomerName FROM ARG_Customer c WHERE c.CustomerID =
o.CustomerID) AS CustomerName,
(SELECT s.SalesmanName
FROM ARG_Salesman s
WHERE s.SalesmanID = (SELECT c.SalesmanID FROM ARG_Customer c WHERE
c.CustomerID = o.CustomerID)) AS SalesmanName,
39
(SELECT s.Commission
FROM ARG_Salesman s
WHERE s.SalesmanID = (SELECT c.SalesmanID FROM ARG_Customer c WHERE
c.CustomerID = o.CustomerID)) AS Commission
FROM DIP_Orders o;
(g) Write a SQL statement to make a join on the tables salesman, customer and orders in
such a form that the same column of each table will appear once and only the relational
rows will come.
Query:
SELECT s.SalesmanName,
c.CustomerName, (SELECT
o.OrderID
FROM ARG_Orders o
WHERE o.CustomerID = c.CustomerID
AND o.CustomerID IN (SELECT c2.CustomerID
FROM ARG_Customer c2
WHERE c2.SalesmanID = s.SalesmanID)
) AS OrderID
FROM ARG_Salesman s,
ARG_Customer c
WHERE c.SalesmanID = s.SalesmanID;
40
(h) Write a SQL statement to make a report with customer name, city, order number, order
date, order amount salesman name and commission to find that either any of the existing
customers have placed no order or placed one or more orders by their salesman or by own.
Query:
SELECT c.CustomerName,
c.City,
(SELECT o.OrderID
FROM ARG_Orders o
WHERE o.CustomerID = c.CustomerID
) AS OrderID,
(SELECT o.OrderDate
FROM ARG_Orders o
WHERE o.CustomerID = c.CustomerID
) AS OrderDate,
(SELECT o.OrderAmount
FROM ARG_Orders o
WHERE o.CustomerID = c.CustomerID
) AS OrderAmount,
(SELECT s.SalesmanName
FROM ARG_Salesman s
41
WHERE s.SalesmanID = c.SalesmanID
) AS SalesmanName,
(SELECT s.Commission
FROM ARG_Salesman s
WHERE s.SalesmanID = c.SalesmanID
) AS Commission
FROM ARG_Customer c;
