--------
select *
from prod;
select *
from prod
order by prod_sale desc, prod_lgu, prod_name asc;
--21.p
-- Find products with sale price of 170,000 won
select prod_name ProductName
, prod_sale SalePrice
from prod
where prod_sale = 170000;
-- Find products above or below 170,000 won
select prod_name ProductName
, prod_sale SalePrice
from prod
where prod_sale < 170000;
select prod_name
, prod_sale SalePriceProductName
from prod
where prod_sale > 170000;
-- Find products at or above, or at or below 170,000 won
select prod_name ProductName
, prod_sale SalePrice
from prod
where prod_sale <= 170000;
select prod_name ProductName
, prod_sale SalePrice
from prod
where prod_sale <> 170000; -- not equal
select prod_id "ProductCode" -- when spaces are needed...
, prod_name "ProductName" -- but since it ties to variable names, use English with no spaces
, prod_cost "CostPrice"
from prod
where prod_cost <=200000;
select mem_id MemberID
, mem_name MemberName
, mem_regno1 RegNoFront
from member
--where mem_regno1 >= 760101; -- string:number --- auto cast -> number:number
where mem_regno1 >= '760101'; -- string:string
-- date takes precedence over string,
-- date:string(/-.) --- auto cast -> date:date
desc member; -- find data type
select prod_name Product
, prod_lgu ProductCategory
, prod_sale SalePrice
from prod;
select prod_name Product
, prod_lgu ProductCategory
, prod_sale SalePrice
from prod
where prod_lgu='P201'
and prod_sale=17000;
select prod_name Product
, prod_lgu ProductCategory
, prod_sale SalePrice
from prod
where prod_lgu='P201'
or prod_sale=17000;
select prod_name Product
, prod_lgu ProductCategory
, prod_sale SalePrice
from prod
where
not prod_sale=17000;
select prod_name
, prod_lgu
, prod_sale
from prod
where prod_lgu<>'P201' and prod_sale<>170000;
select prod_name
, prod_lgu
, prod_sale
from prod
where prod_lgu !='201'
and prod_sale !=170000;
-- De Morgan's law applied
select prod_name
, prod_lgu
, prod_sale
from prod
where not(prod_lgu ='P201'
or prod_sale =170000);-- De Morgan's law applied
select prod_id ,ProductCode
,prod_name ProductName
,prod_sale Price
from prod
where prod_sale betenn >=300000
and prod_sale <= 50000;
SELECT *
FROM member
where mem_job='CivilServant' and mem_mileage>=1500;
select prod_name ProductName
,prod_id ProductCode
,prod_sale ProductPrice
from prod
--where prod_sale=150000 or prod_sale=170000 or prod_sale=330000;
where prod_sale in(15000,17000,330000) and prod_cost > 100000
order by prod_sale asc, prod_sale asc, prod_sale asc;
select mem_id
,mem_name
from member
where mem_id in('c001','f001','w001');
--select * from lprod;
--select * from prod;
--select distinct prod_lgu; -- does this exist?
select lprod_id
,lprod_nm
from lprod
where lprod_gu in( --main SQL
select distinct prod_lgu --main SQL
from prod);
select lprod_id
,lprod_nm
from lprod
where lprod_gu not in
(select distinct prod_lgu from prod);
select buyer_id
,buyer_name
from buyer
where buyer_id in
(select distinct prod_buyer from prod);
select *
from prod
where prod_price between 100000 and 300000; -- range "from X up to Y"
select mem_id
, mem_name
, mem_bir
from member
where mem_bir between '19750101' and '19761231';
select prod_name
, prod_cost
, prod_sale
from prod
--where (prod_cost between 300000 and 15000000) -- same as below
--and (prod_sale between 800000 and 20000000);
where prod_cost between 300000 and 15000000
--where prod_cost between to_member('300,000','15,000,000') -- can also be declared separately
and prod_sale between 800000 and 20000000;
select mem_id
, mem_name
, mem_bir
, to_char(mem_bir,'yyyy-mm-dd') -- adjust output format
from member
--where not mem_bir between '1975/01/01' and '1975/12/31'; -- same as below, not position
--where mem_bir not between '1975/01/01' and '1975/12/31'; -- same as below
--where mem_bir not between '1975-01-01' and '1975-12-31'; -- same as below
where mem_bir not between '19750101' and '19751231';
https://www.w3schools.com/sql/sql_like.asp
