Skip to content
Related Articles

Related Articles

Save Article
Improve Article
Save Article
Like Article

SQL Concepts and Queries

  • Difficulty Level : Basic
  • Last Updated : 19 May, 2021

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.

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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 nformation_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 ATypeOBal      ODCurBal
1111savings10001990-11-096000
1114current20001992-10-071000
1113current70001992-11-034000
1112savings10002003-12-123000
  • 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)

AccNoATypeOBal      ODCurBal
1114current20001992-10-071000
1112savings10002003-12-123000
1113current70001992-11-034000
1111savings10001990-11-096000
  • Scenario-2 : 
    For descending order :
SELECT * FROM Account ORDER BY CurBal DESC;

Output :   



AccNoATypeOBal  ODCurBal
1111savings10001990-11-096000
1113current70001992-11-034000
1112savings10002003-12-123000
1114current20001992-10-071000

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

Example –

SELECT COUNT(AType) FROM Account GROUP BY AType;

Output :  

ATypecount(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;

 LEFT JOIN

  • 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;

RIGHT JOIN

  • 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;

INNER JOIN

  • 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;

FULL JOIN

  •  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 :

AccNoCurBal
11116000
11134000
11141000

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 :

AccNoCurBal
11116000



My Personal Notes arrow_drop_up
Recommended Articles
Page :