For enquiries call:

Phone

+1-469-442-0620

Easter Sale-mobile

HomeBlogProgrammingSQL Cheat Sheet (2024)

SQL Cheat Sheet (2024)

Published
26th Dec, 2023
Views
view count loader
Read it in
14 Mins
In this article
    SQL Cheat Sheet (2024)

    To understand SQL, you must first understand DBMS (database management systems) and databases in general. Data refers to a set of facts regarding a specific object. Whereas, a database refers to a set of small data units organized in a logical order. An RDBMS (Relational Database Management System) is a set of tools that allow users to visualize, organize, and manage the contents of a database while adhering to a set of standard rules that allow the database and the user to respond quickly. If interested in joining some training, learn more about SQL through MySQL Training. Below is the comprehensive version of SQL cheat sheet for interview for quick revision.

    You can finally learn about SQL after being introduced to the concepts of RDBMS/DBMS, databases, and data. SQL (Structured Query Language) refers to the language that the users use to communicate with databases and extract the information that users need. It is used to store, manipulate, and retrieve data from databases. In this article, you will find a helpful SQL cheat sheet that records some of the more regularly used SQL elements, as well as a few less popular ones. We hope that it would assist developers, both novice and experienced, in improving their comprehension of the SQL language. Check out these top database courses to enhance your grasp in this domain and add value to your skills.

    Infographic Version of SQL Cheat Sheet

    Here is the infographic version with ample information about the SQL injection cheat sheet. Your search will end here if you were trying to search “my SQL cheat sheet” on the internet. Let’s get into this.


    Here is the SQL Windows Function Cheat Sheet infograhics.

    Keywords

    A list of SQL keywords, together with a description, and where it is applicable is provided below. Later in the my SQL commands cheat sheet, some of the more complicated terms get their own section. When MySQL is mentioned next to an example, it signifies that the example applies only to MySQL databases as against any other type of database system.

    1. ADD: A new column is added to an already available table.  

    Example: It creates a new column called ‘email_address’ in the ‘users’ database.

    2. ADD CONSTRAINT: It adds a new constraint to an already available table, which can be used to establish rules for any of the table’s data.

    Example: On the columns ID and SURNAME, a new primary key restriction named ‘user’ is added.

    3. ALTER TABLE: A table’s columns can be added, deleted, or edited. It can even be used in order to remove and add constraints from a table.

    Example: To a table called ‘deals’, it adds a new boolean column called ‘approved’

    4. ALTER COLUMN: Substitutes the data type of a column in a table.

    Example: Make the ‘incept date’ column in the ‘users’ table into a ‘datetime’ type.

    5. ALL: If every subquery value satisfies the passed criteria, true is returned.

    Example: Returns users in the HR department that have more tasks as compared to the users with the most tasks.

    6. AND: Used to connect multiple conditions with a WHERE clause.

    Example: Returns events in the United Kingdom, London.

    7. ANY: Subquery values meet any condition, this function returns true.

    Example: Products return from the table of products that received orders - situated in the orders table - in quantities greater than 5.

    8. AS: Renames a column or table with alias value that exists as long as the query.

    Example: Aliases south_west_user_subscription column

    9. ASC: Uses 'ORDER BY' keyword to return data in an increasing order.

    Example: Apricot, Berries, Plum, Raddish.

    10. BETWEEN: Chooses values in a given range.

    Example: Chooses stocks between 50 and 100.

    11. CASE: Changes the output of the query based on certain conditions.

    Example: Users and Subscriptions, as well a new column known as activity_levels makes a conclusion depending on the number of subscriptions.

    12. CHECK: Inserts a constraint that creates a value limit that can be added to a column.  

    Example: Users who are on the user table are above the age if 18

    13. CREATE DATABASE: Creating a new Database.

    Example: Create a new database called 'setupwebsite'.

    14. CREATE TABLE: Creating a new table.

    Example: Create a table called 'users' in the 'setupwebsite' database

    15. DEFAULT: Puts a default value for a column.

    16. DELETE: Deleting data from a table.

    17. DESC: Used along ORDER BY to return data in a decreasing order.

    18. DROP COLUMN: Deleting column from a table.

    19. DROP DATABASE: Deleting entire Database.

    20. DROP DEFAULT: Deletes default value from a column.

    21. DROP TABLE: Deletes table  

    22. EXISTS: Checks the subquery for the existence of any record.

    23. FROM: Specifies the table from which to select or delete data.

    24. IN: As a shorthand for multiple OR conditions, it is used alongside a WHERE clause

    25. INSERT INTO: Addition of new rows in a table

    26. IS NULL: Test for empty values

    27. IS NOT NULL: Tests for values that are not empty

    28. LIKE: The operand value matches a pattern, this function returns true.

    29. NOT: If a record does not meet the condition, it returns true

    Example: Comes true if the user’s first name does not end in ‘son’.

    30. OR: Used with WHERE to include data if the condition is true or not.

    Example: Users who live in Manchester or Sheffield.

    31. ORDER BY: The DESC and ASC keywords are used to separate the data results in a default (ascending) or descending order.

    Example: Putting countries in an alphabetical order.

    32. ROWNUM: The row number matches the condition passed.

    Example: Returns the top ten countries from the table of countries.

    33. SELECT: Used to retrieve data from a database and return it as a set of results.

    Example: All columns from all users are selected.

    34. SELECT DISTINCT: The same as SELECT, however duplicate values are not allowed.

    Example: Creating a table-backup by using data from the users table.

    35. SELECT INFO: Data from one table is copied and inserted into another.

    Example: Returns all nations from the users table, eliminating any entries that are duplicated.

    36. SELECT TOP: Allows to retrieve a specific number of records from a table.

    Example: Returns the top three automobiles from the cars table.

    37. SET: Used with UPDATE to update data in a table.

    Example: In the orders table, update the quantity and values for an order with an id of 642.

    38. SOME: Same as ANY.

    39. TOP: Used in conjunction with SELECT to retrieve a specific amount from a table of records.

    Example: The top 5 users are returned from the users table.

    40. TRUNCATE TABLE: It is like DROP, except instead of destroying the table and all of its data, this merely deletes the data.

    Example: Unloads the table session but does not affect the table itself.

    41. UNION: Returns only separate values after combining the results of two or more SELECT operations.

    Example: Returns the cities from subscribers and events tables.

    42. UNION ALL: As similar as UNION, but with additional values

    43. UNIQUE: This requirement makes sure that each column’s values are unique.

    Example: When building a new users table, it adds a different constraint to the id column.

    44. UPDATE: Existing data in a table is updated.

    Example: In the vehicles table, updates will be done regarding the serviceDue values and mileage for a vehicle with an id of 45.

    45. VALUES: To insert new values to a table, use it in conjunction with the INSERT INTO keyword.

    Example: Adding a brand new car to the table of cars.

    46. WHERE: Filters the results to just show data that satisfies the specified criteria.

    Example: Orders containing a quantity where greater than one item are returned.

    Data Types

    You must mention the data type that each column accepts while editing an existing table or creating a new one. Now let us see the data types in SQL as per the SQL basics cheat sheet for your reference.

    1. String Data Types

    Includes CHAR (size), VARCHAR (size), BINARY (size), VARBINARY (size), TINYBLOB, TINYTEXT, TEXT (size), BLOB (size), MEDIUMTEXT, MEDIUMBLOB, LONGTEXT, LONGBLOB, ENUM (a, b, c, etc…), SET (a, b, c, etc…)

    2. Exact/Approximate Numeric Data Types

    Includes BIT (size), TINYINT (size), BOOL, BOOLEAN, SMALLINT (size), MEDIUMINT (size), INT (size), INTEGER (size), BIGINT (size), FLOAT (p), DOUBLE (size, d), DECIMAL (size, d), DEC (size, d).

    3. Date / Time Data Types

    Includes DATE, DATETIME (fsp), TIMESTAMP (fsp), TIME (fsp), YEAR.

    4. Binary Data types

    It includes Variable/Fixed binary data types such as maximum length of 8000 bytes.

    Views

    A view is a result of an SQL set that is saved in the database under a label so that it can be accessed later without having to run the query again. These mainly come useful if there is a costly SQL query that may be required multiple times; instead of running it repeatedly to generate the same results set, you can do it once and save it like a view. The types of views are as follows:

    1. Creating Views: Creating a particular view.
    2. Replacing Views: With the REPLACE or CREATE command, a view can be replaced or updated.
    3. Deleting Views: With the DROP VIEW command, a view can be simply deleted.

    Querying

    Transact SQL or T-SQL, is the query language used by Microsoft SQL Server. It can assist with tasks such as retrieving multiple rows, inserting new rows, and retrieving data from a single row. The SQL Server uses it as a procedural language. Functions of SQL querying are:

    • Aggregate Functions
    • Ranking Functions
    • Rowset Functions
    • Scalar Functions

    Indexes

    Indexes refer to qualities that can be allocated to frequently searched columns in order to make the retrieval of data faster and more efficient. This does not imply that each column needs to be put as an index, as a column that has an index generally takes more time to update as compared to a column without an index. This is due to the fact that when columns with indexes are updated, the index must also be updated. The types of indexes are as follows:

    • CREATE INDEX

    An index is created under the name ‘idx_test’ on the first_name and sur_name columns of the table. In this situation, values are allowed to be duplicated.

    • CREATE UNIQUE INDEX

    It is identical to CREATE INDEX. However, in this instance values are not allowed to be duplicated.

    • DROP INDEX

    Deletes/Removes/Drops an Index.

    Triggers

    Triggers refer to SQL codes that are executed on their own in response to some events on a specific table. These are used to ensure the data's integrity. A SQL trigger functions similarly to a real-world trigger. Types of triggers as per the SQL commands cheat sheet:

    1. Data Manipulation Language (DML): As previously stated, they respond to DML commands. These are DELETE, UPDATE, and INSERT.
    2. Data Definition Language (DDL): Triggers of this type, as expected, will respond to DDL commands such as DROP, ALTER, and CREATE.
    3. Logon Triggers: This class responds to LOGON events.

    Keys

    In databases that are relations, there is an element of foreign, candidate, alternate, and primary keys. In SQL tables these keys are included as constraints. In this sense, a table will consist of any of the keys in any combination.

    1. Primary Key

    A primary key identifies every record in a table uniquely. Only one primary key every table is allowed, out of which it can be assigned to any combination or single column. This, however, implies that every value in the columns must be distinct.

    The primary key in a table is generally an ID column that is combined with the AUTO INCREMENT keyword. This refers to the fact that the value grows on its own as more records are added.

    2. Foreign Key

    A foreign key is generally applied to either multiple or single columns and is used to connect two tables in a relational database. The foreign key is referred to as the child key, whereas the table containing the candidate key, or referenced key, is referred to as the parent table.

    As a foreign key prevents invalid data from being inserted that is not present in the parent table, it mainly means that the data column is split between two tables.  

    3. Candidate Key

    It is a collection of data or attributes that defines the unique tuple in a table. These keys are as strong as primary (being unique). Any data which can be set as the primary key is the candidate key.

    4. Alternate Key

    In simpler words, all the candidate keys minus the primary keys are alternate keys of the tuple in a relation. The alternate key can be absent or present in a table. There will be zero or no alternate key if there is only one candidate key in the tuple.

    There are other keys as well, including composite key (when a primary key has the two or more attributes), artificial key (when the primary key is long & complex, then artificial key is created for the tuple), and super key. These three are the variations of the above four.  

    Operators

    1. Arithmetic Operators

    • + , Add
    • - , Subtract
    • * , Multiply
    • / , Divide
    • % , Modulo

    2. Bitwise Operator

    • & ,Bitwise AND
    • | ,Bitwise OR
    • ^ ,Bitwise exclusive OR
    • Comparison Operators
    • =, Equal to
    • >, Greater than
    • <. Less than
    • >=, Greater than or equal to
    • <=, Less than or equal to
    • <>, Not equal to

    3. Compound Operators

    • +=, Add equals
    • -=, Subtract equals
    • *=, Multiply equals
    • /=, Divide equals
    • %=, Modulo equals
    • &= Bitwise AND equals
    • ^-= Bitwise exclusive equals
    • |*=, Bitwise OR equals

    Functions

    1. String Functions

    • ASCII:
    • CHAR_LENGTH:
    • CHARACTER_LENGTH:  
    • CONCAT:  
    • CONCAT_WS:  
    • FIELD:  
    • FIND IN SET
    • FORMAT
    • INSERT
    • ISTR
    • LCASE
    • LEFT
    • LENGTH
    • LOCATE
    • LOWER
    • LPAD
    • LTRIM
    • MID
    • POSITION
    • REPEAT
    • REPLACE
    • REVERSE
    • RIGHT
    • RPAD
    • RTRIM
    • SPACE
    • STRCMP
    • SUBSTR
    • SUBSTRING
    • SUBSTRING_INDEX
    • TRIM
    • UCASE
    • UPPER

    2. Numeric Functions

    • ABS
    • ACOS
    • ASIN
    • ATAN
    • ATAN2
    • AVG
    • CEIL
    • CEILING
    • COS
    • COT
    • COUNT
    • DEGREES
    • DIV
    • EXP
    • FLOOR
    • GREATEST
    • LEAST
    • LN
    • LOG
    • LOG10
    • LOG2
    • MAX
    • MIN
    • MOD
    • PI
    • POW
    • POWER
    • RADIANS
    • RAND
    • ROUND
    • SIGN
    • SIN
    • SQRT
    • SUM
    • TAN
    • TRUNCATE

    3. Date Functions

    • ADDDATE
    • ADDTIME
    • CURDATE
    • CURRENT_DATE
    • CUURENT_TIME
    • CURRENT_TIMESTAMP
    • CURTIME
    • DATE
    • DATEDIFF
    • DATE_ADD
    • DATE_FORMAT
    • DATE_SUB
    • DAY
    • DAYNAME
    • DAYOFWEEK
    • DAYOFYEAR
    • EXTRACT
    • FROM DAYS
    • HOUR
    • LAST DAY
    • LOCALTIME
    • LOCALTIMESTAMP
    • MAKEDATE
    • MAKETIME
    • MICROSECOND
    • MINUTE
    • MONTH
    • MONTHNAME
    • NOW
    • PERIOD_ADD
    • PERIOD_DIFF
    • QUARTER
    • SECOND
    • SEC_TO_TIME
    • STR_TO_DATE
    • SUBDATE
    • SUBTIME
    • SYSDATE
    • TIME
    • TIME_FORMAT
    • TIME_TO_SEC
    • TIMEDIFF
    • TIMESTAMP
    • TO_DAYS
    • WEEK
    • WEEKDAY
    • WEEKOFYEAR
    • YEAR
    • YEARWEEK

    4. Misc Functions

    • BIN
    • BINARY
    • CAST
    • COALESCE
    • CONNECTION_ID
    • CONV
    • CONVERT
    • CURRENT_USER
    • DATABASE
    • GROUP BY
    • HAVING
    • IF
    • IFNULL
    • ISNULL
    • LAST_INSERT_ID
    • NULLIF
    • SESSION_USER
    • SYSTEM_USER
    • USER
    • VERSION

    Conclusion

    The SQL commands cheat sheet allows you to construct and manipulate a wide range of database objects with the distinct CREATE, ALTER, and DROP commands. The data can then be loaded into those database objects using commands like INSERT. A comprehensive range of commands, including SELECT, DELETE, and TRUNCATE, as well as cursor commands like DECLARE, OPEN, FETCH, and CLOSE, can be used to alter the data. We hope that your search for the comprehensive SQL syntax cheat sheet and SQL query cheat sheet should have ended here.

    SQL is a very significant tool in the data management domain for which you can join the Knowledgehut MySQL Training.

    Frequently Asked Questions (FAQs)

    1What are the important topics in SQL?

    Some important topics in SQL include Functions, Views, Keywords, Keys, Triggers, Operators, and so on.  

    2How long will it take to learn SQL?

    It takes around two to three weeks to learn SQL. However, some institutes might take around a month to complete the entire training.

    3How many types of SQL commands are there?

    There are mainly 3 types of SQL commands namely

    • DDL (Data Definition Language)
    • DML (Data Manipulation Language)
    • DCL (Data Control Language)
    4What is schema in SQL?

    The schema in SQL refers to a list of logical data structures or database objects associated with the underlying DataBase.  

    5What is a trigger in SQL?

    A trigger in SQL refers to a unique type of stored procedure that runs on its own during the occurrence of an event in the database server.

    Profile

    Mounika Narang

    Author

    Mounika Narang is a project manager having a specialisation in IT project management and Instructional Design. She has an experience of 10 years 
    working with Fortune 500 companies to solve their most important development challenges. She lives in Bangalore with her family.

    Share This Article
    Ready to Master the Skills that Drive Your Career?

    Avail your free 1:1 mentorship session.

    Select
    Your Message (Optional)

    Upcoming Programming Batches & Dates

    NameDateFeeKnow more
    Course advisor icon
    Course Advisor
    Whatsapp/Chat icon