16
DecSQL often named as Structured Query Language is a declarative programming language that is domain specific and used to design and manage data models and to organize data in relational databases. SQL was proposed by the Donald Chamberlin and Raymond Boyce in 1974. It is based on the static typing, and strong discipline language supports cross-platform operating systems. It has different sublanguages called statements to perform operations based on requirements. This blog is a complete guide to SQL cheat sheet and makes you more confident on the platform while using SQL.
SQL commands cheat sheet can be divided into different categories like Data Definition Language, Data control commands, Data manipulation language, etc. Here is the cheat sheet to help you in performing different operations based on different situations. Data Manipulation Language
Query | Description |
Select * from <table>; | This command checks the entire data from the given table. |
Select c1, c2 from <table>; | This command is used to retrieve two specific columns from the given table. |
Select c1, c2 from <table> WHERE condition; | This command is used to query data from the table based on a specific condition. |
Select distinct col from <table>; | This command is used to display distinct records from the table. |
Select distinct col from <table> WHERE condition; | This command is used to display distinct records from the table based on a specific condition. |
Select c1, c2 from <table> ORDER BY c2 ASC; | This command is used to query data from a given table and sort the retrieved data in ascending order. |
Select c1, c2 from <table> ORDER BY c2 DESC; | This command is used to query data from a given table and sort the retrieved data in descending order. |
Select aggregate(c1), c2 from <table> GROUP BY c2; | This command is used to query data from a given table and aggregate data based on a column. |
Select aggregate(c1), c2 from <table> GROUP BY c2 HAVING condition; | This command is used to query data from a given table and aggregate data based on a column and the given condition. |
Select c1, c2 from <t1> INNER JOIN <t2> ON condition; | This command is used to query data from multiple tables, and inner join the result based on the specified condition to a single output. |
Select c1, c2 from <t1> LEFT JOIN <t2> ON condition; | This command is used to query data from multiple tables and left join the result based on the specified condition to a single output. |
Select c1, c2 from <t1> RIGHT JOIN <t2> ON condition; | This command is used to query data from multiple tables, and right to join the result based on the specified condition to a single output. |
Select c1, c2 from <t1> FULL OUTER JOIN <t2> ON condition; | This command is used to query data from multiple tables, and full outer join the result based on the specified condition to a single output. |
Select c1, c2 from <t1> CROSS JOIN <t2>; | This command is used to query data from multiple tables, and cross joins the result to obtain the cartesian product of records and display it as a single output. |
Select c1, c2 from <t1> UNION Select c1, c2 from <t2>; | This command is used to query data from multiple tables and combine the result of records from two tables. |
Query | Description |
CREATE TABLE <t> column names, data type constraints; | This command creates a new table with new columns and data type constraints. |
ALTER TABLE <t>; | This command adds a new column to the table. |
DROP TABLE <t>; | This command deletes the entire table from the database. |
INSERT INTO <t> values, columns; | It is used to add data or records to the table. |
UPDATE TABLE set c1=updated-value | It is used to update the cell value in a table based on the column name. |
UPDATE TABLE set c1=updated-value, c2= updated-value WHERE condition | It is used to update multiple cell values in a table based on column names and the specific condition. |
DELETE FROM <t>; | This command deletes the entire data from the table, not the table structure. |
DELETE FROM <t> WHERE condition; | This command deletes the selected data from the table based on the specified condition. |
Query | Description |
GRANT <Object Privileges>ON<Object Name> TO <user> [GRANT OPTION] | This command is used to grant access to a particular user based on access and grant options. |
REVOKE <Object Privileges> ON <Object Name> FROM <user> | This command is used to revoke access to the particular user from the specified object. |
Function | Description |
Count | It is used to count the total number of elements in a list. |
SUM | It is used to return the sum of elements in a list |
MAX | It is used to return the maximum value from the list |
MIN | It is used to return the minimum value from the list |
AVG | It is used to calculate the average value from the list. |
Comparison Operator | What Does it Mean? |
= | Equal to |
<> | Not Equal to |
!= | Not Equal to |
< | Less Than |
<= | Less Than or Equal to |
> | Greater Than |
>= | Greater than or Equal to |
LIKE ’%expression% ’ | Contains ‘expression’ |
IN (‘exp1’,’exp2’,’exp3’) | Contains any of ‘exp1’,’exp2’,’exp3’ |
Data Types | Description |
Integer | It represents the whole number without any fraction |
Decimal | It gives a number with fraction part. |
Boolean | It returns the Boolean value either TRUE or FALSE. |
Date | It returns the DATE based on the selected format. |
Time | It returns the time in the decided format. |
Timestamp | Here, date and time are given together. |
Text | This is the most common data type that could be given as a set of alphabets, set of numbers, a mix of alphabets and numbers, etc. |
SQL operators | Description |
UNION | It is used to combine columns from multiple tables. |
INTERSECT | It is used to return the intersection value of two queries. |
MINUS | It is used to subtract the second result set from the first one. |
BETWEEN | It returns the middle value using MIN and MAX functions |
NOT LIKE | It returns the rows based on a matching pattern. |
NOT NULL | It will check either value in a table are NULL or NOT NULL. |
SQL keywords | Description |
SELECT | It is used to specify which column to query. You can use * to query the entire table. |
FROM | It is used to declare the table name that can be used to fetch the data. |
LIKE | It is a special operator to define a pattern for row or column using the WHERE clause. |
EQUAL To (=) | It is used to compared values either they are equal or not. |
WHERE | It is used to specify the condition. |
GROUP BY | To identical group data, we can use this keyword. |
HAVING | It is used along with aggregate functions which value should be returned once the specific condition is met. |
Inner Join | It is used to return all the rows when records in one table are similar to records in another table. |
Outer Join | It is used to return rows that are similar either in the left or right table. |
Left/Right Join | It is used to return rows that match the records in the right table for the right join and the left table for the left join. |
SQL is the most common database language used everywhere. The SQL command cheat sheet is generally used to perform different operations from basic to complex based on data requirements. Further, it is used to generate reports to import or export respectively from databases.
To make sure that data is stored reliably in database tables, normalize the databases to reduce the possibilities of anomalies. Here are some criteria for the first normal form (1NF).
Here are some criteria for the second normal form (2 NF) in SQL:
SQL value functions are used to perform operations on data that are frequently used by databases. Here are a few SQL value functions for your reference.
String Value Function | Description |
Substring | This function is used to extract the portion of a string i.e. substring from the source string. |
UPPER | It is used to convert the string to all Upper case |
LOWER | It is used to convert the string to all lower case |
CONVERT | It is used to convert string from one format to another. |
TRIM | This function is used to trim leading and trailing blanks. |
TRANSLATE | This function is used to translate one-character set to another in the source string. |
Numeric Value Functions | Description |
Position | This function returns the starting position of a target string within the source string. |
Character-length | It returns the total number of characters within a string. |
Octet_length | It returns the total number of octets within a string. |
Extract | It returns the single field from a particular interval. |
Read: How to Install Microsoft SQL Server Express
Date-time Value Function | Description |
Current_Date | This function returns the current Date. |
Current_Time | This function returns the current Time. |
Current_timestamp | This function returns the current Date and time together. |
An SQL injection cheat sheet is the resource in which you may find detailed technical information for different variants of SQL injection vulnerability. This cheat sheet is an excellent reference for testers who just started in with the web security domain. The SQL injection cheat sheet was proposed in 2007 and updated over time. This cheat sheet contains information mainly about SQL server, MySQL. PostgreSQL, Oracle, etc. you have to be extra cautious for complex queries usage because the real-live environment may vary based on situations.
Syntax references or Dirty SQL injection Tricks
1). Commenting Out
Line comments are generally used to ignore the rest of the query, so you don’t have to deal with fixing the syntax again.
/*Comment Here*/(SM)
DROP /*comment*/sampletable
DR/**/OP/*bypass Blacklisting*/sampletable
SELECT /*avoid-spaces*/password/**/FROM/**/Memebers
The second category is Inline Comment where rest of the query is generally commented by not closing them, or you can use it for blacklisting, removing spaces, find the value of database versions, etc. for example, here is the special code to determine the MySQL version. If you put this code as a comment, then it is executed in MySQL only. Also, this technique is used to execute the selected code when the current version of the server is higher than the supplied version.
/*! MYSQL Special SQL */ (M)
2). Stacking Queries
Read: MSBI Tutorial Guide for Beginner
Stacking queries mean executing multiple queries together in a single transaction. It is very useful from an injection point of view, especially in SQL server back ended applications. For example, the following query ends a query start the new one.
; (S)
SELECT * FROM members; DROP members--
Here is the database stacked query support table that shows which language are supported and which are not supported for stacked queries in SQL. PHP/MYSQL does not support stacked queries because of multiple database layers in most of the configurations. In the same way, Java does not support queries in Oracle. However, real-live scenarios may be different that depends on situations. Also, database standards are changed frequently to make it more familiar for users. Here a few stacked query attack samples for your reference.
ID: 10; DROP members --
SELECT * FROM products WHERE id = 10; DROP members --
3). If Statement
The blind SQL injection execution is based on IF statements. It is used to test simple stuff blindly and more accurately. Here is the simple syntax for If statement in SQL injection:
IF (condition, true-part, false-part)
SELECT IF (1=1, 'true' , 'false')
For the SQL server, if a statement can be used as:
Read: What is Update Query in SQL? How to Update (Column Name, Table, Statement, Values)
IF condition THEN true-part; ELSE false-part (S)
IF (1=1) SELECT 'true' ELSE SELECT 'false'
For Oracle, if statement can be used as:
BEGIN
IF condition THEN tru-part; ELSE false-part; END IF;
END; (O)
IF (1=1) THEN dbms_lock.sleep(3); ELSE dbms_lock.sleep(0);
END IF; END;
For PostgreSQL, if statement can be used as:
SELECT CASE WHEN condition THEN True-part ELSE false-part END; (p)
SELECT CASE WHEN (1=1) THEN 'A' ELSE 'B' END;
In case of a good production application, you cannot check error status on the page. It is not possible checking data through Union attacks or error-based attacks. Here, you should move ahead with blind SQL injection attacks for the data extraction. It can be divided into two major categories. These are normal blind and the Totally blind. In case of Normal Blind, we cannot check the response on the same page, but it still determines the response of a query through HTTP status code. In case of Totally blind, it is possible checking differences among any kind of output. It can be an injection, logging function, or any other query. However, it is not common using SQL injections in practice. For the normal blind, you may use IF or WHERE clause while the blind injection is based on waiting functions and analyzing response times.
Final Words:
This blog gives you a perfect idea of SQL commands cheat sheet, tips on using an SQL cheat sheet, and SQL injection cheat sheet, etc. this cheat sheet is extremely helpful for database developers who are struggling in executing complex queries. It is always practicing multiple operators and functions together to produce unique results. To get the more comprehensive idea of SQL, we encourage you to join SQL certification program at JanBask Training and start learning database concepts thoroughly.
Read: SQL Data Types for Oracle PL/SQL, MySQL, SQL Server, and MS Access
JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.
AWS
DevOps
Data Science
Hadoop
Salesforce
QA
Business Analyst
SQL Server
Search Posts
Trending Posts
Top 30 Core Java Interview Questions and Answers for Fresher, Experienced Developer 23.8k
Difference Between AngularJs vs. Angular 2 vs. Angular 4 vs. Angular 5 vs. Angular 6 16.5k
Cloud Computing Interview Questions And Answers 12.4k
SSIS Interview Questions & Answers for Fresher, Experienced 10.2k
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews