SQL Statements Contd.

SQL ORDER BY

The ORDER BY keyword in SQL is used to sort the result set of a query by one or more columns. Sorting can be done in ascending order (default) or descending order, allowing for easier data analysis and more meaningful reporting.

✅ Syntax of the ORDER BY Keyword

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

• ASC: Ascending order (default if not specified).
• DESC: Descending order.

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 all employees sorted by their LastName in ascending order:

SELECT *
FROM Employees
ORDER BY LastName ASC;

Result:

EmployeeID	FirstName	LastName	DepartmentID	Salary
3	Alice	Brown	101	65000
1	John	Doe	101	60000
2	Jane	Smith	102	70000

Detailed Examples

Sorting by Multiple Columns

You can sort the result set by multiple columns. The sorting is done first by the first column, then by the second column if the values in the first column are the same, and so on.

Example: To retrieve all employees sorted by DepartmentID in ascending order and Salary in descending order:

SELECT *
FROM Employees
ORDER BY DepartmentID ASC, Salary DESC;

Result:

EmployeeID	FirstName	LastName	DepartmentID	Salary
3	Alice	Brown	101	65000
1	John	Doe	101	60000
2	Jane	Smith	102	70000

Using Aliases with ORDER BY

If you use column aliases in your SELECT statement, you can also use these aliases in the ORDER BY clause.

Example: To sort employees by their last name using an alias:

SELECT FirstName, LastName AS Surname
FROM Employees
ORDER BY Surname ASC;

---

INSERT INTO

In SQL, the INSERT INTO statement is essential for adding new data to tables. Whether you’re populating a table with initial data or adding new records, this command is crucial.

Key Points:

✅ Syntax for Inserting All Columns:

INSERT INTO table_name
VALUES (value1, value2, ...);

✅ Syntax for Inserting Specific Columns:

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

Examples:

💡 Adding a New Employee:

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES ('Jane', 'Smith', 102, 70000);

💡 Inserting Multiple Rows:

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('Alice', 'Brown', 101, 65000),
('Bob', 'Davis', 103, 55000),
('Charlie', 'Miller', 104, 72000);

💡 Using a Subquery:

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
SELECT FirstName, LastName, DepartmentID, Salary
FROM NewEmployees;

Practical Scenarios for Using INSERT INTO:

• Populating a Table with Initial Data:

INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES
(101, 'HR'),
(102, 'Finance'),
(103, 'IT'),
(104, 'Marketing');

• Adding New Records: Whenever a new employee joins the company, add their details to the Employees table:

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES ('Daniel', 'Green', 105, 80000);

Summary:

• Purpose: The INSERT INTO statement adds new rows of data to a table.
• Syntax:
  • All Columns: INSERT INTO table_name VALUES (value1, value2, ...);
  • Specific Columns: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
• Usage: Essential for adding new data to a table, whether inserting individual rows, multiple rows at once, or copying data from another table.

By mastering the INSERT INTO statement, you can efficiently manage and populate your database tables, ensuring they remain up-to-date and accurate!

---

DELETE Statement

The SQL DELETE statement is used to remove existing records from a table. This operation is crucial for maintaining the accuracy and relevance of your database by allowing you to delete outdated or incorrect data.

Key Points:

✅ Basic Syntax:

DELETE FROM table_name
WHERE condition;

✅ Deleting All Records (without a condition):

DELETE FROM table_name;

Examples:

💡 Deleting Specific Records: Suppose you want to delete an employee record where the EmployeeID is 123:

DELETE FROM Employees
WHERE EmployeeID = 123

💡 Deleting Records Based on Multiple Conditions: For instance, you want to delete records where the DepartmentID is 102 and the Salary is less than 50000:

DELETE FROM Employees
WHERE DepartmentID = 102 AND Salary < 50000;

💡 Deleting All Records from a Table: If you need to clear all data from the Employees table but keep the table structure intact:

DELETE FROM Employees;

Practical Scenarios for Using DELETE:

• Removing Outdated Records: Delete records that are no longer relevant to keep your database clean and efficient.

DELETE FROM Orders WHERE OrderDate < '2023-01-01';

• Correcting Data Errors: Remove incorrect data entries to maintain data integrity.

DELETE FROM Customers WHERE Email = 'incorrect@example.com';

Summary:

• Purpose: The DELETE statement removes existing records from a table.
• Syntax:
  • Basic:DELETE FROM table_name WHERE condition;
  • All Records:DELETE FROM table_name;
• Usage: Essential for maintaining the database by removing outdated, incorrect, or irrelevant data.

Mastering the DELETE statement allows you to efficiently manage your database by ensuring it contains only the most accurate and relevant data!

---

UPDATE Statement

The SQL UPDATE statement is used to modify existing records in a table. This is essential for keeping your data accurate and up-to-date, allowing you to change values without deleting and re-inserting records.

Key Points:

✅ Basic Syntax:

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

Examples:

💡 Updating Specific Records: To update the salary of an employee with EmployeeID 123:

UPDATE Employees
SET Salary = 80000
WHERE EmployeeID = 123;

💡 Updating Multiple Columns: To change both the salary and department of the same employee:

UPDATE Employees
SET Salary = 85000, DepartmentID = 105
WHERE EmployeeID = 123;

💡 Updating Records Based on a Condition: To give a salary increase to all employees in the IT department:

UPDATE Employees
SET Salary = Salary * 1.1
WHERE DepartmentID = 103;

Practical Scenarios for Using UPDATE:

• Correcting Data: Update incorrect data entries to ensure data integrity.

UPDATE Customers SET Email = 'corrected@example.com' WHERE CustomerID = 456;

• Applying Bulk Changes: Apply changes across multiple records efficiently.

UPDATE Products SET Price = Price * 1.05 WHERE Category = 'Electronics';

Summary:

• Purpose: The UPDATE statement modifies existing records in a table.
• Syntax:
  • Basic:UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
  • Without Condition (Updates All Records):UPDATE table_name SET column1 = value1, column2 = value2, ...;
• Usage: Essential for maintaining accurate and current data by updating records directly.

By mastering the UPDATE statement, you can efficiently manage and modify your database, ensuring that your data remains precise and up-to-date!

---

LIMIT Clause

The SQL LIMIT clause is used to specify the maximum number of records to return in a result set. This is particularly useful for managing large datasets, paginating results, and improving query performance.

Key Points:

✅ Basic Syntax:

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_records;

✅ Syntax with OFFSET:

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_records OFFSET offset_value;

Examples:

💡 Retrieving the First 10 Records: To get the top 10 rows from the Employees table:

SELECT *
FROM Employees
LIMIT 10;

💡 Using OFFSET for Pagination: To retrieve 10 records starting from the 11th row (useful for pagination):

SELECT *
FROM Employees
LIMIT 10 OFFSET 10;

Practical Scenarios for Using LIMIT:

• Fetching Top N Records: Display the top N records based on specific criteria, such as the highest salaries:

SELECT FirstName, LastName, Salary FROM Employees ORDER BY Salary DESC LIMIT 5;

• Implementing Pagination: Efficiently paginate through large datasets:

SELECT * FROM Products ORDER BY ProductName LIMIT 20 OFFSET 40;

• Optimizing Query Performance: Improve performance by limiting the number of records processed:

SELECT * FROM LargeTable WHERE Condition = 'Value' LIMIT 100;

Summary:

• Purpose: The LIMIT clause restricts the number of rows returned by a query.
• Syntax:
  • Basic:SELECT column1, column2, ... FROM table_name LIMIT number_of_records;
  • With OFFSET:SELECT column1, column2, ... FROM table_name LIMIT number_of_records OFFSET offset_value;
• Usage: Essential for managing large datasets, implementing pagination, and optimizing query performance.

By mastering the LIMIT clause, you can efficiently handle large datasets, making your queries faster and your data presentation more manageable!

---