SQL Syntax
In this tutorial, we will help you understand about the different SQL Syntax concepts with the help of an example.
Every Structured Query Language starts with Keywords like select, insert, update, delete, drop, alter, create, use, show, and each statement end with a semicolon (;).
Even Semicolon separates each SQL statement in the database System when multiple SQL statements are executed.
We can execute Structured Query Language Keywords in both cases uppercase and lowercase, i.e., SQL is case insensitive.
Let’s learn about the SQL Syntax with the help of an example
1. SELECT Statement
The SELECT statement is used to fetch the record from a database.
The syntax of the SELECT statement is as follows:
SELECT * FROM table-name;Example of the SELECT statement
SELECT * FROM Students;| Student_Id | Student_Name | Student_Dept |
| 102 | Deepak Chaudhari | Civil Engineering |
| 103 | Megha Sonje | Information Technology |
| 104 | Ketan Badhan | Mechanical Engineering |
| 105 | Chetan Sangale | Mechanical Engineering |
| 106 | Anu Priya | Computer Engineering |
| 107 | Daya Jain | Chemical Engineering |
The above syntax displays all the records from the mentioned table name.
Use the below syntax to display the records from the specific column.
SELECT column_1, column_2, column_3 FROM table-name;Example:
SELECT Student_Id, Student_Name FROM Students;The output of the above query is as follows:
| Student_Id | Student_Name |
| 102 | Deepak Chaudhari |
| 103 | Megha Sonje |
| 104 | Ketan Badhan |
| 105 | Chetan Sangale |
| 106 | Anu Priya |
| 107 | Daya Jain |
2. CREATE Statement
CREATE statement is used to create a new table in the database with the specific name of the table and the name of each column followed by their data type in the table.
The syntax of the CREATE statement is as follows:
CREATE table table_name( Column_1 data type, Column_2 data type, Column_3 data type );Example of create statement
CREATE table Students( Student_Id int NOT NULL, Student_Name VarChar(50) NOT NULL, Student_Dept VarChar(50) NOT NULL, PRIMARY KEY(Student_Id));The output of the above query is as follows:
| Field | Type | Null | Key | Default | Extra |
| Student_Id | int(11) | NO | PRI | NULL | |
| Student_Name | varchar(50) | NO | NULL | ||
| Student_Dept | varchar(50) | NO | NULL |
3. DELETE Statement-
DELETE Statement is used to remove data from the database
The syntax of the above query is as follows:
DELETE FROM table_name;Example of DELETE Statement
DELETE FROM Students;The above query removes all the records from the mentioned table.
The below DELETE statement syntax is used to delete specific data from the table.
DELETE FROM table_name WHERE condition;Where clause specifies which data or record should be deleted
Example:
DELETE FROM Student_Course WHERE Scourse_Id = 6;The output of the above query is as follows:
| Student_Id | Student_Name |
| 1 | 102 |
| 2 | 102 |
| 1 | 104 |
| 3 | 103 |
| 5 | 106 |
| 7 | 107 |
4. ALTER Statement
The ALTER query is used to add new fields in an already existing table, remove fields, modify the fields and their data type, add constraints, drop the constraints, to rename the field name in an already existing table.
The syntax of the ALTER statement is as follows:
ALTER TABLE table_name ADD column_1 data type;The above syntax adds a new column in an already existing table.
Example
ALTER TABLE Students ADD DateofBirthdate;The syntax for to DROP column using the ALTER query is as follows:
ALTER TABLE table_name DROP COLUMN column_1;Example:
ALTER TABLE Students DROP COLUMN Student_City;The syntax for to MODIFY column using the ALTER query is as follows:
ALTER TABLE table_name MODIFY COLUMN datatype;Example:
ALTER TABLE Students MODIFY COLUMN DateofBirthyear;The output of the above query is as follows:
5. INSERT INTO Statement
INSERT INTO statement adds new data to existing data.
The syntax of the INSERT INTO statement is as follows:
INSERT INTO table_name (Column_1, Column_2, Column_3) VALUES (value_1, value_2, value_3);Example of INSERT INTO statement
INSERT INTO Students (Student_Id, Student_Name, Student_Dept) VALUES (108, ’Komal Maheshwari’, ‘Computer Engineering’);The output of the above query is as follows:
| Student_Id | Student_Name | Student_Dept |
| 102 | Deepak Chaudhari | Civil Engineering |
| 103 | Megha Sonje | Information Technology |
| 104 | Ketan Badhan | Mechanical Engineering |
| 105 | Chetan Sangale | Mechanical Engineering |
| 106 | Anu Priya | Computer Engineering |
| 107 | Daya Jain | Chemical Engineering |
| 108 | Komal Maheshwari | Computer Engineering |
6. UPDATE Statement
The UPDATE query is used to modify the specific data or all the data in a table.
The syntax of the UPDATE statement is as follows:
UPDATE table_name SET column_1 = value WHERE condition;Example of UPDATE Statement
UPDATE Student SET Student_Name = ‘Mangesh Maheshwari’ WHERE Student_Name = ‘Sonal Maheshwari’;The output of the above query is as follows:
7. DROP TABLE Statement
The DROP table query removes the table data and table schema from the database.
The syntax of the DROP TABLE query is as follows:
DROP TABLE table_name;Example of DROP TABLE Statement
DROP TABLE Students;The output of the above query is as follows:
8. TRUNCATE TABLE Statement
The TRUNCATE TABLE Statement removes the records from the table without disturbing the table schema.
The syntax of the TRUNCATE TABLE statement is as follows:
TRUNCATE TABLE table_name;Example of TRUNCATE TABLE Statement
TRUNCATE TABLE Students;
The output of the above query is as follows:
9. CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a new database
The syntax of the CREATE DATABASE is as follows:
CREATE DATABASE DataBase_Name;Example of CREATE DATABASE statement
CREATE DATABASE College;
The output of the above query is as follows:
10. DROP DATABASE Statement
The DROP DATABASE statement is used to remove an already existing database.
The syntax of the DROP DATABASE statement is as follows:
DROP DATABASE DataBase_Name;Example of DROP DATABASE statement
DROP DATABASE College;The output of the above query is as follows:
11. SELECT DISTINCT Statement
The SELECT DISTINCT statement returns a unique value in the specified columns.
The syntax of SELECT DISTINCT statement is as follows:
SELECT DISTINCT column_1, column_2 FROM table_name;Example
SELECT DISTINCT Student_Dept FROM Students;The output of the above query is as follows:
12. INNER Join Statement
The INNER JOIN statement is used to join the table and display similar records in the tables.
The syntax of the INNER JOIN Statement is as follows:
SELECT table_name_1.column_1 FROM table_name_1 INNER JOIN table_name_2 ON table_name_1.column_1 = table_name_2.column_1;Example
SELECT Students.Student_name FROM Students INNER JOIN Student_Course ON Students.Student_Id = Student_Course.Student.Id;The output of the above query is as follows:
13. LEFT JOIN Statement
The LEFT JOIN statement will return all records from the left table and the common records from the right table. If records from the right table are not common, it will return a null value.
The syntax of the LEFT JOIN Statement is as follows:
SELECT table_name_1.column_1 FROM table_name_1 LEFT JOIN table_name_2 ON table_name_1.column_1 = table_name_2.column_1;Example
SELECT Students.Student_Name, Student_Course.SCourse_Id FROM Students LEFT JOIN Student_Course ON Students.Student_Id = Student_Course.Student_Id;The output of the above query is as follows:
14. RIGHT JOIN Statement
The Right Join query display all the records of the table on the right side of the join and common records for the table on the left side of the join. The result-set will contain null for the records for which there is no similar record on the left side.
The syntax of the RIGHT JOIN Statement is as follows:
SELECT table_name_1.column_1 FROM table_name_1 RIGHT JOIN table_name_2 ON table_name_1.column_1 = table_name_2.column_1;Example
SELECT Students.Student_Name, Student_Course.SCourse_Id FROM Students RIGHT JOIN Student_Course ON Students.Student_Id = Student_Course.Student_Id;The output of the above query is as follows:
