MSSQL, 주요 쿼리 정리

-- 1. 데이터베이스 관리
-- 모든 데이터베이스 목록 조회

SELECT name FROM sys.databases;

-- 새 데이터베이스 생성

CREATE DATABASE CompanyDB;

-- 특정 데이터베이스 사용

USE CompanyDB;

-- 데이터베이스 삭제

DROP DATABASE CompanyDB;

-- 2. 테이블 관리
-- 테이블 목록 조회

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

-- 테이블 생성 (직원 관리)

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(255) UNIQUE NOT NULL,
    Department NVARCHAR(50),
    Salary DECIMAL(10,2),
    HireDate DATETIME DEFAULT GETDATE()
);

-- 테이블 구조 확인

EXEC sp_help 'Employees';

-- 테이블 삭제

DROP TABLE Employees;

-- 3. 데이터 조작 (CRUD)
-- 데이터 삽입

INSERT INTO Employees (Name, Email, Department, Salary) 
VALUES ('Alice Johnson', 'alice@example.com', 'HR', 60000);

-- 모든 직원 조회

SELECT * FROM Employees;

-- 특정 직원 정보 조회

SELECT * FROM Employees WHERE Email = 'alice@example.com';

-- 직원 급여 수정

UPDATE Employees SET Salary = 65000 WHERE EmployeeID = 1;

-- 직원 삭제

DELETE FROM Employees WHERE EmployeeID = 1;

-- 테이블 전체 데이터 삭제

TRUNCATE TABLE Employees;

-- 4. JOIN & 집계 함수
-- INNER JOIN (직원과 부서 연결)

SELECT e.Name, e.Email, d.DepartmentName 
FROM Employees e
INNER JOIN Departments d ON e.Department = d.DepartmentID;

-- LEFT JOIN (모든 직원 + 부서 정보)

SELECT e.Name, e.Email, d.DepartmentName 
FROM Employees e
LEFT JOIN Departments d ON e.Department = d.DepartmentID;

-- 각 부서별 평균 급여 계산

SELECT Department, AVG(Salary) AS AvgSalary 
FROM Employees 
GROUP BY Department;

-- 직원 수가 5명 이상인 부서 조회

SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) >= 5;

-- 5. 인덱스 & 성능 튜닝
-- 이메일 컬럼에 인덱스 추가

CREATE INDEX idx_employees_email ON Employees(Email);

-- 인덱스 삭제

DROP INDEX idx_employees_email ON Employees;

-- 실행 계획 확인

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Employees WHERE Email = 'alice@example.com';

-- 6. 트랜잭션 & 동시성 제어
-- 트랜잭션 시작 후 롤백

BEGIN TRANSACTION;
UPDATE Employees SET Salary = 70000 WHERE EmployeeID = 2;
ROLLBACK TRANSACTION; -- 변경사항 취소

-- 트랜잭션 커밋

BEGIN TRANSACTION;
UPDATE Employees SET Salary = 75000 WHERE EmployeeID = 2;
COMMIT TRANSACTION; -- 변경사항 저장

-- 7. 사용자 & 권한 관리
-- 새 사용자 생성

CREATE LOGIN db_user WITH PASSWORD = 'StrongPassword123!';
CREATE USER db_user FOR LOGIN db_user;

-- 특정 테이블에 권한 부여

GRANT SELECT, INSERT, UPDATE, DELETE ON Employees TO db_user;

-- 사용자 삭제

DROP USER db_user;
DROP LOGIN db_user;

-- 8. 백업 & 복구
-- 전체 데이터베이스 백업

BACKUP DATABASE CompanyDB
TO DISK = 'C:\backups\CompanyDB.bak'
WITH FORMAT, INIT, NAME = 'Full Backup';

-- 데이터베이스 복구

RESTORE DATABASE CompanyDB
FROM DISK = 'C:\backups\CompanyDB.bak'
WITH REPLACE;

-- 9. 그 외 다양한 쿼리
-- 현재 실행 중인 쿼리 확인

SELECT * FROM sys.dm_exec_requests;

-- 데이터베이스 크기 확인

EXEC sp_spaceused;

-- 특정 테이블의 레코드 개수 확인

SELECT COUNT(*) FROM Employees;

-- JSON 데이터 처리 (MSSQL 2016+ 지원)

SELECT Name, Email, 
       JSON_VALUE(EmployeeData, '$.Position') AS Position 
FROM Employees WHERE EmployeeData IS NOT NULL;

-- 데이터 피벗 (부서별 직원 수 카운트)

SELECT * FROM (
    SELECT Department, COUNT(EmployeeID) AS EmployeeCount
    FROM Employees
    GROUP BY Department
) AS SourceTable
PIVOT (
    SUM(EmployeeCount) FOR Department IN ([HR], [IT], [Finance], [Marketing])
) AS PivotTable;

-- 동적 SQL 실행

DECLARE @SQLQuery NVARCHAR(MAX);
SET @SQLQuery = 'SELECT * FROM Employees WHERE Salary > 50000';
EXEC sp_executesql @SQLQuery;