The Ultimate Guide on SQL Basics

What Is a Database?

A database is a huge collection of data that is stored and retrieved electronically from a system. The structured data stored in the database is processed, manipulated, controlled, and updated to perform various operations. Some of the popular databases used in the industries are Oracle, MySQL, PostgreSQL, MS SQL Server, and SQLite.

What Is SQL?

SQL is a standard programming language used to operate Relational Databases and carry out various operations such as inserting, manipulating, updating, and retrieving data from relational databases.

Why Is SQL Required?

SQL is required for the following tasks

• To create new databases, tables, and views
• To insert records in a database
• To update records in a database
• To delete records from a database
• To retrieve data from a database

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Applications of SQL

• SQL is used to create a database, define its structure, implement it, and perform various functions on the database.
• SQL is also used for accessing, maintaining, and manipulating already created databases.
• SQL is a well built language for entering data, modifying data, and extracting data in a database.
• SQL is enormously used as a Client/Server language to connect the front-end with the back-end, thus supporting the client/server architecture.
• SQL when deployed as Data Control Language(DCL), helps protect your database from unauthorized access.

Features of SQL

• SQL is used to access data within the relational database.
• SQL is very fast in extracting large amounts of data very efficiently.
• SQL is flexible as it works with multiple database systems from Oracle, IBM, Microsoft, etc.
• SQL helps you manage databases without knowing a lot of coding.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Basic Queries in SQL

In this section, we will run some exciting commands to start with on the MySQL Workbench. 

• SHOW Statment

It lists the databases present on the MySQL Workbench server host. 

Syntax:

show databases;

The following SQL query will show all the databases present on MySQL Workbench server host.

Show databases;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• USE Statment

The USE Statement selects a database and performs SQL operations on that database.The database remains default until the end of session or execution of another USE statement with some other database.

Syntax:

USE database_name;

The following SQL query will let you perform operations on the selected mysql database.

Use mysql;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• CREATE TABLE Statement

The CREATE TABLE statement is used in SQL to create a new table.

Syntax:

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    column3 datatype,

   ....

);

The following SQL query will create a customer table with attributes: customer id, customer name, age, gender, date of purchase, address, item, and price.

create table customers (Cust_Id int primary key, Cust_Name varchar(25),

Age int, Gender char(1), Dop date, Address varchar(20), Item varchar(15), Price float);

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• INSERT INTO Statement

The INSERT INTO statement is basically used to add new data in a table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

The following SQL query will insert data into the records in the customers table.

insert into customers values

(101, "Joseph", 22, "M", "2016-11-23", "Pune", "Vegetable", 80),

(102, "Nilesh", 21, "M", "2016-11-23", "New York", "softdrink", 800),

(103, "Vipul", 33, "M", "2016-11-23", "Miami", "candies", 620),

(104, "Anubhav", 61, "M", "2016-11-23", "Pune", "fruits", 250),

(105, "Utkarsh", 45, "M", "2016-11-23", "New York", "snacks", 380),

(106, "Ishan", 44, "M", "2016-11-23", "Miami", "potato", 60),

(107, "Ankit", 52, "M", "2016-11-23", "Chicago", "ice cream", 220),

(108, "Akshay", 36, "M", "2016-11-23", "Pune", "coffee", 80),

(109, "Akash", 28, "M", "2016-11-23", "New York", "Vegetable", 150),

(110, "Tanishq", 19, "M", "2016-11-23", "Miami", "noodles", 400),

(111, "Raghav", 24, "M", "2016-11-23", "Detroit", "cups", 3400),

(112, "Anand", 54, "M", "2016-11-23", "Pune", "watermelon", 680),

(113, "Amrit", 43, "M", "2016-11-23", "Miami", "facewash", 340),

(114, "Andrew", 18, "M", "2016-11-23", "Boston", "shampoo", 950),

(115, "Ricky", 26, "M", "2016-11-23", "Detroit", "vanilla", 290),

(116, "Cummins", 30, "M", "2016-11-23", "Chicago", "mango", 640),

(117, "Rabada", 40, "M", "2016-11-23", "Boston", "banana", 80),

(118, "Johnson", 55, "M", "2016-11-23", "New York", "batteries", 420),

(119, "Adam", 45, "M", "2016-11-23", "Boston", "router", 160),

(120, "Illeana", 25, "F", "2016-11-23", "Chicago", "brinjal", 550);

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• SELECT Statement

The most commonly used SQL command is the SELECT statement. SQL SELECT statement is used to select rows and columns from a table in the database.

Syntax:

SELECT column1, column2, ...

FROM table_name;

The following SQL query will return all the records of the customers table.

Select * from customers;

The following SQL query will return the columns customer id and customer name of all the records from the customers table.

select Cust_id,Cust_Name from customers;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• DROP TABLE Statement

The DROP Statement is basically used to delete a particular table. Dropping of the table will drop all the objects (tables, views, procedures, etc.) inside it. The user should have admin privileges for deleting a table.

Syntax:

DROP TABLE table_name;

The following SQL query will drop or delete the customers table from the database.

Drop table customers;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• TRUNCATE TABLE Statement

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

Syntax:

TRUNCATE TABLE Categories;

The following SQL query will delete all the records from the customers table.

truncate table customers;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• ALTER TABLE Statement

ALTER TABLE command is used to update the structure of a table by updating the definition of its columns. The ALTER command can perform the following functions:

1) Add, drop, modify table columns

2) Add and drop constraints

3) Enable and Disable constraints

Syntax:

ALTER TABLE table_name ADD column_name datatype;

The following SQL query will return the customers table without an age column.

alter table customers drop column age;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• DELETE Statement

A DELETE statement is used to delete fields from a table.

Syntax:

DELETE FROM table_name WHERE condition;

The following SQL query will delete the record with customer id = 102.

delete from customers where Cust_Id=102;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• UPDATE Statement

The UPDATE statement is basically used to update the existing records in a table.

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

The following SQL query will update the customer name to James and city name as Boston in the record with customer id = 101.

update customers set Cust_Name = "James", Address= "Boston" where Cust_Id = 101;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• DISTINCT Statement

The SELECT DISTINCT statement in SQL is used to return only unique values from a column.

Syntax:

SELECT DISTINCT column1, column2, ...

FROM table_name;

The following SQL query will return the unique cities where customers reside.

select distinct address from customers;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• MIN() Function

The MIN() function returns the smallest value of the selected numeric column in a table.

Syntax:

SELECT MIN(column_name)

FROM table_name

WHERE condition;

The following SQL query will return the minimum price spent by the customer.

select MIN(Price) AS LessPrice from customers;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• MAX() Function

The MAX() function returns the largest value of the selected numeric column.

Syntax:

SELECT MAX(column_name)

FROM table_name

WHERE condition;

The following SQL query will return the maximum price spent by the customer.

select MAX(Price) AS MaxPrice from customers;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• SUM Function

The SUM() function gives the total sum of a numeric column. 

Syntax:

SELECT SUM(column_name)

FROM table_name

WHERE condition;

The following SQL query will return the sum of the price spent by the customers.

select SUM(Price) from customers;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• AVG() Function

The AVG() function gives the average value of a numeric column. 

Syntax:

SELECT AVG(column_name)

FROM table_name

WHERE condition;

The following SQL query will return the average price spent by the customers.

select AVG(Price) from customers;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• GROUP BY CLAUSE

The GROUP BY clause is used to return data that is grouped according to one or more columns.

Syntax:

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s);

The below SQL statement counts the number of customers from each city.

select count(Cust_Id), Address from customers group by Address;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

• HAVING CLAUSE

The HAVING clause is mostly used with aggregate functions. Having clause filters the records from groups based on specified conditions.

Syntax:

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

HAVING condition

ORDER BY column_name(s);

The following SQL query will return the cities where there are more than three customers.

select count(Cust_Id), Address from customers group by Address having count(Cust_Id)>3;

• ORDER BY

The ORDER BY clause is used within a SELECT statement to extract output either in ascending or descending order of columns.

Syntax:

SELECT column-list

FROM table_name [WHERE condition]

[ORDER BY column1 [, column2, .. columnN] [DESC]];

The following SQL query will return the cities in an alphabetical order.

select * from customers order by address;

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Conclusion

SQL was developed to access the data within the Relational Databases. Ever since companies realized the need to work with data, SQL Developers have been in great demand. In this tutorial, we have covered the basics of SQL.

If you have any questions or inputs regarding this “The Ultimate Guide on SQL Basics” article, do share them with us in the comments section below. Our team will review and address them!

To get certified in SQL and start your career in it, check this course link: SQL Training.

Happy learning!