SQL Command Cheat Sheet: Complete Guide Tutorial For Free | CHECK-OUT
Last updated on 09th Jul 2020, Blog, Database, Tutorials
(5.0) | 18526 Ratings
905
SQL often called Structured Query Language is a declarative and multi-paradigm language that is the domain-specific language used for designing and managing data models to organize the data in relational model databases. SQL was designed by Donald Chamberlin and Raymond Boyce. SQL first appeared in 1974. SQL is static typing and strong discipline. SQL supports cross-platform operating systems. The file extension will be .sql and was developed by ISO/IEC. SQL has different sublanguages called statements to perform different operations based on the required operation.
Commands and Content on Cheat sheet SQL
Cheat Sheet SQL commands can be classified into different categories which can be classified as Data Definition Commands, Data Manipulation Commands, and Data Control Commands. Below are the Cheat Sheet SQL commands mentioned which perform different kinds of operations:
| QUERY | DESCRIPTION |
|---|---|
| DATA MANIPULATION COMMANDS | |
| Select * from <table_name>; | To query entire data from the mentioned table |
| Select col1, col2 from <table_name>; | To retrieve two columns from a table |
| Select col1, col2 from table WHERE condition; | To query data from a table based on a condition |
| Select distinct col from <table_name>; | To query distinct records from a table |
| Select distinct col from table WHERE condition; | To query distinct records from a table based on a condition to filter the data |
| Select col1, col2 from table ORDER BY col2 ASC; | To query data from a table and sort the retrieved records in ascending order |
| Select col1, col2 from table ORDER BY col2 DESC; | To query data from a table and sort the retrieved records in descending order |
| Select aggregate(col1), col2 from table GROUP BY col2; | To query data from the table to aggregate the data based on a column. |
| Select aggregate(col1), col2 from table GROUP BY col2 HAVING condition; | To query data from the table to aggregate the data based on a column and a condition. |
| Select col1, col2 from table1 INNER JOIN table2 ON condition; | To query data from multiple tables and inner join the result based on a condition and display a single result |
| Select col1, col2 from table1 LEFT JOIN table2 ON condition; | To query data from multiple tables and left join the result based on a condition and display as a single result |
| Select col1, col2 from table1 RIGHT JOIN table2 ON condition; | To query data from multiple tables and right join the result based on a condition and display as a single result |
| Select col1, col2 from table1 FULL OUTER JOIN table2 ON condition; | To query data from multiple tables and full outer join the result based on a condition and display as a single result |
| Select col1, col2 from table1 CROSS JOIN table2; | To query data from multiple tables and cross join the result to obtain a Cartesian product of the records and display as a single result |
| Select col1, col2 from table1 UNION select col1, col2 from table2; | To query data from multiple tables and combine the results of records from two tables. |
| DATA DEFINITION COMMANDS | |
| CREATE TABLE table_name (col_name DATA_TYPE CONSTRAINT); | To create a table with new columns and all the column definition can be mentioned by a comma |
| ALTER TABLE table_name; | To add a new column to the table |
| DROP TABLE table_name; | To delete the entire table from the database |
| INSERT INTO table_name(list of columns) VALUES(list of values); | To insert data into a table for one record. |
| UPDATE table SET col1=updated_value; | To update a cell value in a table based on column name |
| UPDATE table SET col1=updated_value1, col2=updated_value2 WHERE condition; | To update multiple cell values in a table based on column names and condition |
| DELETE FROM table_name; | To delete the complete data in a table. |
| DELETE FROM table_name WHERE condition; | To delete the complete data in a table based on a condition. |
| DATA CONTROL COMMANDS | |
| GRANT <Object Privileges> ON <Object Name> TO <user> [GRANT OPTION] | To grant access to a particular user based on the grant option and access requirement. |
| REVOKE <Object Privileges> ON <Object Name> FROM <user> | To revoke access to a particular user from a particular object |
Free Tips and Tricks of using Cheatsheet SQL Commands
- The SQL cheat sheet commands can be used in any IDE or tool where the user has connected to the database using the JAR file of the database type.
- The different databases existing in the market are Oracle, Microsoft SQL Server, IBM DB2, etc., which all these can be connected to by using their respective jars and tools to manage the data operations.
- There is a different cheat sheet for SQL Aggregate Functions to be used along with the SQL cheat sheet commands to perform some complex operations based on the required data to query as below.Using a cheat sheet for SQL commands, VIEWS can also be managed and created.
- INDEXES and TRIGGERS can also be managed using cheat sheet SQL commands.
- The different cheat sheet SQL Operators are Arithmetic operators, Logical operators, Comparison operators, and Negation Operators similar to the general-purpose programming languages.
- The different cheat sheets for SQL Expressions are Numeric, Boolean and Date.
- The different SQL Constraints are the rules to execute the commands on the table columns to ensure reliability, redundancy, and accuracy while performing operations on a table.
- In the case of integrity, Referential Integrity plays a major role in performing integrity constraints along with the commands. The different constraints available are Integrity constraints and Dropping constraints.
- SQL Injection is another concept where the user-submitted data should always be validated before processing or running the query to avoid the data breach and to ensure safe and secure data operations without any loss of data.
Querying data from a table
A database table is a set of data elements (values) stored in a model of vertical columns and horizontal rows. Use any of the below to query a table in SQL:
| SQL | Explanation |
| SELECT c1 FROM t | Select data in column c1 from a table named t |
| SELECT * FROM t | Select all rows and columns from a table named t |
| SELECT c1 FROM t WHERE c1 = ‘test’ | Select data in column c1 from a table named t where the value in c1 = ‘test’ |
| SELECT c1 FROM t ORDER BY c1 ASC (DESC) | Select data in column c1 from a table name t and order by c1, either in ascending (default) or descending order |
| SELECT c1 FROM t ORDER BY c1LIMIT and OFFSET offset | Select data in column c1 from a table named t and skip offset of rows and return the next n rows |
| SELECT c1, aggregate(c2)FROM t GROUP BY c1 | Select data in column c1 from a table named t and group rows using an aggregate function |
| SELECT c1, aggregate(c2) FROM t GROUP BY c1HAVING condition | Select data in column c1 from a table named t and group rows using an aggregate function and filter these groups using ‘HAVING’ clause |
Querying data from multiple tables
As well as querying from a single table, SQL gives you the ability to query data from multiple tables:
| SQL | Explanation |
| SELECT c1, c2 FROM t1 INNER JOIN t2 on condition | Select columns c1 and c2 from a table named t1 and perform an inner join between t1 and t2 |
| SELECT c1, c2 FROM t1 LEFT JOIN t2 on condition | Select columns c1 and c2 from a table named t1 and perform a left join between t1 and t2 |
| SELECT c1, c2 FROM t1 RIGHT JOIN t2 on condition | Select columns c1 and c2 from a table named t1 and perform a right join between t1 and t2 |
| SELECT c1, c2 FROM t1 FULL OUTER JOIN t2 on condition | Select columns c1 and c2 from a table named t1 and perform a full outer join between t1 and t2 |
| SELECT c1, c2 FROM t1 CROSS JOIN t2 | Select columns c1 and c2 from a table named t1 and produce a Cartesian product of rows in tables |
| SELECT c1, c2 FROM t1, t2 | Same as above – Select columns c1 and c2 from a table named t1 and produce a Cartesian product of rows in tables |
| SELECT c1, c2 FROM t1 A INNER JOIN t2 B on condition | Select columns c1 and c2 from a table named t1 and joint it to itself using an INNER JOIN clause |
Using SQL Operators
SQL operators are reserved words or characters used primarily in an SQL statement where clause to perform operations:
Best Hands-on SQL Certification Course By Industry Experts
Weekday / Weekend BatchesSee Batch Details| SQL | Explanation |
|---|---|
| SELECT c1 FROM t1 UNION [ALL] SELECT c1 FROM t2 | Select column c1 from a table named t1 and column c1 from a table named t2 and combine the rows from these two queries |
| SELECT c1 FROM t1 INTERSECT SELECT c1 FROM t2 | Select column c1 from a table named t1 and column c1 from a table named t2 and return the intersection of two queries |
| SELECT c1 FROM t1 MINUS SELECT c1 FROM t2 | Select column c1 from a table named t1 and column c1 from a table named t2 and subtract the 2nd result set from the 1st |
| SELECT c1 FROM t WHERE c1 [NOT] LIKE pattern | Select column c1 from a table named t and query the rows using pattern matching % |
| SELECT c1 FROM t WHERE c1 [NOT] in test_list | Select column c1 from a table name t and return the rows that are (or are not) in test_list |
| SELECT c1 FROM t WHERE c1 BETWEEN min AND max | Select column c1 from a table named t and return the rows where c1 is between min and max |
| SELECT c1 FROM t WHERE c1 IS [NOT] NULL | Select column c1 from a table named t and check if the values are NULL or not |
Data modification
Data modification is a key part of SQL, giving the ability to not only add and delete data, but modify existing records:
| SQL | Explanation |
| INSERT INTO t(column_list) VALUES(value_list) | Insert one row into a table named t |
| INSERT INTO t(column_list) VALUES (value_list), (value_list), … | Insert multiple rows into a table named t |
| INSERT INTO t1(column_list)SELECT column_list FROM t2 | Insert rows from t2 into a table named t1 |
| UPDATE tSET c1 = new_value | Update a new value in table t in the column c1 for all rows |
| UPDATE tSET c1 = new_value, c2 = new_value WHERE condition | Update values in column c1 and c2 in table t that match the condition |
| DELETE FROM t | Delete all the rows from a table named t |
| DELETE FROM tWHERE condition | Delete all rows from that a table named t that match a certain condition |
Views
A view is a virtual table that is a result of a query. They can be extremely useful and are often used as a security mechanism, letting users access the data through the view, rather than letting them access the underlying base table:
| SQL | Explanation |
|---|---|
| CREATE VIEW view1 AS SELECT c1, c2 FROM t1 WHERE condition | Create a view, consisting of columns c1 and c2 from a table named t1 where a certain condition has been met. |
Indexes
An index is used to speed up the performance of queries by reducing the number of database pages that have to be visited:
| SQL | Explanation |
|---|---|
| CREATE INDEX index_nameON t(c1, c2) | Create an index on columns c1 and c2 of the table t |
| CREATE UNIQUE INDEX index_name ON t(c3, c4) | Create a unique index on columns c3 and c4 of the table t |
| DROP INDEX index_name | Drop an index |
Stored procedure
A stored procedure is a set of SQL statements with an assigned name that can then be easily reused and share by multiple programs:
| SQL | Explanation |
|---|---|
| CREATE PROCEDURE procedure_name @variable AS datatype = value AS — Comments SELECT * FROM tGO | Create a procedure called procedure_name, create a local variable and then select from table t |
Triggers
A trigger is a special type of stored procedure that automatically executes when a user tries to modify data through a DML event (data manipulation language). A DML event is an INSERT, UPDATE or DELETE statement on a table or view:
| SQL | Explanation |
|---|---|
| CREATE OR MODIFY TRIGGER trigger_name WHEN EVENT ON table_name TRIGGER_TYPE EXECUTE stored_procedure | WHEN:BEFORE – invoke before the event occurs AFTER – invoke after the event occursEVENT:INSERT – invoke for insert UPDATE – invoke for update DELETE – invoke for deleteTRIGGER_TYPE:FOR EACH ROW FOR EACH STATEMENT |
| DROP TRIGGER trigger_name | Delete a specific trigger |
Conclusion
SQL is used to perform database operations on many relational model databases to perform several crud operations. The common language for all relational model databases is SQL (Structured Query Language). The formula only differs in all the databases. All the features in SQL cheat sheet commands can be used to perform mostly all the complex operations or data requirements in any application or directly to generate reports or data files to export or import to or from respectively from the databases.