Start Your Journey With SQL & Databases Beginner-Friendly Concepts, Queries & Practice

SQL (Structured Query Language) is the standard language used to communicate with Relational Database Management Systems (RDBMS). It is used to store, manipulate, and retrieve data in databases.

Here is a basic SQL query to get you started:

SELECT * FROM users;

SELECT * FROM employees;

A Relational Database stores data in tables. A table is a collection of related data entries and it consists of columns and rows.

To practice SQL, you need a database system. There are many options ranging from lightweight local files to robust server software.

For beginners, SQLite is highly recommended. You can use DB Browser for SQLite or try an online compiler like SQL Fiddle.

Before you can insert data, you must define the structure of your database and tables using Data Definition Language (DDL).

CREATE DATABASE company_db;
USE company_db;

Now, let's create a table:

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

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL
);

When creating a table, you must specify the data type for each column. This defines what kind of data can be stored.

Data Manipulation Language (DML) commands allow you to modify data within your tables.

INSERT INTO employees (id, first_name, last_name, age, hire_date)
VALUES (1, 'John', 'Doe', 28, '2023-01-15');

UPDATE employees
SET age = 29
WHERE id = 1;

DELETE FROM employees
WHERE id = 1;

INSERT INTO products (id, name, price)
VALUES (10, 'Laptop', 1200.50);

The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set.

-- Select specific columns
SELECT first_name, last_name FROM employees;

-- Select all columns
SELECT * FROM employees;

You can also use the DISTINCT keyword to return only distinct (different) values.

SELECT DISTINCT age FROM employees;

SELECT name, price FROM products;

These clauses help you filter, sort, and restrict the amount of data returned by a SELECT query.

SELECT * FROM employees
WHERE age >= 30 AND last_name = 'Smith';

SELECT * FROM employees
ORDER BY age DESC;

SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 5;

SELECT name FROM products
WHERE price > 500
ORDER BY price DESC
LIMIT 10;

Aggregate functions perform a calculation on a set of values and return a single value.

• COUNT() - Returns the number of rows
• SUM() - Returns the total sum of a numeric column
• AVG() - Returns the average value
• MIN() - Returns the smallest value
• MAX() - Returns the largest value

SELECT 
    COUNT(*) as total_employees,
    AVG(age) as average_age,
    MAX(age) as oldest_employee
FROM employees;

SELECT SUM(price) FROM products;

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT department, COUNT(id) as emp_count
FROM employees
GROUP BY department
HAVING COUNT(id) > 5;

SELECT category, SUM(revenue) 
FROM sales
GROUP BY category;

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN: Returns all records from the left table, and the matched records from the right table.
• RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

SELECT students.name, enrollments.course_id
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id;