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 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.
Before Executing the Select statement we need to create a Database first.
Create Database Statement:
CREATE DATABASE DATABASE_NAME; --CREATE DATABASE STATEMENT USE TO CREATE NEW DATABASE IN YOU DATABASE SEVER AFTER CREATING YOU NEED TO CHANGE DATABASE WITH USE DATABASE STATEMENT USE DATABASE DATABASE_NAME
Simple Example of SQL statement:
SELECT "column_name" FROM "table_name";
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.
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
SELECT column_name1, column_name2, .…, column_nameN [ FROM table_name ] [ WHERE condition ] [ ORDER BY order_column_name1 [ ASC | DESC ], .... ];
SELECT Statement Example:
SELECT Emp_ID, First_Name, Last_Name, Salary, City FROM Employee_details WHERE Salary = 100000 ORDER BY Last_Name;
2. CREATE TABLE Statement
This SQL statement creates the new table in the SQL database.
Syntax of CREATE TABLE Statement:
CREATE TABLE table_name ( column_name1 data_type(size) [column1 constraint(s)], column_name2 data_type(size) [column2 constraint(s)], ..... ....., column_nameN data_type(size) [columnN constraint(s)], PRIMARY KEY(one or more col) );
--CREATE TABLE STATEMENT CREATE TABLE Employee_details( Emp_Id NUMBER(4) NOT NULL, First_name VARCHAR(30), Last_name VARCHAR(30), Salary Money, City VARCHAR(30), PRIMARY KEY (Emp_Id) );
3. 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.
--STATEMENT FOR INSERTING SINGLE ROW --SYNTAX INSERT INTO table_name(column_name1, column_name2, column_nameN) VALUES(value_1,value_2,value_N); --EXAMPLE INSERT INTO Employee_details(Emp_ID, First_name, Last_name, Salary, City) VALUES(101,'Suraj','Vishwakarma',5000,'Mumbai'); --INSERTING MULTIPLE ROWS AT A TIME INSERT INTO Employee_details ( Emp_ID, First_name, Last_name, Salary, City ) VALUES (101, 'Suraj', 'Vishwakarna', 50000, 'Mumbai'), (102, 'Poonam', 45000, 'Bihar'), (103, 'Ragibi', 55000, 'Mumbai');
4. UPDATE Statement
This SQL statement changes or modifies the stored data in the SQL database.
--SYNTAX UPDATE table_name SET column_name1 = new_value_1, column_name2 = new_value_2, ...., column_nameN = new_value_N [ WHERE CONDITION ]; --EXAMPLE UPDATE Employee_details SET Salary = 100000 WHERE Emp_ID = 10;
5. DELETE Statement
This SQL statement deletes the stored data from the SQL database.
--Syntax of DELETE Statement: DELETE FROM table_name [ WHERE CONDITION ]; --Example of DELETE Statement: DELETE FROM Employee_details WHERE First_Name = 'Sumit';
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: DROP TABLE [ IF EXISTS ] table_name1, table_name2, ……, table_nameN; --Example of DROP TABLE Statement: DROP TABLE Employee_details;
7. 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: DROP DATABASE database_name; --Example of DROP DATABASE Statement: DROP DATABASE Company;
8. TRUNCATE TABLE Statement
This SQL statement deletes all the stored records from the table of the SQL database.
--Syntax of TRUNCATE TABLE Statement: TRUNCATE TABLE table_name; --Example of TRUNCATE TABLE Statement: TRUNCATE TABLE Employee_details;