SELECT table_name

FROM information_schema.tables

WHERE table_type='BASE TABLE'

AND table_schema='public';

DROP DATABASE "AdventureWorks" WITH (FORCE);

select * from customers;

select *

from information_schema.columns

where table_name = 'suppliers';

select companyname, city, country from suppliers;

select categoryname, description from categories;

select distinct country from customers

select distinct city, country from customers

select distinct region from suppliers

select count(*) from products;

select count(*) from orders;

select count(*) from (select distinct city from suppliers) as T; -- or

select count(distinct city) from suppliers;

select count(distinct productid) from order_details;

select customerid, shippeddate - orderdate as delta from orders;

select orderid, ((unitprice * quantity) - discount) as amount_spent from order_details

select * from actor;

select * from film;

select * from staff;

select address, district from address;

select title, description from film;

select city, country_id from city;

select distinct last_name from customer;

select distinct first_name from actor;

select distinct inventory_id from rental;

select count(*) from film;

select count(*) from category;

select count(distinct first_name) from actor;

-- Find the number of distinct (first_name, last_name) pairs

select count(distinct(first_name, last_name)) from actor;

select rental_id, return_date - rental_date as diff from rental;

where customer_name='David Brown'

where title='Drop Can''t, Embrace Can'

select * from suppliers where city = 'Berlin'

select companyname, contactname from customers where country='Mexico';

select count(*) from orders where employeeid = 3;

select count(*) from order_details where quantity > 20;

select count(*) from orders where freight >= 250;

where orderdate = '1996-07-04'

where orderdate > '1996-07-04'

where orderdate >= '1996-07-04'

where orderdate < '1996-07-04'

where orderdate <= '1996-07-04'

select now() --> 2020-10-19 15:33:12.096909+03

select count(*) from orders where orderdate >= '1998-01-01';

select count(*) from orders where orderdate >= date('Jan-01-1998');

select count(*) from orders where shippeddate < '1997-07-05';

select count(*) from orders where shippeddate < date('July-5-1997');

select count(*) from orders where shipcountry='Germany' and freight>100;

select distinct(customerid) from orders where shipvia=2 and shipcountry='Brazil'

select count(*) from customers where country='USA' or country='Canada';

select * from customers where country = 'USA' or country = 'Canada';

select count(*) from suppliers where country='Germany' or country='Spain';

select count(*) from suppliers where country in ('Germany', 'Spain');

select count(*) from orders where shipcountry in ('USA', 'Brazil', 'Argentina');

select count(*) from customers where not country='France';

select count(*) from customers where country != 'France';

select count(*) from suppliers where not country='USA'

select count(*) from suppliers where country!='USA'

select count(*) from orders where shipcountry='Germany' and (freight < 50 or freight > 175);

select count(*) from orders where shipcountry = 'Germany' and not (freight >= 50 and freight <= 175);

select count(*) from orders where (shipcountry='Canada' or shipcountry='Spain') and shippeddate > '1997-05-01';

select count(*) from orders where shipcountry in ('Canada', 'Spain') and shippeddate > date('May-1-1997');

select count(*) from order_details where unitprice between 10 and 20;

select count(*) from orders where shippeddate between '1996-06-01' and '1996-09-30';

select count(*) from orders where shippeddate between date('Jun-1-1996') and date('Sept-30-1996');

select count(*) from suppliers where country in ('Germany', 'France', 'Spain', 'Italy')

select count(*) from products where categoryid in (1, 4, 6, 7)

select * from data_src where journal='Food Chemistry';

select * from nutr_def where nutrdesc = 'Retinol';

select * from food_des where manufacname='Kellogg, Co.';

select count(*) from data_src where year > 2000;

select count(*) from food_des where fat_factor < 4;

select * from weight where gm_wgt = 190.0;

select * from food_des where pro_factor > 1.5 and fat_factor < 5;

select * from data_src where year = 1990 and journal = 'Cereal Foods World';

select count(*) from weight where gm_wgt > 150 and gm_wgt < 200;

select * from nutr_def where units in ('kj', 'kcal');

select * from data_src where year = 2000 and journal = 'Food Chemistry';

select count(*) from food_des where fdgrp_cd != (select distinct fdgrp_cd from fd_group where fddrp_desc = 'Breakfast Cereals');

select count(*) from food_des left join fd_group using(fdgrp_cd) where not fddrp_desc = 'Breakfast Cereals';

select * from data_src where (year between 1990 and 2000) and (journal in ('J. Food Protection', 'Food Chemistry'))

select * from weight where gm_wgt between 50 and 75;

select * from data_src where year in (1960,1970,1980,1990, 2000);

select * from production.product

select * from purchasing.vendor

select column_name, data_type, character_maximum_length, column_default, is_nullable

from INFORMATION_SCHEMA.COLUMNS where table_name = 'vendor';

psql --port=5433 --host=localhost --dbname=northwind --username=postgres

select distinct(country) from suppliers order by country asc

select distinct country, city from suppliers order by country, city asc

select productname, unitprice from products

order by unitprice desc, productname asc;

select min(orderdate) from orders where shipcountry='Italy';

select max(shippeddate) from orders where shipcountry = 'Canada';

select max(shippeddate-orderdate) from orders where shipcountry = 'France' and shippeddate is not null and orderdate is not null;

select * from orders where shipcountry = 'France' and shippeddate is not null and orderdate is not null

order by (shippeddate-orderdate) desc limit 1;

-- OR

select * from orders

where (shippeddate-orderdate) = (

select max(shippeddate-orderdate) from orders where shipcountry = 'France' and shippeddate is not null and orderdate is not null

) and shipcountry = 'France';

select avg(freight) from orders where shipcountry='Brazil';

select sum(quantity) from order_details where productid = (select productid from products where productname='Tofu');

select sum(quantity) from products inner join order_details using(productid) where productname='Tofu';

select avg(quantity) as avg_quantity from order_details

where productid = (

select productid from products where productname='Steeleye Stout'

);

-- OR

select avg(quantity) as avg_quantity from products inner join order_details using(productid)

where productname='Steeleye Stout';

-- pattern is case sensitive

-- % stands for zero, one or more characters

-- _ stands for any single character

select * from customers where contactname like 'D%'

select companyname from suppliers where companyname like '_or%'

select companyname from customers where companyname like '%er'

-- list the total amount spent per order details

select ((unitprice*quantity)-discount) as total_amount_spent from order_details;

-- list the total amount spent per order details & order it by total amount spent in asc fashion

select ((unitprice*quantity)-discount) as total_amount_spent from order_details

order by total_amount_spent asc;

select productid, productname, unitprice*unitsinstock as inventory_value from products

order by inventory_value desc;

select *, ((unitprice*quantity)-discount) as total_spent from order_details order by total_spent desc limit 3;

select *, (unitprice*unitsinstock) as total_dollar_amount from products order by total_dollar_amount asc limit 2;

select count(*) from customers where region is null;

select count(*) from suppliers where region is not null;

select count(*) from orders where shipregion is null;

select

product.name,

product.weight,

product.productnumber

from production.product

order by product.weight asc;

select * from purchasing.productvendor

where productid = 407

order by averageleadtime asc;

select * from sales.salesorderdetail where productid=799 order by orderqty desc;

select discountpct from sales.specialoffer order by discountpct desc limit 1;

select max(discountpct) from sales.specialoffer;

select sickleavehours from humanresources.employee order by sickleavehours asc limit 1

select min(sickleavehours) from humanresources.employee

select * from humanresources.employee order by sickleavehours asc limit 1

select rejectedqty from purchasing.purchaseorderdetail order by orderqty desc limit 1

select max(rejectedqty) as max_rejected_qty from purchasing.purchaseorderdetail

select avg(rate) as average_rate from humanresources.employeepayhistory;

select avg(standardcost) as avg_std_cost from production.productcosthistory where productid = 738;

select sum(scrappedqty) as sum_scrapped_qty from production.workorder where productid = 529;

select distinct vendor.name from purchasing.vendor where vendor.name like 'G%'

select distinct vendor.name from purchasing.vendor where vendor.name like '%Bike%';

select * from person.person where person.firstname like '_t%';

select * from person.emailaddress limit 20;

select * from production.productcategory limit 2;

select count(*) from production.product where product.weight is null;

select count(*) from person.person where additionalcontactinfo is not null;

-- Do you want records that only have data on both tables (inner join)

-- e.g. only orders with order details

-- Do you want all records from first table and any records from second (Left join)

-- e.g. all customers and any orders they might have

-- Do you want all records of one table matched with all records of second table? (cross join)

select <column1>, <column2>, ...

from table1 inner join table2

on table1.column_name = table2.column_name

select companyname, contactname, orderdate, shipcountry from customers inner join orders

on customers.customerid = orders.customerid

select firstname, lastname, orderdate from employees inner join orders

on employees.employeeid = orders.employeeid;

select companyname, unitprice, unitsinstock from products inner join suppliers

on products.supplierid = suppliers.supplierid;

select <column1>, <column2>, ...

from table1 inner join table2 on table1.column_name = table2.column_name

inner join table3 on table1.column_name = table3.column_name

inner join table4 on table1.column_name = table4.column_name

...

select companyname, orderdate, productid, unitprice, quantity

from orders inner join customers on customers.customerid = orders.customerid

inner join order_details on orders.orderid = order_details.orderid;

select customers.companyname, orders.orderdate, order_details.productid, order_details.unitprice, order_details.quantity,

products.productname

from orders inner join customers on customers.customerid = orders.customerid

inner join order_details on orders.orderid = order_details.orderid

inner join products on products.productid = order_details.productid

select customers.companyname, orders.orderdate, order_details.productid, order_details.unitprice, order_details.quantity,

products.productname, categories.categoryname

from orders inner join customers on customers.customerid = orders.customerid

inner join order_details on orders.orderid = order_details.orderid

inner join products on products.productid = order_details.productid

inner join categories on products.categoryid = categories.categoryid;

select customers.companyname, orders.orderdate, order_details.productid, order_details.unitprice, order_details.quantity,

products.productname, categories.categoryname

from orders inner join customers on customers.customerid = orders.customerid

inner join order_details on orders.orderid = order_details.orderid

inner join products on products.productid = order_details.productid

inner join categories on products.categoryid = categories.categoryid

where categories.categoryname = 'Seafood' and order_details.unitprice * order_details.quantity >= 500;

select companyname, orderdate, productid, productname, order_details.unitprice, quantity

from orders inner join customers using(customerid)

inner join order_details using(orderid)

inner join products using(productid)

inner join categories using(categoryid)

where categoryname = 'Seafood' and (order_details.unitprice * order_details.quantity) >= 500

select <column1>, <column2>, ...

from table1 left join table2 on table1.column_name = table2.column_name

select companyname, orderid from customers left join orders on customers.customerid = orders.customerid;

select companyname from customers left join orders on customers.customerid = orders.customerid

where orderid is null;

select productname, orderid from products left join order_details on products.productid = order_details.productid;

select productname, orderid from products left join order_details on products.productid = order_details.productid

where orderid is null;

select * from products left join order_details using(productid)

where order_details.orderid is null;

select <column1>, <column2>, ...

from table1 right join table2 on table1.column_name = table2.column_name

select companyname, orderid from orders right join customers on orders.customerid = customers.customerid;

select companyname, orderid from orders right join customers using(customerid);

select orderid, customers.customerid from customers right join orders on customers.customerid = orders.customerid

where customers.customerid is null;

select orderid, orders.customerid from customers right join orders using(customerid)

where customers.customerid is null

select companyname from orders right join customers on orders.customerid = customers.customerid

where orderid is null;

select companyname from orders right join customers using(customerid)

where orders.orderid is null;

select customers.customerid, companyname, customertypeid

from customercustomerdemo right join customers on customercustomerdemo.customerid = customers.customerid;

select customers.customerid, companyname, customercustomerdemo.customertypeid

from customercustomerdemo right join customers using(customerid);

select <column1>, <column2>, ...

from table1 full join table2 on table1.column_name = table2.column_name

select companyname, orderid from orders full join customers on orders.customerid = customers.customerid

select companyname, orderid from customers full join orders on orders.customerid = customers.customerid

select companyname, orderid from orders full join customers using(customerid);

-- orders without customers + customers without orders + inner join of customers & orders

select productname, categoryname from products full join categories on products.categoryid = categories.categoryid;

select productname, categoryname from products full join categories using(categoryid);

-- Hierarchy - like employees who report to other employees

-- Looking for similarities or dissimilarities same column

-- everyone in the same city or same birthdate

-- Alias A : Employees + Alias B : Employees

select column1, column2, ..

from table1 as t1 join table1 as t2 on t1.column=t2.column

where condition

select c1.companyname as customer_1, c2.companyname as customer_2, c1.city as city

from customers as c1 join customers as c2 on c1.city = c2.city

where c1.customerid != c2.customerid

order by c1.city asc;

select c1.companyname as customer_1, c2.companyname as customer_2, c1.city

from customers as c1 join customers as c2 using(city)

where c1.customerid <> c2.customerid

order by c1.city asc;

select

c1.companyname as customer_1, c2.companyname as customer_2, c1.city

from customers as c1 inner join customers as c2 using(city)

where c1.customerid <> c2.customerid

order by c1.city asc;

select s1.companyname as supplier1, s2.companyname as supplier2, s1.country as country

from suppliers as s1 join suppliers as s2 on s1.country = s2.country

where s1.supplierid <> s2.supplierid

order by s1.country;

select

s1.companyname as supplier1, s2.companyname as supplier2, s1.country

from suppliers as s1 inner join suppliers as s2 using(country)

where s1.supplierid <> s2.supplierid

order by s1.country asc;

select *

from orders inner join order_details using(orderid)

select *

from orders inner join order_details using(orderid)

inner join products using(productid)

from table1 natural join table2

-- equals to

from table1 inner join table2 using(column)

-- equals to

from table1 inner join table2 on table1.column = table2.column

select * from orders natural join order_details

-- equals to

select * from orders inner join order_details using(orderid)

-- equals to

select * from orders inner join order_details on orders.orderid = order_details.orderid

select * from customers natural join orders

natural join order_details

select person.firstname, person.middlename, person.lastname, personphone.phonenumber, phonenumbertype.name

from person.person inner join person.personphone using(businessentityid)

inner join person.businessentity using(businessentityid)

inner join person.phonenumbertype using(phonenumbertypeid)

order by person.businessentityid desc;

select pm.name, pc.name, description

from production.productmodel as pm inner join production.productmodelproductdescriptionculture using(productmodelid)

inner join production.productdescription using(productdescriptionid)

inner join production.culture as pc using(cultureid)

order by pm.name asc;

product.name,

productmodel.name,

culture.name,

productdescription.description

from production.productmodel

inner join production.product using(productmodelid)

inner join production.productmodelproductdescriptionculture using(productmodelid)

inner join production.productdescription using(productdescriptionid)

inner join production.culture using(cultureid)

order by productmodel.name asc;

select product.name,

productreview.rating,

productreview.comments

from production.product left join production.productreview using(productid)

order by productreview.rating asc;

product.name,

workorder.orderqty,

workorder.scrappedqty

from production.workorder right join production.product using(productid)

order by product.productid asc;

select country, count(*) as num_of_customers

from customers group by country

order by num_of_customers desc;

SELECT COUNT(*),categoryname

FROM categories

JOIN products ON categories.categoryid=products.categoryid

GROUP BY categoryname

ORDER BY COUNT(*) DESC;

-- in the previous solution, we grouped by category name, but ideally we should group by categoryid

-- we might also take into account categories without products

-- we might also take into account products without categories

select categories.categoryid,

categories.categoryname,

case

when sum(productid) is null then 0

else count(*)

end as num_of_products

from categories full join products using(categoryid)

group by categories.categoryid

order by num_of_products desc;

categories.categoryid,

categories.categoryname,

sum(od.quantity) as total_num_of_products_sold

from products inner join order_details as od using(productid)

inner join categories using(categoryid)

group by categories.categoryid

order by total_num_of_products_sold desc;

-- if we had product categories that does not yet have products

-- or if we had certain products that do not yet have order details then this solution would be better:

select

categories.categoryid,

categories.categoryname,

case

when sum(products.productid) is null then 0

when sum(order_details.orderid) is null then 0

else sum(order_details.quantity)

end as total_num_of_products_sold

from categories

left join products using(categoryid)

left join order_details using(productid)

group by categories.categoryid

order by total_num_of_products_sold desc;

select productname, avg(quantity) as avg_num_of_items_ordered

from products inner join order_details using(productid)

group by products.productid

order by avg_num_of_items_ordered desc

-- we used inner join to select products with order details and vice versa.

-- what if we have products with no order details? To take them into account:

select

products.productname,

case

when sum(order_details.orderid) is null then 0

else avg(quantity)

end as avg_num_products_ordered

from products left join order_details using(productid)

group by products.productid

order by avg_num_products_ordered desc;

select productname, COALESCE(avg(quantity),0) as avg_num_products_ordered

from products left join order_details using(productid)

group by products.productid

order by avg_num_products_ordered desc;

-- Reference: https://www.postgresqltutorial.com/postgresql-coalesce/

-- coalesce : unite

select

products.productname,

case

when avg(quantity) is null then 0

else avg(quantity)

end as avg_num_products_ordered

from products left join order_details using(productid)

group by products.productid

order by avg_num_products_ordered desc;

select country, count(*) as number_of_suppliers

from suppliers group by country

order by number_of_suppliers desc;

productname,

sum((od.unitprice*od.quantity)-od.discount) as total_sold

from products inner join order_details as od using(productid)

inner join orders using(orderid)

where extract(year from orderdate) = 1997

group by products.productid

order by total_sold desc;

-- OR:

select

products.productname,

sum((order_details.unitprice*order_details.quantity)-order_details.discount) as total_value

from products

inner join order_details using(productid)

inner join orders using(orderid)

group by productid, extract(year from orderdate)

having extract(year from orderdate) = 1997

order by total_value desc;

Select column_names

from table1

where condition

group by column_name(s),

having condition

order by column_names

products.productid,

products.productname,

coalesce(sum((od.unitprice*od.quantity)-od.discount), 0) as amount_sold

from products left join order_details as od using(productid)

group by productid

having coalesce(sum((od.unitprice*od.quantity)-od.discount), 0) < 2000

order by amount_sold asc;

-- coalesce (postgresql) ?= ifnull (mysql)

customers.customerid,

customers.companyname,

sum((od.unitprice*od.quantity)-od.discount) as total_amount_bought

from customers

inner join orders using(customerid)

inner join order_details as od using(orderid)

group by customers.customerid

having sum((od.unitprice*od.quantity)-od.discount) > 5000

order by total_amount_bought desc;

select column_name, data_type, character_maximum_length, column_default, is_nullable

from INFORMATION_SCHEMA.COLUMNS where table_name = 'orders';

select

customers.customerid,

customers.companyname,

sum((od.unitprice * od.quantity) - od.discount) as total_purchase

from customers

inner join orders using(customerid)

inner join order_details as od using(orderid)

where orderdate between date('Jan-1-1997') and date('June-30-1997')

group by customers.customerid

having sum((od.unitprice * od.quantity) - od.discount) > 5000

order by total_purchase desc;

select count(*), country

from customers

group by country

order by count(*) desc

SELECT

c1,

c2,

aggregate_function(c3)

FROM

table_name

GROUP BY

GROUPING SETS (

(c1, c2),

(c1),

(c2),

()

);

-- the number of products sold for all brands and segments

-- the number of products sold by segment

-- the number of products sold by a brand

-- the number of products sold by brand and segment together

group by grouping sets ((field1), (field2), (field3, field4))

categories.categoryname,

products.productname,

sum((od.quantity*od.unitprice)-od.discount) as total_sales

from categories

inner join products using(categoryid)

inner join order_details as od using(productid)

group by grouping sets(

(categories.categoryname),

(products.productname, categories.categoryname)

)

order by categories.categoryname, products.productname;

-- OR;

select

categories.categoryname as category,

products.productname as product,

sum((od.unitprice*od.quantity)-od.discount) as total_sales

from products inner join order_details as od using(productid)