SQLize | PHPize | SQLtest

DROP TABLE IF EXISTS `Vendors`; CREATE TABLE `Vendors` ( `vend_id` CHAR(10) NOT NULL PRIMARY KEY , `vend_name` CHAR(50) NOT NULL , `vend_address` CHAR(50) NULL DEFAULT NULL , `vend_city` CHAR(50) NULL DEFAULT NULL , `vend_state` CHAR(5) NULL DEFAULT NULL , `vend_zip` CHAR(10) NULL DEFAULT NULL , `vend_country` CHAR(50) NULL DEFAULT NULL ); INSERT INTO `Vendors` VALUES ('BRS01', 'Bears R Us', '123 Main Street', 'Bear Town', 'MI', '44444', 'USA'), ('BRE02', 'Bear Emporium', '500 Park Street', 'Anytown', 'OH', '44333', 'USA'), ('DLL01', 'Doll House Inc.', '555 High Street', 'Dollsville', 'CA', '99999', 'USA'), ('FRB01', 'Furball Inc.', '1000 5th Avenue', 'New York', 'NY', '11111', 'USA'), ('FNG01', 'Fun and Games', '42 Galaxy Road', 'London', NULL, 'N16 6PS', 'England'), ('JTS01', 'Jouets et ours', '1 Rue Amusement', 'Paris', NULL, '45678', 'France') ; SELECT "`Vendors` Table Content:" FROM `Vendors` WHERE 1=0; SELECT * FROM `Vendors`; DROP TABLE IF EXISTS `Customers`; CREATE TABLE `Customers` ( `cust_id` char(10) NOT NULL PRIMARY KEY , `cust_name` char(50) NOT NULL , `cust_address` char(50) NULL DEFAULT NULL , `cust_city` char(50) NULL DEFAULT NULL , `cust_state` char(5) NULL DEFAULT NULL , `cust_zip` char(10) NULL DEFAULT NULL , `cust_country` char(50) NULL DEFAULT NULL , `cust_contact` char(50) NULL DEFAULT NULL , `cust_email` char(255) NULL DEFAULT NULL ); INSERT INTO `Customers` VALUES ('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'), ('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green', NULL), ('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie ', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com'), ('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com'), ('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard', NULL) ; SELECT "`Customers` Table Content:" FROM `Customers` WHERE 1=0; SELECT * FROM `Customers`; DROP TABLE IF EXISTS `Orders`; CREATE TABLE `Orders` ( `order_num` INT(11) NOT NULL PRIMARY KEY , `order_date` DATETIME NOT NULL , `cust_id` CHAR(10) NOT NULL , FOREIGN KEY (`cust_id`) REFERENCES `Customers` (`cust_id`) ); INSERT INTO `Orders` VALUES (20005, '2004-05-01 00:00:00', '1000000001'), (20006, '2004-01-12 00:00:00', '1000000003'), (20007, '2004-01-30 00:00:00', '1000000004'), (20008, '2004-02-03 00:00:00', '1000000005'), (20009, '2004-02-08 00:00:00', '1000000001') ; SELECT "`Orders` Table Content:" FROM `Orders` WHERE 1=0; SELECT * FROM `Orders`; DROP TABLE IF EXISTS `Products`; CREATE TABLE `Products` ( `prod_id` CHAR(10) NOT NULL PRIMARY KEY , `vend_id` CHAR(10) NOT NULL , `prod_name` CHAR(255) NOT NULL , `prod_price` DECIMAL(8,2) NOT NULL , `prod_desc` TEXT NULL DEFAULT NULL , FOREIGN KEY (`vend_id`) REFERENCES `Vendors` (`vend_id`) ); INSERT INTO `Products` VALUES ('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket'), ('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket'), ('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket'), ('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it'), ('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included'), ('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots'), ('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll'), ('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown'), ('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown') ; SELECT "`Products` Table Content:" FROM `Products` WHERE 1=0; SELECT * FROM `Products`; DROP TABLE IF EXISTS `OrderItems`; CREATE TABLE `OrderItems` ( `order_num` INT(11) NOT NULL , `order_item` INT(11) NOT NULL , `prod_id` CHAR(10) NOT NULL , `quantity` INT(11) NOT NULL , `item_price` DECIMAL(8,2) NOT NULL , PRIMARY KEY (`order_num`, `order_item`) , FOREIGN KEY (`order_num`) REFERENCES `Orders` (`order_num`) , FOREIGN KEY (`prod_id`) REFERENCES `Products` (`prod_id`) ); INSERT INTO `OrderItems` VALUES (20005, 1, 'BR01', 100, 5.49), (20005, 2, 'BR03', 100, 10.99), (20006, 1, 'BR01', 20, 5.99), (20006, 2, 'BR02', 10, 8.99), (20006, 3, 'BR03', 10, 11.99), (20007, 1, 'BR03', 50, 11.49), (20007, 2, 'BNBG01', 100, 2.99), (20007, 3, 'BNBG02', 100, 2.99), (20007, 4, 'BNBG03', 100, 2.99), (20007, 5, 'RGAN01', 50, 4.49), (20008, 1, 'RGAN01', 5, 4.99), (20008, 2, 'BR03', 5, 11.99), (20008, 3, 'BNBG01', 10, 3.49), (20008, 4, 'BNBG02', 10, 3.49), (20008, 5, 'BNBG03', 10, 3.49), (20009, 1, 'BNBG01', 250, 2.49), (20009, 2, 'BNBG02', 250, 2.49), (20009, 3, 'BNBG03', 250, 2.49) ; SELECT "`OrderItems` Table Content:" FROM `OrderItems` WHERE 1=0; SELECT * FROM `OrderItems`; -- ПРАКТИКА 1 -- 1 Задание SELECT * FROM Vendors; SELECT * FROM Customers; SELECT * FROM Orders; SELECT * FROM Products; SELECT * FROM OrderItems; -- 2 Задание SELECT vend_id FROM Vendors; SELECT cust_name FROM Customers; SELECT order_num FROM Orders; SELECT prod_name FROM Products; SELECT prod_id FROM OrderItems; -- 3 Задание SELECT prod_id, vend_id, prod_name FROM Products; SELECT vend_id, prod_price, prod_name FROM Products; SELECT vend_id, prod_price, prod_desc FROM Products; -- 4 Задание SELECT prod_id FROM Products ORDER BY prod_id; SELECT prod_name FROM Products ORDER BY prod_name; SELECT prod_price FROM Products ORDER BY prod_price; -- 5 Задание SELECT prod_id, vend_id, prod_name FROM Products ORDER BY vend_id, prod_name; SELECT vend_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name; SELECT vend_id, prod_price, prod_desc FROM Products ORDER BY prod_price, prod_desc; -- ПРАКТИКА 2 -- 1 Задание SELECT prod_id, vend_id, prod_name FROM `Products` ORDER BY 1, 3; SELECT vend_id, prod_price, prod_name FROM `Products` ORDER BY 1, 2; SELECT vend_id, prod_price, prod_desc FROM `Products` ORDER BY 2, 3; -- 2 Задание SELECT prod_id, vend_id, prod_name FROM `Products` ORDER BY vend_id DESC; SELECT vend_id, prod_price, prod_name FROM `Products` ORDER BY prod_price DESC; SELECT vend_id, prod_price, prod_desc FROM `Products` ORDER BY prod_desc DESC; -- 3 Задание SELECT prod_id, vend_id, prod_name FROM `Products` ORDER BY vend_id DESC, prod_name; SELECT vend_id, prod_price, prod_name FROM `Products` ORDER BY prod_price DESC, prod_name; SELECT vend_id, prod_price, prod_desc FROM `Products` ORDER BY prod_price DESC, prod_desc; -- 4 Задание SELECT prod_id, vend_id, prod_name FROM `Products` WHERE prod_name = '18 inch teddy bear'; SELECT vend_id, prod_price, prod_name FROM `Products` WHERE prod_price = '5.99'; SELECT vend_id, prod_price, prod_desc FROM `Products` WHERE prod_price = '11.99'; -- 5 Задание SELECT prod_id, vend_id, prod_name FROM `Products` WHERE prod_id RLIKE 'BNBG0[1-3]' AND prod_name NOT LIKE '%fish%'; SELECT vend_id, prod_price, prod_name FROM `Products` WHERE prod_price BETWEEN 3 and 6 AND prod_name LIKE '%bear'; SELECT vend_id, prod_price, prod_desc FROM `Products` WHERE prod_price > 6 AND prod_desc LIKE '%bear%'; -- ПРАКТИКА 3 -- 1 Задание SELECT prod_id, vend_id, prod_name FROM Products WHERE prod_id RLIKE 'BNBG0[1-3]' AND prod_name NOT LIKE '%fish%'; SELECT vend_id, prod_price, prod_name FROM Products WHERE prod_price BETWEEN 3 and 6 AND prod_name LIKE '%bear'; SELECT vend_id, prod_price, prod_desc FROM Products WHERE prod_price > 6 AND prod_desc LIKE '%bear%'; -- 2 Задание SELECT prod_id, vend_id, prod_name FROM Products WHERE vend_id IN ('DLL01','FNG01'); SELECT vend_id, prod_price, prod_name FROM Products WHERE prod_price IN ('5.99','9.49'); -- Использовать IN для строки с описанием не очень продуктивно. SELECT vend_id, prod_price, prod_desc FROM Products WHERE prod_desc IN ('12 inch teddy bear, comes with cap and jacket'); -- 3 Задание SELECT prod_name FROM Products WHERE prod_price NOT IN (3.49); SELECT prod_name FROM Products WHERE prod_price NOT IN (9.49); SELECT prod_name FROM Products WHERE prod_price NOT IN (8.99); -- 4 Задание SELECT prod_price FROM Products WHERE prod_name LIKE '%toy'; SELECT prod_price FROM Products WHERE prod_name LIKE '%bear'; SELECT prod_price FROM Products WHERE prod_name LIKE '%doll'; -- 5 Задание SELECT prod_name FROM Products WHERE prod_name LIKE 'Raggedy ___'; SELECT prod_name FROM Products WHERE prod_name LIKE '_% inch teddy bear'; -- Из-за процента, нижние подчёркивание становится ненужным SELECT prod_name FROM Products WHERE prod_name LIKE '___% bean bag toy'; -- ПРАКТИКА 4 -- 1 Задание SELECT cust_name FROM Customers WHERE cust_name RLIKE '[IAO]'; SELECT cust_name FROM Customers WHERE cust_name RLIKE '[1-4]'; SELECT cust_name FROM Customers WHERE cust_name RLIKE '[DK%]'; -- 2 Задание SELECT CONCAT(vend_zip,' ',vend_address) AS full_address_1 FROM Vendors ORDER BY vend_zip; SELECT CONCAT(vend_state,' ',vend_city) AS full_address_1 FROM Vendors ORDER BY vend_state; SELECT CONCAT(vend_country,' ',vend_zip) AS full_address_1 FROM Vendors ORDER BY vend_country; -- 3 Задание SELECT RTRIM(CONCAT(vend_zip,' ',vend_address,' ')) AS full_address_2 FROM Vendors ORDER BY vend_zip; SELECT RTRIM(CONCAT(vend_state,' ',vend_city,' ')) AS full_address_2 FROM Vendors ORDER BY vend_state; SELECT RTRIM(CONCAT(vend_country,' ',vend_zip,' ')) AS full_address_2 FROM Vendors ORDER BY vend_country; -- 4 Задание -- (Уже выполнено) Нужно было добавить AS, -- что уже было сделано на предыдущих заданиях -- 5 Задание SELECT prod_id, quantity, item_price, quantity-item_price AS expanded_data FROM OrderItems; SELECT prod_id, quantity, item_price, quantity/order_item AS expanded_data FROM OrderItems; SELECT prod_id, quantity, item_price, quantity-order_item-item_price AS expanded_data FROM OrderItems; -- ПРАКТИКА 5 -- 1 Задание SELECT vend_name, RIGHT(vend_name,5) AS vend_name_upcase FROM `Vendors` ORDER BY vend_name; SELECT vend_name, RTRIM(vend_name) AS vend_name_upcase FROM `Vendors` ORDER BY vend_name; SELECT vend_name, SOUNDEX(vend_name) AS vend_name_upcase FROM `Vendors` ORDER BY vend_name; -- 2 Задание SELECT order_num FROM `Orders` WHERE DAY(order_date) = 08; SELECT order_num FROM `Orders` WHERE HOUR(order_date) = 00; SELECT order_num FROM `Orders` WHERE MINUTE(order_date) = 00; -- 3 Задание -- При изначальных условиях, выводом должен был быть null SELECT AVG(prod_price) AS avg_price FROM `Products` WHERE vend_id='BRS01' OR vend_id='FNG01'; SELECT AVG(prod_price) AS avg_price FROM `Products` WHERE vend_id='FNG01' OR vend_id='DLL01'; SELECT AVG(prod_price) AS avg_price FROM `Products` WHERE prod_id='RYL01' OR prod_id='RYL02'; -- 4 Задание -- Тоже приколдес, если изначально брать cust_id, то будет null SELECT COUNT(cust_id) AS cust_count FROM `Customers` WHERE cust_state RLIKE '[KD%]'; SELECT COUNT(cust_id) AS cust_count FROM `Customers` WHERE cust_state RLIKE '[%J%]'; SELECT COUNT(cust_id) AS cust_count FROM `Customers` WHERE cust_state RLIKE '[%L%]'; -- 5 Задание SELECT MAX(prod_price) AS max_price FROM `Products` WHERE prod_name RLIKE '[%K]'; SELECT MAX(prod_price) AS max_price FROM `Products` WHERE prod_name RLIKE '[%o]'; SELECT MAX(prod_price) AS max_price FROM `Products` WHERE prod_id RLIKE 'BNBG0[1-3]'; -- ПРАКТИКА 6 -- 1 Задание SELECT MIN(prod_price) AS min_price FROM `Products` WHERE prod_name RLIKE '[%K]'; SELECT MIN(prod_price) AS min_price FROM `Products` WHERE prod_name RLIKE '[%o]'; SELECT MIN(prod_price) AS min_price FROM `Products` WHERE prod_id RLIKE 'BNBG0[1-3]'; -- 2 Задание SELECT SUM(quantity) AS item_ordered FROM `OrderItems` WHERE prod_id = 'BR01'; SELECT SUM(quantity) AS item_ordered FROM `OrderItems` WHERE prod_id = 'BR03'; SELECT SUM(quantity) AS item_ordered FROM `OrderItems` WHERE prod_id = 'RGAN01'; -- 3 Задание SELECT AVG(DISTINCT prod_price) AS avg_price FROM `Products` WHERE vend_id = 'DLL01' OR vend_id = 'FNG01'; SELECT AVG(DISTINCT prod_price) AS avg_price FROM `Products` WHERE vend_id = 'BRS01' OR vend_id = 'FNG01'; SELECT AVG(DISTINCT prod_price) AS avg_price FROM `Products` WHERE vend_id <> 'BRS01'; -- 4 Задание SELECT COUNT(*) AS num_items , MIN(prod_price) AS price_min , MAX(prod_price) AS price_max , AVG(prod_price) AS price_avg FROM `Products` WHERE vend_id = 'DLL01' OR vend_id = 'FNG01'; SELECT COUNT(*) AS num_items , MIN(prod_price) AS price_min , MAX(prod_price) AS price_max , AVG(prod_price) AS price_avg FROM `Products` WHERE vend_id = 'BRS01' OR vend_id = 'FNG01'; SELECT COUNT(*) AS num_items , MIN(prod_price) AS price_min , MAX(prod_price) AS price_max , AVG(prod_price) AS price_avg FROM `Products` WHERE vend_id <> 'BRS01'; -- 5 Задание SELECT COUNT(vend_id) AS num_prods FROM `Products` GROUP BY `vend_id` -- ПРАКТИКА 7 -- 1 Задание SELECT cust_id, COUNT(cust_id) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(cust_id) >= 2; -- 2 Задание SELECT vend_id, COUNT(vend_id) AS num_prods FROM Products WHERE prod_price >= 4.00 GROUP BY vend_id HAVING COUNT(vend_id) >= 2; -- 3 Задание SELECT order_num, COUNT(order_num) AS items FROM OrderItems # WHERE item_price > 5.00 -- Фильтруем до группировки, но в условии такого нет. GROUP BY order_num HAVING COUNT(order_num) >= 3 ORDER BY items, order_num; -- 4 Задание SELECT o.cust_id, o.order_num FROM Orders AS o JOIN OrderItems AS oi ON o.order_num = oi.order_num WHERE oi.prod_id = 'RGAN01'; -- 5 Задание SELECT c.cust_name, c.cust_state, (SELECT COUNT(*) FROM Orders o WHERE o.cust_id = c.cust_id) AS orders FROM Customers AS c ORDER BY c.cust_name; -- На всякий случай SELECT c.cust_name, c.cust_state, COUNT(o.order_num) FROM Customers AS c JOIN Orders AS o ON o.cust_id = c.cust_id ORDER BY cust_name; -- ПРАКТИКА 8 -- 1 Задание SELECT v.vend_name, p.prod_name, p.prod_price FROM Vendors AS v JOIN Products AS p ON v.vend_id = p.vend_id; -- 2 Задание SELECT p.prod_name, v.vend_name, p.prod_price, oi.quantity FROM OrderItems AS oi JOIN Products AS p ON oi.prod_id = p.prod_id JOIN Vendors AS v ON p.vend_id = v.vend_id WHERE oi.order_num = 20007; -- 3 Задание SELECT c.cust_name, c.cust_contact FROM OrderItems AS oi JOIN Orders AS o ON oi.order_num = o.order_num JOIN Customers AS c ON c.cust_id = o.cust_id WHERE oi.prod_id = 'RGAN01'; -- 4 Задание SELECT c1.cust_id, c1.cust_name, c1.cust_contact FROM Customers AS c1 JOIN Customers AS c2 ON c1.cust_name = c2.cust_name WHERE c2.cust_contact = 'Jim Jones'; -- 5 Задание SELECT o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price FROM Customers AS c JOIN Orders AS o ON c.cust_id = o.cust_id JOIN OrderItems AS oi ON oi.order_num = o.order_num WHERE oi.prod_id = 'RGAN01'; -- ПРАКТИКА 9 -- 1 Задание SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id; SELECT Customers.cust_id, Orders.order_num FROM Customers RIGHT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id; -- 2 Задание SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MI') UNION # UNION убирает дубликаты SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name = 'Fun4All'; -- 3 Задание SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MI') UNION ALL SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name = 'Fun4All'; -- 4 Задание INSERT INTO Customers ( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email ) VALUES ( '1000000006', 'Тоу Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL ); INSERT INTO Customers ( cust_id, cust_name, cust_city, cust_state, cust_zip, cust_country ) VALUES ( '1000000007', 'Mystery Buyer', 'Unknown City', '??', '00000', 'Nowhere' ); -- 5 Задание CREATE TABLE CustNew ( cust_id CHAR(10), cust_name CHAR(50), cust_contact CHAR(50), cust_email CHAR(255), cust_address CHAR(50), cust_city CHAR(50), cust_state CHAR(5), cust_zip CHAR(10), cust_country CHAR(50) ); INSERT INTO CustNew VALUES ('1000000008', 'Default Name' ,'Alice Moore', 'alice@example.com', '1 Elm Street', 'Springfield', 'IL', '62701', 'USA'), ('1000000009', 'Default Name' ,'Bob Stone', 'bob@example.com', '99 Oak Avenue', 'Dayton', 'OH', '45410', 'USA'); INSERT INTO Customers ( cust_id, cust_name ,cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country ) SELECT cust_id, cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country FROM CustNew; SELECT * FROM Customers WHERE cust_id IN ('1000000008', '1000000009'); -- ПРАКТИКА 10 -- 1 Задание INSERT INTO Customers (cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES ('1000000006', 'Night Corp', 'Elm Street 13', 'Springfield', 'IL', '66666', 'USA', 'Freddy Krueger', 'freddy@nightmares.com'); SELECT * FROM Customers; DELETE FROM Customers WHERE cust_id = '1000000006'; SELECT * FROM Customers; -- 2 Задание # Таблица Products у нас уже есть CREATE TABLE Products1 ( prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, prod_price DECIMAL(8,2) NOT NULL, prod_desc TEXT(1000) ); DESCRIBE Products1; -- 3 Задание CREATE TABLE Products2 ( prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, prod_price DECIMAL(8,2) NOT NULL DEFAULT 0.00, prod_desc VARCHAR(1000) DEFAULT 'Нет описания' ); -- 4 Задание ALTER TABLE Vendors ADD vend_phone CHAR(20); SELECT * FROM Vendors; ALTER TABLE Vendors DROP COLUMN vend_phone; SELECT * FROM Vendors; -- 5 Задание # Из-за свойств, удаление Vendors невозможно без удаления зависимостей. #DROP TABLE IF EXISTS OrderItems; #DROP TABLE IF EXISTS Orders; #DROP TABLE IF EXISTS Products; #DROP TABLE IF EXISTS Vendors; #SHOW TABLES; -- 6 Задание CREATE VIEW ProductCustomers AS SELECT c.cust_name, c.cust_contact, oi.prod_id FROM Customers c JOIN Orders o ON c.cust_id = o.cust_id JOIN OrderItems oi ON o.order_num = oi.order_num; SELECT * FROM ProductCustomers; -- 7 Задание CREATE VIEW CustomerEmailList AS SELECT cust_id, cust_name, cust_email FROM Customers WHERE cust_email IS NOT NULL; SELECT * FROM CustomerEmailList; -- 8 Задание SELECT prod_id, quantity, quantity * item_price AS total_price FROM OrderItems WHERE order_num = 20008;