In this article, I am giving around examples of SQL queries which is oftentimes asked when y'all give-up the ghost for a programming interview, having i or 2 twelvemonth sense on this field. Whether y'all give-up the ghost for Java developer position, QA, BA, supports professional, projection director or whatsoever other technical position, may interviewer await y'all to answer basic questions from Database together with SQL. It's too obvious that if y'all are working from i or 2 years on whatsoever projection in that place is expert gamble that y'all come upwardly across to handgrip database, writing SQL queries to insert, update, delete together with pick out records. One unproblematic but effective agency to banking concern check candidate's SQL science is yesteryear bespeak these types of unproblematic query. They are are neither really complex nor really big, but even so they encompass all cardinal concept a programmer should know virtually SQL.
These queries examine your SQL science on Joins, both INNER together with OUTER join, filtering records yesteryear using WHERE together with HAVING clause, grouping records using GROUP BY clause, calculating sum, average together with counting records using aggregate function similar AVG(), SUM() together with COUNT(), searching records using wildcards inwards LIKE operator, searching records inwards a outpouring using BETWEEN together with IN clause, DATE together with TIME queries etc. If y'all direct maintain faced whatsoever interesting SQL query or y'all direct maintain whatsoever occupation together with searching for the solution, y'all tin post service it hither for everyone's benefit. If y'all are looking for to a greater extent than challenging SQL query exercises together with puzzles together with then y'all tin too check Joe Cleko's SQL Puzzles And Answers, i of the best books to actually banking concern check together with ameliorate your SQL skills.
These queries examine your SQL science on Joins, both INNER together with OUTER join, filtering records yesteryear using WHERE together with HAVING clause, grouping records using GROUP BY clause, calculating sum, average together with counting records using aggregate function similar AVG(), SUM() together with COUNT(), searching records using wildcards inwards LIKE operator, searching records inwards a outpouring using BETWEEN together with IN clause, DATE together with TIME queries etc. If y'all direct maintain faced whatsoever interesting SQL query or y'all direct maintain whatsoever occupation together with searching for the solution, y'all tin post service it hither for everyone's benefit. If y'all are looking for to a greater extent than challenging SQL query exercises together with puzzles together with then y'all tin too check Joe Cleko's SQL Puzzles And Answers, i of the best books to actually banking concern check together with ameliorate your SQL skills.
SQL Query Interview Questions together with Answers
Question 1: SQL Query to detect minute highest salary of Employee
Answer: There are many ways to detect minute highest salary of Employee inwards SQL, y'all tin either exercise SQL Join or Subquery to solve this problem. Here is SQL query using Subquery:
select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
See How to detect minute highest salary inwards SQL for to a greater extent than ways to solve this problem.
Question 2: SQL Query to detect Max Salary from each department.
Answer: You tin detect the maximum salary for each subdivision yesteryear grouping all records yesteryear DeptId together with and then using MAX() component to calculate maximum salary inwards each grouping or each department.
SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.
These questions give-up the ghost to a greater extent than interesting if Interviewer volition inquire y'all to impress subdivision get upwardly instead of subdivision id, inwards that case, y'all demand to bring together Employee tabular array alongside Department using unusual cardinal DeptID, brand certain y'all create LEFT or RIGHT OUTER JOIN to include departments without whatsoever employee equally 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, nosotros direct maintain used RIGHT OUTER JOIN because nosotros demand the get upwardly of the subdivision from Department tabular array which is on the right side of JOIN clause, fifty-fifty if in that place is no reference of dept_id on Employee table.
Question 3: Write SQL Query to display the electrical current date.
Answer: SQL has built-in component called GetDate() which returns the electrical current timestamp. This volition piece of work inwards Microsoft SQL Server, other vendors similar Oracle together with MySQL too has equivalent functions.
SELECT GetDate();
Question 4: Write an SQL Query to banking concern check whether appointment passed to Query is the appointment of given format or not.
Answer: SQL has IsDate() component which is used to banking concern check passed value is a appointment or non of specified format, it returns 1(true) or 0(false) accordingly. Remember ISDATE() is an MSSQL component together with it may non piece of work on Oracle, MySQL or whatsoever other database but in that place would last something similar.
SELECT ISDATE('1/08/13') AS "MM/DD/YY";
It volition render 0 because passed appointment is non inwards right format.
Question 5: Write an SQL Query to impress the get upwardly of the distinct employee whose DOB is betwixt 01/01/1960 to 31/12/1975.
Answer: This SQL query is tricky, but y'all tin exercise BETWEEN clause to larn all records whose appointment autumn betwixt 2 dates.
SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;
Question 6: Write an SQL Query detect seat out of employees according to gender whose DOB is betwixt 01/01/1960 to 31/12/1975.
Answer :
SELECT COUNT(*), sexual activity from Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' GROUP BY sex;
Question 7: Write an SQL Query to detect an employee whose Salary is equal or greater than 10000.
Answer :
SELECT EmpName FROM Employees WHERE Salary>=10000;
Question 8: Write an SQL Query to detect get upwardly of employee whose get upwardly Start alongside ‘M’
Answer :
SELECT * FROM Employees WHERE EmpName like 'M%';
Question 9: detect all Employee records containing the give-and-take "Joe", regardless of whether it was stored equally JOE, Joe, or joe.
Answer :
SELECT * from Employees WHERE UPPER(EmpName) like '%JOE%';
Question 10: Write an SQL Query to find the year from date.
Answer: Here is how y'all tin detect Year from a Date inwards SQL Server 2008
SELECT YEAR(GETDATE()) as "Year";
Question 11: Write SQL Query to detect duplicate rows inwards a database? together with and then write SQL query to delete them?
Answer: You tin exercise the next query to pick out 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 tabular array which contains 2 column Student together with Marks, y'all demand to detect all the students, whose marks are greater than average marks i.e. listing of higher upwardly average students.
Answer: This query tin last written using subquery equally shown below:
SELECT student, marks from tabular array where marks > SELECT AVG(marks) from table)
Question 13: How create y'all detect all employees which are too manager? .
You direct maintain given a measure employee tabular array alongside an additional column mgr_id, which contains employee id of the manager.
Answer: You demand to know virtually self-join to solve this problem. In Self Join, y'all tin bring together 2 instances of the same tabular array to detect out additional details equally shown below
SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;
this volition demo employee get upwardly together with director get upwardly inwards 2 column e.g.
get upwardly manager_name
John David
One follow-up is to alter this query to include employees which don't direct maintain a manager. To solve that, instead of using the inner join, only exercise left outer join, this volition too include employees without managers.
Question 14: You direct maintain a composite index of 3 columns, together with y'all entirely render the value of 2 columns inwards WHERE clause of a pick out query? Will Index last used for this operation? For illustration if Index is on EmpId, EmpFirstName, together with EmpSecondName and y'all write query like
SELECT * FROM Employee WHERE EmpId=2 and EmpFirstName='Radhe'
If the given 2 columns are secondary index column together with then the index volition non invoke, but if the given 2 columns comprise the principal index(first column spell creating index) together with then the index volition invoke. In this case, Index volition last used because EmpId and EmpFirstName are principal columns.
Hope this article volition assistance y'all to accept a quick do whenever y'all are going to attend whatsoever interview together with non direct maintain much fourth dimension to give-up the ghost into the deep of each query, but if y'all direct maintain expert fourth dimension to create together with then I advise y'all to read together with solve SQL queries from Joe Celko's SQL Puzzles together with Answers, Second edition, i of the best mass for SQL query lovers together with enthusiastic.
Other Interview Questions posts from Blog
Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners
No comments:
Post a Comment