mysql -u root -p mysql -h localhost -uroot -p cis411 -e "show tables"; mysql -h localhost -uroot -p cis411 -e "SELECT * FROM products WHERE product_inventory=0 ORDER BY product_price DESC LIMIT 3"; SELECT * FROM products WHERE product_inventory=0 ORDER BY product_price ASC; SELECT * FROM products WHERE product_inventory=0 ORDER BY product_price DESC LIMIT 3; SELECT * FROM products WHERE product_inventory=0 AND product_price > 4 AND product_price < 20; SELECT * FROM products WHERE product_category_code='bars' ORDER BY product_price ASC; SELECT * FROM products WHERE product_category_code='bars' ORDER BY product_price ASC; SELECT * FROM products WHERE product_category_code='bars' ORDER BY product_inventory DESC; SELECT * FROM products WHERE product_description LIKE '%bar%'; SELECT * FROM products, ref_product_categories WHERE products.product_category_code = ref_product_categories.product_category_code ORDER BY products.product_category_code ASC SELECT product_id, product_price, product_category_description FROM products, ref_product_categories WHERE products.product_category_code = ref_product_categories.product_category_code ORDER BY products.product_category_code ASC; 24 records INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (0, 0, CURRENT_TIMESTAMP,'10.30'); INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (1, 1, NOW(),'10.30'); INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (2, 2, NOW(),'10.30'); INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (3, 3, NOW(),'10.30'); INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (4, 4, NOW(),'10.30'); INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (5, 5, NOW(),'10.30'); INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (6, 6, NOW(),'10.30'); INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (7, 7, NOW(),'10.30'); INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (8, 8, NOW(),'10.30'); INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (9, 9, NOW(),'10.30'); INSERT INTO payments(payment_id, invoice_id, payment_date, payment_amount) VALUES (10, 10, NOW(),'10.30'); UPDATE payments SET payment_amount=100 WHERE payment_id=0 DELETE FROM payments WHERE payment_id=1 TRUNCATE TABLE payments *** Operations Section *** Write a SQL statement to display login_name from customers that the user from Bendigo of Alberta, Canada SELECT login_name FROM customers WHERE country='Canada' AND county='Alberta' AND town_city='Bendigo' Bjorn7656 Write a SQL statement to display product_price from products that product_category_code is ACID and product_name has BLEND and product_description CHILD SELECT product_price FROM products WHERE product_category_code='ACID' AND product_name LIKE '%BLEND%' AND product_description LIKE '%CHILD%' 19.99 Write a SQL statement to display product_price from products that product_category_code is ACID and product_name has BLEND or product_description CHILD SELECT product_price FROM products WHERE product_category_code='ACID' AND product_name LIKE '%BLEND%' OR product_description LIKE '%CHILD%'; 7 records SELECT product_price FROM products WHERE product_category_code='ACID' OR product_name LIKE '%BLEND%' OR product_description LIKE '%CHILD%'; 62 records How to create a table? DROP TABLE IF EXISTS usersinfo; DROP TABLE IF EXISTS users; CREATE TABLE users ( email varchar(255) NOT NULL, pass varchar(25) NOT NULL, profile varchar(25) NOT NULL, timestamp datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY(email) ); INSERT INTO users (email, pass, profile, timestamp) VALUES ('joe@webbook.com', 'y4u434', 'Admin', '2023-02-22 20:57:05'), ('mike@webbook.com', 'yru434', 'Admin', '2023-02-21 20:57:05'), ('john@webbook.com', '123456', 'Admin', '2023-02-20 20:57:05'); INSERT INTO users (email, pass, profile, timestamp) VALUES ('ron@webbook.com', 'y4u434', 'Admin', '2023-02-22 20:57:05'); CREATE TABLE usersinfo ( phone varchar(15) NOT NULL, name varchar(255) NOT NULL, email varchar(255) NOT NULL, PRIMARY KEY(phone), FOREIGN KEY (email) REFERENCES users(email) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO usersinfo (phone, name, email) VALUES ('832-226-7777', 'Joe', 'joe@webbook.com'), ('832-226-6666', 'mike', 'mike@webbook.com'), ('832-226-5555', 'john', 'john@webbook.com'); INSERT INTO usersinfo (phone, name, email) VALUES ('832-226-8888', 'Ron', 'ron@webbook.com'); ============================================================================= CREATE TABLE employee ( emp_id int(10) NOT NULL, name varchar(40) NOT NULL, birthdate date NOT NULL, gender varchar(10) NOT NULL, hire_date date NOT NULL, PRIMARY KEY (emp_id) ); INSERT INTO employee (emp_id, name, birthdate, gender, hire_date) VALUES (101, 'Bryan', '1988-08-12', 'M', '2015-08-26'), (102, 'Joseph', '1978-05-12', 'M', '2014-10-21'), (103, 'Mike', '1984-10-13', 'M', '2017-10-28'), (104, 'Daren', '1979-04-11', 'M', '2006-11-01'), (105, 'Marie', '1990-02-11', 'F', '2018-10-12'); CREATE TABLE payment ( payment_id int(10) PRIMARY KEY NOT NULL, emp_id int(10) NOT NULL, amount float NOT NULL, payment_date date NOT NULL, FOREIGN KEY (emp_id) REFERENCES employee (emp_id) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO payment (payment_id, emp_id, amount, payment_date) VALUES (301, 101, 1200, '2022-09-15'), (302, 101, 1300, '2022-09-30'), (303, 104, 1400, '2022-10-15'), (304, 105, 1500, '2022-10-30'), (305, 103, 1600, '2022-09-15'), (306, 102, 1800, '2022-09-30'); SELECT name FROM employee, payment WHERE employee.emp_id = payment.emp_id AND payment.amount > 1600;