Top Half-Dozen Sql Query Interview Questions Too Answers

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

In the past, I conduct hold recommended a dyad of books together with websites to improve your SQL query skills but naught is meliorate than the agreement schema, information together with writing your ain SQL queries.

In social club to larn fast, start amongst a pocket-size tabular array amongst few columns which include information types similar number, date, together with String, has less discover of information so that you lot tin dismiss chop-chop empathise together with hold off what should endure output. Includes only about NULL, empty together with out of saltation values to genuinely evidence 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, you lot volition notice SQL script to do a table together with populate amongst sample information together with and so write SQL queries to solve only about mutual problems from Interviews.

SQL Script to do tabular array together with Populate data

In this section, we'll meet our SQL script for creating together with populating sample tabular array required for running SQL queries. I conduct hold chosen Employee together with Department tabular array to learn you lot how to write SQL queries because it is 1 of the most popular SQL query examples together with most of the developers, students, together with technical guys are familiar amongst this scheme.

This is besides the illustration many of you lot conduct hold used inward your academics so it's quite slow to empathise together with correlate. Remember, agreement of schema together with information is real of import non solely to write right SQL queries but besides to verify that your SQL query is right yesteryear looking at the output.

The SQL queries are written for Microsoft SQL Server 2014 together with tested on same, but you lot tin dismiss easily run on Oracle, MySQL or whatsoever other database of your alternative yesteryear removing T-SQL code e.g. the 1 which checks if a tabular array already exists together with and so drib together with re-create it. Most of the code is measure ANSI SQL, thence it volition run every bit it is on whatsoever other database. If you lot withal human face upwards whatsoever job together with so you lot tin dismiss besides cheque 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 you lot don't conduct hold the Test database inward your SQL Server instance together with so either do it or take away the "USE Test" to run on whatsoever database of your choice, you lot tin dismiss besides alter the advert of the database together with perish on the "USE".

When you lot run this script, it volition do together with populate the information commencement time. When you lot run it again, it volition drib together with recreate the tables again, every bit shown inward the next output:

Employee Table Exists, dropping it directly Department Table Exists, dropping it directly  (5 row(s) affected)  (12 row(s) affected)

In this script, I conduct hold followed the naming convention together with tricks which I discussed before inward my article, a meliorate agency to write SQL queries.  All the keyword is on the uppercase instance piece tabular array names together with column names are inward pocket-size together with camel case. This improves the readability of SQL queries yesteryear clearing highlight which ones are keywords together with which ones are object names fifty-fifty if syntax highlight is non available.

This illustration shows that only next only about elementary SQL best practices tin dismiss seriously improve the queries you lot write. If you lot are interested inward learning to a greater extent than SQL best practices, I propose reading SQL Antipatterns, an interesting mass for both beginners together with 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 evidence many of your SQL skills e.g. joins, grouping together with aggregating data, how you lot grip nulls inward SQL etc. It doesn't evidence all skills e.g. correlated subqueries, but you lot tin dismiss conduct hold a human face at questions similar how to notice Nth highest salary of employees to larn that.

This subdivision contains half dozen problems for which you lot demand to write SQL queries, the solution is provided inward the adjacent subdivision but I propose you lot to endeavor to solve these problems commencement before looking at the solution.

1. Can you lot write an SQL query to exhibit Employee (names) who conduct hold a bigger salary than their manager?

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

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

4. Write an SQL query to exhibit all Departments along amongst the discover of people there?

5. Can you lot write an SQL query to exhibit all Employees that don't conduct hold a managing director inward the same department?

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

7. Can you lot write an SQL query to notice the mo highest salary of Employee? (solution)

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

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

10. How do you lot bring together to a greater extent than than ii tables inward SQL query? (solution)

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

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

Don't scroll downwardly to human face the solution until you lot endeavor solving all the problems yesteryear yourself. Some of the questions are tricky, so delight pay special attending to them. It's non a existent interview you lot tin dismiss conduct hold your fourth dimension because all the difficult piece of work you lot heed volition pose directly to notice answers yesteryear its ain volition e'er rest at that topographic point together with that's the existent learning you lot volition acquire yesteryear doing this exercise.

Btw, if you lot are interested inward to a greater extent than SQL query interview questions, together with so you lot tin dismiss besides cheque Joe Celko's SQL Puzzles together with Answers, real interesting together with challenging to genuinely evidence your SQL skills.

Solution of SQL Query Interview Questions

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

1) In this problem, you lot demand to compare employee's salary to their manager's salary. To hand this, you lot demand ii instances of the same table. Also inward social club to notice Manager you lot demand to compare employee id amongst managing director id, this is achieved yesteryear using the self-join inward SQL, where ii instances of the same tabular array are compared.

-- Employees (names) who conduct 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 lilliputian fleck complex job to solve, you lot commencement demand to notice the maximum salary of each department, but the subdivision doesn't conduct hold the salary, it is the employee who has the salary. So nosotros demand to do a virtual tabular array where nosotros should conduct hold both subdivision together with salary. This tin dismiss endure achieved yesteryear joining both Employee together with Department tabular array on dept_id together with and so using GROUP yesteryear clause to grouping salary on dept_id.  Now, individual tin dismiss enquiry 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 conduct hold only calculated. This tin dismiss endure done yesteryear keeping the resultant of the previous query inward a temp tabular array together with and so joining it 1 time again amongst Employee table. 

-- Employees who conduct 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 elementary SQL query interview enquiry to solve.  You only demand to know how to work the COUNT() run together with GROUP BY clause.

-- Departments that conduct 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 work inner bring together to solve the problem,  leaving out empty departments.

-- All Department along amongst the discover 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 commencement SQL query interview question, where nosotros conduct hold used self-join to solve the problem. There nosotros compared the salary of employee together with hither nosotros conduct hold compared their department.  

-- Employees that don't conduct hold a managing 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 job is similar to the fourth enquiry inward this list. Here besides you lot demand to work OUTER JOIN instead of INNER bring together to include empty departments which should conduct 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 well-nigh SQL query interview questions. If you lot are an interviewer, together with so it's genuinely cracking agency to cheque SQL skills of a candidate. Influenza A virus subtype H5N1 defined schema together with real clear together with elementary requirements are what you lot hold off on the brusk duration of Interview. Once the candidate has solved the job you lot tin dismiss fifty-fifty beak over optimization. It's much meliorate than quest him well-nigh the difference betwixt left together with right joins.

If you lot are a candidate together with so it's what you lot genuinely demand to caput start your preparation. Many SQL programmers only don't practise SQL query before going into interviews, which is a large error inward my opinion. Even if your gist science is Java or C++, I strongly propose you lot brush upwards your SQL skills before whatsoever face-to-face programming interview. If you lot desire to do genuinely well, I propose solving SQL problems from Joe Celko's SQL Puzzels.

Further Learning
answer)

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

What is departure betwixt rank(), row_number(), together with dense_rank() inward SQL? (answer)

What is the departure betwixt TRUNCATE together with DELETE ascendance inward SQL? (answer)

How to compare appointment columns inward SQL? (answer)

What is the departure betwixt the Primary together with Foreign cardinal inward SQL? (Answer)

What is the departure betwixt catch together with materialized view? (answer)

Thanks for reading this article, if you lot conduct hold similar this article together with so delight portion amongst your friends together with colleagues. If you lot conduct hold tips to improve SQL science or whatsoever interesting SQL query questions from your interview together with so delight portion amongst us via comments. 

Share this post