Microsoft SQL Server

Chapter 5 - DDL (Data Definition Language)

CREATE TABLE

In Microsoft SQL Server, the CREATE TABLE statement is used to define a new table and its structure in a database. Each table consists of columns with specified data types, and you can also set constraints to enforce rules on the data. Below are some examples and explanations to help you understand how to create tables for different entities.

Key Concepts

1. Creating a Table

You define a table by specifying its name and the columns it will contain.
Each column is defined with a name, a data type, and optional constraints.

2. Data Types

SQL Server supports various data types such as INT, VARCHAR, DATETIME, etc.
Choosing the appropriate data type is crucial for storing data efficiently.

3. Constraints

Constraints are rules applied to columns to ensure data integrity.
Common constraints include PRIMARY KEY, FOREIGN KEY, NOT NULL, and UNIQUE.

Code Samples

1. Store Table

This table will store information about different stores.

CREATE TABLE Store (
    StoreID INT PRIMARY KEY IDENTITY(1,1), -- Auto-incremented primary key
    StoreName VARCHAR(100) NOT NULL,       -- Store name, cannot be NULL
    Location VARCHAR(100) NOT NULL,        -- Store location, cannot be NULL
    OpenDate DATETIME                      -- Date when the store opened
);

2. Products Table

This table stores product details available in each store.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1), -- Auto-incremented primary key
    ProductName VARCHAR(100) NOT NULL,       -- Product name, cannot be NULL
    Category VARCHAR(50),                    -- Product category
    Price DECIMAL(10,2) NOT NULL,            -- Product price with two decimal places
    StockQuantity INT NOT NULL               -- Number of products in stock
);

3. Customer Table

This table stores customer information.

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY IDENTITY(1,1), -- Auto-incremented primary key
    FirstName VARCHAR(50) NOT NULL,       -- Customer's first name, cannot be NULL
    LastName VARCHAR(50) NOT NULL,        -- Customer's last name, cannot be NULL
    Email VARCHAR(100) UNIQUE,            -- Customer's email, must be unique
    Phone VARCHAR(15)                     -- Customer's phone number
);

4. Sales Table

This table records sales transactions.

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY IDENTITY(1,1),   -- Auto-incremented primary key
    SaleDate DATETIME NOT NULL,            -- Date of the sale, cannot be NULL
    StoreID INT FOREIGN KEY REFERENCES Store(StoreID), -- Foreign key to Store table
    ProductID INT FOREIGN KEY REFERENCES Products(ProductID), -- Foreign key to Products table
    CustomerID INT FOREIGN KEY REFERENCES Customer(CustomerID), -- Foreign key to Customer table
    Quantity INT NOT NULL,                   -- Quantity of product sold
    TotalAmount DECIMAL(10,2) NOT NULL   -- Total amount for the sale
);

Combined Code Sample

Here is all the code combined for easier reference:

CREATE TABLE Store (
    StoreID INT PRIMARY KEY IDENTITY(1,1),
    StoreName VARCHAR(100) NOT NULL,
    Location VARCHAR(100) NOT NULL,
    OpenDate DATETIME
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(100) NOT NULL,
    Category VARCHAR(50),
    Price DECIMAL(10,2) NOT NULL,
    StockQuantity INT NOT NULL
);

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    PhoneNumber VARCHAR(15)
);

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY IDENTITY(1,1),
    SaleDate DATETIME NOT NULL,
    StoreID INT FOREIGN KEY REFERENCES Store(StoreID),
    ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
    CustomerID INT FOREIGN KEY REFERENCES Customer(CustomerID),
    Quantity INT NOT NULL,
    TotalAmount DECIMAL(10,2) NOT NULL
);

Feel free to adapt these table definitions to fit your specific requirements!

Tansy MSSQL Course | CREATE TABLE | Chapter 5 | Lesson 1 - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found