MySQL_section6_CRUD Exercise

#1. CRUD Exercise 'Create'

 

(1) Create a new database 'shirts_db'

  CRAETE DATABASE shirts_db;
  USE shirts_db; #shirts_db를 사용하기 위해 선택

 

(2) Create a new table 'shirts'

 

  # shirts 테이블 생성
  CREATE TABLE shirts(
   shirt_id INT NOT NULL AUTO_INCREMENT,
   article VARCHAR(100),
   color VARCHAR(100),
   shirt_size VARCHAR(100),
   last_worn INT,
   PRIMARY KEY(shirt_id)   
  );  

 

  # 생성한 shirts 테이블에 데이터 입력
  INSERT INTO shirts(article, color, shirt_size, last_worn)
   VALUES  ('t-shirt', 'white', 'S', 10),
    ('t-shirt', 'green', 'S', 200),
    ('polo shirt', 'black', 'M', 10),
    ('tank top', 'blue', 'S', 50),
    ('t-shirt', 'pink', 'S', 0),
    ('polo shirt', 'red', 'M', 5),
    ('tank top', 'white', 'S', 200),
    ('tank top', 'blue', 'M', 15);

 

(3) Add a New shirt (Purple polo shirt, size M last worn 50 days ago)

  INSERT INTO shirts(article, color, shirt_size, last_worn VALUES ('purple', 'polo shirt', 'medium', 50);

 

 

#2. CRUD Exercise 'Read'

 

(4) Select all shirts

  SELECT * FROM shirts;

 

(5) Select all medium shirts

  SELECT * FROM shirts WEHRE shirt_size = 'M';

 

 

#3. CRUD Exercise 'Update'

 

(6) Update all polo shirts : Change their size to L

  UPDATE shirts SET shirt_size = 'L' WHERE article = 'polo shirt';

 

(7) Update the shirt last worn 15 days ago : Change last_worn to 0

  UPDATE shirts SET last_worn = 0 WHERE last_worn = 15;

 

(8) Update all white shirts : Change size to 'XS' and color to 'off white'

  UPDATE shirts SET color = 'off white', shirt_size = 'XS' WHERE color = 'white';

 

 

#4. CRUD Exercise 'Delete'

 

(9) Delete all old shirts : Last worn 200 days ago

  DELETE FROM shirts WHERE last_worn = 200;

 

(10) Delete all tank tops

  DELETE FROM shirts WHERE article = 'tank top';

 

(11) Delete all shirts

  DELETE FROM shirts;

 

(12) Drop the entire shirts table  
  DROP TABLE shirts;