1 files changed, 104 insertions, 0 deletions
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;