MSSQL 실습

CREATE DATABASE EX4
ON
(
NAME = 'logical_EX',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\os_file_EX4.mdf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);

USE EX4;

CREATE TABLE customer
( cno char(10) NOT NULL,
cname char(20) NOT NULL,
city char(20) NOT NULL,
point int NOT NULL,
primary key (cno)
);

INSERT INTO customer VALUES ('c1', '이순신', '서울', 200);
INSERT INTO customer VALUES ('c2', '김유신', '인천', 150);
INSERT INTO customer VALUES ('c3', '홍길동', '인천', 300);
INSERT INTO customer VALUES ('c4', '강감찬', '서울', 200);
INSERT INTO customer VALUES ('c5', '안중근', '대전', 230);

SELECT * FROM customer;

CREATE TABLE fruit
( fno char(10) NOT NULL,
fname char(20) NOT NULL,
price int NOT NULL,
quantity int NOT NULL,
city char(20) NOT NULL,
primary key (fno)
)
INSERT INTO fruit VALUES ('f1','오렌지', 1000, 500, '서울');
INSERT INTO fruit VALUES ('f2','포도', 2000, 1000, '인천');
INSERT INTO fruit VALUES ('f3','귤', 500, 1500, '청주');
INSERT INTO fruit VALUES ('f4','사과', 2500, 300, '서울');
INSERT INTO fruit VALUES ('f5','배', 3000, 200, '인천');
INSERT INTO fruit VALUES ('f6','바나나', 1000, 400, '서울');

SELECT * FROM fruit;


CREATE TABLE indent
( cno char(10) NOT NULL,
fno char(10) NOT NULL,
qty int NOT NULL,
odate datetime NOT NULL,
oprs char(20) NULL,
primary key (cno, fno, odate),
foreign key (cno) references customer,
foreign key (fno) references fruit
);

INSERT INTO indent VALUES ('c1', 'f1', 300, '2021-12-21', '김효선');
INSERT INTO indent VALUES ('c1', 'f2', 200, '2030-01-04', '이지연');
INSERT INTO indent VALUES ('c1', 'f3', 400, '2022-01-19', '김효선');
INSERT INTO indent VALUES ('c1', 'f4', 200, '2022-01-22', '이지연');
INSERT INTO indent VALUES ('c1', 'f5', 100, '2022-01-23', '김효선');
INSERT INTO indent VALUES ('c1', 'f6', 100, '2022-01-27', NULL);
INSERT INTO indent VALUES ('c2', 'f1', 300, '2050-02-05', '이지연');
INSERT INTO indent VALUES ('c2', 'f2', 400, '2022-02-16', '김효선');
INSERT INTO indent VALUES ('c3', 'f2', 200, '2040-02-18', NULL);
INSERT INTO indent VALUES ('c4', 'f2', 200, '2022-03-04', '이지연');
INSERT INTO indent VALUES ('c4', 'f4', 300, '2060-03-10', '김효선');
INSERT INTO indent VALUES ('c4', 'f5', 400, '2022-03-18', '김효선');

SELECT * FROM indent;

INSERT INTO customer ( cno, cname, city, point )
VALUES ( 'c6', '유관순','부산', 4000 );

SELECT * FROM customer;

INSERT INTO customer
VALUES ( 'c7', '왕건', '철원', 3000 );

SELECT cno, cname
FROM customer; SELECT fno FROM indent;  [SELECT 예제 04] 판매하는 과일의 모든 속성 정보를 검색하라.
SELECT fno FROM indent;


SELECT DISTINCT fno
FROM indent; SELECT * FROM fruit; SELECT cno, '포인트의 원화가치=', point * 1000
FROM customer;

SELECT cno, cname
FROM customer
WHERE point >= 300;

SELECT *
FROM fruit
WHERE price >= 2000 AND city='서울'; SELECT cno, cname
FROM customer
WHERE city IN ('서울', '대전', '광주'); SELECT *
FROM fruit
WHERE price BETWEEN 2000 AND 3000; SELECT cno, fno, odate,oprs
FROM indent
WHERE oprs IS NULL;

SELECT cno, fno, odate,oprs
FROM indent
WHERE oprs IS not NULL;
SELECT cno, fno, odate,oprs
FROM indent;

SELECT *
FROM indent
WHERE oprs LIKE '김%'; SELECT *
FROM customer
WHERE point >= 200
ORDER BY cname; SELECT fno, fname,quantity
FROM fruit
ORDER BY price ASC, quantity DESC; SELECT fno, fname,quantity
FROM fruit
ORDER BY price ASC, quantity ASC; CREATE TABLE customer2
( cno char(10) NOT NULL,
cname char(20) NOT NULL,
city char(20) NOT NULL,
point int NOT NULL,
primary key (cno)
); INSERT INTO customer2 VALUES ('d1', '이율곡', '속초', 700);
INSERT INTO customer2 VALUES ('d2', '이퇴계', '원주', 750);
INSERT INTO customer2 VALUES ('d3', '장영실', '강릉', 800);
INSERT INTO customer2 VALUES ('d4', '안창호', '포항', 600);
INSERT INTO customer2 VALUES ('d5', '한용운', '울산', 930);
INSERT INTO customer2 VALUES ('e1', '김좌진', '서울', 200);
INSERT INTO customer2 VALUES ('e2', '장보고', '서울', 300);

SELECT * FROM customer2;

SELECT c1.cno, c2.*
FROM customer c1 INNER JOIN customer2 c2
ON c1.city = c2.city;

INSERT INTO customer2 VALUES ('e10', '링컨', '미국', 300);
INSERT INTO customer VALUES ('C10', '케네디', '미국', 300);

SELECT c1.cno, c2.*
FROM customer c1 INNER JOIN customer2 c2
ON c1.city = c2.city;

SELECT c10.cno, e10.*
FROM customer c10 INNER JOIN customer2 e10
ON c10.city = e10.city;

SELECT * FROM customer;
SELECT * FROM customer2;

SELECT customer.*, fruit.*
FROM customer, fruit
WHERE customer.city = fruit.city

SELECT C.*, F.*
FROM customer C
INNER JOIN fruit F
ON C.city = F.city;

SELECT indent.*
FROM fruit, indent
WHERE fruit.fno = indent.fno
AND fruit.fname = '사과';

SELECT I.*
FROM fruit as F
INNER JOIN indent as I
ON F.fno = I.fno
AND F.fname = '사과'; SELECT * FROM indent; SELECT I.*, C.cname,f.fname
FROM indent I, fruit F, customer C
WHERE I.fno=F.fno AND I.cno=C.cno AND F.fname= '사과';

SELECT * FROM indent;
SELECT * FROM fruit;
SELECT * FROM customer;

SELECT I.*, C.cname,f.fname
FROM indent I, fruit F, customer C
WHERE I.fno=F.fno AND I.cno=C.cno AND F.fname= '바나나';


SELECT I.*, C.cname
FROM indent I
INNER JOIN fruit F ON I.fno=F.fno
INNER JOIN customer C ON I.cno=C.cno
AND F.fname= '포도';

SELECT c1.cno, c2.*
FROM customer c1
LEFT OUTER JOIN customer2 c2
ON c1.city = c2.city;

SELECT c1.cno, c2.*
FROM customer c1
RIGHT OUTER JOIN customer2 c2
ON c1.city = c2.city;

SELECT c1.cno, c2.*
FROM customer c1
FULL OUTER JOIN customer2 c2
ON c1.city = c2.city;

SELECT c1.cno, c2.*
FROM customer c1
CROSS JOIN customer2 c2;


CREATE TABLE Employee
(EmployeeId INT, Name NVARCHAR(50), ManagerId INT)
GO
INSERT INTO Employee VALUES(1,'을지문덕',1)
INSERT INTO Employee VALUES(2,'광개토왕',1)
INSERT INTO Employee VALUES(3,'세종대왕',2)
INSERT INTO Employee VALUES(4,'박혁거세',2)
GO
SELECT * FROM Employee;
GO

SELECT 
    E.EmployeeId, 
    E.Name AS 'Employee Name', 
    M.Name AS 'Manager Name'
FROM Employee E
INNER JOIN Employee M ON M.EmployeeId = E.ManagerId;

SELECT ASCII(cno), cno FROM customer;

SELECT CHAR(75)

SELECT CHARINDEX('HAVE', 'I HAVE A DREAM')
SELECT LEFT('Seoul Digital Universtity', 5); SELECT * FROM fruit WHERE LEN(fname)=3; SELECT * FROM fruit WHERE LEN(fname)=2; SELECT UPPER('Seoul Digital Universtity'); SELECT LOWER('Seoul Digital Universtity'); SELECT TRIM('              Seoul Digital University            '); SELECT PATINDEX('%지하철%', '서울 지하철 9호선'); SELECT REPLACE('I am a girl', 'girl', 'boy'); SELECT REPLICATE('Software', 3); SELECT REVERSE('소프트웨어공학과'); SELECT 'SDU', SPACE(5), '소프트웨어공학과'; SELECT STR(123.45, 6, 1); SELECT STUFF('서울 인천 부산', 4, 3, '대전 ') SELECT cno, SUBSTRING(cname, 1, 1), city, point
FROM customer;

SELECT ABS(-2.0+(-4.0)), ABS(0.0), ABS(-3.0);

SELECT ACOS(1.0), ASIN(0.5), ATAN(1.0);

SELECT COS(90.0), SIN(90.0), TAN(45.0), COT(90.0);

SELECT CEILING(123.45), CEILING(-123.45);

SELECT DEGREES(PI()/2), RADIANS(1e-32);

SELECT EXP(1), EXP(2), EXP(10);

SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR(123.0);

SELECT LOG(10), LOG10(10);

SELECT PI( ), PI( )*2;

SELECT POWER(2, 2), POWER(2, 4), POWER(2, 8);

SELECT RAND(100), RAND( ), RAND( ); SELECT ROUND(123.95, 2), ROUND(456.945, 2, 1); SELECT SIGN(-100), SIGN(0), SIGN(100); SELECT SQRT(64), SQRT(0), SQRT(100); SELECT SQUARE(1), SQUARE(2), SQUARE(8); -- 날짜함수 SELECT odate, DATEADD(week, 1, odate) FROM indent; SELECT odate, GETDATE(), DATEDIFF(day, odate, GETDATE())
FROM indent;
SELECT odate, GETDATE(), DATENAME(month, odate)
FROM indent; SELECT DATEPART(month,'2025-05-05');
SELECT * FROM indent WHERE MONTH(odate)=4;

SELECT GETDATE( ), GETUTCDATE( ) SELECT COL_LENGTH('customer', 'cname'); SELECT COL_NAME(OBJECT_ID('customer'),2); SELECT COLUMNPROPERTY(OBJECT_ID('customer'), 'city',
'PRECISION'); SELECT COL_NAME(OBJECT_ID('customer'),1); SELECT count(*) FROM customer; SELECT * FROM indent; SELECT * FROM customer; SELECT count(DISTINCT oprs)
FROM indent; SELECT sum(point) FROM customer
WHERE city='서울';

SELECT cno, sum(qty)
FROM indent
GROUP BY cno; SELECT cno, count(*)
FROM indent
GROUP BY cno
HAVING count(*) >= 3;

SELECT cno
FROM customer
WHERE city = ( SELECT city FROM fruit WHERE fno = 'f1' );

SELECT cno, cname
FROM customer
WHERE point >= ( SELECT avg(point) FROM customer ); SELECT cname
FROM customer
WHERE cno IN ( SELECT cno FROM indent WHERE fno = 'f2' ); UPDATE fruit
SET price = 2500,
quantity = quantity - 50
WHERE fno = 'f2';

SELECT * FROM fruit;

UPDATE customer
SET point = point * 200
WHERE city = '인천';

SELECT * FROM customer;

DELETE FROM customer
WHERE cno='c5';

SELECT * FROM customer;

DELETE FROM indent
WHERE odate < '2022-05-01';
SELECT * FROM indent;

DELETE FROM indent; SELECT name FROM sys.columns
WHERE object_id = object_id('Employee'); SELECT object_name(object_id), name
FROM sys.identity_columns; SELECT name
FROM sys.databases;

SELECT name
FROM sys.procedures;

SELECT name
FROM sys.tables;

SELECT name
FROM sys.views;

SELECT name
FROM sys.sql_logins; SELECT *
FROM sys.systypes;


SELECT object_name(object_id) FROM sys.columns
WHERE name = 'cno'; SELECT name FROM sys.columns
WHERE object_id = object_id('fruit'); SELECT name
FROM sys.views;

SELECT count(*) FROM sys.columns
WHERE object_id = object_id('customer');

CREATE VIEW cheap_fruit
(
    fno, fname, quantity, city
)
AS
SELECT fno, fname, quantity, city
FROM fruit
WHERE price <= 1000;

SELECT * FROM cheap_fruit;

CREATE VIEW custby (cno,totqty )
AS SELECT cno, sum(qty)
FROM indent
GROUP BY cno; SELECT * FROM custby;  CREATE VIEW city_pair ( f1city, f2city )
AS SELECT f1.city, f2.city
FROM fruit f1, customer f2
WHERE f1.city = f2.city; SELECT * FROM city_pair ; CREATE VIEW seoul_cheap_fruit
AS SELECT fno, fname
FROM cheap_fruit
WHERE city = '서울'; SELECT * FROM seoul_cheap_fruit; DROP VIEW seoul_cheap_fruit; DROP VIEW city_pair ; CREATE TABLE customer3
( cno char(10) NOT NULL,
cname char(20) NULL,
city char(20) NULL,
point int NULL,
primary key (cno)
);
INSERT INTO customer3 VALUES ('c1', '이순신', '서울', 200);
INSERT INTO customer3 VALUES ('c2', '김유신', '인천', 150);
INSERT INTO customer3 VALUES ('c3', '홍길동', '인천', 300);
INSERT INTO customer3 VALUES ('c4', '강감찬', '서울', 200);
INSERT INTO customer3 VALUES ('c5', '안중근', '대전', 230);

CREATE VIEW cno_city
AS SELECT cno, city FROM customer3; INSERT INTO cno_city
VALUES ('c6', '부산'); SELECT * FROM customer3; DELETE FROM cno_city
WHERE cno='c5';

CREATE VIEW point_city
AS SELECT point, city FROM customer; INSERT INTO point_city
VALUES ( 400, '광주'); -- 오류 DELETE FROM point_city
WHERE point=200 AND city='서울'; UPDATE point_city
SET point = 400, city='부산'
WHERE point=200 AND city='서울';