SQL Explained: Essential Guide for Beginners
Aditya Gadhave
What is SQL?
SQL is a short-form of the structured query language, and it is pronounced as S-Q-L or sometimes as See-Quell.
This database language is mainly designed for maintaining the data in relational database management systems.
It is a special tool used by data professionals for handling structured data (data which is stored in the form of tables).
It is also designed for stream processing in RDSMS.
You can easily create and manipulate the database, access and modify the table rows and columns, etc.
This query language became the standard of ANSI in the year of 1986 and ISO in the year of 1987.
If you want to get a job in the field of data science, then it is the most important query language to learn.
Big enterprises like Facebook, Instagram, and LinkedIn, use SQL for storing the data in the back-end.
Why SQL?
Following are the reasons which explain why it is widely used:
The basic use of SQL for data professionals and SQL users is to insert, update, and delete the data from the relational database.
SQL allows the data professionals and users to retrieve the data from the relational database management systems.
It also helps them to describe the structured data.
It allows SQL users to create, drop, and manipulate the database and its tables.
It also helps in creating the view, stored procedure, and functions in the relational database.
It allows you to define the data and modify that stored data in the relational database.
It also allows SQL users to set the permissions or constraints on table columns, views, and stored procedures.
Process of SQL
When we are executing the command of SQL on any Relational database management system, then the system automatically finds the best routine to carry out our request, and the SQL engine determines how to interpret that particular command.
Structured Query Language contains the following four components in its process:
Query Dispatcher
Optimization Engines
Classic Query Engine
SQL Query Engine, etc.
A classic query engine allows data professionals and users to maintain non-SQL queries. The architecture of SQL is shown in the following diagram:
Advantages of SQL
SQL provides various advantages which make it more popular in the field of data science. It is a perfect query language which allows data professionals and users to communicate with the database. Following are the best advantages or benefits of Structured Query Language:
1. No programming needed
SQL does not require a large number of coding lines for managing the database systems. We can easily access and maintain the database by using simple SQL syntactical rules. These simple rules make the SQL user-friendly.
2. High-Speed Query Processing
A large amount of data is accessed quickly and efficiently from the database by using SQL queries. Insertion, deletion, and updation operations on data are also performed in less time.
3. Standardized Language
SQL follows the long-established standards of ISO and ANSI, which offer a uniform platform across the globe to all its users.
4. Portability
The structured query language can be easily used in desktop computers, laptops, tablets, and even smartphones. It can also be used with other applications according to the user's requirements.
5. Interactive language
We can easily learn and understand the SQL language. We can also use this language for communicating with the database because it is a simple query language. This language is also used for receiving the answers to complex queries in a few seconds.
6. More than one Data View
The SQL language also helps in making the multiple views of the database structure for the different database users.
Disadvantages of SQL
With the advantages of SQL, it also has some disadvantages, which are as follows:
1. Cost
The operation cost of some SQL versions is high. That's why some programmers cannot use the Structured Query Language.
2. Interface is Complex
Another big disadvantage is that the interface of Structured query language is difficult, which makes it difficult for SQL users to use and manage it.
3. Partial Database control
The business rules are hidden. So, the data professionals and users who are using this query language cannot have full database control.
SQL vs No-SQL
The following table describes the differences between the SQL and NoSQL, which are necessary to understand:
| SQL | No-SQL |
| 1. SQL is a relational database management system. | 1. While No-SQL is a non-relational or distributed database management system. |
| 2. The query language used in this database system is a structured query language. | 2. The query language used in the No-SQL database systems is a non-declarative query language. |
| 3. The schema of SQL databases is predefined, fixed, and static. | 3. The schema of No-SQL databases is a dynamic schema for unstructured data. |
| 4. These databases are vertically scalable. | 4. These databases are horizontally scalable. |
| 5. The database type of SQL is in the form of tables, i.e., in the form of rows and columns. | 5. The database type of No-SQL is in the form of documents, key-value, and graphs. |
| 6. It follows the ACID model. | 6. It follows the BASE model. |
| 7. Complex queries are easily managed in the SQL database. | 7. NoSQL databases cannot handle complex queries. |
| 8. This database is not the best choice for storing hierarchical data. | 8. While No-SQL database is a perfect option for storing hierarchical data. |
| 9. All SQL databases require object-relational mapping. | 9. Many No-SQL databases do not require object-relational mapping. |
| 10. Gauges, CircleCI, Hootsuite, etc., are the top enterprises that are using this query language. | 10. Airbnb, Uber, and Kickstarter are the top enterprises that are using this query language. |
| 11. SQLite, Ms-SQL, Oracle, PostgreSQL, and MySQL are examples of SQL database systems. | 11. Redis, MongoDB, Hbase, BigTable, CouchDB, and Cassandra are examples of NoSQL database systems. |
SQL Data Types
Data types are used to represent the nature of the data that can be stored in the database table.
For example, in a particular column of a table, if we want to store a string type of data then we will have to declare a string data type of this column.
Data types mainly classified into three categories for every database.
String Data types
Numeric Data types
Date and time Data types
| CHAR(Size) | It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1. |
| VARCHAR(Size) | It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters. |
| BINARY(Size) | It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. Default is 1. |
| VARBINARY(Size) | It is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the maximum column length in bytes. |
| TEXT(Size) | It holds a string that can contain a maximum length of 255 characters. |
| TINYTEXT | It holds a string with a maximum length of 255 characters. |
| MEDIUMTEXT | It holds a string with a maximum length of 16,777,215. |
| LONGTEXT | It holds a string with a maximum length of 4,294,967,295 characters. |
| ENUM(val1, val2, val3,...) | It is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted. |
| SET( val1,val2,val3,....) | It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list. |
| BLOB(size) | It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes. |
MySQL Numeric Data Types
| BIT(Size) | It is used for a bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64. The default value is 1. |
| INT(size) | It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and unsigned range varies from 0 to 4294967295. The size parameter specifies the max display width that is 255. |
| INTEGER(size) | It is equal to INT(size). |
| FLOAT(size, d) | It is used to specify a floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal point is specified by d parameter. |
| FLOAT(p) | It is used to specify a floating point number. MySQL used p parameter to determine whether to use FLOAT or DOUBLE. If p is between 0 to24, the data type becomes FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE(). |
| DOUBLE(size, d) | It is a normal size floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal is specified by d parameter. |
| DECIMAL(size, d) | It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by d parameter. The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0. |
| DEC(size, d) | It is equal to DECIMAL(size, d). |
| BOOL | It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true. |
MySQL Date and Time Data Types
| DATE | It is used to specify date format YYYY-MM-DD. Its supported range is from '1000-01-01' to '9999-12-31'. |
| DATETIME(fsp) | It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from '1000-01-01 00:00:00' to 9999-12-31 23:59:59'. |
| TIMESTAMP(fsp) | It is used to specify the timestamp. Its value is stored as the number of seconds since the Unix epoch('1970-01-01 00:00:00' UTC). Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. |
| TIME(fsp) | It is used to specify the time format. Its format is hh:mm:ss. Its supported range is from '-838:59:59' to '838:59:59' |
| YEAR | It is used to specify a year in four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000. |
SQL Server Data Types
SQL Server String Data Type
| char(n) | It is a fixed width character string data type. Its size can be up to 8000 characters. |
| varchar(n) | It is a variable width character string data type. Its size can be up to 8000 characters. |
| varchar(max) | It is a variable width character string data types. Its size can be up to 1,073,741,824 characters. |
| text | It is a variable width character string data type. Its size can be up to 2GB of text data. |
| nchar | It is a fixed width Unicode string data type. Its size can be up to 4000 characters. |
| nvarchar | It is a variable width Unicode string data type. Its size can be up to 4000 characters. |
| ntext | It is a variable width Unicode string data type. Its size can be up to 2GB of text data. |
| binary(n) | It is a fixed width Binary string data type. Its size can be up to 8000 bytes. |
| varbinary | It is a variable width Binary string data type. Its size can be up to 8000 bytes. |
| image | It is also a variable width Binary string data type. Its size can be up to 2GB. |
SQL Server Numeric Data Types
| bit | It is an integer that can be 0, 1 or null. |
| tinyint | It allows whole numbers from 0 to 255. |
| Smallint | It allows whole numbers between -32,768 and 32,767. |
| Int | It allows whole numbers between -2,147,483,648 and 2,147,483,647. |
| bigint | It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. |
| float(n) | It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53. |
| real | It is a floating precision number data from -3.40E+38 to 3.40E+38. |
| money | It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807. |
SQL Server Date and Time Data Type
| datetime | It is used to specify date and time combination. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds. |
| datetime2 | It is used to specify date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds |
| date | It is used to store date only. It supports range from January 1, 0001 to December 31, 9999 |
| time | It stores time only to an accuracy of 100 nanoseconds |
| timestamp | It stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only one-time stamp variable. |
SQL Server Other Data Types
| Sql_variant | It is used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data. |
| XML | It stores XML formatted data. Maximum 2GB. |
| cursor | It stores a reference to a cursor used for database operations. |
| table | It stores result set for later processing. |
| uniqueidentifier | It stores GUID (Globally unique identifier). |
Subscribe to my newsletter
Read articles from Aditya Gadhave directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
Aditya Gadhave
Aditya Gadhave
👋 Hello! I'm Aditya Gadhave, an enthusiastic Computer Engineering Undergraduate Student. My passion for technology has led me on an exciting journey where I'm honing my skills and making meaningful contributions.