--180607
-- Get all columns using asterisk
select * from lprod;
--(Column filtering - get specified columns
select lprod_gu,
lprod_nm,
ascii(lprod_gu) aci
FROM lprod;
--(Filter rows with WHERE, then get columns
select lprod_gu,
lprod_nm
FROM lprod
WHERE lprod_gu < 'P401'; -- strings are also expressed with ', data is case-sensitive (P < p)
--(Filter rows with WHERE, then get columns
select lprod_gu,
lprod_nm
FROM lprod
WHERE lprod_nm = 'Electronics'; -- strings are also expressed with '
-- Problem 01) Select the row with id = 3
select lprod_gu,
LPROD_NM,
lprod_id-- search range column slot
from lprod -- table slot
where lprod_id = '3'; -- search column and its specific attribute value
-- Problem 02) Select the row, row, tuple, record where id = 3
-- Check which table the column belongs to -> from ERD
select lprod_id,
lprod_gu,
lprod_nm
from lprod
where lprod = 3; -- check number via desc -> remove '' and input only number
-- even if lprod = '3' (string), it auto-casts. Then compares and finds matches
desc lprod;
--Problem 3)
-- Output all columns mem_id, mem_name, mem_mileagr
-- Select the row where mem_id is 'a001'
SELECT mem_id,
mem_name,
mem_mileage
from MEMBER
where mem_id ='a001';-- WHERE filters rows
-- Verification needed before update!
select *
from lprod
where lprod_gu='P102';
-- Replace Electronics with Perfume
-- Update syntax
update lprod
set lprod_nm='Perfume'
where lprod_gu='P102';
select * from lprod;
-- End transaction, return to last commit point
rollback;
-- Copy lprod table to create lprod2.
-- Primary and foreign keys are not copied
create table lprod2
as
select * from lprod;
--Problem_
--Update lprod2_nm from "Men's Casual" to "Books"
--where lprod_gu is P202 in lprod2 table.
select * --1 verify (check data)
from lprod2;
select * --1 pre-verify
from lprod2
where lprod_gu = 'P202';
update lprod2 --2 update
set lprod_nm='Books'
where lprod_gu = 'P202';-- use the verified line as-is!!
select * --3 confirm
from lprod2
where lprod_gu = 'P202';
--Problem_
--Update lprod_gu from 'P401' to 'P303'
--where lprod_id = 7 in lprod2 table
select * --1 verify (check data)
from lprod2;
select * --1 verify (check data)
from lprod2
where lprod_id = 7;
update lprod2 -- find target table --2 update
set lprod_gu ='P303' -- when copying the table earlier, names inside stay the same...
where lprod_id = 7;
select * -- what to fetch --3 verify update content
from lprod2 -- from~
where lprod_id = 7; -- condition
--Problem_
--In lprod2 table, modify data where lprod_nm is 'Cosmetics' to 'Test', and change lprod_id to 10
--[Hint] set column_name = 'modified_data', column_name2 = 'modified_data2',
select *
from LPROD2;
select *
from LPROD2
where lprod_nm ='Cosmetics';
update LPROD2
set lprod_nm ='Test', lprod_id = 10
where lprod_nm ='Cosmetics'; -- use verified data as-is!
select *
from LPROD2
where lprod_nm ='Test';
-- Delete the data (row) where lprod_nm is 'Cosmetics' in lprod2 table
select *
from LPROD2
where lprod_nm ='Cosmetics';
delete from lprod2 -- like ordering blue-backed fish // update has no from - upsert
where lprod_nm = 'Cosmetics';
select * from LPROD2;
--TCL transaction control lang — the logical unit that must be executed to 'modify' the database
rollback;
-- Fixed length, variable length
--cahr(6) 'a' where
select *
from test;
select *
from test
where col1 ='a'; -- in the past, when calling char, you had to input 'a '
-- so char(4) is rarely used for primary keys
-- the typical data type for primary keys is varchar2
-- however, fixed-length fields like resident ID or student ID are exceptions
-- Retrieve all rows and columns from the product table
--1) find the product table in ERD // ctrl up and down to search
select * from PROD;
-- Retrieve all rows and columns from the member table
select * from member;
-- Retrieve all rows and columns from the cart table
select * from cart;
--Problem_
-- Copy the buyer table to buyer2
-- Copy the buyer table to create buyer2.
-- Primary and foreign keys are not copied
create table buyer2
as
select * from buyer;
