SQL Interview Questions | Top 20 - Interview Help - Top Interview Questions

Latest

Interview Help - Top Interview Questions

Top Interview Questions and Tips for a good Interview

Tuesday, August 18, 2020

SQL Interview Questions | Top 20

 


Top 20 SQL Interview Questions

Q1. What is the difference between SQL and MySQL?
  •  SQL is a standard language which stands for Structured Query Language based on the English language. It is the core of the relational database which is used for accessing and managing database.
  •  MySQL is a database management system. MySQL is an RDBMS (Relational Database Management System) such as SQL Server, Informix etc.


Q2. What is Primary key?
  
   A Primary key is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table.
   Only one primary key is there in a table.
   Null values are not allowed.
   Uniquely identifies a single row in the table.


Q3.  What is the difference between DROP, DELETE and TRUNCATE commands?
  •  DROP command removes a table and it cannot be rolled back from the database whereas 
  •  DELETE command removes a row in a table and it also can be rolled back from the database &
  •  TRUNCATE command removes all the rows from the table.

Q4.  What do you mean by “Trigger” in SQL?

    It allows you to execute a batch of code when an insert, update or any other query is 
    executed  against  a specific table.


Q5. How can you select unique/Duplicate records from a table?

   You can select unique/Duplicate records from a table by using the DISTINCT keyword.

  Example : Select DISTINCT EMPID from Employee.

  Using this command, it will print unique emp id from the table employee.



Q6. What are joins in SQL?

   A JOIN clause is used to combine (Merge) rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. 
   
   There are 4 joins in SQL namely:
  • Inner Join : It is used to return all the rows from multiple tables where the join condition is met. it selects intersect values from two tables.
  • Right Join : It is used to return all the rows from the right table but only the matching rows from the left table where the join condition is fulfilled.
  • Left Join  : It is used to return all the rows from the left table but only the matching rows from the right table where the join condition is fulfilled.
   
   Full Join  : Full join returns all the records when there is a match in any of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table.

Q7. What is the difference between cross join and natural join?

    The cross join produces the cross product or Cartesian product of two tables whereas 
    the natural join is based on all the columns having the same name and data types in both the tables.

Q8.  What is Self join?

    Self join is a condition in which a table is joined with itself (Unary relationship), means that each row of the table combined with itself.

Q9. What is the difference between CHAR and VARCHAR2 datatype in SQL?

  •     Char is used for strings of fixed length. 
    For example, char(5) can only store 5 characters and will not be able to store a string of any
    other length.
  • varchar2 is used for character strings of variable length
    For example, varchar2(10) can store any length i.e 6,8,2 in this variable.

Q10. Explain different types of index.

     There are three types of index.

  •  Unique Index : This index does not allow the field to have duplicate values if the column is unique indexed. If a primary key is defined, a unique index can be applied automatically.
  • Clustered Index: This index reorders the physical order of the table and searches based on the basis of key values. Each table can only have one clustered index.
  • Non-Clustered Index: This Index does not alter the physical order of the table and maintains a logical order of the data. Each table can have many nonclustered indexes.

Q11. What is ACID property in a database?

    ACID stands for Atomicity, Consistency, Isolation, Durability.
  •    Atomicity: In it either the entire transaction takes place at once or does not happen at all. 

  •    Consistency: It refers to the correctness of a database.

  •    Isolation: In it transactions occurs independently without any interference.

  •    Durability: Once transaction has been completed its execution, the update & modifications are stored. 

Q12. What are Views?

Views is kind of virtual tables which can either have all rows of a table or specific rows based on certain condition.
  •     Simple View : it contains only single based column.
  •     Complex view : constucted on or more than one base table.
                    For example : Joins, order By, Group By


Q13. What is Normalization?

It reduces redundancy or Duplicacy od data within a database. It basically the process of organizing data to avoid duplication.
     
     Different Types of normalizations : 
  •  First Normal Form (1NF) – No repeating values within rows.
  • Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
  • Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.
      
Q14. What is Denormalization?

It is used to improve the performance of a Database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

Q15. What is a foreign key?

A foreign key is one table which can be related to the primary key of another table. Which means The foreign key in the child table references the primary key in the parent table.

Q16. What is a unique key?
  •      This provides uniqueness for the column or set of columns.
  •      Multiple values allowed per table.
  •      Null values allowed.

Q17. What is an ALIAS command?

ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column.

    Example- Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID

       Here, st refers to alias name for student table and Ex refers to alias name for exam table.

Q18. What is a constraint?

Constraint can be used to specify the limit on the data type of table.

    Sample of constraint are:

  •    NOT NULL: That indicates that the column must have some value and cannot be left NULL.
  •    CHECK :  It ensures whether the value in columns fulfills the specified condition.
  •    DEFAULT : It is used to provide a default value for a column. The default value will be added                         to all new records IF no other value is specified.
  •    UNIQUE : This constraint is used to ensure that each row and column has a unique value and                          no value is being repeated in any other row or column.
  • PRIMARY KEY : This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one column to identify the particular record with a unique identity.
  •  FOREIGN KEY : It is used to ensure the referential integrity of data in the table. It matches the value in one table with another using the PRIMARY KEY.

Q19. What is Deadlock.

Deadlock is a situation where process is waiting for a resource indefenitly which is held by another resource.

Q20 . How many Aggregate functions are available in SQL?

SQL Aggregate functions determine and calculate values from multiple columns in a table 
 and  return a single value.
There are 7 aggregate functions in SQL:

  •      AVG(): Returns the average value from specified columns.
  •      COUNT(): Returns number of table rows.
  •      MAX(): Returns the largest value among the records.
  •      MIN(): Returns smallest value among the records.
  •      SUM(): Returns the sum of specified column values.
  •      FIRST(): Returns the first value.
  •      LAST(): Returns last value.


No comments:

Post a Comment