[usth/ICT2.{5,12}] Process images and base data

7 files changed, 231 insertions, 0 deletions

diff --git a/usth/ICT2.5/practical/1.pdf b/usth/ICT2.5/practical/1.pdf
new file mode 100644
index 0000000..510c83e
--- /dev/null
+++ b/usth/ICT2.5/practical/1.pdf
Binary files differdiff --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
--- /dev/null
+++ b/usth/ICT2.5/practical/2.pdf
Binary files differdiff --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
--- /dev/null
+++ b/usth/ICT2.5/practical/3.pdf
Binary files differdiff --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
+Subproject 936a3f26806ac4c6060e450529f7a2f60eb6859