Question

Task 6.2.1. For each publisher with more than $2000 in purchase orders, list the customer id, name, credit code, and total of the orders. Use a WHERE clause to perform the join across the five required tables.

DROP TABLE publishers;
DROP TABLE po_items;
DROP TABLE bookjobs;
DROP TABLE items;
DROP TABLE pos;

CREATE TABLE publishers (
cust_id CHAR(3) NOT NULL,
name CHAR(10),
city CHAR(10),
phone CHAR(8),
creditcode CHAR(1),
PRIMARY KEY (cust_id)
);

CREATE TABLE bookjobs (
job_id CHAR(3) NOT NULL,
cust_id CHAR(3),
job_date DATE,
descr CHAR(10),
jobtype CHAR(1),
PRIMARY KEY (job_id),
FOREIGN KEY (cust_id) REFERENCES publishers (cust_id)
);

CREATE TABLE pos (
job_id CHAR(3) NOT NULL,
po_id CHAR(3) NOT NULL,
po_date DATE,
vendor_id CHAR(3),
PRIMARY KEY (job_id, po_id),
FOREIGN KEY (job_id) REFERENCES bookjobs (job_id)
);

CREATE TABLE items (
item_id CHAR(3) NOT NULL,
descr CHAR(10),
on_hand SMALLINT,
price DECIMAL(5,2),
PRIMARY KEY (item_id)
);

CREATE TABLE po_items (
job_id       CHAR(3) NOT NULL,
po_id       CHAR(3) NOT NULL,
item_id       CHAR(3) NOT NULL,
quantity     SMALLINT,
FOREIGN KEY (job_id) REFERENCES bookjobs (job_id),
FOREIGN KEY (job_id, po_id) REFERENCES pos (job_id, po_id),
FOREIGN KEY (item_id) REFERENCES items (item_id)
);

Answer 1

WHERE clause to perform the join across the five required tables.

SELECT

             customers.cust_fname, orders.order_id, orders.order_date

FROM

             customers INNER JOIN orders

             ON customer.cust_id = orders.cust_id;

Result:

cust_name	order_id	order_date
ABC Co.	123456	2005-06-20
ABC Co.	147893	2006-12-14
...	...	...
XYZ Ltd.	258963	2010-05-06

Interestingly, you can also write an inner join SQL query using WHERE clause to have the same effect as follows.

SELECT

             customers.cust_fname, orders.order_id, orders.order_date

FROM

             customers, orders

WHERE

             customers.cust_id = orders.cust_id;

In this case the join is done implicitly by the database optimizers. This type of query though functionally equivalent is highly discouraged due to its misleading nature as a simple SELECT query.

The INNER JOIN is the default join operation in MySQL databases; therefore, the keyword INNER is optional.

SELECT 

             customers.cust_fname, orders.order_id, orders.order_date 

FROM 

             customers LEFT OUTER JOIN orders 

ON customer.cust_id = orders.cust_id;

SELECT 

             customers.cust_fname, orders.order_id, orders.order_date 

FROM 

             customers RIGHT OUTER JOIN orders 

ON customer.cust_id = orders.cust_id;

SELECT 

             customers.cust_fname, customers.cust_lname

FROM 

             customers

UNION

SELECT 

             employees.emp_fname, employees.emp_lname

FROM 

             employees