Interview Questions SQL - amitbhilagude/userfullinks GitHub Wiki

Difference between DELETE and TRUNCATE
1. DELETE is used to delete a specific row.
2. TRUNCATE is used to delete all rows. It is also possible to delete all rows using the DELETE command however it is slower than the TRUNCATE
Subsets of SQL
1. DDL: Data Definition Language used to create schema
2. DML: Data Manipulation Language used for manipulating data.
3. DCL: Data control language used for access control of data
4. DTL: Data transactional language used for transactional data.
Database Management System(DBMS) Types
1. Relational: Relationships in table
2. Hierarchical: nested tree structure
3. Network: Many to many relationships
4. Object-Oriented: Store each object
JOINS in SQL
1. Inner Join
2. Left Join
3. Right Join
4. Full JOin
Difference between CHAR and VARCHAR
1. CHAR is fixed length so if we define Char(10) then it is going to allow a string of length 10 only.
2. VARCHAR is variable-length so if we define varchar(10) then it will allow any length up to 10 char.
Primary Key and Foreign Key
1. Column in the table which has unique record and it is set as Primary Key to managing the relationship with other tables.
2. Link between Parent and Child Table where Parent Table has Primary Kay and Child Table has Foreign key. It will allow you query or avoid deleting records in Parent table.
Constraints: Allow you to limit the datatype
1. Not NULL: It Will make sure a particular column will never allow null value.
2. Unique: It will make sure a particular column will have a unique value
3. Check: It will allow the condition and column value must fall in that range
4. Default: Set the default value when no values are specified
5. Index: Used for indexing that column.
SQL vs SQL Server vs MySQL
1. SQL: Language
2. SQL Server: DBMS
3. MySQL: DMBS
Data Integrity.
1. Accuracy and Consistency of data is called Data Integrity.
Clustered Index vs Non-Cluster Index vs Unique Index
1. Clustered Index
  1. Clustered Index alters actual record gets arrange with B+ Tree data structure
  2. Data retrieval will be faster
  3. One Cluster Index per table needs to be created
2. Non-Clustered Index
  1. Actual Records doesn't get alter but the additional table will be maintained with pointers
  2. Data retrieval will be slower
  3. Allow many cluster index per table
3. Unique Index
  1. Doesn't allow default value.
SQL Query to display the current date: Select GetDate()
What is Denormalisation?
1. It is a flat data structure and incorporates data into a single table.
2. It is opposite to the normalization
Entities and Relationship
1. Entity is a Unit that will have different attributes
2. Relationship is the relationship between the entities. It will be one-to-one, one-to-many, many-to-one and self referencing
Index
1. Performance tuning method to allow faster record retrieval
Advantages of Normalisation
1. Structure approach
2. Avoids Data Duplication and Redundancy
3. Faster query
4. Easy maintain
Difference between DROP and TRUNCATE
1. DROP will drop the table
2. Truncate will remove the rows.
Normalisation Forms
1. 1NF: Each cell should have a unique value. Create separate tables and relationships to avoid this.
2. 2NF: The database should be 1NF and it must have a single primary key.
3. 3NF: The database should be 2NDF and it must not have any functional dependencies.
4. BCNF: after 3NF if you still more normalization due to anomaly to continue so it will be one candidate key.
ACID Property
1. Automicity : Commit all transactions or options to roll back
2. Consistency
3. Isolation
4. Durability
Operators
1. Arithmetic
2. Bitwise
3. Comparision
4. Compound
5. Logical
Cross Join vs Natural Join
1. Cross columns of two tables which we join
2. Natural Join of two tables both have the same columns and data type.
Different Types of Subquery
1. Correlated and Non-Correlated: Inner query is referring out query table then it is correlated else non-correlated.
Query with the name of employee begin with A is like "A%"
Group Function: It returns only one result e.g. AVG, COUNT, MAX, MIN, SUM
Difference between BETWEEN and IN
1. Between has range however IN check value in a set of values like IN(10,20,30)
MERGE: Conditional update. If the row not exists will do INSERT, If the row exists then will do an update.
Difference between HAVING and WHERE
1. HAVING will be applied for Select and always with Group By else it will work as WHERE
2. WHERE will be applied for each type of statement.
Ways to Execute Dynamic SQL
1. Write query with parameter
2. exe
3. sp_executesql
Case Manipulations: Lower, upper, Initcap
SET Operations: UNIONS, INTERSECT and MINUS
ALIAS Command: Give Particular name AS and user in where clause.
Aggregate and Scaler Functions
1. Aggregrate functions are arithmetic function e.g. sum or max
2. Scaler function is return value based on input e.g. Now()
How to fetch alternate record: Use of Mod function e.g Mode(rowno, 2)
Pattern Matching character % for 0 to more and _ for one character
Select Unique records using a distinct keyword.
SQL Vs PL/SQL
1. SQL: Single statement for insert, update, or delete
2. PL/SQL: Called Procedural SQL to need to define a variable and then use these statement of insert or update or delete.
Views
1. Virtual Table with a relationship where we can create the Views
Different types of User-Defined functions
1. Scaler functions
2. Inline table value function
3. Multi statement value functions
Collations
1. Rules on how data can be sorted or compare
Auto Increment
1. Commonly used for Primarykey to auto increment. It uses IDENTITY command
Difference between STUFF or REPLACE
1. STUFF: Replace chars from start to end index
2. REPLACE: Replace substring