Quick SQL Interview Questions

Question 1. What is the difference between group by and order by commands in SQL

 Answer 1.Group by group the table into a number of sub groups. Order by operates on the whole of the table.

Question 2. What are the different cardinalities?

Answer 2. One to one , One to many , Many to one  and Many to many.

 Question 3. What are the limitations of normalisation?

 Answer 3.   Full normalization may not be always desirable and adverse effect in retrieval and updation

Question 4. Consider a database of students and the courses they take. Discuss the normalization required on the following table and its need.

 stud_course(stud_id ,course_id,course_fee)

 Assume that any number of students can take any number of courses.

Answer 4. StudentCoursemappingTable(stud_id,course_id) ,CourseMasterTable(course_id,course_fee)

Question 5. What is the like keyword used for?

Answer 5.   It is used to check similarity of strings

Question 6. What is the IN operator used to check for?

Answer 6.   It is used to check if a value belongs to a set of values.

Question 7. Entity- relationship model is a special type of _________.

Answer 7. Object based logical model.

Question 8. What is the difference between inner join and outer join?

Answer 8. An inner join between two or more tables is the cartesian product that satisfies the join condition in the WHERE clause. Whereas outer join retrives those values which are matched and unmatched by WHERE clause.

  

Question 9. Define 2nd normal form?

Answer 9.  A relation is said to be 2nd normal form if it is infirst normal form and every non-prime attribute is fully funtionally dependent on a key.

Question 10. Can you drop constraints using drop commnd?

Answer 10. No, to do that we have to alter the table and drop constraints.

Question 11. List the DCL commands?

Answer 11.

1. Grant: to grant access to a user on a database object(table) toperform only certain operations. 
2. Revoke : to take back the access.
3. Commit: to mark the end of a transaction. writes into the data file.
4. Rollback:- to go back to the original consistant state

Question 12. List the aggregate functions?

Answer 12.   sum, avg, min, max,varience, count, stddev


Question 13. List the problems of concurrency

Answer 13.

• Loss of update
• Dirty read
• Incorrect summary
• Phantom record

Question 14. What is the difference between the usage of the aggregate function 'COUNT' when we say, (i)select count(*) from and select count(field_name) from ?

Answer 14.   In case of (i), Count(*) will count the total number of rows including those with NULL in some columns.

In case of (ii), Count(col_name) will count those rows where the value of col_name is not null.

Question 15. What is the difference between DROP and DELETE commands?

Answer 15.

DROP - Is a DDL Command and once it is executed, the structure and data in the table is permanently lost.

DELETE - Is a DML Command and delete will remove the records or rows in a table based on specified conditions.

Question 16. What is Cardinality ?
Answer 16.   Cardinality - The cardinality of a relationship is NOT the count of number of records in a table but it is the way the relationship exists in the schema. For instance, a 1:n cardinality refers to the fact that for every 1 entry in the first table, the second table can have n entries.


Question 17. Difference between UNION & UNION ALL

Answer 17.

UNION: The result of 2 independent select statements  returns only distinct values.

UNION ALL: This displays all the values including the duplicate values that are Repeated in the table