Top Half Dozen Sql Query Interview Questions As Well As Answers

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

In the past, I receive got recommended a duo of books as well as websites to improve your SQL query skills but nada is improve than the agreement schema, information as well as writing your ain SQL queries.

In guild to larn fast, start amongst a pocket-size tabular array amongst few columns which include information types similar number, date, as well as String, has less unwrap of information so that yous tin flame rapidly empathize as well as hold back what should move output. Includes approximately NULL, empty as well as out of outpouring values to truly examine 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, yous volition respect SQL script to do a table as well as populate amongst sample information as well as and so write SQL queries to solve approximately mutual problems from Interviews.

SQL Script to do tabular array as well as Populate data

In this section, we'll run across our SQL script for creating as well as populating sample tabular array required for running SQL queries. I receive got chosen Employee as well as Department tabular array to learn yous how to write SQL queries because it is 1 of the most popular SQL query examples as well as most of the developers, students, as well as technical guys are familiar amongst this scheme.

This is besides the representative many of yous receive got used inward your academics so it's quite slow to empathize as well as correlate. Remember, agreement of schema as well as information is real of import non entirely to write right SQL queries but besides to verify that your SQL query is right past times looking at the output.

The SQL queries are written for Microsoft SQL Server 2014 as well as tested on same, but yous tin flame easily run on Oracle, MySQL or whatever other database of your pick past times removing T-SQL code e.g. the 1 which checks if a tabular array already exists as well as and so driblet as well as re-create it. Most of the code is measure ANSI SQL, so it volition run every bit it is on whatever other database. If yous nonetheless confront whatever employment as well as so yous tin flame besides cheque this guide to migrate SQL Server queries to Oracle.



SQL scripts to do tables 

USE Test GO  -- driblet 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  -- driblet 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 yous don't receive got the Test database inward your SQL Server instance as well as so either do it or withdraw the "USE Test" to run on whatever database of your choice, yous tin flame besides modify the lift of the database as well as give-up the ghost on the "USE".

When yous run this script, it volition do as well as populate the information outset time. When yous run it again, it volition driblet as well as recreate the tables again, every bit shown inward the next output:

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

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

This representative shows that only next approximately uncomplicated SQL best practices tin flame seriously improve the queries yous write. If yous are interested inward learning to a greater extent than SQL best practices, I propose reading SQL Antipatterns, an interesting majority for both beginners as well as 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 examine many of your SQL skills e.g. joins, grouping as well as aggregating data, how yous handgrip nulls inward SQL etc. It doesn't examine all skills e.g. correlated subqueries, but yous tin flame receive got a facial expression at questions similar how to respect Nth highest salary of employees to larn that.

This subdivision contains half-dozen problems for which yous demand to write SQL queries, the solution is provided inward the side past times side subdivision but I propose yous to effort to solve these problems outset before looking at the solution.

1. Can yous write an SQL query to present Employee (names) who receive got a bigger salary than their manager?

2. Write an SQL query to respect Employees who receive got the biggest salary inward their Department?

3. Write an SQL query to listing Departments that receive got less than three people inward it?

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

5. Can yous write an SQL query to present all Employees that don't receive got a director inward the same department?

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

7. Can yous write an SQL query to respect the minute highest salary of Employee? (solution)

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

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

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

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

12. There exists an Order tabular array as well as a Customer table, respect all Customers who receive got never ordered (solution)

Don't scroll downwardly to facial expression the solution until yous effort 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 yous tin flame receive got your fourth dimension because all the difficult operate yous hear volition set right away to respect answers past times its ain volition ever stay in that place as well as that's the existent learning yous volition acquire past times doing this exercise.

Btw, if yous are interested inward to a greater extent than SQL query interview questions, as well as so yous tin flame besides cheque Joe Celko's SQL Puzzles as well as Answers, real interesting as well as challenging to truly examine 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, yous demand to compare employee's salary to their manager's salary. To hit this, yous demand 2 instances of the same table. Also inward guild to respect Manager yous 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 receive got 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 piddling combat complex employment to solve, yous outset demand to respect the maximum salary of each department, but the subdivision doesn't receive got the salary, it is the employee who has the salary. So nosotros demand to do a virtual tabular array where nosotros should receive got both subdivision as well as salary. This tin flame move achieved past times joining both Employee as well as Department tabular array on dept_id as well as and so using GROUP past times clause to grouping salary on dept_id.  Now, somebody tin flame interrogation why nosotros didn't 

Since nosotros demand to impress the lift of the employee who has the highest salary, nosotros demand to compare each employee's salary amongst the department's highest salary which nosotros receive got only calculated. This tin flame move done past times keeping the effect of the previous query inward a temp tabular array as well as and so joining it 1 time to a greater extent than amongst Employee table. 

-- Employees who receive got 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 interrogation to solve.  You only demand to know how to purpose the COUNT() business office as well as GROUP BY clause.

-- Departments that receive got less than three 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 unwrap 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 outset SQL query interview question, where nosotros receive got used self-join to solve the problem. There nosotros compared the salary of employee as well as hither nosotros receive got compared their department.  

-- Employees that don't receive got 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 employment is similar to the quaternary interrogation inward this list. Here besides yous demand to purpose OUTER JOIN instead of INNER bring together to include empty departments which should receive got 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 almost SQL query interview questions. If yous are an interviewer, as well as so it's truly non bad agency to cheque SQL skills of a candidate. Influenza A virus subtype H5N1 defined schema as well as real clear as well as uncomplicated requirements are what yous hold back on the curt duration of Interview. Once the candidate has solved the employment yous tin flame fifty-fifty verbalise over optimization. It's much improve than bespeak him almost the difference betwixt left as well as right joins.

If yous are a candidate as well as so it's what yous truly 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 pith science is Java or C++, I strongly propose yous brush upwards your SQL skills before whatever face-to-face programming interview. If yous 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 as well as HAVING clause inward SQL? (answer)

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

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

How to compare appointment columns inward SQL? (answer)

What is the divergence betwixt the Primary as well as Foreign commutation inward SQL? (Answer)

What is the divergence betwixt catch as well as materialized view? (answer)

Thanks for reading this article, if yous receive got similar this article as well as so delight portion amongst your friends as well as colleagues. If yous receive got tips to improve SQL science or whatever interesting SQL query questions from your interview as well as so delight portion amongst us via comments. 

Share this post