bazi vezbi za II prakticen kol.

-- (1)

SELECT product_name, list_price-list_price*0.10 AS price FROM demo_product_info WHERE list_price > 100
UNION
SELECT product_name, list_price AS price FROM demo_product_info WHERE list_price < 100

-- (2)

SELECT product_name, category, list_price FROM demo_product_info WHERE product_id IN (SELECT DISTINCT product_id FROM demo_order_items WHERE order_id IN (SELECT order_id FROM demo_orders WHERE customer_id IN (SELECT customer_id FROM demo_customers WHERE cust_street_address1 LIKE '% Drive')))

-- (3)

SELECT category, MIN(list_price) AS MIN_LIST_PRICE, MAX(list_price) AS MIN_LIST_PRICE, SUM(list_price)/COUNT(list_price) AS average, COUNT(list_price) AS total FROM demo_product_info GROUP BY category

-- (4)

SELECT cust_first_name, cust_last_name, (SELECT COUNT(DISTINCT product_id) FROM demo_order_items WHERE order_id IN (SELECT order_id FROM demo_orders WHERE customer_id = demo_customers.customer_id)) AS broj, (SELECT SUM(order_total) FROM demo_orders WHERE customer_id = demo_customers.customer_id) AS total FROM demo_customers

-- (5)

SELECT cust_first_name, cust_last_name FROM demo_customers WHERE customer_id = (SELECT customer_id FROM (SELECT customer_id, SUM(order_total) AS total FROM demo_orders GROUP BY customer_id) WHERE total = (SELECT MAX(SUM(order_total)) FROM demo_orders GROUP BY customer_id))

-- (6)

SELECT state_name FROM demo_states WHERE st = (SELECT state FROM (SELECT state, COUNT(state) AS orders FROM (SELECT order_id, (SELECT cust_state FROM demo_customers WHERE demo_customers.customer_id = demo_orders.customer_id) AS state FROM demo_orders) GROUP BY state) WHERE orders = (SELECT MAX(COUNT(state)) AS orders FROM (SELECT order_id, (SELECT cust_state FROM demo_customers WHERE demo_customers.customer_id = demo_orders.customer_id) AS state FROM demo_orders) GROUP BY state))

-- (7)

SELECT product_name FROM demo_product_info WHERE list_price = (SELECT MIN(list_price) FROM demo_product_info WHERE category IN (SELECT category FROM demo_product_info WHERE list_price = (SELECT  MAX(list_price) FROM demo_product_info)))