By looking at the job postings, we can see an enormous demand for SQL Developers across the globe. From finance to tech companies, all of them deal with massive amounts of data. Therefore, these industries need to process and extract meaningful information from the data. SQL is used for accessing and managing data in relational databases. This article will teach you SQL basics and help you understand various aspects of Structured Query Language.
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
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.
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;
-
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;
-
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);
-
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);
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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)
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;
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!
