SQL QUERIES FOR FRESHERS AND EXPERIENCED PROGRAMMERS PART-2

When we are attending any Technical Interview for the position as Software developer most of the questions asked about SQL.If you really have Experience on Web Projects then you should have good knowledge on database, how to handle data base,writing queries. In this article I am going to expose some basic SQL Interview Questions.

1) How to write SQL Query to find Year from Date?

Ans: To find year from Date we can write simple query like as follows

Select YEAR(GETDATE()) as "year";

2) How to find the Maximum Salary from each Department?

Ans:  This is very simple to find maximum salary from each department,firstly  we have to group all records using DeptId and then we have to use MAX function to calculate maximum salary in each group or Department.

Select DeptId,Max(salary)from Employee group by DeptID;

3) Can you Write an Sql query to find an employee salary is equl or greater than 30000?

Ans: This is frequently asked interview question as part of the Sql quires to check your basic query knowledge .

select Emp_name from Emp where sal>=30000;

4) How to find duplicate rows in your table or database?write a query to it?and then write  a query to delete those duplicate rows?

Ans: select * from emp e WHERE  rowid=(select(MAX(rowid))from emp b WHERE a.empno=b.empno);

To Delete those Rows:

DELETE from emp a WHERE rowid !=(select (MAX(rowid))from emp b WHERE a.empno=b.empno);

5) Get department wise average salary from employee table order by salary ascending


Ans:select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc;

6) Get department wise maximum salary from employee table order by salary ascending

Ans:select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by MaxSalary asc;

7) Select TOP 2 salary from employee table

SQL Queries in Oracle:


select * from (select * from employee order by SALARY desc) where rownum <3;

SQL Queries in SQL Server:

select top 2 * from employee order by salary desc;

SQL Queries in MySQL:


select * from employee order by salary desc limit 2;

8) Select Nth Highest salary from employee table

SQL Queries in Oracle:

select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum <N + 1);

SQL Queries in SQL Server:

select min(SALARY) from (select top N * from employee) a;

SQL Queries in MySQL:


select min(SALARY) from (select * from employee order by salary desc limit N) a;

9) What is an ALIAS COMMAND IN SQL?

ANS: ALIAS Command is used when we ALIAS name can be given to table or Column. Mostly we used ALIAS  in WHERE Clause to identify a table or column.

EXAMPLE: 

Select  emp.Employee,dept.Department  from Employee emp,Department as dept WHERE emp.Employee=dept.Department

Note:  Here emp refers to alias name for Employee and dept refers to alias as Department table.

10)Which Operator is used for Pattern Matching in SQL?

Ans:  For Pattern Matching we used as LIKE Operator and it can be used as

select * from Employee where emp_name LIKE 's%';

11) What is GROUP BY IN SQL? How to use GROUP BY IN SQL?

ANS: GROUP BY is a Keyword,it has been added to SQL because aggregate functions like SUM,AVG ect..returns the aggregate of all column values every time they are called.
Why we use GROUP BY IN SQL means without GROUP BY  functionality finding sum of each individual group of column values was not possible.

Also check the post : HR Interview Questions for Experienced Candidate

12) What is stored procedure?

Ans: Stored Procedure is a functions which contains a collection of SQL Queries. The procedure can take inputs,process them and send back output.

13) What are the advantages of Stored Procedure?

Ans: Stored procedures are precompiled and stored in the database. This enables the database to execute the queries much faster. Since many queries can be included in a stored procedure,round trip time to execute multiple queries from source code to database and back is avoided.

14) What is Trigger?   
Ans: Database Triggers are set of commands and get executed when an event occurs on a table,views. Here events means before insert,after insert,on update,on delete of a row.

15)What is Difference Between Stored Procedure and Trigger?

Ans:  
When action is defined automatically  Trigger is invoked. whereas Stored Procedure calls Explicitly.

Note: Trigger can be invoked after we insert something on the table only
Stored Procedure can not be Inactive but Trigger can be inactive.

16) How to write a Query Get Department wise average salary from employee table order by salary ascending ?

ANS:
select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by avgSalary asc;

17) What is difference between Cluster and Non cluster index?

Ans: A cluster index reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster.

A non clustered index does not alter the way it was stored but creates a completely separate object within the table. As a result insert and update command will be faster.

18) What is database lock?

Ans: Database lock tells a transaction,if the data item in questions is currently being used by other transactions.

19)  What is database transaction?

Ans: Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.

20) What are the Wild cards used in database for Pattern Matching?

Ans: SQL Like operator is used for pattern matching. SQL 'LIKE' command takes more time to process. 

See also:

TOP 20 SQL QUERIES INTERVIEW QUESTIONS
Interview procedure in MNC companies for freshers
SQL Query Interview Questions for freshers
Joins in SQL with Examples