|
/*
mysql> Drop table Transactions;
Query OK, 0 rows affected (0.10 sec)
mysql> CREATE TABLE Transactions (
-> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> OrderID INT NOT NULL,
-> DVDID SMALLINT NOT NULL,
-> DateOut DATE NOT NULL,
-> DateDue DATE NOT NULL,
-> DateIn DATE NOT NULL
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue)
-> VALUES (1, 1, CURDATE(), CURDATE()+3),
-> (1, 4, CURDATE(), CURDATE()+1),
-> (1, 8, CURDATE(), CURDATE()+2),
-> (2, 3, CURDATE(), CURDATE()+3),
-> (3, 4, CURDATE(), CURDATE()+4),
-> (3, 1, CURDATE(), CURDATE()+5),
-> (3, 7, CURDATE(), CURDATE()+6),
-> (4, 4, CURDATE(), CURDATE()+7),
-> (5, 3, CURDATE(), CURDATE()+8),
-> (6, 2, CURDATE(), CURDATE()+7),
-> (6, 1, CURDATE(), CURDATE()+6),
-> (7, 4, CURDATE(), CURDATE()+6),
-> (8, 2, CURDATE(), CURDATE()+5),
-> (8, 1, CURDATE(), CURDATE()+4),
-> (8, 3, CURDATE(), CURDATE()+4),
-> (9, 7, CURDATE(), CURDATE()+3),
-> (9, 1, CURDATE(), CURDATE()+3),
-> (10, 5, CURDATE(), CURDATE()+1),
-> (11, 6, CURDATE(), CURDATE()+2),
-> (11, 2, CURDATE(), CURDATE()+6),
-> (11, 8, CURDATE(), CURDATE()+5),
-> (12, 5, CURDATE(), CURDATE()+4),
-> (13, 7, CURDATE(), CURDATE()+3);SELECT ID, YEAR(DateOut) AS YearOu
t
Query OK, 23 rows affected (0.08 sec)
Records: 23 Duplicates: 0 Warnings: 0
-> FROM Transactions
-> WHERE ID>15
-> ORDER BY ID;
+----+---------+
| ID | YearOut |
+----+---------+
| 16 | 2005 |
| 17 | 2005 |
| 18 | 2005 |
| 19 | 2005 |
| 20 | 2005 |
| 21 | 2005 |
| 22 | 2005 |
| 23 | 2005 |
+----+---------+
8 rows in set (0.00 sec)
*/
Drop table Transactions;
CREATE TABLE Transactions (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
OrderID INT NOT NULL,
DVDID SMALLINT NOT NULL,
DateOut DATE NOT NULL,
DateDue DATE NOT NULL,
DateIn DATE NOT NULL
)
ENGINE=INNODB;
INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue)
VALUES (1, 1, CURDATE(), CURDATE()+3),
(1, 4, CURDATE(), CURDATE()+1),
(1, 8, CURDATE(), CURDATE()+2),
(2, 3, CURDATE(), CURDATE()+3),
(3, 4, CURDATE(), CURDATE()+4),
(3, 1, CURDATE(), CURDATE()+5),
(3, 7, CURDATE(), CURDATE()+6),
(4, 4, CURDATE(), CURDATE()+7),
(5, 3, CURDATE(), CURDATE()+8),
(6, 2, CURDATE(), CURDATE()+7),
(6, 1, CURDATE(), CURDATE()+6),
(7, 4, CURDATE(), CURDATE()+6),
(8, 2, CURDATE(), CURDATE()+5),
(8, 1, CURDATE(), CURDATE()+4),
(8, 3, CURDATE(), CURDATE()+4),
(9, 7, CURDATE(), CURDATE()+3),
(9, 1, CURDATE(), CURDATE()+3),
(10, 5, CURDATE(), CURDATE()+1),
(11, 6, CURDATE(), CURDATE()+2),
(11, 2, CURDATE(), CURDATE()+6),
(11, 8, CURDATE(), CURDATE()+5),
(12, 5, CURDATE(), CURDATE()+4),
(13, 7, CURDATE(), CURDATE()+3);
SELECT ID, YEAR(DateOut) AS YearOut
FROM Transactions
WHERE ID>15
ORDER BY ID;
|
