SQL Commands

This section provides a comprehensive guide to common SQL commands used for querying and manipulating data in SQL Server. Understanding these commands is fundamental for effective database management and development.

Data Query Language (DQL)

Used for retrieving data from the database.

SELECT Statement

The SELECT statement is used to fetch data from one or more tables. It's the most frequently used SQL command.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT CustomerName, City
FROM Customers
WHERE Country = 'USA';

Data Manipulation Language (DML)

Used for managing data contents within schema objects.

INSERT Statement

The INSERT statement is used to add new records to a table.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example:

INSERT INTO Products (ProductID, ProductName, Price)
VALUES (101, 'New Gadget', 199.99);

UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE Products
SET Price = 210.50
WHERE ProductID = 101;

DELETE Statement

The DELETE statement is used to remove existing records from a table.

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Products
WHERE ProductName = 'Old Product';

Data Definition Language (DDL)

Used for defining database structure or schema.

CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in the database.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

ALTER TABLE Statement

The ALTER TABLE statement is used to modify an existing table (add, delete, or modify columns).

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE Employees
ADD Email VARCHAR(100);

DROP TABLE Statement

The DROP TABLE statement is used to delete an existing table from the database.

DROP TABLE table_name;

Example:

DROP TABLE Employees;

Common Clauses and Operators

SQL commands often utilize various clauses and operators to refine queries:

JOIN Example

Illustrates how to combine data from two tables.

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Further Reading