Data Query Language (DQL) in SQL Server

Data Query Language (DQL)

Data Query Language (DQL) is specifically designed for querying and retrieving data from a database. DQL primarily consists of the SELECT statement and its associated clauses, which allow users to specify the data they want to retrieve, how it should be filtered, and how it should be presented.

SQL Select:

The SQL SELECT statement is used to retrieve data from a database.

Syntax:

SELECT column1, column2, ...
FROM table_name;

Explanation:

• SELECT: Indicates that you want to retrieve data from the specified columns.
• column1, column2, ...: Names of the columns you want to retrieve data from. You can specify multiple columns separated by commas.
• FROM table_name: Specifies the table from which you want to retrieve the data.

Example:

SELECT FirstName, LastName
FROM Employees;

Select All:

We can use the asterisk (*) symbol to select all columns from a table.

Syntax:

SELECT *
FROM table_name;

Example:

SELECT *
FROM Employees;

Select Distinct:

In SQL, the SELECT DISTINCT statement is used to retrieve unique values from a specific column or combination of columns in a table.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example:

SELECT DISTINCT Department
FROM Employees;

Select As Alias:

In SQL, the AS keyword is used to assign an alias to a column or an expression in the result set of a SELECT query. Aliases are temporary names assigned to columns or expressions in the result set, which can be useful for readability or when using the result set in subsequent calculations or queries.

Syntax:

SELECT column_name AS alias_name
FROM table_name;

Example:

SELECT FirstName AS First_Name, LastName AS Last_Name
FROM Employees;

This query will select the FirstName column from the Employees table and rename it as First_Name, and it will select the LastName column and rename it as Last_Name in the result set.

Select Top:

In SQL, the SELECT TOP clause is used to retrieve a specified number of rows from the beginning of a result set.

Syntax:

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

Example:

SELECT TOP 5 FirstName, LastName
FROM Customers

WHERE Clause:

You can also add conditions to your SELECT statement using the WHERE clause to filter the rows returned based on specific criteria.

Example:

Suppose you want to retrieve the FirstName and LastName of employees with a salary greater than $50,000:

SELECT FirstName, LastName
FROM Employees
WHERE Salary > 50000;

Other Operators:

Here are the common SQL operators that you can use within the WHERE clause to construct conditions:

Comparison Operators:

• =: Equal to
• <> or !=: Not equal to
• <: Less than
• >: Greater than
• <=: Less than or equal to
• >=: Greater than or equal to

Logical Operators:

• AND: Combines two or more conditions. Both conditions must be true for the row to be included.
• OR: Combines two or more conditions. At least one condition must be true for the row to be included.
• NOT: Negates a condition. It returns true if the condition is false, and vice versa.

Example Usage:

SELECT *
FROM Employees
WHERE Salary > 50000
  AND Department = 'Sales';

ORDER BY Clause:

In SQL, the ORDER BY clause is used to sort the result set of a query based on one or more columns. It allows you to specify the order in which the rows should appear in the result set.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Example:

SELECT FirstName, LastName, Age
FROM Employees
ORDER BY LastName ASC, FirstName ASC;

This query retrieves the FirstName, LastName, and Age columns from the Employees table and sorts the result set first by LastName in ascending order and then by FirstName in ascending order.