SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries What are single row and multiple row subqueries? SQL Union Clause

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL How to get current year in SQL server 2012? User Input in PL/SQL

SQL Queries
2022-04-24 17:33:34

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:

FieldTypeNullKeyDefaultExtra
Student_Idint(11)NOPRINULL 
Student_Namevarchar(40)NO NULL 
First_Semint(11)YES NULL 
Second_Semint(11)YES NULL 
Third_Semint(11)YES NULL 
Fourth_Semint(11)YES NULL 
Fifth_Semint(11)YES NULL 
Sixth_Semint(11)YES NULL 
Totalint(11)YES NULL 
SQL Queries

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_IdStudent_NameFirst_SemSecond_SemThird_SemFourth_SemFifth_SemSixth_SemTotal
202111Vaishnavi Patil94918885959291
202112Vaibhav Lokhande85909280858286
202113Yash Dhull90889487859089
202114Sonali Patole95909288929091
202115Axar Patel85808286928485
202116Meena Mishra78758074857778
SQL Queries

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_IdStudent_NameFirst_SemSecond_SemThird_SemFourth_SemFifth_SemSixth_SemTotal
202116Meena Mishra78758074858279
SQL Queries

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_IdStudent_NameFirst_SemSecond_SemThird_SemFourth_SemFifth_SemSixth_SemTotal
202111Vaishnavi Patil94918885959291
202112Vaibhav Lokhande85909280858286
202113Yash Dhull90889487859089
202114Sonali Patole95909288929091
202115Axar Patel85808286928485
SQL Queries

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.