Section1:

Normalization: Normalization is a process of database design that includes organizing and restructuring data in a way to reduce data redundancy, dependency, duplication, and inconsistency.

Denormalization Denormalization is the process opposite of normalization. Denormalization optimizes the performance of the database infrastructure in situations when read operations are more important than write operations

1st NF: If atomicity of the table is 1. Here, atomicity states that a single cell cannot hold multiple values. It must hold only a single-valued attribute. eg: Salary column cannot hold salary as well as month

2nd NF: The second Normal Form applies to relations with composite keys, that is, relations with a primary key composed of two or more attributes. eg: Table1: Student No, Course No, Course Amount has to split into Two Tables: Student Table: Student No, Course No Course Table: Course No,Course Amount

3rd NF: Third normal form, if there is no transitive dependency for non-prime attributes as well as it is in the second normal form. eg: STUDENT (STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_AGE) STATE_COUNTRY (STATE, COUNTRY)

unique Key primary key differences A primary key cannot have any NULL values, while a unique key can have one NULL value.

Unique Key : Will have unique values can have one null value
Primary Key : Unique Key + Not Null
Check: Check for some condition like age> 30
not null: not null
Foreign key: refrencial integirty
default: default value

char and varchar difference char fixed length, varchar variable length. eg: char a(10) = "hello" Length of char is 10 where as varcaher is 5.

TRUNCATE removes all the record from the table at once, whereas the DROP command removes the table or databases and as well as the structure, delete removes data

NON cluster like a index of book, no change in order of storing the data, unique key is non-cluster

5. FUNCTION, Stored proc, triggers, CTE, Transactions, Views, Temp Table, Table var
6. Joins, subQuery,corelated query,
7. Unioun, Unioun All, WIldcard opertor, INTERSECT, Except
8. Partion over Query
9. RANK Query
10. How to optimise the SQL Query
11. Difference b/w a table varialble and temp table.
12. cursors, Live Lock
13. Drop vs Truncate
14. Nested Triggers 25)COALESCE() & ISNULL()?

COALESCE() function returns the first non-null value in a list.
Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times. COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine.

25. Delete Cascade Function
26. Stored Procedure vs functions

Stored procedures are used to perform tranaactions. Stored proecedures can have IN, INOUT and OUT variables. All the database operations insert,update and delete can be performed Exception Handling can be done. Need not return any value. We cannot call procedures from select or having and where clauses.

A function only supports IN parameters. No output parameters are supported. Function can be called from select clause. Functions can't call stored procedures. whereas stored procedures can call functions. Functions should always return some value. Functions cannot perform all database operations in it. Only select is allowed. Functions do not provide exception handling. No transactions

27. Common SQL Command # DDL: CREATE, ALTER TABLE, DROP, TRUNCATE, and ADD COLUMN** **# DML: UPDATE, DELETE, and INSERT** **# DCL: GRANT and REVOKE** **# TCL: COMMIT, SET TRANSACTION, ROLLBACK, and SAVEPOINT** **# DQL: – SELECT**

• What is the difference between local and global variables

Local variables can be accessed only inside the function in which they were declared. Instead, global variables, being declared outside any function, are stored in fixed memory structures and can be used throughout the entire program.

27. Drop, delete truncate differences?
28. How to find the nth highest value in a column of a table?

---

SELECT * FROM table_name ORDER BY column_name DESC LIMIT 1 OFFSET 5;

---

29. How to optimise a stored Procedure?

Create indexes and use sql profiler to find out the query performance. Based on that create the indexes on the columns that are used in joins.

30. Why CTEs?

Before CTEs, my complex queries were becoming monsters – nested subqueries everywhere! Common Table Expressions in SQL have been a game-changer, allowing me to break down those beasts into smaller, more manageable pieces.

But there's more! Here's the real kicker:

In a recent study published by Scopus, one of the largest bibliographic databases for academic literature, researchers highlighted the significance of efficient data retrieval and management practices. This resonates with my belief that mastering Common Table Expressions in SQL is key to unlocking the full potential of database technologies.

31. So, what exactly are CTEs?

Think of them as temporary named result sets you can define within your main SQL query. These virtual tables store intermediate results, making it easier to reference them throughout your code. Multiple CTEs can even be chained together for seriously complex tasks!

Ready to boost your SQL skills? Watch this latest video of mine on common table expression in sql where I'll break down how to use CTEs in different scenarios.

Also Comment and tell

32. What are your biggest challenges with complex SQL queries? Have you tried using CTEs before

33. What is SQL Performance Tuning?

a) using Indexes b) use unioun all instead union c) use select instead of select * d) minimum use of distinct e) dont use != or <> instead use joins f) use limit or top g) avoid multiple joins

PERFORMANCE TUNING SQL

34. How to select random rows from a table?

USING RAND()

SELECT * FROM table_name ORDER BY RAND() LIMIT 5;

35. Explain SQL Query Order of Execution?

36. Triggers? An SQL trigger is a database object that is associated with a table and automatically executes a set of SQL statements when a specific event occurs on that table. Triggers are used to enforce business rules, maintain data integrity, and automate certain actions within a database. They can be triggered by various events, such as inserting, updating, or deleting data in a table, and they allow you to perform additional operations based on those events.

SQL triggers are defined using SQL statements and are associated with a specific table. When the defined trigger event (e.g., INSERT, UPDATE, DELETE) occurs on that table, the associated trigger code is executed automatically. The trigger code can consist of SQL statements that can manipulate data in the same or other tables, enforce constraints, or perform other actions. Triggers are executed within the transaction scope, and they can be defined to execute either before or after the triggering event.