SQL
SQL stands for Structured Query Language. It is used to manipulate and store data in a relational database. Initially, SQL was SEQUEL ( Structured English Query Language ). This was designed by Donald D. Chamberlin
Raymond F. Boyce.
SQL Consists of many sublanguages which are categorized as:
- DQL – Data Query Language
- DDL – Data Definition Language
- DCL – Data Control Language
- DML -Data Manipulation Language
Types of Databases
- Distributed Database
- Object Oriented Database
- Centralized Database
- Operational Database
- Graph Database
- NO SQL Database
- Relational Database
Basic SQL Queries
How to create a Database
Create database Emp;
How to Delete a Database
Drop database Emp;
How to create a Table
Create table emp (Id int auto_increment, name varchar(20), address varchar(20));
How to delete a Table
Drop table emp;
How to View data in the Table
If you want to see full data then:
Select * from emp;
If you want to see selected data then:
Select name from emp where city= ‘goa’;
How to use AND operator
Select * from student where fname= ‘pankaj’ AND lname= ‘kumar’;
How to use OR operator
Select * from student where fname= ‘pankaj’ OR lname= ‘kumar’;
How to use NOT operator
Select * from student where NOT fname= ‘pankaj’;
How to insert data in Table
Insert into student (fname, lname, address, marks) values (‘pankaj’, ‘sharma’ , ‘House no 654’, 456);
How to use Count
This query returns the number of rows that matches specified criteria.
Select Count (ID) from student;
How to Find Average
Select AVG(Marks) from student;
How to Find Sum
Select SUM(Marks) from student;
How to Find Minimum value
Select fname, MIN(Marks) from student;
How to Find the Maximum value
Select fname, lname, MAX(Marks) from student;
How to Use Group By
This query is used to arrange the identical data into groups.
Select Count(ID), city from student group by city;
How to Use Having Query
Select fname, SUM(Marks) from student group by fname having SUM(Marks)>600;
How to Update data in Table
Set fname= ‘pankaj’, lname= ‘sharma’ where id= 2;
How to delete data from Table
Delete from student where city= ‘Delhi’;
Some Other Important Queries
How to Use Order by Query
Order by is used to arrange the data into ascending and descending order.
Arrange data into ascending order
Select Count(ID), city
from student
group by city
order by city asec;
Arrange data into descending order
Select Count(ID), city
from student
group by city
order by city desc;
How to check empty values in Table
Select fname, lname from student where Marks IS NULL;
How to check non empty values in Table
Select fname, lname from student where Marks IS NOT NULL;
Hoe to Use IN operator
It is used to specify multiple values inside where Clause.
Select ID, name from student where city IN(‘Delhi’, ‘Himachal’, ‘Punjab’);
How to use Between operator
It is used to select a particular value within the specified range.
Select ID, name from student where Marks BETWEEN 400 and 450;
These are some of the queries that are used in the SQL. For your ease, we have used the Student and Employee table. If you want to run these queries then you have to replace our table name with your table. If still, you face some issues then do let us know in comments.
SQL Joins
Joins in SQL are commands which are used to combine rows from two or more tables. Different joins are as follows:
Inner Join
This type of join returns those records which have matching values in both the tables. So, if you perform an inner join operation between the emp table and project table, all the tuples which have matching values in both the tables will be returned as output. The syntax for performing this join is:
Select emp.empid, emp.empname, project.projectid, project.projectname
from emp
INNER JOIN project ON emp.empid=project.projectid;
Left Join
This type of join returns all the records from the left table and also those records which satisfy a condition from the right table. Also for the records having no matching values in the right table, the result will contain null values.
Select emp.empid, emp.empname, project.projectid, project.projectname
from emp
LEFT JOIN project ON emp.empid=project.projectid;
Right Join
This type of join returns all the records from the Right table and also those records which satisfy a condition from the Left table. Also for the records having no matching values in the Left table, the result will contain null values.
Select emp.empid, emp.empname, project.projectid, project.projectname
from emp
RIGHT JOIN project ON emp.empid=project.projectid;
Full Join
This type of join returns all those records which either have a match on the left table or have a match on the right table. This join will also include NULL values.
Select emp.empid, emp.empname, project.projectid, project.projectname
from emp
FULL JOIN project ON emp.empid=project.projectid;
Other SQL Joins
Natural Join:
This type of join is same as Inner Join. You can apply Natural join when you want that the number of columns should be less. No columns will be repeated in this type of join.
Self Join:
In this type of join, Each row in a table is joined with itself.
Cross Join:
A join Clause is applied to each row of a table to every row of the other table.
If you have some doubts or wants to add something in this topic then please do let us know in comments.
Read Also
What is test process.