SQL Command Cheat Sheet: Complete Guide Tutorial | CHECK-OUT
SQL Command Cheat Sheet

SQL Command Cheat Sheet: Complete Guide Tutorial For Free | CHECK-OUT

Last updated on 09th Jul 2020, Blog, Tutorials

About author

Vikas Aswal (Senior Sql Database Administrator )

Vikas Aswal is a Senior SQL Database Administrator who has more than 6 years of experience in SQL Databases. and is also an expert in DataStage, Hadoop, Microsoft Power BI, MicroStrategy, OBIEE, and Cognos.

(5.0) | 18526 Ratings 785

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:

QUERYDESCRIPTION
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

    Subscribe For Free Demo

    [custom_views_post_title]

    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:

    SQLExplanation
    SELECT c1 FROM tSelect data in column c1 from a table named t
    SELECT * FROM tSelect 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 offsetSelect 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 c1Select 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 conditionSelect 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:

    SQLExplanation
    SELECT c1, c2 FROM t1 INNER JOIN t2 on conditionSelect 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 conditionSelect 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 conditionSelect 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 conditionSelect 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 t2Select columns c1 and c2 from a table named t1 and produce a Cartesian product of rows in tables
    SELECT c1, c2 FROM t1, t2Same 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 conditionSelect 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:

    Course Curriculum

    Best Hands-on SQL Certification Course By Industry Experts

    Weekday / Weekend BatchesSee Batch Details

    SQLExplanation
    SELECT c1 FROM t1 UNION [ALL] SELECT c1 FROM t2Select 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 t2Select 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 t2Select 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 patternSelect column c1 from a table named t and query the rows using pattern matching %
    SELECT c1 FROM t WHERE c1 [NOT] in test_listSelect 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 maxSelect 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] NULLSelect 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:

    SQLExplanation
    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 t2Insert rows from t2 into a table named t1
    UPDATE tSET c1 = new_valueUpdate a new value in table t in the column c1 for all rows
    UPDATE tSET c1 = new_value, c2 = new_value WHERE conditionUpdate values in column c1 and c2 in table t that match the condition
    DELETE FROM tDelete all the rows from a table named t
    DELETE FROM tWHERE conditionDelete 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:

    SQLExplanation
    CREATE VIEW view1 AS SELECT c1, c2 FROM t1 WHERE conditionCreate 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:

    SQLExplanation
    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_nameDrop 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 Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    SQLExplanation
    CREATE PROCEDURE procedure_name    @variable AS datatype = value AS   — Comments SELECT * FROM tGOCreate 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:

    SQLExplanation
    CREATE OR MODIFY TRIGGER trigger_name WHEN EVENT ON table_name TRIGGER_TYPE EXECUTE stored_procedureWHEN: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_nameDelete 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.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free