Azure SQL Database でテーブルを作成する方法

-- Using SQL Server Management Studio (SSMS)
-- Replace <server_name>, <database_name>, <username>, and <password> with your details

Server name: <server_name>.database.windows.net
Database name: <database_name>
Authentication: SQL Server Authentication
Login: <username>
Password: <password>

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Department NVARCHAR(50)
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100) NOT NULL UNIQUE,
    CreatedDate DATETIME DEFAULT GETDATE()
);

CREATE TABLE EmployeeDetails (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100) NOT NULL,
    DepartmentID INT,
    CONSTRAINT FK_Department FOREIGN KEY (DepartmentID)
        REFERENCES Departments(DepartmentID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Insert data into Departments table
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'HR'), (2, 'Engineering');

-- Insert data into EmployeeDetails table
INSERT INTO EmployeeDetails (EmployeeID, EmployeeName, DepartmentID)
VALUES (101, 'John Doe', 1), (102, 'Jane Smith', 2);

SELECT 
    e.EmployeeID,
    e.EmployeeName,
    d.DepartmentName
FROM 
    EmployeeDetails e
INNER JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID;

-- Add a new column to the EmployeeDetails table
ALTER TABLE EmployeeDetails
ADD Email NVARCHAR(255);

-- Set a default value for the new column
ALTER TABLE EmployeeDetails
ADD CONSTRAINT DF_Email DEFAULT 'example@example.com' FOR Email;

DROP TABLE EmployeeDetails;