SQL CheatSheet For Developers and Beginners

Sql_Cheatsheet
Sql Cheat Sheet

Do you want to have all SQL queries in one place and concentrate more on writing queries than finding them? In this article, we will discuss as many SQL queries as we can to make your study curve easier.

Introduction To SQL

SQL stands for Structured Query Language, a language for storing and manipulating databases. SQL stores data in the form of tables (relations), retrieved and modified using SQL queries. Since 1987 it has been the standard of the International Standard Organisation (ISO).

In terms of career, It is always one of the top five most popular languages. By looking at the number of job postings on LinkedIn, more than 50,000 jobs specialised in SQL. Let’s learn more about it.


Importance of SQL

  1. Simple Syntax: SQL is not a programming language but is a query language; SQL queries are written simply with syntax having a resemblance to English sentences. Due to its simplicity, it is easy to grasp.
  1. Universal Database Language: SQL is the popular database language. It is the most common relational database language and supported by the most popular open-source database platforms.
  1. Understanding datasets: It isn’t easy to check or find the data in large datasets. With SQL queries, processing the data becomes easy and consumes less time to get to the results.
  1. Required Skill: If you want to make a career in data analytics or Data Science, you should have SQL  in your skillset on your resume. SQL easily integrates with any other programming languages like Python, Dart, Java, JavaScript, and many more. So, it is definitely a plus one to your skillset.

Why SQL Overshadowed Previous Database Languages?

After IBM developed two non-relational database languages ISAM and VSAM, which allowed storing, retrieving, and delete data one record at a time.

In 1973, IBM introduced the SEQUEL, which then changed to SQL. In 1979, Oracle introduced the first official version of SQL, inspired by the IBM version.

SQL overtook previous languages because of the following reasons-

  1. With a single query, multiple records of data can be accessed at a time.
  2. It provides data security and integrity.
  3. The syntax is simple and easy to understand.
  4. Users can smoothly perform complex queries after having a clear understanding of the database.

Basic SQL Commands

CREATE TABLE

It is used to create tables in the database.

INSERT

It is used to insert values into tables.

blog banner 1

SELECT and FROM

SELECT determines which columns or rows data to show.

This will give all the rows which satisfy the condition specified.

ALTER

If you forgot to add a column or now want to add one more field, you can do it with the ALTER method.

It will create a new column in the student’s table named address, which can be NULL.

WHERE

WHERE is used to give the condition, or we can say to limit the number of rows in the result.

This will give all the rows of students who live in Canada.

UPDATE

UPDATE statement is used to update any record in the table.

This will update the name of the student with id  = 5 which was initially “John” like this –

AND

AND operator checks whether the multiple conditions computes to True value. If it is true, it gives the result corresponding to it.

It will give the following result where all the students are ‘M’ and live in ‘CANADA’-

OR

OR operator checks if any of the given conditions are true and will provide the combined result of every condition which holds True value.

This will give the students which are ‘M’ or live in ‘CANADA’.

AVG()

This method gives the average value of data of the specified column.

It will give the average of all the values of column fare.

BETWEEN

BETWEEN gives the values in the range. It is used to filter the data. Values can be any number, text, or dates.

This will give the student names with their fare in the range of 10 and 20.

GROUP BY

GROUP BY is used with aggregate functions. It is used to arrange identical data into groups.

This will group students’ names with their count.

HAVING

HAVING is used to filter the aggregated data created by GROUP BY. The syntax for it is –

ORDER BY

ORDER BY gives the way to sort the data in ascending or descending order. We can sort the student’s table according to name, fare, or address.

Arranging names in descending order,

This will give the following result.

Arranging names in ascending order,

It will give the following output,

INNER JOIN

INNER JOIN is used to combine the rows from different tables.

LIKE

LIKE is used to find a specific pattern in the table. It is used with the WHERE clause.

LIMIT

LIMIT is used to limit the number of rows in the output or apply the operation to a limited number of rows.

This will give names of only 5 starting rows.

DELETE

DELETE is used to either delete a particular data from the table or a complete table.

This will delete students whose id =1. The table can be deleted using the following syntax –

CommandDescription
MEDIUMINT()-8388608 To 8388607, 0 To 16777215 UNSIGNED
INT()-2147483647 To 2147483647, 0 To 4294967295 UNSIGNED
BIGINT()-9223372036854775808 To 9223373036854775808, 0 To 18446744073709551615 UNSIGNED.
FLOATA small floating-point number
DOUBLE(,)A large floating-point number.
DECIMAL(,)A double for fixed-value points.
CHAR()Fixed 0 To 255 characters
VARCHAR()Variable 0 to 255 characters.
TEXTIt stores strings with a maximum length of 65535 characters.
DATEYYYY-MM-DD
DATETIMEYYYY-MM-DD HH:MM: SS
TIMESTAMPYYYYMMDDHHMMSS
TIMEHH:MM: SS
ENUMIt is to store text values from predefined values.
BINARYTexts are stored in binary format.
BOOLIt is used to store bool values. (True or False)

Advanced Filtering in SQL

Filtering is done using comparison operators. Let us see some of the comparison operators –

OperatorDescription
=It is “equal to” the operator for checking whether two comparable values are equal.
<> Not equal operator
!=Not equal to
Less than a value
<=Less than or equal to a value
Greater than a value
>=Greater than or equal to
INIf any of expression is True in the set of expressions like   IN (exp1,exp2,exp3,exp4)
LIKEIt contains “expression” like –   LIKE “%expression%”

Types of Operators in SQL

In SQL, there are three specified operators –

  1. Arithmetic operators
  2. Logical operators
  3. Comparison operators

A query can be inside another query. In the subquery, the outer query is the main query, and the inner query is the subquery. First, the subqueries are executed, and their result is passed to the main query. Subqueries are of two types –

  1. Correlated
  2. Non-correlated

Frequently Asked Questions

What is SQL?

As discussed above, it is a database language, a procedural programming language to manage the data.

What are the basic SQL commands?

The basic SQL commands are Create, Insert, delete, update and alter commands. The complex SQL commands involve joining, order by, having, where and combining these clauses.

What is the primary key?

A primary key is a column that uniquely identifies each row in the table. A primary key cannot be NULL. It can either be auto-incremented or manually added.

What are the constraints?

Constraints are limitations on the data type. Constraints are to be specified while creating a column or altering a table. Some of the constraints are –
● PRIMARY KEY
● UNIQUE
● NOT NULL
● FOREIGN KEY

Can you code in SQL?

Yes, we can code in SQL. We have seen the code above and how it works in SQL.

Is SQL harder than Python?

Until the queries are simple, SQL looks simpler than Python. But when the queries become complex, SQL becomes more challenging to understand. Simultaneously, the learning curve of SQL is more comfortable as it is a declarative language.

Conclusion

I hope you got a clear understanding of all the SQL queries.

There is a lot more to learn about it. It needs more and more practice to write neat and clean SQL queries without any help.

Don’t just read the code but experiment with it; you will only get a fruitful result.

You can also read the following SQL Books to have a deeper knowledge of SQL.