SQL - Aamir P

Hello everyone!

Recently, I have started to revise SQL. I thought of also sharing my knowledge with you all.

SQL (Structured Query Language)?is a powerful tool for managing and querying relational databases. Let's begin with some foundational concepts and SQL commands:

What is SQL?

• SQL is a programming language used for managing and querying relational databases.
• It allows you to interact with databases to store, retrieve, update, and manipulate data.

?Basic SQL Commands:

• SELECT:?Used to retrieve data from a database.

SELECT column1, column2 FROM table_name;

• INSERT:?Used to insert new records into a table.

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

• UPDATE:?Used to modify existing records in a table.

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

• DELETE:?Used to delete records from a table.

DELETE FROM table_name WHERE condition;

• CREATE TABLE:?Used to create a new table.

CREATE TABLE table_name (

??column1 datatype,

??column2 datatype,

??...

);

• ALTER TABLE:?Used to modify an existing table.

ALTER TABLE table_name ADD column_name datatype;

• DROP TABLE:?Used to delete an existing table.

DROP TABLE table_name;

Data Types:

SQL supports various data types like INT, VARCHAR, DATE, etc., which define the kind of data a column can hold.

CREATE TABLE students (

??student_id INT,

??first_name VARCHAR(50),

??last_name VARCHAR(50),

??birthdate DATE

);

Constraints:

Constraints are rules applied to a column to enforce data integrity. Examples include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, etc.

CREATE TABLE employees (

??employee_id INT PRIMARY KEY,

??first_name VARCHAR(50),

??last_name VARCHAR(50),

??hire_date DATE NOT NULL

);

Sorting and Filtering:

• You can use the ORDER BY clause to sort query results.

SELECT product_name, price FROM products ORDER BY price DESC;

(By default, it is ascending. DESC is mentioned to say descending)

• The WHERE clause allows you to filter data based on conditions.

SELECT customer_name, order_date FROM orders WHERE order_date >= '2023-01-01';

Joins:

• Joins are used to combine data from multiple tables based on a related column.
• Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

1. INNER JOIN:

• An INNER JOIN returns only the rows that have matching values in both tables.

Example:?Suppose you have two tables, "employees" and "departments," and you want to retrieve a list of employees along with their department names.

SELECT employees.employee_name, departments.department_name

FROM employees

INNER JOIN departments ON employees.department_id = departments.department_id;

2. LEFT JOIN (or LEFT OUTER JOIN):

• A LEFT JOIN returns all rows from the left table (the first table mentioned) and the matched rows from the right table (the second table mentioned). If there is no match, NULL values are returned for columns from the right table.

Example:?You want to retrieve a list of all departments and the employees in each department.

SELECT departments.department_name, employees.employee_name

FROM departments

LEFT JOIN employees ON departments.department_id = employees.department_id;

3. RIGHT JOIN (or RIGHT OUTER JOIN):

• A RIGHT JOIN is similar to a LEFT JOIN but returns all rows from the right table and the matched rows from the left table.

Example:?You want to retrieve a list of all employees and their corresponding department names.

SELECT employees.employee_name, departments.department_name

FROM employees

RIGHT JOIN departments ON employees.department_id = departments.department_id;

4. FULL JOIN (or FULL OUTER JOIN):

• A FULL JOIN returns all rows when there is a match in either the left or the right table. If there is no match, NULL values are returned for columns from the non-matching side.

Example:?You want to retrieve a list of all departments and all employees, including those without departments.

SELECT departments.department_name, employees.employee_name

FROM departments

FULL JOIN employees ON departments.department_id = employees.department_id;

5. SELF JOIN:

• A SELF JOIN is used to join a table with itself. It is often used when a table has a hierarchical structure, such as an organizational chart.

Example:?Suppose you have an "employees" table with a "manager_id" column, and you want to retrieve a list of employees and their respective managers.

SELECT e.employee_name AS employee, m.employee_name AS manager

FROM employees e

LEFT JOIN employees m ON e.manager_id = m.employee_id;

Aggregation:

SQL provides functions like SUM, COUNT, AVG, MIN, and MAX to perform calculations on data.

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department;

Subqueries:

Subqueries are queries nested within other queries, often used for more complex filtering or calculations.

SELECT product_name, price

FROM products

WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

GROUP BY and HAVING:??

• GROUP BY is used to group rows based on a column.?

SELECT department, COUNT(employee_id) AS num_employees

FROM employees

GROUP BY department;

• HAVING is used to filter grouped results.

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department

HAVING AVG(salary) > 50000;

Indexes:?

Indexes can improve query performance by speeding up data retrieval.

Transactions:?

SQL supports transactions to ensure data consistency and integrity.

So, that’s it for the day! Thanks for your time in reading my article. Tell me your feedback or views in the comments section.

Check out this link to know more about me

https://www.dhirubhai.net/feed/update/urn:li:activity:7006538868665577472/