If you want to improve SQL skills, then install a SQL package like MySQL and start practicing with it. To get you started, we’ve outlined a few SQL query questions in this post.
The interview questions for sql queries we’ve filtered out of interviews held by top IT MNC like Flipkart and Amazon. So you’ll gain real-time experience by going through them.
Interview questions for sql queries is contain easy to hard variety of question and now a days windowing function is necessary in advance SQL queries
If you interested to grap a command on SQL ,we providing sql test questions and answers which is easy to help in crack interview exam and sql technical exam. Here we have example of emp and dept table in mysql . emp table in sql contain data of his employee . check out employee and department table queries in mysql
The MySQL schema for this database
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS salgrade;
DROP TABLE IF EXISTS emp;
CREATE TABLE salgrade(
grade int(4) not null primary key,
losal decimal(10,2),
hisal decimal(10,2));
CREATE TABLE dept(
deptno int(2) not null primary key,
dname varchar(50) not null,
location varchar(50) not null);
CREATE TABLE emp(
empno int(4) not null primary key,
ename varchar(50) not null,
job varchar(50) not null,
mgr int(4),
hiredate date,
sal decimal(10,2),
comm decimal(10,2),
deptno int(2) not null);
insert data in dept table
insert into dept values (20,'Research','Dallas');
insert into dept values (30,'Sales','Chicago');
insert into dept values (40,'Operations','Boston');
Insert data in emp table
insert into emp values (7369,'SMITH','CLERK',7902,'93/6/13',800,0.00,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,'98/8/15',1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,'96/3/26',1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,'95/10/31',2975,null,20);
insert into emp values (7698,'BLAKE','MANAGER',7839,'92/6/11',2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,'93/5/14',2450,null,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,'96/3/5',3000,null,20);
insert into emp values (7839,'KING','PRESIDENT',null,'90/6/9',5000,0,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,'95/6/4',1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,'99/6/4',1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,'00/6/23',950,null,30);
insert into emp values (7934,'MILLER','CLERK',7782,'00/1/21',1300,null,10);
insert into emp values (7902,'FORD','ANALYST',7566,'97/12/5',3000,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,'98/12/5',1250,1400,30);
Insert data into salgrade table
insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,99999);
Q.1 Write an SQL query to print the first three characters of ename from emp table
Select substring(ename ,1,3) from emp;
Q.2 Write an SQL query to print the ename from Worker table after removing white spaces from the right side.
Select LTRIM(ename) from emp;
Q.3 Write an SQL query to print the job from Worker table after removing white spaces from the left side
Select LTRIM(job ) from emp;
Q.4 Write an SQL query that fetches the unique values of job from emp table and prints its length.
Select distinct length(job ) from emp ;
Q.5 Write an SQL query to print all emp details from the emp table order by ename Ascending.
Select * from emp order by ename asc;
Q.6 Write an SQL query to print details for emp with the ename as “ADAMS” and “JAMES” from emp table.
Select * from emp where ename in ('Vipul','Satish');
Q.7 Write an Query print sum of salary based on department ?
selet sum(Sal) ,deptno from emp group by deptno having sum(sal) > 5000
Q.8 Write an Query print sum of Salary based on job and also print ename ,job
select ename ,job, salary from emp b
inner join
(select sum(Sal) as salary ,job from emp group by job ) a
on b.job = a.job
Q.9 Write an Query select depart name is not present in emp table
select b.dname , b.location form emp a
left outer join dept b
on b.deptno = a.deptno
where a.deptno is null
Q.10 Write an SQL query to print details of emp with Job name as "CLERK".
Select * from emp where job like 'CLERK%';
Q.11 Write an SQL query to print details of the emps whose ename contains ‘a’.
Select * from emp where ename like '%a%';
Q.12. Write an SQL query to print details of the empswhose ename ends with ‘a’.
Select * from emp where ename like '%a';
Q.13. Write an SQL query to print details of the emp whose ename ends with ‘h’ and contains six alphabets.
Select * from emp where ename like '_____h';
Q.14. Write an SQL query to print details of the emps whose SALARY lies between 100000 and 500000.
Select * from emp where sal between 100000 and 500000;
Q.15. Write an SQL query to print details of the emps who have joined in Feb’2014.
Select * from emp where year(hiredate) = 2014 and month(hiredate) = 2;
Q.16. Write an SQL query to fetch the count of employees working in the job ‘CLERK’.
SELECT COUNT(*) FROM emp WHERE DEPARTMENT = 'CLERK';
Q.17Write an SQL query to fetch the no. of emp for each Job in the descending order.
SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers
FROM worker
GROUP BY DEPARTMENT
ORDER BY No_Of_Workers DESC;
Q.18 Write an SQL query to fetch duplicate records having matching data in some fields of a table.
SELECT ename , COUNT()
FROM emp
GROUP BY ename
HAVING COUNT() > 1;
Q.19 Write an SQL query to clone a new table from another table.
SELECT * INTO empClone FROM Worker;
Q.20 The general way to clone a table without information is:
SELECT * INTO empClone FROM Worker WHERE 1 = 0;
Q.21 Write an SQL query to fetch intersecting records of two tables.
(SELECT * FROM emp)
INTERSECT
(SELECT * FROM EmpClone);
Q.22 Following MySQL query returns the current date:
SELECT CURDATE();
Q.21 Following MySQL query returns the current date and time:
SELECT NOW();
Q.23 Following SQL Server query returns the current date and time:
SELECT getdate();
Q.24 Following Oracle query returns the current date and time:
SELECT SYSDATE FROM DUAL;
Q.25. Write an SQL query to show the top n (say 10) records of a table.
SELECT * FROM Worker ORDER BY Salary DESC LIMIT 10;
Q.26 Following SQL Server query will return the top n records using the TOP command:
SELECT TOP 10 * FROM Worker ORDER BY Salary DESC;
Q.27 Following Oracle query will return the top n records with the help of ROWNUM:
SELECT * FROM (SELECT * FROM Worker ORDER BY Salary DESC)
WHERE ROWNUM <= 10;
Q.28 How can we avoid duplicating records in a query?
SELECT distinct * FROM Worker
OR
SELECT count(*),ename FROM Worker group by ename
The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values;
In Group by taking column name and provide us disctinct result based on Column value.
Q.29 Explain the difference between Rename and Alias.
RENAME TABLE emp TO emp_old, emp_new TO emp;
Or
An alias is created with the AS keyword.
SELECT ename AS EmployeeName FROM Emp
Q.30 What is schema?
In a SQL database, a schema is represent a list of logical structures of data. A database user owns the schema,It can be single or multiple . depend upon requirment . which has the same name as the database manager. As of SQL Server 2005, a schema is an individual entity (container of objects) distinct from the user who constructs the object.
Q.31 View contain Data?
No ,It is contain virtual represtation of data, with cutomize out put.
Answer: No, Views are virtual structures.
Q.32 Can a View be based on another View?
Answer: Yes, A View is based on another View.
Q.33 What is CTE?
Answer: A CTE or common table expression is an expression that contains a temporary result set which is defined in a SQL statement.
Q-34. Write an SQL query to print the name of employees having the highest salary in each department.
Ans.
The required query is:
SELECT t.dname ,
t.ename,
t.sal as salary,
from(SELECT max(sal) as
TotalSalary,dname from dept group by deptno ) as TempNew
Inner Join emp t on TempNew.deptno =t.deptno
and TempNew.TotalSalary=t.sal;
Q-35 Write an SQL query to fetch three max salaries from a table of employee.
Ans.
SELECT distinct Salary
from emp
a WHERE 3 >= (SELECT
count(distinct Salary) from emp b
WHERE a.Salary <= b.Salary)
order by a.Salary desc;
Q-36 Write an SQL query to fetch three min salaries from a table of employee.
Answer
SELECT distinct sal
from emp a
WHERE 3 >= (SELECT count(distinct Salary)
from worker b
WHERE a.Salary >= b.Salary)
order by a.Salary desc;
Q-37 Write an SQL query to fetch nth max salaries from a table of employee.
Answer
SELECT distinct Salary
from worker a
WHERE n >= (SELECT count(distinct Salary)
from worker b
WHERE a.Salary <= b.Salary)
order by a.Salary desc;
Q-38 Write an SQL query to fetch the names of employee who earn the highest salary in table :
Answer:
SELECT ename, sal
from emp
WHERE sal=(SELECT max(sal) from emp);
Q-39 Select the department name of the company which is assigned to the employee whose employee id is grater 103
Answer : select dname from empdept where deptno in (select dept from emp where empid>103)
Q-40 Select the name of the employee who is working under shubham.
Answer :select ename from emp where mgr =(select empid from employee where empname='shubham')
Q-41 Select the name of the employee who is department head of HR.
Answer : select ename from emp where empno =(select mgr from dept where dname='hr')
Q-42 select the details of all employee working in development department.
Answer : select * from emp where deptno in(select deptno from dept where dname='development')
Q-43 Write an SQL query to delete only emp table data.
Ans. We can use the TRUNCATE query to delete data from the SQL database table.
TRUNCATE TABLE emp;
Q-44 Write an SQL query for removing duplicates from a table without using a temporary table.
DELETE FROM StudentStipend
WHERE StudId IN (
SELECT StudId
FROM StudentStipend
GROUP BY Project, Stipend
HAVING COUNT(*) > 1));
Q-45 Write an SQL query for creating a new table with data and structure copied from another table.
Ans. We can perform the required operation using the SELECT INTO query.
SELECT * INTO newTable FROM StudentDetails;
Q-46 Write an SQL query for finding current date-time.
Ans. SQL queries for various Databases are as described below.
SQL Query In Oracle
SELECT SYSDATE FROM DUAL;
Q-47 SQL Query In SQL Server
SELECT getdate();
Q-48 SQL Query In MySQL
SELECT NOW();
Q-49 Using limit clause(MySQL)
SELECT ename FROM emp ORDER BY ename DESC LIMIT N-1,1;
Q-50 How to display Date in DD-MON-YYYY Employee table?
Answer: Select to_date (Hire_date,’DD-MON-YYYY’) Date_Format from Employee;
Q-51 Write SQL query for fetching top n records using LIMIT in emp table ?
Ans. SQL queries for fetching top n records using LIMIT for various Databases are as described below.
In MySQL in emp table
SELECT * FROM emp ORDER BY ename DESC LIMIT N;
In SQL server using the TOP command
SELECT TOP N * FROM emp ORDER BY ename DESC
Conclusion
Here in this Article We can see sql questions practice Details of list of basic sql practical questions . sql query questions is help you to start your learning in sql . We also provide you question of emp and dept table in mysql. sql queries interview questions
Top comments (0)