Getting started with SQL - Detailed Explanation

In this article we will cover basics before you get started with working on SQL. We will cover the following topics in detail

• Overview on relational database
• What is SQL
• Comparison between SQL and MySQL
• Steps to install SQL on both Windows and Linux environment
• Creating your first table using SQL
• List of most used keywords in SQL

What is a Relational Database?

A relational database is one type of database that stores and provides access to data points that are related to one another. Relational database Consists of a collection of relations, each of which is assigned a unique name. A row in a table represents a relationship among a set of values. Since a table is a collection of such relationships, there is a close correspondence between the concept of the table and the mathematical concept of relation, from which the relational data takes its name

The real power of SQL, however, comes from working with data from multiple tables at once. The term "relational database" refers to the fact that the tables within it "relate" to one another—they contain common identifiers that allow information from multiple tables to be combined easily

What is SQL

SQL (Structured Query Language), is a standard language that is designed for managing data in a relational database. This language originally called Sequel was implemented as part of the System R project in the early 1970s. The Sequel language has evolved since then, and its name has changed to SQL

SQL is the most common method of dealing with data in databases today. SQL allows users to create, read, manipulate, and change data. SQL is semantically easy to understand and learn and also it can be used to access large amounts of data directly so it is commonly used by engineers in software development, and popularly used by data analysts also

The SQL language has several parts:

• Data-definition language (DDL): The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schema
• Data-manipulation language (DML): The SQK DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database
• Integrity: The SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed
• View definition: The SQL DDL includes commands for defining views
• Transaction control: SQL includes commands for specifying the beginning and end of transactions
• Embedded SQL and dynamic SQL: Embedded and dynamic SQL define how SQL statements can be embedded within general-purpose programming languages, such as C, C++, and java
• Authorization: The SQL DDL includes commands for specifying access rights to relations and view

SQL vs MySQL Comparison

Following are the key differential points between SQL and MySQL

• SQL is a query language, whereas MySQL is a relational database that uses SQL to query a database.
• SQL is a language that is used to operate your database whereas MySQL was one of the first open-source databases available in the market
• SQL is used in the accessing, updating, and manipulation of data in a database while MySQL is an RDBMS that allows keeping the data that exists in a database organized
• SQL is a Structured Query Language and MySQL is an RDBMS to store, retrieve, modify and administrate a database.

• SQL is developed by Microsoft Corporation and named as Microsoft SQL Server (MS SQL). On the other hand, MySQL is developed by Oracle Corporation
• MySQL does not require a large amount of storage space for performing different operations. It supports multiple storage engines. MySQL also supports plug-in storage engines. On the other hand, MS SQL supports only a single storage engine. Therefore, programmers need to be updated with more improved engines

Tools available to practice SQL

Following are the 10 best most widely used SQL editor tools

1. Microsoft SQL Server Management Studio

SQL Server Management Studio (SSMS) is a software application first launched with Microsoft SQL Server 2005 that is used for configuring, managing, and administering all components within Microsoft SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, and build queries and scripts

Download link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

2. MySQL Workbench

MySQL Workbench is a visual editor that unifies data modeling, SQL development, and database administration in one interface. It allows you to visually design, generate, and manage databases using any OS. Also, the platform is capable of reinforcing your server configuration, reverse engineering, and database backup tasks

MySQL Workbench enables a DBA, developer, or data architect to visually design, model, generate, and manage databases, MySQL Workbench is available on Windows, Linux, and Mac OS X

Download link: https://www.mysql.com/products/workbench/

3. Oracle SQL Developer

Oracle SQL Developer is a free, integrated development environment that simplifies the development and management of Oracle databases in both traditional and cloud deployments

Oracle SQL Developer has a no-fuss SQL editor that allows you to execute statements and scripts as quickly as possible. Some of its functionalities include SQL formatting, script execution recall, tuning advisor, and visual query builder. It even has an integrated file history and a change logging tool that will allow you to track activities in your database

Download link: https://www.oracle.com/tools/downloads/sqldev-downloads.html

4. DbVisualizer

DbVisualizer is a universal database tool that has been designed for use by database administrators, software developers, and data analysts. The tool provides a straightforward, all-in-one user interface for managing enterprise databases

Key features of DbVisualizer include modules for database object management, database connection management, query building, SQL editing, and database server management. Staple features include task monitoring, reference graphs, charts, SQL history, command-line interface, and SSH

Download link: https://www.dbvis.com/download/

5. DBeaver

DBeaver is a free and open-source multi-platform database software trusted by database administrators, developers, and SQL programmers worldwide. Supporting all major databases and operating systems, this platform can easily bring together your different database management operations in a single hub. This makes it ideal for developers handling multiple databases at a time

DBeaver supports all major database providers, including MySQL, PostgreSQL, MariaDB, SQLite, SQL Server, and MongoDB. This allows users to not only import and export data quicker but also let you assemble connections between databases in a single interface. It even organizes your SQL scripts in multiple tabs, so you don’t have to perform coding in a separate window

Download link : https://dbeaver.io/

6. RazorSQL

RazorSQL is a fully-featured database application ideal for developers and DBAs who want complete control over multiple databases. It is equipped with a database browser, database administration feature, SQL query tool, and SQL editor that can easily adapt to the varying needs of your operations. What’s more, the software has a built-in database (HSQLDB) that requires no manual configuration

RazorSQL offers core SQL editing functionalities such as syntax highlighting, auto-completion, multiple query execution, as well as function lookup. In addition to that, it comes with a powerful EditRocket code editor that allows it to support programming languages other than SQL

Download link: https://razorsql.com/download.html

7. SQLite Studio

SQLite Studio is a free and open-source database manager. It is equipped with a simple SQL editor as well as a database administration feature that will allow you to monitor the status of your database as well as make sure that it is in tiptop condition. It brings together SQLite 3 and SQLite2 features in a simple GUI and can support different types of operating systems for easy deployment

SQLite Studio is a fully customizable platform so you can toggle the platform’s colors and fonts according to your preference. It is even skinnable so you can make the interface look native for whatever operating system or environment you’re working on. To top it all off, SQLite Studio allows you to personalize your own shortcuts

Download link: https://sqlitestudio.pl/

8. dbForge Studio

dbForge Studio is a database development and management software created by Devart in 1997. It serves as an integrated environment for various popular database providers such as PostgreSQL, Oracle, Microsoft, and MySQL. With it, developers and database architects can have access to the tools they require in tackling database design, implementation, testing, performance tuning, analysis, and monitoring. It also has a DevOps suite that enables continuous integration and delivery

Download link: https://www.devart.com/dbforge/sql/studio/

9. WinSQL

WinSQL is a universal database software solution that allows you to facilitate a heterogeneous database environment at no cost. Touted as a Swiss-army knife for database management, this platform brings together all the essential tools to develop a well as maintain your databases. It uses drag-and-drop data migration tools, E/R diagramming, MS Excel integration, as well as advanced SQL scripting to help you manage some of the most popular database systems on the market

WinSQL leverages E/R diagrams to help you visualize the design of your database. This makes it easier to map out the organization of your database as well as manage the relations of different objects and structures

Download link: https://web.synametrics.com/snowflake.htm

10. SQuirreL SQL

SQuirreL SQL is a free and open-source client program ideal for connecting JDBC-compliant databases. It comes with tools for table chart creation, advanced script generation, as well as graphing features that make it easy to manage databases. Moreover, it has a robust SQL Editor that not only allows you to perform coding but also makes coding more efficient with its completion pop-up options. It even has bookmarks for common SQL statements to make finding them easy later on

SQuirreL SQL has a results tab that gives you a complete overview of the information you want from your database. Here, you can compile various queries in a single table and even edit the contents within your Object tree, making it easy to analyze query results

Download link: http://squirrel-sql.sourceforge.net/

Setting up SQL environment in Windows

We can install and configure any tool from the above list for setting SQL environment in Windows operating system

In this article, we are covering three tools from the above list Microsoft SQL Server and  MySQL Workbench with steps. Below is a step-by-step process on how to download SQL in Windows 10:

Step-1: Download MSSQL Server

Download SQL in Windows 10 from the following link
https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Microsoft provides two specialized free SQL download editions to work on MS SQL server:

1. Developer
2. Express

Click on the Developer download now button, We will get SQL server installation set up as ‘SQLServer2017-SSEI-Dev.exe’

Step-2: Install SQL Server on Windows 10

Double click on “SQLServer2017-SSEI-Dev.exe”, The below screen will appear with three options: Basic, Custom, and Download files

Accept the terms then Choose the location

Below ‘SQL server install location’ window will appear

• The Default location is C:\Program Files\Microsoft SQL Server
• Optionally, we can also change the installation location by clicking on 3. Once the location is selected, click the ‘Install’ button to start SQL installation Windows 10

Below ‘Downloading install package’ progress screen will be displayed. Wait until the SQL software download is complete

Finish the installation process

Once installation is completed successfully, the below screen will appear

Step-3: Download MySQL Workbench on Windows 10

Below is a step-by-step process on how to setup MySQL Workbench in Windows 10. Download MySQL Workbench  from the link https://www.mysql.com/downloads/

Select MySQL Installer for Windows, Choose the desired installer and click on the download

Step-4: Install MySQL Server, MySQL Workbench and MySQL Shell on Windows 10

After the download, open the installer. It will ask for permission; when it does, click Yes.

The installer will then open. Now, it will ask to choose the setup type. Here, select Custom

Click on Next. With this, you will install MySQL server, MySQL Workbench, and MySQL shell

Open MySQL Servers, select the server you want to install and move it to the  Products/Features to be installed window section. Now, expand Applications, choose MySQL Workbench and MySQL shell. Move both of them to ‘Products/Features to be installed’

Click on the Next button. Now, click on the Execute button to download and install the MySQL server, MySQL Workbench, and the MySQL shell

Once the product is ready to configure, click on Next. Under Type and Networking, go with the default settings and select Next.

For authentication, Set your MySQL Root password and click on next

Go for the default windows service settings and under apply configuration, click on execute. Once the configuration is complete, click on the finish

Complete the installation. This will now launch the MySQL Workbench and the MySQL Shell

Once MySQL Workbench is installed, select the Local instance and enter the password

Setting up SQL environment in Linux

Following are the installation steps to setting SQL Server in Ubuntu

Step-1: Import public add Repository Key

Execute the following command to add the repository to install SQL Server

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

Sample Output

Register the Microsoft SQL Server Ubuntu repository for SQL Server 2019. Replace [version] with your OS version. You can get the version from /etc/lsb-release file.

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/[version]/mssql-server-2019.list)"

$ grep DISTRIB_CODENAME /etc/lsb-release
DISTRIB_CODENAME=focal

$ grep DISTRIB_CODENAME /etc/lsb-release
DISTRIB_CODENAME=lucid

Step-2: Install SQL Server on Linux

sudo apt-get update
sudo apt-get install -y mssql-server

Sample Output:

Step-3: Configure SQL Server

Execute the following command to start the configuration of sql server

deepak@ubuntu:~$ sudo /opt/mssql/bin/mssql-conf setup
Locale en_IN not supported. Using en_US.
usermod: no changes
Choose an edition of SQL Server:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID) - CPU Core utilization restricted to 20 physical/40 hyperthreaded
  7) Enterprise Core (PAID) - CPU Core utilization up to Operating System Maximum
  8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=2109348

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 2  <-- Choose your edition
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=2104294

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010


Choose the language for SQL Server:
(1) English
(2) Deutsch
(3) Español
(4) Français
(5) Italiano
(6) 日本語
(7) 한국어
(8) Português
(9) Русский
(10) 中文 – 简体
(11) 中文 （繁体）
Enter Option 1-11: 1  <-- Select language
Enter the SQL Server system administrator password:  <-- Provide your admin password
Confirm the SQL Server system administrator password:   <-- Re-enter the admin password
Configuring SQL Server...

ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

Step-4: Check status of SQL Server service

Once the configuration is done, verify that the service is running

systemctl status mssql-server --no-pager

Sample Output

Create your first table using SQL

Every tool or Editor of SQL has different steps to create a table in SQL, in the following section we are giving examples for windows of SQL Server and for Linux ubuntu

Creating table in SQL Server 2019

Following are the steps to create a table in SQL Server on Windows 10 platform

Step 1: In SSMS, in Object Explorer, connect to the instance of Database Engine that contains the database to be modified

Step 2: In Object Explorer, expand the Databases node and then expand the database that will contain the new table.

Step 3: In Object Explorer, right-click the Tables node of your database and then click New Table.

Step 4: Type column names, choose data types, and choose whether to allow nulls for each column as shown in the following illustration:

Step 5: To specify more properties for a column, such as identity or computed column values, click the column, and in the column properties tab, choose the appropriate properties.

Step 6: To specify a column as a primary key, right-click the column and select Set Primary Key

Step 7: To create foreign key relationships, check constraints, or indexes, right-click in the Table Designer pane and select an object from the list as shown in the following illustration:

Step 8: From the File menu, choose Save table name, In the Choose Name dialog box, type a name for the table and click OK

Creating a table in MySQL on the Ubuntu platform

Following are the steps to create a table in SQL Server on Windows 10 platform

Step 1: Open a terminal window and log into the MySQL shell. Use either an existing MySQL user account or log in as root.

sudo mysql -u username\root -p

Step 2: Type the password for your account.

The mysql> prompt indicates that you are logged in the MySQL shell

Step 3: Create a database using the CREATE statement

mysql > CREATE DATABASE DBcompany;

Step 4: Verify that the database is created successfully or not by listing out all databases using the show command

mysql > SHOW DATABASES;

Step 5: Select the database to make changes to it by using the USE statement

mysql > USE DBcompany;

Step 6: Create a table using the CREATE command

mysql > create table tblemp (

empid  int primary key ,

ename varchar(20) NOT NULL,

empcontact  int NOT NULL,

empcity varchar(20) NOT NULL);

Step 7: Verify that the table is created using the DESCRIBE command

DESCRIBE tblemp;

The terminal prints out information about the table:

• Field – Indicates column name.
• Type – Specifies data type for the column (varchar for characters, int for numbers).
• Null – Indicates whether the column can remain with null values.
• Key – Displays the primary column.
• Default – Displays the column’s default value.
• Extra – Indicates additional information about the columns

List of most used keywords in SQL

• CREATE – used to create a database, table, views, and index
• PRIMARY KEY – uniquely identifies each of the records
• FROM - used to indicate the table from which the data is selected or deleted
• ADD  - used to add the column to the existing table
• DISTINCT - used to select distinct values
• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

Summary

In this article of Getting started with SQL, first, we have converged overview of the relational database management system and about SQL, the difference between SQL and MySQL, various tools of SQL with its download link, how to install and setup SQL environment in windows and Linux operating system as well as how to create database and steps to create a table in the editors of both windows and Linux operating system, at the end of the article we have listed out SQL keywords which are most commonly used with its usage

Further Reading

Steps to install MSSQL on Windows
Create Tables (Database Engine)