Interview questions for sql queries 2023

Select substring(ename ,1,3) from emp;

Select LTRIM(ename) from emp;

Select LTRIM(job ) from emp;

Select distinct length(job ) from emp ;

Select * from emp order by ename asc;

Select * from emp where ename in ('Vipul','Satish');

selet sum(Sal) ,deptno from emp group by deptno having sum(sal) > 5000

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

select b.dname , b.location form emp a
left outer join dept b
on b.deptno = a.deptno
where a.deptno is null

Select * from emp where job like 'CLERK%';

Select * from emp where ename like '%a%';

Select * from emp where ename like '%a';

Select * from emp where ename like '_____h';

Select * from emp where sal between 100000 and 500000;

Select * from emp where year(hiredate) = 2014 and month(hiredate) = 2;

SELECT COUNT(*) FROM emp WHERE DEPARTMENT = 'CLERK';

SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers
FROM worker
GROUP BY DEPARTMENT
ORDER BY No_Of_Workers DESC;

SELECT ename , COUNT(*)
FROM emp
GROUP BY ename
HAVING COUNT(*) > 1;

SELECT * INTO empClone FROM Worker;

SELECT * INTO empClone FROM Worker WHERE 1 = 0;

(SELECT * FROM emp)
INTERSECT
(SELECT * FROM EmpClone);

SELECT CURDATE();

SELECT NOW();

SELECT getdate();

SELECT SYSDATE FROM DUAL;

SELECT * FROM Worker ORDER BY Salary DESC LIMIT 10;

SELECT TOP 10 * FROM Worker ORDER BY Salary DESC;

SELECT * FROM (SELECT * FROM Worker ORDER BY Salary DESC)
WHERE ROWNUM <= 10;

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.

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

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.

No ,It is contain virtual represtation of data, with cutomize out put.

Answer: Yes, A View is based on another View.

Answer: A CTE or common table expression is an expression that contains a temporary result set which is defined in a SQL statement.

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;

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;

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;

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;

Answer:
SELECT ename, sal
from emp
WHERE sal=(SELECT max(sal) from emp);

Answer : select dname from empdept where deptno in (select dept from emp where empid>103)

Answer :select ename from emp where mgr =(select empid from employee where empname='shubham')

Answer : select ename from emp where empno =(select mgr from dept where dname='hr')

Answer : select * from emp where deptno in(select deptno from dept where dname='development')

Ans. We can use the TRUNCATE query to delete data from the SQL database table.
TRUNCATE TABLE emp;

DELETE FROM StudentStipend
WHERE StudId IN (
SELECT StudId
FROM StudentStipend
GROUP BY Project, Stipend
HAVING COUNT(*) > 1));

Ans. We can perform the required operation using the SELECT INTO query.
SELECT * INTO newTable FROM StudentDetails;

Ans. SQL queries for various Databases are as described below.
SQL Query In Oracle
SELECT SYSDATE FROM DUAL;

SELECT getdate();

SELECT NOW();

SELECT ename FROM emp ORDER BY ename DESC LIMIT N-1,1;

Answer: Select to_date (Hire_date,’DD-MON-YYYY’) Date_Format from Employee;

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