Top Half Dozen Sql Query Interview Questions As Well As Answers

The SQL, brusk shape of Structured Query Language is 1 of the essential skills inward today's programming world. No affair whether y'all are a Java developer, C++ developer or Python developer, y'all must know how to write SQL queries. Every programming labor interview has at to the lowest degree 1 or 2 questions which require y'all to write SQL query for given requirement too many developers struggles there. It's slow to reply theoretical questions similar what is the difference betwixt clustered too non-clustered index (see) or what is the divergence betwixt correlated too non-correlated subqueries (see), but when it comes fourth dimension to truly write SQL queries to solve problems, it's non that easy, specially if y'all haven't done your homework too practice.

In the past, I direct hold recommended a twain of books too websites to improve your SQL query skills but nada is amend than the agreement schema, information too writing your ain SQL queries.

In gild to larn fast, start amongst a minor tabular array amongst few columns which include information types similar number, date, too String, has less position out of information so that y'all tin dismiss chop-chop sympathise too facial expression what should hold out output. Includes about NULL, empty too out of saltation values to truly seek your queries.

Considering all these together today I am going to portion SQL script to do a sample tabular array to practise writing SQL queries for interviews. In this article, y'all volition notice SQL script to do a table too populate amongst sample information too and then write SQL queries to solve about mutual problems from Interviews.

SQL Script to do tabular array too Populate data

In this section, we'll come across our SQL script for creating too populating sample tabular array required for running SQL queries. I direct hold chosen Employee too Department tabular array to learn y'all how to write SQL queries because it is 1 of the most popular SQL query examples too most of the developers, students, too technical guys are familiar amongst this scheme.

This is likewise the instance many of y'all direct hold used inward your academics so it's quite slow to sympathise too correlate. Remember, agreement of schema too information is really of import non entirely to write right SQL queries but likewise to verify that your SQL query is right past times looking at the output.

The SQL queries are written for Microsoft SQL Server 2014 too tested on same, but y'all tin dismiss easily run on Oracle, MySQL or whatever other database of your alternative past times removing T-SQL code e.g. the 1 which checks if a tabular array already exists too and then drib too re-create it. Most of the code is measure ANSI SQL, thus it volition run every bit it is on whatever other database. If y'all nonetheless human face upward whatever work too then y'all tin dismiss likewise banking concern gibe this guide to migrate SQL Server queries to Oracle.



SQL scripts to do tables 

USE Test GO  -- drib Employee tabular array if already exists IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL BEGIN   PRINT 'Employee Table Exists, dropping it now'  DROP TABLE Employee; END  -- drib Department tabular array if already exists IF OBJECT_ID('dbo.Department', 'U') IS NOT NULL BEGIN   PRINT 'Department Table Exists, dropping it now'   DROP TABLE Department; END  -- do tabular array ddl statments CREATE TABLE Employee(emp_id INTEGER PRIMARY KEY, dept_id INTEGER,  mngr_id INTEGER, emp_name VARCHAR(20), salary INTEGER); CREATE TABLE Department(dept_id INTEGER PRIMARY KEY, dept_name VARCHAR(20));  -- alter tabular array to add together unusual keys ALTER TABLE Employee ADD FOREIGN KEY (mngr_id) REFERENCES Employee(emp_id); ALTER TABLE Employee ADD FOREIGN KEY (dept_id) REFERENCES Department(dept_id);  -- populating subdivision tabular array amongst sample data INSERT INTO Department (dept_id, dept_name)  VALUES (1, 'Finance'), (2, 'Legal'), (3, 'IT'), (4, 'Admin'), (5, 'Empty Department');  -- populating employee tabular array amongst sample data INSERT INTO Employee(emp_id, dept_id, mngr_id, emp_name, salary) VALUES( 1, 1, 1, 'CEO', 100), ( 2, 3, 1, 'CTO', 95), ( 3, 2, 1, 'CFO', 100), ( 4, 3, 2, 'Java Developer', 90), ( 5, 3, 2, 'DBA', 90), ( 6, 4, 1, 'Adm 1', 20), ( 7, 4, 1, 'Adm 2', 110), ( 8, 3, 2, 'Web Developer', 50), ( 9, 3, 1, 'Middleware', 60), ( 10, 2, 3, 'Legal 1', 110), ( 11, 3, 3, 'Network', 80), ( 12, 3, 1, 'UNIX', 200);

This query runs on the Test database, if y'all don't direct hold the Test database inward your SQL Server instance too then either do it or take away the "USE Test" to run on whatever database of your choice, y'all tin dismiss likewise modify the advert of the database too cash inward one's chips along the "USE".

When y'all run this script, it volition do too populate the information start time. When y'all run it again, it volition drib too recreate the tables again, every bit shown inward the next output:

Employee Table Exists, dropping it at in 1 lawsuit Department Table Exists, dropping it at in 1 lawsuit  (5 row(s) affected)  (12 row(s) affected)

In this script, I direct hold followed the naming convention too tricks which I discussed before inward my article, a amend agency to write SQL queries.  All the keyword is on the working capital missive of the alphabet instance land tabular array names too column names are inward minor too camel case. This improves the readability of SQL queries past times clearing highlight which ones are keywords too which ones are object names fifty-fifty if syntax highlight is non available.

This instance shows that merely next about uncomplicated SQL best practices tin dismiss seriously improve the queries y'all write. If y'all are interested inward learning to a greater extent than SQL best practices, I propose reading SQL Antipatterns, an interesting mass for both beginners too experienced programmers.

SQL Query Interview Questions

It's the fourth dimension write SQL queries now. This subdivision contains half dozen SQL query Interview questions which volition seek many of your SQL skills e.g. joins, grouping too aggregating data, how y'all handgrip nulls inward SQL etc. It doesn't seek all skills e.g. correlated subqueries, but y'all tin dismiss direct hold a facial expression at questions similar how to notice Nth highest salary of employees to larn that.

This subdivision contains half dozen problems for which y'all demand to write SQL queries, the solution is provided inward the adjacent subdivision but I propose y'all to elbow grease to solve these problems start before looking at the solution.

1. Can y'all write an SQL query to present Employee (names) who direct hold a bigger salary than their manager?

2. Write an SQL query to notice Employees who direct hold the biggest salary inward their Department?

3. Write an SQL query to listing Departments that direct hold less than iii people inward it?

4. Write an SQL query to present all Departments along amongst the position out of people there?

5. Can y'all write an SQL query to present all Employees that don't direct hold a director inward the same department?

6. Can y'all write SQL query to listing all Departments along amongst the full salary there?

7. Can y'all write an SQL query to notice the minute highest salary of Employee? (solution)

8. How to notice all duplicate records from a table? (solution)

9. How do y'all re-create all rows of a tabular array using SQL query? (solution)

10. How do y'all bring together to a greater extent than than 2 tables inward SQL query? (solution)

11. How to notice 2d highest salary without using a co-related subquery? (solution)

12. There exists an Order tabular array too a Customer table, notice all Customers who direct hold never ordered (solution)

Don't scroll downward to facial expression the solution until y'all elbow grease solving all the problems past times yourself. Some of the questions are tricky, so delight pay special attending to them. It's non a existent interview y'all tin dismiss direct hold your fourth dimension because all the difficult run y'all heed volition position at in 1 lawsuit to notice answers past times its ain volition ever rest at that topographic point too that's the existent learning y'all volition acquire past times doing this exercise.

Btw, if y'all are interested inward to a greater extent than SQL query interview questions, too then y'all tin dismiss likewise banking concern gibe Joe Celko's SQL Puzzles too Answers, really interesting too challenging to truly seek your SQL skills.

Solution of SQL Query Interview Questions

Here is the solution of all SQL query problems discussed inward the final section

1) In this problem, y'all demand to compare employee's salary to their manager's salary. To accomplish this, y'all demand 2 instances of the same table. Also inward gild to notice Manager y'all demand to compare employee id amongst director id, this is achieved past times using the self-join inward SQL, where 2 instances of the same tabular array are compared.

-- Employees (names) who direct hold a bigger salary than their manager

SELECT a.emp_name FROM Employee a JOIN Employee b ON a.mngr_id = b.emp_id WHERE a.salary > b.salary; 

2) This is a petty flake complex work to solve, y'all start demand to notice the maximum salary of each department, but the subdivision doesn't direct hold the salary, it is the employee who has the salary. So nosotros demand to do a virtual tabular array where nosotros should direct hold both subdivision too salary. This tin dismiss hold out achieved past times joining both Employee too Department tabular array on dept_id too and then using GROUP past times clause to grouping salary on dept_id.  Now, individual tin dismiss inquiry why nosotros didn't 

Since nosotros demand to impress the advert of the employee who has the highest salary, nosotros demand to compare each employee's salary amongst the department's highest salary which nosotros direct hold merely calculated. This tin dismiss hold out done past times keeping the resultant of the previous query inward a temp tabular array too and then joining it in 1 lawsuit to a greater extent than amongst Employee table. 

-- Employees who direct hold the biggest salary inward their Department SELECT a.emp_name, a.dept_id FROM Employee a JOIN (SELECT a.dept_id, MAX(salary) as max_salary FROM Employee a JOIN Department b ON a.dept_id = b.dept_id GROUP BY a.dept_id) b ON a.salary = b.max_salary AND a.dept_id = b.dept_id;   3) This is a rather uncomplicated SQL query interview inquiry to solve.  You merely demand to know how to purpose the COUNT() business office too GROUP BY clause.

-- Departments that direct hold less than iii people inward it SELECT dept_id, COUNT(emp_name) as 'Number of Employee' FROM Employee GROUP BY dept_id HAVING COUNT(emp_name) < 3; 

4) This is a tricky problem, candidates oft purpose inner bring together to solve the problem,  leaving out empty departments.

-- All Department along amongst the position out of people there SELECT b.dept_name, COUNT(a.dept_id) as 'Number of Employee' FROM Employee a FULL OUTER JOIN Department b ON a.dept_id=b.dept_id GROUP BY b.dept_name;   5) This is similar to the start SQL query interview question, where nosotros direct hold used self-join to solve the problem. There nosotros compared the salary of employee too hither nosotros direct hold compared their department.  

-- Employees that don't direct hold a director inward the same department SELECT a.emp_name FROM Employee a JOIN Employee b ON a.mngr_id = b.emp_id WHERE a.dept_id != b.dept_id;   6) This work is similar to the quaternary inquiry inward this list. Here likewise y'all demand to purpose OUTER JOIN instead of INNER bring together to include empty departments which should direct hold no salaries.  -- All Department along amongst the full salary there SELECT b.dept_name, SUM(a.salary) as 'Total Salary' FROM Employee a FULL OUTER JOIN Department b ON a.dept_id = b.dept_id GROUP BY b.dept_name;

Here is the output of these SQL queries when running from SQL Server Management Studio:

That's all inward this article virtually SQL query interview questions. If y'all are an interviewer, too then it's truly corking agency to banking concern gibe SQL skills of a candidate. H5N1 defined schema too really clear too uncomplicated requirements are what y'all facial expression on the brusk duration of Interview. Once the candidate has solved the work y'all tin dismiss fifty-fifty utter over optimization. It's much amend than yell for him virtually the difference betwixt left too right joins.

If y'all are a candidate too then it's what y'all truly demand to caput start your preparation. Many SQL programmers merely don't practise SQL query before going into interviews, which is a large error inward my opinion. Even if your meat science is Java or C++, I strongly propose y'all brush upward your SQL skills before whatever face-to-face programming interview. If y'all desire to do truly well, I propose solving SQL problems from Joe Celko's SQL Puzzels.

Further Learning
answer)

What is the difference betwixt WHERE too HAVING clause inward SQL? (answer)

What is divergence betwixt rank(), row_number(), too dense_rank() inward SQL? (answer)

What is the divergence betwixt TRUNCATE too DELETE ascendance inward SQL? (answer)

How to compare appointment columns inward SQL? (answer)

What is the divergence betwixt the Primary too Foreign cardinal inward SQL? (Answer)

What is the divergence betwixt watch too materialized view? (answer)

Thanks for reading this article, if y'all direct hold similar this article too then delight portion amongst your friends too colleagues. If y'all direct hold tips to improve SQL science or whatever interesting SQL query questions from your interview too then delight portion amongst us via comments.