|
/*
mysql> Drop table Books;
Query OK, 0 rows affected (0.00 sec)
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.11 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.00 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.01 sec)
mysql> SELECT Name, Category, InStock, OnOrder
-> FROM Books
-> WHERE InStock>20 AND (Category IS NULL OR NOT (Category='Fiction'))
-> ORDER BY Name;
+-------+------------+---------+---------+
| Name | Category | InStock | OnOrder |
+-------+------------+---------+---------+
| From | NULL | 46 | 31 |
| Hello | Nonfiction | 21 | 32 |
| Poet | Nonfiction | 35 | 13 |
+-------+------------+---------+---------+
3 rows in set (0.00 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 InStock>20 AND (Category IS NULL OR NOT (Category='Fiction'))
ORDER BY Name;
|