SQL Practice Exercises with Solutions :

In my previous article i have given the different examples of SQL as well as most important complex sql queries for interview purpose.I would like to combine all those examples and want to make one best article on SQL Practice Exercises with solutions.My main purpose writing this article on SQL Practice Exercises with solution is to get idea about different SQL real world examples as well as user can easily implement it in day to day life.These are the scenarios which are useful for real world industry. I have already written article on real world industry examples of SQL.I want to add some queries from that article also.There are following SQL Practice Exercises with Solutions which are mostly used in day to day life in world of programming.

Important Queries for SQL Practice Exercises with Solutions :

Example 1 : How to create table with same structure with data?

Query :

Let us consider that user wants to create a replica of table named ‘Student’.

Create table Student_Replica as Select * from Student;

Explanation :

The above query will create the same table as student named ‘Student_Replica’ with its data.

Example 2 : How to create table with same structure without data?

Query :

Let us consider that user wants to create a replica of table named ‘Student’ without having data.

Create table Student_Replica as Select * from Student where 1=2;

Explanation :

The above query will create the same table as student named ‘Student_Replica’ without data.It is possible due to the condition 1=2. The condition 1=2 means True=False which is always False condition.So with using the above query user can create duplicate table structure without data.

Example 3 : How to display Last 10 records from Student table.

Query :

There are some situations where user needs to fetch some last records from the table.The following query will fetch the last records from the table.

Select * from Student S where rownum <=10

union

select * from (Select * from Student S order by rowid desc) where rownum <=10;

Explanation :

Here we are using simple logic of union and order by the 10 records from student table.

Example 4 : How to fetch first 5 highest marks with Student table.

Query :

There are so many examples where user needs to fetch the highest values from the specified table.Following query will fetch the first 5 highest marks from student table.

select min(marks)from(select distinct marks from Student order by marks desc)where rownum<=5;

Explanation:

In above example we are using the rownum concept as well as we are using inner view of descending marks from student table.

Example 5: How to display 1 to 100 numbers with using query.

Query :

There are scenarios where user wants to display 1 to 100 numbers with using the query.

Select level from dual connect by level <=100;

Explanation:

In this example user needs to use the concept of hierarchical queries.With using the level attribute of hierarchical query user can display first 100 numbers.

Example 6 : How to find the duplicate row count from specific table.

Query :

This example is most important example for real world scenarios.There are so many times where user needs to find out the duplicate row count from the table.

 

Select Employee_no, count (Employee_no) from Employee

Group by Employee_no

Having count (Employee_no)>1

Order by count (Employee_no) desc;

Explanation :

In this example we need to use the Count function as well as group by and having. You need to use order by clause as well.

Example 7 : How to delete duplicate rows from the table.

Query :

Using above query we find out the duplicate record count from the table. There are situations where user needs to find out the duplicate rows as well as delete those rows. Following query is useful in that case.

Delete FROM Employee WHERE ROWID <>

(Select max (rowid) from Employee b where Employee_num=b.Employee_num);

Explanation :

Here we are using the <> operator from SQL to delete duplicate rows from the table.

Example 8 : How user can display following structure with using single SQL Query.

$

$$

$$$

Query :

We can not use dual table to perform this operation. We need to use Employee table with data more than 4 to perform this.

SELECT lpad (‘$’, ROWNUM,’$’) FROM Employee WHERE ROWNUM <4;

Explanation :

Here we are using lpad() function to fetch dollar symbol.

Example 9 :How to check for Email is correct or wrong with using single query.

Query :

User needs to use regular expression function to check the Email validation with single query.

SELECT
Email
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

Explanation :

These are above some SQL Practice Exercises with Solutions which are highly used in real life scenarios.In out SQL Practice Exercises with Solutions we are using the Regular expression function named REGEXP_LIKE to check for Email validation.

FOR ANY SQL SUPPORT CONTACT : COMPLEXSQL@GMAIL.COM

Example 10 : How to fetch maximum salary of Employee and minimum salary of Employee together from Employee table.

Query :

This is bit tricky question.We can not use the 2 aggregate functions together.

Select max (salary) from Employee

Union

Select min (salary) from Employee;

Explanation :

The example is pretty simple where user needs to use the Set operators from SQL.Here user needs to use union set operator.

Example 11 :  List the Students whose name starts with P and surname starts with S.

Query :

The Like operator will help to achieve this,

Select * from Students where name like ‘P%’ and surname like ‘S%’;

Explanation :

The SQL Like special character is most used wildcard to fetch data with specific condition in the table.

Example 12 :How to fetch last record from Student table.

Query :

This is also most common SQL Practice Exercises with Solutions where user needs to fetch the last record from the table,

Select * from Student where rowid = select max(rowid) from Student;

Explanation :

The records are stored in to table according to the rowid of the table. So User needs to fetch the maximum of row id record from Student table.

FOR ANY SQL SUPPORT CONTACT : COMPLEXSQL@GMAIL.COM

Example 13 : How to fetch all the Student who took admission at 2016.

Query :

There are so many scenarios where user needs to fetch the record according to admission year or joining date.

select * from Student where To_char(Joining_date,’YYYY’)=’2016′;

Explanation :

The above example uses the To_Char function to fetch the specific year from the date.

Example 14 : What is query to display odd records from Student table.

Query :

This query is also most important and most used SQL Practice Exercises with Solutions to display odd as well as display Even records from the specific table,

Select * from(Select rownum as rno,S.* from Student S) where Mod(rno,2)=1;

Explanation :

In this example user needs to use the Rownum as well as Mod functions to check out the odd records from the table.

Example 15 : What is query to display even records from Student table.

Query :

This query is also most important and most used SQL Practice Exercises with Solutions to display even as well as display odd records from the specific table,

Select * from(Select rownum as rno,S.* from Student S) where Mod(rno,2)=0;

Explanation :

In this example user needs to use the Rownum as well as Mod functions to check out the even records from the table.

Example 16 : How to find out manager name and employee name from same table.

Query :

This is scenario with self join in SQL.Following query will find out the Manager name with Employee name from Employee table,

Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;

Explanation :

In this query user is using the self join to fetch the records from the table.

These are some most important SQL Practice Exercises with Solution.I will update the examples of SQL Practice Exercises with Solutions on weekly basis so that user will get the best SQL queries for practice.Hope you like this article on SQL Practice Exercises with Solutions.If you like this article or if you have any questions or queries regarding the same kindly comment in to comment section.