SQL Statements

By /

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:

EmployeeIDNameDepartmentIDSalary
1John Doe10160000
2Jane Smith10270000
3Mike Johnson10380000

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:

NameSalary
John Doe60000
Jane Smith70000
Mike Johnson80000

SELECT * :

All of the columns in the table we are searching will be returned when SELECT is used with an asterisk (*).

SELECT * FROM Employees
EmployeeIDNameDepartmentIDSalary
1John Doe10160000
2Jane Smith10270000
3Mike Johnson10380000

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:

EmployeeIDNameDepartmentIDSalary
1John Doe10160000
2Jane Smith10270000
3John Doe10160000
4Alice Brown10380000
5John Doe10160000

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:

NameDepartmentID
John Doe101
Jane Smith102
Alice Brown103

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:

EmployeeIDNameDepartmentIDSalary
1John Doe10160000
2Jane Smith10270000
3Mike Johnson10380000
4Alice Brown10490000

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:

EmployeeIDNameDepartmentIDSalary
2Jane Smith10270000
3Mike Johnson10380000
4Alice Brown10490000

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:

EmployeeIDFirstNameLastNameSalary
1JohnDoe60000
2JaneSmith70000

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:

FullNameSalary
John Doe60000
Jane Smith70000

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:

EmployeeIDFirstNameLastNameDepartmentID
1JohnDoe101
2JaneSmith102
3AliceBrown101

Departments Table:

DepartmentIDDepartmentName
101HR
102Sales

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:

FirstNameLastNameDepartmentName
JohnDoeHR
JaneSmithSales
AliceBrownHR

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:

EmployeeIDFirstNameLastNameDepartmentIDSalary
1JohnDoe10160000
2JaneSmith10270000
3AliceBrown10165000

To retrieve only the employees from the “HR” department:

SELECT *
FROM Employees
WHERE DepartmentID = 101;

Result:

EmployeeIDFirstNameLastNameDepartmentIDSalary
1JohnDoe10160000
3AliceBrown10165000

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.

Related Posts

Scroll to Top