SQL Tutorial - askthedev.com

SQL Introduction

What is SQL?

SQL is a programming language used to communicate with databases. It allows users to perform tasks such as querying, updating, and managing data.

What is a Database?

A database is an organized collection of structured data, typically stored electronically in a computer system. Databases are managed by a database management system (DBMS).

SQL Syntax

SQL statements are composed of keywords that define specific operations. Below are examples of SQL statements:

SELECT * FROM table_name;

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

SQL Data Types

SQL supports various data types including:

Data Type	Description
INT	Integer numbers.
VARCHAR(n)	Variable length string with a maximum length of n.
DATE	Stores date values.

SQL Statements

SQL SELECT Statement

The SELECT statement is used to select data from a database. Here’s an example:

SELECT * FROM employees;

SQL WHERE Clause

The WHERE clause is used to filter records that meet specific criteria:

SELECT * FROM employees WHERE department = 'Sales';

SQL ORDER BY Clause

The ORDER BY clause is used to sort the result set:

SELECT * FROM employees ORDER BY last_name ASC;

SQL GROUP BY Clause

The GROUP BY clause groups rows that have the same values:

SELECT department, COUNT(*) FROM employees GROUP BY department;

SQL INSERT INTO Statement

The INSERT INTO statement is used to add new records:

INSERT INTO employees (first_name, last_name, department) VALUES ('John', 'Doe', 'HR');

SQL UPDATE Statement

The UPDATE statement is used to modify existing records:

UPDATE employees SET department = 'Marketing' WHERE last_name = 'Doe';

SQL DELETE Statement

The DELETE statement removes records from a table:

DELETE FROM employees WHERE last_name = 'Doe';

SQL Joins

SQL INNER JOIN

The INNER JOIN keyword selects records with matching values in both tables:

SELECT employees.first_name, departments.department_name 
FROM employees 
INNER JOIN departments ON employees.department_id = departments.id;

SQL LEFT JOIN

The LEFT JOIN returns all records from the left table and the matched records from the right table:

SELECT employees.first_name, departments.department_name 
FROM employees 
LEFT JOIN departments ON employees.department_id = departments.id;

SQL RIGHT JOIN

The RIGHT JOIN returns all records from the right table and the matched records from the left table:

SELECT employees.first_name, departments.department_name 
FROM employees 
RIGHT JOIN departments ON employees.department_id = departments.id;

SQL FULL OUTER JOIN

The FULL OUTER JOIN returns all records when there is a match in either left or right table records:

SELECT employees.first_name, departments.department_name 
FROM employees 
FULL OUTER JOIN departments ON employees.department_id = departments.id;

SQL JOIN with a USING Clause

Using the USING clause simplifies joins when both columns share the same name:

SELECT employees.first_name, departments.department_name 
FROM employees 
JOIN departments USING (department_id);

SQL Functions

SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criterion:

SELECT COUNT(*) FROM employees;

SQL AVG() Function

The AVG() function calculates the average value of a numeric column:

SELECT AVG(salary) FROM employees;

SQL SUM() Function

The SUM() function returns the total sum of a numeric column:

SELECT SUM(salary) FROM employees;

SQL MIN() Function

The MIN() function returns the smallest value in a column:

SELECT MIN(salary) FROM employees;

SQL MAX() Function

The MAX() function returns the largest value in a column:

SELECT MAX(salary) FROM employees;

SQL Wildcards

SQL LIKE Operator

The LIKE operator is used to search for a specified pattern in a column:

SELECT * FROM employees WHERE first_name LIKE 'J%';

SQL BETWEEN Operator

The BETWEEN operator selects values within a given range:

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause:

SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

SQL IS NULL Operator

The IS NULL operator is used to test for empty values:

SELECT * FROM employees WHERE department IS NULL;

SQL Indexes

What is an Index?

An index is a database object that improves the speed of data retrieval operations on a table.

How to Create an Index

You can create an index using the following syntax:

CREATE INDEX index_name ON table_name (column_name);

How to Drop an Index

To remove an index, use the following syntax:

DROP INDEX index_name;

SQL Constraints

Not Null Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value:

CREATE TABLE employees (
    id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL
);

Unique Constraint

The UNIQUE constraint ensures that all values in a column are different:

CREATE TABLE employees (
    id INT UNIQUE,
    first_name VARCHAR(50)
);

Primary Key Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50)
);

Foreign Key Constraint

The FOREIGN KEY constraint prevents actions that would destroy links between tables:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

SQL Views

What is a View?

A view is a virtual table based on the result set of a SQL statement.

How to Create a View

You can create a view using the following syntax:

CREATE VIEW view_name AS 
SELECT column1, column2
FROM table_name;

How to Drop a View

To remove a view, use the following syntax:

DROP VIEW view_name;

SQL Transactions

What is a Transaction?

A transaction is a sequence of operations performed as a single logical unit of work.

SQL COMMIT Statement

The COMMIT statement saves all changes made during the transaction:

COMMIT;

SQL ROLLBACK Statement

The ROLLBACK statement undoes changes made during the transaction:

ROLLBACK;

SQL Stored Procedures

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save and reuse.

How to Create a Stored Procedure

You can create a stored procedure using the following syntax:

CREATE PROCEDURE procedure_name AS
BEGIN
   -- SQL statements here
END;

How to Call a Stored Procedure

To execute a stored procedure:

EXEC procedure_name;

SQL Best Practices

Use Indentation and Comments

Maintain clean and readable code with proper indentation and commenting.

Naming Conventions

Follow a consistent naming convention for tables, columns, and indexes to enhance clarity.

askthedev.com Latest Articles