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
- 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.
- 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.
- 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.
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-
- With a single query, multiple records of data can be accessed at a time.
- It provides data security and integrity.
- The syntax is simple and easy to understand.
- Users can smoothly perform complex queries after having a clear understanding of the database.
Basic SQL Commands
It is used to create tables in the database.
It is used to insert values into tables.
SELECT and FROM
SELECT determines which columns or rows data to show.
This will give all the rows which satisfy the condition specified.
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 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 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 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 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’.
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 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 is used with aggregate functions. It is used to arrange identical data into groups.
This will group students’ names with their count.
HAVING is used to filter the aggregated data created by GROUP BY. The syntax for it is –
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 is used to combine the rows from different tables.
LIKE is used to find a specific pattern in the table. It is used with the WHERE clause.
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 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 –
|MEDIUMINT()||-8388608 To 8388607, 0 To 16777215 UNSIGNED|
|INT()||-2147483647 To 2147483647, 0 To 4294967295 UNSIGNED|
|BIGINT()||-9223372036854775808 To 9223373036854775808, 0 To 18446744073709551615 UNSIGNED.|
|FLOAT||A 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.|
|TEXT||It stores strings with a maximum length of 65535 characters.|
|DATETIME||YYYY-MM-DD HH:MM: SS|
|ENUM||It is to store text values from predefined values.|
|BINARY||Texts are stored in binary format.|
|BOOL||It 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 –
|=||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|
|IN||If any of expression is True in the set of expressions like IN (exp1,exp2,exp3,exp4)|
|LIKE||It contains “expression” like – LIKE “%expression%”|
Types of Operators in SQL
In SQL, there are three specified operators –
- Arithmetic operators
- Logical operators
- 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 –
Frequently Asked Questions
As discussed above, it is a database language, a procedural programming language to manage the data.
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.
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.
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
● NOT NULL
● FOREIGN KEY
Yes, we can code in SQL. We have seen the code above and how it works in SQL.
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.
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.