Comprehensive SQL Tutorial for Beginners

 

Introduction to SQL and Databases

A database is a system used to store, organize, and retrieve data. A relational database stores data in tables, which can be linked to each other using primary keys and foreign keys. In SQL, you can interact with a database by creating tables, inserting data, querying information, and modifying structures.

This tutorial will walk you through SQL basics, database creation, understanding data types, joins, stored procedures, and more, with visual examples.

---

1. Creating a Database and Defining Data Types

Creating a Database

Before we start creating tables and inserting data, we need to create a database.

sql
CREATE DATABASE company;

You can check if the database was created by listing all available databases:

sql
SHOW DATABASES;

Once the database is created, switch to it:

sql
USE company;

Defining Data Types

In SQL, each column in a table must have a data type, which specifies the kind of data it can store. Here are some common data types:

• INT: Stores integers (whole numbers).
• VARCHAR(size): Stores variable-length strings (text).
• TEXT: Stores long text.
• DATE: Stores date values in 'YYYY-MM-DD' format.
• FLOAT: Stores floating-point numbers.
• BOOLEAN: Stores TRUE or FALSE values.

---

2. Creating Tables with Data Types

Let's start by creating a table to store employee information, defining appropriate data types for each column.

sql
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,  -- Unique identifier for each employee
  name VARCHAR(100),            -- Employee's name (string of up to 100 characters)
  age INT,                      -- Employee's age (integer)
  salary FLOAT,                 -- Employee's salary (decimal number)
  hire_date DATE,               -- The date employee was hired
  active BOOLEAN                -- Whether the employee is currently active (true/false)
);

Visualizing the Table Structure:

employee_id	name	age	salary	hire_date	active
1	John Doe	28	55000.00	2020-03-10	TRUE
2	Jane Smith	35	65000.00	2018-06-20	FALSE
3	Emily Davis	40	75000.00	2015-09-15	TRUE

---

3. Inserting Data into Tables

After creating a table, you can insert data into it using the INSERT INTO statement.

sql
INSERT INTO employees (employee_id, name, age, salary, hire_date, active) 
VALUES 
(1, 'John Doe', 28, 55000.00, '2020-03-10', TRUE),
(2, 'Jane Smith', 35, 65000.00, '2018-06-20', FALSE),
(3, 'Emily Davis', 40, 75000.00, '2015-09-15', TRUE);

---

4. SQL Queries and Joins

Now that we have data in our employees table, let's perform some SQL queries and explore joins.

4.1 SELECT Statement

To retrieve data from a table, you can use the SELECT statement:

sql
SELECT * FROM employees;

This will return all columns and rows from the employees table.

---

4.2 INNER JOIN

We will now create a second table, departments, and show how to use INNER JOIN to combine data from multiple tables.

sql
CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(100)
);
INSERT INTO departments (department_id, department_name)
VALUES (1, 'Human Resources'), (2, 'Engineering');

Now let's use an INNER JOIN to combine the employee data with the department data.

sql
SELECT employees.name, employees.age, departments.department_name
FROM employees
INNER JOIN departments ON employees.employee_id = departments.department_id;

Visualizing the JOIN Result:

name	age	department_name
John Doe	28	Human Resources
Jane Smith	35	Engineering
Emily Davis	40	Human Resources

---

5. Advanced SQL Commands

5.1 Using SQL Functions

1. COUNT(): Returns the number of rows.

sql
SELECT COUNT(*) FROM employees;

2. SUM(): Adds the values in a numeric column.

sql
SELECT SUM(salary) FROM employees;

3. AVG(): Finds the average value of a column.

sql
SELECT AVG(age) FROM employees;

4. DISTINCT: Retrieves unique values from a column.

sql
SELECT DISTINCT department_name FROM departments;

5.2 ALTER TABLE

To modify the structure of an existing table (e.g., adding a new column):

sql
ALTER TABLE employees ADD email VARCHAR(100);

---

6. SQL Constraints

Constraints are used to enforce rules on the data in a table.

1. NOT NULL: Ensures a column cannot have NULL values.

sql
ALTER TABLE employees MODIFY name VARCHAR(100) NOT NULL;

2. UNIQUE: Ensures all values in a column are unique.

sql
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

3. CHECK: Ensures that the values in a column meet a specified condition.

sql
ALTER TABLE employees ADD CONSTRAINT check_age CHECK (age > 18);

---

7. Stored Procedures

A stored procedure is a precompiled set of SQL statements that can be executed as a unit. Stored procedures help to avoid repeated code and improve performance.

Example: Creating a stored procedure that returns the details of an employee by employee_id:

sql
DELIMITER $$
CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT)
BEGIN
  SELECT * FROM employees WHERE employee_id = emp_id;
END $$
DELIMITER ;

To execute the stored procedure:

sql
CALL GetEmployeeDetails(1);

---

8. Indexing

An index improves the speed of data retrieval in a table. Let's create an index on the name column of the employees table:

sql
CREATE INDEX idx_employee_name ON employees(name);

---

9. SQL Data Types Summary

Below is a summary of common SQL data types:

Data Type	Description
INT	Integer numbers (whole numbers)
VARCHAR(n)	Variable-length string with a maximum length of n
TEXT	Long text
DATE	Date in 'YYYY-MM-DD' format
FLOAT	Floating-point number (decimal)
BOOLEAN	TRUE or FALSE value

---

10. Conclusion

This SQL tutorial covered key topics such as creating databases and tables, understanding data types, writing basic and advanced SQL queries, using joins, stored procedures, and managing constraints and indexes. These concepts are foundational to working with relational databases, enabling you to create, retrieve, and manage data efficiently.

By mastering SQL commands and concepts, you can effectively handle complex database operations and queries.

---

#SQLTutorial #LearnSQL #DatabaseCreation #SQLJoins #StoredProcedures #SQLConstraints #AdvancedSQL #SQLDataTypes #SQLIndex #TechCareers #DatabaseDesign #TechTutorial #RelationalDatabase #HighCPM #TechContent