Skip to content

03

Initialization

use 20200198db;
set foreign_key_checks = 0;
drop table if exists salesman, customer, orders;
set foreign_key_checks  = 1;

Tables

Create the three tables given below and answer the following queries

salesman

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
5003 Lauson Hen 0.12
5007 Paul Adam Rome 0.13

customer

customer_id cust_name city grade salesman_id
3002 Nick Rimando New York 100 5001
3005 Graham Zusi California 200 5002
3001 Brad Guzan London 5005
3004 Fabian Johns Paris 300 5006
3007 Brad Davis New York 200 5001
3009 Geoff Camero Berlin 100 5003
3008 Julian Green London 300 5002
3003 Jozy Altidor Moscow 200 5007

orders

ord_no purch_amt ord_date customer_id salesman_id
70001 150.50 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.50 2012-08-17 3009 5003
70007 948.50 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760.00 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.40 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.60 2012-04-25 3002 5001 
create table salesman(
  salesman_id int(4),
  name varchar(20),
  city varchar(20),
  commission float(10, 5),

  PRIMARY KEY(salesman_id)
);

create table customer(
  customer_id int(4),
  cust_name varchar(20),
  city varchar(20),
  grade int(4),
  salesman_id int(4),

  PRIMARY KEY(customer_id),
  FOREIGN KEY(salesman_id) REFERENCES salesman(salesman_id) on delete cascade
);

create table orders(
  ord_no int(5),
  purch_amt float(10, 5),
  ord_date date,
  customer_id int(4),
  salesman_id int(4),

  PRIMARY KEY(ord_no),
  FOREIGN KEY(salesman_id) REFERENCES salesman(salesman_id) on delete cascade
);

insert into salesman values
  (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),
  (5003, "Lauson Hen", null, 0.12),
  (5007, "Paul Adam", "Rome", 0.13);

insert into customer values 
    (3002, "Nick Rimando", "New York", 100, 5001),
    (3005, "Graham Zusi", "California", 200, 5002),
    (3001, "Brad Gusan", "London", null, 5005),
    (3004, "Fabian Johns", "Paris", 300, 5006),
    (3007, "Brad Davis", "New York", 200, 5001),
    (3009, "Geoff Camero", "Berlin", 100, 5003),
    (3008, "Julian Green", "London", 300, 5002),
    (3003, "Jozy Altidor", "Moscow", 200, 5007);

insert into orders values
  (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);

Questions

  1. Write a query to find those customers with their name and those salesmen with their name and city who lives in the same city.
select customer.city as City, cust_name as Customer, salesman.name as Salesperson
 from customer, salesman
 where customer.salesman_id = salesman.salesman_id
         and customer.city = salesman.city;
  1. Write a SQL statement to find the names of all customers along with the salesmen who works for them.
select cust_name as Customer, salesman.name as Salesperson
 from customer, salesman
 where customer.salesman_id = salesman.salesman_id;
  1. Write a SQL statement to display all those orders by the customers not located in the same cities where their salesmen live.
select *
 from orders
 where (orders.customer_id, orders.salesman_id) in (
    select customer.customer_id, salesman.salesman_id
     from customer, salesman
     where customer.city != salesman.city
  );
  1. Write a SQL statement that finds out each order number followed by the name of the customers who made the order.
select ord_no as "Order Number", cust_name as Customer
 from orders, customer
 where orders.customer_id = customer.customer_id;
  1. Write a SQL statement that sorts out the customer and their grade who made an order. Each of the customers must have a grade and served by at least a salesman, who belongs to a city.
select cust_name as Customer, grade as Grade
 from customer
 where grade is not null
     and salesman_id is not null
 order by grade desc;
  1. Write a query that produces all customers with their name, city, salesman and commission, who served by a salesman and the salesman works at a rate of the commission within 12% to 14%.
select cust_name as Customer, customer.city, salesman.name, salesman.commission as commission
 from customer, salesman
 where customer.salesman_id = salesman.salesman_id
     and salesman.commission between 0.12 and 0.14;
  1. Write a SQL statement that produces all orders with the order number, customer name, commission rate and earned commission amount for those customers who carry their grade is 200 or more and served by an existing salesman.
select ord_no as "Order Number", cust_name as Customer, commission as "Commission Rate", (commission * purch_amt) as "Earned Commission"
 from orders, customer, salesman
 where orders.customer_id = customer.customer_id
     and orders.salesman_id = salesman.salesman_id
     and grade > 200
     and customer.salesman_id is not null;
  1. Write a query to display all customers with a grade above 100.
select cust_name as Customer
from customer
where grade > 100;
  1. Write a query statement to display all customers in New York who have a grade value above 100.
select cust_name as Customer
from customer
where city = "New York"
 and grade > 100;

  1. Write a SQL statement to display all the customers, who are either belongs to the city New York or not had a grade above 100.

    select cust_name as Customer
from customer
where (city = "New York") or (grade <= 100);

  2. Write a SQL query to display those customers who are neither belongs to the city New York nor grade value is more than 100.

    select cust_name as Customer
from customer
where not(city = "New York" or grade > 100);

  3. Write a SQL statement to display either those orders which are not issued on date 2012-09-10 and issued by the salesman whose ID is 5005 and below, or those orders which purchase amount is 1000.00 and below.

    select *
from orders
where not(
    ord_date < 2012-09-10
    and salesman_id in (select salesman_id from salesman where salesman_id <= 5005)
    )
    or purch_amt <= 1000;
Last Updated: 2023-01-25 ; Contributors: AhmedThahir

Comments