SQL Quick Reference Guide

SQL, or Structured Query Language, is a standard programming language specifically designed for managing and manipulating relational databases. It allows users to create, modify, and query data efficiently. This SQL Quick Reference Guide will walk you through essential SQL commands, data types, operators, functions, clauses, constraints, indexes, views, and transactions, along with examples that are easy to understand for beginners.

SQL Commands

A. Data Query Language (DQL)

DQL is used for querying the database to retrieve data. The most common command is:

SELECT column1, column2 FROM table_name;

B. Data Definition Language (DDL)

DDL commands define and modify database schema. Key commands include:

• CREATE – To create a new database object
• ALTER – To modify existing database objects
• DROP – To delete database objects

CREATE TABLE students (id INT, name VARCHAR(100));

C. Data Manipulation Language (DML)

DML is used to manipulate data in the database. Key commands include:

• INSERT – To add new rows of data
• UPDATE – To modify existing data
• DELETE – To remove data

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

D. Data Control Language (DCL)

DCL commands control access to data within the database. Common commands include:

• GRANT – To give user access privileges
• REVOKE – To remove user access privileges

GRANT SELECT ON students TO user_name;

SQL Data Types

Data Type	Description
INT	Integer data type
VARCHAR(n)	Variable-length character string
DATE	Stores date values
FLOAT	Stores floating-point numbers

SQL Operators

A. Comparison Operators

Used to compare two values. Common operators include:

• = – Equal to
• != – Not equal to
• > – Greater than
• < – Less than

B. Logical Operators

Used to combine multiple conditions:

• AND – Returns true if both conditions are true
• OR – Returns true if at least one condition is true
• NOT – Returns true if the condition is false

C. Other Operators

Includes various functions like:

• BETWEEN – Checks if a value is within a range
• LIKE – Searches for a specified pattern
• IN – Checks if a value exists in a set

SQL Functions

A. Aggregate Functions

These functions perform calculations on a set of values:

• COUNT() – Returns the number of rows
• SUM() – Returns the sum of a numeric column
• AVG() – Returns the average value

SELECT COUNT(*) FROM students;

B. String Functions

Functions to manipulate string values:

• LENGTH() – Returns the length of a string
• UPPER() – Converts a string to uppercase
• LOWER() – Converts a string to lowercase

C. Numeric Functions

Functions that perform calculations on numbers:

• ROUND() – Rounds a number to a specified decimal
• ABS() – Returns the absolute value

D. Date Functions

Functions that manipulate date and time values:

• NOW() – Returns the current date and time
• DATEDIFF() – Returns the difference between two dates

SQL Clauses

A. SELECT

The SELECT clause is used to specify which columns to retrieve:

SELECT name FROM students;

B. FROM

Used to specify the table from which to retrieve data:

SELECT * FROM students;

C. WHERE

Used to filter records based on a condition:

SELECT * FROM students WHERE age > 18;

D. ORDER BY

Used to sort the result set:

SELECT * FROM students ORDER BY name ASC;

E. GROUP BY

Groups rows that have the same values in specified columns:

SELECT COUNT(*), age FROM students GROUP BY age;

F. HAVING

Used to filter groups based on a condition:

SELECT age, COUNT(*) FROM students GROUP BY age HAVING COUNT(*) > 1;

G. JOIN

Joins rows from two or more tables based on a related column:

1. INNER JOIN

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

2. LEFT JOIN

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

3. RIGHT JOIN

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

4. FULL JOIN

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

5. CROSS JOIN

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

SQL Constraints

Constraint	Description
NOT NULL	Ensures a column cannot have a NULL value
UNIQUE	Ensures all values in a column are different
PRIMARY KEY	Uniquely identifies each row
FOREIGN KEY	Links two tables together
CHECK	Ensures that all values in a column satisfy specific conditions
DEFAULT	Sets a default value for a column when no value is specified

SQL Indexes

Indexes are used to speed up the retrieval of rows from a database table. An index is created on a column of a table to enhance the efficiency of data access:

CREATE INDEX idx_student_name ON students (name);

SQL Views

Views are virtual tables created based on the result set of a SELECT query. A view can simplify complex queries:

CREATE VIEW student_view AS 
SELECT name, age FROM students WHERE age > 18;

SQL Transactions

A. COMMIT

The COMMIT command is used to save all changes made in a transaction:

COMMIT;

B. ROLLBACK

Used to undo changes made in a transaction:

ROLLBACK;

C. SAVEPOINT

Sets a point within a transaction to which you can later roll back:

SAVEPOINT savepoint_name;

Summary of SQL Commands

This guide has covered the foundational aspects of SQL, including various commands for querying, defining, manipulating, and controlling access to data. Below is a quick summary:

SQL Action	Command
Retrieve Data	SELECT
Create Table	CREATE TABLE
Insert Data	INSERT
Update Data	UPDATE
Delete Data	DELETE
Join Tables	INNER, LEFT, RIGHT, FULL, CROSS JOIN

FAQ

Q1: What is SQL?

A1: SQL stands for Structured Query Language, a standard language for managing and manipulating relational databases.

Q2: What are the main types of SQL commands?

A2: The main types include DQL, DDL, DML, and DCL.

Q3: How do I retrieve data from a database?

A3: You can retrieve data using the SELECT statement.

Q4: What is a JOIN in SQL?

A4: A JOIN is used to combine rows from two or more tables based on a related column.

Q5: How can I ensure data integrity in my database?

A5: You can use constraints like NOT NULL, UNIQUE, and FOREIGN KEY to maintain data integrity.