Overview
SQL is a very powerful language that consists of various statements to perform specific operations on the data. In this article, we will explain some of the fundamental SQL statements that are being used in the database management system.
What are SQL Statements?
SQL statements are basically a block, the foundation of the database management system. These statements allow us to interact with the database i.e. retrieve stored data, modify data, and perform various operations. SQL has a rich set of commands, but keeping simplicity in mind, we will focus on some of the most essential ones.
Types of SQL Statements
There are 15 types of SQL Statements that are called the bread and butter of SQL statements as follows –
- Select Statement
- From Clause
- Where Clause
- Order By Clause
- Group By Clause
- Having Clause
- Join Statement
- Union Statement
- Subqueries
- Insert Statement
- Update Statement
- SET Clause
- Delete Statement
- Drop Statement
- Truncate Statement
Select Statement
The select statement is the backbone of SQL. It retrieves data from one or more database tables. For example, to retrieve all the data from a table named “Wildcard_Course” you would use as following example –
Syntax:
SELECT * FROM Table_Name
Example:
SELECT * FROM Wildcard_Course
From Clause
The from clause specifies the source table name from which data should be retrieved. In the previous example, “Wildcard_Course” is the source table.
Where Clause: The where clause is used to filter data based on specified conditions. For example, if you want to retrieve only the course pertaining to SQL, you would write as following example –
Syntax:
SELECT * FROM Table_Name WHERE Condition
Example:
SELECT * FROM Wildcard_Course WHERE Course_Name LIKE '%SQL%'
Order By Clause
Order by clause is used to sort the result set in ascending or descending order based on one or more columns. For ascending order shortcode is used as “ASC” and for descending order shortcode is used as “DESC”. SQL is used in ascending order by default. Refer below examples –
Syntax:
SELECT * FROM Table_Name WHERE Condition ORDER BY ASC/DESC
Example:
SELECT * FROM Wildcard_Course WHERE Course_Name LIKE '%SQL%' ORDER BY Course_Name
SELECT * FROM Wildcard_Course WHERE Course_Name LIKE '%SQL%' ORDER BY Course_Name ASC
SELECT * FROM Wildcard_Course WHERE Course_Name LIKE '%SQL%' ORDER BY Course_Name DESC
Group By Clause
Group by clause is used to a grouping of rows with similar values into summary rows. It is often used with aggregate functions like SUM, AVG, and COUNT to analyze data.
Syntax:
SELECT Column_Name, COUNT(Column_Name) AS [Alias Name] FROM Table_Name GROUP BY Column_Name;
Example:
SELECT Department, COUNT(EmployeeName) AS [No Of Employee] FROM Employee GROUP BY Department;
Having Clause
The having clause filters the results of a group by query based on specified conditions. It is used to filter aggregated data as follows –
Syntax:
SELECT Column_Name, COUNT(Column_Name) AS [Alias Name] FROM Table_Name GROUP BY Column_Name HAVING Condition;
Example:
SELECT Department, COUNT(EmployeeName) AS [No Of Employee] FROM Employee GROUP BY Department HAVING COUNT(EmployeeName)>2;
Join Statement
The join statement is used to combine the result sets of two or more SELECT statements into a single result set as follows –
Example:
SELECT e.,ne.
FROM Employee e
JOIN NN_Employees3 ne ON ne.EmployeeID=e.EmployeeID
Union Statement
SQL allows you to combine data from multiple tables using JOIN statements. You can perform inner, outer, or right joins to retrieve data from related tables as follows –
Example:
SELECT EmployeeID, EmployeeName FROM Employee
UNION
SELECT EmployeeID, CONCAT(FirstName,' ', LastName) AS [EmployeeName] FROM NN_Employees3
Subqueries
Subqueries are nested queries within a main query. They are used to retrieve data based on the result of another query.
Example:
SELECT * FROM Employee WHERE EmployeeID IN (SELECT EmployeeID FROM NN_Employees3)
Insert Statement
The INSERT INTO statement is used to add new records to a table. To insert a new employee into the “Employee” table as follows –
Example:
INSERT INTO Employee (EmployeeID,EmployeeName,Designation,Department,Salary)
VALUES(20,'Vaishnavi','AM','Call Center',25000)
INSERT INTO Employee (EmployeeID,EmployeeName,Designation,Department,Salary)
VALUES(21,'Trisha','DM','Call Center',45000)
Update Statement
The UPDATE statement allows you to modify existing records in a table. For example, to update all employee’s salaries by increasing 5000 of the call center department as follows –
Example:
UPDATE Employee SET EmployeeName='Akram Ali' WHERE EmployeeID=13 AND EmployeeName='Ali'
UPDATE Employee SET EmployeeName=CONCAT(EmployeeName,' ','Thhappa') WHERE Department='Call Center';
UPDATE Employee SET Salary=Salary+5000 WHERE Department='Call Center';
Set Clause
A set clause is used to specify the column names to provide the desired value to override refer to the examples in the previous example.
Delete Statement
The DELETE statement is used to remove records from a table. To delete an employee from the “Employee” table
Example:
DELETE FROM Employee WHERE EmployeeID=22 AND EmployeeName='Trishul Twinkle'
Drop Statement
A drop table statement is used to delete or remove the table permanently from the database as follows –
Example:
DROP TABLE NN_Employees3
Truncate Statement
Truncate table statement is used to delete or remove records from a table and also resets the identity counter permanently as follows –
Example:
TRUNCATE TABLE NN_Employees3
Best Practices for Writing SQL Statements
Refer to the below best practices for writing SQL statements –
- Use Proper Indentation: Organize your SQL statements for readability, especially when dealing with complex queries.
- Comment Your Code: Comments provide context and add to understanding the purpose of your SQL statements.
- Avoid Using *(Asterisk): It is advisable to specify the exact columns you need rather than selecting all columns with SELECT *.
- Use Joins: When working with multiple tables, employ JOIN statements to combine data effectively.
- Test Your Statements: Always run your SQL statements in a testing environment before applying them to production data.
FAQs
SQL statements are used to interact with databases, allowing users to retrieve, manipulate, and manage data.
The SELECT statement retrieves data from a database, allowing users to interact with and analyze their data.
You can use the UPDATE statement to modify existing records in a table by specifying the column and new values.
The GROUP BY clause is used to group rows with similar values into summary rows, often used with aggregate functions.
Indexing columns, optimizing queries, and using proper database design can enhance SQL query performance.
The common types of SQL statements include SELECT, INSERT, UPDATE, DELETE, and various advanced statements like JOIN, GROUP BY, HAVING, and ORDER BY.
Conclusion
Understanding basic SQL statements is essential for anyone dealing with databases. These fundamental statements form the building blocks for more complex queries and data manipulation. We hope you like this very well.
Please share this on your social media network so that it can be available to even more needy persons, which may benefit them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response ASAP.
