Understanding SQL Statements
SQL statements are the specific queries or operations entered into the database using the SQL language. They are used to access, modify, or manage data stored in database tables. SQL statements consist of keywords, expressions, clauses, and operators.
Components of SQL Statements:
- Keywords: Specific reserved words that perform particular operations (e.g.,
SELECT
,INSERT
,UPDATE
,DELETE
). - Expressions: Combinations of symbols and operators that evaluate to a value (e.g.,
Salary > 50000
). - Clauses: Components of SQL statements that define different parts (e.g.,
WHERE
,GROUP BY
,ORDER BY
). - Operators: Symbols that specify operations to perform (e.g.,
=
,>
,<
,AND
,OR
).
SELECT Statement
The SELECT
statement is used to choose data from a database. The information retrieved is stored in a result table known as the result set.
Example: Here, we select the Name
column from a table called Employees
:
SELECT Name
FROM Employees;
More Detailed Example:
Suppose we have an Employees
table with the following structure:
EmployeeID | Name | DepartmentID | Salary |
---|---|---|---|
1 | John Doe | 101 | 60000 |
2 | Jane Smith | 102 | 70000 |
3 | Mike Johnson | 103 | 80000 |
To retrieve the Name
and Salary
of all employees with a salary greater than 50,000:
SELECT Name, Salary
FROM Employees
WHERE Salary > 50000;
This query retrieves the Name
and Salary
columns for employees whose salary is greater than 50,000. The result set might look like this:
Name | Salary |
---|---|
John Doe | 60000 |
Jane Smith | 70000 |
Mike Johnson | 80000 |
SELECT * :
All of the columns in the table we are searching will be returned when SELECT is used with an asterisk (*).
SELECT * FROM Employees
EmployeeID | Name | DepartmentID | Salary |
---|---|---|---|
1 | John Doe | 101 | 60000 |
2 | Jane Smith | 102 | 70000 |
3 | Mike Johnson | 103 | 80000 |
SELECT DISTINCT
The SELECT DISTINCT
statement is used in SQL to return only distinct (different) values from a database table. This is particularly useful when you want to eliminate duplicate records and ensure that the result set contains only unique entries.
Syntax of SELECT DISTINCT
SELECT DISTINCT column1, column2, ...
FROM table_name;
Basic Example
Consider a table called Employees
with the following data:
EmployeeID | Name | DepartmentID | Salary |
---|---|---|---|
1 | John Doe | 101 | 60000 |
2 | Jane Smith | 102 | 70000 |
3 | John Doe | 101 | 60000 |
4 | Alice Brown | 103 | 80000 |
5 | John Doe | 101 | 60000 |
To retrieve a list of unique department IDs from the Employees
table:
SELECT DISTINCT DepartmentID
FROM Employees;
Result:
DepartmentID
101
102
103
Noteworthy Point
✅ The combination of Column Name and Group by can also be used to get the unique records of a table.
— Syntax as follows
SELECT column1, column2, …
FROM table_name
GROUP BY column1, column2, …;
Example with Multiple Columns
You can also use SELECT DISTINCT
with multiple columns to get unique combinations of values.
Example: To retrieve a list of unique names and their associated department IDs:
SELECT DISTINCT Name, DepartmentID
FROM Employees;
Result:
Name | DepartmentID |
---|---|
John Doe | 101 |
Jane Smith | 102 |
Alice Brown | 103 |
SELECT INTO
The SELECT INTO
statement is used in SQL to create a new table and insert data into it from an existing table. This is particularly useful for copying data from one table to another, making backups, or creating a subset of data.
Syntax of SELECT INTO
SELECT column1, column2, ...
INTO new_table_name
FROM existing_table_name
WHERE condition;
Basic Example
Consider a table called Employees
with the following data:
EmployeeID | Name | DepartmentID | Salary |
---|---|---|---|
1 | John Doe | 101 | 60000 |
2 | Jane Smith | 102 | 70000 |
3 | Mike Johnson | 103 | 80000 |
4 | Alice Brown | 104 | 90000 |
To create a new table called HighSalaryEmployees
with employees who have a salary greater than 70,000:
SELECT EmployeeID, Name, DepartmentID, Salary
INTO HighSalaryEmployees
FROM Employees
WHERE Salary > 70000;
This query creates a new table HighSalaryEmployees
and inserts the rows from Employees
where the salary is greater than 70,000.
Result:
EmployeeID | Name | DepartmentID | Salary |
---|---|---|---|
2 | Jane Smith | 102 | 70000 |
3 | Mike Johnson | 103 | 80000 |
4 | Alice Brown | 104 | 90000 |
ALIAS (AS) For Columns
In SQL, an alias is a temporary name assigned to a table or column for the purpose of making the output more readable or the SQL code more concise. The AS
keyword is used to create column aliases, but it is optional; you can define an alias without it. Aliases are particularly useful when you need to rename columns in the result set, especially when working with complex queries or when the original column names are not descriptive.
Syntax of Column Alias
SELECT column_name AS alias_name
FROM table_name;
You can also use aliases without the AS
keyword:
SELECT column_name alias_name
FROM table_name;
Basic Examples
Simple Column Alias
Consider a table called Employees
with the following data:
EmployeeID | FirstName | LastName | Salary |
---|---|---|---|
1 | John | Doe | 60000 |
2 | Jane | Smith | 70000 |
To create a more readable output, you might want to combine the FirstName
and LastName
columns into a single column called FullName
:
SELECT FirstName + ' ' + LastName AS FullName, Salary
FROM Employees;
Result:
FullName | Salary |
---|---|
John Doe | 60000 |
Jane Smith | 70000 |
ALIAS For Tables :
In SQL, table aliases provide temporary names to tables within a query. This can make complex queries easier to write and read, particularly when dealing with multiple tables or when the table names are lengthy. Aliases are especially useful in join operations where you need to reference columns from multiple tables. The AS
keyword is optional but commonly used for clarity.
Syntax of Table Alias
SELECT column1, column2, ...
FROM table_name AS alias_name
WHERE condition;
Or without AS
:
SELECT column1, column2, ...
FROM table_name alias_name
WHERE condition;
Basic Example
Consider two tables, Employees
and Departments
:
Employees Table:
EmployeeID | FirstName | LastName | DepartmentID |
---|---|---|---|
1 | John | Doe | 101 |
2 | Jane | Smith | 102 |
3 | Alice | Brown | 101 |
Departments Table:
DepartmentID | DepartmentName |
---|---|
101 | HR |
102 | Sales |
To retrieve employee names along with their department names using table aliases:
sqlCopy code
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;
Result:
FirstName | LastName | DepartmentName |
---|---|---|
John | Doe | HR |
Jane | Smith | Sales |
Alice | Brown | HR |
SQL WHERE Clause
In SQL, the WHERE
clause is used to filter rows returned by a query based on specified conditions. It allows you to retrieve only the rows that meet the specified criteria, making your queries more targeted and efficient.
Syntax of the WHERE Clause
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Basic Example
Consider a table called Employees
with the following data:
EmployeeID | FirstName | LastName | DepartmentID | Salary |
---|---|---|---|---|
1 | John | Doe | 101 | 60000 |
2 | Jane | Smith | 102 | 70000 |
3 | Alice | Brown | 101 | 65000 |
To retrieve only the employees from the “HR” department:
SELECT *
FROM Employees
WHERE DepartmentID = 101;
Result:
EmployeeID | FirstName | LastName | DepartmentID | Salary |
---|---|---|---|---|
1 | John | Doe | 101 | 60000 |
3 | Alice | Brown | 101 | 65000 |
Using Comparison Operators

You can use various comparison operators in the WHERE
clause to specify conditions:
- Equality (
=
): Retrieve rows where a column equals a specific value. - Inequality (
<>
or!=
): Retrieve rows where a column does not equal a specific value. - Greater Than (
>
), Less Than (<
), Greater Than or Equal To (>=
), Less Than or Equal To (<=
): Retrieve rows based on numerical comparisons. - LIKE: Retrieve rows based on pattern matching.
Example: To retrieve employees with a salary greater than $60,000:
SELECT *
FROM Employees
WHERE Salary > 60000;
Using Logical Operators
You can combine multiple conditions in the WHERE
clause using logical operators (AND
, OR
, NOT
) to create complex conditions.
Example: To retrieve employees from the “HR” department with a salary greater than $60,000:
SELECT *
FROM Employees
WHERE DepartmentID = 101
AND Salary > 60000;
Practical Scenarios for Using the WHERE Clause
Filtering Rows Based on Criteria
Example: Retrieving employees who joined after a specific date:
SELECT *
FROM Employees
WHERE JoinDate > '2022-01-01';
Implementing Security Measures
Example: Ensuring that only authorized users can access sensitive information:
SELECT *
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM AuthorizedDepartments);
Summary
- Purpose: The
WHERE
clause is used to filter rows returned by a query based on specified conditions. - Syntax:
SELECT column1, column2, ... FROM table_name WHERE condition;
- Usage: The
WHERE
clause helps retrieve only the rows that meet specific criteria, making queries more targeted and efficient.
By using the WHERE
clause effectively, you can tailor your SQL queries to retrieve the precise data you need, improving the accuracy and relevance of your results.