Thursday, May 9, 2024

Top 10 Frequently asked SQL Query Interview Questions Answers

In this article, I am giving some examples of SQL queries which is frequently asked when you go for a programming interview, having one or two year experience in this field. Whether you go for a Java developer position, QA, BA, support professional, project manager, or any other technical position, may interviewer expects you to answer basic questions from Database and SQL. It's also obvious that if you are working for one or two years on any project there is a good chance that you come across to handle databases, writing SQL queries to insert, update, delete and select records. 

One simple but effective way to check the candidate's SQL skill is by asking these types of simple queries. They are neither very complex nor very big, but yet they cover all key concepts a programmer should know about SQL. 

These queries test your SQL skill on Joins, both INNER and OUTER join, filtering records by using WHERE and HAVING clause, grouping records using GROUP BY clause, calculating the sum, average, and counting records using an aggregate function like AVG(), SUM(), and COUNT(), searching records using wildcards in LIKE operator, searching records in a bound using BETWEEN and IN clause, DATE and TIME queries, etc. 

If you have faced any interesting SQL query or you have any problem and searching for a solution, you can post it here for everyone's benefit.




20 Most Popular SQL Query Interview Questions and Answers

Without any further ado, here is a list of the most common SQL query Interview questions and answers from Programming Job interviews:


Question 1: SQL Query to find the second highest salary of Employee
Answer: There are many ways to find the second highest salary of an Employee in SQL, you can either use SQL Join or Subquery to solve this problem. Here is an SQL query using Subquery:

SELECT MAX(Salary) 
FROM Employee 
WHERE Salary NOT IN (select MAX(Salary) from Employee ); 

See How to find the second highest salary in SQL for more ways to solve this problem.


Question 2: SQL Query to find Max Salary from each department.
Answer: You can find the maximum salary for each department by grouping all records by DeptId and then using MAX() function to calculate the maximum salary in each group or each department.

SELECT DeptID, MAX(Salary) 
FROM Employee 
GROUP BY DeptID. 

These questions become more interesting if the Interviewer will ask you to print the department name instead of the department id, in that case, you need to join the Employee table with Department using the foreign key DeptID, make sure you do LEFT or RIGHT OUTER JOIN to include departments without any employee as well.  

Here is the query
SELECT DeptName, MAX(Salary) 
FROM Employee e RIGHT JOIN Department d 
ON e.DeptId = d.DeptID 
GROUP BY DeptName;

In this query, we have used RIGHT OUTER JOIN because we need the name of the department from the Department table which is on the right side of the JOIN clause, even if there is no reference of dept_id on the Employee table. 


10 Frequently asked SQL Query Interview Questions




Question 3: Write SQL Query to display the current date?
Answer: SQL has built-in function called GetDate() which returns the current timestamp. This will work in Microsoft SQL Server, other vendors like Oracle and MySQL also have equivalent functions.
SELECT GetDate(); 


Question 4: Write an SQL Query to check whether the date passed to Query is the date of the given format or not?
Answer: SQL has IsDate() function which is used to check passed value is a date or not of specified format, it returns 1(true) or 0(false) accordingly. Remember the ISDATE() is an MSSQL function and it may not work on Oracle, MySQL, or any other database but there would be something similar.

SELECT  ISDATE('1/08/13') AS "MM/DD/YY"; 

It will return 0 because the passed date is not in the correct format.


Question 5: Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1960 to 31/12/1975.
Answer: This SQL query is tricky, but you can use BETWEEN clause to get all records whose dates fall between two dates.
SELECT DISTINCT EmpName 
FROM Employees 
WHERE DOB BETWEEN 01/01/1960’ AND31/12/1975’;




Question 6: Write an SQL Query to find the number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.
Answer : Here is teh sql query to find the number of employees according to gender and whose date of birth is between two given dates
SELECT COUNT(*), sex 
FROM Employees  
WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' 
GROUP BY sex;

Question 7: Write an SQL Query to find an employee whose salary is equal to or greater than 10000.
Answer : You can use WHERE clause with less than and equal to operator to solve this problem. Here is the sql query to find employees whose salary is equal to or greater than a given number
SELECT EmpName FROM  Employees WHERE  Salary>=10000;


Question 8: Write an SQL Query to find the name of an employee whose name Start with ‘M’
Answer :  You can use the Llike operator to find the name of all employees whose name start with letter "M", here is an exmaple:
SELECT * FROM Employees WHERE EmpName like 'M%';


Question 9: find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.

Answer :You can use SQL function like UPPER()  and like operator to find all employees whose name contains a given word like "Joe" as shown in following example:
SELECT * from Employees  WHERE  UPPER(EmpName) like '%JOE%';


Question 10: Write an SQL Query to find the year from date.
Answer:  You can use the GETDATE() function to get the current date and then you can use the YEAR() function to extract the year from the date in SQL server. 

Here is how you can find Year from a Date in Microsoft SQL Server database 
SELECT YEAR(GETDATE()) as "Year";




Question 11: Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
Answer: You can use the following query to select distinct records:
SELECT * FROM emp a 
WHERE rowid = (SELECT MAX(rowid) 
FROM EMP b 
WHERE a.empno=b.empno)

to Delete:
DELETE FROM emp a 
WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);


Question 12: There is a table which contains two columns Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above-average students.

Answer: This query can be written using subquery as shown below:
SELECT student, marks 
FROM table 
WHERE marks > SELECT AVG(marks) from table)

SQL Query Interview Questions and Answers



Question 13: How do you find all employees who are also managers?
You have given a standard employee table with an additional column mgr_id, which contains the employee id of the manager.

Employee department SQL Query question

Answer: You need to know about self-join to solve this problem. In Self Join, you can join two instances of the same table to find out additional details as shown below

SELECT e.name, m.name 
FROM Employee e, Employee m 
WHERE e.mgr_id = m.emp_id;

this will show employee name and manager name in two columns like

name  manager_name
John   David


One follow-up is to modify this query to include employees which don't have a manager. To solve that, instead of using the inner join, just use the left outer join, this will also include employees without managers. 

Another interesting problem which is based upon Self join is to find all employees who earn more than their managers, which is also asked as follow up question after this one. 



Question 14: You have a composite index of three columns, and you only provide the value of two columns in the WHERE clause of a select query? Will Index be used for this operation? 

For example, if Index is on EmpId, EmpFirstName, and EmpSecondName and you write a query like

SELECT * FROM Employee WHERE EmpId=2 and EmpFirstName='Radhe'

If the given two columns are secondary index columns then the index will not invoke, but if the given 2 columns contain the primary index(first column while creating index) then the index will invoke. In this case, the Index will be used because EmpId and EmpFirstName are primary columns.


Hope this article will help you to take a quick practice whenever you are going to attend an interview and not have much time to go into the deep of each query.


Other Interview Questions posts from Java67 Blog

146 comments:

  1. Really gud bro iam seraching for two months onwards thnxs...

    ReplyDelete
  2. Nice stuffs...Thanks..pls also try to update somethng new if you have

    ReplyDelete
  3. Excellent Yaar..I was searching such question..Thanks keep posting

    ReplyDelete
  4. very nice, really helpful .... need more such type of questions.

    ReplyDelete
  5. thnx bro it's really helpful

    ReplyDelete
  6. Thanks For uploading............
    And please upload for subqueries Also............

    ReplyDelete
    Replies
    1. Thanks pal...I needed these things...

      Delete
    2. Good queries for practice SQL

      Delete
    3. Thank you queries are really useful for SQL interv preparation

      Delete
  7. Question 9: find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.

    SQL>SELECT * FROM Employees
    WHERE UPPER(EmpName) LIKE '%JOE%';

    ReplyDelete
    Replies
    1. Select * from employee where EmpName in ('Joe','JOE','joe')

      Delete
    2. This wont find jOE, jOe kind of names. Query given by previous Anonymous is correct.

      Delete
    3. select * from employee
      Where EmpName like '%Joe%'

      This will help you find any name whose name came with joe but if u want find only starting 3 letter who start with name Joe. That time u having use below query.

      select * from employee
      Where EmpName like 'Joe%'

      and vice versa for last name word was joe use this

      select * from employee
      Where EmpName like '%Joe'

      Delete
    4. SELECT * FROM Employees
      WHERE EmpName LIKE '%JOE%

      Works fine in Ms Sql server.. and will find you jOE, jOe, kind of names as well.. it is insensitive.

      Delete
    5. SELECT * FROM Employees
      WHERE EmpName LIKE '%JOE%

      Works fine in MsSql Server. It is case insensitive.

      Delete
  8. Even a children can create all these query's ......

    ReplyDelete
    Replies
    1. then why you read till end
      a child can but you can't

      Delete
    2. Why to give reply after 4 yrs...of the comment

      Delete
  9. good effort publish more queries

    ReplyDelete
  10. I was asked this SQL Question in recent interview :

    1) You have a composite index of three columns, and you only provide value of two columns in WHERE clause of a select query? Will Index be used for this operation?

    Can any one please help here?

    ReplyDelete
    Replies
    1. If the given two columns are secondary index column then index will not invoke

      but if the given 2 columns contain primary index(first col while creating index) then index will invoke.

      Delete
    2. in your case the composite index would not work because of the column not included in the where clause.

      Still you want to use an index you give an index hint select /*+ INDEX(TABLE_NAME IDX_NAME) */ * from table_name;

      After imposing the force index you can read the explain plan and verify the cost , if index scan is more costlier than the FTS then its not a good idea to go with index

      Delete
  11. Good Questions.
    Need more sql questions of this kind. :)
    Thanks.

    ReplyDelete
  12. Initial few question are upto standard . Other are very very basic SQL queries

    ReplyDelete
  13. nice upload some query for experience developer

    ReplyDelete
  14. Some queries are very good but some one is very basic and please update this page after some period.

    ReplyDelete
  15. My list of some good SQL Query based interview Questions :

    1) Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
    2) TODO
    3) TODO
    4) TODO

    ha ha ha

    ReplyDelete
    Replies
    1. for selecting the distinct records:

      select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno)


      to Delete:

      delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

      Delete
    2. I know to ways one way use max and another way use distinct
      one way :DELETE
      FROM TestTable
      WHERE ID NOT IN
      (
      SELECT MAX(ID)
      FROM TestTable
      GROUP BY NameCol)
      GO
      and
      another way
      Select Distinct id, name into #temp from emp;
      Truncate table emp;
      Insert into emp(id,name) select id,name from #temp;

      Delete
    3. Ans: Select Key column --> Group by Key column --> Having count(*) > 1
      Will give Duplicate records
      Replace select with Delete statement

      Delete
    4. eg., Delete from customer where cus_id in (select cus_id from customer
      group by cus_id
      having count(*) > 1)

      Delete
    5. For performance (Millions of records) Use exists instead of in and Constant for Existence Checking

      eg.,
      delete from customer a where exists (select 1 from customer b where a.cus_id = b.cus_id
      group by cus_id
      having count(*) > 1)

      Delete
    6. You can use the RowNumber() to delete duplicate records -

      DELETE E
      FROM Employee E
      INNER JOIN (Select RowNumber () over (Partitioned by ID group by ID) R
      From Employee) Emp
      ON E.ID=Emp.ID
      Where Emp.R>1
      Where R>1

      Delete
    7. There may be 100 records with same cus_id , but values in other columns in all the records will not be same.....Then the join condition will fetch all 100 records and will delete 99 records.(Though they are not duplicate)....
      col1 col2 col3
      1 A 2
      1 A 2
      1 B 2
      only first two are duplicate , third is not duplicate..

      Delete
  16. Really good ones, Can you add more do this list please?

    ReplyDelete
    Replies
    1. Hi Anonymous, I am planning to add more such SQL queries, but if you have been asked something then you can also share with us. Thanks

      Delete
  17. I was asked to write following SQL queries in interview. There is a table which contains two column Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above average students. Here is what I wrote:

    SELECT Student, Marks FROM Products WHERE Marks> AVG(Marks);

    And he says WRONG? Hmmmmm.

    ReplyDelete
    Replies
    1. Hi Shubhangi,

      You can not use the where clause with SQL functions. Instead you should use HAVING. So, correct query will be- SELECT Student,Marks FROM Products HAVING Marks>AVG(Marks);

      Delete
    2. select student, marks from table where marks > (select avg(marks) from table)....


      will this work?

      Delete
    3. rahul's not right answer......above subquery will be good......

      Delete
    4. Shubhangi, Aggreate functions can't be used in WHERE clause.

      Rahul, HAVING can not be used without GROUP BY.

      Subquery will serve the purpose.

      Delete
    5. SELECT Student, Marks FROM products
      WHERE marks>(select avg(marks) from products);
      Here we can't use having clause as it is not a group by expression. We can't use group functions like avg,sum,max in where clause even it is a group by expression.

      Delete
    6. I think this is right

      SELECT Student, Marks FROM Products WHERE Marks > (SELECT AVG(Marks) FROM Products);

      Delete
    7. You can try this -
      WITH cte_mks(Student,AvgMarks)
      AS
      (SELECT Student,AVG(marks)mks
      FROM GROUP BY Student)
      SELECT s.Student,Marks
      FROM s
      INNER JOIN cte_mks m on s.Student=m.Student
      and s.marks>m.AvgMarks

      Delete
  18. its very helpful for all give some more example

    ReplyDelete
  19. Anohter interesting query I come across on an interview was "how do you find all employees which are also manager?". give an standard employee table with an additional column mgr_id, which contains employee id of manager.
    Answer : You need to know about self join to solve this problem. In Self Join, you join two instances of same table as shown below

    Select e.name, m.name from Employee e, Employee m where e.mgr_id = m.emp_id;

    this will show employee name and manger name in two column e.g.

    surabhi Balaji
    snhea Balaji

    One follow-up is to modify this query to include employees which doesn't have manager. To solve that, instead of using inner join, just use left outer join, this will include employees without managers.

    ReplyDelete
  20. querys r very helpful to me need more querys to practice

    ReplyDelete
    Replies
    1. sql is not case sensitive

      Delete
    2. or we can directly use

      Select * from Employees where manager_id id not null.


      How about the above one. Because an employee is a manager, then he must be having manager id.

      Please correct me if i'm wrong.

      Delete
  21. SELECT *
    FROM Customer_Entry
    WHERE (Customer_Name LIKE '%JOE%')

    ReplyDelete
  22. The answers given certainly aren't portable or platform independent SQL

    ReplyDelete
    Replies
    1. Yes, I believe GETDATE() method works only SQL Server, not sure if it works on Oracle or MySQL. Though in MSSQL it returns :

      SELECT GETDATE()

      2014-12-11 15:40:02.910

      Delete
    2. Hi,

      I was asked following SQL query in a recent interview :

      You have an Employee table with id, name, salary, manager_id and dept_id and you have a Deparatment table with dept_id and dept_name. Write SQL queries to

      1) Print name of Employee and their Manager's name
      2) Print All deparatment and number of employees on it
      3) Print all employees who has higher salary than average salary of their department.

      I solved them like following, but he says not correct ......

      1) SELECT e1.name, e2.name as manager FROM Employee e1 JOIN Employee e2 WHERE e1.manager_id = e2.id

      2) SELECT d.dept_name, count(*) as NumOfEmploye FROM Employee e, Department d WHERE e.dept_id = d.dept_id

      third one I couldn't able to solve in limited time.

      Please suggest why he said my answers are wrong, I still didn't get it. thank you

      Delete
    3. Hello Mansi, I think your first query is correct. That's the right way to do the self join. but you second query has a little mistake. Since Question was about print all deparatments, you should have used left outer join instead of innner join. In your query it will not print departments where no employee is working. So the correct query would be :

      SELECT d.dept_name, count(*) as NumOfEmploye FROM Depatment d LEFT JOIN Employee e ON d.dept_id = e.dept_id

      Remeber I have also change the order, bringing Department table at left.

      3) For third query I think you can use a subquery to solve the problem. First find out average salary of the department and then print all employee whose salry is greater than average salary.

      Delete
    4. Hi Mansi, Amit here. You can use correlated subquery to find all employees whose salary is greater than average salary in their department, here is the SQL query :

      SELECT emp_name, salary FROM Employee e1 WHERE salary >= (SELECT AVG(salary) FROM Employee e2 where e1.department = e2.department)

      Let me know if you have any question, happy to help.

      Delete
  23. An interesting question for freshers, you have a table called Scorecard with a numeric column score , containing last 6 scores of a cricket player as shown below :

    create table Scorecard (int score)

    insert into Scorecard values (144);
    insert into Scorecard values (144);
    insert into Scorecard values (99);
    insert into Scorecard values (23);
    insert into Scorecard values (68);
    insert into Scorecard values (105);

    Can you write a SQL query to find the second largest score from this table? for example in this case it shoud return 105

    ReplyDelete
    Replies
    1. This question can be solved with and without correlated subquery :

      without correlated sub query :

      select MAX(score) from Scorecard where score NOT IN (select MAX(score) from Scorecard)

      using correlated sub query

      select MAX(score) from Scorecard s1 where 2 = (select count(distinct score) from Scorecard s2 where s2.score >= s1.score)

      Delete
    2. Top n Analysis-
      SELECT *
      FROM (SELECT ROWNUM AS RANK,S1.SCORE
      FROM (SELECT SCORE
      FROM Scorecard
      ORDER BY SCORE DESC)S1)S2
      WHERE S2.RANK=2

      Delete
  24. thanks guys all post are awesome

    ReplyDelete
  25. I asked one Q that in a column positive and negative numbers are there(like 10 20 30 100 -10 -20 -30 -200) now with single select statement I want positive and negative numbers separately. Can anyone explain this?

    ReplyDelete
    Replies
    1. Hi pankaj, please check this query hope it will satisfy ur criteria.

      select * from number1 where digit >= 10 or digit <= 10;

      if m wrong please guide.

      Delete
    2. select (case when digit>=0 then digit end )positive, (case when digit<=0 then digit end )negative
      from t.n;

      Delete
  26. On Question 2, your left outer join is incorrect. You stated, "make sure you do LEFT OUTER JOIN to include departments without any employee as well. Here is the query"

    SELECT DeptName, MAX(Salary) FROM Employee LEFT JOIN Department d ON e.DeptId = d.DeptID;

    Problem is, this join will give you all employees whether or not they belong to a department.

    ReplyDelete
  27. SELECT DeptName, MAX(Salary) FROM Employee e LEFT JOIN Department d ON e.DeptId = d.DeptId
    in most SQL dialects will miserably fail due to the missing GROUP BY clause.
    Even if your SQL dialect will permit it (which I seriously doubt) it
    would return not departments without employees but employees without department, to reach claimed goal you have either write
    SELECT DeptName, MAX(Salary) FROM Department d LEFT JOIN Employee e ON e.DeptId = d.DeptId
    or
    SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptId


    SELECT GetDate();
    Many if not most SQL dialects mandate FROM clause.

    I am to lazy to read this crap further - if someone would give such answer to me his chances to get the job will be around zero.

    ReplyDelete
  28. Very nice queries for experience....

    ReplyDelete
  29. You have an Employee table with id, name, salary, manager_id and dept_id and you have a Deparatment table with dept_id and dept_name. Write SQL queries to
    Print All deparatment and number of employees on it

    ANSWER ASAP if possible....thanx in advance

    ReplyDelete
  30. @Amit, the key here is to use LEFT or RIGHT outer join because Interviewer is looking for ALL department i.e. dpeartment with no employees. Your query should be like

    SELECT d.dept_name, COUNT(id) from Department d LEFT JOIN Employee e ON d.dept_id = e.dept_id
    GROUP BY dept_name;

    Since we have put Department table on left, it will include all department, those also for which dept_id is not available in Employee table.

    ReplyDelete
  31. FInd more sql querys for interview practice on below link....good collection

    http://datawearhousebiworld.blogspot.com/p/blog-page_44.html

    ReplyDelete
  32. I was asked to write the SQL for - Display numbers of customers who ordered and bought the items in the same month ?

    ReplyDelete
    Replies
    1. Select count(customerid) from customers where datepart(mm,bought_date)=datepart(mm,order_date)

      Delete
    2. select customerid from customers where datepart(mm,order_date)=datepart(mm,bought_date)

      Delete
  33. Mostily asked sql developer interview questions:

    Q1. What is the use of GRANT command?

    Ans. GRANT command is used to grant specific user to perform specific task.

    Q2. What is the use of SQL check constraint?
    Ans. CHECK constraint limits the value range that can be placed in a column.

    ReplyDelete
  34. Thanks for the article. Some of the SQL statements are running to the right hand side, could you make them wrap around please? Thanks.

    ReplyDelete
  35. SELECT ISDATE('1/08/13') AS "MM/DD/YY"; why this will return 0. it returns 1 because value is datetime

    ReplyDelete
  36. These SQL queries are good to ask for freshers, or 1 to 2 years experience programmer but any experienced SQL or Web developer surely can answer all of these questions. I would probably include more SQL queries on joins because that's the one area where both junior and senior developer struggle e..g giving them couple of tables with more than 20 columns on each and then asking some SQL queries for generating daily reports. I also try to minimize database specific questions e.g. something which is only applicable to MySQL, SQL SERVER or Oracle should not be asked unless you are tied to just one and really need expert on those database.

    A good interview for SQL should include
    - joins
    - stored procedure
    - and joins again

    Good luck

    ReplyDelete
  37. Why my query doesn't work?

    The query is:

    SELECT MGHEAD.MGLINE, MGHEAD.MTRESP, MGHEAD.MTTRDT, MGHEAD.MTWHLO, MGHEAD.MTTRNR, MGHEAD.MTTRSH
    FROM MVXBE.MVXCDTMESP.MGHEAD
    WHERE (MGHEAD.MGLINE ='100') AND (MGHEAD.MTWHLO='R31') AND (MGHEAD.MTTRSH='15')

    When I run, I have an error: Column MTTRSH not in table MGHEAD in MVXCDTMESP
    Can you help me?

    ReplyDelete
  38. please any one know how to find employee who working more than one department ,write the syntex only .

    ReplyDelete
    Replies
    1. select emp,count(dept) from emp e inner join dept on e.emp=d.emp group by emp having count(dept)>1

      Delete
  39. A good exercise for brains:)

    ReplyDelete
  40. Good work . it is really helpful

    ReplyDelete
  41. *
    **
    ***
    ****
    *****

    using lpad and rpad

    ReplyDelete
  42. The first Query can also be written as
    select max(sal) from emp
    where sal < (select max(sal) from emp where sal;
    It can be nested thrice also so it will give third highest salaries among employees..

    ReplyDelete
  43. Thanks a lot, Really nice Interview Questions.

    ReplyDelete
  44. Good queries for Java Developers

    ReplyDelete
  45. Knowing just SQL queries will not be enough, you also need to prepare traditional SQL interview questions like:
    What are window function?
    Difference between rank() and dense_rank() in SQL?
    Difference between where and having clause in SQL?
    Difference between correlated vs non-correlated subquery?
    primary key vs foreign key
    truncate vs delete etc.

    ReplyDelete
  46. please post more questions..........
    Thanks

    ReplyDelete
  47. What is difference between Clustered Index and No-Clustered Index??

    ReplyDelete
    Replies
    1. Clustered indexes sort and store the data rows in the table or view based on their key values.
      Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

      Delete
  48. What is normalization? Explain different forms of normalization?

    Normalization is a process of organizing the data to minimize the redundancy in the relational database management system (RDBMS). The use of normalization in database is to decompose the relations with anomalies to produce well structured and smaller relations. There are 6 forms of normalization which are as follows:-
    - 1NF represents a relation with no repeating groups
    - 2NF represents no non-prime attribute in the table
    - 3NF defines that every non-prime attribute is non-transitively dependent on every candidate key
    - 4NF defines that every non-trival multi-valued dependency in table is dependent on superkey.
    - 5NF defines that every non-trival join dependency in table is implied by superkey in table.
    - 6NF defines that a table features no non-trival join dependency.

    ReplyDelete
  49. We Have two table Product_detail and Supplier. In Product_detail column are P_id,P_name,P_quantity,P_comments AND In Supplier column are S_id, S_name,S_phone,S_city.

    Question 1 : list all supplier name and phone where product quantity is 0 ?
    Question 2 : list all supplier name where comments is greater then 300 string?

    ReplyDelete
    Replies
    1. Q1: SELECT s_name,s_phone from Supplier s where s.column_name in (select p.colum_name from product_detail p where p.p_quantity = 0)

      OR
      SELECT s_name,s_phone from Supplier s left outer join product_detail p on s.column_name = p.column_name and p.p_quantity = 0)

      Q2. SELECT s_name from Supplier s where s.column_name in (select p.colum_name from product_detail p where len(p.p_comments) > 300)

      OR
      SELECT s_name from Supplier s left outer join product_detail p on s.column_name = p.column_name and len(p.p_comments) > 300

      Delete
  50. Id Dept_Name Dept_Id
    1 A 3
    2 B 5
    3 C 2
    4 D 1
    5 E 1

    I have this department table

    Dept_Name Dept_Id
    A C
    B E
    C B
    D A
    E A

    I want this type output like above.
    anybody can help me

    Which query is used?

    ReplyDelete
    Replies
    1. use self join
      select m.dept_name,n.dept_id from department m,department n where m.id=n.dept_id
      hopefully it works (Y)

      Delete
    2. select d.name,s.name as deptid from Dept_Test d inner join Dept_Test s on d.id=s.deptid

      Delete
    3. select d.name,s.name as deptid from Dept_Test d inner join Dept_Test s on d.id=s.deptid

      Delete
    4. select d.dept_name,m.dept_name as dept_id from department d INNER JOIN department m ON d.dept_id=m.id
      order by d.dept_name

      Delete
  51. Id Dept_Name Dept_Id
    1 A 3
    2 B 5
    3 C 2
    4 D 1
    5 E 1

    I have this department table

    Dept_Name Dept_Id
    A C
    B E
    C B
    D A
    E A

    I want this type output like above.
    anybody can help me

    Which query is used?

    ReplyDelete
    Replies
    1. select d.dept_name,m.dept_name as dept_id from department d INNER JOIN department m ON d.dept_id=m.id
      order by d.dept_name

      Delete
  52. hmmm.. effective stuff..

    ReplyDelete
  53. This is an excellent collection. Can I request you to add more complex and tough SQL questions please e.g. related to index rebuild, performance, query troubleshooting and finding ways to optimize queries, that will help a lot.

    ReplyDelete
  54. hi guys I have one query---- i.e. suppose their is a string named as 'aabacus' to get no of characters'a' from this string we can use select regexp_count('aabacus' 'a') from dual; then o/p -- 3, is their any alternate method to get the same result.


    thanks

    ReplyDelete
    Replies
    1. select len(REPLACE('aabacus','a','aa'))-len('aabacus')

      Delete
  55. Adding one set of questions I faced during oracle interview. Adding the answers too!!

    create table employee(id int, name varchar(50), department varchar(50), manager int, doj date);
    insert into employee values(1,'John','IT',9,'05-08-2010');
    insert into employee values(2,'Alex','Corp',0,'06-03-2008');
    insert into employee values(3,'Linda','IT',9,'07-02-2010');
    insert into employee values(4,'Rahul','Purchase',8,'08-12-2010');
    insert into employee values(5,'Ismail','Purchase',8,'09-08-2012');
    insert into employee values(6,'Zheng','Sales',7,'10-05-2012');
    insert into employee values(7,'Reiki','Sales',2,'11-02-2009');
    insert into employee values(8,'Aris','Sales',2,'12-08-2011');
    insert into employee values(9,'Jena','IT',2,'01-01-2008');
    insert into employee values(10,'Bonny','IT',9,'01-01-2008');
    select * from employee;
    select id,manager,to_char(doj,'dd-mon-yyyy') from employee;
    1. list the employees who are not managers

    select name from employee
    where id not in (select manager from employee);

    2. manager with only one reportee

    select mgr from (select e1.id as mgr ,e2.id as id from employee e1
    inner join employee e2 on
    e1.id=e2.manager)abc
    group by mgr having count(id)=1

    3. what is the month with most hiring?

    select dt from (select dt,rank() over(order by cnt desc) as rnk from (select to_char(doj,'mm') as dt ,count(*) as cnt from employee
    group by to_char(doj,'mm'))) where rnk=1;

    4.what is the experience gap between the first employee and the latest?

    select max(doj)-min(doj) from employee;

    5. name the manager with most reportees?

    select mgr from (select mgr,rank() over( order by cnt desc) as rnk from (select mgr,count(id) as cnt from (select e1.id as mgr,e2.id as id from employee e1
    inner join employee e2
    on e1.id=e2.manager)
    group by mgr)) where rnk=1

    6.list managers who joined after the reportees

    select mgr from (select e1.id as mgr,e2.id as id,e1.doj as mdoj, e2.doj as edoj from employee e1
    join employee e2 on
    e1.id=e2.manager)
    group by mgr
    having max(mdoj)>min(edoj)

    7.department with most managers and how many?

    select department,cnt from (select department,rank() over(order by cnt desc) as rnk,cnt from (select count(distinct manager) as cnt ,department from employee
    group by department)) where rnk=1

    ReplyDelete
    Replies
    1. Great questions Bonny, keep it up. Thanks for sharing with us.

      Delete
  56. Good questions and explained with clarity.

    ReplyDelete
  57. really good article , helped me for interview preperation

    ReplyDelete
  58. very uesfully sql query and some more query in the point of interview process query pls upload....

    ReplyDelete
  59. very usefully sql query

    ReplyDelete
  60. Emp tb1 and emp tb2 two table here I want second emp tb2 emp id 1,2,1,2 emp id 1 salary 2000 empid 2 salary 4000 empid 1 salary 3000 empid 2 salary 5000
    Result
    Emp id 1 ,1 5000
    Emp id 2 ,2 9000
    How to written a query's

    ReplyDelete
  61. Emp tb1 and emp tb2 two table here I want second emp tb2 emp id 1,2,1,2 emp id 1 salary 2000 empid 2 salary 4000 empid 1 salary 3000 empid 2 salary 5000
    Result
    Emp id 1 ,1 5000
    Emp id 2 ,2 9000
    How to written a query's

    ReplyDelete
  62. Example @gmail.com
    How to get before @value .I want select query

    ReplyDelete
    Replies
    1. You can use substr and instr combination

      Delete
  63. • Rank function syntax in SQL

    SELECT a1.ename, a1.Sal, COUNT (a2.Sal) Sal_Rank
    FROM emp a1,emp a2
    WHERE a1.Sal < a2.Sal OR (a1.Sal=a2.Sal AND a1.ename = a2.ename)
    GROUP BY a1.ename, a1.Sal
    ORDER BY a1.Sal DESC, a1.ename DESC;


    • Running_Total


    SELECT a1.ename, a1.Sal, SUM(a2.Sal) Running_Total
    FROM emp a1, emp a2
    WHERE a1.Sal <= a2.sal or (a1.Sal=a2.Sal and a1.eName = a2.eName)
    GROUP BY a1.eName, a1.Sal
    ORDER BY a1.Sal DESC, a1.eName DESC;

    • Percent To Total
    SELECT a1.eName, a1.Sal, a1.Sal/(SELECT SUM(Sal) FROM emp) Pct_To_Total
    FROM emp a1, emp a2
    WHERE a1.Sal <= a2.sal or (a1.Sal=a2.Sal and a1.eName = a2.eName)
    GROUP BY a1.eName, a1.Sal
    ORDER BY a1.Sal DESC, a1.eName DESC;



    • Cumulative Percent To Total

    SELECT a1.eName, a1.Sal, SUM(a2.Sal)/(SELECT SUM(Sal) FROM emp) Pct_To_Total
    FROM emp a1, emp a2
    WHERE a1.Sal <= a2.sal or (a1.Sal=a2.Sal and a1.eName = a2.eName)
    GROUP BY a1.eName, a1.Sal
    ORDER BY a1.Sal DESC, a1.eName DESC;

    ReplyDelete
  64. 125. Display all employees with their department names
    126. Display ename who are working in sales department
    127. Display ENAME, DNAME, SAL and COMM for employees with salary between 2000 to 5000 and location is Chicago
    128. Display those employees whose salary is greater than his managers salary
    129. Display those employees who are working in the same dept where his manager is work
    130. Display those employees who are not working under any Manager
    131. Display ENAME, GRADE (deptno 10 or 30) (grade is not 4) (joined company before 31-DEC-82)
    132. Delete employees joined company before 31-Dec-82 while their Location is New York or Chicago?
    133. Display employee name ,job,deptname,loc for all who are working as manager?
    134. Display those employees whose manager name is JONES and also display their manager name?
    135. Display employee names who are working in ACCOUNTING department
    136. Display the employee names who are working in CHICAGO
    137. Display name and salary of FORD if his salary is equal to hisal of his grade?
    138. Display employees whose salary is less than his manager but more than salary of other managers?
    139. Display those employees whose manager name is Jones
    140. Display the details of those employees who do not have any person working under him
    141. Display the details of those employees who are in sales department and grade is 3
    142. Display those department where no employee working?
    143. Display ename, job and his manager. Display also employees who are without managers?
    144. Find out the number of employees whose salary is greater than their managers salary?
    145. Display ename, job, dname, his manager name, his grade and display output department number wise?
    146. List ename, job, sal, grade and dname for everyone in a company except 'CLERK'. Sort on salary display the highest salary first?
    147. Display ENAME, Manager Name for employees who has EMPNO as odd number; Display ENAME, Manager’s Manager Name for all the employees who has EMPNO as even number. If Manager’s Manager is not present display Manager Name and display employee name if no manager is present for that employee. Output should consist of two columns: 1-ENAME, 2-BOSS

    ReplyDelete
    Replies
    1. Hello Yashwanth, very good questions, can you or anyone else provide solutions as well?

      Delete
    2. Good list, I recommend "TOP 30 SQL Interview Coding Tasks" by Matthew Urban (https://www.amazon.com/TOP-SQL-Interview-Coding-Tasks-ebook/dp/B07GC5RS3K), very good list there, helped with my interview. thanks.

      Delete
  65. Where can i find a data set to play with the above queries

    ReplyDelete
  66. what is sql injection ? please explain with an example

    ReplyDelete
    Replies
    1. Hello Anonymous, SQL Injection is a vulnerability which comes by dynamically generating SQL without sanitizing user input. This allows attacker to inject SQL code which can harm your system or leak more data then expected. For example, if you are writing SQL query like

      'SELECT * from Users where UserId = ' + userId

      where userId is a String entered by user then if user enter something like "1 or 1=1' then it will return all rows from User table.

      Delete
  67. second highest salary

    select min(Salary) from Employee where amounts in (select top 2 Salary from Employee order by Salary desc)

    ReplyDelete
  68. Good information for Beginers

    ReplyDelete
  69. Hi all I just started to learn sql so can u tall me the detail explanation for the que-11,12

    ReplyDelete
  70. This comment has been removed by the author.

    ReplyDelete
  71. Helpful.. Thank you

    ReplyDelete
  72. hi, where can i find/practice the questions in this table in? is there any online interactive sql table wher ei can practice the joins, ideally with the same dataset? THANK YOU

    ReplyDelete
  73. Anyone help!! There is a table with 2 columns.one is Name and the other is marks. How to find the topper of the class without using MAX, LIMIT, ORDER BY functions.

    ReplyDelete
  74. The first part of the answer to Q11 does not answer the first part of the question, it simply states a new problem and answers that.

    ReplyDelete
  75. select ISDATE('1/08/13') as "MM/DD/YY"; // am getting the output value as 1 // i think ISDATE checks only whether the given value is in date format or not...

    ReplyDelete
  76. Hi very nice, is there a database or table we can create to practice the questions

    ReplyDelete
    Replies
    1. Sorry, I don't have database but you can create your own and just insert some dummy data. There are also website which allows you to practice queries. See the link

      Delete
  77. hello,
    i am trying to find out how to write a select which takes dates with ratings only when the rating changed against last date compared to next one (in this example it would be 2021-01-31 B, 2021-04-30 A, 2021-07-31 AA+, 2021-10-31 A) - thank you for your advice
    bidated rating
    2021-01-31 B
    2021-02-28 B
    2021-03-31 B
    2021-04-30 A
    2021-05-31 A
    2021-06-30 A
    2021-07-31 AA+
    2021-08-31 AA+
    2021-09-30 AA+
    2021-10-31 A

    ReplyDelete
    Replies
    1. This is an interesting problem, because you need to compare each rows to the subsequent rows, I think you can use a correlated subquery

      Delete
  78. Hello,

    I have one sql question

    There are two tables 1. Trade_details & 2. Location
    coloums are in 1st tables Trade_id, Counter_Party, Quanity & Location id
    coloumns are 2nd table Location id & City.
    so question is : I want to list of trade in mumbai city and quantity is >100

    ReplyDelete
    Replies
    1. Hello Anonymous, you can join the two tables on LocationId and then filter for Mumbai.
      here is how your SELECT query with JOIN look like

      Select Trade_Id, Counter_Party, , Location, Quantity
      from Trade_Details t join Location l on t.LocationId = l.LocationId
      where l.City='Mumbai'
      and t.quantity > 100

      Delete
  79. Hi javin paul where can i find this question in archives. I could see up to year 2016. Please where I can find collective of all questions.

    ReplyDelete
    Replies
    1. Hello there, YEs, they are here but they were moved to later date because I have updated many of them. you can find them under 2021 or 2020 archives. In fact, you better look at label to see all the posts related to SQL etc.

      Delete
  80. Hi Friends, I have a doubt from SQL Query. Doubt- How to get all the OrderIDs from the Customer(CustomerID) who has given max orders

    Table- Orders
    Columns- CustomerID, OrderID

    CustomerID OrderID
    101 2000
    101 1980
    102 2012
    103 2045
    104 2670
    104 2430
    104 3200

    Here, CustomerID 104 user has given max 3 orders.
    I want to identify this through query. CustomerID and all OrderID

    ReplyDelete

Feel free to comment, ask questions if you have any doubt.