SQL Syntax
SQL Syntax
When you want to do some operations on the data in the database, then you must have to write the query in the predefined syntax of SQL.
The syntax of the structured query language is a unique set of rules and guidelines, which is not case-sensitive. Its Syntax is defined and maintained by the ISO and ANSI standards.
Following are some most important points about the SQL syntax which are to remember:
- You can write the keywords of SQL in both uppercase and lowercase, but writing the SQL keywords in uppercase improves the readability of the SQL query.
- SQL statements or syntax are dependent on text lines. We can place a single SQL statement on one or multiple text lines.
- You can perform most of the action in a database with SQL statements.
- SQL syntax depends on relational algebra and tuple relational calculus.
SQL Statement
SQL statements tell the database what operation you want to perform on the structured data and what information you would like to access from the database
The statements of SQL are very simple and easy to use and understand. They are like plain English but with a particular syntax.
Simple Example of SQL statement:
Each SQL statement begins with any of the SQL keywords and ends with the semicolon (;). The semicolon is used in the SQL for separating the multiple Sql statements which are going to execute in the same call. In this SQL tutorial, we will use the semicolon (;) at the end of each SQL query or statement
Most Important SQL Commands and Statements
- Select Statement
- Update Statement
- Delete Statement
- Create Table Statement
- Alter Table Statement
- Drop Table Statement
- Create Database Statement
- Drop Database Statement
- Insert Into Statement
- Truncate Table Statement
- Describe Statement
- Distinct Clause
- Commit Statement
- Rollback Statement
- Create Index Statement
- Drop Index Statement
- Use Statement
Let's discuss each statement in short one by one with syntax and one example:
1. SELECT Statement
This SQL statement reads the data from the SQL database and shows it as the output to the database user.
Syntax of SELECT Statement:
[ FROM table_name ]
[ WHERE condition ]
[ ORDER BY order_column_name1 [ ASC | DESC ], .... ];
Example of SELECT Statement:
FROM Employee_details
WHERE Salary = 100000
ORDER BY Last_Name
This example shows the Emp_ID, First_Name, Last_Name, Salary, and City of those employees from the Employee_details table whose Salary is 100000. The output shows all the specified details according to the ascending alphabetical order of Last_Name.
3. UPDATE Statement
This SQL statement changes or modifies the stored data in the SQL database.
Syntax of UPDATE Statement:
SET column_name1 = new_value_1, column_name2 = new_value_2, ...., column_nameN = new_value_N
[ WHERE CONDITION ];
Example of UPDATE Statement:
SET Salary = 100000
WHERE Emp_ID = 10;
This example changes the Salary of those employees of the Employee_details table whose Emp_ID is 10 in the table.
3. DELETE Statement
This SQL statement deletes the stored data from the SQL database.
Syntax of DELETE Statement:
[ WHERE CONDITION ];
Example of DELETE Statement:
WHERE First_Name = 'Sumit';
This example deletes the record of those employees from the Employee_details table whose First_Name is Sumit in the table.
4. CREATE TABLE Statement
This SQL statement creates the new table in the SQL database.
CREATE TABLE table_name
(
column_name1 data_type [column1 constraint(s)],
column_name2 data_type [column2 constraint(s)],
.....
.....,
column_nameN data_type [columnN constraint(s)],
PRIMARY KEY(one or more col)
);
Example of CREATE TABLE Statement:
Emp_Id NUMBER(4) NOT NULL,
First_name VARCHAR(30),
Last_name VARCHAR(30),
Salary Money,
City VARCHAR(30),
PRIMARY KEY (Emp_Id)
);
This example creates the table Employee_details with five columns or fields in the SQL database. The fields in the table are Emp_Id, First_Name, Last_Name, Salary, and City. The Emp_Id column in the table acts as a primary key, which means that the Emp_Id column cannot contain duplicate values and null values.
5. ALTER TABLE Statement
This SQL statement adds, deletes, and modifies the columns of the table in the SQL database.
Syntax of ALTER TABLE Statement:
The above SQL alter statement adds the column with its datatype in the existing database table.
The above 'SQL alter statement' renames the old column name to the new column name of the existing database table.
The above SQL alter statement deletes the column of the existing database table.
Example of ALTER TABLE Statement:
ADD Designation VARCHAR(18);
This example adds the new field whose name is Designation with size 18 in the Employee_details table of the SQL database.
6. DROP TABLE Statement
This SQL statement deletes or removes the table and the structure, views, permissions, and triggers associated with that table.
Syntax of DROP TABLE Statement:
table_name1, table_name2, ……, table_nameN;
The above syntax of the drop statement deletes specified tables completely if they exist in the database.
Example of DROP TABLE Statement:
This example drops the Employee_details table if it exists in the SQL database. This removes the complete information if available in the table.
7. CREATE DATABASE Statement
This SQL statement creates the new database in the database management system.
Syntax of CREATE DATABASE Statement:
Example of CREATE DATABASE Statement:
The above example creates the company database in the system.
8. DROP DATABASE Statement
This SQL statement deletes the existing database with all the data tables and views from the database management system.
Syntax of DROP DATABASE Statement:
Example of DROP DATABASE Statement:
The above example deletes the company database from the system.
9. INSERT INTO Statement
This SQL statement inserts the data or records in the existing table of the SQL database. This statement can easily insert single and multiple records in a single query statement.
Syntax of insert a single record:
(
column_name1,
column_name2, .…,
column_nameN
)
VALUES
(value_1,
value_2, ..…,
value_N
);
Example of insert a single record:
(
Emp_ID,
First_name,
Last_name,
Salary,
City
)
VALUES
(101,
Akhil,
Sharma,
40000,
Bangalore
);
This example inserts 101 in the first column, Akhil in the second column, Sharma in the third column, 40000 in the fourth column, and Bangalore in the last column of the table Employee_details.
Syntax of inserting a multiple records in a single query:
( column_name1, column_name2, .…, column_nameN)
VALUES (value_1, value_2, ..…, value_N), (value_1, value_2, ..…, value_N),….;
Example of inserting multiple records in a single query:
( Emp_ID, First_name, Last_name, Salary, City )
VALUES (101, Amit, Gupta, 50000, Mumbai), (101, John, Aggarwal, 45000, Calcutta), (101, Sidhu, Arora, 55000, Mumbai);
This example inserts the records of three employees in the Employee_details table in the single query statement.
10. TRUNCATE TABLE Statement
This SQL statement deletes all the stored records from the table of the SQL database.
Syntax of TRUNCATE TABLE Statement:
Example of TRUNCATE TABLE Statement:
This example deletes the record of all employees from the Employee_details table of the database.
11. DESCRIBE Statement
This SQL statement tells something about the specified table or view in the query.
Syntax of DESCRIBE Statement:
Example of DESCRIBE Statement:
This example explains the structure and other details about the Employee_details table.
12. DISTINCT Clause
This SQL statement shows the distinct values from the specified columns of the database table. This statement is used with the SELECT keyword.
Syntax of DISTINCT Clause:
FROM table_name;
Example of DISTINCT Clause:
FROM Employee_details;
This example shows the distinct values of the City and Salary column from the Employee_details table.
13. COMMIT Statement
This SQL statement saves the changes permanently, which are done in the transaction of the SQL database.
Syntax of COMMIT Statement:
Example of COMMIT Statement:
WHERE salary = 30000;
COMMIT;
This example deletes the records of those employees whose Salary is 30000 and then saves the changes permanently in the database.
14. ROLLBACK Statement
This SQL statement undo the transactions and operations which are not yet saved to the SQL database.
Syntax of ROLLBACK Statement:
Example of ROLLBACK Statement:
WHERE City = Mumbai;
ROLLBACK;
This example deletes the records of those employees whose City is Mumbai and then undo the changes in the database.
15. CREATE INDEX Statement
This SQL statement creates the new index in the SQL database table.
Syntax of CREATE INDEX Statement:
ON table_name ( column_name1, column_name2, …, column_nameN );
Example of CREATE INDEX Statement:
ON employee_details (First_Name);
This example creates an index idx_First_Name on the First_Name column of the Employee_details table.
16. DROP INDEX Statement
This SQL statement deletes the existing index of the SQL database table.
Syntax of DROP INDEX Statement:
Example of DROP INDEX Statement:
This example deletes the index idx_First_Name from the SQL database.
17. USE Statement
This SQL statement selects the existing SQL database. Before performing the operations on the database table, you have to select the database from the multiple existing databases.
Syntax of USE Statement:
Example of USE DATABASE Statement:
