SQL Reference Guide - askthedev.com

Structured Query Language (SQL) is an essential tool for interacting with databases. Understanding SQL is crucial for anyone interested in data management, web development, or software engineering. This guide will provide a comprehensive overview of SQL, covering its syntax, commands, data types, and much more, with plenty of examples and clear explanations.

SQL Introduction

A. What is SQL?

SQL, or Structured Query Language, is a standardized programming language specifically designed for managing and manipulating relational databases. It allows users to perform various operations on data stored within these databases.

B. Advantages of SQL

Standardized Language: SQL is a standardized language supported by all major database systems.
Powerful Data Manipulation: SQL allows complex queries and data manipulation operations.
Data Integrity: SQL ensures data accuracy and integrity through constraints and transactions.
Scalable: SQL databases can handle large volumes of data efficiently.

C. SQL vs NoSQL

Feature	SQL	NoSQL
Data Structure	Relational (tables)	Non-relational (various formats)
Schema	Fixed schema	Dynamically schema
Use Cases	Complex queries	Big data, unstructured data
Examples	MySQL, PostgreSQL	MongoDB, Cassandra

SQL Syntax

A. SQL Statements

SQL consists of various statements which serve different purposes. These statements are categorized into four main types:

Data Query Language (DQL)
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)

B. SQL Keywords

Keywords are reserved words with a special meaning in SQL. Important keywords include:

SELECT – to select data from a database
INSERT – to insert data into a table
UPDATE – to modify existing data
DELETE – to remove data

SQL Data Types

A. Numeric Data Types

Data Type	Description
INT	Integer type for whole numbers
FLOAT	Floating point type for decimal numbers
DOUBLE	Double precision floating point

B. String Data Types

Data Type	Description
CHAR(n)	Fixed length string of length n
VARCHAR(n)	Variable length string, up to n characters
TEXT	Large amount of text data

C. Date and Time Data Types

Data Type	Description
DATE	Stores date value (YYYY-MM-DD)
TIME	Stores time value (HH:MM:SS)
DATETIME	Stores date and time value

D. Other Data Types

Other useful data types include:

BLOB: Binary Large Object for storing binary data
BOOLEAN: To store true/false values

SQL Operators

A. Arithmetic Operators

Arithmetic operators are used for mathematical calculations:

+ : Addition
– : Subtraction
* : Multiplication
/ : Division

B. Comparison Operators

Comparison operators are used to compare two values:

= : Equal to
!= or <> : Not equal to
> : Greater than
< : Less than

C. Logical Operators

Logical operators are used to combine multiple conditions:

AND : True if both conditions are true
OR : True if at least one condition is true
NOT : Inverts the truth value

D. Bitwise Operators

Bitwise operators perform bit-level operations:

& : Bitwise AND
| : Bitwise OR
^ : Bitwise XOR
~ : Bitwise NOT

SQL Functions

A. Aggregate Functions

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

Function	Description
COUNT()	Returns the number of rows that match a specified condition
SUM()	Returns the total sum of a numeric column
AVG()	Returns the average value of a numeric column
MAX()	Returns the maximum value in a set
MIN()	Returns the minimum value in a set

B. String Functions

String functions operate on string data types:

Function	Description
CONCAT()	Concatenates two or more strings
LENGTH()	Returns the length of a string
UPPER()	Converts a string to uppercase
LOWER()	Converts a string to lowercase

C. Date Functions

Date functions operate on date data types:

Function	Description
CURDATE()	Returns the current date
NOW()	Returns the current date and time
DATEDIFF()	Calculates the difference between two dates
DATE_FORMAT()	Formats a date value based on a specified format

D. Numeric Functions

Numeric functions perform operations on numeric data types:

Function	Description
ROUND()	Rounds a number to a specified number of decimal places
FLOOR()	Returns the largest integer value less than or equal to a number
CEIL()	Returns the smallest integer value greater than or equal to a number

E. Conversion Functions

Conversion functions convert data from one type to another:

Function	Description
CAST()	Converts a value to a specified data type
CONVERT()	Converts a value from one type to another

SQL Constraints

A. NOT NULL

The NOT NULL constraint ensures that a column cannot have a NULL value:

CREATE TABLE students (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL
);

B. UNIQUE

The UNIQUE constraint ensures that all values in a column are different:

CREATE TABLE students (
    id INT UNIQUE,
    name VARCHAR(50)
);

C. PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table and cannot contain NULL values:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

D. FOREIGN KEY

The FOREIGN KEY constraint links two tables together:

CREATE TABLE courses (
    id INT PRIMARY KEY,
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

E. CHECK

The CHECK constraint ensures that all values in a column satisfy a specific condition:

CREATE TABLE students (
    id INT,
    age INT CHECK (age >= 18)
);

F. DEFAULT

The DEFAULT constraint sets a default value for a column when no value is specified:

CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    registration_date DATE DEFAULT CURRENT_DATE
);

SQL Commands

A. Data Query Language (DQL)

1. SELECT

The SELECT statement is used to query data from a database:

SELECT name FROM students WHERE age > 20;

B. Data Definition Language (DDL)

1. CREATE

The CREATE statement creates a new table or database:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

2. ALTER

The ALTER statement modifies an existing table:

ALTER TABLE students ADD COLUMN age INT;

3. DROP

The DROP statement deletes a table or database:

DROP TABLE students;

C. Data Manipulation Language (DML)

1. INSERT

The INSERT statement adds new records to a table:

INSERT INTO students (id, name) VALUES (1, 'John Doe');

2. UPDATE

The UPDATE statement modifies existing records:

UPDATE students SET age = 21 WHERE id = 1;

3. DELETE

The DELETE statement removes records from a table:

DELETE FROM students WHERE id = 1;

D. Data Control Language (DCL)

1. GRANT

The GRANT statement provides access privileges to users:

GRANT SELECT ON students TO user1;

2. REVOKE

The REVOKE statement removes user privileges:

REVOKE SELECT ON students FROM user1;

SQL Joins

A. INNER JOIN

The INNER JOIN keyword returns records that have matching values in both tables:

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

B. LEFT JOIN

The LEFT JOIN keyword returns all records from the left table, and matched records from the right table:

SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id;

C. RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table, and matched records from the left table:

SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.id = courses.student_id;

D. FULL JOIN

The FULL JOIN keyword returns all records when there is a match in either left or right table records:

SELECT students.name, courses.course_name
FROM students
FULL JOIN courses ON students.id = courses.student_id;

E. CROSS JOIN

The CROSS JOIN returns the Cartesian product of two tables:

SELECT students.name, courses.course_name
FROM students
CROSS JOIN courses;

SQL Views

A. Creating a View

A VIEW is a virtual table based on the result-set of a SQL statement:

CREATE VIEW student_courses AS
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

B. Using Views

To use a view, simply query it like a table:

SELECT * FROM student_courses;

C. Updating a View

Updating a view can also propagate changes back to the base table depending on the view’s structure:

UPDATE student_courses SET course_name = 'Biology' WHERE name = 'John Doe';

SQL Transactions

A. ACID Properties

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable transactions in SQL.

askthedev.com Latest Articles