SQL, or Structured Query Language, is a standardized language used to interact with databases. It enables users to perform a wide array of operations, including querying data, inserting records, updating existing data, and deleting records. SQL operates seamlessly across various relational database systems like MySQL, PostgreSQL, Oracle Database, SQL Server, and more.
SQL Basics
SELECT: Retrieves data from one or more tables.
SELECT column1, column2 FROM table_name WHERE condition;INSERT: Adds new records into a table.
INSERT INTO table_name (column1, column2) VALUES (value1, value2);UPDATE: Modifies existing records in a table.
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;DELETE: Removes records from a table.
DELETE FROM table_name WHERE condition;
Data Definition Language (DDL) Commands
CREATE: Creates database objects like tables, indexes, views, or schemas.
CREATE TABLE table_name (column1 datatype, column2 datatype, ... );ALTER: Modifies the structure of existing database objects.
ALTER TABLE table_name ADD column_name datatype;DROP: Deletes existing database objects.
DROP TABLE table_name;
Data Control Language (DCL) Commands
GRANT: Provides user access privileges to database objects.
GRANT SELECT, INSERT ON table_name TO user_name;REVOKE: Withdraws previously granted permissions from users.
REVOKE SELECT ON table_name FROM user_name;
Transaction Control Commands
COMMIT: Saves all changes made during the current transaction to the database.
COMMIT;ROLLBACK: Undoes changes made during the current transaction and restores the database to its original state since the last COMMIT.
ROLLBACK;
Querying and Schema Management Commands
USE: Specifies which database to use in multi-database environments.
USE database_name;DESCRIBE (or DESC): Provides metadata about a table's structure.
DESC table_name;SHOW: Displays information about databases, tables, or other database objects.
SHOW DATABASES;SHOW TABLES;
Other Useful SQL Commands
TRUNCATE: Deletes all records from a table quickly, but cannot be rolled back.
TRUNCATE TABLE table_name;GRANT: Assigns specific privileges to database users.
GRANT SELECT ON table_name TO user_name;REVOKE: Withdraws specific privileges from database users.
REVOKE SELECT ON table_name FROM user_name;
1. Creating Tables
In SQL, tables are created using the CREATE TABLE statement, defining columns along with their data types and constraints:
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
BirthDate DATE,
DepartmentID INT
);
2. Inserting Data
To add data into a table, use the INSERT INTO statement:
INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, DepartmentID)VALUES (1, 'John', 'Doe', '1990-05-15', 101);
3. Querying Data
Retrieve data from a table using the SELECT statement:
SELECT FirstName, LastName, DepartmentIDFROM Employees
WHERE DepartmentID = 101;
SQL Queries
4. Filtering Data
Filter records using the WHERE clause:
SELECT *FROM Employees
WHERE BirthDate >= '1990-01-01';
5. Joining Tables- JOINS
Combine data from multiple tables using JOIN clauses:
SELECT e.FirstName, e.LastName, d.DepartmentNameFROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
6. Aggregating Data
Aggregate functions summarize data:
SELECT DepartmentID, COUNT(*) AS NumberOfEmployeesFROM Employees
GROUP BY DepartmentID;
Advanced SQL Concepts
7. Subqueries
Nested queries within another query:
SELECT FirstName, LastNameFROM Employees
WHERE DepartmentID IN (
SELECT DepartmentID
FROM Departments
WHERE DepartmentName = 'IT'
);
8. Views(Advanatages)
Virtual tables based on SQL statements:
CREATE VIEW EmployeeDetails ASSELECT FirstName, LastName, DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
9. Transactions
Manage sequences of SQL operations:
BEGIN TRANSACTION;UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 123;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 456;
COMMIT;