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`
Telegram: DB developers channel