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');
Telegram: DB developers channel