An Introduction to SQL

Introduction

SQL, or Structured Query Language, is a standardized programming language used for handling and manipulating relational databases. It allows users to interact with databases by performing operations such as querying, updating, inserting, and deleting data. SQL is crucial for working with relational databases, which organize data into tables with rows and columns.

Here are some key reasons why SQL is widely used:

• Efficient Data management - Data retrieval and manipulation.

• Standardization - SQL is used across various relational database management systems like MySQL, PostgreSQL, SQL Server, and Oracle.

• Powerful Querying - Complex queries, filtering, and sorting.

• Data Integrity and Consistency - SQL supports transactions, and you can define constraints.

• Scalability - Ability to handle large data sets and create indexes.

• Data Security - SQL provides mechanisms to control access to data, and many SQL-based systems support data encryption

• Flexibility - SQL integrates well with other tools and technologies (e.g. Power BI, Tableau, Python, R), and is used for data analysis.

• Reporting and Automation - Create custom reports and perform regular data maintenance tasks

• Support and Community - there is extensive documentation and a large community of users, additionally many resources available.

Basic SQL

Basic SQL statements generally follow a structure: COMMAND [options] FROM table [WHERE condition].

COMMANDS

SELECT retrieves data from a database. It is in the format SELECT column1, column2 FROM table_name;. To select all columns, you use SELECT * FROM table_name.

INSERT INTO adds new rows to a table. INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE modifies existing data in a table. UPDATE table_name SET column1 = value1 WHERE condition;

DELETE removes rows from a table. DELETE FROM table_name WHERE condition;

FILTERING DATA

WHERE filters records based on a condition. SELECT * FROM table_name WHERE column_name = value;

AND/OR combine multiple conditions. SELECT * FROM table_name WHERE column1 = value1 AND column2 = value2;

SORTING DATA

ORDER BY sorts the result set by one or more columns. SELECT * FROM table_name ORDER BY column_name ASC; -- ASC for ascending, DESC for descending.

AGGREGATING DATA

COUNT counts the number of rows. SELECT COUNT(*) FROM table_name;

SUM calculates the sum of a column. SELECT SUM(column_name) FROM table_name;

AVG calculates the average value of a column. SELECT AVG(column_name) FROM table_name;

MIN/MAX finds the minimum or maximum value

JOINING TABLES

INNER JOIN returns rows with matching values in both tables.

SELECT columns FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

LEFT JOIN returns all rows from the left table and matched rows from the right table.

SELECT columns FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

CREATING TABLES

CREATE TABLE defines a new table and its columns.

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints
);

MODIFYING TABLES

ALTER TABLE adds, deletes, or modifies columns.

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY COLUMN column_name datatype;

DROPPING TABLES

DROP TABLE deletes an entire table and its data.

DROP TABLE table_name;

Let’s say we have a table called Employees:

To retrieve all employees, we use SELECT * FROM Employees.

To add a new employee, we use INSERT INTO Employees (EmployeeID, Name, Department, Salary) VALUES (4, 'Anna Brown', 'HR', 57000);.

To update an employee's salary, we use UPDATE Employees SET Salary = 52000 WHERE EmployeeID = 1;.

To delete an employee, we use DELETE FROM Employees WHERE EmployeeID = 4;.