Store database, DDL, and DML

StoreDB, Customers/Products/Orders/OrderItems, DDL (CREATE/ALTER/DROP), DML (INSERT/UPDATE/DELETE), and SELECT.

Beginner 42 min read SQLSQL ServerDDL

Lesson 6 / 7 0%

Store database and DDL/DML

Level: beginner · Reading time: about 35 to 45 minutes

Topics: StoreDB, customers, products, orders, order items, DDL, DML, CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT

What will we learn?

design a store database
DDL: CREATE, ALTER, DROP
DML: INSERT, UPDATE, DELETE
SELECT and WHERE
foreign keys and OrderItems for many-to-many

1. Store scenario

Customers register, products are defined, orders are placed. Entities: Customer, Product, Order — we name the table Orders because ORDER is reserved.

Initial tables: Customers, Products, Orders.

2. Customer entity

CustomerId	FirstName	LastName	Phone
1	Ali	Ahmadi	09121234567
2	Sara	Rezaei	09129876543

3. Product entity

ProductId	ProductName	Price	Stock
1	Laptop	50000000	5
2	Mouse	800000	20
3	Keyboard	1500000	10

4. Orders entity

OrderId	CustomerId	OrderDate	TotalAmount
1001	1	2026-08-15	1500000
1002	2	2026-08-15	50000000

5. Customer–Order relationship

One customer, many orders; each order one customer. Customer 1 ---- N Orders — one-to-many.

6. CustomerId role in Orders

In Customers: CustomerId is the primary key. In Orders: CustomerId is a foreign key pointing to Customers.CustomerId.

7. Simple ER diagram

Customers (CustomerId PK) — 1:N — Orders (OrderId PK, CustomerId FK). Products separate with ProductId PK.

text

Customers 1 ---- N Orders

Products (standalone for now)

8. Create StoreDB

sql

CREATE DATABASE StoreDB;

USE StoreDB;

9. SQL command groups

Two important groups this session: DDL and DML.

10. What is DDL?

Data Definition Language — defines structure: databases, tables, alter, drop.

11. DDL commands

CREATE · ALTER · DROP

12. CREATE

Create a new object — database or table.

sql

CREATE DATABASE StoreDB;

CREATE TABLE Customers
(
    CustomerId INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Phone VARCHAR(11)
);

13. Create Customers table

sql

CREATE TABLE Customers
(
    CustomerId INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Phone VARCHAR(11)
);

14. Why not INT for phone?

Phone numbers are not for math; they may start with zero. Use VARCHAR(11).

15. Create Products table

sql

CREATE TABLE Products
(
    ProductId INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(18,2),
    Stock INT
);

16. CHECK on price and stock

sql

CREATE TABLE Products
(
    ProductId INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(18,2) CHECK (Price >= 0),
    Stock INT CHECK (Stock >= 0)
);

17. Create Orders table

Without a foreign key the relationship is not enforced yet.

sql

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    OrderDate DATE,
    TotalAmount DECIMAL(18,2)
);

18. Define foreign key

sql

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    OrderDate DATE,
    TotalAmount DECIMAL(18,2),

    FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
);

19. Why foreign keys matter

If customer 500 does not exist, the foreign key blocks an invalid order.

20. ALTER

Change an existing object — e.g. add an Email column.

sql

ALTER TABLE Customers
ADD Email VARCHAR(100);

21. ALTER example — Address

sql

ALTER TABLE Customers
ADD Address NVARCHAR(250);

22. DROP

Remove an object entirely — use with care.

sql

DROP TABLE Products;

23. DROP DATABASE

Drop the entire database — very sensitive.

sql

DROP DATABASE StoreDB;

24. DDL summary

CREATE = build · ALTER = change structure · DROP = remove structure

25. What is DML?

Data Manipulation Language — manipulate data inside tables, not structure.

26. DML commands

INSERT · UPDATE · DELETE — and SELECT to read data.

27. INSERT

sql

INSERT INTO Customers
(
    CustomerId,
    FirstName,
    LastName,
    Phone
)
VALUES
(
    1,
    N'Ali',
    N'Ahmadi',
    '09121234567'
);

28. The N prefix for Unicode

N'Ali' — Unicode literal for NVARCHAR. Use N for Persian text.

29. Insert second customer

Listing column names is safer than VALUES without a column list.

sql

INSERT INTO Customers
VALUES
(2, N'Sara', N'Rezaei', '09129876543');

30. Insert product

sql

INSERT INTO Products
(ProductId, ProductName, Price, Stock)
VALUES
(1, N'Laptop', 50000000, 5);

31. Insert more products

sql

INSERT INTO Products VALUES (2, N'Mouse', 800000, 20);
INSERT INTO Products VALUES (3, N'Keyboard', 1500000, 10);

32. Insert order

sql

INSERT INTO Orders
(OrderId, CustomerId, OrderDate, TotalAmount)
VALUES
(1001, 1, '2026-08-15', 1500000);

33. UPDATE

sql

UPDATE Customers
SET Phone = '09120000000'
WHERE CustomerId = 1;

34. WHERE in UPDATE

UPDATE Customers SET Phone = ... without WHERE changes every customer's phone!

35. UPDATE price

sql

UPDATE Products
SET Price = 900000
WHERE ProductId = 2;

36. Decrease stock

sql

UPDATE Products
SET Stock = Stock - 1
WHERE ProductId = 2;

37. DELETE

sql

DELETE FROM Customers
WHERE CustomerId = 2;

38. DELETE without WHERE

DELETE FROM Customers; — removes all rows; table remains.

39. DELETE vs DROP

DELETE → rows · DROP → the table itself

40. SELECT

sql

SELECT *
FROM Customers;

41. Specific columns

sql

SELECT FirstName, LastName
FROM Customers;

42. WHERE in SELECT

sql

SELECT *
FROM Customers
WHERE CustomerId = 1;

43. Filter by price

sql

SELECT *
FROM Products
WHERE Price > 1000000;

44. DDL vs DML summary

DDL	DML
Structure	Data
CREATE, ALTER, DROP	INSERT, UPDATE, DELETE

45. Full CREATE example

StoreDB + Customers + Products + Orders with FK.

sql

CREATE DATABASE StoreDB;
USE StoreDB;

CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Phone VARCHAR(11)
);

CREATE TABLE Products (
    ProductId INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(18,2) CHECK (Price >= 0),
    Stock INT CHECK (Stock >= 0)
);

CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    OrderDate DATE,
    TotalAmount DECIMAL(18,2),
    FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);

46. Seed data

INSERT customers, products, and an order.

sql

INSERT INTO Customers VALUES (1, N'Ali', N'Ahmadi', '09121234567');
INSERT INTO Customers VALUES (2, N'Sara', N'Rezaei', '09129876543');

INSERT INTO Products VALUES (1, N'Laptop', 50000000, 5);
INSERT INTO Products VALUES (2, N'Mouse', 800000, 20);
INSERT INTO Products VALUES (3, N'Keyboard', 1500000, 10);

INSERT INTO Orders VALUES (1001, 1, '2026-08-15', 1500000);

47. Design gap

Orders says who ordered — not what products. Order 1001 might be 2 mice and 1 keyboard.

48. Order–Product relationship

Orders N ---- N Products — many-to-many.

49. Bridge entity

Junction table: OrderItems or OrderDetails.

50. Create OrderItems

sql

CREATE TABLE OrderItems
(
    OrderId INT,
    ProductId INT,
    Quantity INT CHECK (Quantity > 0),

    FOREIGN KEY (OrderId) REFERENCES Orders(OrderId),
    FOREIGN KEY (ProductId) REFERENCES Products(ProductId)
);

51. Final structure

Customers 1→N Orders 1→N OrderItems N→1 Products

text

Customer → Order → OrderItem → Product

52. Insert order line items

Order 1001: 2 mice (product 2) + 1 keyboard (product 3)

sql

INSERT INTO OrderItems VALUES (1001, 2, 2);
INSERT INTO OrderItems VALUES (1001, 3, 1);

53. What the database knows now

Customer Ali → order 1001 → product 2 ×2 and product 3 ×1. Full order chain recorded.

Session summary

Tables: Customers, Products, Orders, OrderItems. DDL: CREATE, ALTER, DROP. DML: INSERT, UPDATE, DELETE, SELECT. Customer 1:N orders, orders N:N products via OrderItems.

Session 6 exercises

Work these after the reading.