What is SQL and explain different joins?

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
What is SQL

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.