From e09aa90e0e388d4c42e38381961bc3fa1771cff3 Mon Sep 17 00:00:00 2001 From: Nguyễn Gia Phong Date: Wed, 8 Jul 2020 14:11:07 +0700 Subject: [usth/ICT2.{5,12}] Process images and base data --- usth/ICT2.5/practical/1.pdf | Bin 0 -> 833453 bytes usth/ICT2.5/practical/1.sql | 58 ++++++++++++++++++++++++ usth/ICT2.5/practical/2.pdf | Bin 0 -> 191759 bytes usth/ICT2.5/practical/2.sql | 69 ++++++++++++++++++++++++++++ usth/ICT2.5/practical/3.pdf | Bin 0 -> 91823 bytes usth/ICT2.5/practical/3.sql | 104 +++++++++++++++++++++++++++++++++++++++++++ usth/ICT2.5/practical/mywind | 1 + 7 files changed, 232 insertions(+) create mode 100644 usth/ICT2.5/practical/1.pdf create mode 100644 usth/ICT2.5/practical/1.sql create mode 100644 usth/ICT2.5/practical/2.pdf create mode 100644 usth/ICT2.5/practical/2.sql create mode 100644 usth/ICT2.5/practical/3.pdf create mode 100644 usth/ICT2.5/practical/3.sql create mode 160000 usth/ICT2.5/practical/mywind (limited to 'usth/ICT2.5/practical') diff --git a/usth/ICT2.5/practical/1.pdf b/usth/ICT2.5/practical/1.pdf new file mode 100644 index 0000000..510c83e Binary files /dev/null and b/usth/ICT2.5/practical/1.pdf differ diff --git a/usth/ICT2.5/practical/1.sql b/usth/ICT2.5/practical/1.sql new file mode 100644 index 0000000..7fb1394 --- /dev/null +++ b/usth/ICT2.5/practical/1.sql @@ -0,0 +1,58 @@ +CREATE DATABASE Company; +USE Company; + +CREATE TABLE employee ( + fname char(255), + minit char(255), + lname char(255), + ssn int(9) PRIMARY KEY, + bdate date, + address char(255), + sex bigint(255), + salary int(9), + super_ssn int(9), + dno int(9), + FOREIGN KEY (super_ssn) REFERENCES employee(ssn)); + +CREATE TABLE department ( + dname char(255), + dnumber int(9) PRIMARY KEY, + mgr_ssn int(9), + mgr_start_date date, + FOREIGN KEY (mgr_ssn) REFERENCES employee(ssn)); + +ALTER TABLE employee ADD FOREIGN KEY (dno) REFERENCES department(dnumber); + +CREATE TABLE dept_locations ( + dnumber int(9), + dlocation char(255), + PRIMARY KEY (dnumber, dlocation), + FOREIGN KEY (dnumber) REFERENCES department(dnumber)); + +CREATE TABLE project ( + pname char(255), + pnumber int(9) PRIMARY KEY, + plocation char(255), + dnum int(9), + FOREIGN KEY (dnum) REFERENCES department(dnumber)); + +CREATE TABLE works_on ( + essn int(9), + pno int(9), + hours int(9), + PRIMARY KEY (essn, pno), + FOREIGN KEY (essn) REFERENCES employee(ssn), + FOREIGN KEY (pno) REFERENCES project(pnumber)); + +CREATE TABLE dependant ( + essn int(9), + dependant_name char(255), + sex bigint(255), + bdate date, + relationship char(255), + PRIMARY KEY (essn, dependant_name), + FOREIGN KEY (essn) REFERENCES employee(ssn)); + +ALTER TABLE employee ADD partner_ssn int(9); + +DROP DATABASE Company; diff --git a/usth/ICT2.5/practical/2.pdf b/usth/ICT2.5/practical/2.pdf new file mode 100644 index 0000000..924fffc Binary files /dev/null and b/usth/ICT2.5/practical/2.pdf differ diff --git a/usth/ICT2.5/practical/2.sql b/usth/ICT2.5/practical/2.sql new file mode 100644 index 0000000..23cbf4b --- /dev/null +++ b/usth/ICT2.5/practical/2.sql @@ -0,0 +1,69 @@ +SOURCE mywind/northwind-default-current-timestamp.sql; +SOURCE mywind/northwind-data.sql; + +SELECT * +FROM products +WHERE discontinued=1; + +SELECT * +FROM products +ORDER BY list_price +LIMIT 4; + +SELECT id, product_name, list_price +FROM products +WHERE list_price +BETWEEN 15 AND 25; + +SELECT id, CONCAT(first_name, ' ', last_name) AS name +FROM employees; + +SELECT id, first_name, last_name +FROM employees +WHERE first_name LIKE 'A%'; + +SELECT COUNT(DISTINCT city) AS 'number of different cities' +FROM employees; + +SELECT DISTINCT ship_name +FROM orders; + +SELECT MIN(list_price), MAX(list_price) +from products; + +SELECT COUNT(id) AS 'number of current products' +FROM products +WHERE discontinued=0; + +SELECT AVG(list_price), STD(list_price) +FROM products; + +SELECT product_name, list_price +FROM products +WHERE list_price > (SELECT AVG(list_price) FROM products); + +INSERT INTO suppliers (company, last_name, first_name, city, country_region) +VALUES ('Habeco', 'Nguyễn', 'Hồng Linh', 'Hanoi', 'Vietnam'); + +INSERT INTO products ( + supplier_ids, + product_code, + product_name, + list_price, + discontinued, + category) +VALUES ( + (SELECT id FROM suppliers WHERE company='Habeco'), + 'TB', + 'Truc Bach', + 22, + 0, + 'Beverages'); + +UPDATE products +SET standard_cost=18 +WHERE product_name='Truc Bach'; + +DELETE +FROM suppliers +WHERE company='Habeco'; diff --git a/usth/ICT2.5/practical/3.pdf b/usth/ICT2.5/practical/3.pdf new file mode 100644 index 0000000..4adcc28 Binary files /dev/null and b/usth/ICT2.5/practical/3.pdf differ diff --git a/usth/ICT2.5/practical/3.sql b/usth/ICT2.5/practical/3.sql new file mode 100644 index 0000000..9e264e8 --- /dev/null +++ b/usth/ICT2.5/practical/3.sql @@ -0,0 +1,104 @@ +SOURCE mywind/northwind-default-current-timestamp.sql; +SOURCE mywind/northwind-data.sql; + +CREATE VIEW recent_orders +AS SELECT * +FROM orders +WHERE order_date>'2006-03-24'; +SELECT * +FROM recent_orders; + +SELECT + product_code, + unit_price, + quantity, + (unit_price*quantity*(1-discount)) AS value +FROM order_details, products +WHERE order_id=31 AND product_id=products.id; + +CREATE VIEW order_values +AS SELECT + recent_orders.id AS id, + order_date, + company, + (unit_price*quantity*(1-discount)) AS value +FROM recent_orders, customers, order_details +WHERE recent_orders.id=order_id AND customer_id=customers.id; +SELECT * +FROM order_values +ORDER BY id; + +CREATE VIEW order_subtotal +AS SELECT id, order_date, company, SUM(value) as sub_total +FROM order_values +GROUP BY id; +SELECT * +FROM order_subtotal; + +SELECT * +FROM order_subtotal +WHERE sub_total>=800; + +SELECT + CONCAT(first_name, ' ', last_name) AS full_name, + SUM(unit_price*quantity*(1-discount)) AS sale +FROM orders, order_details, employees +WHERE orders.id=order_id AND employee_id=employees.id +GROUP BY employee_id +ORDER BY sale DESC; + +SELECT + company, + CONCAT(first_name, ' ', last_name) AS full_name, + email_address, + 'C' as 'type' +FROM employees +UNION +SELECT + company, + CONCAT(first_name, ' ', last_name) AS full_name, + email_address, + 'S' as 'type' +FROM suppliers; + +SELECT DISTINCT category +FROM products +ORDER BY category; + +SELECT + MIN(standard_cost), + MAX(standard_cost), + AVG(standard_cost), + STD(standard_cost), + VARIANCE(standard_cost) +FROM products; + +CREATE VIEW price_by_category +AS SELECT category, AVG(list_price) as average_price +FROM products +GROUP BY category; +SELECT * +FROM price_by_category; + +DELIMITER // +CREATE PROCEDURE top_categories(n int) +BEGIN + SELECT * + FROM price_by_category + ORDER BY average_price DESC + LIMIT n; +END// +DELIMITER ; +CALL top_categories(3); + +SELECT category, MIN(average_price) +FROM price_by_category; + +CREATE VIEW employee_names +AS SELECT id, CONCAT(first_name, ' ', last_name) as full_name +FROM employees; +SELECT + id, + (SELECT full_name FROM employee_names WHERE id=created_by) as creator, + (SELECT full_name FROM employee_names WHERE id=approved_by) as approver +FROM purchase_orders; diff --git a/usth/ICT2.5/practical/mywind b/usth/ICT2.5/practical/mywind new file mode 160000 index 0000000..936a3f2 --- /dev/null +++ b/usth/ICT2.5/practical/mywind @@ -0,0 +1 @@ +Subproject commit 936a3f26806ac4c6060e450529f7a2f60eb6859d -- cgit 1.4.1