Untitled

Name:-
Div:-FY-IT.B
DATE:-9/12/2022
Subject:-DBMS
Roll-No:-
PC_NO:-
ENROLLMENT:-
AIM:- Assigmnet7

Table-name: Order-details
ord_no      purch_amt   ord_date    customer_id  salesman_id
----------  ----------  ----------  -----------  -----------
70001       150.5       2012-10-05  3005         5002
70009       270.65      2012-09-10  3001         5005
70002       65.26       2012-10-05  3002         5001
70004       110.5       2012-08-17  3009         5003
70007       948.5       2012-09-10  3005         5002
70005       2400.6      2012-07-27  3007         5001
70008       5760        2012-09-10  3002         5001
70010       1983.43     2012-10-10  3004         5006
70003       2480.4      2012-10-10  3009         5003
70012       250.45      2012-06-27  3008         5002
70011       75.29       2012-08-17  3003         5007
70013       3045.6      2012-04-25  3002         5001

CREATE TABLE Ord_Detail
(
Ord_No NUMBER(7) PRIMARY KEY,
Purch_Amt NUMBER(5,2),
Ord_Date DATE,
Customer_Id NUMBER(5),
Salesman_Id NUMBER(5)
);

ALTER TABLE Ord_Detail
MODIFY Purch_Amt NUMBER(10,2);

ALTER TABLE Ord_Detail
ADD FOREIGN KEY (Customer_Id) REFERENCES Cus_Detail (Cust_Id);

ALTER TABLE Ord_Detail
ADD FOREIGN KEY (Salesman_Id) REFERENCES SalesMan_Details (Salesman_ID);

INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70001,150.5,'10/05/2012',3005,5002);

INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70009,270.65,'09/10/2012',3001,5005);


INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70002,65.26,'10/05/2012',3002,5001);

INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70004,110.5,'08/17/2012',3009,5003);


INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70007,948.5,'09/10/2012',3005,5002);


INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70005,2400.6,'07/27/2012',3007,5001);


INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70008,5760,'09/10/2012',3002,5001);


INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70010,1983.43,'10/10/2012',3004,5006);


INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70003,2480.4,'10/10/2012',3009,5003);

INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70012,250.45,'06/27/2012',3008,5002);


INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70011,75.29,'08/17/2012',3003,5007);


INSERT INTO Ord_Detail (Ord_No,Purch_Amt,Ord_Date,Customer_Id,Salesman_Id)
VALUES (70013,3045.6,'04/25/2012',3002,5001);

SELECT *
FROM Ord_Detail;


WRITE a SQL query TO calculate total purchase amount OF ALL orders. Display total purchase amount.

Ans:

SELECT SUM(Purch_Amt) Total_Amount
FROM Ord_Detail;

 output:


WRITE a SQL query TO calculate average purchase amount OF ALL orders. Display average purchase amount.

Ans:

SELECT AVG(Purch_Amt) Average
FROM Ord_Detail;

Output :


WRITE a SQL query TO COUNT the NUMBER OF UNIQUE salespeople. Display NUMBER OF salespeople.

Ans:

SELECT COUNT(DISTINCT Salesman_Id)
FROM Ord_Detail;

Output:


WRITE a SQL query TO find the maximum purchase amount.
Ans:

SELECT MAX(Purch_Amt)
FROM Ord_Detail;

Output:


WRITE a SQL query TO find the minimum purchase amount.
Ans:


SELECT MIN(Purch_Amt)
FROM Ord_Detail;
Output:


WRITE a SQL query TO COUNT ALL the orders generated ON '2012-08-17'. Display  NUMBER OF orders.
Ans:


SELECT COUNT(Ord_Date) Total_Order
FROM Ord_Detail
WHERE Ord_Date = '08/17/2012';

Output :


Table-name: customer_details
customer_id |   cust_name    |    city    | grade | salesman_id
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | NEW York   |   100 |        5001
        3007 | Brad Davis     | NEW York   |   200 |        5001
        3005 | Graham Zusi    | California |   200 |        5002
        3008 | Julian Green   | London     |   300 |        5002
        3004 | Fabian Johnson | Paris      |   300 |        5006
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |     |        5005

CREATE TABLE Cus_Detail
(
Cust_Id NUMBER(7) PRIMARY KEY,
Cust_Name VARCHAR2(20),
City VARCHAR2(20),
Grade NUMBER(5),
Sal_Id NUMBER(5)
);
ALTER TABLE Cus_Detail
ADD FOREIGN KEY (Sal_Id) REFERENCES SalesMan_Details (Salesman_ID);

INSERT INTO Cus_Detail (Cust_Id,Cust_Name,City,Grade,Sal_Id)
VALUES (3002,'Nick Rimando','New York',100,5001);

INSERT INTO Cus_Detail (Cust_Id,Cust_Name,City,Grade,Sal_Id)
VALUES (3007,'Brad Davis','New York',200,5001);

INSERT INTO Cus_Detail (Cust_Id,Cust_Name,City,Grade,Sal_Id)
VALUES (3005,'Graham Zusi','Califonia',200,5002);

INSERT INTO Cus_Detail (Cust_Id,Cust_Name,City,Grade,Sal_Id)
VALUES (3008,'Julian Green','London',300,5002);

INSERT INTO Cus_Detail (Cust_Id,Cust_Name,City,Grade,Sal_Id)
VALUES (3004,'Fabian Johnson','Paris',300,5006);

INSERT INTO Cus_Detail (Cust_Id,Cust_Name,City,Grade,Sal_Id)
VALUES (3009,'Geoff Cameron','Berlin',100,5003);

INSERT INTO Cus_Detail (Cust_Id,Cust_Name,City,Grade,Sal_Id)
VALUES (3003,'Jozy Altidor','Moscow',200,5007);

INSERT INTO Cus_Detail (Cust_Id,Cust_Name,City,Grade,Sal_Id)
VALUES (3001,'Brad Guzan','London',100,5005);
SELECT *
FROM Cus_Detail;


WRITE a SQL query TO find the highest grade OF the customers FOR each OF the city. Display  city, maximum grade.

Ans:


                                                         SELECT City,Grade
                                                          FROM Cus_Detail
                                                          ORDER BY Grade DESC;
                                                                Output:


WRITE a SQL query TO COUNT the NUMBER OF customers. Display  NUMBER OF customers.

Ans:

  SELECT COUNT(Cust_Name) Total_Customer
FROM Cus_Detail;

Output:


WRITE a SQL query TO find the NUMBER OF customers who got AT LEAST a gradation FOR his/her activity.

Ans:

  SELECT COUNT(Grade) least_gradation
FROM Cus_Detail;

Output:
LEAST_GRADATION
8


4.WRITE a SQL query TO find the highest grade OF the customers FOR each OF the city. Display  city, maximum grade.

Ans:

SELECT City,Grade
FROM Cus_Detail
ORDER BY Grade DESC;
Output :


WRITE a SQL query TO find the highest purchase amount ordered BY each customer. Display  customer ID, maximum purchase amount.

Ans:
SELECT Customer_Id,Purch_Amt
FROM Ord_Detail
ORDER BY Purch_Amt DESC;


Output:


Table-name:
SALESMAN_details

salesman_id |    name    |   city   | commission
-------------+------------+----------+------------
        5001 | James Hoog | NEW York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12


CREATE TABLE SalesMan_Details
(
Salesman_ID NUMBER(5) PRIMARY KEY,
S_Name VARCHAR2(20),
City VARCHAR2(20),
S_commission NUMBER(5,2)
);


INSERT INTO SalesMan_Details (Salesman_ID,S_Name,City,S_commission)
VALUES (5001,'James Hoog','New York',0.15);

INSERT INTO SalesMan_Details (Salesman_ID,S_Name,City,S_commission)
VALUES (5002,'Nail Knite','Paris',0.13);


INSERT INTO SalesMan_Details (Salesman_ID,S_Name,City,S_commission)
VALUES (5005,'Pit Alex','London',0.11);


INSERT INTO SalesMan_Details (Salesman_ID,S_Name,City,S_commission)
VALUES (5006,'Mc Lyon','Paris',0.14);


INSERT INTO SalesMan_Details (Salesman_ID,S_Name,City,S_commission)
VALUES (5007,'Paul Adam','Rome',0.13);


INSERT INTO SalesMan_Details (Salesman_ID,S_Name,City,S_commission)
VALUES (5003,'Lauson Hen','San Jose',0.12);

SELECT *
FROM SalesMan_Details;


Queries: (USE ANY TABLE FROM above TO solve following queries)
WRITE a SQL query TO COUNT NUMBER OF orders BY the combination OF each ORDER DATE AND salesperson. Display  ORDER DATE, salesperson id.

Ans:

SELECT Ord_Date,Salesman_Id,COUNT(*)
FROM Ord_Detail
GROUP BY Ord_Date,Salesman_Id;


Output:
ORD_DATE    SALESMAN_ID COUNT(*)
10/05/2012  5002    1
08/17/2012  5007    1
09/10/2012  5005    1
10/05/2012  5001    1
04/25/2012  5001    2
10/10/2012  5006    1
09/10/2012  5002    1
06/27/2012  5002    1
10/10/2012  5003    1
08/17/2012  5003    1


WRITE a SQL query TO find the highest purchase amount ordered BY each customer ON a particular DATE. Display , ORDER DATE AND highest purchase amount.

Ans:

SELECT Ord_Date,Purch_Amt
FROM Ord_Detail
ORDER BY Purch_Amt DESC;

  Output:
ORD_DATE    PURCH_AMT
09/10/2012  5760
04/25/2012  3045.6
04/25/2012  3045.6
10/10/2012  2480.4
07/27/2012  2400.6
07/27/2012  2400.6
10/10/2012  1983.43
09/10/2012  948.5
09/10/2012  270.65
06/27/2012  250.45


WRITE a SQL query TO find the highest purchase amount ON '2012-08-17' BY each salesperson. Display  salesperson ID, purchase amount.

Ans:

SELECT Salesman_Id,Purch_Amt
FROM Ord_Detail
WHERE Ord_Date ='08/17/2012';
Output:

SALESMAN_ID PURCH_AMT
5003    110.5
5007    75.29


WRITE a SQL query TO find highest ORDER (purchase) amount BY each customer IN a particular ORDER DATE. Filter the result BY highest ORDER (purchase) amount above 2000.00. Display  customer id, ORDER DATE AND maximum purchase amount.

Ans:

SELECT Customer_Id,Ord_Date,MAX(Purch_Amt)
FROM Ord_Detail
GROUP BY Customer_Id,Ord_Date
HAVING MAX(Purch_Amt)>2000.00;

Output:


CUSTOMER_ID ORD_DATE    MAX(PURCH_AMT)
3007    07/27/2012  2400.6
3009    10/10/2012  2480.4
3002    09/10/2012  5760
3002    04/25/2012  3045.6

WRITE a SQL query TO find the maximum ORDER (purchase) amount BY each customer. The customer ID should be IN the RANGE 3002 AND 3007(BEGIN AND END VALUES are included.). Display  customer id AND maximum purchase amount.

Ans:
  SELECT Customer_Id,MAX(Purch_Amt)
FROM Ord_Detail
WHERE Customer_Id BETWEEN 3002 AND 3007
GROUP BY Customer_Id;

Output:

CUSTOMER_ID MAX(PURCH_AMT)
3004    1983.43
3002    5760
3005    948.5
3007    2400.6
3003    75.29


More queries:

a SQL query TO find the salespersons AND customers who live IN same city. Display customer name, salesperson name AND salesperson city.

Ans:
SELECT SalesMan_Details.S_Name AS "Salesman",
Cus_Detail.Cust_Name, Cus_Detail.City
FROM SalesMan_Details,Cus_Detail
WHERE SalesMan_Details.City=Cus_Detail.City;
Output:

Salesman    CUST_NAME   CITY
James Hoog  Nick Rimando    NEW York
James Hoog  Brad Davis  NEW York
Pit Alex    Julian Green    London
Mc Lyon Fabian Johnson  Paris
Nail Knite  Fabian Johnson  Paris
Pit Alex    Brad Guzan  London

WRITE a SQL query TO find ALL the customers along WITH the salesperson who works FOR them. Display  customer name, AND salesperson name.

Ans:

SELECT Cus_Detail.Cust_Name, SalesMan_Details.S_Name
FROM Cus_Detail,SalesMan_Details
WHERE SalesMan_Details.Salesman_ID = Cus_Detail.Sal_Id;
Output:

CUST_NAME   S_NAME
Nick Rimando    James Hoog
Brad Davis  James Hoog
Graham Zusi Nail Knite
Julian Green    Nail Knite
Fabian Johnson  Mc Lyon
Geoff Cameron   Lauson Hen
Brad Guzan  Pit Alex
Jozy Altidor    Paul Adam

WRITE a SQL query TO find those sales people who generated orders FOR their customers but NOT located IN the same city. Display  ord_no, cust_name, customer_id (orders TABLE), salesman_id (orders TABLE).

Ans:

SELECT Ord_No, Cust_Name, Ord_Detail.Customer_Id, Ord_Detail.Salesman_Id
FROM SalesMan_Details, Cus_Detail, Ord_Detail
WHERE Cus_Detail.City <> SalesMan_Details.City
AND Ord_Detail.Customer_Id = Cus_Detail.Cust_Id
AND Ord_Detail.Salesman_Id = SalesMan_Details.Salesman_ID;
Output:

ORD_NO  CUST_NAME   CUSTOMER_ID SALESMAN_ID
70012   Julian Green    3008    5002
70001   Graham Zusi 3005    5002
70007   Graham Zusi 3005    5002
70011   Jozy Altidor    3003    5007
70003   Geoff Cameron   3009    5003
70004   Geoff Cameron   3009    5003

a SQL query TO find those orders made BY customers. Display  ORDER NUMBER, customer name.

Ans :

SELECT Ord_Detail.Ord_No, Cus_Detail.Cust_Name
FROM Ord_Detail, Cus_Detail
WHERE Ord_Detail.Customer_Id = Cus_Detail.Cust_Id;
 Output:

ORD_NO  CUST_NAME
70009   Brad Guzan
70013   Nick Rimando
70002   Nick Rimando
70008   Nick Rimando
70023   Nick Rimando
70011   Jozy Altidor
70010   Fabian Johnson
70007   Graham Zusi
70001   Graham Zusi
70005   Brad Davis


A  query TO find the salesperson AND customer who belongs TO same city. Display Salesman, cust_name AND city.

Ans:


SELECT SalesMan_Details.S_Name AS "Salesman",
Cus_Detail.Cust_Name, Cus_Detail.City
FROM SalesMan_Details,Cus_Detail
WHERE SalesMan_Details.City=Cus_Detail.City;
 Output:

Salesman    CUST_NAME   CITY
James Hoog  Nick Rimando    NEW York
James Hoog  Brad Davis  NEW York
Pit Alex    Julian Green    London
Mc Lyon Fabian Johnson  Paris
Nail Knite  Fabian Johnson  Paris
Pit Alex    Brad Guzan  London

A query TO find those orders WHERE ORDER amount EXISTS BETWEEN 500 AND 2000. Display ord_no, purch_amt, cust_name, city.

Ans:
SELECT  a.Ord_No,a.Purch_Amt,
b.Cust_Name,b.City
FROM Ord_Detail a,Cus_Detail b
WHERE a.Customer_Id=b.Cust_Id
AND a.Purch_Amt BETWEEN 500 AND 2000;

 Output:

ORD_NO  PURCH_AMT   CUST_NAME   CITY
70010   1983.43 Fabian Johnson  Paris
70007   948.5   Graham Zusi Califonia

A  SQL query TO find the salesperson(s) AND the customer(s) he handle. Display Customer Name, city, Salesman, commission.

Ans:

SELECT a.Cust_Name AS "Customer Name",
a.City, b.S_Name AS "Salesman", b.S_commission
FROM Cus_Detail a
INNER JOIN SalesMan_Details b
ON a.Sal_Id=b.Salesman_ID;


 Output:

Customer Name   CITY    Salesman    S_COMMISSION
Nick Rimando    NEW York    James Hoog  .15
Brad Davis  NEW York    James Hoog  .15
Graham Zusi Califonia   Nail Knite  .13
Julian Green    London  Nail Knite  .13
Fabian Johnson  Paris   Mc Lyon .14
Geoff Cameron   Berlin  Lauson Hen  .12
Brad Guzan  London  Pit Alex    .11
Jozy Altidor    Moscow  Paul Adam   .13


A  SQL query TO find those salespersons who received a commission FROM the company more than 12%. Display  Customer Name, customer city, Salesman, commission.

Ans:
SELECT a.Cust_Name AS "Customer Name",
a.city, b.S_Name AS "Salesman", b.S_commission
FROM Cus_Detail a
INNER JOIN SalesMan_Details b
ON a.Sal_Id=b.Salesman_ID
WHERE b.S_commission>.12;

 Output:

Customer Name   CITY    Salesman    S_COMMISSION
Nick Rimando    NEW York    James Hoog  .15
Brad Davis  NEW York    James Hoog  .15
Graham Zusi Califonia   Nail Knite  .13
Julian Green    London  Nail Knite  .13
Fabian Johnson  Paris   Mc Lyon .14
Jozy Altidor    Moscow  Paul Adam   .13

a SQL query TO find the details OF an ORDER. Display  ord_no, ord_date, purch_amt, Customer Name, grade, Salesman, commission.

Ans:
SELECT a.Ord_No,a.Ord_Date,a.Purch_Amt,
b.cust_name AS "Customer Name", b.Grade,
c.S_Name AS "Salesman", c.S_commission
FROM Ord_Detail a
INNER JOIN Cus_Detail b
ON a.Customer_Id=b.Cust_Id
INNER JOIN SalesMan_Details c
ON a.Salesman_Id=c.Salesman_ID;

 Output:

ORD_NO  ORD_DATE    PURCH_AMT   Customer Name   GRADE   Salesman    S_COMMISSION
70012   06/27/2012  250.45  Julian Green    300 Nail Knite  .13
70001   10/05/2012  150.5   Graham Zusi 200 Nail Knite  .13
70007   09/10/2012  948.5   Graham Zusi 200 Nail Knite  .13
70009   09/10/2012  270.65  Brad Guzan  100 Pit Alex    .11
70010   10/10/2012  1983.43 Fabian Johnson  300 Mc Lyon .14
70011   08/17/2012  75.29   Jozy Altidor    200 Paul Adam   .13
70003   10/10/2012  2480.4  Geoff Cameron   100 Lauson Hen  .12
70004   08/17/2012  110.5   Geoff Cameron   100 Lauson Hen  .12
70015   07/27/2012  2400.6  Brad Davis  200 James Hoog  .15
70005   07/27/2012  2400.6  Brad Davis  200 James Hoog  .15