SQL Queries
In a database, queries are used to request the result set of data from the table or action on the records.
A Query can answer your simple or complicated question, perform the operation, combine records from the different tables, add records into the Database or the table, delete records, and modify records from the Database or the table.
In an SQL database, we can execute multiple queries such as insert data into the table, modify the data, delete the table, select data from the table, modification into the table, etc.
SQL queries that we execute on the Database and table are as follows:
- SQL CREATE query
- SQL INSERT query
- SQL SELECT query
- SQL UPDATE query
- SQL DELETE query
- SQL DROP query
- SQL TRUNCATE query
- SQL ALTER query
We will look at each query one by one with syntax and examples.
SQL CREATE query:
SQL CREATE query is used to create a database, table, index, view, trigger, sequence, and function.
The syntax for SQL CREATE Database:
CREATE DATABASE Database_Name;
CREATE DATABASE is a keyword used to create a Database followed by a database name.
Syntax for SQL CREATE Table:
CREATE TABLE Table_Name( Column_Name_1 data type column_constraint, Column_Name_2 data type column_constraint, Column_Name_3 data type column_constraint), Column_Name_4 data type column_constraint), Column_Name_5 data type column_constraint);
CREATE TABLE is a keyword used to create a table followed by table name and column definition.
We will create one table named Diploma_Student with Nine columns:
CREATE TABLE Diploma_Student(Student_Id int NOT NULL, Student_Name varchar(40) NOT NULL, First_Sem int, Second_Sem int, Third_Sem int, Fourth_Sem int, Fifth_Sem int, Sixth_Sem int, Total int, PRIMARY KEY(Student_Id));
In the above example, we have created a Diploma_Student table with following columns.
We will now use the DESC keyword followed by table name:
| Field | Type | Null | Key | Default | Extra |
| Student_Id | int(11) | NO | PRI | NULL | |
| Student_Name | varchar(40) | NO | NULL | ||
| First_Sem | int(11) | YES | NULL | ||
| Second_Sem | int(11) | YES | NULL | ||
| Third_Sem | int(11) | YES | NULL | ||
| Fourth_Sem | int(11) | YES | NULL | ||
| Fifth_Sem | int(11) | YES | NULL | ||
| Sixth_Sem | int(11) | YES | NULL | ||
| Total | int(11) | YES | NULL |
SQL INSERT query:
We use the SQL INSERT query to add records in the empty table, or add some extra records in the existing table.
The syntax for SQL INSERT Query:
INSERT INTO Table_Name Values(Value1, Vlaue2, Value3, Value4, Value5);
The above query is used to add records in all table columns.
If you want to add records in the selected columns, use the following insert syntax:
INSERT INTO Table_Name(Column1, Column2, Column3, Column4) Values(Value1, Value2, Value3, Value4);
In the following example, we will add six records in the newly created table Diploma_Student:
INSERT INTO Diploma_Student VALUES(202111, 'Vaishnavi Patil', 94, 91, 88, 85, 95, 92, 91);
INSERT INTO Diploma_Student VALUES(202112, 'Vaibhav Lokhande', 85, 90, 92, 80, 85, 82, 86);
INSERT INTO Diploma_Student VALUES(202113, 'Yash Dhull', 90, 88, 94, 87, 85, 90, 89);
INSERT INTO Diploma_Student VALUES(202114, 'Sonali Patole', 95, 90, 92, 88, 92, 90, 91);
INSERT INTO Diploma_Student VALUES(202115, 'Axar Patel', 85, 80, 82, 86 , 92, 84, 85);
INSERT INTO Diploma_Student VALUES(202116, 'Meena Mishra', 78, 75, 80, 74 , 85, 77, 78)
We have inserted six records into the Diploma_Student table.
SQL SELECT query:
SQL SELECT query is used to retrieve records from the table. We can fetch all the records from the table or retrieve the selected records according to the conditions using the SELECT query. For example, we will use the WHERE clause with the SELECT query to fetch students records whose total is greater than 85.
Syntax of SQL SELECT Query:
SELECT * FROM Table_Name;
The above syntax is used to fetch all the records from the table.
SELECT * FROM Table_Name WHERE conditions;
The above syntax is used to fetch specific records from the table.
We will fetch all the records from the Diploma_Student table using the following query:
SELECT * FROM Diploma_Student;
The Output of the SELECT Query is as follows:
| Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total |
| 202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 |
| 202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 |
| 202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 |
| 202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 |
| 202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 |
| 202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 |
SQL UPDATE Query:
SQL UPDATE Query is used to modify the data of a table.
Syntax of SQL UPDATE query:
UPDATE Table_Name SET Column_Name = Value WHERE Condition;
Above syntax updates the records based on the given condition.
We will modify the student marks of the sixth sem whose student id is 202116.
UPDATE Diploma_Student SET Sixth_Sem = 82 WHERE Student_Id = 202116;
We will now execute the SELECT query to check whether Diploma_Student records are updated or not.
SELECT * FROM Diploma_Student WHERE Student_Id = 202116;
| Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total |
| 202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 82 | 79 |
As we can see, records are updated successfully.
SQL ALTER Query:
In SQL, ALTER Query adds a column, deletes a column, and modifies a column in a table. SQL ALTER query is also used to rename a column, add, and drop constraints.
Syntax of how to add a column in a table:
ALTER TABLE Table_Name ADD Column_Name Data type;
Syntax of how to Drop column in a table:
ALTER TABLE Table_Name DROP Column Column_Name;
Syntax of how to modify column in a table:
ALTER TABLE Table_Name MODIFY Column_Name data type;
SQL DELETE Query:
SQL DELETE query is used to remove the data from the table.
Syntax of SQL DELETE Query:
DELETE FROM Table_Name;
The above syntax is used to remove all the records from the table. If you want to delete specific data from the table, use the below query:
DELETE FROM Table_Name WHERE condition;
If we want to delete records from the Diploma_Student table whose Student id is 202116, then, execute the following query:
DELETE FROM Diploma_Student WHERE Student_Id = 202116;
We will now execute the SELECT query to check whether Diploma_Student records are deleted or not.
SELECT * FROM Diploma_Student;
| Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total |
| 202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 |
| 202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 |
| 202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 |
| 202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 |
| 202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 |
SQL TRUNCATE Query:
SQL TRUNCATE Query is used to delete all the records of a table. It keeps the table structure as it is.
Syntax of SQL Truncate Query:
TRUNCATE TABLE Table_Name;
We will now remove all the records of the Diploma_Student table using the truncate query:
TRUNCATE TABLE Diploma_Student;
After executing the truncate query, if we execute the SELECT query message displays an empty set or no result.
SQL DROP Query:
SQL DROP query is used to delete the record and table from the structure. It is also used to drop the Database from the system.
Syntax of SQL DROP query:
DROP TABLE Table_Name;
We will now drop the Diploma_Student table from the Database.
DROP TABLE Diploma_Student;
It will delete all the records of the table with table structure.