|
/*
mysql> Drop table Books;
mysql> CREATE TABLE Books
-> (
-> ID SMALLINT NOT NULL PRIMARY KEY,
-> Name VARCHAR(40) NOT NULL,
-> Category VARCHAR(15),
-> InStock SMALLINT NOT NULL,
-> OnOrder SMALLINT NOT NULL
-> );
Query OK, 0 rows affected (0.45 sec)
mysql> INSERT INTO Books
-> VALUES (101, 'On', 'Nonfiction', 13, 11),
-> (102, 'News', 'Fiction', 15, 21),
-> (103, 'Hello', 'Nonfiction', 21, 32),
-> (104, 'Poet', 'Nonfiction', 35, 13),
-> (105, 'Dunces', 'Fiction', 5, 35),
-> (106, 'One', 'Fiction', 28, 14),
-> (107, 'From', NULL, 46, 31);
Query OK, 7 rows affected (0.36 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from Books;
+-----+--------+------------+---------+---------+
| ID | Name | Category | InStock | OnOrder |
+-----+--------+------------+---------+---------+
| 101 | On | Nonfiction | 13 | 11 |
| 102 | News | Fiction | 15 | 21 |
| 103 | Hello | Nonfiction | 21 | 32 |
| 104 | Poet | Nonfiction | 35 | 13 |
| 105 | Dunces | Fiction | 5 | 35 |
| 106 | One | Fiction | 28 | 14 |
| 107 | From | NULL | 46 | 31 |
+-----+--------+------------+---------+---------+
7 rows in set (0.14 sec)
mysql> SELECT Name, Category, InStock, OnOrder
-> FROM Books
-> WHERE Category='Fiction' AND (InStock+OnOrder)>40
-> ORDER BY Name;
+------+----------+---------+---------+
| Name | Category | InStock | OnOrder |
+------+----------+---------+---------+
| One | Fiction | 28 | 14 |
+------+----------+---------+---------+
1 row in set (0.05 sec)
*/
Drop table Books;
CREATE TABLE Books
(
ID SMALLINT NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL,
Category VARCHAR(15),
InStock SMALLINT NOT NULL,
OnOrder SMALLINT NOT NULL
);
INSERT INTO Books
VALUES (101, 'On', 'Nonfiction', 13, 11),
(102, 'News', 'Fiction', 15, 21),
(103, 'Hello', 'Nonfiction', 21, 32),
(104, 'Poet', 'Nonfiction', 35, 13),
(105, 'Dunces', 'Fiction', 5, 35),
(106, 'One', 'Fiction', 28, 14),
(107, 'From', NULL, 46, 31);
select * from Books;
SELECT Name, Category, InStock, OnOrder
FROM Books
WHERE Category='Fiction' AND (InStock+OnOrder)>40
ORDER BY Name;
|