SQL Á¤º¹ Äõ¸® ¿¹Á¦ ¸ðÀ½Áý 
ÃÖÃÊ ÀÛ¼ºÀÏ : 2021³â 1¿ù 11ÀÏ


-------------------------------------------------
-- 2Àå

CREATE TABLE tCity
(
	name CHAR(10) PRIMARY KEY,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL
);

INSERT INTO tCity VALUES ('¼­¿ï',605,974,'y','°æ±â');
INSERT INTO tCity VALUES ('ºÎ»ê',765,342,'y','°æ»ó');
INSERT INTO tCity VALUES ('¿À»ê',42,21,'n','°æ±â');
INSERT INTO tCity VALUES ('ûÁÖ',940,83,'n','Ãæû');
INSERT INTO tCity VALUES ('ÀüÁÖ',205,65,'n','Àü¶ó');
INSERT INTO tCity VALUES ('¼øõ',910,27,'n','Àü¶ó');
INSERT INTO tCity VALUES ('Ãáõ',1116,27,'n','°­¿ø');
INSERT INTO tCity VALUES ('ȫõ',1819,7,'n','°­¿ø');

SELECT * FROM tCity;

CREATE TABLE tStaff
(
	name CHAR (15) PRIMARY KEY,
	depart CHAR (10) NOT NULL,
	gender CHAR(3) NOT NULL,
	joindate DATE NOT NULL,
	grade CHAR(10) NOT NULL,
	salary INT NOT NULL,
	score DECIMAL(5,2) NULL
);

INSERT INTO tStaff VALUES ('±èÀ¯½Å','Ãѹ«ºÎ','³²','2000-2-3','ÀÌ»ç',420,88.8);
INSERT INTO tStaff VALUES ('À¯°ü¼ø','¿µ¾÷ºÎ','¿©','2009-3-1','°úÀå',380,NULL);
INSERT INTO tStaff VALUES ('¾ÈÁß±Ù','Àλç°ú','³²','2012-5-5','´ë¸®',256,76.5);
INSERT INTO tStaff VALUES ('À±ºÀ±æ','¿µ¾÷ºÎ','³²','2015-8-15','°úÀå',350,71.25);
INSERT INTO tStaff VALUES ('°­°¨Âù','¿µ¾÷ºÎ','³²','2018-10-9','»ç¿ø',320,56.0);
INSERT INTO tStaff VALUES ('Á¤¸ùÁÖ','Ãѹ«ºÎ','³²','2010-9-16','´ë¸®',370,89.5);
INSERT INTO tStaff VALUES ('Çã³­¼³Çå','Àλç°ú','¿©','2020-1-5','»ç¿ø',285,44.5);
INSERT INTO tStaff VALUES ('½Å»çÀÓ´ç','¿µ¾÷ºÎ','¿©','2013-6-19','ºÎÀå',400,92.0);
INSERT INTO tStaff VALUES ('¼º»ï¹®','¿µ¾÷ºÎ','³²','2014-6-8','´ë¸®',285,87.75);
INSERT INTO tStaff VALUES ('³í°³','Àλç°ú','¿©','2010-9-16','´ë¸®',340,46.2);
INSERT INTO tStaff VALUES ('ȲÁøÀÌ','Àλç°ú','¿©','2012-5-5','»ç¿ø',275,52.5);
INSERT INTO tStaff VALUES ('ÀÌÀ²°î','Ãѹ«ºÎ','³²','2016-3-8','°úÀå',385,65.4);
INSERT INTO tStaff VALUES ('ÀÌ»çºÎ','Ãѹ«ºÎ','³²','2000-2-3','´ë¸®',375,50);
INSERT INTO tStaff VALUES ('¾Èâȣ','¿µ¾÷ºÎ','³²','2015-8-15','»ç¿ø',370,74.2);
INSERT INTO tStaff VALUES ('À»Áö¹®´ö','¿µ¾÷ºÎ','³²','2019-6-29','»ç¿ø',330,NULL);
INSERT INTO tStaff VALUES ('Á¤¾à¿ë','Ãѹ«ºÎ','³²','2020-3-14','°úÀå',380,69.8);
INSERT INTO tStaff VALUES ('È«±æµ¿','Àλç°ú','³²','2019-8-8','Â÷Àå',380,77.7);
INSERT INTO tStaff VALUES ('´ëÁ¶¿µ','Ãѹ«ºÎ','³²','2020-7-7','Â÷Àå',290,49.9);
INSERT INTO tStaff VALUES ('À庸°í','Àλç°ú','³²','2005-4-1','ºÎÀå',440,58.3);
INSERT INTO tStaff VALUES ('¼±´ö¿©¿Õ','Àλç°ú','¿©','2017-8-3','»ç¿ø',315,45.1);

SELECT * FROM tStaff;

-------------------------------------------------
-- 4Àå

SELECT name, area, popu, metro, region FROM tCity;
SELECT * FROM tCity;

SELECT name AS µµ½Ã¸í, area AS "¸éÀû(Á¦°öKm)", popu AS "Àα¸(¸¸¸í)" FROM tCity;

SELECT µµ½Ã¸í = name, area '¸éÀû(Á¦°öKm)', popu [Àα¸(¸¸¸í)] FROM tCity;

SELECT name, popu * 10000 AS "Àα¸(¸í)" FROM tCity;

SELECT name, area, popu, popu * 10000 / area AS "Àα¸¹Ðµµ" FROM tCity;

SELECT 60 * 60 * 24 AS "ÇÏ·ç" FROM dual;

SELECT 60 * 60 * 24;

SELECT * FROM tCity WHERE area > 1000;

SELECT name, area FROM tCity WHERE area > 1000;

SELECT * FROM tCity WHERE name = '¼­¿ï'			-- ¸ÂÀ½
SELECT * FROM tCity WHERE name = ¼­¿ï				-- Ʋ¸²
SELECT * FROM tCity WHERE name = "¼­¿ï"			-- Ʋ¸². ´Ü, ¸¶¸®¾Æ´Â ÀÎÁ¤ÇÑ´Ù.

SELECT * FROM tStaff WHERE score = NULL;

SELECT * FROM tStaff WHERE score IS NULL;

SELECT * FROM tStaff WHERE score IS NOT NULL;

SELECT * FROM tCity WHERE popu >= 100 AND area >= 700;

SELECT * FROM tCity WHERE region = '°æ±â' AND popu >= 50 OR area >= 500;
SELECT * FROM tCity WHERE region = '°æ±â' AND (popu >= 50 OR area >= 500);

SELECT * FROM tCity WHERE region != '°æ±â';
SELECT * FROM tCity WHERE NOT(region = '°æ±â');

SELECT * FROM tCity WHERE region = 'Àü¶ó' OR metro = 'y';

SELECT * FROM tCity WHERE region != 'Àü¶ó' AND metro != 'y';

SELECT * FROM tCity WHERE NOT(region = 'Àü¶ó' OR metro = 'y');

SELECT * FROM tCity WHERE name LIKE '%õ%';

SELECT * FROM tCity WHERE name NOT LIKE '%õ%';

SELECT * FROM tCity WHERE name LIKE 'õ%';
SELECT * FROM tCity WHERE name LIKE '%õ';

SELECT * FROM tCity WHERE TRIM(name) LIKE '%õ';

SELECT * FROM tCity WHERE popu BETWEEN 50 AND 100;

SELECT * FROM tCity WHERE popu >= 50 AND popu <= 100;

SELECT * FROM tStaff WHERE name BETWEEN '°¡' AND '»ç';
SELECT * FROM tStaff WHERE joindate BETWEEN '20150101' AND '20180101';

SELECT * FROM tCity WHERE region IN ('°æ»ó', 'Àü¶ó');

SELECT * FROM tCity WHERE region = '°æ»ó' OR region = 'Àü¶ó';

SELECT * FROM tCity WHERE region NOT IN ('°æ»ó', 'Àü¶ó');

SELECT * FROM tStaff WHERE name LIKE IN ('ÀÌ%', '¾È%');

SELECT * FROM tStaff WHERE name LIKE 'ÀÌ%' OR name LIKE '¾È%';

SELECT * FROM tCity ORDER BY popu;
SELECT * FROM tCity ORDER BY popu DESC;

SELECT region, name, area, popu FROM tCity ORDER BY region, name DESC;

SELECT * FROM tCity ORDER BY area;
SELECT * FROM tCity ORDER BY 2;

SELECT name FROM tCity ORDER BY popu;

SELECT name, popu * 10000 / area FROM tCity ORDER BY popu * 10000 / area;

SELECT * FROM tCity WHERE region = '°æ±â' ORDER BY area;

SELECT * FROM tCity ORDER BY area WHERE region = '°æ±â';

SELECT region FROM tCity;
SELECT DISTINCT region FROM tCity;
SELECT DISTINCT region FROM tCity ORDER BY region;

SELECT ALL depart FROM tStaff;
SELECT DISTINCT depart FROM tStaff;

SELECT name, rowid, rownum FROM tCity;

SELECT * FROM tCity WHERE rownum <= 4;

SELECT * FROM tCity ORDER BY area DESC WHERE rownum <= 4;

SELECT * FROM tCity WHERE rownum <= 4 ORDER BY area DESC;

SELECT * FROM (SELECT * FROM tCity ORDER BY area DESC) WHERE rownum <= 4;

SELECT TOP 4 * FROM tCity ORDER BY area DESC;

SELECT TOP 20 PERCENT * FROM tCity ORDER BY popu DESC;

SELECT * FROM tExam ORDER BY Score DESC;

SELECT TOP 100 * FROM tExam ORDER BY Score DESC;

SELECT TOP 1 PERCENT WITH TIES * FROM tExam ORDER BY Score DESC;

SELECT * FROM tCity ORDER BY area DESC LIMIT 4;

SELECT * FROM tCity ORDER BY area DESC LIMIT 2, 3;

SELECT * FROM tCity ORDER BY area DESC OFFSET 0 ROWS FETCH NEXT 4 ROWS ONLY;

SELECT * FROM tCity ORDER BY area DESC OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

SELECT * FROM tCity WHERE metro = 'n' ORDER BY area DESC OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

-------------------------------------------------
-- 5Àå

SELECT COUNT(*) FROM tStaff;

SELECT COUNT(*) AS "ÃÑ Á÷¿ø¼ö" FROM tStaff;

SELECT COUNT(*) FROM tStaff WHERE salary >= 400;

SELECT COUNT(*) FROM tStaff WHERE salary >= 10000;

SELECT name FROM tStaff WHERE salary >= 400;

SELECT COUNT(name) FROM tStaff;
SELECT COUNT(depart) FROM tStaff;

SELECT COUNT(DISTINCT depart) FROM tStaff;

SELECT COUNT(score) FROM tStaff;

SELECT COUNT(*) - COUNT(score) FROM tStaff;
SELECT COUNT(*) FROM tStaff WHERE score IS NULL;

SELECT SUM(popu), AVG(popu) FROM tCity;

SELECT MIN(area), MAX(area) FROM tCity;

SELECT SUM(score), AVG(score) FROM tStaff WHERE depart = 'Àλç°ú';
SELECT MIN(salary), MAX(salary) FROM tStaff WHERE depart = '¿µ¾÷ºÎ';

SELECT SUM(name) FROM tStaff;		-- ¿¡·¯

SELECT MIN(name) FROM tStaff;

SELECT MAX(popu), name FROM tCity;

SELECT AVG(salary) FROM tStaff;
SELECT SUM(salary)/COUNT(*) FROM tStaff;

SELECT AVG(score) FROM tStaff;
SELECT SUM(score)/COUNT(*) FROM tStaff;

SELECT COUNT(*) FROM tStaff WHERE depart = 'ºñ¼­½Ç';
SELECT MAX(salary) FROM tStaff WHERE depart = 'ºñ¼­½Ç';

SELECT '¿µ¾÷ºÎ', AVG(salary) FROM tStaff WHERE depart='¿µ¾÷ºÎ';
SELECT 'Ãѹ«ºÎ', AVG(salary) FROM tStaff WHERE depart='Ãѹ«ºÎ';
SELECT 'Àλç°ú', AVG(salary) FROM tStaff WHERE depart='Àλç°ú';

SELECT depart, AVG(salary) FROM tStaff GROUP BY depart;

SELECT depart, COUNT(*), MAX(joindate), AVG(score) FROM tStaff GROUP BY depart; 

SELECT gender, AVG(salary) FROM tStaff GROUP BY gender;

SELECT name, SUM(salary) FROM tStaff GROUP BY name;

SELECT depart, gender, COUNT(*) FROM tStaff GROUP BY depart, gender;

SELECT gender, depart, COUNT(*) FROM tStaff GROUP BY gender, depart;

SELECT depart, gender, COUNT(*) FROM tStaff GROUP BY depart, gender 
ORDER BY depart, gender;

SELECT depart, salary FROM tStaff GROUP BY depart;

SELECT SUM(salary) FROM tStaff GROUP BY depart;

SELECT depart, SUM(salary) FROM tStaff;

SELECT depart, SUM(salary) FROM tStaff GROUP BY depart;
SELECT SUM(salary) FROM tStaff;

SELECT depart, AVG(salary) FROM tStaff GROUP BY depart HAVING AVG(salary) >= 340;

SELECT depart, AVG(salary) FROM tStaff GROUP BY depart HAVING AVG(salary) >= 340 
ORDER BY AVG(salary);

SELECT depart, AVG(salary) FROM tStaff WHERE salary > 300 GROUP BY depart ;

SELECT depart, AVG(salary) FROM tStaff WHERE salary > 300 
GROUP BY depart HAVING AVG(salary) >= 360 ORDER BY depart;

SELECT depart, MAX(salary) FROM tStaff WHERE depart IN ('Àλç°ú', '¿µ¾÷ºÎ') GROUP BY depart;
SELECT depart, MAX(salary) FROM tStaff GROUP BY depart HAVING depart IN ('Àλç°ú', '¿µ¾÷ºÎ');

-------------------------------------------------
-- 6Àå

INSERT INTO tCity (name, area, popu, metro, region) 
VALUES ('¼­¿ï', 605, 974, 'y', '°æ±â');

INSERT INTO tCity VALUES ('¼­¿ï', 605, 974, 'y', '°æ±â');

INSERT INTO tCity VALUES ('ÆòÅÃ', 453, 51, 'n', '°æ±â');

INSERT INTO tCity VALUES ('ÆòÅÃ', 51, 453, 'n', '°æ±â');		// area¿Í popu°¡ ¹Ù²ñ
INSERT INTO tCity VALUES ('ÆòÅÃ', 453, 'n', 51, '°æ±â');		// popu¿Í metro ¼ø¼­°¡ ¹Ù²ñ
INSERT INTO tCity VALUES ('ÆòÅÃ', 453, 'n', '°æ±â');		// popu Çʵ尪 ´©¶ô

INSERT INTO tCity (area, popu, metro, region, name) 
VALUES (453, 51, 'n', '°æ±â', 'ÆòÅÃ');

TRUNCATE TABLE tCity;

INSERT INTO tCity (name, area, popu, metro, region) VALUES 
('¼­¿ï',605,974,'y','°æ±â'), 
('ºÎ»ê',765,342,'y','°æ»ó'),
('¿À»ê',42,21,'n','°æ±â'),
('ûÁÖ',940,83,'n','Ãæû'),
('ÀüÁÖ',205,65,'n','Àü¶ó'),
('¼øõ',910,27,'n','Àü¶ó'),
('Ãáõ',1116,27,'n','°­¿ø'),
('ȫõ',1819,7,'n','°­¿ø');

INSERT INTO tStaff(name, depart, gender, joindate, grade, salary, score)
SELECT name, region, metro, '20210629', '½ÅÀÔ', area, popu FROM tCity WHERE region = '°æ±â';

INSERT INTO tStaff(name, depart, gender, joindate, grade, salary, score)
SELECT name, Áö¿øºÎ¼­, gender, ¿À´Ã, '¼ö½À', 230, score * 0.1 FROM tCandidate 
WHERE result = 'ÇÕ°Ý';

CREATE TABLE tSudo AS SELECT name, area, popu FROM tCity WHERE region = '°æ±â';
SELECT * FROM tSudo;

SELECT * INTO tCityCopy FROM tCity;

CREATE TABLE tStaff_8¿ù20ÀÏ AS SELECT * FROM tStaff;

DELETE FROM tCity WHERE name = 'ºÎ»ê';

DELETE FROM tCity WHERE region = '°æ±â';

DELETE FROM tCity;

SELECT * FROM tCity WHERE popu > 50;

-- DELETE
-- SELECT *
FROM tStaff WHERE grade = '°úÀå';

UPDATE tCity SET popu = 1000, region = 'Ãæû' WHERE name = '¼­¿ï';

UPDATE tCity SET popu = 1000, region = 'Ãæû';

UPDATE tCity SET popu = popu * 2 WHERE name = '¿À»ê';

-------------------------------------------------
-- 7Àå

INSERT INTO tCity VALUES ('Æò¾ç','¾öû ³ÐÀ½','²Ï ¸¹À»°É','n','Á¶¼±ÀιιÎÁÖÁÖÀÇ°øÈ­±¹');

INSERT INTO tCity (name, popu, metro, region) VALUES ('¿ï»ê', 114, 'y', '°æ»ó');
INSERT INTO tCity (name, metro, region) VALUES ('»ïô', 'n', '°­¿ø');

INSERT INTO tCity (area, popu, metro, region) VALUES (456, 123, 'n', 'Ãæû');
INSERT INTO tCity (name, area, popu) VALUES ('ÀÇÁ¤ºÎ', 456,123);

CREATE TABLE tNullable
(
    name CHAR(10) NOT NULL,
    age INT
);

CREATE TABLE tCityDefault
(
	name CHAR(10) PRIMARY KEY,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) DEFAULT 'n' NOT NULL,
	region CHAR(6) NOT NULL
);

INSERT INTO tCityDefault (name, area, popu, region) VALUES ('ÁøÁÖ', 712, 34, '°æ»ó');
INSERT INTO tCityDefault (name, area, popu, metro, region) VALUES ('ÀÎõ', 1063, 295, 'y', '°æ±â');

INSERT INTO tCityDefault VALUES ('°­¸ª', 1111, 22, '°­¿ø');			-- ¿¡·¯
INSERT INTO tCityDefault VALUES ('°­¸ª', 1111, 22, DEFAULT, '°­¿ø');	-- Á¤»ó ½ÇÇà

UPDATE tCity_Default SET metro = DEFAULT WHERE name = 'ÀÎõ'

CREATE TABLE tCheckTest
(
	gender CHAR(3) NULL CHECK(gender = '³²' OR gender = '¿©'),
	grade INT NULL CHECK (grade >= 1 AND grade <= 3),
	origin CHAR(3) NULL CHECK(origin IN ('µ¿','¼­','³²','ºÏ')),
	name CHAR(10) NULL CHECK(name LIKE '±è%')
);

INSERT INTO tCheckTest (gender) VALUES ('¿©');
INSERT INTO tCheckTest (grade) VALUES (1);
INSERT INTO tCheckTest (origin) VALUES ('µ¿');
INSERT INTO tCheckTest (name) VALUES ('±èÁÂÁø');

INSERT INTO tCheckTest (gender) VALUES ('³ë');
INSERT INTO tCheckTest (grade) VALUES (0);
INSERT INTO tCheckTest (origin) VALUES ('Áß');
INSERT INTO tCheckTest (name) VALUES ('û»ê¸®');

UPDATE tCheckTest SET grade = 4 WHERE grade IS NOT NULL;

region CHAR(6) NOT NULL CHECK (region IN ('°æ±â', 'Ãæû', '°­¿ø', '°æ»ó', 'Àü¶ó', 'Á¦ÁÖ'))
INSERT INTO tCity3 VALUES ('¿ï¸ª',72,1,'n','¿ì»ê');

INSERT INTO tCity VALUES ('Ãáõ',1116,27,'n','°­¿ø');

CREATE TABLE tCity
(
	name CHAR(10),
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL,
	CONSTRAINT PK_tCity_name PRIMARY KEY(name)
); 

CREATE TABLE tCity
(
	name CHAR(10) PRIMARY KEY,
	region CHAR(6) PRIMARY KEY,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL
);

CREATE TABLE tCityCompoKey
(
	name CHAR(10) NOT NULL,
	region CHAR(6) NOT NULL,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL,
	CONSTRAINT PK_tCity_name_region PRIMARY KEY (name, region)
);

INSERT INTO tCityCompoKey VALUES ('±¤ÁÖ', 'Àü¶ó', 123, 456, 'y');
INSERT INTO tCityCompoKey VALUES ('±¤ÁÖ', '°æ±â', 123, 456, 'n');

CREATE TABLE tCityUnique
(
	name CHAR(10) PRIMARY KEY,
	area INT NULL ,
	popu INT UNIQUE NULL,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL
);

CREATE TABLE tCityUnique
(
	name CHAR(10) PRIMARY KEY,
	area INT NULL ,
	popu INT NULL,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL,
	CONSTRAINT Unique_tCity_area_popu UNIQUE(area, popu)
);

CREATE SEQUENCE seqSale START WITH 1 INCREMENT BY 1;

CREATE TABLE tSale
(
	saleno INT PRIMARY KEY,
	customer VARCHAR(20),
	product VARCHAR(30)
);

INSERT INTO tSale VALUES (seqSale.NEXTVAL, '´Ü±º', 'ÁöÆÎÀÌ');
INSERT INTO tSale VALUES (seqSale.NEXTVAL, '°íÁÖ¸ù', '°íµî¾î');

INSERT INTO tSale VALUES (NEXT VALUE FOR seqSale, '´Ü±º', 'ÁöÆÎÀÌ');
INSERT INTO tSale VALUES (NEXT VALUE FOR seqSale, '°íÁÖ¸ù', '°íµî¾î');

DELETE FROM tSale WHERE saleno = 2;

¿À¶óŬ : INSERT INTO tSale VALUES (seqSale.NEXTVAL, '¹ÚÇõ°Å¼¼', '°è¶õ');
MSSQL : INSERT INTO tSale VALUES (NEXT VALUE FOR seqSale, '¹ÚÇõ°Å¼¼', '°è¶õ');

INSERT INTO tSale VALUES (2, '°íÁÖ¸ù', '°íµî¾î');

CREATE TABLE tSaleSeq
(
	saleno INT GENERATED AS IDENTITY PRIMARY KEY,
	customer VARCHAR(20),
	product VARCHAR(30)
);

INSERT INTO tSaleSeq (customer, product) VALUES ('´Ü±º', 'ÁöÆÎÀÌ');
INSERT INTO tSaleSeq (customer, product) VALUES ('°íÁÖ¸ù', '°íµî¾î');

saleno INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
saleno INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,

INSERT INTO tSale (saleno, customer, product) VALUES (5, '´Ü±º', 'ÁöÆÎÀÌ');	-- °¡´É
INSERT INTO tSale (saleno, customer, product) VALUES (NULL, '°íÁÖ¸ù', '°íµî¾î');	-- ¿¡·¯

CREATE TABLE tSaleId
(
	saleno INT IDENTITY PRIMARY KEY,
	customer VARCHAR(20),
	product VARCHAR(30)
);

INSERT INTO tSaleId (customer, product) VALUES ('´Ü±º', 'ÁöÆÎÀÌ');
INSERT INTO tSaleId (customer, product) VALUES ('°íÁÖ¸ù', '°íµî¾î');

DELETE FROM tSaleId WHERE saleno = 2;
INSERT INTO tSaleId (customer, product) VALUES ('¹ÚÇõ°Å¼¼', '°è¶õ');

INSERT INTO tSaleId (saleno, customer, product) VALUES (2, '°íÁÖ¸ù', '°íµî¾î');

SET IDENTITY_INSERT tSaleId ON;
INSERT INTO tSaleId (saleno, customer, product) VALUES (2, '°íÁÖ¸ù', '°íµî¾î');
SET IDENTITY_INSERT tSaleId OFF;

INSERT INTO tSaleId VALUES ('¿Õ°Ç', '³Ê±¸¸®');

UPDATE tSaleId SET product = 'Â¥ÆÄ°ÔƼ' WHERE saleno = @@IDENTITY;

CREATE TABLE tSale
(
	saleno INT AUTO_INCREMENT PRIMARY KEY,
	customer NCHAR(10),
	product NCHAR(30)
);

INSERT INTO tSale (customer, product) VALUES ('´Ü±º', 'ÁöÆÎÀÌ');
INSERT INTO tSale (customer, product) VALUES ('°íÁÖ¸ù', '°íµî¾î');

DELETE FROM tSale WHERE saleno = 2;
INSERT INTO tSale (customer, product) VALUES ('¹ÚÇõ°Å¼¼', '°è¶õ');

INSERT INTO tSale (saleno, customer, product) VALUES (2, '°íÁÖ¸ù', '°íµî¾î');

ALTER TABLE tSale AUTO_INCREMENT = 100;

INSERT INTO tSale (customer, product) VALUES ('¿Õ°Ç', '³Ê±¸¸®');
UPDATE tSale SET product = 'Â¥ÆÄ°ÔƼ' WHERE saleno = LAST_INSERT_ID();

-------------------------------------------------
-- 8Àå

UPDATE tProject SET ´ã´çÀÚ='¹®Á¾¹Î' WHERE ÇÁ·ÎÁ§Æ®='TV ±¤°í°Ç';

SELECT ÇÁ·ÎÁ§Æ®¸í FROM tProject WHERE ´ã´çÀÚ = '±è»óÇü';

CREATE TABLE tEmployee
(
	name CHAR(10) PRIMARY KEY,
	salary INT NOT NULL,
	addr VARCHAR(30) NOT NULL
);

INSERT INTO tEmployee VALUES ('±è»óÇü', 650, 'ÀÌõ½Ã');
INSERT INTO tEmployee VALUES ('¹®Á¾¹Î', 480, '´ë±¸½Ã');
INSERT INTO tEmployee VALUES ('±Ç¼ºÁ÷', 625, '¾Èµ¿½Ã');

CREATE TABLE tProject
(
	projectID INT PRIMARY KEY,
	employee CHAR(10) NOT NULL,
	project VARCHAR(30) NOT NULL,
	cost INT
);

INSERT INTO tProject VALUES (1, '±è»óÇü', 'È«Äá ¼öÃâ°Ç', 800);
INSERT INTO tProject VALUES (2, '±è»óÇü', 'TV ±¤°í°Ç', 3400);
INSERT INTO tProject VALUES (3, '±è»óÇü', '¸ÅÃâºÐ¼®°Ç', 200);
INSERT INTO tProject VALUES (4, '¹®Á¾¹Î', '°æ¿µ Çõ½Å¾È ÀÛ¼º', 120);
INSERT INTO tProject VALUES (5, '¹®Á¾¹Î', '´ë¸®Á¡ °èȹ', 85);
INSERT INTO tProject VALUES (6, '±Ç¼ºÁ÷', '³ëÁ¶ Çù»ó°Ç', 24);

INSERT INTO tProject VALUES (7, 'È«±æµ¿', '¿øÀÚÀç ¸ÅÀÔ', 900);

DELETE FROM tEmployee WHERE name = '±è»óÇü';

CREATE TABLE tProject
(
	projectID ... ,
	employee ... ,
	project ... ,
	cost ... ,
	CONSTRAINT FK_emp FOREIGN KEY(employee) REFERENCES tEmployee(name)
);

INSERT INTO tEmployee VALUES ('È«±æµ¿', 330, 'À强');
INSERT INTO tProject VALUES (7, 'È«±æµ¿', '¿øÀÚÀç ¸ÅÀÔ', 900);

DELETE FROM tProject WHERE employee = '±è»óÇü';
DELETE FROM tEmployee WHERE name = '±è»óÇü';

DROP TABLE tEmployee;

CREATE TABLE tProject
(
	....
	CONSTRAINT FK_emp FOREIGN KEY(employee) REFERENCES tEmployee(name)
	ON DELETE CASCADE
);

DELETE FROM tEmployee WHERE name = '±è»óÇü';

CREATE TABLE tProject
(
	....
	CONSTRAINT FK_emp FOREIGN KEY(employee) REFERENCES tEmployee(name)
	ON DELETE CASCADE ON UPDATE CASCADE
);

UPDATE tEmployee SET name = '¹®»çÀå' WHERE Name = '¹®Á¾¹Î';

-- ȸ¿ø Å×À̺í
CREATE TABLE tMember
(
    member VARCHAR(20) PRIMARY KEY, 	-- ¾ÆÀ̵ð
    age INT NOT NULL,                      	-- ³ªÀÌ 
    email VARCHAR(30) NOT NULL,           	-- À̸ÞÀÏ
    addr VARCHAR(50) NOT NULL,            	-- ÁÖ¼Ò
    money INT DEFAULT 1000 NOT NULL,     	-- ¿¹Ä¡±Ý
    grade INT DEFAULT 1 NOT NULL,         	-- °í°´µî±Þ. 1=ÁØȸ¿ø, 2=Á¤È¸¿ø, 3=¿ì¼öȸ¿ø
    remark VARCHAR(100) NULL             	-- ¸Þ¸ð »çÇ×
);

-- ȸ¿ø µ¥ÀÌÅÍ
INSERT INTO tMember VALUES ('ÃáÇâ',16,'1004@naver.com','Àü³² ³²¿ø½Ã',20000, 2, '');
INSERT INTO tMember VALUES ('À̵µ·É',18,'wolf@gmail.com','¼­¿ï ½Å»çµ¿',150000, 3, '');
INSERT INTO tMember VALUES ('Çâ´Ü',25,'candy@daum.net','Àü³² ³²¿ø½Ã',5000, 2, '');
INSERT INTO tMember VALUES ('¹æÀÚ',28,'devlin@ssang.co.kr','¼­¿ï °³Æ÷µ¿',1000, 1, '¿äÁÖÀÇ °í°´');

-- »óÇ° ºÐ·ù Å×À̺í
CREATE TABLE tCategory
(
   category VARCHAR(10) PRIMARY KEY,	-- ºÐ·ù¸í
   discount INT NOT NULL,			-- ÇÒÀÎÀ²
   delivery INT NOT NULL,                   	-- ¹è¼Ûºñ
   takeback CHAR(1)                         	-- ¹ÝÇ° °¡´É¼º
);

-- ºÐ·ù µ¥ÀÌÅÍ
INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('½ÄÇ°', 0, 3000, 'n');
INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('ÆмÇ', 10, 2000, 'y');
INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('°¡Àü', 20, 2500, 'y');
INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('¼ºÀÎ', 5, 1000, 'n');

-- »óÇ° Å×À̺í
CREATE TABLE tItem
(
    item VARCHAR(20) PRIMARY KEY,   	-- »óÇ°¸í
    company VARCHAR(20) NULL,            	-- Á¦Á¶»ç
    num INT NOT NULL,                      	-- Àç°í
    price INT NOT NULL,                     	-- Á¤°¡
    category VARCHAR(10) NOT NULL,       	-- ºÐ·ù
    CONSTRAINT item_fk FOREIGN KEY(category) REFERENCES tCategory(category)
);

-- »óÇ° µ¥ÀÌÅÍ
INSERT INTO tItem (item,company,num,price,category) VALUES ('³ëÆ®ºÏ', '»ù¼º', 3, 820000, '°¡Àü');
INSERT INTO tItem (item,company,num,price,category) VALUES ('û¹ÙÁö', '¹æ¹æ', 80, 32000, 'ÆмÇ');
INSERT INTO tItem (item,company,num,price,category) VALUES ('»ç°ú', '¹®°æ³ó¿ø', 24, 16000, '½ÄÇ°');
INSERT INTO tItem (item,company,num,price,category) VALUES ('´ëÃß', 'º¸Àº³ó¿ø', 19, 15000, '½ÄÇ°');
INSERT INTO tItem (item,company,num,price,category) VALUES ('ÀüÀÚ´ã¹è', 'TNG', 4, 70000, '¼ºÀÎ');
INSERT INTO tItem (item,company,num,price,category) VALUES ('¸¶¿ì½º', '³í¸®ÅØ', 3, 90000, '°¡Àü');

-- ÁÖ¹® Å×À̺í
MSSQL :
CREATE TABLE tOrder
(
  orderID INT IDENTITY PRIMARY KEY,		-- ÁÖ¹® ¹øÈ£
  member VARCHAR(20) NOT NULL,   		-- ÁÖ¹®ÀÚ
  item VARCHAR(20) NOT NULL,			-- »óÇ°
  orderDate DATE DEFAULT GETDATE() NOT NULL,	-- ÁÖ¹® ³¯ÀÚ
  num INT NOT NULL,				-- °³¼ö
  status INT DEFAULT 1 NOT NULL,			-- 1:ÁÖ¹®, 2:¹è¼ÛÁß, 3:¹è¼Û¿Ï·á, 4:¹ÝÇ°
  remark VARCHAR(1000) NULL			-- ¸Þ¸ð »çÇ×
);

¿À¶óŬ :
CREATE TABLE tOrder
(
  orderID INT GENERATED AS IDENTITY PRIMARY KEY,	-- ÁÖ¹® ¹øÈ£
  member VARCHAR(20) NOT NULL,   		-- ÁÖ¹®ÀÚ
  item VARCHAR(20) NOT NULL,			-- »óÇ°
  orderDate DATE DEFAULT SYSDATE NOT NULL,	-- ÁÖ¹® ³¯ÀÚ
  num INT NOT NULL,				-- °³¼ö
  status INT DEFAULT 1 NOT NULL,			-- 1:ÁÖ¹®, 2:¹è¼ÛÁß, 3:¹è¼Û¿Ï·á, 4:¹ÝÇ°
  remark VARCHAR2(1000) NULL			-- ¸Þ¸ð »çÇ×
);

¸¶¸®¾Æ :
CREATE TABLE tOrder
(
  orderID INT AUTO_INCREMENT PRIMARY KEY,	-- ÁÖ¹® ¹øÈ£
  member VARCHAR(20) NOT NULL,   		-- ÁÖ¹®ÀÚ
  item VARCHAR(20) NOT NULL,			-- »óÇ°
  orderDate DATE DEFAULT CURDATE() NOT NULL,	-- ÁÖ¹® ³¯ÀÚ
  num INT NOT NULL,				-- °³¼ö
  status INT DEFAULT 1 NOT NULL,			-- 1:ÁÖ¹®, 2:¹è¼ÛÁß, 3:¹è¼Û¿Ï·á, 4:¹ÝÇ°
  remark VARCHAR(1000) NULL			-- ¸Þ¸ð »çÇ×
);

-- ÁÖ¹® µ¥ÀÌÅÍ
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('ÃáÇâ','û¹ÙÁö','2019-12-3',3,2);
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('Çâ´Ü','´ëÃß','2019-12-4',10,1);
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('¹æÀÚ','ÀüÀÚ´ã¹è','2019-12-2',4,1);
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('Çâ´Ü','»ç°ú','2019-12-5',5,2);
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('ÈïºÎ','³ëÆ®ºÏ','2019-12-5',2,1);
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('¹æÀÚ','ÇÚµåÆù','2019-11-1',1,3);

member VARCHAR(20) NOT NULL REFERENCES tMember(member),   	-- ÁÖ¹®ÀÚ
item VARCHAR(20) NOT NULL REFERENCES tItem(item),			-- »óÇ°

DROP TABLE tOrder, tItem, tCategory, tMember;

-------------------------------------------------
-- 9Àå

SELECT MAX(popu), name FROM tCity;

SELECT name FROM tCity WHERE popu = MAX(popu);

SELECT MAX(popu) FROM tCity;

SELECT name FROM tCity WHERE popu = 974;

SELECT name FROM tCity WHERE popu = (SELECT MAX(popu) FROM tCity);

SELECT MAX(num) FROM tItem;

SELECT item FROM tItem WHERE num = 80;

SELECT item FROM tItem WHERE num = (SELECT MAX(num) FROM tItem);

SELECT category FROM tItem WHERE item= 'û¹ÙÁö';

SELECT delivery FROM tCategory WHERE category = 'ÆмÇ';

SELECT delivery FROM tCategory WHERE category = (SELECT category FROM tItem 
WHERE item = 'û¹ÙÁö');

SELECT item FROM tItem WHERE price = 70000;

SELECT member FROM tOrder WHERE item = 
(SELECT item FROM tItem WHERE price = 70000);

SELECT age FROM tMember WHERE member = 
(SELECT member FROM tOrder WHERE item = 
(SELECT item FROM tItem WHERE price = 70000));

SELECT price FROM tItem WHERE item = 
(SELECT item FROM tOrder WHERE member = 'Çâ´Ü');

SELECT price FROM tItem WHERE item = 
(SELECT item FROM tOrder WHERE member = 'Çâ´Ü' 
ORDER BY item OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY);

SELECT item, price FROM tItem WHERE item IN 
(SELECT item FROM tOrder WHERE member = 'Çâ´Ü');

SELECT item, price FROM tItem WHERE item IN ('´ëÃß', '»ç°ú');

SELECT price FROM tItem WHERE item = (SELECT item FROM tOrder WHERE member = 'À̵µ·É');

SELECT depart, gender FROM tStaff WHERE name = 'À±ºÀ±æ';

SELECT * FROM tStaff WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²';

SELECT * FROM tStaff WHERE depart = 
(SELECT depart FROM tStaff WHERE name = '¾ÈÁß±Ù')
AND gender = (SELECT gender FROM tStaff WHERE name = '¾ÈÁß±Ù');

SELECT * FROM tStaff WHERE (depart, gender) = 
(SELECT depart, gender FROM tStaff WHERE name = '¾ÈÁß±Ù');

SELECT * FROM tStaff WHERE (depart, salary) IN 
(SELECT depart, MAX(salary) FROM tStaff GROUP BY depart);

SELECT * FROM tStaff S WHERE salary = 
(SELECT MAX(salary) FROM tStaff WHERE depart = S.depart);

SELECT T.* FROM tStaff T
INNER JOIN (SELECT depart, MAX(salary) ms FROM tStaff GROUP BY depart) M
ON T.depart = M.depart AND T.salary = M.ms;

UPDATE tCity SET (area, popu) = (SELECT area, popu FROM tCity WHERE name='ºÎ»ê') 
WHERE name = '¼­¿ï';

SELECT name FROM tStaff WHERE salary > ANY 
(SELECT salary FROM tStaff WHERE depart = '¿µ¾÷ºÎ');
SELECT name FROM tStaff WHERE salary > ALL 
(SELECT salary FROM tStaff WHERE depart = '¿µ¾÷ºÎ');

SELECT name FROM tStaff WHERE salary > 
(SELECT MIN(salary) FROM tStaff WHERE depart = '¿µ¾÷ºÎ');
SELECT name FROM tStaff WHERE salary > 
(SELECT MAX(salary) FROM tStaff WHERE depart = '¿µ¾÷ºÎ');

SELECT item, price FROM tItem WHERE item = ANY 
(SELECT item FROM tOrder WHERE member = 'Çâ´Ü');

SELECT member, item, (SELECT price FROM tItem WHERE tItem.item = tOrder.item) price 
FROM tOrder;

SELECT O.member, O.item, (SELECT price FROM tItem I WHERE I.item = O.item) price 
FROM tOrder O;

SELECT O.member, O.item, I.price FROM tOrder O JOIN tItem I ON I.item = O.item; 

EXISTS (SELECT * FROM tCity WHERE area > 1000)

SELECT name FROM tCity WHERE EXISTS (SELECT * FROM tCity WHERE area > 1000);

SELECT name FROM tCity C WHERE EXISTS (SELECT * FROM tCity WHERE C.area > 1000);

SELECT * FROM tMember M WHERE EXISTS 
(SELECT * FROM tOrder O WHERE O.member = M.member);

SELECT * FROM tMember M WHERE NOT EXISTS 
(SELECT * FROM tOrder O WHERE O.member = M.member);

SELECT * FROM tMember WHERE member IN (SELECT DISTINCT member FROM tOrder);

....EXISTS (SELECT item FROM tOrder O WHERE O.member = M.member);
....EXISTS (SELECT member FROM tOrder O WHERE O.member = M.member);
....EXISTS (SELECT '¾ó¾¾±¸' FROM tOrder O WHERE O.member = M.member);

SELECT * FROM (SELECT * FROM tCity) A;

SELECT * FROM (SELECT name, popu, area FROM tCity) A;
SELECT * FROM (SELECT * FROM tCity WHERE metro = 'y') B;

SELECT member, addr FROM (SELECT * FROM tMember WHERE age < 19) A 
WHERE A.money >= 100000;

SELECT member, addr FROM tMember WHERE age < 19 AND money >= 100000;

SELECT * FROM (SELECT * FROM tStaff WHERE grade = '°úÀå' OR grade = 'ºÎÀå') A 
WHERE A.score >= 70;

SELECT * FROM tStaff WHERE grade = '°úÀå' OR grade = 'ºÎÀå' AND score >= 70;

SELECT member, addr FROM (SELECT * FROM tMember WHERE age < 19) A 
WHERE A.money >= 100000;

SELECT * FROM (SELECT * FROM tCity WHERE metro = 'y') A;

SELECT * FROM (SELECT * FROM tCity WHERE metro = 'y') AS A;

SELECT name, popu * 10000 AS ingu FROM tCity;

SELECT name, popu * 10000 AS ingu FROM tCity WHERE ingu > 1000000;

SELECT * FROM (
	SELECT name, popu * 10000 AS ingu FROM tCity
) A
WHERE A.ingu > 1000000;

SELECT * FROM (
	SELECT name, popu * 10000 AS ingu FROM tCity
)
WHERE ingu > 1000000;

SELECT name, (popu * 10000 / area) AS dens FROM tCity;

SELECT name, (popu * 10000 / area) AS dens 
	,CASE 
		WHEN (popu * 10000 / area) > 1000 THEN '°í¹Ðµµ'
		WHEN (popu * 10000 / area) > 100 THEN 'Á߹еµ'
		ELSE 'Àú¹Ðµµ'
	END densgrade
FROM tCity;

SELECT name, (popu * 10000 / area) AS dens 
	,CASE 
		WHEN dens > 1000 THEN '°í¹Ðµµ'
		WHEN dens > 100 THEN 'Á߹еµ'
		ELSE 'Àú¹Ðµµ'
	END densgrade
FROM tCity;

SELECT name, dens 
	,CASE 
		WHEN dens > 1000 THEN '°í¹Ðµµ'
		WHEN dens > 100 THEN 'Á߹еµ'
		ELSE 'Àú¹Ðµµ'
	END densgrade
FROM
(
	SELECT name, (popu * 10000 / area) AS dens FROM tCity
) CD;

SELECT name, dens 
	,CASE 
		WHEN dens > 1000 THEN '°í¹Ðµµ'
		WHEN dens > 100 THEN 'Á߹еµ'
		ELSE 'Àú¹Ðµµ'
	END densgrade
	,
    CASE
    WHEN
        CASE 
            WHEN dens > 1000 THEN '°í¹Ðµµ'
            WHEN dens > 100 THEN 'Á߹еµ'
            ELSE 'Àú¹Ðµµ'
        END = '°í¹Ðµµ' THEN '8Â÷·Î'
    WHEN
        CASE 
            WHEN dens > 1000 THEN '°í¹Ðµµ'
            WHEN dens > 100 THEN 'Á߹еµ'
            ELSE 'Àú¹Ðµµ'
        END = 'Á߹еµ' THEN '4Â÷·Î'
    ELSE '2Â÷·Î' 
    END roadplan
FROM
(
	SELECT name, (popu * 10000 / area) AS dens FROM tCity
) CD;

SELECT name, dens, densgrade,
CASE
    WHEN densgrade = '°í¹Ðµµ' THEN '8Â÷·Î'
    WHEN densgrade = 'Á߹еµ' THEN '4Â÷·Î'
    ELSE '2Â÷·Î' 
END roadplan
FROM
(
    SELECT name, dens 
        ,CASE 
            WHEN dens > 1000 THEN '°í¹Ðµµ'
            WHEN dens > 100 THEN 'Á߹еµ'
            ELSE 'Àú¹Ðµµ'
        END densgrade
    FROM
    (
        SELECT name, (popu * 10000 / area) AS dens FROM tCity
    ) CD
) CR;

SELECT * FROM tItem WHERE category = '½ÄÇ°' 
UNION 
SELECT * FROM tItem WHERE category = '°¡Àü';

SELECT DISTINCT depart FROM tStaff WHERE salary > 400 
UNION ALL
SELECT DISTINCT depart FROM tStaff WHERE score > 80;

SELECT * FROM tItem WHERE category = '½ÄÇ°' OR category = '°¡Àü';

SELECT * FROM tMember UNION SELECT * FROM tItem;

SELECT member FROM tMember 
UNION
SELECT name FROM tStaff 
UNION
SELECT name FROM tEmployee;

SELECT * FROM ºÎ»ê´ë¸®Á¡ UNION SELECT * FROM ¼­¿ï´ë¸®Á¡;

SELECT name FROM tStaff WHERE depart = '¿µ¾÷ºÎ'
INTERSECT 
SELECT name FROM tStaff WHERE gender = '¿©';

SELECT name FROM tStaff
INTERSECT
SELECT member FROM tMember;

SELECT name FROM tStaff WHERE depart = '¿µ¾÷ºÎ'
MINUS 
SELECT name FROM tStaff WHERE gender = '¿©';

SELECT name FROM tStaff WHERE gender = '¿©'
MINUS
SELECT name FROM tStaff WHERE depart = '¿µ¾÷ºÎ';

¿À¶óŬ, ¸¶¸®¾Æ : CREATE TABLE tStaff2 AS SELECT * FROM tStaff;
MSSQL : SELECT * INTO tStaff2 FROM tStaff;

UPDATE tStaff2 SET salary = 500 WHERE name = '¾Èâȣ';
UPDATE tStaff2 SET depart = 'Àλç°ú' WHERE name = '¼º»ï¹®';
DELETE FROM tStaff2 WHERE name = 'È«±æµ¿';
INSERT INTO tStaff2 VALUES ('¾î¿ìµ¿', 'Ãѹ«ºÎ', '¿©', '20220401', '½ÅÀÔ', 450, 0);

SELECT * FROM tStaff2
MINUS 
SELECT * FROM tStaff;

¿À¶óŬ, ¸¶¸®¾Æ : CREATE TABLE tCityNew AS SELECT * FROM tCity;
MSSQL : SELECT * INTO tCityNew FROM tCity;

UPDATE tCityNew SET popu = 1000 WHERE name = '¼­¿ï';
UPDATE tCityNew SET area = 900 WHERE name = 'ºÎ»ê';
DELETE FROM tCityNew WHERE name = '̵̇';
INSERT INTO tCityNew VALUES ('ÀÌõ',461,21,'n','°æ±â');

MERGE INTO tCity T USING tCityNew S ON (S.name = T.name)
WHEN MATCHED THEN
	UPDATE SET T.area = S.area, T.popu = S.popu
WHEN NOT MATCHED THEN
	INSERT VALUES (S.name, S.area, S.popu, S.metro, S.region);

MERGE INTO tCity T USING (SELECT * FROM tCityNew WHERE region = '°æ±â') S ON (S.name = T.name)
....

MERGE INTO tCity T USING tCityNew S ON (S.name = T.name AND S.region = '°æ±â')
....

CREATE TABLE tCityPopu
(
	name CHAR(10) PRIMARY KEY,
	popu INT NULL
);

INSERT INTO tCityPopu VALUES ('¼­¿ï',1000);
INSERT INTO tCityPopu VALUES ('ºÎ»ê',500);
INSERT INTO tCityPopu VALUES ('̵̇',100);

MERGE INTO tCity C USING tCityPopu P ON (C.name = P.name)
WHEN MATCHED THEN UPDATE SET C.popu = P.popu;

UPDATE tCity SET popu = tCityPopu.popu FROM tCityPopu WHERE tCity.name = tCityPopu.name;

UPDATE tCity SET popu = S.popu FROM tCityPopu AS S WHERE tCity.name = S.name;

UPDATE tCity AS T SET T.popu = S.popu FROM tCityPopu AS S WHERE T.name = S.name;

UPDATE tCity SET popu = (SELECT popu FROM tCityPopu P WHERE P.name = tCity.name)
WHERE name IN (SELECT name FROM tCityPopu)

-------------------------------------------------
-- 10Àå

CREATE TABLE tCar
(
	car VARCHAR(30) NOT NULL,		-- À̸§
	capacity INT NOT NULL,			-- ¹è±â·®
	price INT NOT NULL,			-- °¡°Ý
	maker VARCHAR(30) NOT NULL		-- Á¦Á¶»ç
);

INSERT INTO tCar (car, capacity, price, maker) VALUES ('¼Ò³ªÅ¸', 2000, 2500, 'Çö´ë');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('Ƽº¼¸®', 1600, 2300, '½Ö¿ë');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('A8', 3000, 4800, 'Audi');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('SM5', 2000, 2600, '»ï¼º');

CREATE TABLE tMaker
(
	maker VARCHAR(30) NOT NULL,		-- ȸ»ç
	factory CHAR(10) NOT NULL,	    	-- °øÀå
	domestic CHAR(1) NOT NULL		-- ±¹»ê ¿©ºÎ. Y/N
);

INSERT INTO tMaker (maker, factory, domestic) VALUES ('Çö´ë', 'ºÎ»ê', 'y');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('½Ö¿ë', 'ûÁÖ', 'y');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('Audi', 'µ¶ÀÏ', 'n');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('±â¾Æ', '¼­¿ï', 'y');

SELECT * FROM tCar, tMaker;

SELECT * FROM tCar CROSS JOIN tMaker;

SELECT * FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

SELECT tCar.car, tCar.price, tMaker.maker, tMaker.factory FROM tCar, tMaker 
WHERE tCar.maker = tMaker.maker;

SELECT tCar.*, tMaker.factory FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

SELECT car, price, tMaker.maker, factory FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

SELECT car, price, maker, factory FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

SELECT C.car, C.price, M.maker, M.factory FROM tCar C, tMaker M WHERE C.maker = M.maker;

SELECT A.car, A.price, B.maker, B.factory FROM tCar A, tMaker B WHERE A.maker = B.maker;

SELECT C.car, C.price, M.maker, M.factory FROM tCar C INNER JOIN tMaker M 
ON C.maker = M.maker;

SELECT C.car, C.price, M.maker, M.factory FROM tMaker M INNER JOIN tCar C 
ON M.maker = C.maker;

SELECT C.car, C.price, maker, M.factory FROM tCar C INNER JOIN tMaker M USING(maker);

SELECT C.car, C.price, M.maker, M.factory FROM tCar C LEFT OUTER JOIN tMaker M 
ON C.maker = M.maker;

SELECT C.car, C.price, M.maker, M.factory FROM tCar C RIGHT OUTER JOIN tMaker M 
ON C.maker = M.maker;

SELECT C.car, C.price, M.maker, M.factory FROM tMaker M LEFT OUTER JOIN tCar C 
ON C.maker = M.maker;

SELECT C.car, C.price, M.maker, M.factory FROM tCar C FULL OUTER JOIN tMaker M 
ON C.maker = M.maker;

SELECT * FROM tCar C 
INNER JOIN tMaker M ON C.maker = M.maker 
INNER JOIN tCity T ON M.factory = T.name;

SELECT C.car, M.factory, T.area FROM tCar C 
INNER JOIN tMaker M ON C.maker = M.maker 
INNER JOIN tCity T ON M.factory = T.name;

SELECT * FROM tCar C 
LEFT OUTER JOIN tMaker M ON C.maker = M.maker 
LEFT OUTER JOIN tCity T ON M.factory = T.name;

SELECT * FROM tMaker M 
INNER JOIN tCity T ON M.factory = T.name 
INNER JOIN tCar C ON M.maker = C.maker;

SELECT * FROM tMaker M 
LEFT OUTER JOIN tCity T ON M.factory = T.name 
LEFT OUTER JOIN tCar C ON M.maker = C.maker;

SELECT maker FROM tCar WHERE car = 'Ƽº¼¸®';
SELECT factory FROM tMaker WHERE maker = '½Ö¿ë';

SELECT factory FROM tMaker WHERE maker = 
(SELECT maker FROM tCar WHERE car = 'Ƽº¼¸®');

SELECT * FROM tCar C INNER JOIN tMaker M ON M.maker = C.maker;

SELECT * FROM tCar C INNER JOIN tMaker M ON M.maker = C.maker AND C.car = 'Ƽº¼¸®';

SELECT M.factory FROM tCar C INNER JOIN tMaker M 
ON M.maker = C.maker AND C.car = 'Ƽº¼¸®';

SELECT M.factory, C.price FROM tCar C INNER JOIN tMaker M 
ON M.maker = C.maker AND C.car = 'Ƽº¼¸®';

SELECT factory, price FROM tMaker WHERE maker = 
(SELECT maker FROM tCar WHERE car = 'Ƽº¼¸®');

SELECT factory, (SELECT price FROM tCar WHERE car = 'Ƽº¼¸®') AS price 
FROM tMaker WHERE maker = (SELECT maker FROM tCar WHERE car = 'Ƽº¼¸®');

SELECT C.*, M.factory, M.domestic FROM tCar C INNER JOIN tMaker M 
ON M.maker = C.maker AND C.car = 'Ƽº¼¸®';

INSERT INTO tCar (car, capacity, price, maker) VALUES ('Ƽº¼¸®', 1800, 2600, '½Ö¿ë');

SELECT C.*, M.factory, M.domestic FROM tCar C LEFT OUTER JOIN tMaker M 
ON C.maker = M.maker;

INSERT INTO tCar (car, capacity, price, maker) VALUES ('¼Ò³ªÅ¸', 2400, 2900, 'Çö´ë');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('Çö´ë', '¿ï»ê', 'y');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('Çö´ë', '¸¶»ê', 'y');

SELECT * FROM tCar C INNER JOIN tMaker M ON C.maker = M.maker;

SELECT * FROM tCar C INNER JOIN tMaker M ON C.maker = M.maker WHERE C.capacity = 2000;

SELECT * FROM tCar C INNER JOIN tMaker M ON C.maker = M.maker AND C.capacity = 2000;

SELECT * FROM tCar C LEFT OUTER JOIN tMaker M ON C.maker = M.maker 
WHERE C.capacity = 2000;

SELECT * FROM tCar C LEFT OUTER JOIN tMaker M 
ON C.maker = M.maker AND C.capacity = 2000;

SELECT * FROM tCar C LEFT OUTER JOIN tMaker M 
ON C.maker = M.maker AND C.capacity = 2000 WHERE C.price > 2800 ORDER BY price DESC;

SELECT * FROM tCar WHERE capacity > 2000 C LEFT JOIN tMaker M ON C.maker = M.maker;

SELECT * FROM (SELECT * FROM tCar WHERE capacity > 2000) C 
LEFT JOIN tMaker M ON C.maker = M.maker;

SELECT * FROM tCar C LEFT JOIN tMaker M ON C.maker = M.maker WHERE C.capacity > 2000;

SELECT * FROM (SELECT * FROM tCar WHERE capacity > 2000) C LEFT JOIN 
(SELECT * FROM tMaker WHERE factory = '¿ï»ê') M ON C.maker = M.maker;

SELECT * FROM (SELECT * FROM tCar WHERE capacity > 2000) C LEFT JOIN 
tMaker M ON C.maker = M.maker WHERE M.factory = '¿ï»ê';

SELECT * FROM tMember, tOrder;

SELECT * FROM tMember M, tOrder O WHERE M.member = O.member;
SELECT * FROM tMember M INNER JOIN tOrder O ON M.member = O.member;

SELECT M.addr, M.member, O.item, O.num, O.orderDate FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member;

SELECT M.addr, M.member, O.item, O.num, O.orderDate FROM tMember M 
LEFT OUTER JOIN tOrder O ON M.member = O.member;

SELECT M.addr, O.member, O.item, O.num, O.orderDate FROM tMember M 
RIGHT OUTER JOIN tOrder O ON M.member = O.member;

SELECT M.addr, M.member, O.member, O.item, O.num, O.orderDate FROM tMember M 
FULL OUTER JOIN tOrder O ON M.member = O.member;

SELECT item, price FROM tItem WHERE item = 
(SELECT item FROM tOrder WHERE member='ÃáÇâ');

SELECT * FROM tItem I INNER JOIN tOrder O ON O.item = I.item;

SELECT * FROM tItem I INNER JOIN tOrder O ON O.item = I.item WHERE O.member = 'ÃáÇâ';
SELECT * FROM tItem I INNER JOIN tOrder O ON O.item = I.item AND O.member = 'ÃáÇâ';

SELECT O.item, I.price FROM tItem I 
INNER JOIN tOrder O ON O.item = I.item WHERE O.member = 'ÃáÇâ';

SELECT O.item, I.price, O.num FROM tItem I 
INNER JOIN tOrder O ON O.item = I.item WHERE O.member = 'Çâ´Ü';

SELECT item, price, num FROM tItem WHERE item IN 
(SELECT item FROM tOrder WHERE member='Çâ´Ü');

SELECT item, price, (SELECT num FROM tOrder O WHERE O.item = I.item) 
FROM tItem I WHERE item IN (SELECT item FROM tOrder WHERE member='Çâ´Ü');

SELECT * FROM tMember;

SELECT * FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member; 

SELECT * FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member 
INNER JOIN tItem I ON I.item = O.item;

SELECT * FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member 
INNER JOIN tItem I ON I.item = O.item
INNER JOIN tCategory C ON I.category = C.category;

SELECT M.member, O.item, O.num, O.orderDate, I.price, C.delivery FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member 
INNER JOIN tItem I ON I.item = O.item
INNER JOIN tCategory C ON I.category = C.category;

SELECT M.member, O.item, O.num, O.orderDate, I.price, C.delivery 
FROM (SELECT * FROM tMember WHERE age > 19) M 
INNER JOIN tOrder O ON M.member = O.member 
INNER JOIN tItem I ON I.item = O.item
LEFT OUTER JOIN tCategory C ON I.category = C.category AND C.category != '¼ºÀÎ'
WHERE I.price * O.num > 100000 ORDER BY M.member;

SELECT M.member, O.item, O.num, I.price FROM tMember M 
INNER JOIN tOrder O ON M.member = O.MEMBER 
INNER JOIN tItem I ON I.item = O.item;

SELECT member, num * price AS total, item FROM
(
    SELECT M.member, O.item, O.num, I.price FROM tMember M 
    INNER JOIN tOrder O ON M.member = O.MEMBER 
    INNER JOIN tItem I ON I.item = O.item
) A;

SELECT * FROM
(
	SELECT M.member, M.addr, O.item, O.num, O.orderDate, I.price, C.delivery 
	FROM (SELECT * FROM tMember WHERE age > 19) M 
	INNER JOIN tOrder O ON M.member = O.member 
	INNER JOIN tItem I ON I.item = O.item
	LEFT OUTER JOIN tCategory C ON I.category = C.category AND C.category != '¼ºÀÎ'
	WHERE I.price * O.num > 100000
) A
LEFT OUTER JOIN tCity T ON TRIM(T.name) = SUBSTR(A.addr, 0, 2);

MSSQL : LEFT OUTER JOIN tCity T ON T.name = SUBSTRING(A.addr, 0, 4);
¸¶¸®¾Æ : LEFT OUTER JOIN tCity T ON T.name = SUBSTRING(addr, 1, 2);

CREATE TABLE tDirectory
(
	id INT PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	parent INT NOT NULL
);

INSERT INTO tDirectory (id, Name, parent) VALUES (1, 'Root', 0);
INSERT INTO tDirectory (id, Name, parent) VALUES (2, 'Data', 1);
INSERT INTO tDirectory (id, Name, parent) VALUES (3, 'Program', 1);
INSERT INTO tDirectory (id, name, parent) VALUES (4, 'Sound', 2);
INSERT INTO tDirectory (id, name, parent) VALUES (5, 'Picture', 2);
INSERT INTO tDirectory (id, name, parent) VALUES (6, 'Game', 3);
INSERT INTO tDirectory (id, name, parent) VALUES (7, 'StartCraft', 6);

SELECT A.name ºÎ¸ð, B.name ÀÚ½Ä FROM tDirectory A 
INNER JOIN tDirectory B ON A.id = B.parent;

SELECT A.name ºÎ¸ð, A.id, B.name, B.parent ÀÚ½Ä FROM tDirectory A CROSS JOIN tDirectory B;

SELECT * FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

SELECT * FROM tCar INNER JOIN tMaker ON tCar.maker = tMaker.maker;

SELECT * FROM tCar LEFT OUTER JOIN tMaker ON tCar.maker = tMaker.maker;

SELECT * FROM tCar, tMaker WHERE tCar.maker = tMaker.maker(+);
SELECT * FROM tCar, tMaker WHERE tCar.maker(+) = tMaker.maker;

SELECT * FROM tCar, tMaker WHERE tCar.maker *= tMaker.maker;

-------------------------------------------------
-- 11Àå

SELECT AVG(score) FROM tStaff WHERE depart = '¿µ¾÷ºÎ';

SELECT ROUND(AVG(score), 2) FROM tStaff WHERE depart = '¿µ¾÷ºÎ';

SELECT name, REPLACE(depart, 'ºÎ', 'ÆÀ') FROM tStaff;

SELECT name, grade, salary FROM tStaff WHERE depart='Àλç°ú';

SELECT TRIM(name) || ' ' || grade, salary FROM tStaff WHERE depart='Àλç°ú';

MSSQL : SELECT TRIM(name) + ' ' + grade, salary FROM tStaff WHERE depart='Àλç°ú';
¸¶¸®¾Æ : SELECT CONCAT(name, ' ', grade), salary FROM tStaff WHERE depart='Àλç°ú';

SELECT ROUND(1234.5678, 0) FROM dual;	-- 1235
SELECT ROUND(1234.5678, 1) FROM dual;	-- 1234.6
SELECT ROUND(1234.5678, 2) FROM dual; 	-- 1234.57

SELECT name, area, ROUND(area, -2) FROM tCity;

SELECT LENGTH('korea´ëÇѹα¹') FROM dual;		-- 9
SELECT LENGTHB('korea´ëÇѹα¹') FROM dual;		-- 17

SELECT * FROM tItem WHERE LENGTH(item) = 2;

SELECT CONCAT(region, name) FROM tCity;

¿À¶óŬ : SELECT region || name FROM tCity;
MSSQL : SELECT region + name FROM tCity;

SELECT region || 'µµÀÇ ' || name FROM tCity;

SELECT CONCAT(CONCAT(region, 'µµÀÇ '), name) FROM tCity;
MSSQL, ¸¶¸®¾Æ : SELECT CONCAT(region, 'µµÀÇ ' , name) FROM tCity;

SELECT INSTR('¿ì¸®³ª¶ó ´ëÇѹα¹', '³ª¶ó') FROM dual;      -- 3
SELECT INSTR('¿ì¸®³ª¶ó ´ëÇѹα¹', '¹ÎÁ·') FROM dual;      -- 0

SELECT INSTR('±¹¹Î¿¡ ÀÇÇÑ ±¹¹ÎÀ» À§ÇÑ ±¹¹ÎÀÇ ±¹¹Î´ç', '±¹¹Î', 3) FROM dual;     -- 8
SELECT INSTR('±¹¹Î¿¡ ÀÇÇÑ ±¹¹ÎÀ» À§ÇÑ ±¹¹ÎÀÇ ±¹¹Î´ç', '±¹¹Î', 1, 3) FROM dual;  -- 15
SELECT INSTR('±¹¹Î¿¡ ÀÇÇÑ ±¹¹ÎÀ» À§ÇÑ ±¹¹ÎÀÇ ±¹¹Î´ç', '±¹¹Î', -1) FROM dual;    -- 19

MSSQL : SELECT CHARINDEX('±¹¹Î', '±¹¹Î¿¡ ÀÇÇÑ ±¹¹ÎÀ» À§ÇÑ ±¹¹ÎÀÇ ±¹¹Î´ç', 3);     -- 8
¸¶¸®¾Æ : SELECT POSITION('±¹¹Î' IN '±¹¹Î¿¡ ÀÇÇÑ ±¹¹ÎÀ» À§ÇÑ ±¹¹ÎÀÇ ±¹¹Î´ç');     -- 1

SELECT SUBSTR('¾Æ¸§´Ù¿î ´ëÇѹα¹ ±Ý¼ö°­»ê', 6, 4) FROM dual;    -- ´ëÇѹα¹
SELECT SUBSTR('¾Æ¸§´Ù¿î ´ëÇѹα¹ ±Ý¼ö°­»ê', -4, 2) FROM dual;   -- ±Ý¼ö

SELECT SUBSTR(name,1,1), COUNT(*) FROM tStaff GROUP BY SUBSTR(name,1,1) 
ORDER BY COUNT(*) DESC;

SELECT SUBSTR('...À̸§:È«±æµ¿,...', INSTR('...À̸§:È«±æµ¿,...','À̸§') + 3, 3) FROM dual;

SELECT LOWER('wonderful SQL') FROM dual; -- wonderful sql
SELECT UPPER('wonderful SQL') FROM dual; -- WONDERFUL SQL
SELECT INITCAP('wonderful SQL') FROM dual; -- Wonderful Sql

SELECT * FROM tCity WHERE metro = 'y';

SELECT * FROM tCity WHERE UPPER(metro) = 'Y';
SELECT * FROM tCity WHERE LOWER(metro) = 'y';

SELECT CONCAT(name, ' »ç¿ø´Ô') FROM tStaff;

SELECT CONCAT(TRIM(name), ' »ç¿ø´Ô') FROM tStaff;

SELECT LPAD('SQL', 10, '>') FROM dual;  -- >>>>>>>SQL
SELECT RPAD('SQL', 10, '<') FROM dual;  -- SQL<<<<<<<

SELECT LPAD(RPAD('SQL', 10, '<'), 17, '>') FROM dual;	-- >>>>>>>SQL<<<<<<<

SELECT name, LPAD(area, 4, '0') FROM tCity;

SELECT REPLACE('µ¶µµ´Â ÀϺ»¶¥ÀÌ´Ù', 'ÀϺ»', 'Çѱ¹') FROM dual;

SELECT REPLACE('±¸±Û¿¡¼­ ±¸±Û¸µÇÑ´Ù.', '±¸±Û', '³×À̹ö') FROM dual; 	
-- ³×À̹ö¿¡¼­ ³×À̹ö¸µÇÑ´Ù.

SELECT REPLACE('Get_Total_Score', '_', '') FROM dual;   -- GetTotalScore

SELECT REPLACE('µ¶µµ´Â ÀϺ»¶¥ÀÌ´Ù. ´ë¸¶µµ´Â ÀϺ»¶¥ÀÌ´Ù.', 'ÀϺ»', 'Çѱ¹') FROM dual;

SELECT STUFF('µ¶µµ´Â ÀϺ»¶¥ÀÌ´Ù. ´ë¸¶µµ´Â ÀϺ»¶¥ÀÌ´Ù.', 5, 2, 'Çѱ¹');

SELECT SUBSTR('µ¶µµ´Â ÀϺ»¶¥ÀÌ´Ù. ´ë¸¶µµ´Â ÀϺ»¶¥ÀÌ´Ù.', 1, 
INSTR('µ¶µµ´Â ÀϺ»¶¥ÀÌ´Ù. ´ë¸¶µµ´Â ÀϺ»¶¥ÀÌ´Ù.', 'ÀϺ»') - 1) || 'Çѱ¹' ||
SUBSTR('µ¶µµ´Â ÀϺ»¶¥ÀÌ´Ù. ´ë¸¶µµ´Â ÀϺ»¶¥ÀÌ´Ù.', 
INSTR('µ¶µµ´Â ÀϺ»¶¥ÀÌ´Ù. ´ë¸¶µµ´Â ÀϺ»¶¥ÀÌ´Ù.', 'ÀϺ»') + 2) FROM dual;

SELECT SUBSTR(str, 1 , INSTR(str, 'ÀϺ»') - 1) || 'Çѱ¹' || SUBSTR(str, INSTR(str, 'ÀϺ»') + 2) 
FROM tTable;

pos = INSTR(str, 'ÀϺ»');
SELECT SUBSTR(str, 1 , pos - 1) || 'Çѱ¹' || SUBSTR(str, pos + 2) FROM tTable;

INSERT INTO tDate VALUES (TO_DATE('2021/12/25 12:34:56', 'yyyy/mm/dd hh24:mi:ss'));

SELECT AVG(popu) FROM tCity;

¿À¶óŬ : SELECT CAST(AVG(popu) AS INT) FROM tCity;		-- 193
MSSQL : SELECT AVG(CAST(popu AS DECIMAL)) FROM tCity;	-- 193.25

SELECT '12' + 34 FROM dual;	-- 46
SELECT '12' || 34 FROM dual;	-- 1234

SELECT '12' + 34;					-- 46
SELECT '12' + CAST(34 AS VARCHAR(10));	-- 1234

SELECT 'ÀÀ´äÇ϶ó ' + 1989;				-- ¿¡·¯
SELECT 'ÀÀ´äÇ϶ó ' + CAST(1989 AS VARCHAR(10));	-- ÀÀ´äÇ϶ó 1989

SELECT 'ÀÀ´äÇ϶ó ' || 1989 FROM dual;

SELECT TO_CHAR(12345) FROM dual;		-- 12345
SELECT TO_CHAR(12345, '999,999') FROM dual;	--  12,345
SELECT TO_CHAR(12345, 'FM999,999') FROM dual; 	-- 12,345
SELECT TO_CHAR(12345, '000,999') FROM dual; 	-- 012,345

SELECT TO_NUMBER('12345') FROM dual;		-- 12345
SELECT TO_NUMBER('12,345') FROM dual;		-- ¿¡·¯
SELECT TO_NUMBER('12,345', '999,999') FROM dual;	-- 12345

SELECT 'ÀÀ´äÇ϶ó ' + CONVERT(VARCHAR(10), 1989);		-- ÀÀ´äÇ϶ó 1989

¿À¶óŬ : SELECT name, NVL(score, 10) FROM tStaff;
MSSQL : SELECT name, ISNULL(score, 10) FROM tStaff;
¸¶¸®¾Æ : SELECT name, IFNULL(score, 10) FROM tStaff;

SELECT name, NULLIF(score, 0) FROM tStaff;

SELECT name, NVL(NULLIF(score, 0), 60) FROM tStaff;

SELECT name, NVL2(score, salary * score / 100, 50) FROM tStaff;

SELECT name, DECODE(gender, '³²', '¸ÚÀïÀÌ', '¿©', '¿¹»ÛÀÌ', '¸ó³­ÀÌ') FROM tStaff;

SELECT SYSDATE FROM dual;  -- 20/10/17 12:21:42

INSERT INTO tStaff VALUES ('±èÇѽ½', '±âȹ½Ç', '¿©', SYSDATE, '¼ö½À', 480, 50);

SELECT SYSDATE + 12 FROM dual;

SELECT SYSDATE + 5/24 FROM dual;
SELECT SYSDATE - 30/1440 FROM dual;
SELECT SYSDATE - 80/86400 FROM dual;

SELECT name, sysdate - joindate FROM tStaff;

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd hh24:mi:ss') FROM dual;	  -- 2020/10/17 12:18:51
SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd AM hh:mi:ss') FROM dual; -- 2020/10/17 ¿ÀÈÄ 12:20:35

SELECT TO_CHAR(SYSDATE, 'yyyy"³â" mm"¿ù" dd"ÀÏ" hh24"½Ã" mi"ºÐ" ss"ÃÊ"') FROM dual;
-- 2020³â 10¿ù 17ÀÏ 12½Ã 24ºÐ 44ÃÊ

SELECT name, TO_CHAR(joindate, 'yyyy') FROM tStaff;

SELECT TO_CHAR(joindate, 'yyyy') AS ³â, TO_CHAR(joindate, 'mm') AS ¿ù, 
TO_CHAR(joindate, 'dd') AS ÀÏ FROM tStaff;

SELECT TO_DATE('1919/3/1', 'yyyy/mm/dd') FROM dual;

SELECT TO_DATE('1919-3-1', 'yyyy-mm-dd') FROM dual;
SELECT TO_DATE('19190301', 'yyyymmdd') FROM dual;

SELECT TO_DATE('1919/3/1') FROM dual;
SELECT TO_DATE('1919-3-1') FROM dual;
SELECT TO_DATE('19190301') FROM dual;

SELECT sysdate - '1919/3/1' FROM dual;	-- ¿¡·¯

SELECT sysdate - TO_DATE('1919/3/1') FROM dual;

SELECT TO_CHAR(TO_DATE('2023/3/8', 'yyyy/mm/dd') + 99, 'yyyy"³â" mm"¿ù" dd"ÀÏ"') FROM dual;

SELECT GETDATE();

SELECT name, YEAR(joindate) AS ³â, MONTH(joindate) AS ¿ù, DAY(joindate) AS ÀÏ FROM tStaff;

SELECT YEAR(joindate), count(*) FROM tStaff GROUP BY YEAR(joindate) 
ORDER BY YEAR(joindate);

SELECT DATEPART(dw, GETDATE());
SELECT DATENAME(dw, GETDATE());

SELECT name, DATEDIFF(day,joindate,GETDATE()) FROM tStaff;

SELECT DATEDIFF(day, '1919/3/1', GETDATE());

SELECT CONVERT(VARCHAR(20), GETDATE(),0);		-- 06 29 2021 10:43PM
SELECT CONVERT(VARCHAR(20), GETDATE(),11);		-- 21/06/29
SELECT CONVERT(VARCHAR(20), GETDATE(),111); 		-- 2021/06/29
SELECT CONVERT(VARCHAR(20), GETDATE(),101); 		-- 06/29/2021
SELECT CONVERT(VARCHAR(20), GETDATE(),103); 		-- 29/06/2021

SELECT NOW();

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s');

SELECT DATE_FORMAT(joindate, '%Y'), count(*) FROM tStaff 
GROUP BY DATE_FORMAT(joindate, '%Y') ORDER BY DATE_FORMAT(joindate, '%Y');

SELECT DATE_ADD(NOW(), INTERVAL 12 DAY);		-- 12ÀÏ ÈÄ
SELECT DATE_ADD(NOW(), INTERVAL 5 HOUR);		-- 5½Ã°£ ÈÄ
SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE);		-- 30ºÐ Àü

SELECT name, DATEDIFF(NOW(), joindate) FROM tStaff;

-------------------------------------------------
-- 12Àå

SELECT member, age, addr FROM tMember;

CREATE VIEW vMember AS
SELECT member, age, addr FROM tMember;

SELECT * FROM vMember;

SELECT member, age FROM vMember;
SELECT * FROM vMember WHERE addr LIKE '%¼­¿ï%';
SELECT * FROM vMember ORDER BY member;

SELECT name, email FROM vMember;

DROP VIEW vMember;

CREATE VIEW vMember AS SELECT member, age, addr, email FROM tMember;

¿À¶óŬ, ¸¶¸®¾Æ : CREATE OR REPLACE VIEW vMember AS SELECT member, age, addr, email FROM tMember;
MSSQL : ALTER VIEW vMember AS SELECT member, age, addr, email FROM tMember;

CREATE VIEW vMemberMirror AS SELECT * FROM tMember;

CREATE VIEW vStaffVirt AS SELECT depart, salary, name FROM tStaff;

CREATE VIEW vStaffHorz AS SELECT * FROM tStaff WHERE depart = 'Ãѹ«ºÎ';

CREATE VIEW vStaffPart AS SELECT name, salary FROM tStaff WHERE depart = 'Ãѹ«ºÎ';

CREATE VIEW vStaffAlias(n, d, s) AS SELECT name, depart, salary FROM tStaff;

CREATE OR REPLACE VIEW vStaffAlias AS SELECT name n, depart d , salary s FROM tStaff;

SELECT * FROM vStaffAlias ORDER BY s;           -- ¸ÂÀ½
SELECT * FROM vStaffAlias ORDER BY salary;      -- ¿¡·¯

CREATE VIEW vStaffBonus AS SELECT name, salary * score / 100 AS bonus FROM tStaff;

SELECT * FROM vStaffBonus WHERE bonus > 300;

CREATE VIEW vShopping AS
SELECT M.member, M.addr, O.item, O.num, O.orderDate FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member;

CREATE VIEW vUnion AS
SELECT name, salary FROM tStaff WHERE depart = 'Àλç°ú'
UNION
SELECT name, salary FROM tEmployee;

CREATE VIEW vvStaffHorz AS
SELECT name, joindate, salary FROM vStaffHorz;

CREATE VIEW vOriginal AS SELECT a, b, c FROM tOriginal;

CREATE VIEW vOriginal(a, b, c) AS SELECT x, y, c FROM tOriginal;

UPDATE vMember SET addr = '¼­¿ï ½Å»çµ¿' WHERE member = 'ÃáÇâ';

UPDATE vStaffBonus SET bonus = 500 WHERE name = 'À¯°ü¼ø';

INSERT INTO vStaffHorz VALUES ('±èÇѽ½', 'Ãѹ«ºÎ', '¿©', '2022/08/14', '»ç¿ø', 520, 55);

INSERT INTO vStaffHorz VALUES ('±èÇÑ°á', '¿µ¾÷ºÎ', '³²', '2023/05/13', '»ç¿ø', 490, 35);

CREATE VIEW vStaffHorzCheck AS 
SELECT * FROM tStaff WHERE depart = 'Ãѹ«ºÎ' WITH CHECK OPTION;

DELETE FROM tStaff WHERE name IN ('±èÇѽ½','±èÇÑ°á');
INSERT INTO vStaffHorzCheck VALUES ('±èÇÑ°á', '¿µ¾÷ºÎ', '³²', '2023/05/13', '»ç¿ø', 490, 35);

UPDATE vStaffHorzCheck SET depart = '±âȹÆÀ' WHERE name = '±èÀ¯½Å';

INSERT INTO vStaffVirt (name, depart, salary) VALUES ('ÀÌ¿Ï¿ë', '¿µ¾÷ºÎ', 99);

CREATE VIEW vNotExist AS SELECT * FROM tNotExist;		-- ¿¡·¯
CREATE FORCE VIEW vNotExist AS SELECT * FROM tNotExist;	-- °¡´É

CREATE VIEW vStaffReadOnly AS SELECT * FROM tStaff 
WHERE depart = 'Àλç°ú' WITH READ ONLY;

CREATE VIEW vMemberEnc WITH ENCRYPTION AS
SELECT member, age, addr FROM tMember;

CREATE GLOBAL TEMPORARY TABLE tGtt (
	name VARCHAR(20) PRIMARY KEY,
	score INT
);

INSERT INTO tGtt VALUES ('±èÇѽ½', 95);
INSERT INTO tGtt VALUES ('±èÇÑ°á', 80);
SELECT * FROM tGtt;

DROP TABLE tGtt;
CREATE GLOBAL TEMPORARY TABLE tGtt (
	name VARCHAR(20) PRIMARY KEY,
	score INT
) ON COMMIT PRESERVE ROWS;

INSERT INTO tGtt VALUES ('±è±Ô¹Î', 70);
SELECT * FROM tGtt;

CREATE TABLE #tTemp (
	name VARCHAR(20) PRIMARY KEY,
	score INT
);

INSERT INTO #tTemp VALUES ('±èÇѽ½', 95);
INSERT INTO #tTemp VALUES ('±èÇÑ°á', 80);
INSERT INTO #tTemp VALUES ('±è±Ô¹Î', 70);
SELECT * FROM #tTemp;

CREATE TEMPORARY TABLE tTemp (
	name VARCHAR(20) PRIMARY KEY,
	score INT
);

INSERT INTO tTemp VALUES ('±èÇѽ½', 95);
INSERT INTO tTemp VALUES ('±èÇÑ°á', 80);
INSERT INTO tTemp VALUES ('±è±Ô¹Î', 70);
SELECT * FROM tTemp;

CREATE GLOBAL TEMPORARY TABLE tTaskForce AS SELECT * FROM tStaff;

INSERT INTO tTaskForce SELECT * FROM (SELECT * FROM tStaff 
WHERE joindate <= '20160101' ORDER BY salary DESC) WHERE rownum <= 10;

MSSQL : SELECT TOP 10 * INTO #tTaskForce FROM tStaff 
WHERE joindate <= '20160101' ORDER BY salary DESC;
¸¶¸®¾Æ : CREATE TEMPORARY TABLE tTaskForce AS SELECT * FROM tStaff 
WHERE joindate <= '20160101' ORDER BY salary DESC LIMIT 10;

DELETE FROM tTaskForce WHERE score < (SELECT AVG(score) FROM tTaskForce 
WHERE gender = '³²') AND gender = '³²';

DELETE FROM tTaskForce WHERE salary < 
(SELECT AVG(salary) FROM tStaff) AND gender = '¿©';

DELETE FROM tTaskForce WHERE salary > 300 AND grade = '´ë¸®';
INSERT INTO tTaskForce SELECT * FROM tStaff WHERE salary > 380 AND grade = '°úÀå';

SELECT name, salary, score FROM tStaff WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²';

SELECT * FROM 
(
	SELECT name, salary, score FROM tStaff WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²'
) A
WHERE salary >= (
	SELECT avg(salary) FROM 
	(
		SELECT name, salary, score FROM tStaff WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²'
	) B
);

CREATE GLOBAL TEMPORARY TABLE tBusiMan AS SELECT name, salary, score FROM tStaff;
INSERT INTO tBusiMan SELECT name, salary, score FROM tStaff 
WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²';

MSSQL: SELECT name, salary, score INTO #tBusiMan FROM tStaff 
WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²';
¸¶¸®¾Æ : CREATE TEMPORARY TABLE tBusiMan AS 
SELECT name, salary, score FROM tStaff WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²';

SELECT * FROM tBusiMan WHERE salary >= (SELECT avg(salary) FROM tBusiMan);

CREATE VIEW vBusiMan AS SELECT name, salary, score FROM tStaff 
WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²';
SELECT * FROM vBusiMan WHERE salary >= (SELECT avg(salary) FROM vBusiMan);

WITH tBusiMan AS 
(SELECT name, salary, score FROM tStaff WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²')
SELECT * FROM tBusiMan WHERE salary >= (SELECT avg(salary) FROM tBusiMan);

WITH tBusiMan(À̸§, ¿ù±Þ, ¼ºÃëµµ) AS 
(SELECT name, salary, score FROM tStaff WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²')
SELECT * FROM tBusiMan WHERE ¿ù±Þ >= (SELECT avg(¿ù±Þ) FROM tBusiMan);

WITH tBusiMan AS 
(SELECT name, salary, score FROM tStaff WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²'),
tBusiGirl AS 
(SELECT name, salary, score FROM tStaff WHERE depart = '¿µ¾÷ºÎ' AND gender = '¿©')
SELECT * FROM tBusiGirl WHERE salary >= (SELECT avg(salary) FROM tBusiMan);

WITH tBusiMan AS 
(SELECT name, salary, score FROM tStaff WHERE depart = '¿µ¾÷ºÎ' AND gender = '³²'),
tBusiManGod AS 
(SELECT name, salary, score FROM tBusiMan WHERE score > 70)
SELECT * FROM tBusiManGod;

WITH Shopping AS
(SELECT M.member, M.addr, O.item, O.num, O.orderDate FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member)
SELECT * FROM Shopping WHERE num >= (SELECT AVG(num) FROM Shopping);

CREATE VIEW vTemp AS
(SELECT M.member, M.addr, O.item, O.num, O.orderDate FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member);

SELECT * FROM vTemp WHERE num >= (SELECT AVG(num) FROM vTemp);

WITH tFact(num, sum) AS (
	SELECT 1 AS num, 1 AS sum FROM dual
	UNION ALL
	SELECT num + 1, sum * (num + 1) FROM tFact T WHERE T.num < 10
)
SELECT * FROM tFact;

WITH tTree(id, name, parent, depth) AS
(
	SELECT id, name, parent, 0 FROM tDirectory WHERE parent = 0
	UNION ALL
	SELECT D.id, D.name, D.parent, T.depth + 1 FROM tDirectory D 
	INNER JOIN tTree T ON D.parent = T.id
)
SELECT * FROM tTree;

WITH tTree(id, name, parent, depth, fullpath) AS
(
	SELECT id, name, parent, 0, CAST(name AS VARCHAR(256)) 
	FROM tDirectory WHERE parent = 0
	UNION ALL
	SELECT D.id, D.name, D.parent, T.depth + 1, 
	CAST(CONCAT(CONCAT(T.fullpath, '/'), D.name) AS VARCHAR(256)) 
	FROM tDirectory D 
	INNER JOIN tTree T ON D.parent = T.id
)
SELECT * FROM tTree;

WITH tTree(id, name, parent, depth, fullpath) AS
(
	SELECT id, name, parent, 0, CAST(name AS VARCHAR(256)) 
	FROM tDirectory WHERE parent = 0
	UNION ALL
	SELECT D.id, D.name, D.parent, T.depth + 1, 
	LPAD('L ', (T.depth + 1) * 4) || D.name 
	FROM tDirectory D 
	INNER JOIN tTree T ON D.parent = T.id
)
SELECT fullpath FROM tTree;

CAST(SPACE((T.depth + 1) * 4) + 'L ' + D.name AS VARCHAR(256))

-------------------------------------------------
-- 13Àå

CREATE TABLE tMonthSale
(
	year INT,
	month INT,
	sales	INT
);

INSERT INTO tMonthSale VALUES(2021, 9, 3650);
INSERT INTO tMonthSale VALUES(2021, 10, 4120);
INSERT INTO tMonthSale VALUES(2021, 11, 5000);
INSERT INTO tMonthSale VALUES(2021, 12, 4420);
INSERT INTO tMonthSale VALUES(2022, 1, 3800);
INSERT INTO tMonthSale VALUES(2022, 2, 4200);
INSERT INTO tMonthSale VALUES(2022, 3, 4150);

SELECT gender, SUM(salary) FROM tStaff GROUP BY gender;
SELECT depart, SUM(salary) FROM tStaff GROUP BY depart;
SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY depart, gender;

SELECT depart, SUM(salary) FROM tStaff GROUP BY ROLLUP(depart);

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY ROLLUP(depart, gender);

SELECT gender, depart, SUM(salary) FROM tStaff GROUP BY ROLLUP(gender, depart);

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY depart, ROLLUP(gender);
SELECT gender, depart, SUM(salary) FROM tStaff GROUP BY gender, ROLLUP(depart);

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY CUBE(depart, gender);

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY ROLLUP(depart, gender)
UNION
SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY ROLLUP(gender, depart);

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY CUBE(gender, depart);

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY GROUPING SETS(depart, gender);

SELECT GROUPING(depart), depart, SUM(salary) FROM tStaff GROUP BY ROLLUP(depart);

SELECT CASE GROUPING(depart) WHEN 1 THEN 'ÀüüºÎ¼­' ELSE depart END AS depart, 
SUM(salary) FROM tStaff GROUP BY ROLLUP(depart);

SELECT CASE GROUPING(depart) WHEN 1 THEN 'ÀüüºÎ¼­' ELSE depart END AS depart,
	CASE GROUPING(gender) WHEN 1 THEN 'Àüü¼ºº°' ELSE gender END AS gender, 
	SUM(salary) FROM tStaff GROUP BY CUBE(depart, gender)
	ORDER BY /*GROUPING(depart), GROUPING(gender),*/ depart, gender;

SELECT name, depart, salary, SUM(salary) FROM tStaff;

SELECT depart, SUM(salary) FROM tStaff GROUP BY depart;

SELECT name, depart, salary, (SELECT SUM(salary) FROM tStaff) AS ¿ù±ÞÃÑÇÕ FROM tStaff;

SELECT name, depart, salary, SUM(salary) OVER() AS ¿ù±ÞÃÑÇÕ FROM tStaff;

SELECT name, depart, salary, ROUND(salary * 100.0 / SUM(salary) OVER(), 2) 
AS ¿ù±ÞºñÀ² FROM tStaff;

SELECT name, depart, salary, (SELECT SUM(salary) FROM tStaff GROUP BY depart) 
AS ºÎ¼­¿ù±ÞÃÑÇÕ FROM tStaff;

SELECT name, depart, salary, (SELECT SUM(salary) FROM tStaff WHERE depart = A.depart) 
AS ºÎ¼­¿ù±ÞÃÑÇÕ FROM tStaff A ORDER BY depart;

SELECT name, depart, salary, SUM(salary) OVER(PARTITION BY depart) 
AS ºÎ¼­¿ù±ÞÃÑÇÕ FROM tStaff;

SELECT name, depart, salary, SUM(salary) OVER(ORDER BY name) 
AS ´©Àû¿ù±Þ FROM tStaff;

SELECT name, depart, salary, SUM(salary) OVER(PARTITION BY depart ORDER BY name) 
AS ºÎ¼­´©Àû¿ù±Þ FROM tStaff;

SELECT name, depart, salary, (SELECT SUM(salary) FROM tStaff 
WHERE name <= A.name) AS ´©Àû¿ù±Þ FROM tStaff A ORDER BY name;
SELECT name, depart, salary, (SELECT SUM(salary) FROM tStaff 
WHERE name <= A.name AND depart = A.depart) AS ºÎ¼­´©Àû¿ù±Þ 
FROM tStaff A ORDER BY depart, name;

SELECT name, depart, salary, SUM(salary) OVER(ORDER BY name 
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS ´©Àû¿ù±Þ FROM tStaff;

SELECT name, depart, salary, SUM(salary) OVER(ORDER BY name 
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ´©Àû¿ù±Þ FROM tStaff;

SELECT name, depart, joindate, salary, 
SUM(salary) OVER(PARTITION BY depart ORDER BY joindate 
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS ´©Àû¿ù±Þ FROM tStaff;

SELECT name, depart, salary, SUM(salary) OVER(ORDER BY salary 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ´©Àû¿ù±Þ FROM tStaff;

SELECT name, depart, salary, SUM(salary) OVER(ORDER BY salary 
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ´©Àû¿ù±Þ FROM tStaff;

SELECT RANK() OVER (ORDER BY salary DESC), tStaff.* FROM tStaff;
MSSQL, ¸¶¸®¾Æ : SELECT RANK() OVER (ORDER BY salary DESC), * FROM tStaff;

SELECT RANK() OVER (ORDER BY salary DESC), tStaff.* FROM tStaff ORDER BY score;

SELECT RANK() OVER (ORDER BY salary DESC) AS ¼øÀ§, tStaff.* FROM tStaff 
WHERE depart = '¿µ¾÷ºÎ';

SELECT RANK() OVER (PARTITION BY depart ORDER BY salary DESC) 
AS ¼øÀ§, tStaff.* FROM tStaff;

SELECT DENSE_RANK() OVER (ORDER BY salary DESC), tStaff.* FROM tStaff;

SELECT ROW_NUMBER() OVER (ORDER BY name), tStaff.* FROM tStaff;

SELECT ROW_NUMBER() OVER (ORDER BY rownum) AS ¼ø¼­, tStaff.* FROM tStaff;

SELECT ROW_NUMBER() OVER (ORDER BY name) AS ¼ø¼­, tStaff.* FROM tStaff
ORDER BY ¼ø¼­ OFFSET 2 * 5 ROWS FETCH NEXT 5 ROWS ONLY;

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY name) AS ¼ø¼­, tStaff.* FROM tStaff) S
WHERE S.¼ø¼­ > 2 * 5 AND rownum <= 5;

SELECT TOP(5) * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY name) AS ¼ø¼­, tStaff.* FROM tStaff) S
WHERE S.¼ø¼­ > 2 * 5;

SELECT ROW_NUMBER() OVER (ORDER BY name) AS ¼ø¼­, tStaff.* FROM tStaff LIMIT 11, 5;

SELECT NTILE(4) OVER (ORDER BY salary DESC) AS ±¸°£, name, salary FROM tStaff;
SELECT NTILE(4) OVER (PARTITION BY gender ORDER BY salary DESC) 
AS ±¸°£, name, gender, salary FROM tStaff;

SELECT name, score FROM 
(SELECT NTILE(5) OVER (ORDER BY score DESC) AS ±¸°£, tStaff.* FROM tStaff) S 
WHERE S.±¸°£ = 3;

SELECT year, month, sales,
	LAG(sales) OVER (ORDER BY year, month) AS priorMonth,
	LEAD(sales) OVER (ORDER BY year, month) AS nextMonth
FROM tMonthSale;

SELECT year, month, sales,
	sales - LAG(sales) OVER (ORDER BY year, month) AS incsales
FROM tMonthSale;

SELECT year, month, sales,
	LAG(sales) OVER (PARTITION BY year ORDER by year, month) AS priorMonth,
	LEAD(sales) OVER (PARTITION BY year ORDER by year, month) AS nextMonth
FROM tMonthSale;

SELECT year, month, sales,
	ROUND(CUME_DIST() OVER (ORDER BY year, month) * 100, 2) AS cume,
	ROUND(PERCENT_RANK() OVER (ORDER BY year, month) * 100, 2) AS rank
FROM tMonthSale;

SELECT name, salary,
	ROUND(CUME_DIST() OVER (ORDER BY salary) * 100, 2) AS cume,
	ROUND(PERCENT_RANK() OVER (ORDER BY salary) * 100, 2) AS rank
FROM tStaff;

SELECT depart, name, salary,
	ROUND(CUME_DIST() OVER (PARTITION BY depart ORDER BY salary) * 100, 2) AS cume,
	ROUND(PERCENT_RANK() OVER (PARTITION BY depart ORDER BY salary) * 100, 2) AS rank
FROM tStaff;

SELECT name, salary,
	FIRST_VALUE(salary) OVER (ORDER BY salary) AS first,
	LAST_VALUE(salary) OVER (ORDER BY salary) AS midlast,
	LAST_VALUE(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last
FROM tStaff;

SELECT name, salary,
	salary - FIRST_VALUE(salary) OVER (ORDER BY salary) AS ÃÖÀú¿ù±Þ±âÁØ,
	LAST_VALUE(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - salary AS ÃÖ°í¿ù±Þ±âÁØ
FROM tStaff ORDER BY name;

SELECT depart, name, salary,
	salary - FIRST_VALUE(salary) OVER (PARTITION BY depart ORDER BY salary) AS ÃÖÀú¿ù±Þ±âÁØ,
	LAST_VALUE(salary) OVER (PARTITION BY depart ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - salary AS ÃÖ°í¿ù±Þ±âÁØ
FROM tStaff ORDER BY depart, salary;

SELECT year, month, sales,	
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sales) 
OVER (PARTITION BY year) AS cont,	
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY sales) 
OVER (PARTITION BY year) AS disc
FROM tMonthSale;

CREATE TABLE tSeason
(
	item VARCHAR(10),
	season VARCHAR(10),
	sale INT
);

INSERT INTO tSeason VALUES ('³Ã¸é', 'º½', 20);
INSERT INTO tSeason VALUES ('³Ã¸é', '¿©¸§', 50);
INSERT INTO tSeason VALUES ('³Ã¸é', '°¡À»', 30);
INSERT INTO tSeason VALUES ('³Ã¸é', '°Ü¿ï', 10);
INSERT INTO tSeason VALUES ('«»Í', 'º½', 30);
INSERT INTO tSeason VALUES ('«»Í', '¿©¸§', 10);
INSERT INTO tSeason VALUES ('«»Í', '°¡À»', 20);
INSERT INTO tSeason VALUES ('«»Í', '°Ü¿ï', 40);

SELECT * FROM tSeason
PIVOT (MAX(sale) FOR season IN ('º½', '¿©¸§', '°¡À»', '°Ü¿ï'));

SELECT * FROM tSeason
PIVOT (MAX(sale) FOR season IN ('¿©¸§', 'º½', '°¡À»'));

SELECT * FROM tSeason
PIVOT (MAX(sale) FOR item IN ('³Ã¸é', '«»Í'))

SELECT * FROM tSeason
PIVOT (sale FOR season IN ('º½', '¿©¸§', '°¡À»', '°Ü¿ï')) pvt;

SELECT * FROM tSeason
PIVOT (SUM(sale) FOR season IN ('º½', '¿©¸§', '°¡À»', '°Ü¿ï')) pvt;

INSERT INTO tSeason2 VALUES ('³Ã¸é', 'º½', 20);
INSERT INTO tSeason2 VALUES ('³Ã¸é', '¿©¸§', 50);
INSERT INTO tSeason2 VALUES ('³Ã¸é', '°¡À»', 30);
INSERT INTO tSeason2 VALUES ('«»Í', 'º½', 30);
INSERT INTO tSeason2 VALUES ('«»Í', '°¡À»', 20);
INSERT INTO tSeason2 VALUES ('«»Í', '°Ü¿ï', 40);
INSERT INTO tSeason2 VALUES ('«»Í', '°Ü¿ï', 30);

SELECT * FROM tSeason2 PIVOT (MAX(sale) FOR season IN ('º½', '¿©¸§', '°¡À»', '°Ü¿ï')) pvt;
SELECT * FROM tSeason2 PIVOT (SUM(sale) FOR season IN ('º½', '¿©¸§', '°¡À»', '°Ü¿ï')) pvt;

CREATE TABLE tTraffic
(
	line VARCHAR(10),
	hour INT,
	car VARCHAR(20),
	traffic INT
);

INSERT INTO tTraffic VALUES ('°æºÎ', 1, '½Â¿ëÂ÷', 40);
INSERT INTO tTraffic VALUES ('°æºÎ', 2, '½Â¿ëÂ÷', 41);
INSERT INTO tTraffic VALUES ('°æºÎ', 3, '½Â¿ëÂ÷', 42);
INSERT INTO tTraffic VALUES ('°æºÎ', 1, 'Æ®·°', 30);
INSERT INTO tTraffic VALUES ('°æºÎ', 3, 'Æ®·°', 32);
INSERT INTO tTraffic VALUES ('È£³²', 1, '½Â¿ëÂ÷', 20);
INSERT INTO tTraffic VALUES ('È£³²', 2, '½Â¿ëÂ÷', 10);
INSERT INTO tTraffic VALUES ('È£³²', 2, '½Â¿ëÂ÷', 11);
INSERT INTO tTraffic VALUES ('È£³²', 3, '½Â¿ëÂ÷', 22);
INSERT INTO tTraffic VALUES ('È£³²', 1, 'Æ®·°', 10);
INSERT INTO tTraffic VALUES ('È£³²', 2, 'Æ®·°', 11);
INSERT INTO tTraffic VALUES ('È£³²', 3, 'Æ®·°', 12);

SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR line IN ('°æºÎ', 'È£³²')) pvt;
SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR hour IN ('1', '2', '3')) pvt;

SELECT line, car, traffic FROM tTraffic 
PIVOT (SUM(traffic) FOR car IN ('½Â¿ëÂ÷', 'Æ®·°')) pvt;

SELECT * FROM
(
	SELECT line, car, traffic FROM tTraffic
) prepvt
PIVOT (SUM(traffic) FOR car IN ('½Â¿ëÂ÷', 'Æ®·°')) pvt;

SELECT line, SUM(½Â¿ëÂ÷), SUM(Æ®·°) FROM tTraffic
PIVOT (SUM(traffic) FOR car IN ('½Â¿ëÂ÷' AS ½Â¿ëÂ÷, 'Æ®·°' AS Æ®·°)) pvt
GROUP BY line;

SELECT * FROM
(
	SELECT hour, car, traffic FROM tTraffic
) prepvt
PIVOT (SUM(traffic) FOR car IN ('½Â¿ëÂ÷', 'Æ®·°')) pvt;

SELECT * FROM
(
	SELECT car, traffic FROM tTraffic
) prepvt
PIVOT (SUM(traffic) FOR car IN ('½Â¿ëÂ÷', 'Æ®·°')) pvt;

SELECT line, Æ®·°, ½Â¿ëÂ÷ FROM
(
	SELECT line, hour, car, traffic FROM tTraffic
) prepvt
PIVOT (SUM(traffic) FOR car IN ('½Â¿ëÂ÷' AS ½Â¿ëÂ÷, 'Æ®·°' AS Æ®·°)) pvt;

SELECT line || '¼± ' || CAST(hour AS VARCHAR(10)) || '½Ã' AS ±¸ºÐ, Æ®·°, ½Â¿ëÂ÷ FROM
(
	SELECT line, hour, car, traffic FROM tTraffic
) prepvt
PIVOT (SUM(traffic) FOR car IN ('½Â¿ëÂ÷' AS ½Â¿ëÂ÷, 'Æ®·°' AS Æ®·°)) pvt
ORDER BY line;

CREATE TABLE tCityStat
(
	name CHAR(10),
	attr CHAR(10),
	value INT
);

INSERT INTO tCityStat VALUES ('¼­¿ï', 'area', 605);
INSERT INTO tCityStat VALUES ('¼­¿ï', 'popu', 974);
INSERT INTO tCityStat VALUES ('¼­¿ï', 'gu', 25);
INSERT INTO tCityStat VALUES ('ÀÎÁ¦', 'area', 1646);
INSERT INTO tCityStat VALUES ('ÀÎÁ¦', 'popu', 3);
INSERT INTO tCityStat VALUES ('ÀÎÁ¦', 'home', 15409);
INSERT INTO tCityStat VALUES ('ȫõ', 'area', 1819);

SELECT * FROM tCityStat
PIVOT (MAX(value) FOR attr IN ('area' AS area, 'popu' AS popu)) pvt;

SELECT name, ROUND(popu * 10000 / area, 2) AS Àα¸¹Ðµµ FROM
(
    SELECT * FROM tCityStat
    PIVOT (MAX(value) FOR attr IN ('area' AS area, 'popu' AS popu)) pvt
) A;

WHERE popu IS NOT NULL AND area IS NOT NULL

CREATE TABLE tSeasonPivot AS 
SELECT * FROM tSeason
PIVOT (SUM(sale) FOR season IN ('º½' AS º½, '¿©¸§' AS ¿©¸§, '°¡À»' AS °¡À», '°Ü¿ï' AS °Ü¿ï));

SELECT * INTO tSeasonPivot FROM tSeason
PIVOT (SUM(sale) FOR season IN (º½, ¿©¸§, °¡À», °Ü¿ï)) pvt;

SELECT * FROM tSeasonPivot
UNPIVOT (sale FOR season IN (º½, ¿©¸§, °¡À», °Ü¿ï)) unpvt;

SELECT * FROM
(
    SELECT * FROM tSeason
    PIVOT (SUM(sale) FOR season IN 
    ('º½' AS º½, '¿©¸§' AS ¿©¸§, '°¡À»' AS °¡À», '°Ü¿ï' AS °Ü¿ï)) pvt
) A
UNPIVOT (sale FOR season IN (º½, ¿©¸§, °¡À», °Ü¿ï)) unpvt;

-------------------------------------------------
-- 14Àå

ALTER TABLE tCity ADD mayor CHAR(12) NULL;

UPDATE tCity SET mayor = '¿À·úµµ' WHERE name = 'ºÎ»ê';

ALTER TABLE tCity ADD mayor CHAR(12) NOT NULL;

ALTER TABLE tCity DROP COLUMN mayor;

DROP TABLE tProject;
CREATE TABLE tProject
(
	projectID INT,
	employee CHAR(10),
	project VARCHAR(30),
	cost INT
);

INSERT INTO tProject VALUES (1, '±è»óÇü', 'È«Äá ¼öÃâ°Ç', 800);
INSERT INTO tProject VALUES (1, '±è»óÇü', 'TV ±¤°í°Ç', 3400);

TRUNCATE TABLE tProject;
ALTER TABLE tProject ADD CONSTRAINT PK_projectID PRIMARY KEY(projectID);

¿À¶óŬ, MSSQL : ALTER TABLE tProject DROP CONSTRAINT PK_projectID;
¿À¶óŬ, ¸¶¸®¾Æ : ALTER TABLE tProject DROP PRIMARY KEY;

DELETE FROM tProject WHERE employee='¹ÙÀ̵ç';
ALTER TABLE tProject ADD CONSTRAINT FK_Project_Employee 
FOREIGN KEY(employee) REFERENCES tEmployee(name);

ALTER TABLE tProject DROP CONSTRAINT FK_Project_Employee;

¿À¶óŬ, ¸¶¸®¾Æ : ALTER TABLE tCity MODIFY region CHAR(30);
MSSQL : ALTER TABLE tCity ALTER COLUMN region CHAR(30);

INSERT INTO tCity VALUES ('Á¦ÁÖ',1849,67,'y','Á¦ÁÖƯº°ÀÚÄ¡µµ');

¿À¶óŬ, ¸¶¸®¾Æ : ALTER TABLE tCity MODIFY region CHAR(1);
MSSQL : ALTER TABLE tCity ALTER COLUMN region CHAR(1);

¿À¶óŬ, ¸¶¸®¾Æ : ALTER TABLE tCity MODIFY popu DECIMAL(10,2);
MSSQL : ALTER TABLE tCity ALTER COLUMN popu DECIMAL(10,2);

UPDATE tCity SET popu = 21.2389 WHERE name = '¿À»ê';

CREATE TABLE tCityBackup AS SELECT * FROM tCity;		-- ¹é¾÷
TRUNCATE TABLE tCity;					-- ¿øº» ºñ¿ò
ALTER TABLE tCity MODIFY popu DECIMAL(10,2);		-- Çʵå ŸÀÔ º¯°æ
INSERT INTO tCity SELECT * FROM tCityBackup;		-- ¹é¾÷ º¹¿ø
UPDATE tCity SET popu = 21.2389 WHERE name = '¿À»ê';		-- Çʵ尪 º¯°æ
COMMIT;									-- È®Á¤
DROP TABLE tCityBackup;					-- ¹é¾÷ »èÁ¦

DELETE FROM tProject WHERE employee='Æ®·³ÇÁ';
¿À¶óŬ, ¸¶¸®¾Æ : ALTER TABLE tProject MODIFY project VARCHAR(30) NOT NULL;	
MSSQL : ALTER TABLE tProject ALTER COLUMN project VARCHAR(30) NOT NULL;

¿À¶óŬ : ALTER TABLE tProject MODIFY cost DEFAULT 100;
¸¶¸®¾Æ : ALTER TABLE tProject MODIFY cost VARCHAR(30) DEFAULT 100;
MSSQL : ALTER TABLE tProject ALTER COLUMN project VARCHAR(30) DEFAULT 100 NOT NULL;

INSERT INTO tProject (projectid, employee, project) VALUES (4, '¿À¹Ù¸¶', '±âÈÄ º¯È­ ȸÀÇ Âü¼®');

INSERT INTO tProject VALUES (5, 'Ŭ¸°ÅÏ', 'ºÏÇÙ Á¦°Å', -100);

DELETE FROM tProject WHERE employee='Ŭ¸°ÅÏ';
ALTER TABLE tProject ADD CONSTRAINT cost_check CHECK(cost > 0);

CREATE TABLE tCityBackup AS SELECT * FROM tCity;
DROP TABLE tCity;
CREATE TABLE tCity
(
	name CHAR(10) PRIMARY KEY,
	region CHAR(6) NOT NULL,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL
);
INSERT INTO tCity (name, region, area, popu, metro) 
    SELECT name, region, area, popu, metro FROM tCityBackup;
COMMIT;
DROP TABLE tCityBackup;

¿À¶óŬ : ALTER TABLE tCity RENAME COLUMN popu TO ingu;
MSSQL : sp_rename 'tCity.popu', 'ingu';
¸¶¸®¾Æ : ALTER TABLE tCity CHANGE popu ingu INT NULL; 

¿À¶óŬ, ¸¶¸®¾Æ : ALTER TABLE tCity RENAME TO tDosi;
MSSQL : sp_rename 'tCity', 'tDosi';

COMMENT ON TABLE tCity IS 'µµ½Ã ¸ñ·Ï';
COMMENT ON COLUMN tCity.popu IS 'Àα¸';

SELECT COMMENTS FROM user_tab_comments where table_name = 'TCITY';
SELECT * FROM user_col_comments where table_name = 'TCITY';

sp_addextendedproperty 'MS_Description', 'µµ½Ã¸ñ·Ï', 'USER', DBO, 'TABLE', tCity;
sp_addextendedproperty 'MS_Description', 'Àα¸', 'USER', DBO, 'TABLE', tCity, 'COLUMN', popu;

-------------------------------------------------
-- 15Àå

SET SERVEROUTPUT ON;

BEGIN
	DBMS_OUTPUT.PUT_LINE('¾È³çÇϼ¼¿ä');
END;

BEGIN
	DBMS_OUTPUT.PUT_LINE('¾È³çÇϼ¼¿ä')
END

BEGIN
	DBMS_OUTPUT.PUT_LINE(2 + 3 * 4);
	DBMS_OUTPUT.PUT_LINE(POWER(2, 3));
END;

DECLARE
    v_price INT := 1000;
    v_num INT := 5;
    v_total INT;
BEGIN
    v_total := v_price * v_num;
    DBMS_OUTPUT.PUT_LINE(v_total);
END;

DECLARE v_price INT := 1000;v_num INT := 5;v_total INT;
BEGIN v_total := v_price * v_num;DBMS_OUTPUT.PUT_LINE(v_total);END;

DECLARE
    c_mile CONSTANT NUMBER := 1.609;
    v_kilo INT;
BEGIN
    v_kilo := 400;
    DBMS_OUTPUT.PUT_LINE(v_kilo * c_mile);
END;

DECLARE
    v_price tItem.price%TYPE := 1000;
    v_num v_price%TYPE := 5;
    v_total v_price%TYPE;
BEGIN
    v_total := v_price * v_num;
    DBMS_OUTPUT.PUT_LINE(v_total);
END;

DECLARE 
    v_isOk BOOLEAN;
BEGIN
    v_isOk := (1 = 1);
    IF v_isOK THEN
        DBMS_OUTPUT.PUT_LINE('OK');
    END IF;
END;

DECLARE 
    v_member tOrder.member%TYPE;
BEGIN
    SELECT member INTO v_member FROM tOrder WHERE orderID = 1;
    DBMS_OUTPUT.PUT_LINE(v_member);
END;

DECLARE 
    v_member tOrder.member%TYPE;
    v_item VARCHAR(20);
BEGIN
    SELECT member, item INTO v_member, v_item FROM tOrder WHERE orderID = 1;
    DBMS_OUTPUT.PUT_LINE(v_member || 'ÀÇ ' || v_item || ' ÁÖ¹®');
END;

DECLARE
    v_row tCity%ROWTYPE;
BEGIN
    SELECT * INTO v_row FROM tCity WHERE name = 'ºÎ»ê';
    DBMS_OUTPUT.PUT_LINE(TRIM(v_row.name) || ', ' || v_row.area || ', ' || v_row.popu);
END;

DECLARE
    v_row tCity%ROWTYPE;
BEGIN
    SELECT name, region INTO v_row.name, v_row.region FROM tCity WHERE name = '̵̇';
    DBMS_OUTPUT.PUT_LINE(v_row.region || 'µµÀÇ ' || v_row.name);
END;

DECLARE
    TYPE cap IS RECORD(area INT, popu INT);
    city cap;
BEGIN
    SELECT area, popu INTO city FROM tCity WHERE name = 'ºÎ»ê';
    DBMS_OUTPUT.PUT_LINE(city.area || ',' || city.popu);
END;

TYPE cap IS RECORD(area tCity.area%TYPE, popu tCity.popu%TYPE);

DECLARE
    v_maxPopu INT;
    v_cityName VARCHAR(10);
BEGIN
    SELECT MAX(popu) INTO v_maxPopu FROM tCity;
    SELECT name INTO v_cityName FROM tCity WHERE popu = v_maxPopu;
    DBMS_OUTPUT.PUT_LINE(v_cityName);
END;

DECLARE
    v_popu INT;
BEGIN
    SELECT popu INTO v_popu FROM tCity WHERE name = '¼­¿ï';
    v_popu := v_popu * 2;
    UPDATE tCity SET popu = v_popu WHERE name = '¼­¿ï';
END;

DECLARE
    TYPE int_array IS VARRAY(5) OF NUMBER;
    ar int_array;
BEGIN
    ar := int_array(8, 9, 0, 6, 2);
    FOR idx IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(ar(idx));
    END LOOP;
END;

DECLARE
    TYPE int_array IS VARRAY(3) OF NUMBER;
    TYPE int_array2 IS VARRAY(3) OF int_array;
    ar int_array2;
BEGIN
    ar := int_array2(int_array(1, 2, 3), int_array(4, 5, 6), int_array(7, 8, 9));
    DBMS_OUTPUT.PUT_LINE(ar(2)(3));
END;

DECLARE
    TYPE int_table IS TABLE OF NUMBER;
    ar int_table;
BEGIN
    ar := int_table(1, 2, 3, 4, 5, 6);
    DBMS_OUTPUT.PUT_LINE(ar(4));
END;

DECLARE
    TYPE string_int_map IS TABLE OF NUMBER INDEX BY STRING(20);
    score string_int_map;
BEGIN
    score('È«±æµ¿') := 80;
    score('ȲÁøÀÌ') := 90;
    score('¾î¿ìµ¿') := 90;
    score.DELETE('ȲÁøÀÌ');
    DBMS_OUTPUT.PUT_LINE(score.COUNT());
    DBMS_OUTPUT.PUT_LINE(score('È«±æµ¿'));
END;

DECLARE v_score INT := 12;
BEGIN
    IF v_score = 12 THEN
        DBMS_OUTPUT.PUT_LINE('12ÀÔ´Ï´Ù');
    ELSE
        DBMS_OUTPUT.PUT_LINE('12°¡ ¾Æ´Õ´Ï´Ù');
    END IF;
END;

DECLARE v_score INT := 12;
BEGIN
    IF v_score = 12 THEN
        DBMS_OUTPUT.PUT_LINE('12ÀÔ´Ï´Ù');
        DBMS_OUTPUT.PUT_LINE('Âü ÀßÇß¾î¿ä.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('12°¡ ¾Æ´Õ´Ï´Ù');
        DBMS_OUTPUT.PUT_LINE('Á» ´õ ³ë·ÂÇϼ¼¿ä.');
    END IF;
END;

DECLARE 
    v_popu INT;
    v_message VARCHAR(50);
BEGIN
    SELECT popu INTO v_popu FROM tCity WHERE name = 'ºÎ»ê';
    IF v_popu > 100 THEN
        v_message := '100¸¸ÀÌ ³Ñ½À´Ï´Ù';
    ELSE
        v_message := '100¸¸º¸´Ù Àû½À´Ï´Ù';
    END IF;    
    DBMS_OUTPUT.PUT_LINE('ºÎ»êÀÇ Àα¸´Â ' || v_message);
END;

DECLARE 
    v_num INT := 1;
    v_total INT := 0;
BEGIN
    WHILE v_num <= 100
    LOOP
        v_total := v_total + v_num;
        v_num := v_num + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('1~100±îÁöÀÇ ÇÕ»ê °á°ú = ' || v_total);
END;

DECLARE 
    v_total INT := 0;
BEGIN
    FOR v_num IN 1 .. 100
    LOOP
        v_total := v_total + v_num;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('1~100±îÁöÀÇ ÇÕ»ê °á°ú = ' || v_total);
END;

BEGIN
    FOR v_cities IN (SELECT * FROM tCity WHERE region='°æ±â')
    LOOP
        DBMS_OUTPUT.PUT_LINE(TRIM(v_cities.name) || ' : ' || v_cities.area || ',' || v_cities.popu);
    END LOOP;
END;

DECLARE 
    v_num INT := 1;
    v_total INT := 0;
BEGIN
    WHILE 1 = 1
    LOOP
        v_total := v_total + v_num;
        v_num := v_num + 1;
        IF v_num > 100 THEN
            EXIT;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('1~100±îÁöÀÇ ÇÕ»ê °á°ú = ' || v_total);
END;

DECLARE 
    v_num INT := 1;
    v_total INT := 0;
BEGIN
    LOOP
        v_total := v_total + v_num;
        v_num := v_num + 1;
        EXIT WHEN v_num > 100;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('1~100±îÁöÀÇ ÇÕ»ê °á°ú = ' || v_total);
END;

DECLARE 
    v_num INT := 1;
    v_total INT := 0;
BEGIN
    <<HERE>>    
    v_total := v_total + v_num;
    v_num := v_num + 1;
    IF v_num <= 100 THEN
        GOTO HERE;
    END IF;
   	DBMS_OUTPUT.PUT_LINE('1~100±îÁöÀÇ ÇÕ»ê °á°ú = ' || v_total);
END;

BEGIN
    DBMS_LOCK.SLEEP(5);
    DBMS_OUTPUT.PUT_LINE('´ë±âÁ¾·á');
END;

DECLARE 
    v_num INT := 2;
    v_numword CHAR(30);
BEGIN
    v_numword := 
    CASE v_num
        WHEN 1 THEN 'Çϳª'
        WHEN 2 THEN 'µÑ'
        WHEN 3 THEN '¼Â'
        WHEN 4 THEN '³Ý'
        ELSE '±×¿Ü'
    END;
    DBMS_OUTPUT.PUT_LINE(v_numword);
END;

DECLARE 
    v_num INT := 6;
    v_numword CHAR(30);
BEGIN
    v_numword := 
    CASE 
        WHEN v_num < 0 THEN 'À½¼ö'
        WHEN v_num IN (1, 2, 3) THEN 'Çϳª, µÑ, ¼Â'
        WHEN v_num > 4 THEN '³Ýº¸´Ù ´õ Å­'
        ELSE '±×¿Ü'
    END;
    DBMS_OUTPUT.PUT_LINE(v_numword);
END;

SELECT item, 
    CASE category 
        WHEN 'ÆмÇ' THEN '¹ÞÀÚ ¸¶ÀÚ »¡¾Æ¼­ ÀÔÀ¸¼¼¿ä.'
        WHEN '°¡Àü' THEN 'Ãæ°ÝÀ» ÁÖÁö ¸¶¼¼¿ä.'
        WHEN '½ÄÇ°' THEN '³ÃÀå º¸°üÇϼ¼¿ä.'
        WHEN '¼ºÀÎ' THEN '¾ÖµéÀº °¡¶ó'
    END AS ÁÖÀÇ»çÇ×
FROM tItem

SELECT member, item,
    CASE status
        WHEN 1 THEN 'ÁÖ¹®'
        WHEN 2 THEN '¹è¼ÛÁß'
        WHEN 3 THEN '¹è¼Û¿Ï·á'
        ELSE '±âŸ'
    END AS »óÅÂ
FROM tOrder;

SELECT member, item, DECODE(status, 1, 'ÁÖ¹®', 2, '¹è¼ÛÁß', 3, '¹è¼Û¿Ï·á', '±âŸ') 
AS »óÅÂ FROM tOrder;

DECLARE 
    v_num INT := 2;
    v_popu INT;
BEGIN
    CASE 
        WHEN v_num = 1 THEN SELECT popu INTO v_popu FROM tCity WHERE name = '¼­¿ï';
        WHEN v_num = 2 THEN DBMS_OUTPUT.PUT_LINE('µÑ');
        WHEN v_num = 3 THEN COMMIT;
        WHEN v_num > 4 THEN ROLLBACK;
        ELSE DBMS_OUTPUT.PUT_LINE('¾Ë ¼ö ¾ø´Â ¸í·É');
    END CASE;
END;

DECLARE v_member CHAR(20);
BEGIN
    SELECT member INTO v_member FROM tOrder WHERE orderID = 100;
    DBMS_OUTPUT.PUT_LINE(v_member);
END;

DECLARE v_member CHAR(20);
BEGIN
    SELECT member INTO v_member FROM tOrder WHERE orderID = 100;
    DBMS_OUTPUT.PUT_LINE(v_member);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('ÁÖ¹® ¹øÈ£°¡ ¾ø½À´Ï´Ù.');
END;

DECLARE v_member CHAR(20);
BEGIN
    SELECT member INTO v_member FROM tOrder WHERE orderID > 1;
    DBMS_OUTPUT.PUT_LINE(v_member);
    EXCEPTION
        WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('ÇϳªÀÇ ÁÖ¹®¸¸ ¼±ÅÃÇϽʽÿÀ.');
END;

DECLARE 
    v_member CHAR(20);
    v_orderID INT := -1;
    negativeOrder EXCEPTION;
BEGIN
    IF (v_orderID < 0) THEN
        RAISE negativeOrder;
    END IF;
    SELECT member INTO v_member FROM tOrder WHERE orderID = v_orderID;
    DBMS_OUTPUT.PUT_LINE(v_member);
    EXCEPTION
        WHEN negativeOrder THEN
            DBMS_OUTPUT.PUT_LINE('ÁÖ¹® ¹øÈ£°¡ À½¼ö¿©¼­´Â ¾ÈµË´Ï´Ù.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('¿øÀÎÀ» ¾Ë ¼ö ¾ø´Â ¿¹¿Ü°¡ ¹ß»ýÇß½À´Ï´Ù.');
END;

DECLARE 
    v_member CHAR(20);
    v_orderID INT := -1;
BEGIN
    IF (v_orderID < 0) THEN
        RAISE_APPLICATION_ERROR(-20000, 'ÁÖ¹® ¹øÈ£°¡ À½¼ö¿©¼­´Â ¾ÈµË´Ï´Ù.');
    END IF;
    SELECT member INTO v_member FROM tOrder WHERE orderID = v_orderID;
    DBMS_OUTPUT.PUT_LINE(v_member);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLCODE);
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

SELECT '¾È³çÇϼ¼¿ä';
SELECT 2 + 3 * 4;
SELECT POWER(2, 3);

DECLARE @salary INT;			-- Á¤¼öÇüÀÇ @salary º¯¼ö
DECLARE @a INT, @b INT = 100;		-- µÎ °³ÀÇ Á¤¼öÇü º¯¼ö. b´Â 100À¸·Î ÃʱâÈ­
DECLARE @age INT, @name CHAR(5);	-- Á¤¼öÇü º¯¼ö¿Í ¹®ÀÚ¿­ º¯¼ö ¼±¾ð

DECLARE @price INT;
SET @price = 1000;
SELECT @price;

DECLARE @name CHAR(20);
SELECT @name = member FROM tOrder WHERE orderID=1;
SELECT @name;

SET @name = (SELECT member FROM tOrder WHERE orderID=1);

DECLARE @score INT = 12;
IF @score = 12
	PRINT '12ÀÔ´Ï´Ù.';
ELSE
	PRINT '12°¡ ¾Æ´Õ´Ï´Ù.';

DECLARE @message VARCHAR(50);
IF (SELECT popu FROM tCity WHERE name = 'ºÎ»ê') > 100
BEGIN	
	SET @message='100¸¸ÀÌ ³Ñ½À´Ï´Ù';
	PRINT '¸¹À̵µ ³º¾Ò³×¿ä'
END
ELSE
BEGIN
	SET @message='100¸¸º¸´Ù Àû½À´Ï´Ù';
	PRINT '¾ÆÁ÷ ´õ ¸¹ÀÌ ³º¾Æ¾ß°Ú³×¿ä'
END
PRINT 'ºÎ»êÀÇ Àα¸´Â ' + @message;

DECLARE @num INT = 1, @sum INT = 0;

WHILE @num <= 100
BEGIN
	SET @sum = @sum + @num;
	SET @num = @num + 1;
END
PRINT '1~100±îÁöÀÇ ÇÕ»ê °á°ú = ' + CONVERT(VARCHAR(10), @sum);

WHILE 1=1
BEGIN
	SET @sum = @sum + @num;
	SET @num = @num + 1;
	IF @num > 100
		BREAK;
END

HERE:
SET @sum = @sum + @num;
SET @num = @num + 1;
IF @num <= 100
	GOTO HERE;

WAITFOR DELAY '00:00:05';		-- 5ÃÊ°£ ´ë±â
WAITFOR TIME '02:20:00';			-- 2:20ºÐ±îÁö ´ë±â

BEGIN TRY
	INSERT INTO tCity VALUES ('¼­¿ï',600,1000,'y','°æ±â');
END TRY
BEGIN CATCH
    PRINT '±âº»Å°°¡ °°Àº ·¹Äڵ带 »ðÀÔÇÒ ¼ö ¾ø½À´Ï´Ù.';
    PRINT '¿¡·¯ ¹øÈ£ : ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
    PRINT '¸Þ½ÃÁö : ' + ERROR_MESSAGE()
END CATCH

BEGIN TRY
    SELECT mayor FROM tCity;
END TRY
BEGIN CATCH
    PRINT '½ÃÀå´Ô¿¡ ´ëÇÑ Á¤º¸´Â ¾ø½À´Ï´Ù.'
END CATCH

DECLARE @popu INT = 12345;

BEGIN TRY
	IF @popu > 10000
		THROW 50001, 'µµ½ÃÀÇ Àα¸°¡ 1¾ïÀÌ ³ÑÀ» ¸®°¡ ¾øÀ»ÅÙµ¥...', 1;
	ELSE
		INSERT INTO tCity VALUES ('±¤ÁÖ',600,@popu,'y','Àü¶ó');
END TRY
BEGIN CATCH
    PRINT '¿¡·¯ ¹øÈ£ : ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
    PRINT '¸Þ½ÃÁö : ' + ERROR_MESSAGE()
END CATCH

-------------------------------------------------
-- 16Àå

CREATE PROCEDURE SP_GetPopu
AS
    v_popu INT;
BEGIN
    SELECT popu INTO v_popu FROM tCity WHERE name = '¼­¿ï';
    DBMS_OUTPUT.PUT_LINE(v_popu);
END;

EXECUTE SP_GetPopu();

SELECT * FROM user_objects WHERE object_type = 'PROCEDURE';

SELECT * FROM user_source WHERE name = 'SP_GETPOPU';

CREATE PROCEDURE SP_GetPopu
...
    SELECT popu INTO v_popu FROM tCity WHERE name = 'ºÎ»ê';

DROP PROCEDURE SP_GetPopu;

CREATE OR REPLACE PROCEDURE SP_GetPopu
AS
    v_popu INT;
BEGIN
    SELECT popu INTO v_popu FROM tCity WHERE name = 'ºÎ»ê';
    DBMS_OUTPUT.PUT_LINE(v_popu);
END;

CREATE OR REPLACE PROCEDURE SP_GetPopu
AS
    v_popu INT;
BEGIN
    SELECT population INTO v_popu FROM tCity WHERE name = 'ºÎ»ê';
    DBMS_OUTPUT.PUT_LINE(v_popu);
END;

DROP TABLE tCity;

CREATE OR REPLACE PROCEDURE SP_GetCityPopu(p_name IN CHAR)
AS
    v_popu INT;
BEGIN
    SELECT popu INTO v_popu FROM tCity WHERE name = p_name;
    DBMS_OUTPUT.PUT_LINE(p_name || 'ÀÇ Àα¸´Â ' || v_popu || '¸¸¸íÀÔ´Ï´Ù.');
END;

EXEC SP_GetCityPopu('¿À»ê');		-- ¿À»êÀÇ Àα¸´Â 21¸¸¸íÀÔ´Ï´Ù.
EXEC SP_GetCityPopu('ûÁÖ');		-- ûÁÖÀÇ Àα¸´Â 83¸¸¸íÀÔ´Ï´Ù.

EXEC SP_GetCityPopu();      		-- ¿¡·¯
EXEC SP_GetCityPopu('¼­¿ï', 'ºÎ»ê');    	-- ¿¡·¯

CREATE OR REPLACE PROCEDURE SP_GetCityPopu(p_name IN tCity.name%TYPE := '¼­¿ï')
....

CREATE OR REPLACE PROCEDURE SP_GrantBonus(p_member CHAR, p_bonus INT)
AS
BEGIN
    UPDATE tMember SET money = money + p_bonus WHERE member = p_member;
    COMMIT;
END;

EXEC SP_GrantBonus('ÃáÇâ', 123);

EXEC SP_GrantBonus(p_member=>'ÃáÇâ', p_bonus=>123);
EXEC SP_GrantBonus(p_bonus=>123, p_member=>'ÃáÇâ');

CREATE OR REPLACE PROCEDURE SP_OutCityPopu(p_name IN CHAR, o_popu OUT INT)
AS
BEGIN
    SELECT popu INTO o_popu FROM tCity WHERE name = p_name;
END;

DECLARE 
    v_popu INT;
BEGIN
    SP_OutCityPopu('¼­¿ï', v_popu);
    DBMS_OUTPUT.PUT_LINE(v_popu);
END; 

CREATE OR REPLACE PROCEDURE SP_OutCityAreaPopu(p_name IN CHAR, o_area OUT INT, o_popu OUT INT)
AS
BEGIN
    SELECT area, popu INTO o_area, o_popu FROM tCity WHERE name = p_name;
END;

DECLARE 
    v_area INT;
    v_popu INT;
BEGIN
    SP_OutCityAreaPopu('ºÎ»ê', v_area, v_popu);
    DBMS_OUTPUT.PUT_LINE(v_area || ' ,' || v_popu);
END;

CREATE OR REPLACE PROCEDURE SP_GetCityPopu(p_name IN tCity.name%TYPE := '¼­¿ï')
AS
    v_popu INT;
BEGIN
    SP_OutCityPopu(p_name, v_popu);
    DBMS_OUTPUT.PUT_LINE(p_name || 'ÀÇ Àα¸´Â ' || v_popu || '¸¸¸íÀÔ´Ï´Ù.');
END;

EXECUTE SP_GetCityPopu('Æò¾ç');

CREATE OR REPLACE PROCEDURE SP_GetCityPopu(p_name IN tCity.name%TYPE := '¼­¿ï')
AS
    v_popu INT;
BEGIN
    SELECT popu INTO v_popu FROM tCity WHERE name = p_name;
    DBMS_OUTPUT.PUT_LINE(p_name || 'ÀÇ Àα¸´Â ' || v_popu || '¸¸¸íÀÔ´Ï´Ù.');
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('¾ø´Â µµ½ÃÀÔ´Ï´Ù.');
        WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE('µµ½Ã°¡ ³Ê¹« ¸¹½À´Ï´Ù.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('¾Ë ¼ö ¾ø´Â ¿¹¿ÜÀÔ´Ï´Ù.');
END;

SELECT popu INTO v_popu FROM tCity WHERE name = p_name;
IF v_popu IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('¾ø´Â µµ½ÃÀÔ´Ï´Ù.');
END IF;

CREATE OR REPLACE FUNCTION FN_AddInt(a INT, b INT) 
RETURN INT
AS
BEGIN
	RETURN a + b;
END;

SELECT FN_AddInt(2, 3) FROM dual;
SELECT * FROM tCity WHERE popu > FN_AddInt(10, 20);

DECLARE v_sum INT;
BEGIN
    v_sum := FN_AddInt(2, 3);
    DBMS_OUTPUT.PUT_LINE(v_sum);
END;

CREATE OR REPLACE FUNCTION FN_GetSum(p_upBound INT)
RETURN INT
AS
    v_total INT := 0;
BEGIN
    FOR v_num IN 1 .. p_upBound
    LOOP
        v_total := v_total + v_num;
    END LOOP;
    RETURN v_total;
END;

SELECT FN_GetSum(10) FROM dual;

CREATE OR REPLACE TYPE gu_row AS OBJECT
(
    dan INT, 
    num INT, 
    multi INT
);

CREATE OR REPLACE TYPE gu_table AS TABLE OF gu_row;

CREATE OR REPLACE FUNCTION makeDan(p_dan INT)
RETURN gu_table PIPELINED
AS
    v_row gu_row;
BEGIN
    FOR v_num IN 1..9
    LOOP
        v_row := gu_row(p_dan, v_num, p_dan * v_num);
        PIPE ROW(v_row);
    END LOOP;
    RETURN;
END;

SELECT * FROM TABLE(makeDan(5));
SELECT * FROM TABLE(makeDan(7));

CREATE PROCEDURE PROC_GetCityPopu
	@p_name CHAR(10)
AS
BEGIN
	SELECT popu FROM tCity WHERE name = @p_name;
END

EXEC PROC_GetCityPopu @p_name = '¼­¿ï';
EXEC PROC_GetCityPopu '¼­¿ï';
PROC_GetCityPopu '¼­¿ï';

ALTER PROCEDURE PROC_GetCityPopu
	@p_name CHAR(10)
AS
DECLARE
	@popu INT
BEGIN
	SELECT @popu = popu FROM tCity WHERE name = @p_name;
	PRINT @popu
END

ALTER PROCEDURE PROC_GetCityPopu
	@p_name CHAR(10)
AS
BEGIN
	SELECT popu FROM tCity2 WHERE name = @p_name;
END

CREATE PROCEDURE PROC_GrantBonus
	@p_member CHAR(20),
	@p_bonus INT = 100
AS
BEGIN
    UPDATE tMember SET money = money + @p_bonus WHERE member = @p_member;
END;

PROC_GrantBonus 'ÃáÇâ', 2000;
PROC_GrantBonus 'ÃáÇâ';

PROC_GrantBonus @p_member = 'ÃáÇâ', @p_bonus = 2000;
PROC_GrantBonus @p_bonus = 2000, @p_member = 'ÃáÇâ';

CREATE PROCEDURE PROC_OutCityPopu
	@p_name CHAR(10), 
	@o_popu INT OUTPUT
AS
BEGIN
    SELECT @o_popu = popu FROM tCity WHERE name = @p_name;
END;

DECLARE @popu INT;
EXECUTE PROC_OutCityPopu '¼­¿ï', @popu OUTPUT;
PRINT '¼­¿ïÀÇ Àα¸´Â ' + CAST(@popu AS VARCHAR(10)) + '¸¸¸íÀÔ´Ï´Ù.';

CREATE PROCEDURE PROC_RetCityPopu
	@p_name CHAR(10)
AS
DECLARE
	@popu INT;
BEGIN
    SELECT @popu = popu FROM tCity WHERE name = @p_name;
    RETURN @popu;
END;

DECLARE @popu INT;
EXECUTE @popu = PROC_RetCityPopu '¼­¿ï';
PRINT '¼­¿ïÀÇ Àα¸´Â ' + CAST(@popu AS VARCHAR(10)) + '¸¸¸íÀÔ´Ï´Ù.';

PRINT '¼­¿ïÀÇ Àα¸´Â ' + CAST(PROC_RetCityPopu '¼­¿ï' AS VARCHAR(10)) + '¸¸¸íÀÔ´Ï´Ù';
SELECT PROC_RetCityPopu '¼­¿ï';

CREATE PROCEDURE PROC_InsertSeoul
AS
BEGIN
	INSERT INTO tCity VALUES ('¼­¿ï',605,974,'y','°æ±â');
END

ALTER PROCEDURE PROC_InsertSeoul
AS
BEGIN
	INSERT INTO tCity VALUES ('¼­¿ï',605,974,'y','°æ±â');
	IF @@ERROR != 0
	BEGIN
		PRINT('»õ ·¹Äڵ带 »ðÀÔÇÏÁö ¸øÇß½À´Ï´Ù.');
	END
END

ALTER PROCEDURE PROC_InsertSeoul
AS
BEGIN
	BEGIN TRY
		INSERT INTO tCity VALUES ('¼­¿ï',605,974,'y','°æ±â');
	END TRY

	BEGIN CATCH
		PRINT '¿¡·¯ ¹øÈ£ : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
		PRINT '¿¡·¯ ¸Þ½ÃÁö : ' + ERROR_MESSAGE();
	END CATCH
END

CREATE FUNCTION FN_AddInt(@a INT, @b INT) RETURNS INT
AS
BEGIN
	RETURN @a + @b;
END

SELECT Study.dbo.FN_AddInt(2, 3);

CREATE FUNCTION FN_GetCity(@region CHAR(10))
RETURNS TABLE
AS
RETURN SELECT * FROM tCity WHERE region = @region;

SELECT * FROM dbo.FN_GetCity('°­¿ø');

SELECT * FROM dbo.FN_GetCity('°­¿ø') WHERE popu > 10;

CREATE FUNCTION FN_GetCityTable(@region CHAR(10))
RETURNS @result TABLE
	(name VARCHAR(10),popu INT)
AS
BEGIN
	INSERT INTO @result SELECT name, popu FROM tCity WHERE region = @region;
	RETURN;
END

SELECT * FROM dbo.FN_GetCityTable('°æ±â');

-------------------------------------------------
-- 17Àå

DECLARE
    CURSOR v_cursor IS SELECT name FROM tCity;
    v_name CHAR(10);
BEGIN
    OPEN v_cursor;
    LOOP
        FETCH v_cursor INTO v_name;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
    CLOSE v_cursor;
END;

DECLARE
    CURSOR v_cursor IS SELECT * FROM tCity;
    v_city tCity%ROWTYPE;
BEGIN
    OPEN v_cursor;
    LOOP
        FETCH v_cursor INTO v_city;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_city.region || v_city.name || v_city.area);
    END LOOP;
    CLOSE v_cursor;
END;

DECLARE
BEGIN
    FOR v_cursor IN (SELECT name FROM tCity)
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_cursor.name);
    END LOOP;
END;

BEGIN
    UPDATE tCity SET popu = popu WHERE region = '°­¿ø';
    IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('°á°ú¼ÂÀÌ ÀÖÀ½'); END IF;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '°³ÀÇ ÇàÀÌ ¹Ù²ñ');
END;

DECLARE
    v_cursor SYS_REFCURSOR;
    v_name CHAR(10);
BEGIN
    OPEN v_cursor FOR SELECT name FROM tCity;
    LOOP
        FETCH v_cursor INTO v_name;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
    CLOSE v_cursor;
END;

CREATE OR REPLACE PROCEDURE SP_OutCityName(p_region IN CHAR, o_cursor OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN o_cursor FOR SELECT name FROM tCity WHERE region = p_region;
END;

DECLARE
    v_cursor SYS_REFCURSOR;
    v_name CHAR(10);
BEGIN
    SP_OutCityName('Àü¶ó', v_cursor);
    LOOP
        FETCH v_cursor INTO v_name;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
END;

DECLARE
    CURSOR v_cursor IS SELECT * FROM tCity FOR UPDATE;
    v_city tCity%ROWTYPE;
BEGIN
    OPEN v_cursor;
    LOOP
        FETCH v_cursor INTO v_city;
        EXIT WHEN v_cursor%NOTFOUND;
        IF v_city.metro = 'y' THEN
            UPDATE tCity SET area = area + 10 WHERE CURRENT OF v_cursor;
        END IF;
    END LOOP;
    CLOSE v_cursor;
END;

DECLARE @name CHAR(10)
DECLARE testcursor CURSOR FOR SELECT name FROM tCity
OPEN testcursor
FETCH NEXT FROM testcursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @name
	FETCH NEXT FROM testcursor INTO @name
END
CLOSE testcursor
DEALLOCATE testcursor

START:
FETCH NEXT FROM testcursor INTO @name
IF @@FETCH_STATUS = 0
BEGIN
	PRINT @name
	GOTO START
END

DECLARE @name CHAR(10)
DECLARE @varCur CURSOR
SET @varCur = CURSOR FOR SELECT name FROM tCity;
OPEN @varCur
FETCH NEXT FROM @varCur INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @name
	FETCH NEXT FROM @varCur INTO @name
END
CLOSE @varCur
DEALLOCATE @VarCur

DECLARE testcursor CURSOR LOCAL SCROLL FOR SELECT name FROM tCity;
OPEN testcursor;

FETCH NEXT FROM testcursor;
FETCH NEXT FROM testcursor;
FETCH PRIOR FROM testcursor;
FETCH LAST FROM testcursor;

DECLARE testcursor CURSOR LOCAL FOR SELECT * FROM tCity;
OPEN testcursor;

FETCH NEXT FROM testcursor;
UPDATE tCity SET popu=1234 WHERE CURRENT OF testcursor;

CLOSE testcursor;
DEALLOCATE testcursor;

DECLARE testcursor CURSOR LOCAL STATIC FOR SELECT name, popu FROM tCity;
OPEN testcursor;

DECLARE @name CHAR(10);
DECLARE @popu INT;
FETCH NEXT FROM testcursor INTO @name, @popu;
PRINT @name + ':' + CONVERT(VARCHAR(12), @popu);
WAITFOR DELAY '00:00:10';
FETCH NEXT FROM testcursor INTO @name, @popu;
PRINT @name + ':' + CONVERT(VARCHAR(12), @popu);

UPDATE tCity SET popu = 500 WHERE name = 'ºÎ»ê';

DECLARE testcursor CURSOR LOCAL DYNAMIC FOR SELECT name, popu FROM tCity;
OPEN testcursor;

DECLARE @name CHAR(10);
DECLARE @popu INT;
FETCH NEXT FROM testcursor INTO @name, @popu;
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @name + ':' + CONVERT(NVARCHAR(12), @popu);
	WAITFOR DELAY '00:00:01';
	FETCH NEXT FROM testcursor INTO @name, @popu;
END

INSERT INTO tCity VALUES ('ÆòÅÃ',453,51,'n','°æ±â');

DECLARE 
    v_orderID INT := 1;
    v_sql VARCHAR(100);
    v_member CHAR(20);
BEGIN
    v_sql := 'SELECT member FROM tOrder WHERE orderID = ' || v_orderID;
    EXECUTE IMMEDIATE v_sql INTO v_member;
    DBMS_OUTPUT.PUT_LINE(v_member);
END;

DECLARE
    v_area INT := 0;
    v_op CHAR(10) := 'Å©´Ù';
    v_sql VARCHAR(100);
    v_name CHAR(10);
BEGIN
    v_sql := 'SELECT name FROM tCity WHERE ';
    IF v_area != 0 THEN
        v_sql := v_sql || 'area ';
        IF v_op = '°°´Ù' THEN v_sql := v_sql || '= '; END IF;
        IF v_op = 'Å©´Ù' THEN v_sql := v_sql || '> '; END IF;
        IF v_op = 'ÀÛ´Ù' THEN v_sql := v_sql || '< '; END IF;
        v_sql := v_sql || v_area || ' AND ';
    END IF;
    v_sql := v_sql || ' rownum = 1';
    EXECUTE IMMEDIATE v_sql INTO v_name;
    DBMS_OUTPUT.PUT_LINE(v_name);
END;

SELECT member INTO v_member FROM tOrder WHERE orderID = v_orderID;

v_table VARCHAR(10) := 'tCity';
SELECT name INTO v_name FROM v_table WHERE rownum = 1;

DECLARE 
    v_table VARCHAR(10) := 'tCity';
    v_sql VARCHAR(100);
    v_name CHAR(20);
BEGIN
    v_sql := 'SELECT name FROM ' || v_table || ' WHERE rownum = 1';
    EXECUTE IMMEDIATE v_sql INTO v_name;
    DBMS_OUTPUT.PUT_LINE(v_name);
END;

DECLARE @area INT = 1000;
DECLARE @op CHAR(10) = 'Å©´Ù';
DECLARE @sql VARCHAR(100);

SET @sql = 'SELECT TOP 1 name FROM tCity';
IF @area != 0
BEGIN
	SET @sql += ' WHERE area ';
	IF @op = '°°´Ù' SET @sql += '=';
	IF @op = 'Å©´Ù' SET @sql += '>';
	IF @op = 'ÀÛ´Ù' SET @sql += '<';
	SET @sql = @sql + CAST(@area AS VARCHAR(10));
END
EXECUTE(@sql);

DECLARE 
    hour_begin INT := 1;
    hour_end INT := 2;
    hour_now INT := hour_begin;
    v_sql VARCHAR(1000);
    v_cursor SYS_REFCURSOR;
    TYPE linecar IS RECORD(line VARCHAR(100), car VARCHAR(100), t1 VARCHAR(100), t2 VARCHAR(100));
    v_lc linecar;
BEGIN
    v_sql := 'SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR hour IN (';
    WHILE hour_now <= hour_end
    LOOP
        v_sql := v_sql || hour_now;
        IF hour_now != hour_end THEN v_sql := v_sql || ', '; END IF;
        hour_now := hour_now + 1;
    END LOOP;
    v_sql := v_sql || '))'; 
    
    OPEN v_cursor FOR v_sql;
    LOOP
        -- INTO Àý¿¡ ·¹ÄÚµå ŸÀÔÀ» ÀûÀ¸¸é µÇ´Âµ¥ ŸÀÔÀ» ¹Ì¸® °áÁ¤ÇÒ ¼ö ¾ø´Ù.
        FETCH v_cursor INTO v_lc;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_lc.line || v_lc.car || v_lc.t1 || ',' || v_lc.t2);
    END LOOP;
END;

DECLARE @hour_begin INT = 1;
DECLARE @hour_end INT = 2;
DECLARE @hour INT = @hour_begin;
DECLARE @sql VARCHAR(1000);

SET @sql = 'SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR hour IN (';
WHILE @hour <= @hour_end
BEGIN
	SET @sql += '[' + CAST(@hour AS VARCHAR) + ']';
	IF @hour != @hour_end SET @sql += ', ';
	SET @hour += 1;
END
SET @sql += ')) as pvt';

EXEC(@sql);

-------------------------------------------------
-- 18Àå

CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE ON tCity
BEGIN
    DBMS_OUTPUT.PUT_LINE('µµ½Ã Á¤º¸¸¦ °»½ÅÇÏ¿´½À´Ï´Ù.');
END;

UPDATE tCity SET popu = popu + 1 WHERE name = '¼­¿ï';


CREATE OR REPLACE TRIGGER TR_Message
AFTER INSERT OR UPDATE OR DELETE ON tCity
BEGIN
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('»õ·Î¿î µµ½Ã¸¦ »ðÀÔÇÏ¿´½À´Ï´Ù.');
    END IF;
    IF UPDATING THEN
        DBMS_OUTPUT.PUT_LINE('µµ½Ã Á¤º¸¸¦ °»½ÅÇÏ¿´½À´Ï´Ù.');
    END IF;
    IF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('µµ½Ã¸¦ »èÁ¦ÇÏ¿´½À´Ï´Ù.');
    END IF;
END;

INSERT INTO tCity VALUES ('¿©ÁÖ',608,11,'n','°æ±â');
UPDATE tCity SET popu = 12 WHERE name = '¿©ÁÖ';
DELETE FROM tCity WHERE NAME = '¿©ÁÖ';

UPDATE tCity SET popu = popu + 1 WHERE region = '°æ±â';

CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE ON tCity
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);
END;

REFERENCING OLD AS pre_rec NEW AS post_rec

UPDATE tCity SET area = area + 1 WHERE name = '¼­¿ï';

CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE OF popu ON tCity 
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);
END;

CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE ON tCity 
FOR EACH ROW
WHEN (NEW.popu > 10)
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);
END;

UPDATE tCity SET popu = popu + 1 WHERE region = '°­¿ø';

CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE ON tCity
FOR EACH ROW
BEGIN
    IF :NEW.popu > 10000 THEN
        RAISE_APPLICATION_ERROR(-20000, 'Àα¸°¡ 1¾ïÀ» ³ÑÀ» ¼ö´Â ¾ø½À´Ï´Ù.');
    END IF;
    DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);
END;

UPDATE tCity SET popu = 12345 WHERE name = '¼­¿ï';

CREATE OR REPLACE TRIGGER TR_PreventChoi
AFTER INSERT ON tStaff
FOR EACH ROW
BEGIN
    IF SUBSTR(:NEW.name,1,1) = 'ÃÖ' THEN
        RAISE_APPLICATION_ERROR(-20000, 'ÃÖ°¡´Â ¾ÈµÇ!');
    END IF;
END;

INSERT INTO tStaff VALUES ('ÃÖ¹«¼±','Ãѹ«ºÎ','³²','2021-6-25','»ç¿ø',280,0);

CREATE OR REPLACE TRIGGER TR_PreventChoi
BEFORE INSERT ON tStaff
FOR EACH ROW
BEGIN
    IF SUBSTR(:NEW.name,1,1) = 'ÃÖ' THEN
        DBMS_OUTPUT.PUT_LINE('ÃÖ°¡´Â ¾ÈµÇ');
        :NEW.name := '±è' || SUBSTR(:NEW.name, 2);
    END IF;
END;

CREATE OR REPLACE TRIGGER TR_DoubleMoney
BEFORE INSERT ON tMember
FOR EACH ROW
BEGIN
    IF :NEW.age = 18 THEN
        :NEW.money := :NEW.money * 2;
    END IF;
END;

INSERT INTO tMember (member, age, email, addr, money) VALUES 
('½ÅÀÔ»ý', 18, 'fresher@kyunghee.ac.kr', '¼­¿ï ȸ±âµ¿', 8000);

CREATE VIEW vCarMaker AS
SELECT tCar.*, factory, domestic FROM tCar INNER JOIN tMaker ON
tCar.maker = tMaker.maker;

INSERT INTO vCarMaker VALUES('ƼÄÚ', 800, 900, '´ë¿ì', '¿ï¸ª', 'y');

CREATE OR REPLACE TRIGGER TR_AddNewCar
INSTEAD OF INSERT ON vCarMaker
FOR EACH ROW
BEGIN
	INSERT INTO tCar (car, capacity, price, maker) VALUES 
        (:NEW.car, :NEW.capacity, :NEW.price, :NEW.maker);
	INSERT INTO tMaker (maker, factory, domestic) VALUES 
        (:NEW.maker, :NEW.factory, :NEW.domestic);
END;

CREATE OR REPLACE TRIGGER TR_Message2
AFTER UPDATE ON tCity
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || '¸éÀû:' || :OLD.area || '->' || :NEW.area);
END;

CREATE OR REPLACE TRIGGER TR_Message3
AFTER UPDATE ON tCity
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || 'Áö¿ª:' || :OLD.region || '->' || :NEW.region);
END;

CREATE OR REPLACE TRIGGER TR_Message3
AFTER UPDATE ON tCity
FOR EACH ROW
FOLLOWS TR_Message
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || 'Áö¿ª:' || :OLD.region || '->' || :NEW.region);
END;

UPDATE tCity SET area = 1000, popu = 12345 WHERE name = '¼­¿ï';

CREATE OR REPLACE TRIGGER TR_OnNewCar
AFTER INSERT ON tCar
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('½ÅÂ÷°¡ Ãâ½ÃµÇ¾ú½À´Ï´Ù.');
END;

INSERT INTO vCarMaker VALUES('ƼÄÚ', 800, 900, '´ë¿ì', '¿ï¸ª', 'y');

CREATE OR REPLACE TRIGGER TR_Change
AFTER DDL ON DATABASE
BEGIN
    DBMS_OUTPUT.PUT_LINE('¸í·É : ' || ora_sysevent);
    DBMS_OUTPUT.PUT_LINE('ŸÀÔ : ' || ora_dict_obj_type);
    DBMS_OUTPUT.PUT_LINE('À̸§ : ' || ora_dict_obj_name);
END;

CREATE TABLE tTemp ( id INT );
DROP TABLE tTemp;

CREATE TABLE tCityHistory
(
    dt DATE,
    suser VARCHAR(20),
    ip VARCHAR(20),
    action VARCHAR(10),
    name CHAR(10),
    area VARCHAR(30) NULL,
    popu VARCHAR(30) NULL
);

CREATE OR REPLACE TRIGGER TR_History
AFTER INSERT OR UPDATE OR DELETE ON tCity
FOR EACH ROW
DECLARE
    areaChange VARCHAR(30);
    popuChange VARCHAR(30);
    suser VARCHAR(20);
    ip VARCHAR(20);
BEGIN
    SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') INTO suser FROM DUAL;
    SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO ip FROM DUAL;
    
    IF INSERTING THEN
        INSERT INTO tCityHistory VALUES 
            (SYSDATE, suser, ip, 'INSERT', :NEW.name, :NEW.area, :NEW.popu);
    END IF;
    IF UPDATING THEN
        IF :OLD.area = :NEW.area THEN
            areaChange := :OLD.area;
        ELSE
            areaChange := :OLD.area || '->' || :NEW.area;
        END IF;
        IF :OLD.popu = :NEW.popu THEN
            popuChange := :OLD.popu;
        ELSE
            popuChange := :OLD.popu || '->' || :NEW.popu;
        END IF;
        INSERT INTO tCityHistory VALUES 
            (SYSDATE, suser, ip, 'UPDATE', :NEW.name, areaChange, popuChange);
    END IF;
    IF DELETING THEN
        INSERT INTO tCityHistory VALUES 
            (SYSDATE, suser, ip, 'DELETE', :OLD.name, :OLD.area, :OLD.popu);
    END IF;
END;

CREATE TRIGGER TR_Message
ON tCity AFTER UPDATE 
AS
BEGIN
    PRINT('µµ½Ã Á¤º¸¸¦ °»½ÅÇÏ¿´½À´Ï´Ù.');
END;

UPDATE tCity SET popu = popu + 1 WHERE name = '¼­¿ï';
UPDATE tCity SET popu = popu + 1 WHERE region = '°æ±â';

ALTER TRIGGER TR_Message
ON tCity AFTER UPDATE 
AS
BEGIN
	SELECT * FROM deleted;
	SELECT * FROM inserted;
END;

ALTER TRIGGER TR_Message
ON tCity AFTER UPDATE 
AS
BEGIN
	SELECT name, popu, ' => ', (SELECT popu FROM inserted WHERE name = D.name) FROM deleted D;
END;

ALTER TABLE tCity DISABLE TRIGGER TR_Message;

ALTER TRIGGER TR_Message
ON tCity AFTER UPDATE 
AS
BEGIN
	IF (SELECT popu FROM inserted) > 10000
	BEGIN
		PRINT('Àα¸°¡ 1¾ïÀ» ³ÑÀ» ¼ö´Â ¾ø½À´Ï´Ù.');
		ROLLBACK;
	END
END;

CREATE TRIGGER TR_PreventChoi
ON tStaff AFTER INSERT
AS
IF EXISTS (SELECT * FROM inserted WHERE name LIKE 'ÃÖ%')
BEGIN
	PRINT 'ÃÖ°¡´Â ¾ÈµÇ!';
	ROLLBACK TRANSACTION;
END

CREATE TRIGGER TR_AddNewCar
ON vCarMaker INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO tCar (car, capacity, price, maker) SELECT car, capacity, price, maker FROM inserted;
	INSERT INTO tMaker (maker, factory, domestic) SELECT maker, factory, domestic FROM inserted;
END

CREATE TRIGGER TR_DoubleMoney
ON tMember AFTER INSERT
AS
DECLARE @name CHAR(20)
DECLARE @age INT
SELECT @name = member, @age = age FROM inserted;
IF @age = 18
	UPDATE tMember SET money = money * 2 WHERE member = @name;

CREATE TRIGGER TR_MoneyChange
ON tMember
AFTER UPDATE
AS
DECLARE @name CHAR(20)
IF UPDATE(money)
BEGIN
	SELECT @name = member FROM inserted;
	PRINT @name + 'ÀÇ ¿¹Ä¡±ÝÀÌ °»½ÅµÇ¾ú½À´Ï´Ù. ºÎÁ¤ÀÌ ¾Æ´ÑÁö È®ÀÎÇØ º¸½Ê½Ã¿À';
END;

CREATE TRIGGER NewDataBase
ON ALL SERVER
AFTER CREATE_DATABASE, DROP_DATABASE
AS
PRINT '»õ·Î¿î DB°¡ »ý¼º ¶Ç´Â Æı«µÇ¾ú½À´Ï´Ù.';

CREATE DATABASE NewDB
DROP DATABASE NewDB

DROP TRIGGER NewDataBase ON ALL SERVER

-------------------------------------------------
-- 19Àå

INSERT INTO tCity VALUES ('ÆòÅÃ',453,51,'n','°æ±â');
SELECT * FROM tCity;

SELECT * FROM v$transaction;

DELETE FROM tCity WHERE metro = 'n';
SELECT * FROM tCity;

UPDATE tMember SET money = money + 100 WHERE member='ÃáÇâ';
APDATE tMember SET money = money - 100 WHERE member='À̵µ·É';

UPDATE tMember SET money = money + 100 WHERE member='ÃáÇâ';
UPDATE tMember2 SET money = money - 100 WHERE member='À̵µ·É';

BEGIN TRANSACTION
UPDATE tMember SET money = money + 100 WHERE member='ÃáÇâ';
UPDATE tMember SET money = money - 100 WHERE member='À̵µ·É';
ROLLBACK;

BEGIN TRAN
UPDATE tMember SET money = money + 10000 WHERE member = 'ÃáÇâ';
DECLARE @remain INT
SELECT @remain = money FROM tMember WHERE member = 'À̵µ·É';
IF @remain < 10000 
BEGIN
	ROLLBACK
END
ELSE
BEGIN
	UPDATE tMember SET money = money - 10000 WHERE member = 'À̵µ·É';
	COMMIT
END
SELECT * FROM tMember WHERE member IN ('ÃáÇâ', 'À̵µ·É');

UPDATE tCity SET popu = 1000 WHERE name = '¼­¿ï';
SAVEPOINT p1000;
UPDATE tCity SET popu = 1100 WHERE name = '¼­¿ï';
SAVEPOINT p1100;
UPDATE tCity SET popu = 1200 WHERE name = '¼­¿ï';
SAVEPOINT p1200;
ROLLBACK TO SAVEPOINT p1100;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

¿À¶óŬ : ALTER PROFILE DEFAULT LIMIT IDLE_TIME 1;
MSSQL : SET LOCK_TIMEOUT 60000

UPDATE tMember SET age=25 WHERE member='Çâ´Ü';
UPDATE tItem SET num=10 WHERE item='µÎºÎ';
COMMIT

UPDATE tItem SET num=5 WHERE item='µÎºÎ';
UPDATE tMember SET age=18 WHERE member='Çâ´Ü';
COMMIT

-------------------------------------------------
-- ºÎ·Ï

SET @score = 123;
SELECT @score;

SELECT @maxnum := MAX(num) FROM tItem;
SELECT item FROM tItem WHERE num = @maxnum;

DELIMITER $$
CREATE [OR REPLACE] PROCEDURE ÇÁ·Î½ÃÀú À̸§()
BEGIN
	¿©±â¿¡ Äڵ带 ÀÛ¼ºÇÑ´Ù.
END $$
DELIMITER ;

DELIMITER $$
CREATE OR REPLACE PROCEDURE SP_proc()
BEGIN
	DECLARE score INT DEFAULT 123;
	SELECT score;
	SET score = score + 1;
	SELECT score;
END $$
DELIMITER ;

CALL SP_proc();

DELIMITER $$
CREATE OR REPLACE PROCEDURE SP_proc()
BEGIN
	DECLARE score INT;
	SET score = 12;
	IF score = 12 THEN
		SELECT '12ÀÔ´Ï´Ù';
	ELSE
		SELECT '12°¡ ¾Æ´Õ´Ï´Ù.';
	END IF;
END $$
DELIMITER ;

DELIMITER $$
CREATE OR REPLACE PROCEDURE SP_proc()
BEGIN
	DECLARE score INT DEFAULT 86;
	CASE
		WHEN score > 95 THEN SELECT 'A+';
		WHEN score > 90 THEN SELECT 'A';
		WHEN score > 85 THEN SELECT 'B+';
		WHEN score > 80 THEN SELECT 'B';
		ELSE SELECT 'C';
	END CASE;
END $$
DELIMITER ;

DELIMITER $$
CREATE OR REPLACE PROCEDURE SP_proc()
BEGIN
	DECLARE score INT DEFAULT 86;
	DECLARE grade CHAR(2);
	SET grade = CASE
		WHEN score > 95 THEN 'A+'
		WHEN score > 90 THEN 'A'
		WHEN score > 85 THEN 'B+'
		WHEN score > 80 THEN 'B'
		ELSE 'C'
	END;
	SELECT grade;
END $$
DELIMITER ;

DELIMITER $$
CREATE OR REPLACE PROCEDURE SP_proc()
BEGIN
	DECLARE num INT DEFAULT 1;
	DECLARE sum INT DEFAULT 0;
	WHILE (num <= 100) DO
		SET sum = sum + num;
		SET num = num + 1;
	END WHILE;

	SELECT sum;
END $$
DELIMITER ;

DELIMITER $$
CREATE OR REPLACE PROCEDURE SP_proc()
BEGIN
	DECLARE num INT DEFAULT 1;
	DECLARE sum INT DEFAULT 0;
	REPEAT
		SET sum = sum + num;
		SET num = num + 1;
		UNTIL num > 100
	END REPEAT;

	SELECT sum;
END $$
DELIMITER ;

DELIMITER $$
CREATE OR REPLACE PROCEDURE SP_proc()
BEGIN
	DECLARE num INT DEFAULT 1;
	DECLARE sum INT DEFAULT 0;
	sumloop: LOOP
		SET sum = sum + num;
		SET num = num + 1;
		IF num > 100 THEN
			LEAVE sumloop;
		END IF;
	END LOOP;

	SELECT sum;
END $$
DELIMITER ;

DELIMITER $$
CREATE OR REPLACE PROCEDURE SP_GrantBonus(IN p_member CHAR(20))
BEGIN
	UPDATE tMember SET money = money + 1000 WHERE member = p_member;
END $$
DELIMITER ;

DELIMITER $$
CREATE OR REPLACE PROCEDURE SP_GetMemberNum(OUT o_member_num INT)
BEGIN
	SELECT COUNT(*) INTO o_member_num FROM tMember;
END $$
DELIMITER ;

CALL SP_GetMemberNum(@num);
SELECT @num;

DELIMITER $$
CREATE OR REPLACE FUNCTION FN_AddInt(a INT, b INT) RETURNS INT
BEGIN
	RETURN a + b;
END $$
DELIMITER ;

SELECT FN_AddInt(2, 3);

DELIMITER $$
CREATE OR REPLACE PROCEDURE SP_dumpCity()
BEGIN
	DECLARE city_name CHAR(10);
	DECLARE eof INT DEFAULT 0;

	DECLARE testcursor CURSOR FOR SELECT name FROM tCity;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1; 
	OPEN testcursor;

	fetch_loop: LOOP
		FETCH testcursor INTO city_name;
		IF eof THEN 
			LEAVE fetch_loop;
		END IF;
		SELECT city_name;
	END LOOP;
	CLOSE testcursor;
END $$
DELIMITER ;

DELIMITER $$
CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE ON tCity
FOR EACH ROW
BEGIN
	SET @result = CONCAT(OLD.popu, '->', NEW.popu);
END $$
DELIMITER ;

UPDATE tCity SET popu = popu + 1 WHERE name = '¼­¿ï';

SELECT @result;