New members: Get your first 7 days of ITTutorPro Premium for free! Join for free No credit card required.
Menu
New members: Get your first 7 days of ITTutorPro Premium for free! Join for free No credit card required.
SQL (Structured Query Language) is a domain-specific language used for managing, querying, and manipulating relational databases.
SQL databases are relational, while NoSQL databases are non-relational, offering different data modeling and querying approaches.
A primary key uniquely identifies each row in a table, ensuring data integrity and enabling efficient data retrieval.
Normalization is the process of organizing data to minimize redundancy and improve data integrity in a database.
An index is a data structure that enhances query performance by allowing faster data retrieval based on indexed columns.
INNER JOIN returns only matching rows, while LEFT JOIN returns all rows from the left table and matching rows from the right table.
A stored procedure is a precompiled set of SQL statements that can be executed in a database, often used for repetitive tasks and business logic.
A trigger is a set of actions that automatically execute in response to specific database events, such as INSERT, UPDATE, or DELETE operations.
ACID stands for Atomicity, Consistency, Isolation, and Durability, ensuring reliable and consistent database transactions.
SQL injection is a security vulnerability. It can be prevented by using parameterized queries and input validation to protect against malicious input.
The GROUP BY clause groups rows in a result set by specified columns, often used with aggregate functions like SUM or COUNT.
Optimize queries by using indexes, limiting the use of SELECT *, and avoiding unnecessary JOINs or subqueries.
UNION combines and removes duplicates from the result set, while UNION ALL combines all rows without removing duplicates.
A subquery is a query nested within another query. Unlike a JOIN, it can return a single value or row for comparison
Use database management tools like SQL Server Management Studio to perform backups and restores, ensuring data protection and recovery.
The HAVING clause filters results in a GROUP BY query based on aggregate function results, similar to the WHERE clause for individual rows.
Optimize performance by indexing, query optimization, hardware improvements, and monitoring tools to identify and resolve bottlenecks.
A view is a virtual table based on the result of a query. It simplifies complex queries and enhances security by limiting data access.
A self-join is used to join a table to itself. It's useful for hierarchical data or when you need to compare rows within the same table.
A clustered index determines the physical order of data in a table, while a non-clustered index is a separate data structure used for query optimization.
The ROLLBACK statement is used to undo changes made in a transaction and return the database to its previous state.
A database is a container for tables and data, while a schema is a logical container within a database used to organize and group objects.
A foreign key constraint enforces referential integrity by linking a column to the primary key of another table to maintain data consistency.
Use the GROUP BY clause and HAVING clause to identify duplicates by aggregating and filtering records with the same values.
A CTE is a temporary result set that simplifies complex queries. It is often used for recursive queries and to enhance query readability.
The CASE statement is used for conditional logic in SQL queries, allowing you to perform different actions based on specified conditions.
Data integrity ensures data accuracy and consistency by enforcing rules, constraints, and referential integrity, preventing erroneous or invalid data.
A unique key allows one null value but multiple unique values, while a primary key enforces uniqueness and does not allow null values.
The ISNULL function replaces null values with a specified default value in the result set, enhancing data readability.
Triggers are stored procedures that automatically respond to database events. They are useful for auditing, data validation, and automation.
A server-side cursor is processed on the server, while a client-side cursor is processed on the client application, affecting performance and resource usage.
Avoid deadlocks by minimizing transaction duration, using appropriate isolation levels, and handling them with deadlock detection and resolution mechanisms.
The CHECK constraint defines specific conditions that data must meet to be inserted or updated, ensuring data validity and integrity.
An identity column automatically generates unique values. It is created using the IDENTITY property and is often used as a primary key.
A self-contained subquery does not depend on the outer query and can be executed independently. It is often used for complex filtering.
Optimize execution plans by using query hints, indexes, and statistics, and ensuring that SQL Server uses the most efficient plan.
SQL Server Profiler is a tool for monitoring and analyzing SQL Server events and performance, helping diagnose issues and optimize queries.
The MERGE statement performs insert, update, or delete operations in a single query, typically used for data synchronization between tables.
The ROW_NUMBER() function assigns a unique row number to each row in the result set, aiding in pagination and ranking.
A common bottleneck is slow queries. Address it by indexing, query optimization, and reviewing the database schema for design improvements.
A SQL injection attack exploits vulnerabilities by injecting malicious SQL code. Prevent it by using parameterized queries and input validation.
Ensure regular backups, validate backups, test recovery procedures, and document a clear recovery plan for data protection and continuity.
A correlated subquery references columns from the outer query and is executed once for each row in the result set. It's used for row-specific comparisons.
Languages like T-SQL and PL/SQL extend SQL functionality by allowing procedural programming, creating functions, and handling business logic within the database.
The TRUNCATE statement quickly deletes all rows from a table, making it more efficient than DELETE when the entire table needs to be cleared.
Analyze query execution plans, identify bottlenecks, optimize indexes, update statistics, and use query hints for performance improvement.
An inner join returns matching rows from both tables, while an outer join returns all rows from one table and matching rows from the other.
Manage isolation levels using SET TRANSACTION ISOLATION LEVEL to control data consistency and concurrency, ensuring transaction behavior meets specific requirements.
The ORDER BY clause is used to sort query results based on specified columns in ascending or descending order, facilitating data presentation.
A temporary table is a table that exists for a short duration. It is often used for storing and manipulating intermediate query results.
