Have you ever wondered how websites and apps store and manage their vast amounts of data? Behind the scenes, a powerful language called SQL acts as the silent maestro, orchestrating the flow of information. But for the uninitiated, SQL syntax can seem like an arcane code, shrouded in mystery.
Take a look at all the topics that are discussed in this article:
Fear not, curious explorer! So, buckle up and get ready to embark on a journey through the fundamentals of SQL syntax.
What is SQL Syntax?
SQL syntax refers to the set of rules and guidelines that define how SQL statements should be written and structured correctly. It specifies the proper use of SQL keywords, identifiers, expressions, and clauses to construct valid SQL queries for data manipulation and management tasks.
SQL Case Sensitivity
SQL (Structured Query Language) is generally case-insensitive when it comes to keywords and statements. This means that keywords like SELECT, INSERT, UPDATE, DELETE, and so on, can be written in either uppercase or lowercase, or a combination of both, and they will be treated the same way by the SQL engine. For example, SELECT, select, and SeLeCt all have the same meaning in SQL statements.
However, MySQL, which is a popular open-source relational database management system (RDBMS), makes a distinction between table names based on their case. This behavior is known as case-sensitive table names.
In MySQL, if you create a table with a specific case for its name (e.g., MyTable), you need to refer to that table using the same case when writing SQL queries. For instance, if you try to query the MyTable table using SELECT * FROM mytable;, MySQL will treat mytable as a different table name and return an error because it cannot find the table with that exact name.
SQL Statement
An SQL statement is a command or instruction written in the Structured Query Language (SQL) that is used to interact with a relational database management system (RDBMS). It allows users to perform various operations such as creating, modifying, and retrieving data from databases.
Here’s a simple example of a SQL statement:
SELECT * FROM customers;
This SQL statement is a basic SELECT query that retrieves all columns (*) from the customer’s table. It’s a common query used to fetch all records from a specific table in a database.
SQL Commands and Statements
Let’s discuss each statement in short one by one with syntax and one example:
1. SELECT Statement
The SELECT statement is used in SQL to retrieve data from a database.
Syntax of SELECT Statement:
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_n ASC|DESC
LIMIT num_rows;
Example of SELECT Statement:
SELECT name, salary
FROM employees
WHERE age > 30 AND department = 'IT'
ORDER BY salary DESC;
This query will return the name and salary columns from the employees table, filtered to include only employees who are older than 30 and work in the ‘IT’ department, sorted in descending order by the salary column.
2. UPDATE Statement
The UPDATE statement is used in SQL to modify existing data in a table.
Syntax of UPDATE Statement:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example of UPDATE Statement:
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'IT';
This query will update the salary column for all employees whose department is ‘IT’. The new salary will be calculated by multiplying the current salary by 1.1 (a 10% increase).
3. DELETE Statement
The DELETE statement is used in SQL to remove rows from a table.
Syntax of DELETE Statement:
DELETE FROM table_name
WHERE condition;
Example of DELETE Statement:
DELETE FROM employees
WHERE age > 60;
This query will remove all rows from the employees table where the age column is greater than 60.
4. CREATE TABLE Statement
The CREATE TABLE statement is used in SQL to create a new table in a database.
Syntax of CREATE TABLE Statement:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
...
);
Example of CREATE TABLE Statement:
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 18),
grade DECIMAL(3, 2) DEFAULT 0.0
);
In this example:
- id is an integer column that serves as the primary key and is set to auto-increment.
- name is a VARCHAR column that stores up to 50 characters and cannot be NULL.
- age is an integer column with a CHECK constraint that ensures the age is at least 18.
- grade is a decimal column with a precision of 3 digits and a scale of 2 decimal places. It has a default value of 0.0.
After executing this CREATE TABLE statement, a new table named students will be created in the database with the specified columns and constraints.
5. ALTER TABLE Statement
The ALTER TABLE statement is used in SQL to modify the structure of an existing table. It allows you to add, modify, or drop columns, as well as add or drop constraints.
Syntax of ALTER TABLE Statement:
ALTER TABLE table_name
operation;
The operation can be one of the following:
1. Add a new column
ALTER TABLE table_name
ADD COLUMN new_column_name datatype constraint;
2. Modify an existing column
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype new_constraint;
3. Drop a column
ALTER TABLE table_name
DROP COLUMN column_name;
4. Add a constraint
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;
5. Drop a constraint
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Example of ALTER TABLE Statement:
-- Add a new column 'email'
ALTER TABLE students
ADD COLUMN email VARCHAR(50);
-- Modify the 'age' column to allow null values
ALTER TABLE students
MODIFY COLUMN age INT NULL;
After executing these statements, the students table will have a new column email of type VARCHAR(50), and the age column will now allow null values.
6. DROP TABLE Statement
The DROP TABLE statement is used in SQL to remove an entire table from a database.
Syntax of DROP TABLE Statement:
DROP TABLE table_name;
Example of DROP TABLE Statement:
DROP TABLE students;
After executing this statement, the students table and all its data will be permanently deleted from the database.
Note: The DROP TABLE statement is a destructive operation that cannot be undone. Once you drop a table, all data in that table is lost forever. Therefore, it’s essential to use this command with caution, especially on production databases. It’s generally a good practice to first take a backup of your data before dropping a table.
7. CREATE DATABASE Statement
The CREATE DATABASE statement is used in SQL to create a new database.
Syntax of CREATE DATABASE Statement:
CREATE DATABASE database_name;
Example of CREATE DATABASE Statement:
CREATE DATABASE school_management;
After executing this statement, a new database with the name school_management will be created in your database management system (DBMS).
8. DROP DATABASE Statement
The DROP DATABASE statement is used in SQL to remove an entire database from a database management system (DBMS).
Syntax of DROP DATABASE Statement:
DROP DATABASE database_name;
Example of DROP DATABASE Statement:
DROP DATABASE school_management;
After executing this statement, the school_management database and all its tables, views, stored procedures, and other objects will be permanently deleted from the DBMS.
Note: The DROP DATABASE statement is a destructive operation that cannot be undone. Once you drop a database, all data and objects within that database are lost forever. Therefore, it’s crucial to use this command with extreme caution, especially on production databases. It’s generally recommended to take a backup of your database before dropping it.
9. INSERT INTO Statement
The INSERT INTO statement is used in SQL to insert new rows of data into a table.
Syntax of INSERT INTO Statement:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example of INSERT INTO Statement:
INSERT INTO students (name, age, grade)
VALUES ('John Doe', 20, 3.5);
This statement will insert a new row into the students table with the values ‘John Doe’ for the name column, 20 for the age column, and 3.5 for the grade column. The id column is not explicitly specified, assuming it is an auto-incrementing primary key column.
If you want to insert multiple rows at once, you can use the following syntax:
INSERT INTO students (name, age, grade)
VALUES
('Jane Smith', 21, 3.8),
('Michael Johnson', 19, 3.2),
('Emily Davis', 22, 3.9);
This statement will insert three new rows into the students table with the specified values for each row.
10. TRUNCATE TABLE Statement
The TRUNCATE TABLE statement is used in SQL to remove all rows from a table. It is similar to the DELETE statement, but it has some important differences.
Syntax of TRUNCATE TABLE Statement:
TRUNCATE TABLE table_name;
Example of TRUNCATE TABLE Statement:
TRUNCATE TABLE students;
After executing this statement, all rows from the students table will be removed, and the table will be empty.
11. DESCRIBE Statement
The DESCRIBE statement (or DESC for short) is used in SQL to get information about the columns in a table. It displays the column names, data types, and other metadata about the columns.
Syntax of DESCRIBE Statement:
DESCRIBE table_name;
OR
DESC table_name;
Example of DESCRIBE Statement:
DESCRIBE students;
The DESCRIBE statement is a useful tool for quickly inspecting the structure of a table and getting information about its columns.
12. DISTINCT Clause
The DISTINCT clause in SQL is used to retrieve unique values from a column or a set of columns in a table. It eliminates duplicate rows from the query result set.
Syntax of DISTINCT Clause:
SELECT DISTINCT column1, column2, ...
FROM table_name;
Example of DISTINCT Clause:
SELECT DISTINCT age
FROM students;
This query will return a result set containing only the unique ages present in the students table, eliminating any duplicate ages.
13. COMMIT Statement
The COMMIT statement is used in SQL to save the changes made to the database within a transaction. It is used to mark the end of a successful transaction, making the changes permanent.
Syntax of COMMIT Statement:
COMMIT;
Example of COMMIT Statement:
START TRANSACTION;
UPDATE accounts
SET balance = balance - 100
WHERE name = 'Account A';
UPDATE accounts
SET balance = balance + 100
WHERE name = 'Account B';
COMMIT;
Here’s what happens:
- START TRANSACTION; begins a new transaction.
- The first UPDATE statement subtracts $100 from the balance of ‘Account A’.
- The second UPDATE statement adds $100 to the balance of ‘Account B’.
- COMMIT; saves the changes made by the two UPDATE statements and makes them permanent in the database.
14. ROLLBACK Statement
The ROLLBACK statement is used in SQL to undo the changes made within a transaction. It is used to revert the database to its previous consistent state before the transaction was initiated.
Syntax of ROLLBACK Statement:
ROLLBACK;
Example of ROLLBACK Statement:
START TRANSACTION;
UPDATE accounts
SET balance = balance - 100
WHERE name = 'Account A' AND balance >= 100;
UPDATE accounts
SET balance = balance + 100
WHERE name = 'Account B';
-- Check if the first UPDATE statement affected any rows
IF @@ROWCOUNT = 0 THEN
-- If no rows were affected, account A did not have enough balance
ROLLBACK; -- Undo the entire transaction
PRINT 'Insufficient balance in Account A';
ELSE
COMMIT; -- Save the changes made by the transaction
END IF;
The ROLLBACK statement is crucial for maintaining data integrity and ensuring that the database remains in a consistent state if any part of a transaction fails or encounters an error. It allows you to undo the changes made within the transaction and revert the database to its previous state.
15. CREATE INDEX Statement
The CREATE INDEX statement is used in SQL to create an index on one or more columns in a table. Indexes are data structures that store a subset of data from a table in a way that allows for faster data retrieval.
Syntax of CREATE INDEX Statement:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, ...);
Example of CREATE INDEX Statement:
CREATE INDEX idx_name ON students (name);
This statement will create a non-unique index named idx_name on the name column of the students table.
16. DROP INDEX Statement
The DROP INDEX statement is used in SQL to remove an existing index from a table.
Syntax of DROP INDEX Statement:
DROP INDEX index_name ON table_name;
Example of DROP INDEX Statement:
DROP INDEX idx_name ON students;
This statement will remove the index named idx_name from the students table.
17. USE Statement
The USE statement is used in SQL to select a specific database to work within a database management system (DBMS).
Syntax of USE Statement:
USE database_name;
Example of USE DATABASE Statement:
USE school_management;
After executing this statement, the school_management database becomes the active database, and any subsequent SQL statements will be executed against this database until you switch to another database or end the current session.
It’s important to note that the USE statement is specific to certain database systems, such as MySQL, MariaDB, and SQL Server. Other database systems, like PostgreSQL and Oracle, don’t have a dedicated USE statement. Instead, you specify the database name when establishing the connection or by providing it as a prefix to the table name (e.g., database_name.table_name).