2. DML (Data Manipulation Language)

Inserting Data

The INSERT INTO statement is used to add new data into a table.

INSERT INTO Students (FirstName, LastName, Age)
VALUES ('John', 'Doe', 25);

When rows are affected by a query (insert, delete, update, etc.), the output specifies the number of rows affected. For example: 4 rows affected.

Selecting Data

Retrieving All Data

The SELECT * statement retrieves all columns from a table.

SELECT * FROM Students;

Selecting Specific Columns

To retrieve specific columns, list them explicitly.

SELECT FirstName, LastName
FROM Students;

Using Aliases (AS)

Aliases allow renaming columns or tables for better readability.

SELECT FirstName + ' ' + LastName AS FullName
FROM Students;

SELECT FirstName + ' ' + LastName AS FullName, Age AS Student_Age
FROM Students;

Casting Data Types

Use CAST to convert data types.

SELECT 'Student: ' + FirstName + ' : Your Age is ' + CAST(Age AS NVARCHAR(10)) AS Student_Info
FROM Students;

Retrieving Limited Rows

Retrieving Top Rows

The TOP keyword limits the number of rows returned.

SELECT TOP(2) FirstName, LastName
FROM Students;

SELECT TOP 50 PERCENT FirstName, LastName
FROM Students;

Filtering Data

Use the WHERE clause to filter data based on conditions.

SELECT *
FROM Students
WHERE Age > 24;

Operators

• =: Equal to

• != or <>: Not equal to

• >: Greater than

• <: Less than

• >=: Greater than or equal to

• <=: Less than or equal to

• OR: Logical OR

• AND: Logical AND

Using Functions in Filters

Retrieve data based on functions like YEAR() or MONTH().

SELECT *
FROM Students AS S
WHERE YEAR(S.BirthDate) = 2022;

Handling Null Values

• IS NULL: Checks if a value is null.

• IS NOT NULL: Checks if a value is not null.

SELECT *
FROM Students
WHERE MiddleName IS NULL;

Sorting Data

Sort data using ORDER BY. By default, it sorts in ascending order (ASC). Use DESC for descending order.

SELECT *
FROM Students
ORDER BY Age DESC;

SELECT *
FROM Students
ORDER BY Age, FirstName

Skipping and Fetching Rows

The OFFSET and FETCH clauses allow skipping rows and fetching a specific number of rows.

SELECT *
FROM Students
ORDER BY FirstName ASC
OFFSET 2 ROWS
FETCH NEXT 1 ROWS ONLY;

Using IN for Multiple Matches

Instead of multiple OR conditions, use IN.

SELECT *
FROM Students
WHERE FirstName IN ('Aysel', 'Eli', 'Leyla');

Using BETWEEN for Ranges

Simplify range conditions with BETWEEN.

SELECT *
FROM Students
WHERE BirthDate BETWEEN '2002-01-19' AND '2020-09-09';

Pattern Matching with LIKE

Use the LIKE operator to match patterns.

• A%: Starts with A.

• %L: Ends with L.

• %O%: Contains O.

SELECT *
FROM Students
WHERE FirstName LIKE 'A%';

Updating Data

The UPDATE statement modifies existing records.

UPDATE Students
SET FirstName = 'Zerife'
WHERE FirstName = 'Aysel';

Deleting Data

The DELETE statement removes records.

DELETE FROM Students
WHERE ID = 1;

Distinct Data

The SELECT DISTINCT statement retrieves unique values, eliminating duplicates.

SELECT DISTINCT FirstName
FROM Students;

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version