SQL Basics

Learn fundamental SQL concepts that apply to all database systems

Introduction to SQL

SQL (Structured Query Language) is a standard language for accessing and manipulating databases. It allows you to perform various operations like retrieving data, inserting records, updating records, and deleting records.

Key Concepts:

  • DDL (Data Definition Language): CREATE, ALTER, DROP
  • DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
  • DCL (Data Control Language): GRANT, REVOKE
  • TCL (Transaction Control Language): COMMIT, ROLLBACK

Basic SQL Commands

SELECT Statement

The SELECT statement is used to select data from a database.

SELECT column1, column2, ...
FROM table_name;

Example:

SELECT FirstName, LastName
FROM Employees;

WHERE Clause

The WHERE clause is used to filter records.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT * 
FROM Customers
WHERE Country='Mexico';

INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

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

Example:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Common SQL Functions

Aggregate Functions

  • COUNT() - Returns the number of rows
  • SUM() - Returns the sum of a numeric column
  • AVG() - Returns the average value
  • MAX() - Returns the largest value
  • MIN() - Returns the smallest value
SELECT COUNT(*), AVG(Price)
FROM Products;

Common Functions

  • UPPER() - Converts a string to upper case
  • LOWER() - Converts a string to lower case
  • LEN() - Returns the length of a string
  • ROUND() - Rounds a numeric field to the specified number of decimals
SELECT ProductName, UPPER(Category)
FROM Products;

JOIN Operations

JOINs are 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.

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN

Returns all records from the left table, and the matched records from the right table.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

RIGHT JOIN

Returns all records from the right table, and the matched records from the left table.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

FULL OUTER JOIN

Returns all records when there is a match in either left or right table records.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;