§2023-04-01
-
inner join,
-
left join,
-
right join,
-
full outer join,
-
cross join,
-
natural join, and
-
a special kind of join called self-join.
試作機器: hc4MnGnome.yushei.net
DROP DATABASE IF EXISTS sales_order_db;
CREATE DATABASE sales_order_db;
--- if you use psql, please do \c sales_order_db
DROP SCHEMA IF EXISTS sales_order;
CREATE SCHEMA sales_order;
--- \dn will show search
--- SET SEARCH_PATH = sales_order
--- sales_order_db=> SET SEARCH_PATH = sales_order;
--- SET <-- message
--- SHOW SEARCH_PATH
--- So if you create a table named test while the search_path is set to sales_order, the full name
--- of the table will be sales_order.test. However, you can refer to it simply as test in your SQL queries,
--- as long as the search_path is set correctly.
--- use \d table_name will show the SCHEMA, or
--- SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'salesmen';
SET SEARCH_PATH = sales_order;
CREATE TABLE salesmen (
salesman_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
city TEXT NOT NULL,
commission FLOAT(2) NOT NULL
);
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
cust_name TEXT NOT NULL,
city TEXT NOT NULL,
grade SMALLINT NOT NULL,
--- ins postgreSQL SERIAL is a type of INTEGER
salesman_id INTEGER NOT NULL,
CONSTRAINT fk_salesmen FOREIGN KEY (salesman_id) REFERENCES salesmen(salesman_id) ON DELETE CASCADE
);
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
purch_amt FLOAT(2),
order_date TIMESTAMP NOT NULL DEFAULT NOW(),
customer_id INTEGER NOT NULL,
salesman_id INTEGER NOT NULL,
CONSTRAINT fk_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
CONSTRAINT fk_salesmen FOREIGN KEY (salesman_id) REFERENCES salesmen(salesman_id) ON DELETE CASCADE
);
- seed the tables
--- seed salesmen table
INSERT INTO salesmen (salesman_id, name, city, commission)
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),
(5007, 'Paul Adam', 'Rome', 0.13),
(5003, 'Lauson Hen', 'San Jose', 0.12);
(6 rows)
--- seed cutomers table
INSERT INTO customers (customer_id, cust_name, city, grade, salesman_id )
VALUES
(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', 400, 5005);
(8 rows)
--- seed orders table
INSERT INTO orders (order_id, purch_amt, order_date, customer_id, salesman_id)
VALUES
(70001, 150.5 , '2012-10-05 12:00:00', 3005, 5002),
(70009, 270.65, '2012-09-10 09:09:09', 3001, 5005),
(70002, 65.26, '2012-10-05 12:40:55', 3002, 5001),
(70004, 110.5 , '2012-08-17 11:34:45', 3009, 5003),
(70007, 948.5 , '2012-09-10 15:15:30', 3005, 5002),
(70005, 2400.6 , '2012-07-27 10:15:40', 3007, 5001),
(70008, 5760 , '2012-09-10 14:45:00', 3002, 5001),
(70010, 1983.43, '2012-10-10 08:09:10', 3004, 5006),
(70003, 2480.4 , '2012-10-10 08:15:30', 3009, 5003),
(70012, 250.45, '2012-06-27 13:13:13', 3008, 5002),
(70011, 75.29, '2012-08-17 14:14:15', 3003, 5007),
(70013, 3045.6 , '2012-04-25 20:45:05', 3002, 5001);
(12 rows)
- Exercise
- Write a SQL query to find the saleman and customer who reside in the same city.
- Return Salesman, cust_name and city.
SELECT salesmen.name AS "Salesman", customers.cust_name, customers.city
FROM salesmen,customers
WHERE salesmen.city=customers.city;
sales_order_db=> SELECT salesmen.name AS "Salesman", customers.cust_name, customers.city
FROM salesmen,customers
WHERE salesmen.city=customers.city;
Salesman | cust_name | city
------------+----------------+----------
James Hoog | Brad Davis | New York
James Hoog | Nick Rimando | New York
Nail Knite | Fabian Johnson | Paris
Pit Alex | Brad Guzan | London
Pit Alex | Julian Green | London
Mc Lyon | Fabian Johnson | Paris
(6 rows)
- Using JOIN
SELECT salesmen.name AS "Salesman Name", salesmen.city as "Salesman City", customers.cust_name AS "Customer Name", customers.city As "Customer City"
FROM salesmen JOIN customers
ON salesmen.salesman_id = customers.salesman_id;
Salesman Name | Salesman City | Customer Name | Customer City
---------------+---------------+----------------+---------------
James Hoog | New York | Nick Rimando | New York
James Hoog | New York | Brad Davis | New York
Nail Knite | Paris | Graham Zusi | California
Nail Knite | Paris | Julian Green | London
Mc Lyon | Paris | Fabian Johnson | Paris
Lauson Hen | San Jose | Geoff Cameron | Berlin
Paul Adam | Rome | Jozy Altidor | Moscow
Pit Alex | London | Brad Guzan | London
(8 rows)
SELECT salesmen.name AS "Salesman Name", salesmen.city as "Salesman City", customers.cust_name AS "Customer Name", customers.city As "Customer City"
FROM salesmen JOIN customers
ON salesmen.salesman_id = customers.salesman_id
WHERE salesmen.city = customers.city;
Salesman Name | Salesman City | Customer Name | Customer City
---------------+---------------+----------------+---------------
James Hoog | New York | Nick Rimando | New York
James Hoog | New York | Brad Davis | New York
Mc Lyon | Paris | Fabian Johnson | Paris
Pit Alex | London | Brad Guzan | London
(4 rows)
- write a SQL query to find those orders where the order amount exists between 500 and 2000.
- Return order_id, purch_amt, cust_name, cit
sales_order_db=> SELECT a.order_id, a.purch_amt, b.cust_name,b.city
FROM orders a, customers b
WHERE a.customer_id = b.customer_id
AND a.purch_amt BETWEEN 500 AND 2000;
order_id | purch_amt | cust_name | city
----------+-----------+----------------+------------
70007 | 948.5 | Graham Zusi | California
70010 | 1983.43 | Fabian Johnson | Paris
(2 rows)
- write a SQL query to find the salesman and the customer he represents. `- Return Customer Name, city, Salesman, commission.
SELECT customers.cust_name, customers.city AS cust_city, salesmen.name AS salesmen_name, salesmen.commission
FROM customers, salesmen
WHERE customers.salesman_id = salesmen.salesman_id;
----------------+------------+---------------+------------
Nick Rimando | New York | James Hoog | 0.15
Brad Davis | New York | James Hoog | 0.15
Graham Zusi | California | Nail Knite | 0.13
Julian Green | London | Nail Knite | 0.13
Fabian Johnson | Paris | Mc Lyon | 0.14
Geoff Cameron | Berlin | Lauson Hen | 0.12
Jozy Altidor | Moscow | Paul Adam | 0.13
Brad Guzan | London | Pit Alex | 0.11
(8 rows)
- write a SQL query to find salespeople who received commissions of more than 12 percent from the company.
- Return Customer Name, customer city, Salesman, commission.
SELECT customers.cust_name as "Customer Name", customers.city as "Customer City", salesmen.name as "Salesman Name", salesmen.commission as "Commission"
FROM customers, salesmen
WHERE
customers.salesman_id = salesmen.salesman_id AND
salesmen.commission > 0.12;
Customer Name | Customer City | Salesman Name | Commission
----------------+---------------+---------------+------------
Nick Rimando | New York | James Hoog | 0.15
Brad Davis | New York | James Hoog | 0.15
Graham Zusi | California | Nail Knite | 0.13
Julian Green | London | Nail Knite | 0.13
Fabian Johnson | Paris | Mc Lyon | 0.14
Jozy Altidor | Moscow | Paul Adam | 0.13
(6 rows)
- write a SQL query to locate those salespeople
- who do not live in the same city where their customers live and
- have received a commission of more than 12% from the company.
- Return Customer Name, customer city, Salesman, salesman city, commission.
SELECT customers.cust_name as "Customer Name", customers.city as "Customer City", salesmen.name as "Salesman Name", salesmen.city as "Salesman City", salesmen.commission as "Commission"
FROM customers INNER JOIN salesmen
ON customers.salesman_id = salesmen.salesman_id
WHERE customers.city != salesmen.city AND
salesmen.commission > 0.12;
Customer Name | Customer City | Salesman Name | Salesman City | Commission
---------------+---------------+---------------+---------------+------------
Graham Zusi | California | Nail Knite | Paris | 0.13
Julian Green | London | Nail Knite | Paris | 0.13
Jozy Altidor | Moscow | Paul Adam | Rome | 0.13
(3 rows)
Caution!
SELECT customers.cust_name as "Customer Name", customers.city as "Customer City", salesmen.name as "Salesman Name", salesmen.city as "Salesman City", salesmen.commission as "Commission"
FROM customers, salesmen
WHERE customers.city != salesmen.city AND
salesmen.commission > 0.12;
Customer Name | Customer City | Salesman Name | Salesman City | Commission
----------------+---------------+---------------+---------------+------------
Nick Rimando | New York | Nail Knite | Paris | 0.13
Nick Rimando | New York | Mc Lyon | Paris | 0.14
Nick Rimando | New York | Paul Adam | Rome | 0.13
Brad Davis | New York | Nail Knite | Paris | 0.13
Brad Davis | New York | Mc Lyon | Paris | 0.14
Brad Davis | New York | Paul Adam | Rome | 0.13
Graham Zusi | California | James Hoog | New York | 0.15
Graham Zusi | California | Nail Knite | Paris | 0.13
Graham Zusi | California | Mc Lyon | Paris | 0.14
Graham Zusi | California | Paul Adam | Rome | 0.13
Julian Green | London | James Hoog | New York | 0.15
Julian Green | London | Nail Knite | Paris | 0.13
Julian Green | London | Mc Lyon | Paris | 0.14
Julian Green | London | Paul Adam | Rome | 0.13
Fabian Johnson | Paris | James Hoog | New York | 0.15
Fabian Johnson | Paris | Paul Adam | Rome | 0.13
Geoff Cameron | Berlin | James Hoog | New York | 0.15
Geoff Cameron | Berlin | Nail Knite | Paris | 0.13
Geoff Cameron | Berlin | Mc Lyon | Paris | 0.14
Geoff Cameron | Berlin | Paul Adam | Rome | 0.13
Jozy Altidor | Moscow | James Hoog | New York | 0.15
Jozy Altidor | Moscow | Nail Knite | Paris | 0.13
Jozy Altidor | Moscow | Mc Lyon | Paris | 0.14
Jozy Altidor | Moscow | Paul Adam | Rome | 0.13
Brad Guzan | London | James Hoog | New York | 0.15
Brad Guzan | London | Nail Knite | Paris | 0.13
Brad Guzan | London | Mc Lyon | Paris | 0.14
Brad Guzan | London | Paul Adam | Rome | 0.13
(28 rows)
SELECT customers.cust_name as "Customer Name", customers.city as "Customer City", salesmen.name as "Salesman Name", salesmen.city as "Salesman City", salesmen.commission as "Commission"
FROM customers FULL OUTER JOIN salesmen
ON customers.salesman_id = salesmen.salesman_id
WHERE customers.city != salesmen.city AND
salesmen.commission > 0.12;
Customer Name | Customer City | Salesman Name | Salesman City | Commission
---------------+---------------+---------------+---------------+------------
Graham Zusi | California | Nail Knite | Paris | 0.13
Julian Green | London | Nail Knite | Paris | 0.13
Jozy Altidor | Moscow | Paul Adam | Rome | 0.13
(3 rows)