CREATE TABLE Customers (
cust_id CHAR(10) PRIMARY KEY,
cust_name CHAR(50) NOT NULL,
cust_address CHAR(50) DEFAULT NULL,
cust_city CHAR(50) DEFAULT NULL,
cust_state CHAR(5) DEFAULT NULL,
cust_zip CHAR(10) DEFAULT NULL,
cust_country CHAR(50) DEFAULT NULL,
cust_contact CHAR(50) DEFAULT NULL,
cust_email CHAR(255) DEFAULT NULL
);
INSERT INTO Customers VALUES (1000000001, "Village Toys", "200 Maple Lane", "Detroit", "MI", 44444, "USA", "John Smith", "sales@villagetoys.com");
INSERT INTO Customers VALUES (1000000002, "Kids Place", "333 South Lake Drive", "Columbus", "OH", 43333, "USA", "Michelle Green", NULL);
INSERT INTO Customers VALUES (1000000003, "Fun4All", "1 Sunny Place", "Muncie", "PU", 42222, "USA", "Jim Jones", "jjones@fun4all.com");
INSERT INTO Customers VALUES (1000000004, "Fun4All", "829 Riverside Drive", "Phoenix", "AZ", 88888, "USA", "Deinse L. Stephens", "dstephens@fun4all.com");
INSERT INTO Customers VALUES (1000000005, "The Toy Store", "4545 53rd Streer", "Chicago", "IL", 54545, "USA", "Kim Howard", NULL);
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)
);
INSERT INTO OrderItems VALUES (20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems VALUES (20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems VALUES (20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems VALUES (20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems VALUES (20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems VALUES (20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems VALUES (20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems VALUES (20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems VALUES (20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems VALUES (20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems VALUES (20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems VALUES (20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems VALUES (20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems VALUES (20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems VALUES (20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems VALUES (20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems VALUES (20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems VALUES (20009, 3, 'BNBG03', 250, 2.49);
CREATE TABLE Orders (
order_num INT(11) PRIMARY KEY,
order_dare DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL
);
INSERT INTO Orders VALUES (20005, '2004-05-01 00:00:00', 1000000001);
INSERT INTO Orders VALUES (20006, '2004-01-12 00:00:00', 1000000003);
INSERT INTO Orders VALUES (20007, '2004-01-30 00:00:00', 1000000004);
INSERT INTO Orders VALUES (20008, '2004-02-03 00:00:00', 1000000005);
INSERT INTO Orders VALUES (20009, '2004-02-08 00:00:00', 1000000001);
CREATE TABLE Products (
prod_id CHAR(10) PRIMARY KEY,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(255) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc TEXT DEFAULT NULL
);
INSERT INTO Products VALUES ('BR01', 'BRS01', '8 inch teddy bear', '5.99', '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products VALUES ('BR02', 'BRS01', '12 inch teddy bear', '8.99', '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products VALUES ('BR03', 'BRS01', '18 inch teddy bear', '11.99', '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products VALUES ('BNBG01', 'DLL01', 'Fish bean bag toy', '3.49', 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products VALUES ('BNBG02', 'DLL01', 'Bird bean bag toy', '3.49', 'Bird bean bag toy, eggs are not included');
INSERT INTO Products VALUES ('BNBG03', 'DLL01', 'Rabbit bean bag toy', '3.49', 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products VALUES ('RGAN01', 'DLL01', 'Raggedy Ann', '4.99', '18 inch Raggedy Ann doll');
INSERT INTO Products VALUES ('RYL01', 'FNG01', 'King doll', '9.49', '12 inch king doll with royal garments and crown');
INSERT INTO Products VALUES ('RYL02', 'FNG01', 'Queen doll', '9.49', '12 inch queen doll with royal garments and crown');
CREATE TABLE Vendors (
vend_id CHAR(10) PRIMARY KEY,
vend_name CHAR(50) NOT NULL,
vend_address CHAR(50) DEFAULT NULL,
vend_city CHAR(50) DEFAULT NULL,
vend_state CHAR(5) DEFAULT NULL,
vend_zip CHAR(10) DEFAULT NULL,
vend_country CHAR(50) DEFAULT NULL
);
INSERT INTO Vendors VALUES ('BRS01', 'Bears R Us', '123 Main Street', 'Bear Town', 'MI', 44444, 'USA');
INSERT INTO Vendors VALUES ('BRE02', 'Bear Emporium', '500 Park Street', 'Anytown', 'OH', 44333, 'USA');
INSERT INTO Vendors VALUES ('DLL01', 'Doll House Inc.', '555 High Street', 'Dollsville', 'CA', 9999, 'USA');
INSERT INTO Vendors VALUES ('FRB01', 'Furball Inc.', '1000 5th Avenue', 'New York', 'NY', 1111, 'USA');
INSERT INTO Vendors VALUES ('FNG01', 'Fun and Games', '42 Galaxy Road', 'London', NULL, 'N16 6PS', 'England');
INSERT INTO Vendors VALUES ('JTS01', 'Jouets et ours', '1 Rue Amusement', 'Paris', NULL, 45678, 'France');
SELECT '1 часть' AS 'Задачи по SQL';
#Задание 1.1
SELECT * FROM Customers;
#Задание 1.2
SELECT cust_id from Customers;
#Задание 1.3
SELECT prod_id, prod_name, prod_price FROM Products;
#Задание 1.4
SELECT prod_id FROM Products order by prod_id;
#Задание 1.5
SELECT prod_id, prod_name, prod_price from Products order by prod_name, prod_price;
SELECT '2 часть' AS 'Задачи по SQL';
#Задание 2.1
select prod_id, prod_price, prod_name from Products order by 2,3;
#Задание 2.2
select prod_id, prod_name, prod_price from Products order by prod_name desc;
#Задание 2.3
select prod_id, prod_name, prod_price from Products order by prod_name desc, prod_price asc;
#Задание 2.4
select prod_id, prod_name, prod_price from Products where prod_price = 3.49;
#Задание 2.5
select prod_id, prod_name, prod_price from Products where prod_price = 11.99 and vend_id = "BRS01";
SELECT '3 часть' AS 'Задачи по SQL';
#Задание 3.1
SELECT prod_id, prod_name, prod_price FROM Products WHERE prod_name = '8 inch teddy bear' OR prod_price = '3.49';
#Задание 3.2
SELECT prod_id, prod_name, prod_desc FROM Products WHERE prod_price IN ('3.49', '9.49');
#Задание 3.3
SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01';
#Задание 3.4
SELECT prod_price FROM Products WHERE prod_name LIKE 'Fish bean%';
#Задание 3.5
SELECT prod_id, prod_name, prod_price FROM Products WHERE prod_name LIKE '_____bean bag toy';
SELECT '4 часть' AS 'Задачи по SQL';
#Задание 4.1
SELECT cust_address FROM Customers WHERE cust_address RLIKE '[JM%]';
#Задание 4.2
SELECT CONCAT(vend_name, vend_country) FROM Vendors ORDER BY vend_name;
#Задание 4.3
SELECT RTRIM(CONCAT(vend_name, vend_address)) FROM Vendors ORDER BY vend_name;
#Задание 4.4
#Задание 4.5
Telegram: DB developers channel