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¶
- 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;
- 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;
- 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
);
- 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;
- 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;
- 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;
- 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;
- Write a query to display all customers with a grade above 100.
- Write a query statement to display all customers in New York who have a grade value above 100.
-
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.
-
Write a SQL query to display those customers who are neither belongs to the city New York nor grade value is more than 100.
-
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.