SQL Skills Assessment - Data Manipulation

Podcast Beta

Play an AI-generated podcast conversation about this lesson

Questions and Answers

Which SQL command is used to modify existing records in a table?

INSERT

DELETE

UPDATE (correct)

SELECT

What is a best practice when deleting records in SQL?

Use SELECT * to verify records before deletion.

Utilize a WHERE clause to target specific records. (correct)

Always delete all records to maintain integrity.

Avoid using a WHERE clause.

Which technique can help improve the efficiency of SQL queries?

Using SELECT * for all queries.

Performing joins without considering performance.

Ignoring EXPLAIN command to save time.

Creating indexes on frequently queried columns. (correct)

What does the EXPLAIN command do in SQL?

Analyzes how queries are executed to identify performance issues. Signup and view all the answers

Which property of ACID ensures that completed transactions remain permanent even during failures?

Durability Signup and view all the answers

What is a common aggregate function used in SQL?

SUM() Signup and view all the answers

What does a Common Table Expression (CTE) allow a SQL user to do?

Define temporary result sets for use within a single SQL statement. Signup and view all the answers

Which command is used to start a transaction in SQL?

BEGIN TRANSACTION Signup and view all the answers

Which SQL statement is used to create an index on a table?

CREATE INDEX Signup and view all the answers

What is a view in SQL?

A stored query that can be reused Signup and view all the answers

How do you update existing data in a SQL table?

UPDATE Signup and view all the answers

What is the difference between TRUNCATE and DELETE?

TRUNCATE removes all rows; DELETE removes specific rows Signup and view all the answers

What does the GRANT statement do in SQL?

Grants permissions to a user Signup and view all the answers

Which data type is typically used for storing boolean values in SQL?

BOOL Signup and view all the answers

What clause is used to filter aggregated results in SQL?

HAVING Signup and view all the answers

Which operator is suitable for checking if a value falls within a specified range?

BETWEEN Signup and view all the answers

Which SQL keyword is utilized to combine records from multiple tables based on related columns?

JOIN Signup and view all the answers

What keyword should you use to rearrange the result set of a query in SQL?

ORDER BY Signup and view all the answers

Which function calculates the total number of entries in a specific column within a table?

COUNT() Signup and view all the answers

Which type of join will return all records from both tables, with matched records or NULL where there are no matches?

FULL JOIN Signup and view all the answers

Which constraint ensures that a column value must be unique and cannot contain NULL values?

PRIMARY KEY Signup and view all the answers

What does an INNER JOIN do in SQL?

Returns rows when there is a match in both tables Signup and view all the answers

Which join returns all rows from the left table and the matched rows from the right table?

LEFT JOIN Signup and view all the answers

What does the UNION operator do in SQL?

Combines the result sets of two or more queries, removing duplicates Signup and view all the answers

Which SQL operation returns only rows that appear in both result sets?

INTERSECT Signup and view all the answers

What is the purpose of the LIKE keyword in SQL?

To perform pattern matching Signup and view all the answers

Which function returns the current date and time in SQL?

GETDATE() Signup and view all the answers

What is a window function in SQL?

A function that performs calculations across a set of table rows Signup and view all the answers

What is a temporary table in SQL?

A table created to store data temporarily for a session Signup and view all the answers

How can you execute a stored procedure in SQL?

Both B and C Signup and view all the answers

What is a trigger in SQL?

A function that automatically runs when an event occurs in the database Signup and view all the answers

When does an AFTER INSERT trigger execute?

After a row is inserted into the table Signup and view all the answers

What does the TRY...CATCH block do in SQL?

It attempts to run code and catches errors to prevent query failure Signup and view all the answers

Which constraint ensures that a column contains only unique values?

UNIQUE Signup and view all the answers

What is the purpose of query optimization in SQL?

Both A and B Signup and view all the answers

Which command is used to create a backup of an SQL database?

BACKUP DATABASE Signup and view all the answers

What is a role in SQL?

A set of permissions granted to users Signup and view all the answers

What does the CASE statement do in SQL?

It allows conditional logic in queries Signup and view all the answers

What is the difference between the DROP and DELETE statements in SQL?

DROP removes a table, DELETE removes rows Signup and view all the answers

What is a recursive query in SQL?

A query that references itself Signup and view all the answers

Which keyword is used to create a recursive query in SQL?

WITH Signup and view all the answers

What is the purpose of data normalization in databases?

To organize the data to reduce redundancy Signup and view all the answers

What is a CROSS JOIN in SQL?

A join that returns all possible combinations of rows from two tables Signup and view all the answers

What is the difference between RANK() and DENSE_RANK()?

RANK() skips numbers for tied ranks, while DENSE_RANK() does not Signup and view all the answers

Is SQL case-sensitive by default?

No, SQL is case-insensitive by default Signup and view all the answers

What is an ACID property in SQL?

Atomicity, Consistency, Isolation, Durability Signup and view all the answers

Which function is used to raise a user-defined error in SQL?

THROW Signup and view all the answers

What is a materialized view in SQL?

A view that stores the result of the query in the database Signup and view all the answers

What does the COALESCE() function do in SQL?

Returns the first non-NULL value from a list of expressions Signup and view all the answers

Which function is used to extract data from a JSON column in SQL?

JSON_EXTRACT() Signup and view all the answers

How long does data in a temporary table exist?

Until the session that created it ends Signup and view all the answers

What happens when an SQL injection attack occurs?

Unauthorized SQL code is inserted into a query Signup and view all the answers

Which clause is used to partition a table in SQL?

PARTITION BY Signup and view all the answers

What does the TRY...CATCH block handle in SQL?

Handles errors in a transaction Signup and view all the answers

How do you refresh a materialized view in SQL?

REFRESH MATERIALIZED VIEW view_name Signup and view all the answers

What is the main advantage of using a Common Table Expression (CTE) in SQL?

Improves the readability and maintainability of complex queries. Signup and view all the answers

Which of the following best describes a materialized view in SQL?

A static snapshot of data at a certain point in time, requiring manual refresh. Signup and view all the answers

What is the function of the HAVING clause in SQL?

To filter results after aggregating data with GROUP BY. Signup and view all the answers

Which SQL operation is often used to combine records from multiple tables based on related columns but without including unmatched rows?

INNER JOIN Signup and view all the answers

What does the ROW_NUMBER() window function provide in SQL?

A unique sequential number assigned to rows within a partition of a result set. Signup and view all the answers

Study Notes

SQL Baseline Skills and Areas of Expertise Assessment

Data Manipulation

Definition: Involves the use of SQL commands to insert, update, delete, and retrieve data from databases.
Key Commands:
- SELECT: Retrieve data from one or more tables.
- INSERT: Add new records to a table.
- UPDATE: Modify existing records in a table.
- DELETE: Remove records from a table.
Best Practices:
- Use WHERE clause to target specific records during updates and deletions.
- Verify successful execution using return status.

Query Optimization

Purpose: Improve performance and efficiency of SQL queries.
Techniques:
- Indexing: Create indexes on columns used frequently in SELECT, WHERE, or JOIN clauses to speed up data retrieval.
- EXPLAIN Command: Analyze how queries are executed to identify bottlenecks and optimize.
- *Avoiding SELECT : Specify required columns for more efficient data retrieval.
- Joins Optimization: Use appropriate JOIN types and ensure proper indexing on joined columns.
Common Indicators of Poor Performance: Longer execution times, high resource consumption, and frequent lock contentions.

Transaction Management

Definition: Controls the execution of SQL commands to ensure data reliability and integrity.
Key Concepts:
- ACID Properties:
  - Atomicity: Transactions are all-or-nothing.
  - Consistency: Transactions leave the database in a valid state.
  - Isolation: Concurrent transactions do not interfere with each other.
  - Durability: Completed transactions remain permanent, even during failures.
- Control Commands:
  - BEGIN TRANSACTION: Start a transaction.
  - COMMIT: Save changes made during the transaction.
  - ROLLBACK: Revert changes if errors occur.
Best Practices:
- Keep transactions short to reduce lock contention.
- Use appropriate isolation levels.

Advanced SQL Functions

Aggregate Functions: Perform calculations on a set of values to return a single value.
- Examples: COUNT(), SUM(), AVG(), MAX(), MIN().
Window Functions: Provide calculations across a specified range of rows related to the current row.
- Syntax: FUNCTION() OVER (PARTITION BY column ORDER BY column).
Common Table Expressions (CTEs): Allow defining temporary result sets for use within a single SQL statement.
- Syntax: WITH cte_name AS (SELECT ...).
Subqueries: Queries nested within other queries to provide intermediate results or filter sets.
Dynamic SQL: Constructing and executing SQL statements dynamically within applications or scripts.

This structured overview covers foundational aspects key to a proficient understanding of SQL. Emphasis on practical applications and best practices enhances proficiency in manipulating and managing data effectively.

Data Manipulation

SQL commands allow you to insert, update, delete, and retrieve data from databases.
SELECT retrieves data from one or more tables.
INSERT, UPDATE, and DELETE modify data within tables.
Use WHERE clause to target specific records during updates and deletions.
Verify successful execution using return status.

Query Optimization

Improve performance and efficiency of SQL queries.
Indexing on columns used frequently speeds up data retrieval.
EXPLAIN command analyzes query execution to identify bottlenecks and optimize.
Avoid SELECT * and specify needed columns for efficient data retrieval.
Use appropriate JOIN types and proper indexing on joined columns.
Indicators of poor performance: Longer execution times, high resource consumption, and frequent lock contentions.

Transaction Management

Controls SQL command execution to ensure data reliability and integrity.
ACID Properties ensure data integrity:
- Atomicity: Transactions are all-or-nothing.
- Consistency: Transactions maintain database validity.
- Isolation: Concurrent transactions don't interfere.
- Durability: Completed transactions are permanent.
BEGIN TRANSACTION, COMMIT, and ROLLBACK control transaction execution.
Keep transactions short to reduce lock contention.
Use appropriate isolation levels.

Advanced SQL Functions

Aggregate Functions perform calculations on a dataset, returning a single value.
Examples: COUNT(), SUM(), AVG(), MAX(), MIN().
Window Functions perform calculations across a specified range of rows related to the current row.
Common Table Expressions(CTEs) define temporary result sets for use in a single SQL statement.
Subqueries are nested queries providing intermediate results or filtered data.
Dynamic SQL constructs and executes SQL statements dynamically within applications or scripts.

Basic SQL Syntax

SELECT * is used to select all columns from a table.
AS is used to rename a column in a SQL query.

Data Types

VARCHAR is used to store text data in SQL.
TEXT is best for storing large amounts of text in SQL.

Filtering Data

WHERE is used to filter records in SQL.
BETWEEN is used to test if a value is within a range of values.

Sorting Data

ORDER BY is used to sort the result of a query in SQL.
By default, ORDER BY sorts data in ascending order.

Joins

JOIN is used to combine rows from two or more tables based on a related column.
FULL JOIN returns all rows from both tables, matching where possible, and filling in NULLs where no match is found.

Grouping Data

GROUP BY is used to group rows that have the same values in SQL.
HAVING is used to filter the results after applying GROUP BY.

Aggregate Functions

COUNT() is used to find the total number of rows in a table.
AVG() is used to calculate the average of a numeric column.

Subqueries

A subquery is a query inside another query.
Subqueries can be used in SELECT, WHERE, or FROM clauses.

Constraints

A primary key is a unique identifier for each row.
NOT NULL ensures that a column does not accept NULL values.

Transactions

COMMIT saves all changes made during the current transaction.
ROLLBACK is used to undo changes made during a transaction.

Indexing

An index in a SQL table speeds up queries.
CREATE INDEX is used to create an index on a table.

Views

A view is a stored query that can be reused.
You can use the UPDATE statement, if the view supports updates, to update data through a view.

Data Modification

INSERT INTO is used to insert new data into a table.
UPDATE is used to update existing data in a SQL table.

Deleting Data

DELETE is used to delete rows from a table in SQL.
TRUNCATE removes all rows while DELETE removes specific rows.

Data Security

GRANT grants permissions to a user.
REVOKE is used to revoke a user's permission in SQL.

Joins (Advanced)

INNER JOIN returns rows only when there's a match in both tables.
LEFT JOIN returns all rows from the left table and only matched rows from the right table.
RIGHT JOIN returns all rows from the right table and only matched rows from the left table.
FULL JOIN returns all rows from both tables, including unmatched rows.

Set Operations

UNION combines results of two or more queries, removing duplicates.
INTERSECT returns only rows present in both result sets.
EXCEPT returns rows present in the first result set but not in the second.

String Functions

LIKE is used for pattern matching in SQL queries.
To search for values starting with "A", use WHERE column LIKE 'A%'.

Date Functions

GETDATE() returns the current date and time.
DATEPART(YEAR, date_column) extracts the year from a date column.

Window Functions

Window functions perform calculations across a set of rows.
ROW_NUMBER() assigns a sequential number to each row in the result set.

Temporary Tables

Temporary tables are used to store data temporarily for a session.
They are automatically deleted after the session ends.
Use CREATE TABLE #temp_table to create a temporary table.

Stored Procedures

Stored procedures are pre-compiled SQL statements stored in the database.
They can be executed with EXECUTE procedure_name or CALL procedure_name.

Triggers

Triggers are functions automatically executed when a database event occurs.
AFTER INSERT trigger runs after a row is inserted into the table.

Error Handling

TRY...CATCH block handles errors during query execution.
ERROR_MESSAGE() retrieves the error message within the CATCH block.

Constraints (Advanced)

PRIMARY KEY ensures that all values in a column are unique and not NULL.
UNIQUE constraint only ensures that values in a column are unique.
FOREIGN KEY ensures a column references a primary key in another table.
CHECK constraint ensures a condition is met before inserting or updating data.
A composite key is a PRIMARY KEY consisting of multiple columns.

Query Optimization

Query optimization focuses on minimizing query execution time.
EXPLAIN or EXPLAIN PLAN are tools used to analyze and optimize queries.

Data Integrity

CHECK constraints ensure a condition is met before data is inserted or updated.
A PRIMARY KEY can consist of multiple columns (composite key).

Data Backup and Recovery

BACKUP DATABASE command creates a backup.
RESTORE DATABASE command restores a database from a backup.

Permissions and Roles

Role is a set of permissions granted to users.
REVOKE ROLE command removes a role from a user.

Advanced Queries

CASE statement provides conditional logic within queries.
Use SELECT CASE WHEN condition THEN result ELSE other_result END AS new_column to create a conditional column.

Temporary and Permanent Deletion

DELETE removes rows from a table.
DROP removes a table permanently.
TRUNCATE removes all rows from a table, but doesn't delete the table itself.

Functions and Stored Procedures

Functions return a value; procedures do not.
CREATE PROCEDURE procedure_name AS BEGIN SQL statement END creates a stored procedure.

Recursive Queries

Recursive queries reference themselves, allowing for traversal of hierarchical data.
The WITH RECURSIVE keyword is used to define recursive common table expressions (CTEs).

Data Normalization

Data normalization aims to reduce redundancy and improve data integrity.
The first normal form (1NF) eliminates repeating groups or arrays within a table.

Advanced Joins

A CROSS JOIN produces all possible combinations of rows from two tables.
A SELF JOIN joins a table to itself, allowing comparisons within the same table.

Windowing and Ranking Functions

RANK() skips numbers for tied ranks while DENSE_RANK() does not, causing gaps in ranking.
NTILE() divides rows into equally sized groups based on a user-defined number of tiles.

Case Sensitivity

SQL is generally case-insensitive for keywords and identifiers.
The COLLATE clause can modify case-sensitivity for specific columns or operations.

Transactions (Advanced)

ACID properties: Atomicity (all or nothing), Consistency (data integrity), Isolation (independent operations), Durability (persistent changes).
Isolation prevents data interference between concurrent transactions.

Error Codes and Handling

The ERROR_NUMBER() function returns the error number in a CATCH block.
THROW raises a user-defined error within a stored procedure or trigger.

Functions (Advanced)

CONCAT() concatenates strings by combining them.
COALESCE() returns the first non-NULL value from a list of expressions.

Stored Procedures (Advanced)

Stored procedures can return multiple result sets.
Parameters are passed to stored procedures using the CALL keyword with parameter values.

Views (Advanced)

A materialized view physically stores the query result, improving performance but requiring manual refresh.
The REFRESH MATERIALIZED VIEW command updates a materialized view.

JSON Functions

JSON_EXTRACT() extracts data from a JSON column using JSON path expressions.
JSON path expressions are used to query JSON data stored in a column.

Pivoting and Unpivoting Data

The PIVOT function transforms rows into columns, summarizing data by categories.
The UNPIVOT function converts columns into rows, transforming data into a more granular format.

Temporary vs. Permanent Data

Temporary tables exist only within the session that created them.
Temporary tables are session-specific and cannot be shared between users.

SQL Injection and Security

SQL injection exploits vulnerabilities in database applications to execute unauthorized SQL code.
Using prepared statements prevents SQL injection by separating the SQL code from user input.

Partitioning Data

Partitioning divides large tables into smaller, manageable partitions based on specific criteria.
The PARTITION BY clause defines the partitioning scheme for a table.

Advanced Error Handling

The TRY...CATCH block handles errors within a transaction, providing a way to control error scenarios.
The THROW statement re-throws an error within a CATCH block, allowing further error handling.

SQL Syntax and Clauses

SELECT: Retrieves data from a database table.
FROM: Specifies the table from which data is to be retrieved.
WHERE: Filters data based on specified conditions.
ORDER BY: Sorts the result set in ascending or descending order.
GROUP BY: Groups rows with the same value in a specified column.
HAVING: Filters the grouped data further based on conditions.

Data Types

Numeric: Store numerical values (e.g., INT, DECIMAL, FLOAT).
Character: Store alphanumeric data (e.g., VARCHAR, CHAR, TEXT).
Date and Time: Store dates and times (e.g., DATE, TIME, DATETIME).
Boolean: Store True or False values (e.g., BIT).

Filtering Data with WHERE

Uses comparison operators (>, <, =, !=, >=, <=) to filter data based on criteria.
Examples:
- WHERE age > 18 (Retrieve data where age is greater than 18).
- WHERE city = 'New York' (Retrieve data where city is equal to 'New York').

Sorting Data with ORDER BY

Sorts results in ascending (ASC) or descending (DESC) order.
Example:
- ORDER BY name ASC (Sort results in ascending order based on the name column).
- ORDER BY age DESC (Sort results in descending order based on the age column).

Joins

Combine data from multiple tables based on shared columns.

INNER JOIN

Returns rows only when there is a match in both participating tables.

LEFT JOIN

Returns all rows from the left table and matching rows from the right table. Rows from the right table with no match will have NULL values.

RIGHT JOIN

Returns all rows from the right table and matching rows from the left table. Rows from the left table with no match will have NULL values.

FULL JOIN

Returns all rows from both tables, whether there is a match or not. If there is no match, NULL values will be used.

Set Operations

Combine results from multiple queries.

UNION

Combines results from multiple queries, removing duplicate rows.

INTERSECT

Returns rows that are present in both queries involved.

EXCEPT

Returns rows that are present in the first query but not in the second query.

Aggregate Functions

Perform calculations on a set of values.

COUNT

Returns the number of rows in a table.

SUM

Returns the total value of all values in a column.

AVG

Returns the average value of all values in a column.

MIN

Returns the minimum value in a column.

MAX

Returns the maximum value in a column.

GROUP BY Clause

Groups rows with the same values in a specified column, often used with aggregate functions.
Example:
- Select the average age of people in each city:
  SELECT city, AVG(age) FROM people GROUP BY city;

HAVING Clause

Filters results after grouping has been applied, often used with aggregate functions.
Example:
- Find cities where the average age is greater than 30:
  SELECT city, AVG(age) FROM people GROUP BY city HAVING AVG(age) > 30;

Subqueries (Nested Queries)

Queries embedded within another query.
Example:
- Find employees whose salary is greater than the average salary:
  SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Correlated Subqueries

Subqueries that depend on the outer query.
Each row in the outer query causes the subquery to execute.

Window Functions

Perform calculations across a set of rows, providing contextual information.

ROW_NUMBER

Assigns a sequential number to each row within a partition.

RANK

Assigns a rank to each row within a partition, with ties receiving the same rank, and a gap in ranks for subsequent rows.

DENSE_RANK

Similar to RANK, but ties do not create gaps in ranks.

NTILE

Divides the result set into a specified number of groups (tiles).

Common Table Expressions (CTEs)

Named temporary result sets defined within a query.
Improves query readability and performance.
Example:
- Find the top 10 customers by total sales: WITH TopCustomers AS (SELECT customer_id, SUM(amount) AS total_sales FROM orders GROUP BY customer_id ORDER BY total_sales DESC) SELECT * FROM TopCustomers LIMIT 10;

Recursive Queries with CTEs

Queries that repeatedly execute the CTE until a certain condition is met.
Useful for hierarchical data structures.

Temporary Tables

Temporary storage for data within a session.
Not accessible after the session ends.
Example:
- Create a temporary table:
  CREATE TEMPORARY TABLE temp_data (column1 INT, column2 VARCHAR(255));

Views (Creating and Using Views)

Virtual tables based on underlying tables.
Simplify complex queries and enhance security.
Example:
- Create a view:
  CREATE VIEW active_customers AS SELECT * FROM customers WHERE active = 1;
- Use the view:
  SELECT * FROM active_customers;

Materialized Views

Views with precalculated data cached for faster access.
Useful for frequently used queries.

Indexes (Clustered, Non-Clustered)

Improve query performance by creating data structures that store information about the location of data.

Clustered Index

Defines the order in which data is stored in a table.
Each table can only have one clustered index.

Non-Clustered Index

Creates a separate structure that points to the location of data.
Allows multiple non-clustered indexes per table.

Transactions and ACID Properties

Used to ensure data integrity and consistency.
ACID Properties:
- Atomicity: All operations within a transaction are executed as a single unit, either fully committed or rolled back.
- Consistency: Data remains in a valid state before and after a transaction.
- Isolation: Concurrent transactions do not interfere with each other.
- Durability: Once a transaction is committed, its changes are permanently stored.

COMMIT and ROLLBACK

COMMIT: Saves the changes made within a transaction.
ROLLBACK: Reverts all changes made within a transaction.

Error Handling with TRY...CATCH

Handles errors that may occur during query execution.
Provides more control over error management.

Stored Procedures

Precompiled blocks of code stored on the server, executed on demand.
Improve performance, security, and reusability.

Functions (Built-in and User-Defined)

Perform specific operations on input values.

Built-in Functions

Predefined functions provided by the database system.

User-Defined Functions

Functions created by users to perform custom tasks.

Triggers (AFTER, BEFORE, INSTEAD OF)

Automatic actions triggered by specific events (e.g., insert, update, delete).

AFTER Trigger

Executes after a specific event occurs.

BEFORE Trigger

Executes before a specific event occurs.

INSTEAD OF Trigger

Replaces a specific operation (e.g., insert, update, delete).

Data Integrity Constraints

Enforce data validity and relationships within a database.

PRIMARY KEY

Uniquely identifies each row in a table.

FOREIGN KEY

References a primary key in another table, enforcing referential integrity.

UNIQUE

Ensures that all values in a column are unique.

CHECK

Verifies that data meets specified conditions.

NOT NULL

Ensures that a column cannot be empty.

Normalization

Process of organizing data into tables to reduce data redundancy and improve data integrity.

1NF (First Normal Form)

Each column contains a single value.

2NF (Second Normal Form)

Meets 1NF and all non-key columns are dependent on the entire primary key.

3NF (Third Normal Form)

Meets 2NF and all non-key columns are dependent only on the primary key, not on any other non-key columns.

BCNF (Boyce-Codd Normal Form)

Meets 3NF and all determinants must be candidate keys (columns that can uniquely identify a row).

Denormalization

The process of introducing redundancy into a database design to improve performance.
Typically done to reduce the number of joins needed to retrieve data.

JOIN Types and Use Cases

INNER JOIN: Used when you only want to retrieve rows that have a match in both tables.
LEFT JOIN: Used when you want to retrieve all rows from the left table and matching rows from the right table.
RIGHT JOIN: Used when you want to retrieve all rows from the right table and matching rows from the left table.
FULL JOIN: Used when you want to retrieve all rows from both tables, regardless of whether there is a match.

Self Joins

Joins a table to itself based on a relationship between columns within the same table.

Cross Joins

Returns all possible combinations of rows from two participating tables.

PIVOT and UNPIVOT

PIVOT: Transposes the result set, turning columns into rows and vice versa.
UNPIVOT: Reverses the effects of PIVOT, transforming rows into columns.

SQL String Functions

CONCAT: Concatenates strings.
UPPER: Converts strings to uppercase.
LOWER: Converts strings to lowercase.
LENGTH: Returns the length of a string.
TRIM: Removes leading and trailing spaces from a string.

SQL Date Functions

GETDATE: Returns the current date and time.
DATEADD: Adds a specified interval to a date.
DATEDIFF: Calculates the difference between two dates.

Permissions and Roles (GRANT, REVOKE)

GRANT: Assigns privileges to users or roles.
REVOKE: Removes privileges from users or roles.

SQL Injection and Security Best Practices

A security vulnerability that allows attackers to inject malicious code into SQL statements.
Preventing SQL Injection:
- Use parameterized queries.
- Validate user inputs.
- Avoid using dynamic SQL (unless absolutely necessary).
- Follow least privilege principles.
Security Best Practices:
- Use strong passwords.
- Encrypt sensitive data.
- Implement proper access controls.
- Regularly audit security logs.

Backup and Recovery (BACKUP, RESTORE)

BACKUP: Creates a copy of a database for recovery purposes.
RESTORE: Recovers a database from a backup.

Partitioning Data (PARTITION BY)

Divides a large table into smaller partitions for better management and performance.
Improves query performance by reducing the amount of data that needs to be scanned.

Full-Text Search

Provides efficient search capabilities for large text fields.
Creates an index on text data, enabling quick searches based on keywords.

JSON Data and Functions (JSON_EXTRACT, JSON_VALUE)

JSON_EXTRACT: Extracts a specific JSON object or array from a JSON string.
JSON_VALUE: Extracts a specific scalar value from a JSON string.

XML Data Handling in SQL

XML Functions: Support working with XML data (e.g., XML.query(), XML.value()).

Data Import and Export (BULK INSERT, OUTFILE)

BULK INSERT: Imports data from a file into a database table.
OUTFILE: Exports data from a database table into a file.

Cursor Operations

Cursors: Enable row-by-row processing of data.
Useful for complex data manipulation tasks that require iteration through a result set.

Dynamic SQL

SQL statements constructed at runtime.
Provides flexibility but requires careful security considerations.

Query Optimization Techniques (EXPLAIN, QUERY PLAN)

EXPLAIN: Analyzes and provides information about the execution plan of a query.
QUERY PLAN: Visual representation of how a database system will execute a query.
Techniques:
- Index appropriately.
- Optimize join order.
- Use appropriate data types.
- Avoid unnecessary operations.

Indexing Strategies

Choose the right indexes for the tables and queries involved.
Consider the type of index needed (clustered, non-clustered).
Avoid creating too many indexes, as they can impact insert and update performance.

Stored Procedures vs. Functions

Stored Procedures: Can be used to perform complex operations or multiple tasks and can return multiple result sets.
Functions: Should be used for more focused operations that return a single value.

Handling NULL Values in SQL

NULL: Represents a missing or unknown value.
Use IS NULL and IS NOT NULL operators to identify NULL values.
Use the COALESCE function to provide a default value if a value is NULL.

Concurrency and Locking

Concurrency: Allowing multiple users to access and modify data simultaneously.
Locking: Mechanism used to prevent data corruption by restricting access to data during modification.

SQL Performance Tuning (Indexes, Execution Plans)

Indexes: Improve query performance by reducing the amount of data that needs to be scanned.
Execution Plans: Visual representation of how a query is executed, helping to identify performance bottlenecks.
Tuning Techniques:
- Use appropriate indexes for columns frequently used in WHERE clauses, JOIN clauses, and ORDER BY.
- Optimize join order, ensuring that joins are performed with smaller tables first.
- Consider using a materialized view for frequently used queries.
- Eliminate unnecessary operations and data conversions.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge on SQL baseline skills, focusing specifically on data manipulation techniques. The quiz covers key commands like SELECT, INSERT, UPDATE, and DELETE, as well as best practices for optimizing queries. Assess your understanding and become proficient in managing databases effectively.