SQL Interview Question with Answers



 SQL Interview Question with Answers


1. What is the SQL server query execution sequence?

  • FROM -> goes to Secondary files via primary file 
  • WHERE -> applies filter condition (non-aggregate column) 
  • SELECT -> dumps data in tempDB system database 
  • GROUP BY -> groups data according to grouping predicate 
  • HAVING -> applies filter condition (aggregate function) 
  • ORDER BY -> sorts data ascending/descending.

2. What is Normalization? 

  • Step by step process to reduce the degree of data redundancy. 
  • Breaking down one big flat table into multiple table based on normalization rules. Optimizing the memory but not in term of performance.
  • Normalization will get rid of insert, update and delete anomalies.
  • Normalization will improve the performance of the delta operation (aka. DML operation); 
  • UPDATE, INSERT, DELETE 
  • Normalization will reduce the performance of the read operation; SELECT

3. What are the three degrees of normalization and how is normalization done in each degree?

  • 1NF: 

    • A table is in 1NF when: All the attributes are single-valued. 
    • With no repeating columns (in other words, there cannot be two different columns with the same information). 
    • With no repeating rows (in other words, the table must have a primary key).
    • All the composite attributes are broken down into its minimal component.
    • There should be SOME (full, partial, or transitive) kind of functional dependencies between non-key and key attributes. 
    • 99% of times, it’s usually 1NF.

 

  • 2NF:

    • A table is in 2NF when: 
    • It is in 1NF. 
    • There should not be any partial dependencies so they must be removed if they exist.

 

  • 3NF: 

    • A table is in 3NF when: It is in 2NF
    • There should not be any transitive dependencies so they must be removed if they exist. 


BCNF: 

  • A stronger form of 3NF so it is also known as 3.5NF 
  • We do not need to know much about it. Just know that here you compare between a prime attribute and a prime attribute and a non-key attribute and a non-key attribute.

4. What are the different database objects ? 

There are total seven database objects (6 permanent database object + 1 temporary database object) Permanent DB objects 
  • Table 
  • Views 
  • Stored procedures 
  • User-defined Functions 
  • Triggers 
  • Indexes Temporary DB object 
  • Cursors 

5. What is collation? 

Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters. ASCII value can be used to compare these character data.

6. What is a constraint and what are the seven constraints?

Constraint: something that limits the flow in a database. 
○ 1. Primary key
○ 2. Foreign key 
○ 3. Check ■ Ex: check if the salary of employees is over 40,000 
○ 4. Default ■ Ex: If the salary of an employee is missing, place it with the default value. 
○ 5. Nullability ■ NULL or NOT NULL 
○ 6. Unique Key 
○ 7. Surrogate Key ■ mainly used in data warehouse 

7. What is a Surrogate Key ? 

‘Surrogate’ means ‘Substitute’.
Surrogate key is always implemented with a help of an identity column.
Identity column is a column in which the value are automatically generated by a SQL Server based on the seed value and incremental value.
Identity columns are ALWAYS INT, which means surrogate keys must be INT. Identity columns cannot have any NULL and cannot have repeated values. Surrogate key is a logical key. 

8. What is a derived column , hows does it work , how it affects the performance of a database and how can it be improved? 

The Derived Column a new column that is generated on the fly by applying expressions to transformation input columns. 
Ex: FirstName + ‘ ‘ + LastName AS ‘Full name’ Derived column affect the performances of the data base due to the creation of a temporary new column. Execution plan can save the new column to have better performance next time.

9. What is a Transaction? 

○ It is a set of TSQL statement that must be executed together as a single logical unit. 
○ Has ACID properties: 

  • Atomicity: Transactions on the DB should be all or nothing. So transactions make sure that any operations in the transaction happen or none of them do.
  • Consistency: Values inside the DB should be consistent with the constraints and integrity of the DB before and after a transaction has completed or failed.

 

  • Isolation: Ensures that each transaction is separated from any other transaction occurring on the system. 

 

  • Durability: After successfully being committed to the RDMBS system the transaction will not be lost in the event of a system failure or error.

Actions performed on explicit transaction:

  • BEGIN TRANSACTION: marks the starting point of an explicit transaction for a connection. 
  • COMMIT TRANSACTION (transaction ends): used to end an transaction successfully if no errors were encountered. All DML changes made in the transaction become permanent.
  • ROLLBACK TRANSACTION (transaction ends): used to erase a transaction which errors are encountered. All DML changes made in the transaction are undone.
  • SAVE TRANSACTION (transaction is still active): sets a savepoint in a transaction. If we roll back, we can only rollback to the most recent savepoint. Only one save point is possible per transaction. However, if you nest Transactions within a Master Trans, you may put Save points in each nested Tran. That is how you create more than one Save point in a Master Transaction.

10. What are the differences between OLTP and OLAP? 

  • OLTP stands for Online Transactional Processing
  • OLAP stands for Online Analytical Processing 

OLTP:

  • Normalization Level: highly normalized Data Usage : Current Data (Database) Processing : fast for delta operations (DML) Operation : Delta operation (update, insert, delete) aka DML Terms Used : table, columns and relationships

OLAP: 

  • Normalization Level: highly denormalized Data Usage : historical Data (Data warehouse) Processing : fast for read operations Operation : read operation (select) Terms Used : dimension table, fact table

No comments:
Write comments

Please do not enter spam links

Meet US

Services

More Services