SQL Programming Examples :
In my previous article i have given complex sql queries as well as PL SQL examples for reference. These example gives the user about the interview questions that may ask in interview. In this article i want to concentrate on SQL Programming Examples. I would like to give you some SQL Programming examples which are really used in day-to-day life in industry.These SQL Programming examples are very much used in industries on more frequent basis.Following are some important SQL Programming Examples which are real very useful in real life :
Example 1 : Selecting Distinct values from the table ?
This is most common query. I just would like to start with simple queries.The user always needs to check the distinct values from specific program.
Select Distinct Name From Employee;
The above query is most used and most useful query for SQL Programming Examples.Everytime user needs to check the distinct values. So use of distinct keyword in the SQL is very important.
Example 2 : Finding the table information from the database.
The second most important query used by programmers is finding the table information from specified database.There are so many situations where user will have to search specific table from the database.To check all the tables from the databases.The following query will check all the tables from the database.
Select table_name,Owner from All_tables order by table_name,owner;
Sometimes user will know about some table name part. In that case following query will be useful to user:
SELECT Table_Name FROM User_Tables WHERE Table_Name LIKE ‘EMP%’;
So this query is also very important SQL Programming examples which will used to find out the specific table information.
Example 3 : Queries for Pattern matching (Like Operator in SQL)
These are also most used queries which are used to find out the data with specific pattern. To find out the data with specific pattern user needs to use the
Select * from Employees where name like ‘%S%’ and surname like ‘%S%’;
The above query will check the Employees where name contains S and surname contains S.If you want to check SQL Like examples.
Example 4 : Queries using Greater than and less than operator
There are so many situations where user needs to use the greater than and less than operator.There are situations where user needs to use both operators together. These kind of queries are also most used and important SQL queries.Following are some real life examples of the same :
If user wants to see the employees who’s salary is greater than 100000.
Select * from Employees where salary > 100000;
If user wants to see Employees where salary is less than 10000.
Select * from Employees where salary < 10000;
If user wants to see Employees where salary is less than 100000 and greater than 1000;
Select * from Employees where salary > 1000 and salary < 100000;
If user wants to see Employees who’s salary is greater than and equal to 10000.
Select * from Employees where salary >= 10000;
These are some most important examples of using greater than and less than operator.
Example 5 : Examples using in-built functions in SQL
There are so many examples which are really very useful in day-to-day programmers life.There are so many examples where user needs to calculate the average of something or count of the records. In reporting we need distinct count of the column.I will give you simple examples of using in built function. I will not cover all examples but you can check the examples by clicking on this link.
If user wants to calculate the count of the Employees where department name is ‘Sales’.
Select count(*) from Employees where department=’Sales’;
If user wants to calculate distinct count of departments from department table.
Select count(distinct department) from Departments;
If user wants to calculate average of salary of Employees from Employee table.
Select avg(salary) from Employee;
Example 6 : Using Group by and order by clause
There are so many situations where user need to group with specific columns.I would like to give you some examples where user needs to work with group by and order by column.
If user wants to calculate the sum of Employees salary department-wise.
SELECT Department_No, Sum (Salary) FROM Employees
GROUP BY Department_No;
If user wants to calculate maximum salary departmentwise and its order is descending.
SELECT Department_No, Max (Salary) FROM Employees
GROUP BY Department_No
ORDER BY Salary;
Example 7: Using String Functions
There are situations where user need to handle the different strings using multiple string functions. There are so many situations where user needs to handle the validations of the string . In those cases user needs to use different string functions. Substr function is most used string function to handle multiple types of validations in SQL as well as PL SQL.I will share you some most important examples of String functions which are used in real life.
If user wants to make first letter of each column as capital then use Initcap function.
SELECT INITCAP (Ename) FROM Employees;
If user wants to check that employee name is Amit but he/she does not know about it is stored in capitals or small letters.
Select * from Employees where Upper(name)=Upper(‘Amit’);
The above statement will remove case insensitivity of column named ‘name’.
If user wants to fetch first 3 letters from Employee name from Employee table.
SELECT Substr (Ename, 1, 3) FROM Employees;
Example 8 : Use of joins
These queries are most used queries in adding any logic anywhere. You might know that there is always relationship between two or more tables. The join queries are most used queries to achieve some business functionality.I have given real life examples of joins in my articles.Here i will give you some examples of joins which are used in real life.
Fetch the employee name and department name from two different tables where joining condition is department_id:
Select a.Employee_name,b.Department_name
where a.Department_ID=b.Department_ID;
The second most important query asked in interview is how to find out the second highest salary of employee. In real world also some situations where user needs to find out the second highest content from the table.
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;
The third example of join is using self join.I will give the Employee and its manager name example for self join.
Select E.Employee_id,E.Name as ‘Employee Name’,F.Name as ‘Manager Name’ from Employee E,Employee F where E.Emp_jd=F.Mgr_id;
If you want to explore more joining examples kindly click here.
Example 9 : Using Set Operators
Now we are moving from simple SQL queries to complex sql queries. There are so many situations where we need to catch the data by using multiple sets. In that cases set operators in SQL are useful.
If user wants to fetch employee data from two different tables kindly use following query :
Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;
Union
Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;
Example 10 : Using Hierarchical Queries in SQL
There are situations where user needs to handle the parent child relationship in the table. These kind of situations are handled by Hierarchical queries.
If user wants to see bosses of the Employees in Hierarchical structure :
select lpad(‘ ‘,level*3,’ ‘)||ename name,
SYS_CONNECT_BY_PATH(ename,’/’) boss
from Employee_M
connect by prior employee_id = manager_id
start with manager_id is null;
Example 11 : Using Regular Expression
There are situations where user needs to use the Regular Expression to achieve some purpose.There are different functions like Regexp_like to achieve the business logic.
If user wants to validate the email address then Regexp_like function is used.Following query will help the user to validate mail id.
WITH T_validate AS
(SELECT ‘amiets@gmail.com’ email FROM dual)
SELECT * FROM T_validate WHERE REGEXP_LIKE (EMAIL, ‘^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$‘);
Example 12 : Using Row_id and Rownum
There are so many situations where user needs to use Rowid and Rownum in SQL.There are situations where user needs to use the rowid column as primary key.I would like to give you one example of rowid and rownum in following section.
If user wants to fetch last record from the table then following query is useful:
Select * from Employee where Rowid= select max(Rowid) from Employee;
If user wants to display last five records from Employee table:
Select * from Employee e where rownum <=5
union
select * from (Select * from Employee e order by rowid desc) where rownum <=5;
These are above some most important SQL Programming Examples used in real life. I have explained different real life examples of SQL in other article as well. This article gives you the basic idea about SQL Programming Examples with few examples.If you want to check out for other SQL Programming Examples kindly check for following links :
Complex SQL Programming Examples :
SQL Programming Examples for Interview :
SQL Programming Examples for Real life Scenarios :
Hope You like this article on SQL Programming Examples which will brief about different SQL Queries with concepts and examples.If you like this article or if you have any suggestions regarding the same kindly comment on comment section.