Tuesday, July 1, 2014

SQL Queries Interview Questions



IS NULL
Ternary/Three-valued Logic in SQL
Logical operations in SQL have 3 possible values NOT 2 possible values. What are those 3 possible values? They are TRUE, FALSE, and UNKNOWN.
The equality operator
A comparison to NULL returns UNKNOWN – this is true even when comparing NULL to NULL. The correct way to check for a NULL or a non-NULL column is to use the IS NULL or the IS NOT NULL syntax
SELECT * FROM Computer WHERE laptopModel IS NULL

SQL/Databases: What is a self join?
A self join is basically when a table is joined to itself. We can think a join is performed between two identical copies of that table.

Show each employee’s name, and his/her manager’s name:
SELECT e1.Name, e2.Name FROM Employee e1
INNER JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID

Retrieving unique rows without DISTINCT
SQL/Databases: Retrieving unique rows without DISTINCT
We can actually accomplish this with the GROUP BY keyword. Here’s what the SQL would look like:
SELECT employee_location from employee GROUP BY employee_location

Find Maximum Value WIthout Using Aggregate in SQL
Find Maximum Value WIthout Using Aggregate in SQL
MySQL:
select Numbers from  Compare order by  Numbers DESC LIMIT 1
SQL Server:
select TOP 1  Numbers from Compare order by Numbers DESC

SELECT DISTINCT Numbers FROM Compare
WHERE Numbers NOT IN (
SELECT Smaller.Numbers FROM Compare AS Larger
JOIN Compare AS Smaller ON Smaller.Numbers < Larger.Numbers
)

http://javarevisited.blogspot.sg/2012/12/how-to-find-duplicate-records-in-table-mysql-query-example.html
select name, count(name) as times from contacts group by name, phone having times>1;
Doing calculation using SELECT CLAUSE
select 1+2;

Calling function on SELECT clause e.g. displaying current date
select now();

SELECT data from one row till another row like Paging
select * from Stock order by COMPANY LIMIT 0,2;

http://java67.blogspot.com/2013/04/10-frequently-asked-sql-query-interview-questions-answers-database.html
SQL Query to find second highest salary of Employee
select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

SQL Query to find Max Salary from each department.
SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.

Write SQL Query to display current date.
SQL has built in function called GetDate() which returns current timestamp.
SELECT GetDate();
MySQL:
select NOW();
select LOCALTIME();

Write an SQL Query to check whether date passed to Query is date of given format or not.
SELECT  ISDATE('1/08/13') AS "MM/DD/YY";
No ISDATE function in MySQL

Write a SQL Query to print the name of distinct employee whose DOB is between 01/01/1960 to 31/12/1975.
SELECT DISTINCT EmpName FROM Employees WHERE DOB  BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

Write an SQL Query find number of employees according to gender  whose DOB is between 01/01/1960 to 31/12/1975.
SELECT COUNT(*), sex from Employees  WHERE  DOB BETWEEN ‘01/01/1960 ' AND ‘31/12/1975’  GROUP BY sex;

SELECT * FROM Employees WHERE EmpName like 'M%';

find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.
SELECT  * from Employees  WHERE  upper(EmpName) like upper('joe%');

Write a SQL Query to find  year from date.
SELECT YEAR(GETDATE()) as "Year";
MySQL: SELECT YEAR(NOW()) as "Year"

References
http://java67.blogspot.com/2013/04/10-frequently-asked-sql-query-interview-questions-answers-database.html
http://crackaninterview.com/sql-queries-interview-questions-answers/

Labels

Review (572) System Design (334) System Design - Review (198) Java (189) Coding (75) Interview-System Design (65) Interview (63) Book Notes (59) Coding - Review (59) to-do (45) Linux (43) Knowledge (39) Interview-Java (35) Knowledge - Review (32) Database (31) Design Patterns (31) Big Data (29) Product Architecture (28) MultiThread (27) Soft Skills (27) Concurrency (26) Cracking Code Interview (26) Miscs (25) Distributed (24) OOD Design (24) Google (23) Career (22) Interview - Review (21) Java - Code (21) Operating System (21) Interview Q&A (20) System Design - Practice (20) Tips (19) Algorithm (17) Company - Facebook (17) Security (17) How to Ace Interview (16) Brain Teaser (14) Linux - Shell (14) Redis (14) Testing (14) Tools (14) Code Quality (13) Search (13) Spark (13) Spring (13) Company - LinkedIn (12) How to (12) Interview-Database (12) Interview-Operating System (12) Solr (12) Architecture Principles (11) Resource (10) Amazon (9) Cache (9) Git (9) Interview - MultiThread (9) Scalability (9) Trouble Shooting (9) Web Dev (9) Architecture Model (8) Better Programmer (8) Cassandra (8) Company - Uber (8) Java67 (8) Math (8) OO Design principles (8) SOLID (8) Design (7) Interview Corner (7) JVM (7) Java Basics (7) Kafka (7) Mac (7) Machine Learning (7) NoSQL (7) C++ (6) Chrome (6) File System (6) Highscalability (6) How to Better (6) Network (6) Restful (6) CareerCup (5) Code Review (5) Hash (5) How to Interview (5) JDK Source Code (5) JavaScript (5) Leetcode (5) Must Known (5) Python (5)

Popular Posts