SQL Skills Assessment - Data Manipulation
60 Questions
3 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

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?

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

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

    <p>Durability</p> Signup and view all the answers

    What is a common aggregate function used in SQL?

    <p>SUM()</p> Signup and view all the answers

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

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

    Which command is used to start a transaction in SQL?

    <p>BEGIN TRANSACTION</p> Signup and view all the answers

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

    <p>CREATE INDEX</p> Signup and view all the answers

    What is a view in SQL?

    <p>A stored query that can be reused</p> Signup and view all the answers

    How do you update existing data in a SQL table?

    <p>UPDATE</p> Signup and view all the answers

    What is the difference between TRUNCATE and DELETE?

    <p>TRUNCATE removes all rows; DELETE removes specific rows</p> Signup and view all the answers

    What does the GRANT statement do in SQL?

    <p>Grants permissions to a user</p> Signup and view all the answers

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

    <p>BOOL</p> Signup and view all the answers

    What clause is used to filter aggregated results in SQL?

    <p>HAVING</p> Signup and view all the answers

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

    <p>BETWEEN</p> Signup and view all the answers

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

    <p>JOIN</p> Signup and view all the answers

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

    <p>ORDER BY</p> Signup and view all the answers

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

    <p>COUNT()</p> 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?

    <p>FULL JOIN</p> Signup and view all the answers

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

    <p>PRIMARY KEY</p> Signup and view all the answers

    What does an INNER JOIN do in SQL?

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

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

    <p>LEFT JOIN</p> Signup and view all the answers

    What does the UNION operator do in SQL?

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

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

    <p>INTERSECT</p> Signup and view all the answers

    What is the purpose of the LIKE keyword in SQL?

    <p>To perform pattern matching</p> Signup and view all the answers

    Which function returns the current date and time in SQL?

    <p>GETDATE()</p> Signup and view all the answers

    What is a window function in SQL?

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

    What is a temporary table in SQL?

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

    How can you execute a stored procedure in SQL?

    <p>Both B and C</p> Signup and view all the answers

    What is a trigger in SQL?

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

    When does an AFTER INSERT trigger execute?

    <p>After a row is inserted into the table</p> Signup and view all the answers

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

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

    Which constraint ensures that a column contains only unique values?

    <p>UNIQUE</p> Signup and view all the answers

    What is the purpose of query optimization in SQL?

    <p>Both A and B</p> Signup and view all the answers

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

    <p>BACKUP DATABASE</p> Signup and view all the answers

    What is a role in SQL?

    <p>A set of permissions granted to users</p> Signup and view all the answers

    What does the CASE statement do in SQL?

    <p>It allows conditional logic in queries</p> Signup and view all the answers

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

    <p>DROP removes a table, DELETE removes rows</p> Signup and view all the answers

    What is a recursive query in SQL?

    <p>A query that references itself</p> Signup and view all the answers

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

    <p>WITH</p> Signup and view all the answers

    What is the purpose of data normalization in databases?

    <p>To organize the data to reduce redundancy</p> Signup and view all the answers

    What is a CROSS JOIN in SQL?

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

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

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

    Is SQL case-sensitive by default?

    <p>No, SQL is case-insensitive by default</p> Signup and view all the answers

    What is an ACID property in SQL?

    <p>Atomicity, Consistency, Isolation, Durability</p> Signup and view all the answers

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

    <p>THROW</p> Signup and view all the answers

    What is a materialized view in SQL?

    <p>A view that stores the result of the query in the database</p> Signup and view all the answers

    What does the COALESCE() function do in SQL?

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

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

    <p>JSON_EXTRACT()</p> Signup and view all the answers

    How long does data in a temporary table exist?

    <p>Until the session that created it ends</p> Signup and view all the answers

    What happens when an SQL injection attack occurs?

    <p>Unauthorized SQL code is inserted into a query</p> Signup and view all the answers

    Which clause is used to partition a table in SQL?

    <p>PARTITION BY</p> Signup and view all the answers

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

    <p>Handles errors in a transaction</p> Signup and view all the answers

    How do you refresh a materialized view in SQL?

    <p>REFRESH MATERIALIZED VIEW view_name</p> Signup and view all the answers

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

    <p>Improves the readability and maintainability of complex queries.</p> Signup and view all the answers

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

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

    What is the function of the HAVING clause in SQL?

    <p>To filter results after aggregating data with GROUP BY.</p> 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?

    <p>INNER JOIN</p> Signup and view all the answers

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

    <p>A unique sequential number assigned to rows within a partition of a result set.</p> 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.

    Quiz Team

    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.

    More Like This

    The 'Atmation' Quiz
    6 questions

    The 'Atmation' Quiz

    CleverWaterfall avatar
    CleverWaterfall
    SQL Database Management
    279 questions

    SQL Database Management

    CongenialCopernicium avatar
    CongenialCopernicium
    SQL Commands Overview
    14 questions

    SQL Commands Overview

    ExquisitePalladium avatar
    ExquisitePalladium
    SQL Commands and Grouping
    8 questions

    SQL Commands and Grouping

    SupportiveSocialRealism avatar
    SupportiveSocialRealism
    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser