In my previous article i have given the basic idea about the SQL Statements.
In this article i would like to give you examples of types of queries in SQL.There are multiple types of queries in SQL like simple queries,complex queries,co-related queries,Subqueries e.t.c.In next section i would like to explain these different types of queries in SQL with its usages and examples.
There are following types of Queries :
1.Basic SQL Queries
2.Complex SQL Queries
3.Sub-queries
So this article gives you different types of queries with real life industry examples.
Basic SQL Types of Queries :
In this article i would like to give you examples of basic SQL Queries. My format is to give one type of Basic SQL Query with example.There are following types of Basic SQL Queries :
Type 1 :
The first type of basic sql queries is create a table in SQL. I have already explained different examples and ways to create a table in SQL.
Example :
Create a table named Employee_master with Emp_No and Employee_name column.
Create table Employee_Master
(Emp_No Number(10),
Employee_Name Varchar2(30));
Type 2 :
The second type is to insert the data in SQL statement. I would like to give you simple example of Inserting data in table.
Example :
I would like to insert the data in Employee_Master table which is already created.
Insert into Employee_Master
Values(101,’Ashish’);
The above statement will insert the data in table named Employee_Master table.
Type 3 :
The third type is how to update the data in to table. These are most basic types of queries in SQL.
Example :
Kindly update Employee_Name to Amit where Employee_Number is 101.
Update Employee_Master set Employee_Name=’Amit’
where Employee_No=101;
Type 4 :
Delete table in SQL
The another types of queries in SQL is to delete table in SQL. User can delete the table data according to the requirements. User can use the where filter to add the condition to delete data in table.
Example:
I would like to delete data of Employee no 101 from Employee_Master table.
Delete from Employee_Master where Employee_No=101;
Type 5 :
The most important type of Basic sql queries is to alter the table. There are multiple types of altering the table. User can add the column,remove the column,add different constraints ,remove constraints with using alter table in sql.
Example :
I would like to add new column named salary in Employee_Master table.
Alter table Employee_Master
Add column Salary number(10);
Type 6:
Drop table in SQL
When user wants to permanently delete the table user needs to use drop query in SQL.
Example :
I would like to drop table named Employee_Master
Drop table Employee_Master;
These are above some most important types of queries for Basic SQL.
Complex Sql Queries :
When user needs to use different complex syntaxes to fulfill the business purpose;we need to use the complex sql queries in that case.I have already given different examples of complex sql queries. I would like to give you one example of complex sql queries.
Example :
Write a query to find second highest salary of Employee.
SELECT max(e1.sal), e1.deptno FROM s_emp e1 WHERE sal < (SELECT max(sal) FROM s_emp e2 WHERE e2.deptno = e1.deptno) GROUP BY e1.deptno;
Subqueries in SQL :
The third most important types of queries is subqueries in SQL. I have already explained about different subqueries in SQL. In this section i would like to give you example of Subqueries in SQL,
Subqueries are nothing but queries within query.
Simple Sub-query :
This is also called as single row subquery in which we need to use the aggregate function.
Select Employee_No,Employee_Name from Employee_Master
where Salary=(Select max(Salary) from Employee_Master);
Multi-row Sub-query :
In this type of sub-query user needs to use multi-row operators like IN,Any.
Select Employee_No,Employee_Name from Employee
where Department_Name in
(Select Department_Name from Employee where Department_name in (‘OBIEE’,Oracle’));
Co-related Sub-query :
Co-related queries also called as synchronized queries in which the query from outer side will execute first.
Select * from Employee_Master E where Not exist
(Select Department_no From Department D where E.Employee_id=D.Employee_ID);
These are some most important types of queries. You need to check each and every type of query by clicking the link given in article. The article gives you detailed information about types of queries in SQL.I hope you like this article. If you like this article or if you have any concerns with the same kindly comment in comments section.