SQL Concepts and Queries

In this article, we will discuss the overview of SQL and will mainly focus on Concepts and Queries and will understand each with the help of examples. Let’s discuss it one by one.

Overview :
SQL is a computer language that is used for storing, manipulating, and retrieving data in a structured format. This language was invented by IBM. Here SQL stands for Structured Query Language. Interacting databases with SQL queries, we can handle a large amount of data. There are several SQL-supported database servers such as MySQL, PostgreSQL, sqlite3 and so on. Data can be stored in a secured and structured format through these database servers. SQL queries are often used for data manipulation and business insights better.

SQL Database :
Here, we will discuss the queries and will understand with the help of examples. 

Query-1 :
Show existing databases –
Let’s consider the existing database like information_schema, mysql, performance_schema, sakila, student, sys, and world. And if you want to show the exiting database then we will use the show database query as follows. 

SHOW DATABASES;  

 Output :

Existing database Name
information_schema
mysql
performance_schema
sakila
student
sys
world

Query-2 :
Drop a database –
Suppose we want to drop the database namely student.

DROP DATABASE student;
SHOW DATABASES;  

Database Name
information_schema
mysql
performance_schema
sakila
sys
world

Query-3 :
Create a database –
Suppose we want to create a database namely a bank.

CREATE DATABASE bank;
SHOW DATABASES;

Database Name
bank
information_schema
mysql
performance_schema
sakila
sys
world

Query-4 :
Using a database –

USE bank;

Query-5 :
Create a Table –
Here data type may be varchar, integer, date, etc.

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

Example –

CREATE TABLE IF NOT EXISTS Employee (
   EmployeeID int,
   FirstName varchar(55),
   LastName varchar(55),
   Email varchar(150),
   DOB date
);

Query-6 :
Show tables in the same database –

SHOW TABLES;

Query-7 :
Dropping a Table –

DROP TABLE table_name;

Query-8 :
Inserting values into an existing table –

INSERT INTO Employee 
VALUES(1111,'Dipak','Bera','dipakbera@gmail.com','1994-11-22');

Query-9 :
Fetching values in a table –

SELECT * FROM Employee;

Query-10 :
Not Null –
We can specify which column does not accept the null value when we insert a value(row) in a table. It will be done at the time of table creation.

CREATE TABLE table_name (
  column1 datatype NOT NULL,
  column2 datatype,
 ....
);

Query-11 :
Unique –
We can also specify that entries in a particular column should be unique.

CREATE TABLE table_name (
 column1 datatype UNIQUE,
 column2 datatype,
....
);

Example –

CREATE TABLE demo_table
(
EmployeeID int NOT NULL UNIQUE, 
FirstName varchar(55), 
LastName varchar(55)
);

KEY CONCEPTS in SQL :
Here, we will discuss some important concepts like keys, join operations, having clauses, order by, etc. Let’s discuss it one by one. 

• PRIMARY KEY –
  The constraint PRIMARY KEY suggests that entries should be neither null nor duplicate corresponding to the specified column.

CREATE TABLE IF NOT EXISTS Customer(
CustID int NOT NULL,
FName varchar(55),
LName varchar(55),
Email varchar(100),
DOB date,
CONSTRAINT customer_custid_pk  PRIMARY KEY(CustID)
);

• FOREIGN KEY –
  The FOREIGN KEY is used to build a connection between the current table and the previous table containing the primary key.

CREATE TABLE Account(
AccNo int NOT NULL,
AType varchar(20),
OBal int,
OD date,
CurBal int,
CONSTRAINT customer_AccNo_fk  FOREIGN KEY(AccNo) REFERENCES Customer(CustID)
);

• Here, AccNo column in the Account table is referred to by CustID column in the Customer table. Here Account table is a child table and the Customer table is the parent table.

ORDER BY :
The ORDER BY keyword is used to show the result in ascending or descending order. By default, it is in ascending order.

Syntax –

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

• Scenario-1 : 
  Suppose we have the Account Table as follows.

AccNo	AType	OBal	OD	CurBal
1111	savings	1000	1990-11-09	6000
1114	current	2000	1992-10-07	1000
1113	current	7000	1992-11-03	4000
1112	savings	1000	2003-12-12	3000

• Now, we will use the Order By command as follows.

SELECT * FROM Account ORDER BY CurBal;      

Output : 
(By default it will be in increasing order)

AccNo	AType	OBal	OD	CurBal
1114	current	2000	1992-10-07	1000
1112	savings	1000	2003-12-12	3000
1113	current	7000	1992-11-03	4000
1111	savings	1000	1990-11-09	6000

• Scenario-2 : 
  For descending order :

SELECT * FROM Account ORDER BY CurBal DESC;

Output :   

AccNo	AType	OBal	OD	CurBal
1111	savings	1000	1990-11-09	6000
1113	current	7000	1992-11-03	4000
1112	savings	1000	2003-12-12	3000
1114	current	2000	1992-10-07	1000

GROUP BY :
This keyword is used for grouping the results.

Example –

SELECT COUNT(AType) FROM Account GROUP BY AType;

Output :  

AType	count(AType)
savings	2
current	2

                          

JOIN CONCEPTS :
Here, we will discuss the join concept as follows.

• LEFT JOIN :
  The LEFT JOIN keyword returns all records from the left table (table1) along with the matching records from the right table (table2).                   
  Syntax – 

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

• RIGHT JOIN :
  The RIGHT JOIN keyword returns all records from the right table (table2) along with the matching records from the left table (table1).
• Syntax –

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

• INNER JOIN :       
  The INNER JOIN keyword returns all matching records from both the table.
•  Syntax – 

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

• FULL JOIN :
  The FULL JOIN or FULL OUTER JOIN keyword returns all records from both the table.
• Syntax –

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

•  Note –            
  This keyword is not used in MySQL’s latest version. Instead, the keyword UNION is used. Here the syntax is as follows.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

• SELF JOIN :
  This is a regular join between aliases of the same table.
• Syntax –

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

WHERE CLAUSE :
This clause is used for filtering our data.

Syntax –

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example –

SELECT  AccNo,CurBal FROM Account WHERE CurBal>=1000;

Output :

AccNo	CurBal
1111	6000
1113	4000
1114	1000

HAVING CLAUSE :
This is required as the WHERE clause does not support aggregate functions such as count, min, max, avg, sum, and so on.

SELECT column1, column2, ...
FROM table_name
HAVING condition;

Example –

SELECT  AccNo,CurBal FROM Account HAVING CurBal=MAX(CurBal);

Output :

AccNo	CurBal
1111	6000