You will take the attached joins.dump file and create 2 tables with data pasting

Question

You will take the attached joins.dump file and create 2 tables with data pasting the file's contents in an sql window in phpMyAdmin. You are to write a query for each of the following report items that I need:

Tell me who ordered the printer without just a where statement

Tell me who ordered the printer with an inner join

Tell me who ordered the printer with a left join

Tell me who ordered what

(Here is the source code of the join.dump file)

But may I also ask, what is the general purpose of a dump file? What is the proper way of opening/using it?

Regards, Gabriel.

11 CREATE TABLE IF NOT EXISTS joins customers customer id int (11) NOT NULL auto increment, name varchar (20) default NULL, PRIMARY KEY ('customer id') 5| ) ENGINE 1SAM DEFAULT CHARSET-1 at inl AUTO_INCREMENT-5 ; 81 -- Dumping data for table joins customers 10 11| INSERT INTO joins customers' ('customer id', 'name') VALUES 12 (1, John Smith', 13, Darryl Evans, 14 (3, "Mark Fydrich'), 15(4, 'Ron Guidry'): 16 17 CREATE TABLE IF NOT EXISTS joins orders ( 18 19 20 21 22 ENGINE MyISAM DEFAULT CHARSET latinl AUTO_INCREMENT 6 23 product id int (11) NOT NULL auto increment , product varchar (20) default NULL, customer id, int (11) default NULL, PRIMARY KEY ( product_id') 25 -- Dumping data for table joins orders 27 INSERT INTO joins-orders. ('product_id,product .,customer-id) VALUES 29, 'Printer', 1), 30 (2, Table', 4), 31 (3, 'Chair', 4) 32(4, 'Lamp', 2);

Explanation / Answer

The queries for the given scenario is mentioned below. In table joins_orders customer_id is not mentioned as foreign key but for applying any join it needs to be declared as foreign key referencing customer_id of joins_customers table.

1) Subquery in the where clause can be used to fetch the customers who ordered Printer.
SELECT name
FROM joins_customers
WHERE customer_id IN (SELECT customer_id FROM joins_orders WHERE product = 'Printer');

2) The inner join is used between the two tables to get the customers who ordered printer.
SELECT c.name
FROM joins_customers AS c
INNER JOIN joins_orders AS o
ON o.customer_id = c.customer_id
WHERE product = 'Printer';

3) With left join customer name is fetched who ordered printer
SELECT c.name
FROM joins_customers AS c
LEFT JOIN joins_orders AS o
ON o.customer_id = c.customer_id
WHERE product = 'Printer';

4)
SELECT c.name, o.product
FROM joins_customers AS c
INNER JOIN joins_orders AS o
ON o.customer_id = c.customer_id;

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.