In this article, we will learn some frequently asked SQL Query interview Questions for DBA,Business Analyst and for all Programming language Developers(JAVA,.NET,PHP etc..).
In this post i am taking two tables such as Employee table and Incentive table. Based on these two tables i am writing SQL Queries and generate result also. Do best practice on these tables you will get more confident on SQL subject. Earlier,I have already shared Joins in SQL and go through this link to know more awareness on it.
Table 1: Employee Table
Table 2: Incentive Table
Now Let us start to write Basic SQL Queries Based on the above tables.
1) How to get all Employee details from the employee table ?
Ans: This is the very very Basic Query every one should know how to retrieve all employee details from the table or database
select * from Employee;
Result:
2) How to get First_Name from employee table in upper case?
Ans: select upper(First_Name)from Employee;
Result:
3) How to get Unique Department from employee table?
Ans:
select distinct Department from Employee;
Result:
4) How to get get length of First_Name from Employee Table?
Ans:
select length(First_Name)from Employee;
Result:
5) How to get all employee details from the employee table order by First_Name Ascending
Ans:
select * from employee order by First_Name asc;
Result:
select * from employee ordery by First_Name desc;
6) How to retrieve all employee details from the Employee table order by First_Name Ascending and Salary descending?
Ans:
select * from Employee order by First_Name asc,Salary desc;
Result:
7) How to get Employee details from employee table whose salary greater than 15000?
Ans:
select * from Employee where salary>15000;
Result:
select * from Employee where salary
ALSO READ : SQL GROUP BY AND HAVING clause
8) How to get employee details from employee table whose salary between 20000 to 30000?
Ans:
select * from Employee where salary between 20000 and 30000;
Result:
Ans:
select sysdate from dual;
Result:
10) How to get Department, no of employees in a department,total salary with respect to a department from employee table order by total salary descending?
Ans:
select Department,count(First_Name),sum(salary)total_salary from Employee group by Department order by total_salary desc;
Result:
Ans:
select Department,max(salary)MaxSalary from Employee group by Department order by MaxSalary asc;
Result:
12) How to 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;
Result:
13) How to select employee details from employee table if data exists in Incentive table?
Ans:
select * from Employee where
exists(select * from Incentives);
Result:
Ans:
select First_Name,Incentive_Amount from Employee a inner join
Incentives B on A.Employee_ID=B.Employee-ref-id and Incentive_Amount>3500;
15) How to Select Top two salary from employee table?
Ans:
select * from (select * from Employee order by Salary desc)where rownum
Result:
16) How to select Nth Highest Salary form Employee table?
Ans:
select * from (select * from Employee order by Salary desc);
17) Write Syntax to delete table employee?
Ans:
Drop table tablename;
18) How can you write syntax to set Employee_id as PrimaryKey in Employee table?
Ans:
ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK
PRIMARY KEY(EMPLOYEE_ID);
if you want set two fields (Employee_id,First_Name) as primary key in employee table then write a query like below:
ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK
PRIMARY KEY(EMPLOYEE_ID,FIRST_NAME);
19) How to write a query to drop foreign key on employee table?
Ans:
ALTER TABLE INCENTIVES drop
CONSTRAINT INCENTIVES_FK;
20) What is SQL Injection?
Ans: SQL Injection is one of the technique used by hackers to hack a website by injecting SQL commands in data fields.
In this post i am taking two tables such as Employee table and Incentive table. Based on these two tables i am writing SQL Queries and generate result also. Do best practice on these tables you will get more confident on SQL subject. Earlier,I have already shared Joins in SQL and go through this link to know more awareness on it.
Table 1: Employee Table
Table 2: Incentive Table
Now Let us start to write Basic SQL Queries Based on the above tables.
1) How to get all Employee details from the employee table ?
Ans: This is the very very Basic Query every one should know how to retrieve all employee details from the table or database
select * from Employee;
Result:
2) How to get First_Name from employee table in upper case?
Ans: select upper(First_Name)from Employee;
Result:
3) How to get Unique Department from employee table?
Ans:
select distinct Department from Employee;
Result:
4) How to get get length of First_Name from Employee Table?
Ans:
select length(First_Name)from Employee;
Result:
5) How to get all employee details from the employee table order by First_Name Ascending
Ans:
select * from employee order by First_Name asc;
Result:
If you want descending Order then write Query Like Below:
select * from employee ordery by First_Name desc;
6) How to retrieve all employee details from the Employee table order by First_Name Ascending and Salary descending?
Ans:
select * from Employee order by First_Name asc,Salary desc;
Result:
7) How to get Employee details from employee table whose salary greater than 15000?
Ans:
select * from Employee where salary>15000;
Result:
I hope you must write how to get employee details from employee table whose salary less than 20000
select * from Employee where salary
ALSO READ : SQL GROUP BY AND HAVING clause
8) How to get employee details from employee table whose salary between 20000 to 30000?
Ans:
select * from Employee where salary between 20000 and 30000;
Result:
9) How to get Database date?
Ans:
select sysdate from dual;
Result:
10) How to get Department, no of employees in a department,total salary with respect to a department from employee table order by total salary descending?
Ans:
select Department,count(First_Name),sum(salary)total_salary from Employee group by Department order by total_salary desc;
Result:
11) How to 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;
Result:
12) How to 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;
Result:
13) How to select employee details from employee table if data exists in Incentive table?
Ans:
select * from Employee where
exists(select * from Incentives);
Result:
14) How to select First_Name,Incentive amount from Employee and incentives table for those employees who have incentives and incentive amount greater than 3500?
Ans:
select First_Name,Incentive_Amount from Employee a inner join
Incentives B on A.Employee_ID=B.Employee-ref-id and Incentive_Amount>3500;
15) How to Select Top two salary from employee table?
Ans:
select * from (select * from Employee order by Salary desc)where rownum
Result:
16) How to select Nth Highest Salary form Employee table?
Ans:
select * from (select * from Employee order by Salary desc);
17) Write Syntax to delete table employee?
Ans:
Drop table tablename;
18) How can you write syntax to set Employee_id as PrimaryKey in Employee table?
Ans:
ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK
PRIMARY KEY(EMPLOYEE_ID);
if you want set two fields (Employee_id,First_Name) as primary key in employee table then write a query like below:
ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK
PRIMARY KEY(EMPLOYEE_ID,FIRST_NAME);
19) How to write a query to drop foreign key on employee table?
Ans:
ALTER TABLE INCENTIVES drop
CONSTRAINT INCENTIVES_FK;
20) What is SQL Injection?
Ans: SQL Injection is one of the technique used by hackers to hack a website by injecting SQL commands in data fields.
