Sololearn blogWhat is SQL?
time to read 8 min read

What is SQL?

It’s an organised collection of  structured data mainly stored electronically in a computer system. Databases are usually managed or controlled by the database management system (DBMS).

For better efficiency and fast processing, we stored the data in rows and columns. The rows and columns mainly create tables. 

The data can then be easily accessed, managed, modified, updated, controlled, and organised. Most databases use structured query language (SQL) for writing and querying data.

How are SQL databases used?

When an SQL query is written & run, it is processed by a query optimiser. The query reaches SQL server, where it compiles in three phases; Parsing, Binding and Optimisation.

  • Parsing – A process to check the syntax
  • Binding – A process to check the query semantics
  • Optimisation – A process to generate the query execution plan

Lastly, all the possible permutations and combinations are generated to find the most effective query execution plan in an efficient time.

What is SQL used for?

Now that we understand what is SQL and DATABASE are and how it works, let’s see what SQL can do. This programming language has various uses for data analysts and data science professionals. It’s particularly helpful because it can —

  • Execute queries against a database
  • Retrieve data from a database
  • Insert data into a database
  • Update records in a database
  • Delete records from a database
  • Create new databases, or new tables in a database
  • Create stored procedures & views in a database
  • Set permissions on tables, procedures, and views

SQL database table structure

The tables are the database objects that behave as containers for the data, in which the data will be logically organised in rows and columns format. Each row is considered as an entity that is described by the columns that hold the attributes of the entity.

For example – the employee table contains one row for each employee, and each employee is described by the table columns that hold the employee’s information, such as the employee id, first name, last name, country and age.

Here are some commands and their uses –

  • CREATE – Creates a new table, a view of a table, or another object in the database.
  • ALTER – Modifies an existing database object, such as a table.
  • DROP – Deletes an entire table, a view of a table or other objects in the database.
  • SELECT – Retrieves certain records from one or more tables.
  • INSERT – Creates a record.
  • UPDATE – Modifies a record.
  • DELETE – Deletes a record.
  • GRANT – Gives a privilege to users.
  • REVOKE – Takes back privileges granted from users.

SQL data types

SQL Data Types define the type of value that can be stored in a table column. For example – if we want a column to store only integer values, then we can define its data type as int and for text value, we’ll define char or varchar.

SQL data types can be mainly divided into following categories.

  • Numeric – int, tinyint, bigint, float, real, etc.
  • Date and Time – Date, Time, Datetime, etc.
  • Character and String – char, varchar, text, etc.
  • Unicode character string – nchar, nvarchar, ntext, etc.
  • Binary – binary, varbinary, etc.
  • Miscellaneous – clob, blob, xml, cursor, table, etc.

Few important points about Data Types 

  • Data types listed here don’t include all the data types, these are the most popularly used data types. Some relational database vendors have their own data types that might be not listed here.
  • Every relational database vendor has its own maximum size limit for different data types, you don’t need to remember the limit.
  • Not all data types are supported by every relational database.
  • while designing database schema and writing SQL queries, make sure to check if the data types are supported or not.

Examples of SQL Databases

Oracle Database

Developed by Oracle Corporation, Oracle Database is based on a multi-model DBMS. It is widely used when processing online transactions.

PostgreSQL 

An open-source Relational Database Management System, Postgre is free to use. It is widely used for data warehousing.

Microsoft SQL Server

SQL Server, developed by Microsoft, is a Relational Database Management System. It is built on SQL, the standard query language for Database Management Systems.

MySQL

Based on Structured Query Language (SQL), MySQL is a Relational Database Management System. It is used in e-commerce platforms, data warehousing, etc. It is widely used as a web Database Management System.

IBM Db2

Db2 is a Relational Database Management System developed by IBM. It is designed to analyse, store and retrieve data efficiently.

PostgreSQL

PostgreSQL is an advanced, enterprise-class, and open-source relational database system. PostgreSQL supports both SQL (relational) and JSON (non-relational) querying.

PostgreSQL is used as a primary database for many web applications as well as mobile and analytics applications.

Common Use cases of PostgreSQL

  • General purpose transaction database

Large corporations and startups alike use PostgreSQL as primary databases to support their applications and products.

  • Robust database in the LAPP stack

LAPP stands for Linux, Apache, PostgreSQL, and PHP (or Python and Perl). PostgreSQL is primarily used as a robust back-end database that powers many dynamic websites and web applications.

  • Geospatial database

PostgreSQL with the PostGIS extension supports geospatial databases for geographic information systems (GIS).

Who uses PostgreSQL

Many companies have built products and solutions based on PostgreSQL. Some featured companies are Apple, Fujitsu, Red Hat, Cisco, Juniper Network, Instagram, etc.

Language supported in PostgreSQL

  • C/C++
  • Java
  • Ruby
  • Python 
  • C#
  • JavaScript – node.js
  • Perl
  • Go

MariaDB and MySQL :

MySQL is the largest open source database community. MariaDB is a fork from MySQL and is 100% compatible with prior versions of MySQL. However, while the charter for MariaDB remains open source and cross-platform, the future is unclear for MySQL.

MariaDB is an open source relational database management system that is a compatible drop-in replacement for the widely used MySQL database technology.

MariaDB is based on SQL and supports ACID-style data processing with guaranteed atomicity, consistency, isolation and durability for transactions. Among other features, the database also supports JSON APIs, parallel data replication and multiple storage engines, including InnoDB, MyRocks, Spider, Aria, TokuDB, Cassandra and MariaDB ColumnStore.

Much of the development work on the open source database has focused on achieving feature parity between MariaDB and MySQL. MariaDB Corp

Which Database Is Right For You?

Choosing the right database is more difficult because there are more than 250 databases available in the market.

But here we’ll find the right database according to our needs. There are some points to keep in mind while choosing the database. 

  • How many users use the application simultaneously.
  • Do we plan to scale the database in the future?
  • Wanna analyse the data or implement advanced technology such as machine learning or artificial intelligence.
  • Integration of data with other solutions like business intelligence tools.

Now which database will we use SQL (relational) or NoSQL (non-relational)?

SQL database – as we know the relational database consists of tables. To maintain and query the relational database, the DBMS uses structured query language (SQL).

Advantages of SQL database – is ideal for storing structured data such as id number, pin codes, credit card numbers, names, dates etc.

SQL is well maintained, has great support and works with most modern frameworks and libraries.

Most important is their security.

The best SQL databases are PostgreSQL and MySQL.

NoSQL database – is used to store or process unstructured data such as collected data from social media like photos, videos, audio files etc.

So when we need to store such type of data then we can go for a NoSQL database.

NoSQL Database is divided into four types:

  1. Key value store
    1. eg. DynamoDB and Redis
  2. Document store
    1. eg. MongoDB and Couchbase
  3. Column store
    1. eg. Apache Cassandra and Scylla
  4. Graph store
    1. eg. Neo4J and Datastax Enterprise 

Most important factors:

Atomicity – means each transaction is treated as a unit. It can either fail completely or succeed completely. If one of the operations fails then the whole transaction fails.

Consistency – means that only valid data can be written in the database. Valid data means the data follows all the rules. If input data is invalid then no data is written in the database.

Durability – means that data is saved by the system even if the transaction is failed. Data won’t be lost even if the system crashes.

Isolation – means that unfinished transactions remain isolated. Means that all the transactions are processed securely and independently.

Conclusion

As we can see, our choice of choosing the database for our project depends on several factors, including our type of data that we’re gonna collect and process, integration with our tools and our scaling approach. It’s not a question about SQL and NoSQL.

Table Basics

Tables are composed of rows and columns.. These are uniquely identified by their names. The data or information for the database are stored in these tables. 

Columns contain the column name, data type, and any other attributes for the column.

Rows contain the records or data for the columns. Here is an example table called students.

Roll no. First Name, Last Name and Perc (%) are the columns. The rows contain the data for this table.

Selecting Data

SELECT command is used to select the data.

For example – if we are selecting the only Name and Age column then the command should follow.

SELECT 
  Name, 
  Age 
FROM 
  employee;

This command will display Name and Age columns only.

Sometimes we need to display all the columns so in place of columns name we’ll use asterisk (*). The command looks like.

You’ll learn the basics with better understanding from here.

SELECT 
  * 
FROM 
  employee;

This command will display all the columns that are present in the table.

Creating Tables

For creating the tables in the SQL database we use the CREATE TABLE command. 

CREATE TABLE table_name (
  column1 datatype, column2 datatype, 
  column3 datatype, column4 datatype, 
  ...
);

table_name specifies the name of the table. Table names can be anything.

Column parameter specifies the name of the column.

Datatype parameter specifies the type of the data that column can hold like varchar, integer, date etc.

Let’s create a table and see how it looks.

CREATE TABLE Example 

CREATE TABLE employee (
  Id int, 
  Name varchar(255), 
  Country varchar(255), 
  Age int
);

The ID and Age column is of type int and will hold an integer value.

The Name and Country columns are of type varchar and will hold characters or string, and the maximum length for these fields is 255 characters.

The empty employee table will now look like this –

Creating the table using another existing table

It’s useful when we wanna create a copy of another existing table. This is also created by the CREATE TABLE command with the AS keyword.

The new table gets the same column definitions. You can select all columns or specific ones that depend on the user.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

CREATE TABLE new_table_name AS  
SELECT 
  column1, 
  column2, 
  ...columnN 
FROM 
  existing_table_name

Now we need to insert the data or values in the table.

Inserting into a Table

For inserting the data into the table, we use INSERT INTO command. There are two ways to insert data in the table. 

For intermediate level you’ll learn here.

First method – specifying both column name and data to be inserted.

INSERT INTO table_name (column1, column2, column3,...) 
VALUES 
  (value1, value2, value3,...);

Second method – in this method, you don’t need to specify the column names in the SQL query. But make sure the order of the values is in the same order as the columns in the table.

INSERT INTO table_name 
VALUES 
  (value1, value2, value3,...);

INSERT INTO example

INSERT INTO employeeVALUES (1, "Henery", "America", 34);

The above SQL command inserts a new record in the employee table. Now the table will look like this.

Insert Data Only in Specified Columns

Sometimes we need to insert specific records or data in the table. Suppose we need to insert only Name and Country.

The following SQL command will insert only Name and Country.

INSERT INTO employee (Name, Country) 
VALUES 
  ('Nick', 'Thailand');

With the above command, the values are inserted into only the Name and Country column.

Related blogposts