/* Chapter 15 - Examples */


CREATE TABLE Manufacturers
(
   ManfID CHAR(8)NOT NULL PRIMARY KEY,
   ManfName VARCHAR(30) NOT NULL
)
ENGINE=INNODB;


INSERT INTO Manufacturers
VALUES ('abc123', 'ABC Manufacturing'),
('def456', 'DEF Inc.'),
('ghi789', 'GHI Corporation'),
('jkl123', 'JKL Limited'),
('mno456', 'MNO Company');


CREATE TABLE Parts
(
   PartID SMALLINT NOT NULL PRIMARY KEY,
   PartName VARCHAR(30) NOT NULL,
   ManfID CHAR(8) NOT NULL
)
ENGINE=INNODB;


INSERT INTO Parts
VALUES (101, 'DVD burner', 'abc123'),
(102, 'CD drive', 'jkl123'),
(103, '80-GB hard disk', 'mno456'),
(104, 'Mini-tower', 'ghi789'),
(105, 'Power supply', 'def456'),
(106, 'LCD monitor', 'mno456'),
(107, 'Zip drive', 'ghi789'),
(108, 'Floppy drive', 'jkl123'),
(109, 'Network adapter', 'def456'),
(110, 'Network hub', 'jkl123'),
(111, 'Router', 'mno456'),
(112, 'Sound card', 'ghi789'),
(113, 'Standard keyboard', 'mno456'),
(114, 'PS/2 mouse', 'jkl123'),
(115, '56-K modem', 'ghi789'),
(116, 'Display adapter', 'mno456'),
(117, 'IDE controller', 'def456');


SELECT PartName, ManfName
FROM Parts AS p, Manufacturers as m
WHERE p.ManfID = m.ManfID
ORDER BY PartName;


EXPLAIN SELECT PartName, ManfName
FROM Parts AS p, Manufacturers as m
WHERE p.ManfID = m.ManfID
ORDER BY PartName;