What Is Sql Complete Guide

What is SQL?

SQL is a standardized language used for managing and manipulating relational databases. It allows users to perform operations such as querying data, inserting, updating, and deleting records, creating and modifying database tables, and much more.

Step-by-Step Guide with Complete Examples

Step 1: Setting Up Your Environment

Before you start writing SQL queries, you need an environment where you can run them and see the results. One popular option is MySQL because it’s free, open-source, and widely used. You can set up MySQL on your local machine or use an online SQL playground like DB Fiddle.

For this example, let's assume you're using DB Fiddle.

1. Open DB Fiddle.
2. Choose MySQL as the database type from the dropdown.
3. Start writing your SQL queries in the provided SQL editor.

Step 2: Creating a Database

In SQL, a database is a collection of related tables.

CREATE DATABASE example_db;
USE example_db;

• CREATE DATABASE statement creates a new database.
• USE statement selects the database to be used.

Step 3: Creating a Table

A table is a collection of data organized in rows and columns.

Let's create a table named employees.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

• CREATE TABLE statement creates a new table named employees.
• id INT AUTO_INCREMENT PRIMARY KEY creates a column id that automatically increments with each new record and serves as the primary key.
• first_name and last_name are strings with a maximum length of 50 characters.
• age is an integer.
• department is a string with a maximum length of 50 characters.

Step 4: Inserting Data into a Table

Data can be inserted into a table using the INSERT INTO statement.

Let's add some sample data to the employees table.

INSERT INTO employees (first_name, last_name, age, department) VALUES ('John', 'Doe', 28, 'HR');
INSERT INTO employees (first_name, last_name, age, department) VALUES ('Jane', 'Smith', 34, 'Finance');
INSERT INTO employees (first_name, last_name, age, department) VALUES ('Emily', 'Johnson', 42, 'IT');
INSERT INTO employees (first_name, last_name, age, department) VALUES ('Chris', 'Brown', 25, 'HR');

• INSERT INTO specifies the table name.
• VALUES contains the data to be inserted in the table.

Step 5: Selecting Data from a Table

To retrieve data from a table, use the SELECT statement.

Let's get all the records from the employees table.

SELECT * FROM employees;

Result:

| id | first_name | last_name | age | department |
|----|------------|-----------|-----|------------|
| 1  | John       | Doe       | 28  | HR         |
| 2  | Jane       | Smith     | 34  | Finance    |
| 3  | Emily      | Johnson   | 42  | IT         |
| 4  | Chris      | Brown     | 25  | HR         |

You can also select specific columns:

SELECT first_name, last_name FROM employees;

Result:

| first_name | last_name |
|------------|-----------|
| John       | Doe       |
| Jane       | Smith     |
| Emily      | Johnson   |
| Chris      | Brown     |

Step 6: Filtering Data

To filter data based on certain conditions, use the WHERE clause.

Let's find all employees in the 'HR' department.

SELECT * FROM employees WHERE department = 'HR';

Result:

| id | first_name | last_name | age | department |
|----|------------|-----------|-----|------------|
| 1  | John       | Doe       | 28  | HR         |
| 4  | Chris      | Brown     | 25  | HR         |

Step 7: Updating Data

To update existing records in a table, use the UPDATE statement.

Let's update Chris Brown's age to 26.

UPDATE employees SET age = 26 WHERE first_name = 'Chris' AND last_name = 'Brown';

Now, if we select all records from the employees table again:

SELECT * FROM employees;

Result:

| id | first_name | last_name | age | department |
|----|------------|-----------|-----|------------|
| 1  | John       | Doe       | 28  | HR         |
| 2  | Jane       | Smith     | 34  | Finance    |
| 3  | Emily      | Johnson   | 42  | IT         |
| 4  | Chris      | Brown     | 26  | HR         |

Step 8: Deleting Data

To delete records from a table, use the DELETE FROM statement.

Let's delete Emily Johnson's record from the employees table.

DELETE FROM employees WHERE first_name = 'Emily' AND last_name = 'Johnson';

Now, if we select all records from the employees table again:

SELECT * FROM employees;

Result:

| id | first_name | last_name | age | department |
|----|------------|-----------|-----|------------|
| 1  | John       | Doe       | 28  | HR         |
| 2  | Jane       | Smith     | 34  | Finance    |
| 4  | Chris      | Brown     | 26  | HR         |

Step 9: Sorting Data

To sort data, use the ORDER BY clause.

Let's list all employees sorted by their last name in ascending order.

SELECT * FROM employees ORDER BY last_name ASC;

Result:

| id | first_name | last_name | age | department |
|----|------------|-----------|-----|------------|
| 2  | Jane       | Smith     | 34  | Finance    |
| 1  | John       | Doe       | 28  | HR         |
| 4  | Chris      | Brown     | 26  | HR         |

Sorting in descending order:

SELECT * FROM employees ORDER BY last_name DESC;

Result: