90+ SQL Commands for Efficient Database Management

Looking for the SQL commands to effectively control your database?

Have you ever imagined how websites and apps handle their data? Well, the secret is in something called databases. They use a special language called SQL (Structured Query Language) to manage and organize all their data.

SQL acts as a foundation for relational database management systems (RDBMS). It offers a robust and consistent approach to engaging with databases.

Whether you are a database analyst, a software developer, or just starting your data journey, understanding SQL commands is essential for efficient data manipulation and retrieval.

In this blog, we will talk about 90+ SQL commands that will help you feel more comfortable when working with databases.

Table of Contents

SQL Commands

NumberCommandUSe For
1SelectRetrieve data from a database
2Insert Inserts new data into a database
3UPDATEUpdates existing data in a database
4DELETEDeletes data from a database
5CREATE DATABASECreates a new database
6CREATE TABLE Creates a new table in a database
7ALTER TABLEModifies an existing table structure
8DROP TABLE Deletes a table from a database
9TRUNCATE TABLE Removes all records from a table
10CREATE INDEXCreates an index on a table
11DROP INDEXDeletes an index from a table
12JOINCombines rows from two or more tables based on a related column
13INNER JOIN Returns rows when there is a match in both tables
14LEFT JOINReturns all rows from the left table, and the matched rows from the right table
15RIGHT JOIN Returns all rows from the right table, and the matched rows from the left table
16FULL JOINReturns rows when there is a match in one of the tables
17UNIONCombines the results of two or more
SELECT statements
18UNION ALL Combines the results of two or more SELECT statements, including duplicates
19GROUP BY Groups rows that have the same values into summary rows @code_learning
20HAVING Filters records based on a specified condition
21ORDER BY Sorts the result set in ascending or descending order
22COUNT Returns the number of rows that satisfy the condition
23SUM Calculates the sum of a set of values
24AVG Calculates the average of a set of values
25MIN Returns the smallest value in a set of values
26MAXReturns the largest value in a set of values
27DISTINCT Selects unique values from a column
28WHERE Filters records based on specified conditions
29AND Combines multiple conditions in a WHERE clause
30OR Specifies multiple alternative conditions in a WHERE clause
31NOT Negates a condition in a WHERE clause
32BETWEENSelects values within a specified range
33INSpecifies multiple values for a column
34LIKE Selects rows that match a specified pattern
35IS NULL Checks for NULL values in a column
36IS NOT NULL Checks for non-NULL values in a column
37EXISTSTests for the existence of any record in a subquery
38CASE Performs conditional logic in SQL statements
39WHEN Specifies conditions in a CASE statement
40THENSpecifies the result if a condition is true in a CASE statement
41ELSESpecifies the result if no condition is true in a CASE statement
42END Ends the CASE statement
43PRIMARY KEY Uniquely identifies each record in a table
44FOREIGN KEY Establishes a relationship between tables
45CONSTRAINT Enforces rules for data in a table
46DEFAULT Specifies a default value for a column
47NOT NULL Ensures that a column cannot contain NULL values
48UNIQUE Ensures that all values in a column are unique
49CHECKEnforces a condition on the values in a column
50CASCADE Automatically performs a specified action on related records
51SET NULL Sets the value of foreign key columns to NULL when a referenced record is deleted SET
52DEFAULT Sets the value of foreign key columns to their default value when a referenced record is deleted
53NO ACTION Specifies that no action should be taken on related records when a referenced record is deleted
54RESTRICTRestricts the deletion of a referenced record if there are related records
55CASE WHENConditional expression in SELECT statements
56WITH Defines a common table expression (CTE)
57INTO Specifies a target table for the result set of SELECT statement
58TOP Limits the number of rows returned by a query
59LIMIT Limits the number of rows returned by a query (used in some SQL dialects)
60OFFSET Specifies the number of rows to skip before starting to return rows
61FETCH Retrieves rows from a result set one at a time
62ROW_NUMBER( )Assigns a unique sequential integer to each row in a result set
63RANK( )Assigns a unique rank to each row in a result set, with gaps in the ranking sequence
possible
64DENSE_RANK( ) Assigns a unique rank to each row in a result set, with no gaps in the ranking sequence
65NTILE( ) Divides the result set into a specified number of equally sized groups
66LEAD( ) Retrieves the value from the next row in a result set
67LAG( )Retrieves the value from the previous row in a result set
68PARTITION BY Divides the result set into partitions to which the window function is applied separately
69ORDER BYSpecifies the order of rows within each partition for window functions
70ROWSSpecifies the window frame for window functions
71RANGE Specifies the window frame based on values rather than rows for window functions
72CURRENT_TIMESTAMPReturns the current date and time
73CURRENT_DATE Returns the current date
74CURRENT_TIMEReturns the current time
75DATEADD Adds a specified time interval to a date
76DATEDIFFCalculate the difference between the two dates
77DATEPART Extracts a specific part of a date
78GETDATE Returns the current date and time (similar to CURRENT_TIMESTAMP)
79GROUPING SETSSpecifies multiple groupings for aggregation
80CUBE Generates all possible combinations of grouping sets for aggregation
81ROLLUPGenerates subtotal values for a hierarchy of values
82INTERSECT Returns the intersection of two result sets
83EXCEPT Returns the difference between two result sets
84MERGE Performs insert, update, or delete
operations on a target table based on the results of a join with a source table
85CROSS APPLY Performs a correlated subquery against each row of the outer table
86OUTER APPLY Similar to CROSS APPLY, but also returns rows from the outer table that have no matching rows in the inner table
87PIVOT Rotates a table-valued expression by turning the unique values from one column into multiple columns in the output
88UNPIVOT Rotates a table-valued expression by turning multiple columns into unique rows in the output
89COALESCEReturns the first non-NULL expression in a list
90NULLIF Returns NULL if the two specified expressions are equal, otherwise returns the first expression
91IIF Returns one of two values based on a Boolean expression
92CONCAT Concatenates two or more strings
93SUBSTRINGExtracts a substring from a string
94CHARINDEX Finds the position of a substring within a string
95REPLACE Replaces all occurrences of a specified substring within a string with another substring
96LEN Returns the length of a string
97UPPER Converts a string to uppercase
98LOWER Converts a string to lowercase
99TRIM Removes leading and trailing spaces from a string
100ROUND Rounds a numeric value to a specified number of decimal places

Conclusion

SQL commands are the foundation of effective database management, allowing users to effortlessly engage with and control data. Whether you are retrieving information or modifying the structure of the tables, having strong SQL commands is vital for individuals involved in relational databases.

When you use these commands the right way, you can make the most out of your database and make sure your application runs really well.

Thanks for reading this blog. We hope this article provided helpful information regarding SQL commands.

Read More –