SQL Interview Questions - part2


Ques.26. What is the difference between delete, truncate and drop command?
Ans. The difference between the Delete, Truncate and Drop command is -

  • Delete command is a DML command, it removes rows from table based on the condition specified in the where clause, being a DML statement we can rollback changes made by delete command.
  • Truncate is a DDL command, it removes all the rows from table and also frees the space held unlike delete command. It takes lock on the table while delete command takes lock on rows of table.
  • Drop is a DDL command, it removes the complete data along with the table structure(unlike truncate command that removes only the rows).

Ques.27. What are the different types of joins in SQL?
Ans. Joins are used to combine records from multiple tables. The different types of joins in SQL are-

  1. Inner Join - To fetch rows from two tables having matching data in the specified columns of both the tables.
    SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
    

  2. Left Join - To fetch all rows from left table and matching rows of the right table
    SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
    

  3. Right Join - To fetch all rows from right table and matching rows of the left table
    SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
    

  4. Full Outer Join - To fetch all rows of left table and all rows of right table
    SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
    

  5. Self Join - Joining a table to itself, for referencing its own data
    SELECT * FROM TABLE1 T1, TABLE1 T2 WHERE T1.columnA = T2.columnB;
    


Ques.28. What is the difference between cross join and full outer join?
Ans. A cross join returns cartesian product of the two tables, so there is no condition or on clause as each row of tabelA is joined with each row of tableB whereas a full outer join will join the two tables on the basis of condition specified in the on clause and for the records not satisfying the condition null value is placed in the join result.


Ques.29. What are difference between having and where clause?
Ans. A 'where' clause is used to fetch data from database that specifies a particular criteria (specified after the where clause). Whereas a 'having' clause is used along with 'groupby' to fetch data that meets a particular criteria specified by the aggregate function. For example-

Emp_Project
Employee Project
A P1
B P2
C P3
B P3

In the above table if we want to fetch Employee working in project P2, we will use 'where' clause-

1
Select Employee from Emp_Project wh2ere Project = P2;

Now if we want to fetch Employee who is working on more than one project, for this we will first have to group the Employee column along with count of project and than the 'having' clause can be used to fetch relevant records-

1
Select Employee from Emp_Project groupby Employee having count(Project)>1;

Ques.30. What is the difference between Union and Union All command?
Ans. The fundamental difference between Union and Union All command is, Union is by default distinct i.e. it combines the distinct result set of two or more select statements. Whereas, Union combines all the rows including duplicates in the result set of different select statements.


Ques.31. Define the select into statement.
Ans. Select into statement is used to directly select data from one table and insert into other, the new table gets created with same name and type as of the old table-

1
SELECT * INTO newtable FROM oldTable;

Ques.32. What is a View in SQL?
Ans. A view is virtual table, it is a named set of SQL statements which can be later referenced and used as a table.

1
2
3
CREATE VIEW VIEW_NAME AS
SELECT COLUMN1, COLUMN2
FROM  TABLE_NAME WHERE CONDITION;


Ques.33. Can we use 'where' clause with 'groupby'?
Ans. Yes, we can use 'where' clause with 'groupBy'. The rows that doesn't meet the where conditions are removed first and then the grouping is done based on the groupby column.

1
2
3
4
SELECT Employee, Count(Project )
FROM Emp_Project
WHERE Employee != 'A'
GROUP BY Project;

Ques.34. What is Database Normalisation?
Ans. Database normalisation is the process of organisation of data in order to reduce the redundancy and anamolies in the database. We have different Normalisation forms in SQL like - First Normal Form, Second Normal Form, Third Normal Form and BCNF.


Ques.35. Explain First Normal Form(1NF).
Ans. According to First Normal Form a column cannot have multiple values, each value in the columns must be atomic.


Ques.36. Explain Second Normal Form(2NF).
Ans. For a table to be considered in Second Normal Form, it must follow 1NF and no column should be dependent on the primary key.


Ques.37. Explain Third Normal Form(3NF).
Ans. For a table to be Third Normla Form, it must follow 2NF and each non-prime attribute must be dependent on primary key of the table.
For each functional dependency X -> Y either-
X should be the super key or Y should be the prime attribute(part of one of the candidate keys) of table


Ques.38. Explain Boyce and Codd Normal Form(BCNF).
Ans. BCNF is the advanced or stricter version of 3NF.
For each functional dependency X -> Y-
X should be the super key


Ques.39. What are transactions in SQL?
Ans. Transaction is a set of operations performed in a logical sequence. It is executed as a whole, if any statement in the transaction fails, the whole transaction is marked as failed and not committed to the database.


Ques.40. What are ACID properties?
Ans. ACID properties refers to the four properties of transactions in SQL-

  1. Atomicity - All the operations in the transaaction are performed as a whole or not performed at all.
  2. Consistency - State of database changes only on successfull committed transaction.
  3. Isolation - Even with concurrent execution of the multiple transactions, the final state of the DB would be same as if transactions got executed sequentially. In otehr words each transaction is isolated from one another.
  4. Durability - Even in the state of crash or power loss the state of committed transaction remain persistent.

Ques.41. What are locks in SQL?
Ans. Locks in SQL are used for maintaining database integrity in case of concurrent execution of same peice of data.


Ques.42. What are the different types of locks in database?
Ans. The different types of locks in database are-

  1. Shared locks - Allows data to be read-only(Select operations), prevents the data to be updated when in shared lock.
  2. Update locks - Applied to resources that can be updated. There can be only one update lock on a data at a time.
  3. Exclusive locks - Used to lock data being modified(INSERT, UPDATE, or DELETE) by one transaction thus ensuring that multiple updates cannot be made to the same resource at the same time.
  4. Intent locks - A notification mechanism usinh which a transaction conveys that intends to acquire lock on data.
  5. Schema locks- Used for operations when schema or structure of the database is required to be updated.
  6. Bulk Update locks - Used in case of bulk operations when the TABLOCK hint is used.

Ques.43. What are aggregate functions in SQL?
Ans. Aggregate functions are the SQL functions which return a single value calculated from multiple values of columns. Some of the aggregate functions in SQL are-

  • Count() - Returns the count of the number of rows returned by the SQL expression
  • Max() - Returns the max value out of the total values
  • Min() - Returns the min value out of the total values
  • Avg() - Returns the average of the total values
  • Sum() - Returns the sum of the values returned by the SQL expression

Ques.44. What are scalar functions in SQL?
Ans. Scalar functions are the functions that return a single value by processing a single value in SQL. Some of the wodely used SQL functions are-

  • UCASE() - USed to convert a string to upper case
  • LCASE() - Used to convert a string to lower case
  • ROUND() - Used to round a number to the decimal places specified
  • NOW() - Used to fetch current system date and time
  • LEN() - Used to find length of a string
  • SUBSTRING() or MID() - MID and SUBSTRING are synonyms in SQL. They are used to extract a substring from a string by specifying the start and end index. Syntax - SUBSTRING(ColumnName,startIndex,EndIndex).
  • LOCATE() - Used to find the index of the character in a string. Syntax - LOCATE(character,ColumnName)
  • LTRIM() - Used to trim spaces from left
  • RTRIM() - Used to trim spaces from right

Ques.45. What is a coalesce function?
Ans. Coalesce function is used to return the first not NULL value out of the multiple values or expressions passed to the coalesce function as parameters.Example-
COALESCE(NULL, NULL, 5, 'ArtOfTesting') will return the value 5.
COALESCE(NULL, NULL, NULL) will return NULL value as no not NULL value is encountered in the parameters list.


Ques.46. What are cursors in SQL?
Ans. Cursors are objects in SQL that are used to traverse the result set of a SQL query one by one.


Ques.47. What are stored procedures? Explain there advanatages?
Ans. Stored procedures are SQL procedures(bunch of SQL statements) that are stored in the database and can be called by other procedures, triggers and other applications.

CREATE PROCEDURE 
procedureName
AS
Begin
     Set of SQL statements
End

The advantages of stored procedure are-

  1. Stored procedures improve performance as the procedures are pre-compiled as well as cached.
  2. Make queries easliy maintanable and reusable as any change is required to be made at single location.
  3. Reduce network usage and traffic.
  4. Improve security as stored procedures restrict direct access to the database.

Ques.48. What are triggers in SQL?
Ans. Triggers are special type of stored procedures that get executed when a specified event occurs. Syntax-

CREATE TRIGGER 
triggerName 
triggerTime{Before or After}
triggerEvent{Insert, Update or Delete}     
ON tableName 
FOR EACH ROW     
triggerBody

Ques.49. What are orphan records?
Ans. Orphan records are the records having foreign key to a parent record which doesn't exist or got deleted.


Ques.50. How can we remove orphan records from a table?
Ans. In order to remove orphan records from database we need to create a join on the parent and child tables and then remove the rows from child table where id IS NULL.

1
2
3
4
5
DELETE PT 
FROM ParentTable PT
LEFT JOIN ChildTable CT 
ON PT.ID = CT.ID 
WHERE PT.ID IS NULL

*Remember: Delete with joins requires name/alias before from clause in order to specify the table of which data is to be deleted.