WikiGalaxy

Personalize

SQL Basics

Introduction to SQL

What is SQL?

SQL stands for Structured Query Language and is used to communicate with databases. It is the standard language for relational database management systems.

Basic SQL Commands

SQL commands are divided into several categories, including Data Query Language (DQL), Data Definition Language (DDL), Data Control Language (DCL), and Data Manipulation Language (DML).


    SELECT * FROM Customers;

Data Definition Language (DDL)

Creating Tables

DDL commands such as CREATE, ALTER, and DROP are used to define and modify database structures.

Example: Creating a Table

The CREATE TABLE statement is used to create a new table in the database.


    CREATE TABLE Employees (
      EmployeeID int,
      FirstName varchar(255),
      LastName varchar(255),
      BirthDate date
    );

Data Manipulation Language (DML)

Inserting Data

DML commands like INSERT, UPDATE, and DELETE are used to manipulate data within tables.

Example: Inserting Data

The INSERT INTO statement is used to add new rows of data to a table.


    INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate)
    VALUES (1, 'John', 'Doe', '1980-01-01');

Data Query Language (DQL)

Selecting Data

DQL commands, primarily the SELECT statement, are used to query data from the database.

Example: Selecting Data

The SELECT statement retrieves data from one or more tables.


    SELECT FirstName, LastName FROM Employees WHERE EmployeeID = 1;

Data Control Language (DCL)

Managing Permissions

DCL commands such as GRANT and REVOKE are used to control access to data in the database.

Example: Granting Permissions

The GRANT statement is used to give users access privileges to the database.


    GRANT SELECT ON Employees TO User1;

SQL Joins

Understanding Joins

SQL Joins are used to combine rows from two or more tables based on a related column between them.

Example: Inner Join

An INNER JOIN returns records that have matching values in both tables.


    SELECT Employees.FirstName, Departments.DepartmentName
    FROM Employees
    INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

SQL Functions

Using SQL Functions

SQL functions are used to perform calculations on data, format data, and manipulate strings.

Example: Aggregate Function

The COUNT function returns the number of rows that match a specified condition.


    SELECT COUNT(EmployeeID) FROM Employees;

SQL Transactions

Managing Transactions

Transactions in SQL are used to manage a sequence of operations as a single unit of work.

Example: Starting a Transaction

A transaction is started with the BEGIN TRANSACTION statement and completed using COMMIT or ROLLBACK.


    BEGIN TRANSACTION;
    INSERT INTO Employees (EmployeeID, FirstName, LastName) VALUES (2, 'Jane', 'Doe');
    COMMIT;