-- Example
-- For the row in buyer2 whose buyer_id is p30203
-- update the buyer_name value of buyer2 to 'Geoseong'
select *
from buyer2
where buyer_id = 'P30203';
update buyer2
set buyer_name='Geoseong'
where buyer_id = 'P30203';
select *
from buyer2
where buyer_id = 'P30203';
-- Delete the row from the buyer2 table whose buyer_name is 'Pirious'
select *
from buyer2
where buyer_name = 'Pirious';
delete from buyer2 -- deletes by 'row' / horizontal / row / tuple / record unit
where buyer_name = 'Pirious';
rollback;
update buyer2 -- to delete only a specific field
set buyer_name = null
where buyer_id = 'P30202'; -- cannot update ("PC04"."BUYER2"."BUYER_NAME") to NULL
alter table buyer2 -- NN -> null
modify(buyer_name varchar2(40) null);
update buyer2 -- to delete only a specific field
set buyer_name = null
where buyer_id = 'P30202'; -- cannot update ("PC04"."BUYER2"."BUYER_NAME") to NULL
select *
from BUYER2
where buyer_id = 'P30202';
-- From the product table prod, retrieve product code prod_id and product name prod_name
select prod_id product_code -- add an alias (supplementary description, column value label) in English
,prod_name as product_name
from prod; -- after FROM, always a table!
-- From the cart table, retrieve order number, product code, member id, and quantity
select cart_no order_no
, cart_prod "product_code" -- add an alias (supplementary description) in English
, cart_member as "member_id"
, cart_qty quantity -- omission is used most often
from cart;
------------- Arithmetic Operations-------------
-- Retrieve the mileage of the member table divided by 12
select mem_mileage
, mem_mileage/12
from MEMBER;
select mem_mileage member_mileage -- 'alias' is a name used briefly
, mem_mileage/12 divided_value -- a real number
, round(mem_mileage/12,2) rounded_value -- function - '2' means how many digits are shown. Rounded at the 3rd digit
, mem_mileage + 12 sum_value
, mem_mileage - 12 diff_value
, mem_mileage * 12 product_value
from MEMBER;
-----
select prod_id product_id,
prod_name product_name,
prod_price*55 unit_price
from prod;
select prod_lgu product_category
from prod;
select distinct prod_lgu product_category -- remove duplicates
from prod;
select distinct prod_buyer
from prod;
------------- Sorting database records
select mem_id member_id
,mem_name "dad_went_into_the_room"
,mem_bir birthday
,mem_mileage mileage
from member
order by mem_id asc;
select mem_name "dad_went_into_the_room" -- sorting is possible even if id is not selected
,mem_bir birthday
,mem_mileage mileage
from member
order by mem_id asc;
select * -- therefore, sorting is also possible after selecting all
from member
order by mem_id asc;
select mem_id member_id
,mem_name full_name
,mem_bir birthday
,mem_mileage mileage
from member
order by full_name asc; -- you can also sort by selecting an alias (ALIAS) item
select mem_id member_id
,mem_name full_name
,mem_bir birthday
,mem_mileage mileage
from member
order by 3 asc; -- sorts by the third item among the SELECTed items
select mem_id
,mem_name
,mem_bir
,mem_mileage
from member
order by mem_mileage, 1; -- asc is omitted after mem_mileage!
-- when mem_mileage is the same, a '2nd sort' is applied within the primary-sort range
select mem_id
,mem_name
,mem_bir
,mem_mileage
from member
order by mem_mileage asc, 1 asc; -- asc is omitted after both mem_mileage and 1!
select mem_id
,mem_name
,mem_bir
,mem_mileage
from member
order by mem_mileage desc, 2 asc; -- different sort orders can be combined
-- From the member table, retrieve member id, job, and hobby
-- Ascending by job, descending by hobby, ascending by member id
select mem_id member_id
, mem_job job
, mem_like hobby
from MEMBER
order by mem_job,mem_like desc, mem_id ; -- asc can be omitted
select mem_job,mem_memorial
from member;
update member
set mem_memorial = null
where mem_job = 'office_worker';
select mem_job,mem_memorial
from member
where mem_job = 'office_worker';
rollback;
-------------------------------------- Attachments
