Download SQL Indexes for Data Analytics and more Exams Computer Science in PDF only on Docsity!
Attributes of a subquery (storage, scope, lifetime, reuse) - Stored in memory Temporary lifetime Deletes at end of query? Single query scope Limited reuseability Benefits of indexes? - Reduces query performance. Improves application performance. Enhances overall user experience. Can a unique index have a clustered or non-clustered index? - Yes, it can have either? Core components of SSRS - View, Create, Manage. Component to process report in different formats. An API for integration. Data Lake vs Data Warehouse - Data Lake: Unstructured data Repository for row data Schema on read Used by data scientists Data Warehouse: Structured Data Optimized for analysis Schema on write Used by business analyst Difference between DELETE and TRUNCATE - The DELETE statement is used to remove specific rows from a table based on a condition. It can be rolled back and generates individual delete operations for each row.
TRUNCATE, on the other hand, is used to remove all rows from a table. It cannot be rolled back, and it is faster than DELETE as it deallocates the data pages instead of logging individual row deletions. Different types of SSRS reports? - Parameterized, Snapshot, Cached, Clickthrough, Drilldown, Drillthrough, Sub-report, Linked report, Ad-hoc Explain the concept of data partitioning - Its the process of dividing a large dataset into smaller pieces called partitions. Used to improve query performance, enable parallel processing, and manage large datasets. Common Strategies: Range, Hash, and List Main architecture components of SSRS? - Report Manager Report Designer Report Server Other servers you can use in SSRS? - Oracle, ODBC, OLE DB, Hyperion, Teradata, Flat XML files What are different kinds of isolation levels? - Rules that control how transactions see and interact with each other's data. READ UNCOMMITTED Allows dirty, non-repeatable, phantom reads Fastest, least safe (NOLOCK) READ COMMITTED (Default) Prevents dirty reads Allows non-repeatable & phantom reads REPEATABLE READ Prevents dirty & non-repeatable reads Allows phantom reads SERIALIZABLE Prevents dirty, non-repeatable, phantom reads
What is a data dictionary? - Automated or manual file storing definitions of data elements and their characteristics. What is a data mark? - A data mart is a subset of a data warehouse. It focuses on specific business line/department. It contains summarized and relevant data for a particular user group or business area. What is a data region in SSRS, and the different kinds? - Rows that shows rows of summarized information from datasets. Types: Chart, Gauge, List, Matrix, Table What is a data warehouse? - A data warehouse is a centralized repository that stores large quantities of structured data. It is designed for query and analysis as opposed to transaction processing. What is a database index? - A structure that helps the database find data faster without scanning every row. What is a dirty read? - Reading uncommited data in a table. What is a filtered index? - Indexes that include subset of rows in a table. It helps reduce size of index. What is a heap table? - A table without an clustered index. The rows are stored in a B-tree hierarchy structure without no particular order. What is a non-clustered index? - Contains copy of indexed columns and pointer to location of data in table. Can have multiple non-clustered indexes in a table. What is a phantom read? - When data that you are using has been changed during another transaction. This results in varying reads of data.
What is a primary key? - It is a type of constraint used to identify each row within a table. No null values are used and only unique values are allowed. Only one primary key can exist in a table. What is a relational database? - A relational database is a type of database that organizes data into tables with predefined relationships. It uses SQL to query and manage the data. What is a star schema? - A star schema is a data warehouse schema where a central fact table is surrounded by dimension tables. What is a sub query? - Sub-query is a query whose return values are used in filtering conditions of the main query. What is a sub report in SSRS? - Reports inserted into the main report. It can take queries passed into it as well as parameters. It acts as an extension of the main report. What is a temporary table? - A table used to store data temporarily. Stored in database Temporary lifetime Deleted at end of session/window Scope allows multiple queries Multiple queries during session What is a unique index? - An index that enforces uniqueness of values in a field or set of fields. What is batch processing? - Batch processing is a method of running high-volume, repetitive data jobs where transactions are collected overtime. Best for large amounts of data. What is data modeling? - A type of process of creating a visual representation of data structures and relationships within a system.
Why do we use SQL transactions? - Transactions ensure data consistencies and integrity. Can make atomic units of work and provide rollback functionality.
