SQL の面接質問

Normalization organizes a database to reduce redundancy and improve data integrity through a series of normal forms.

Example – unnormalized → 3NF:

-- 3NF: no transitive dependencies
CREATE TABLE Departments (
    DeptID   INT          PRIMARY KEY,
    DeptName NVARCHAR(100) NOT NULL
);

CREATE TABLE Employees_3NF (
    EmpID   INT          PRIMARY KEY,
    EmpName NVARCHAR(100) NOT NULL,
    DeptID  INT          NOT NULL REFERENCES Departments(DeptID)
);

↥ back to top

Data independence is the ability to change the schema at one level without affecting the schema at the next higher level.

• Physical data independence – move/resize data files without changing the logical schema.
• Logical data independence – add columns to a table without breaking views or applications.

Example:

-- Add a column without breaking the existing view
ALTER TABLE Employees ADD Department NVARCHAR(50) NULL;

SELECT * FROM vw_ActiveEmployees;  -- still works unchanged

↥ back to top

A cursor allows row-by-row processing of a result set. Always prefer set-based operations — cursors are significantly slower.

DECLARE @EmpID INT, @Salary DECIMAL(12,2);

DECLARE emp_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT EmployeeID, Salary FROM Employees WHERE IsActive = 1;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @EmpID, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Salary < 50000
        UPDATE Employees SET Salary = 50000 WHERE EmployeeID = @EmpID;

    FETCH NEXT FROM emp_cursor INTO @EmpID, @Salary;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Equivalent set-based rewrite (preferred):

UPDATE Employees SET Salary = 50000 WHERE IsActive = 1 AND Salary < 50000;

↥ back to top

# 22. SQL Stored Procedures

JOIN without a qualifier is INNER JOIN.

-- Identical queries:
SELECT c.Name, o.Amount FROM Customers c JOIN       Orders o ON o.CustomerID = c.CustomerID;
SELECT c.Name, o.Amount FROM Customers c INNER JOIN Orders o ON o.CustomerID = c.CustomerID;

↥ back to top

-- Find top expensive queries in plan cache
SELECT TOP 10
       qs.execution_count,
       qs.total_logical_reads / qs.execution_count AS avg_reads,
       qs.total_elapsed_time  / qs.execution_count AS avg_us,
       SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
           ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
             ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER  BY avg_reads DESC;

↥ back to top

Prepared Statements reduce the risk of SQL injection by separating SQL data and commands. They also optimize query execution by allowing repetitive parameter bindings.

Key Advantages

• Security: They defend against SQL injection by distinguishing between SQL code and input values.
• Performance: Prepared statements can be faster when used repeatedly, as they are parsed and executed in discrete steps.
• Readability and Maintainability: Separating the SQL code from the parameters makes it more readable. It can make the code easier to understand, review, and maintain.

When to Use Prepared Statements

• Input from Untrusted Sources: When SQL queries are constructed with data from untrusted sources, prepared statements ensure the data is treated as literal values, preventing SQL injection.

• Repeated Executions: For queries executed multiple times, using a prepared statement can be more efficient than constructing and executing a new query each time. This is especially relevant in loops or high-volume operations.

TRUNCATE TABLE Logs;      -- table exists, empty
DROP TABLE IF EXISTS Logs; -- table no longer exists

↥ back to top

A database is a systematic or organized collection of related information stored in such a way that it can be easily accessed, retrieved, managed, and updated.

In SQL Server 2022, you create a database using CREATE DATABASE:

Syntax:

CREATE DATABASE database_name
[ ON PRIMARY (
    NAME = logical_name,
    FILENAME = 'path\file.mdf',
    SIZE = size,
    MAXSIZE = max_size,
    FILEGROWTH = growth_increment
  )
]
[ LOG ON (
    NAME = log_logical_name,
    FILENAME = 'path\file.ldf'
  )
];

Example:

CREATE DATABASE SalesDB
ON PRIMARY (
    NAME = SalesDB_Data,
    FILENAME = 'C:\SQLData\SalesDB.mdf',
    SIZE = 100MB,
    MAXSIZE = 1GB,
    FILEGROWTH = 10MB
)
LOG ON (
    NAME = SalesDB_Log,
    FILENAME = 'C:\SQLData\SalesDB_log.ldf',
    SIZE = 20MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 5MB
);

USE SalesDB;
GO

↥ back to top

Example:

CREATE TABLE Products (
    ProductID   INT           IDENTITY PRIMARY KEY,
    SKU         CHAR(8)       NOT NULL,
    Name        NVARCHAR(200) NOT NULL,
    Description NVARCHAR(MAX) NULL,
    ImageData   VARBINARY(MAX) NULL
);

INSERT INTO Products (SKU, Name, Description)
VALUES ('SKU-0001', N'Laptop Pro', N'High-performance laptop');

SELECT SKU, Name FROM Products;

↥ back to top

# 3. SQL Database

LAG() accesses a previous row\'s value; LEAD() accesses a next row\'s value — both without a self-join.

-- Syntax: LAG(column, offset, default) OVER (PARTITION BY ... ORDER BY ...)

-- Month-over-month sales comparison
SELECT
    SaleMonth,
    TotalSales,
    LAG(TotalSales,  1, 0) OVER (ORDER BY SaleMonth) AS PrevMonthSales,
    LEAD(TotalSales, 1, 0) OVER (ORDER BY SaleMonth) AS NextMonthSales,
    TotalSales - LAG(TotalSales, 1, 0) OVER (ORDER BY SaleMonth) AS MoM_Change
FROM (
    SELECT FORMAT(OrderDate, 'yyyy-MM') AS SaleMonth,
           SUM(Amount) AS TotalSales
    FROM   Orders
    GROUP  BY FORMAT(OrderDate, 'yyyy-MM')
) m
ORDER  BY SaleMonth;

-- Partition by region: compare to previous month within each region
SELECT Region, SaleMonth, TotalSales,
       LAG(TotalSales) OVER (PARTITION BY Region ORDER BY SaleMonth) AS PrevSales,
       TotalSales - LAG(TotalSales) OVER (PARTITION BY Region ORDER BY SaleMonth) AS Diff
FROM (
    SELECT Region, FORMAT(OrderDate, 'yyyy-MM') AS SaleMonth,
           SUM(Amount) AS TotalSales
    FROM   Orders
    GROUP  BY Region, FORMAT(OrderDate, 'yyyy-MM')
) t;

-- Detect gaps in sequential IDs
WITH Gaps AS (
    SELECT OrderID,
           OrderID - LAG(OrderID) OVER (ORDER BY OrderID) AS GapSize
    FROM   Orders
)
SELECT * FROM Gaps WHERE GapSize > 1;

↥ back to top

A database table is a structure that organizes data into rows and columns. Each row represents a record and each column represents a field (attribute).

Example:

CREATE TABLE Employees (
    EmployeeID   INT           IDENTITY(1,1) PRIMARY KEY,
    FirstName    NVARCHAR(50)  NOT NULL,
    LastName     NVARCHAR(50)  NOT NULL,
    HireDate     DATE          NOT NULL DEFAULT CAST(GETDATE() AS DATE),
    Salary       DECIMAL(10,2) NULL
);

SELECT * FROM Employees;

↥ back to top

A FOREIGN KEY enforces referential integrity: every FK value must exist as a PK/UNIQUE value in the referenced table, or be NULL.

CREATE TABLE Orders (
    OrderID    INT  IDENTITY PRIMARY KEY,
    CustomerID INT  NOT NULL
        CONSTRAINT fk_Order_Customer FOREIGN KEY REFERENCES Customers(CustomerID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    OrderDate  DATE NOT NULL DEFAULT CAST(GETDATE() AS DATE)
);

↥ back to top

> TEXT and NTEXT are deprecated in SQL Server 2022. Use VARCHAR(MAX) / NVARCHAR(MAX).

Example:

CREATE TABLE Documents (
    DocID      INT           IDENTITY PRIMARY KEY,
    Title      NVARCHAR(200) NOT NULL,
    Body       NVARCHAR(MAX) NOT NULL,
    Attachment VARBINARY(MAX) NULL
);

↥ back to top

# 2. SQL Data Types

-- List all triggers
SELECT name, type_desc, parent_class_desc, is_disabled
FROM   sys.triggers ORDER BY name;

↥ back to top

CREATE TABLE Departments (DeptID INT PRIMARY KEY, DeptName NVARCHAR(100) NOT NULL);

CREATE TABLE Employees (
    EmployeeID INT  IDENTITY PRIMARY KEY,
    FirstName  NVARCHAR(50) NOT NULL,
    DeptID     INT  NULL,
    CONSTRAINT fk_Emp_Dept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
        ON DELETE SET NULL   -- if dept deleted → employee DeptID becomes NULL
        ON UPDATE CASCADE    -- if dept PK changes → employee FK updated
);

-- Test ON DELETE SET NULL
DELETE FROM Departments WHERE DeptID = 3;
SELECT * FROM Employees WHERE DeptID IS NULL;  -- affected employees

-- Disable / re-enable a FK constraint
ALTER TABLE Employees NOCHECK CONSTRAINT fk_Emp_Dept;  -- disable
ALTER TABLE Employees CHECK   CONSTRAINT fk_Emp_Dept;  -- re-enable

↥ back to top

# 12. SQL Join

An index is a data structure that speeds up data retrieval at the cost of additional storage and slower writes.

-- Create non-clustered index
CREATE INDEX ix_Employees_LastName ON Employees (LastName ASC);

-- Composite index
CREATE INDEX ix_Orders_CustomerDate ON Orders (CustomerID, OrderDate DESC);

-- Unique index
CREATE UNIQUE INDEX uix_Users_Email ON Users (Email);

-- Filtered index (index only active employees)
CREATE INDEX ix_ActiveEmployees ON Employees (DeptID, Salary) WHERE IsActive = 1;

-- Drop index
DROP INDEX ix_Employees_LastName ON Employees;

-- List indexes
SELECT i.name, i.type_desc, i.is_unique
FROM   sys.indexes i
WHERE  i.object_id = OBJECT_ID('Employees') AND i.type > 0;

↥ back to top

A UNIQUE constraint ensures all values in a column are distinct. Unlike PK, a UNIQUE column can contain one NULL.

CREATE TABLE Users (
    UserID   INT           IDENTITY PRIMARY KEY,
    Username NVARCHAR(50)  NOT NULL CONSTRAINT uq_Username UNIQUE,
    Email    NVARCHAR(150) NOT NULL CONSTRAINT uq_Email    UNIQUE
);

-- Composite unique key
ALTER TABLE Products ADD CONSTRAINT uq_SupplierSKU UNIQUE (SupplierID, SKU);

↥ back to top

A candidate key is any column(s) that could serve as the primary key — unique and NOT NULL. One is chosen as the PK; others are enforced as UNIQUE NOT NULL.

CREATE TABLE Employees (
    EmployeeID INT           IDENTITY PRIMARY KEY,  -- chosen PK
    Email      NVARCHAR(150) NOT NULL UNIQUE,        -- candidate key 1
    SSN        CHAR(11)      NOT NULL UNIQUE,        -- candidate key 2
    FirstName  NVARCHAR(50)  NOT NULL
);

↥ back to top

-- Monitor current locks
SELECT request_session_id, resource_type, resource_description, request_mode
FROM   sys.dm_tran_locks
WHERE  request_session_id > 50;

↥ back to top

• Aggregate: operates on multiple rows, returns one result per group.
• Scalar: operates on one value per row.

-- Scalar functions
SELECT
    LEN(N'Hello World')                 AS Len,          -- 11
    UPPER(N'hello')                     AS Upper,        -- HELLO
    TRIM(N'  spaces  ')                 AS Trimmed,      -- spaces
    REPLACE(N'foo bar', N'bar', N'baz') AS Replaced,     -- foo baz
    CONCAT(N'SQL', N' ', N'Server')     AS Concat,       -- SQL Server
    ROUND(3.14159, 2)                   AS Rounded,      -- 3.14
    ABS(-42)                            AS AbsVal,       -- 42
    ISNULL(NULL, N'default')            AS NullCheck,    -- default
    COALESCE(NULL, NULL, N'first')      AS Coalesced;    -- first

↥ back to top