자전거 가게 DB 만들기 실습

CREATE TABLE SHOP (ID INTEGER PRIMARY KEY, TYPE TEXT, LEVEL TEXT, BRAND TEXT, NAME TEXT, QUANTITY INTEGER);

 

 

INSERT INTO SHOP VALUES (1, "LOAD", "DURA", "GIANT", "ADVAN", 10);

INSERT INTO SHOP VALUES (2, "LOAD", "105", "SCOTT", "POIL", 5);

INSERT INTO SHOP VALUES (3, "MTB", "SRAM", "MERIDA", "XCL", 3);

INSERT INTO SHOP VALUES (4, "LOAD", "105", "TRIGON", "R", 3);

INSERT INTO SHOP VALUES (5, "LOAD", "ULTE", "TRIGON", "R", 2);

INSERT INTO SHOP VALUES (6, "LOAD", "ULTE", "SPECIAL", "VENGI", 3);

 

SELCET * FROM SHOP;

SELCET SUM(QUANTITY) FROM SHOP;

SELCET MAX(QUANTITY) FROM SHOP;

SELCET * FROM SHOP WHERE QUANTITY > 5 ORDER BY BRAND;

SELCET * FROM SHOP WHERE QUANTITY > 2 ORDER BY QUANTITY;

SELCET * FROM SHOP WHERE QUANTITY > 2 ORDER BY ID;

SELECT BRAND, ID, SUM(QUANTITY) FROM SHOP;

SELECT TYPE, ID, SUM(QUANTITY) FROM SHOP ORDER BY LEVEL;

 

 

스키마는

 

SHOP	6ROWS
ID(PK)	INTEGER
TYPE	TEXT
LEVEL	TEXT
BRAND	TEXT
NAME	TEXT
QUANTITY	INTEGER

 

 

쿼리는

ID	TYPE	LEVEL	BRAND	NAME	QUANTITY
1	LOAD	DURA	GIANT	ADVAN	10
2	LOAD	105	SCOTT	POIL	5
3	MTB	SRAM	MERIDA	XCL	3
4	LOAD	105	TRIGON	R	3
5	LOAD	ULTE	TRIGON	R	2
6	LOAD	ULTE	SPECIAL	VENGI	3

 

SUM(QUANTITY)

26

 

MAX(QUANTITY)

10

 

ID	TYPE	LEVEL	BRAND	NAME	QUANTITY
1	LOAD	DURA	GIANT	ADVAN	10

 

ID	TYPE	LEVEL	BRAND	NAME	QUANTITY
3	MTB	SRAM	MERIDA	XCL	3
4	LOAD	105	TRIGON	R	3
6	LOAD	ULTE	SRECIAL	VENGI	3
2	LOAD	105	SCOTT	POIL	5
1	LOAD	ULTE	GIANT	ADVAN	10

 

아이디 정렬은 위 쿼리에서 ID 기준으로 내림차순 (1,2,3,4,6 순으로)