100+ SQL Query Interview Questions With Answers-I

Let’s see, SQL Interview questions and answers or SQL query interview questions and answers.

1. How to select all records from the table?

To select all the records from the table we need to use the following syntax:

 

2. SQL Query to find second highest salary of Employee?

There are many ways to find second highest salary of Employee in SQL, you can either use SQL Join or Subquery to solve this problem. Here is SQL query using Subquery:

 

3. SQL Query to find Max Salary from each department?

You can find the maximum salary for each department by grouping all records by DeptId and then using MAX() function to calculate maximum salary in each group or each department.

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID;

 

 

4. Define join and name the different type of joins?

Join keyword is used to fetch data from related two or more tables. It returns rows where there is at least one match in both the tables included in the join.
Type of joins are-

  1. Outer Join
  2. Inner Join
  3. Equi and Non-Equi Join

 

5. Write SQL Query to display the current date?

SQL has built-in function called GetDate() which returns the current timestamp. This will work in Microsoft SQL Server, other vendors like Oracle and MySQL also has equivalent functions.

SELECT GetDate(); 

 

6. How to Insert Record in Table?

To add the record in a table INSERT syntax is used.

 

 

7. Write an SQL Query to check whether date passed to Query is the date of given format or not?

SQL has IsDate() function which is used to check passed value is a date or not of the specified format, it returns 1(true) or 0(false) accordingly. Remember ISDATE() is an MSSQL function and it may not work on Oracle, MySQL or any other database but there would be something similar.

It will return 0 because passed date is not in correct format.

 

8. Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1990 to 31/12/1995?

This SQL query is tricky, but you can use BETWEEN clause to get all records whose date fall between two dates.

 

9. Write an SQL Query find the number of employees according to gender?

 

10. Write an SQL Query to find the name of an employee whose name Start with ‘A’?

This is the example of Wildcard search in SQL.

 

11. Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?

You can use the following query to select distinct records:

to Delete:

 

12. How do you add a column to a table?

You can use ALTER command to modify the structure of Table in SQL. To add another column in the table following command has been used.

 

13. How can you Delete the Specific record from Table?

Delete command in SQL is used to delete a row or rows from a table based on the specified condition.

To delete the complete Data of a Table.

 

14. There is a table which contains two column Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above average students?

This query can be written using subquery as shown below:

 

15. How do you find all employees which are also manager? 

You need to know about self-join to solve this problem. In Self Join, you can join two instances of the same table to find out additional details as shown below

 

16. What is Difference between Primary Key and Unique Key in SQL?

Primary and Unique key uniquely identifies each row in table but there are some subtle difference between them. here are some of them :

1) Unique key in a table can be null, at-least one but primary key can not be null in any table in relation database like MySQL , Oracle etc.

2) Primary key can be combination of more than one unique keys in same table.

3) There can be only one primary key per table in relation database e.g. MySQL, Oracle or Sybase but there can be more than one unique key per table.

4) Unique key is represented using unique constraint while primary key is created using primary key constraint in any table and it’s automatically gets unique constraint.

5) Many database engine automatically puts clustered index on primary key and since you can only have one clustered index per table, its not available to any other unique key at same time.

 

17. What is Difference between Truncate and Delete command in SQL?

There are many difference between Truncate and Delete command. Please find some below mentioned difference between both –

  1. truncate is fast delete is slow.
  2. truncate doesn’t do logging delete logs on per row basis.
  3. rollback is possible with delete not with truncate until specifically supported by the vendor.
  4. truncate doesn’t fire trigger, delete does.
  5. Don’t delete, truncate it when it comes to purge tables.
  6. truncate reset identity column in table if any, delete doesn’t.
  7. truncate is DDL while delete is DML (use this when you are writing exam)
  8. truncate doesn’t support where clause, delete does.

 

18. What is difference between View and Materialized View in Database or SQL?

What is View :

Views are logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables. Performance of the view depend upon our select query. If we want to improve the performance of view we should avoid to use join statement in our query or if we need multiple joins between table always try to use index based column for joining as we know index based columns are faster than non index based column. View allow to store definition of the query in the database itself.

What is  Materialized View :

Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

 

19. What is difference between UNION vs UNION ALL in SQL?

1) Both UNION and UNION ALL are used to combine result of two separate SQL query, it could be on same table or different table but data should be same. E.g. if product_id is used in two table e.g. Product and Order, then two sql queries which pulls product_id from these two table can be combined using UNION or UNION ALL.

2) Key difference between UNION and UNION ALL is that Union will remove duplicates but Union All will keep them. In another words, UNION is equal to running distinct on output of UNION ALL. For example, if product_id 10 is returned by both of SQL query then it will only appear once if you use UNION and appear twice if you use UNION ALL.

3) Due to above difference query execution time of UNION ALL is smaller than UNION, which means former runs faster than later. So if you want faster output and don’t care of duplicates use UNION ALL.

4) Keep in mind that benefits gained by not removing duplicates can be easily wiped out by transferring more data over a poor bandwidth network connection. That’s why in practice some time UNION ALL appear slower than UNION because it return lot of data with duplicates which require more time to travel from database server to client machine. So evaluate performance of UNION and UNION ALL case by case.

5) Another worth noting thing while using UNION and UNION ALL is that all queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. For example if result of query 1 has three column and result of query 2 has two column then you cannot combine them using UNION command.

 

20. What is a primary key?

A Primary key is column whose values uniquely identify every row in a table. Primary key values can never be reused.

 

21. What are foreign keys?

When a one table’s primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables.
Foreign Key constraints enforce referential integrity.

 

22. Is it possible for a table to have more than one foreign key? 

Yes, a table can have many foreign keys and only one primary key.

 

23. What is Trigger?

Trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table)

 

24. What is a stored procedure? 

A stored procedure is a set of SQL queries which can take input and send back output.

 

25. How to select random rows from a table?

Using SAMPLE clause we can select random rows

 

26. Explain DML and DDL?

DML stands for Data Manipulation Language. INSERT, UPDATE and DELETE  are DML statements.

DDL stands for Data Definition Language. CREATE ,ALTER, DROP, RENAME are DDL statements.

 

27. Give the order of SQL SELECT ?

Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.

 

28. What are the properties of a transaction?

Generally these properties are referred as ACID properties. They are:

  1. Atomicity
  2. Consistency
  3. Isolation
  4. Durability.

 

29. What do you mean by query optimization?

Query optimization is a process in which database system compares different query strategies and select the query with the least cost.

 

30. What is Referential Integrity?

Set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

 

31.  Explain the difference between Rename and Alias?

Rename in SQL is a permanent name given to a table or column whereas Alias in SQL is a temporary name given to a table or column.

 

32. What is the difference between Having clause and Where clause?

Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.
If GROUP BY clause is not used then Having behaved like WHERE clause only.

 

33. Select first 3 characters of FIRST_NAME from EMPLOYEE ?

 

34. Get FIRST_NAME from employee table after removing white spaces from right side

 

35. Get length of FIRST_NAME from employee table

 

36. Get First_Name from employee table after replacing ‘o’ with ‘$’

 

37. Get First_Name and Last_Name as single column from employee table separated by a ‘_’

 

38. Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table

 

39. Get all employee details from the employee table order by First_Name Ascending

 

40. Get all employee details from the employee table order by First_Name Ascending and Salary descending

 

Find More : 100+ SQL Interview Questions with Answers – II

2 Comments
  1. Nice Post 9:13 AM / September 12, 2016 - Reply

    Hey Anshul, Nice effort sir … Thanks for this helpful article ….

  2. Wonderful 3:11 PM / October 13, 2016 - Reply

    Easy, Comprehensive, Informative …. Thanks Buddy

Leave a Reply