Top SQL Queries for Practice (With Short Answers) - Indexes, Views & Performance (89–100)

 

89. What is an index?
    An index improves query performance.
90. Disadvantage of index?
    Slows down inserts/updates and uses storage.
91. What is a clustered index?
    Sorts and stores table data physically.
92. What is non-clustered index?
    Stores index separately from actual data.
93. What is a view?
    A virtual table based on query.
94. Why use a view?
    For abstraction, security, and reusable logic.
95. What is a stored procedure?
    A precompiled SQL block stored in database.
96. What is a trigger?
    A trigger executes automatically on INSERT/UPDATE/DELETE.
97. How to improve SQL query performance?
    Use indexes, avoid SELECT *, optimize joins, filter early.
98. What is normalization?
    Organizing data to reduce redundancy.
99. What is denormalization?
    Adding redundancy to improve read performance.
100. Scenario: Query is slow, what will you check first?
     Check execution plan, indexes, joins, filters, and data volume.

Joins (36–50)

36. What is a JOIN?
    A JOIN combines rows from multiple tables.
37. What is INNER JOIN?
    Returns matching rows from both tables.
38. What is LEFT JOIN?
    Returns all rows from left table and matching rows from right.
39. What is RIGHT JOIN?
    Returns all rows from right table and matching rows from left.
40. What is FULL OUTER JOIN?
    Returns all matching and non-matching rows from both tables.
41. What is CROSS JOIN?
    Returns Cartesian product of both tables.
42. What is SELF JOIN?
    A table joined with itself.
43. Difference between INNER and LEFT JOIN?
    INNER returns matches only; LEFT returns all left rows.
44. What happens if no match in LEFT JOIN?
    NULL values are returned for right table columns.
45. What is Cartesian product?
    Every row from first table joins with every row from second.
46. Can we join more than 2 tables?
    Yes, multiple joins are allowed.
47. Which join is used to find unmatched records?
    LEFT JOIN with WHERE right_table.id IS NULL.
48. Scenario: Find customers with no orders.
    Use LEFT JOIN between customers and orders, filter NULL orders.
49. Scenario: Get employee with manager name.
    Use SELF JOIN on employee table.
50. Can we join tables without primary key?
    Yes, using common columns. 

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516

Filtering & Sorting (21–35)

 

21. What does WHERE clause do?
    It filters rows based on conditions.
22. Difference between WHERE and HAVING?
    WHERE filters rows before grouping; HAVING filters groups after aggregation.
23. What is ORDER BY?
    It sorts query results in ascending or descending order.
24. Default sort order in SQL?
    Ascending (ASC) by default.
25. What is DISTINCT?
    It removes duplicate values from result.
26. How do you fetch top 5 records?
    SELECT TOP 5 * FROM table; (SQL Server)
27. How do you fetch first 5 rows in MySQL?
    SELECT * FROM table LIMIT 5;
28. What is BETWEEN?
    It filters values within a range.
29. What is IN operator?
    It matches values from a given list.
30. What is LIKE used for?
    It is used for pattern matching.
31. What does % mean in LIKE?
    It matches zero or more characters.
32. What does _ mean in LIKE?
    It matches exactly one character.
33. How do you handle NULL values?
    Use IS NULL or IS NOT NULL.
34. Can we use = NULL?
    No, use IS NULL.
35. What is alias in SQL?
    A temporary name given to a column or table.

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516

Why Indexing ?

What is Index:

Indexes are schema objects, used to locate data quickly without searching every row in a database table. It works similar to book's index page. For example, if I have to look for or locate particular word/data in a book, I will go to the book's index page and get the exact page number to get to the page. Without index page I would have gone through scanning whole book to find out the word. Similarly in database when we have huge data, to search data faster we have Indexes. Indexes do not get created automatically as it depends on user requirement of search criteria and also it has its own disadvantage well.

Why Index:

Indexes allow the database application to speed up searches.

How to create Index:

Indexes can be created using one or more column of a table.
Syntax:
CREATE INDEX <index_name> ON <table_name> <column_name>

Disadvantage:

1. It takes more time to update/Insert/Delete records in a tables with indexes as the indexes also gets updated/deleted/inserted accordingly. You should be careful while creating Index on your table.
2. It require additional space in database.

When to create Indexes:

1. Create indexes on columns (and tables) that will be frequently searched against ( the columns used for filtering or in Where condition )
2. Columns used for Join condition
3. Huge data sets are good for index creation.
4. Tables which has more fields.

Indexes are widely used in various fields, including databases, finance, economics, and more, to efficiently retrieve and organize data. Here are some common types of indexes:

1. Database Indexes:

   • B-Tree Index: A balanced tree structure used in most relational databases for efficient data retrieval. B-tree indexes are suitable for range queries.
   • Hash Index: A data structure that uses a hash function to map keys to locations, making it suitable for exact-match queries.
   • Bitmap Index: Uses bitmap vectors to represent data, especially useful for columns with low cardinality, like gender or boolean values.
   • Spatial Index: Optimizes queries for spatial data, such as geographic coordinates, by organizing data in a way that accelerates location-based queries.

2. Financial Indexes:

   • Stock Market Indexes: Track the performance of a group of stocks or the overall stock market. Examples include the S&P 500, Dow Jones Industrial Average, and NASDAQ Composite.
   • Bond Market Indexes: Measure the performance of a group of bonds or the bond market as a whole, such as the Bloomberg Barclays U.S. Aggregate Bond Index.
   • Commodity Indexes: Reflect the performance of a basket of commodities, like the S&P GSCI (Goldman Sachs Commodity Index).

3. Economic Indexes:

   • Consumer Price Index (CPI): Measures changes in the cost of a basket of goods and services over time, used to assess inflation.
   • Gross Domestic Product (GDP): Measures the economic performance of a country by calculating the total value of goods and services produced within its borders.

4. Search Engine Indexes:

   • Web Search Index: A database of web pages and their content, allowing search engines like Google to retrieve relevant results quickly.
   • Inverted Index: Used in information retrieval systems, it maps keywords or terms to the documents in which they appear, facilitating text-based searches.

5. Library and Catalog Indexes:

   • Library Card Catalog: An index of books and other materials in a library, often organized by title, author, subject, or other criteria.
   • Online Catalogs: Digital indexes of library collections, providing search functionality for users.

6. Geographical Indexes:

   • Geographic Information System (GIS) Indexes: Store and organize spatial data, making it possible to query and analyse geographic information.

7. Stock Market Volatility Index:

   • VIX (CBOE Volatility Index): Measures the market's expectation of future volatility, often referred to as the "fear gauge."

8. Cryptocurrency Indexes:

   • Crypto Market Index: Reflects the performance of various cryptocurrencies, like the CoinMarketCap Cryptocurrency Index.

These are just a few examples of the types of indexes used in different domains. Each type of index serves a specific purpose, from improving data retrieval in databases to tracking economic or financial trends.