Full Code on SQL
This section provides a comprehensive set of SQL code examples covering Data
Definition Language (DDL), Data Manipulation Language (DML), and Data Control
Language (DCL).
Data Definition Language (DDL)
DDL statements are used to define, modify, and delete database objects such as tables,
indexes, and users.
CREATE TABLE
Used to create a new table in the database.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY IDENTITY(1,1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
Phone VARCHAR(20),
Address VARCHAR(255),
City VARCHAR(50),
State VARCHAR(50),
ZipCode VARCHAR(10)
);
CREATE TABLE Products (
ProductID INT PRIMARY KEY IDENTITY(1,1),
ProductName VARCHAR(100) NOT NULL,
Description TEXT,
Price DECIMAL(10, 2) NOT NULL,
StockQuantity INT NOT NULL DEFAULT 0
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY IDENTITY(1,1),
CustomerID INT NOT NULL,
OrderDate DATETIME DEFAULT GETDATE(),
TotalAmount DECIMAL(10, 2) NOT NULL,
OrderStatus VARCHAR(50) DEFAULT 'Pending',
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
CREATE TABLE OrderItems (
OrderItemID INT PRIMARY KEY IDENTITY(1,1),
OrderID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
UnitPrice DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
ALTER TABLE
Used to add, modify, or drop columns in an existing table, or to add/drop constraints.
-- Add a new column
ALTER TABLE Customers
ADD DateOfBirth DATE;
-- Modify an existing column
ALTER TABLE Products
ALTER COLUMN Description VARCHAR(500);
-- Drop a column
ALTER TABLE Customers
DROP COLUMN ZipCode;
-- Add a CHECK constraint
ALTER TABLE Products
ADD CONSTRAINT CHK_StockQuantity CHECK (StockQuantity >= 0);
DROP TABLE
Used to delete an existing table from the database.
DROP TABLE OrderItems;
DROP TABLE Orders;
DROP TABLE Products;
DROP TABLE Customers;
CREATE INDEX
Used to create indexes on tables to speed up data retrieval.
CREATE INDEX IX_Customers_LastName ON Customers (LastName);
CREATE INDEX IX_Products_ProductName ON Products (ProductName);
Data Manipulation Language (DML)
DML statements are used for managing data within schema objects.
INSERT INTO
Used to add new rows of data into a table.
-- Inserting into Customers
INSERT INTO Customers (FirstName, LastName, Email, Phone, Address, City,
State)
VALUES (
'Alice', 'Smith', '[Link]@[Link]', '555-1234',
'123 Main St', 'Anytown', 'CA'
);
INSERT INTO Customers (FirstName, LastName, Email, Phone, Address, City,
State)
VALUES (
'Bob', 'Johnson', 'bob.j@[Link]', '555-5678',
'456 Oak Ave', 'Otherville', 'NY'
);
-- Inserting into Products
INSERT INTO Products (ProductName, Description, Price, StockQuantity)
VALUES ('Laptop', 'High-performance laptop', 1200.00, 50);
INSERT INTO Products (ProductName, Description, Price, StockQuantity)
VALUES ('Mouse', 'Wireless optical mouse', 25.00, 200);
-- Inserting into Orders (assuming CustomerID 1 and 2 exist)
INSERT INTO Orders (CustomerID, TotalAmount, OrderStatus)
VALUES (1, 1225.00, 'Completed');
INSERT INTO Orders (CustomerID, TotalAmount, OrderStatus)
VALUES (2, 25.00, 'Pending');
-- Inserting into OrderItems (assuming OrderID 1 and ProductID 1, 2 exist)
INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
VALUES (1, 1, 1, 1200.00);
INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
VALUES (1, 2, 1, 25.00);
INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
VALUES (2, 2, 1, 25.00);
SELECT
Used to retrieve data from one or more tables.
-- Select all columns from Customers
SELECT * FROM Customers;
-- Select specific columns from Products
SELECT ProductName, Price FROM Products;
-- Select with WHERE clause
SELECT * FROM Customers WHERE City = 'Anytown';
-- Select with ORDER BY clause
SELECT ProductName, Price FROM Products ORDER BY Price DESC;
-- Select with JOIN
SELECT
[Link], [Link],
[Link], [Link],
[Link], [Link],
[Link]
FROM Customers c
JOIN Orders o ON [Link] = [Link]
JOIN OrderItems oi ON [Link] = [Link]
JOIN Products p ON [Link] = [Link]
WHERE [Link] = 'Alice';
-- Aggregate functions with GROUP BY and HAVING
SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders, SUM(TotalAmount) AS
TotalSpent
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 1;
UPDATE
Used to modify existing data in a table.
-- Update a single customer's email
UPDATE Customers
SET Email = '[Link]@[Link]'
WHERE CustomerID = 1;
-- Update product stock quantity
UPDATE Products
SET StockQuantity = StockQuantity - 1
WHERE ProductID = 2;
DELETE FROM
Used to delete existing rows from a table.
-- Delete an order item
DELETE FROM OrderItems
WHERE OrderItemID = 3;
-- Delete a customer (will fail if there are related orders due to foreign
key constraint)
-- DELETE FROM Customers WHERE CustomerID = 2;
Data Control Language (DCL)
DCL statements are used to control access to data and the database.
GRANT
Used to give users access privileges to the database.
-- Grant SELECT permission on the Customers table to a user named
'ReportUser'
GRANT SELECT ON Customers TO ReportUser;
-- Grant INSERT, UPDATE, DELETE permissions on the Products table to a user
named 'AdminUser'
GRANT INSERT, UPDATE, DELETE ON Products TO AdminUser;
REVOKE
Used to remove user access privileges.
-- Revoke SELECT permission on the Customers table from 'ReportUser'
REVOKE SELECT ON Customers FROM ReportUser;
References
[5] SQLShack. (2019, October 25). SQL WHILE loop with simple examples.
[Link]
[6] Microsoft Learn. (2025, November 18). WHILE (Transact-SQL) - SQL Server.
[Link]
sql?view=sql-server-ver17
[7] Codecademy. (2025, January 12). SQL | Loops.
[Link]
[8] GeeksforGeeks. (2025, July 23). PL/SQL Tutorial.
[Link]
[9] Tutorialspoint. PL/SQL Tutorial.
[Link]
[10] Oracle. Sample PL/SQL Programs.
[Link]
[11] Microsoft Learn. (2025, July 16). Open SQL Database By Using VB .NET.
[Link]
us/troubleshoot/developer/dotnet/framework/general/open-database-by-sql-
server-dotnet-data-provider
[12] Microsoft Learn. (2024, September 7). [Link] code examples.
[Link]
code-examples
[13] C# Corner. Connecting Databases Using [Link] In [Link]. [Link]
[Link]/UploadFile/abb1a5/connecting-database-using-ado-net-in-VB-
Net/
[14] Websofttuts. (2018, July 5). CRUD in [Link] and SQL Server.
[Link]