<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="C:/Users/Jason/DSI/sql/05_src/sql/farmersmarket.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="8651"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/><expanded_item id="4" parent="1"/></tab_structure><tab_browse><table title="booth" custom_title="0" dock_id="1" table="4,5:mainbooth"/><dock_state state="000000ff00000000fd0000000100000002000004fa000004defc0100000001fb000000160064006f0063006b00420072006f00770073006500310100000000000004fa0000011f00ffffff000002720000000000000004000000040000000800000008fc00000000"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="booth" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="90"/><column index="2" value="108"/><column index="3" value="300"/><column index="4" value="72"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 1*">-- COUNT

--count the number of products

SELECT count(product_id) AS num_of_prod

FROM product;

-- how many products per product_qty_type

SELECT product_qty_type, count(product_id)

FROM product

WHERE product_qty_type IS NOT NULL -- remove the NULL rows

GROUP BY product_qty_type;

--how many products per product_qty_type and per their product_size

SELECT product_size, product_qty_type, count(product_id)

FROM product

GROUP BY product_size, product_qty_type;

--how many UNIQUE products were bought

SELECT count(DISTINCT product_id)

FROM customer_purchases;

</sql><sql name="SQL 2*">-- SUM &amp; AVG

--how much did a customer spend on each day

SELECT

market_date,

customer_id,

SUM(quantity*cost_to_customer_per_qty) as total_cost

FROM customer_purchases

GROUP BY market_date, customer_id;

--how much does each customer spend on average

SELECT

customer_first_name,

customer_last_name,

ROUND(AVG(quantity*cost_to_customer_per_qty), 2) as avg_cost

FROM customer_purchases cp

INNER JOIN customer c

ON c.customer_id = cp.customer_id

GROUP BY c.customer_id;</sql><sql name="SQL 3*">-- MIN &amp; max

--what is the most expensive product

SELECT product_name, max(original_price) -- doesnt handle ties well!

FROM product p

INNER JOIN vendor_inventory as vi

ON p.product_id = vi.product_id;

-- minimum price per each type of the unit/lbs

SELECT product_name, product_qty_type,

min(original_price)

FROM product p

INNER JOIN vendor_inventory as vi

ON p.product_id = vi.product_id

GROUP BY product_qty_type;

--PROVE IT

SELECT DISTINCT product_name, product_qty_type,original_price

FROM product p

INNER JOIN vendor_inventory as vi

ON p.product_id = vi.product_id

ORDER BY product_qty_type, original_price</sql><sql name="SQL 4*">--HAVING

--how much did a customer spend on each day

SELECT

market_date,

customer_id,

SUM(quantity*cost_to_customer_per_qty) as total_cost

FROM customer_purchases

WHERE customer_id BETWEEN 1 AND 5

GROUP BY market_date, customer_id

HAVING total_cost &gt; 50;

--how many products were bought

SELECT count(product_id) as num_of_prod, product_id

FROM customer_purchases

WHERE product_id &lt;= 8

GROUP BY product_id

HAVING count(product_id) BETWEEN 300 AND 500;

--&quot;top&quot;

SELECT count(product_id) as num_of_prod, product_id

FROM customer_purchases

GROUP BY product_id

ORDER BY count(product_id) DESC

LIMIT 3</sql><sql name="SQL 5*">--SUBQUERIES: JOIN

--&quot;what is the single item that has been bought in the greatest quantity?&quot;

--outer query

SELECT product_name, max(quantity)

FROM product p

INNER JOIN (

--inner query

SELECT product_id, count(quantity) as quantity

FROM customer_purchases

GROUP BY product_id

) x ON p.product_id = x.product_id;

--simple subquery in a FROM statement

SELECT DISTINCT inflation, product_id

FROM (

SELECT product_id, cost_to_customer_per_qty

,CASE WHEN cost_to_customer_per_qty &lt; '1.00' THEN cost_to_customer_per_qty*5

ELSE cost_to_customer_per_qty END as inflation

FROM customer_purchases

)

</sql><sql name="SQL 6*">--subqueries: WHERE

-- &quot;what postal codes have had at least 3000? at the farmers market?&quot;

SELECT DISTINCT customer_postal_code

FROM customer

WHERE customer_id IN (

SELECT customer_id

FROM customer_purchases

GROUP BY customer_id

HAVING sum(quantity*cost_to_customer_per_qty) &gt; 3000

);

--'what is the name of the vendor who sells pie'

SELECT DISTINCT vendor_name

FROM vendor_inventory vi

INNER JOIN vendor v

ON vi.vendor_id = v.vendor_id

WHERE product_id IN (

SELECT product_id

FROM product

WHERE product_name LIKE '%pie%'

)

</sql><sql name="SQL 7*"> --temp

--&quot;if a table named &quot;new_vendor_inventory&quot; exists, delete it, otherwise do NOTHING

DROP TABLE IF EXISTS new_vendor_inventory;

--make the TABLE

CREATE TEMP TABLE new_vendor_inventory AS

--definition of the TABLE

SELECT *,

original_price*5 as inflation

FROM vendor_inventory;

--put a temp table into another one

DROP TABLE IF EXISTS new_new_vendor_inventory;

CREATE TEMP TABLE new_new_vendor_inventory AS

SELECT *

,inflation*2 as super_inflation

from new_vendor_inventory;

SELECT * FROM new_new_vendor_inventory</sql><sql name="SQL 8*">--CTE

--calculate sales per vendor per day

WITH vendor_daily_sales AS (

SELECT

md.market_date

,market_day

,market_week

,market_year

,vendor_name

,sum(quantity*cost_to_customer_per_qty) as sales

FROM customer_purchases cp

INNER JOIN vendor v

ON v.vendor_id = cp.vendor_id

INNER JOIN market_date_info md

ON cp.market_date = md.market_date

GROUP BY md.market_date,v.vendor_id

)

-- re-aggregate daily sales within each week number for each vendor

SELECT

market_year,

market_week,

vendor_name,

sum(sales)

FROM vendor_daily_sales</sql><sql name="SQL 9*">--dates

SELECT DISTINCT

DATE('now'),

DATETIME('now')

--strftime

,strftime('%Y/%m', 'now')

,strftime('%Y/%m', 'now', '+50 days') as the_future

,market_date

,strftime('%Y-%m', market_date, '+50 days', '-1 year') as the_past

--dateadd

--last daty of the month

,strftime('%Y-%m-%d', market_date, 'start of month', '-1 day')

--datediff

,market_date

,julianday('now') - julianday(market_date) -- number of days between now and each market date

,(julianday('now') - julianday(market_date) )/ 365.25 --number of years between now and each market date

,(julianday('now') - julianday(market_date)) * 24 -- number of HOURS between now and each market date

FROM market_date_info;</sql><current_tab id="7"/></tab_sql></sqlb_project>