SQL Syntax Basics

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. Understanding SQL syntax is crucial for querying, updating, and managing data stored in databases. This chapter covers the fundamental aspects of SQL syntax, from basic to advanced topics.

What is SQL?

SQL is a language used to communicate with databases. It is the standard language for relational database management systems. SQL statements are used to perform tasks such as updating data in a database or retrieving data from a database.

History of SQL

1970s: SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce.
1986: SQL became a standard of the American National Standards Institute (ANSI).
1987: SQL became a standard of the International Organization for Standardization (ISO).

Importance of SQL

Data Management: SQL is essential for storing, retrieving, and manipulating data.
Industry Standard: SQL is used by most database management systems (DBMS), such as MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.
Data Analysis: SQL is widely used in data analysis and reporting.

SQL Statements

SQL statements are the instructions that tell the database what actions to perform. They can be broadly categorized into the following types:

Data Definition Language (DDL)

Defines the structure of the database.

CREATE: Creates objects in the database.
ALTER: Alters the structure of the database.
DROP: Deletes objects from the database.

Data Manipulation Language (DML)

Manages data within schema objects.

SELECT: Retrieves data from the database.
INSERT: Inserts data into a table.
UPDATE: Updates existing data within a table.
DELETE: Deletes records from a table.

Data Control Language (DCL)

Controls access to data in the database.

GRANT: Gives a privilege to a user.
REVOKE: Takes back privileges granted to a user.

Transaction Control Language (TCL)

Manages changes made by DML statements.

COMMIT: Saves the work done.
ROLLBACK: Restores the database to the last committed state.
SAVEPOINT: Sets a point within a transaction to which a rollback can be performed.

Basic SQL Syntax

The general structure of an SQL statement is as follows:

				
					<keyword> <parameters>;

				
					SELECT * FROM employees;

SELECT: Keyword to retrieve data.
*: Indicates all columns.
FROM: Keyword to specify the table.
employees: The name of the table.

employee_id	first_name	last_name	department
1	John	Doe	HR
2	Jane	Smith	Finance

Data Definition Language (DDL)

Creating a Database

				
					CREATE DATABASE mydatabase;

CREATE DATABASE: Command to create a new database.
mydatabase: The name of the new database.

Creating a Table

				
					CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

CREATE TABLE: Command to create a new table.
employees: The name of the table.
Columns defined within parentheses: employee_id, first_name, last_name, and department.

Altering a Table

				
					ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

ALTER TABLE: Command to modify an existing table.
ADD COLUMN: Adds a new column to the table.
salary DECIMAL(10, 2): Specifies the new column name and its data type.

Dropping a Table

				
					DROP TABLE employees;

DROP TABLE: Command to delete a table.
employees: The name of the table to be deleted.

Data Manipulation Language (DML)

Inserting Data into a Table

				
					INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (1, 'John', 'Doe', 'HR');

INSERT INTO: Command to insert new data into a table.
employees: The name of the table.
Column names within parentheses: employee_id, first_name, last_name, department.
VALUES: Keyword followed by the values to insert.

Updating Data in a Table

				
					UPDATE employees
SET department = 'Finance'
WHERE employee_id = 1;

UPDATE: Command to update existing data.
employees: The name of the table.
SET: Keyword to specify the new values.
department = 'Finance': The new value for the department column.
WHERE: Clause to specify which rows to update.

Deleting Data from a Table

				
					DELETE FROM employees
WHERE employee_id = 1;

DELETE FROM: Command to delete data from a table.
employees: The name of the table.
WHERE: Clause to specify which rows to delete.

Retrieving Data from a Table

				
					SELECT * FROM employees;

SELECT: Command to retrieve data.
*: Indicates all columns.
FROM: Keyword to specify the table.
employees: The name of the table.

Joins

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

Inner Join

				
					SELECT employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

INNER JOIN: Combines rows from both tables where the condition is met.
ON: Clause to specify the join condition.

first_name	department_name
John	HR
Jane	Finance

Left Join

				
					SELECT employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

LEFT JOIN: Combines rows from the left table with matching rows from the right table and returns all rows from the left table.

first_name	department_name
John	HR
Jane	Finance
Mike	NULL

Subqueries

Subqueries are nested queries within a larger query.

				
					SELECT first_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR');

Subquery: SELECT department_id FROM departments WHERE department_name = 'HR'
Outer query: SELECT first_name FROM employees WHERE department_id = (subquery)

first_name
John

Aggregate Functions

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

COUNT()

				
					SELECT COUNT(*) FROM employees;

COUNT(*): Returns the number of rows in the table.

count
3

SUM()

				
					SELECT SUM(salary) FROM employees;

SUM(salary): Returns the total sum of the salary column.

sum
150000

AVG()

				
					SELECT AVG(salary) FROM employees;

AVG(salary): Returns the average value of the salary column.

avg
50000

MAX()

				
					SELECT MAX(salary) FROM employees;

MAX(salary): Returns the maximum value in the salary column.

max
70000

MIN()

				
					SELECT MIN(salary) FROM employees;

MIN(salary): Returns the minimum value in the salary column.

min
30000

Data Control Language (DCL)

Granting Privileges

				
					GRANT SELECT ON employees TO user1;

GRANT: Command to give privileges.
SELECT ON employees: Specifies the privilege and the table.
TO user1: Specifies the user to grant the privilege to.

Revoking Privileges

				
					REVOKE SELECT ON employees FROM user1;

REVOKE: Command to take back privileges.
SELECT ON employees: Specifies the privilege and the table.
FROM user1: Specifies the user to revoke the privilege from.

Transaction Control Language (TCL)

Commit

				
					BEGIN;
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (3, 'Mike', 'Johnson', 'IT');
COMMIT;

BEGIN: Starts a new transaction.
INSERT: Adds a new row to the employees table.
COMMIT: Saves the transaction.

Rollback

				
					BEGIN;
DELETE FROM employees WHERE employee_id = 3;
ROLLBACK;

BEGIN: Starts a new transaction.
DELETE: Deletes a row from the employees table.
ROLLBACK: Reverts the transaction.

Savepoint

				
					BEGIN;
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (4, 'Anna', 'Williams', 'IT');
SAVEPOINT sp1;
UPDATE employees SET department = 'HR' WHERE employee_id = 4;
ROLLBACK TO sp1;
COMMIT;

SAVEPOINT sp1: Creates a savepoint within the transaction.
ROLLBACK TO sp1: Rolls back to the savepoint.
COMMIT: Saves the transaction.

This chapter covered the basics of SQL syntax, from defining databases and tables to manipulating data and managing transactions. Understanding these fundamental concepts is essential for effectively working with relational databases. By mastering SQL syntax, you can efficiently query, update, and manage your data. Happy coding !❤️

SQL Syntax Basics

What is SQL?

History of SQL

Importance of SQL

SQL Statements

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Control Language (DCL)

Transaction Control Language (TCL)

Basic SQL Syntax

Data Definition Language (DDL)

Creating a Database

Creating a Table

Altering a Table

Dropping a Table

Data Manipulation Language (DML)

Inserting Data into a Table

Updating Data in a Table

Deleting Data from a Table

Retrieving Data from a Table

Joins

Inner Join

Left Join

Subqueries

Aggregate Functions

COUNT()

SUM()

AVG()

MAX()

MIN()

Data Control Language (DCL)

Granting Privileges

Revoking Privileges

Transaction Control Language (TCL)

Commit

Rollback

Savepoint

Table of Contents

Explore

Popular Tutorials

Contact here