/*
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2004-01-12 12:30:00 |
| 1002 | 101 | 1 | 2001-02-12 12:31:00 |
| 1003 | 103 | 2 | 2002-03-12 12:34:00 |
| 1004 | 104 | 3 | 2003-04-12 12:36:00 |
| 1005 | 102 | 1 | 2004-05-12 12:41:00 |
| 1006 | 103 | 2 | 2001-06-12 12:59:00 |
| 1007 | 101 | 1 | 2002-07-12 13:01:00 |
| 1008 | 103 | 1 | 2003-08-12 13:02:00 |
| 1009 | 102 | 4 | 2004-09-12 13:22:00 |
| 1010 | 101 | 2 | 2005-11-12 13:30:00 |
| 1011 | 103 | 1 | 2006-12-12 13:32:00 |
| 1012 | 105 | 1 | 2001-02-12 13:40:00 |
| 1013 | 106 | 2 | 2002-04-12 13:44:00 |
| 1014 | 103 | 1 | 2003-06-12 14:01:00 |
| 1015 | 106 | 1 | 2005-01-12 14:05:00 |
| 1016 | 104 | 2 | 2003-11-12 14:28:00 |
| 1017 | 105 | 1 | 2002-03-12 14:31:00 |
| 1018 | 102 | 1 | 2001-05-12 14:32:00 |
| 1019 | 106 | 3 | 2003-07-12 14:49:00 |
| 1020 | 103 | 1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)
mysql> DELETE Orders.*
-> FROM Books, Orders
-> WHERE Books.BookID=Orders.BookID
-> AND Books.BookName='News';
Query OK, 3 rows affected (0.05 sec)
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2004-01-12 12:30:00 |
| 1002 | 101 | 1 | 2001-02-12 12:31:00 |
| 1003 | 103 | 2 | 2002-03-12 12:34:00 |
| 1004 | 104 | 3 | 2003-04-12 12:36:00 |
| 1006 | 103 | 2 | 2001-06-12 12:59:00 |
| 1007 | 101 | 1 | 2002-07-12 13:01:00 |
| 1008 | 103 | 1 | 2003-08-12 13:02:00 |
| 1010 | 101 | 2 | 2005-11-12 13:30:00 |
| 1011 | 103 | 1 | 2006-12-12 13:32:00 |
| 1012 | 105 | 1 | 2001-02-12 13:40:00 |
| 1013 | 106 | 2 | 2002-04-12 13:44:00 |
| 1014 | 103 | 1 | 2003-06-12 14:01:00 |
| 1015 | 106 | 1 | 2005-01-12 14:05:00 |
| 1016 | 104 | 2 | 2003-11-12 14:28:00 |
| 1017 | 105 | 1 | 2002-03-12 14:31:00 |
| 1019 | 106 | 3 | 2003-07-12 14:49:00 |
| 1020 | 103 | 1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
17 rows in set (0.00 sec)
*/
Drop table Books;
Drop table Orders;
CREATE TABLE Books
(
BookID SMALLINT NOT NULL PRIMARY KEY,
BookName VARCHAR(40) NOT NULL,
InStock SMALLINT NOT NULL
)
ENGINE=INNODB;
CREATE TABLE Orders
(
OrderID SMALLINT NOT NULL PRIMARY KEY,
BookID SMALLINT NOT NULL,
Quantity TINYINT (40) NOT NULL DEFAULT 1,
DateOrdered TIMESTAMP,
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;
INSERT INTO Orders VALUES (1001, 103, 1, '2004-01-12 12:30:00'),
(1002, 101, 1, '2001-02-12 12:31:00'),
(1003, 103, 2, '2002-03-12 12:34:00'),
(1004, 104, 3, '2003-04-12 12:36:00'),
(1005, 102, 1, '2004-05-12 12:41:00'),
(1006, 103, 2, '2001-06-12 12:59:00'),
(1007, 101, 1, '2002-07-12 13:01:00'),
(1008, 103, 1, '2003-08-12 13:02:00'),
(1009, 102, 4, '2004-09-12 13:22:00'),
(1010, 101, 2, '2005-11-12 13:30:00'),
(1011, 103, 1, '2006-12-12 13:32:00'),
(1012, 105, 1, '2001-02-12 13:40:00'),
(1013, 106, 2, '2002-04-12 13:44:00'),
(1014, 103, 1, '2003-06-12 14:01:00'),
(1015, 106, 1, '2005-01-12 14:05:00'),
(1016, 104, 2, '2003-11-12 14:28:00'),
(1017, 105, 1, '2002-03-12 14:31:00'),
(1018, 102, 1, '2001-05-12 14:32:00'),
(1019, 106, 3, '2003-07-12 14:49:00'),
(1020, 103, 1, '2004-01-12 14:51:00');
INSERT INTO Books VALUES (101, 'Writing', 12),
(102, 'News', 17),
(103, 'Angels', 23),
(104, 'Poet', 32),
(105, 'Dunces', 6),
(106, 'Solitude', 28);
select * from Orders;
DELETE Orders.*
FROM Books, Orders
WHERE Books.BookID=Orders.BookID
AND Books.BookName='News';
select * from Orders;