diff options
-rwxr-xr-x | usth/ICT2.12/labwork/1 | 55 | ||||
-rw-r--r-- | usth/ICT2.12/labwork/1.pdf | bin | 0 -> 1376916 bytes | |||
-rwxr-xr-x | usth/ICT2.12/labwork/2 | 54 | ||||
-rw-r--r-- | usth/ICT2.12/labwork/2.pdf | bin | 0 -> 1348500 bytes | |||
-rw-r--r-- | usth/ICT2.12/labwork/dino-gang.jpg | bin | 0 -> 53971 bytes | |||
m--------- | usth/ICT2.12/recipe | 0 | ||||
m--------- | usth/ICT2.5/cheese-shop | 0 | ||||
-rw-r--r-- | usth/ICT2.5/practical/1.pdf | bin | 0 -> 833453 bytes | |||
-rw-r--r-- | usth/ICT2.5/practical/1.sql | 58 | ||||
-rw-r--r-- | usth/ICT2.5/practical/2.pdf | bin | 0 -> 191759 bytes | |||
-rw-r--r-- | usth/ICT2.5/practical/2.sql | 69 | ||||
-rw-r--r-- | usth/ICT2.5/practical/3.pdf | bin | 0 -> 91823 bytes | |||
-rw-r--r-- | usth/ICT2.5/practical/3.sql | 104 | ||||
m--------- | usth/ICT2.5/practical/mywind | 0 |
14 files changed, 340 insertions, 0 deletions
diff --git a/usth/ICT2.12/labwork/1 b/usth/ICT2.12/labwork/1 new file mode 100755 index 0000000..2590192 --- /dev/null +++ b/usth/ICT2.12/labwork/1 @@ -0,0 +1,55 @@ +#!/usr/bin/env python3 +from cv2 import ( + THRESH_BINARY, ADAPTIVE_THRESH_MEAN_C, + adaptiveThreshold as adaptive_threshold, calcHist as calc_hist, blur, + equalizeHist as equalize_hist, GaussianBlur as gaussian_blur, + imread, imshow, Laplacian as laplacian, medianBlur as median_blur, + resize, Sobel as sobel, threshold, waitKey as wait_key) +from numpy import uint8 + +FILENAME = 'dino-gang.jpg' +GREYSCALE_COEFF = 0.2126, 0.7152, 0.0722 +THRESHOLD, WHITE = 128, 255 +BLUR_KSIZE = 6, 9 + + +def disp(image, name): + """Display the given image.""" + imshow(name, image.astype(uint8)) + wait_key() + + +# Exercise 1 +image = imread(FILENAME) +disp(image, 'original') + +# Exercise 2 +disp(resize(image, (512, 512)), 'square') + +# Exercise 3 +# I'm going to ignore about cv2.IMREAD_GRAYSCALE +grey_image = uint8(image.dot(GREYSCALE_COEFF)) +disp(grey_image, 'grey') + +# Exercise 4 +a, b = 1/2, 1/3 +disp(image*a+b, 'brightness adjusted') + +# Exercise 5 +disp(threshold(grey_image, THRESHOLD, WHITE, THRESH_BINARY)[-1], + 'binary threshold') +disp(adaptive_threshold(grey_image, WHITE, ADAPTIVE_THRESH_MEAN_C, + THRESH_BINARY, 25, 12), 'adaptive threshold') + +# Exercise 6 +hist = calc_hist([grey_image], [0], None, [256], [0, 256]) +disp(equalize_hist(grey_image), 'equalized') + +# Exercise 7 +disp(blur(image, BLUR_KSIZE), 'blur') +disp(gaussian_blur(image, (0, 0), 5), 'Gaussian blur') +disp(median_blur(image, 7), 'median blur') + +# Exercise 8 +disp(laplacian(image, 2), 'Laplacian') +disp(sobel(image, 2, 1, 1), 'Sobel') diff --git a/usth/ICT2.12/labwork/1.pdf b/usth/ICT2.12/labwork/1.pdf new file mode 100644 index 0000000..b7286d0 --- /dev/null +++ b/usth/ICT2.12/labwork/1.pdf Binary files differdiff --git a/usth/ICT2.12/labwork/2 b/usth/ICT2.12/labwork/2 new file mode 100755 index 0000000..af253ed --- /dev/null +++ b/usth/ICT2.12/labwork/2 @@ -0,0 +1,54 @@ +#!/usr/bin/env python3 +from cv2 import ( + COLOR_BGR2GRAY, KMEANS_RANDOM_CENTERS, THRESH_BINARY, TERM_CRITERIA_EPS, + TERM_CRITERIA_MAX_ITER, Canny as canny, cvtColor as cvt_color, + HoughLines as hough_lines, imread, imshow, inRange as in_range, kmeans, + Laplacian as laplacian, line, Sobel as sobel, threshold, + waitKey as wait_key) +from numpy import cos, float32, pi, sin, uint8 + +FILENAME = 'dino-gang.jpg' +THRESHOLD, WHITE = 128, 255 +CANNY_THRESH = 69 + + +def disp(image, name): + """Display the given image.""" + imshow(name, image.astype(uint8)) + wait_key() + + +image = imread(FILENAME) +disp(image, 'original') + +# Exercise 1 +# Requiring all three channels to be greater than THRESHOLD +# using in_range produces a blacker result (fewer white points). +# The information inferred by human (me) is less clear. +grey = cvt_color(image, COLOR_BGR2GRAY) +disp(threshold(grey, THRESHOLD, WHITE, THRESH_BINARY)[-1], 'threshold') +disp(in_range(image, (THRESHOLD,)*3, (WHITE,)*3), 'in range') + +# Exercise 2 +disp(laplacian(image, 2), 'Laplacian') +disp(sobel(image, 2, 1, 1), 'Sobel') +# Canny produces a lot visible edge comparing to Laplacian and Sobel. +edges = canny(image, CANNY_THRESH, CANNY_THRESH*2) +disp(edges, 'canny') + +# Exercise 3 +pixels = float32(image.reshape((-1, 3))) +criteria = TERM_CRITERIA_EPS|TERM_CRITERIA_MAX_ITER, 10, 1.0 +ret, labels, centers = kmeans(pixels, 3, None, criteria, + 10, KMEANS_RANDOM_CENTERS) +# Compared to global threshold, this has colors. +# I am unsure how this relate to adaptive threshold though. +disp(centers[labels.flatten()].reshape(image.shape), 'seg') + +# Exercise 4 +for ((rho, theta),) in hough_lines(edges, 1, pi/180, THRESHOLD): + a, b = cos(theta), sin(theta) + x, y = a*rho, b*rho + line(image, (int(x-b*1000), int(y+a*1000)), (int(x+b*1000), int(y-a*1000)), + (0, 0, 255), 2) +disp(image, 'hough') diff --git a/usth/ICT2.12/labwork/2.pdf b/usth/ICT2.12/labwork/2.pdf new file mode 100644 index 0000000..2471ca1 --- /dev/null +++ b/usth/ICT2.12/labwork/2.pdf Binary files differdiff --git a/usth/ICT2.12/labwork/dino-gang.jpg b/usth/ICT2.12/labwork/dino-gang.jpg new file mode 100644 index 0000000..1d8001a --- /dev/null +++ b/usth/ICT2.12/labwork/dino-gang.jpg Binary files differdiff --git a/usth/ICT2.12/recipe b/usth/ICT2.12/recipe new file mode 160000 +Subproject d1eff3a50da29e8364162dabdd622789e90cf29 diff --git a/usth/ICT2.5/cheese-shop b/usth/ICT2.5/cheese-shop new file mode 160000 +Subproject 63ad0c373de0f57de4cd8032e0d6703cb2d2d41 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 |