(01).
mysql -u root -p
(02).
CREATE DATABASE school_items;
(03).
USE school_items;
(04).
CREATE TABLE item(itCode char(4) Primary key, itName varchar(25),
price decimal(9,2), imported tinyint(1), brand varchar(20));
(05).
INSERT INTO item values('IT10', 'Stapler', '240.00', 1,
'Mango'),('IT11', 'Pen', '60.00', 0, 'Atlas'),('IT12', 'Pencil', 40.00,
0, 'Atlas'),('IT13', 'Eraser', 20.00, 0, NULL),('IT15', 'Backpack',
'4000.00', 0, 'DSI'),('IT16', 'Notebook', 160.00, NULL, 'Atlas'),('IT17',
'Folder', 20.00, 1, 'Mango'),('IT18', 'Calculator', 2700.00, 1,
'Casio'),('IT19', 'Highlighter', 150.00, 0, NULL), ('IT20', 'Scissors',
100.00, 0, 'Mango'),('IT14', 'Flash Drive', 1500.00, 1, 'Kingston');
(06).
CREATE TABLE bookShop(bsCode char(4) Primary key, bsName
varchar(25), contact char(11), address text(50));
(07).
INSERT INTO bookShop VALUES('BS01', 'Sarasavi', '0112946058',
'Colombo 04'), ('BS02', 'Godage', NULL, 'Main Street'), ('BS03',
'Gunasena', '0912546893', NULL), ('BS04', 'Samudra', '0415670045',
'Dharmapala Mawatha');
(08).
CREATE TABLE distribute(itCode char(4), bsCode char(4), qty int,
date DATE);
INSERT INTO distribute VALUES('IT10', 'BS01', 30, '2023-04-08'),
('IT10', 'BS03', 3, '2023-04-02'), ('IT10', 'BS04', 10, '2023-04-25'),
('IT11', 'BS01', 25, NULL), ('IT11', 'BS02', 12, '2023-05-07'), ('IT13',
'BS04', 60, '2023-05-09'), ('IT16', 'BS02', 5, NULL);
1. SHOW TABLES;
2. DESCRIBE item;
3. EXIT
mysql -u root -p
USE school_items;
4. SELECT * FROM item;
5. SELECT * FROM bookShop;
6. SELECT * FROM distribute;
7. SELECT itName, price FROM item;
8. SELECT itCode FROM distribute;
9. SELECT DISTINCT itCode FROM distribute;
10. SELECT itName, price FROM item ORDER BY price DESC;
11. SELECT * FROM item WHERE price>1000.00;
12. SELECT * FROM item WHERE brand IN('Atlas', 'Mango', 'DSI');
13. SELECT itCodes FROM distribute WHERE bsCode='BS01';
14. SELECT itName FROM item WHERE itName LIKE 'F%';
15. SELECT itName FROM item WHERE itName LIKE '%er';
