1: Basic SQL Questions-

1. What is the purpose of the SQL WHERE clause?

   • Answer: The WHERE clause filters rows in a SQL query based on a specified condition. It is commonly used in SELECT, UPDATE, and DELETE statements.

    sqlCopy code-- Example: Retrieve employees from the 'IT' department
    SELECT * FROM employees WHERE department = 'IT';

1. Explain the difference between CHAR and VARCHAR data types.

   • Answer: CHAR is a fixed-length string, and VARCHAR is a variable-length string. CHAR pads with spaces to the specified length, while VARCHAR only stores the actual characters.

    sqlCopy code-- Example: Creating a table with CHAR and VARCHAR columns
    CREATE TABLE example (
        char_col CHAR(5),
        varchar_col VARCHAR(5)
    );

1. How do you use the ORDER BY clause in a SQL query?

   • Answer: ORDER BY is used to sort the result set. It can be sorted in ascending (ASC) or descending (DESC) order.

    sqlCopy code-- Example: Retrieve products sorted by price in descending order
    SELECT * FROM products ORDER BY price DESC;

1. What is a SQL injection and how can it be prevented?

   • Answer: SQL injection is a malicious technique where an attacker injects SQL code into a query. It can be prevented by using parameterized queries or prepared statements.

    sqlCopy code-- Example: Vulnerable query
    SELECT * FROM users WHERE username = 'user' AND password = 'password';

    -- Example: Using parameterized query (preventing SQL injection)
    SELECT * FROM users WHERE username = ? AND password = ?;

1. Describe the difference between UNION and UNION ALL.

   • Answer: UNION combines result sets and removes duplicates, while UNION ALL combines result sets without removing duplicates.

    sqlCopy code-- Example: Using UNION to get distinct values from two tables
    SELECT column_name FROM table1
    UNION
    SELECT column_name FROM table2;

    -- Example: Using UNION ALL to combine all values from two tables
    SELECT column_name FROM table1
    UNION ALL
    SELECT column_name FROM table2;

1. How can you retrieve unique values from a column using SQL?

   • Answer: Use the DISTINCT keyword to retrieve unique values from a column.

    sqlCopy code-- Example: Retrieve unique department names from employees
    SELECT DISTINCT department FROM employees;

1. Explain the difference between COUNT and COUNT DISTINCT functions.

   • Answer: COUNT counts all rows, while COUNT DISTINCT counts unique values.

    sqlCopy code-- Example: Counting all rows in the 'orders' table
    SELECT COUNT(*) FROM orders;

    -- Example: Counting unique customer IDs in the 'orders' table
    SELECT COUNT(DISTINCT customer_id) FROM orders;

1. What is the purpose of the GROUP_CONCAT function in MySQL?

   • Answer: GROUP_CONCAT is used to concatenate values from multiple rows into a single string.

    sqlCopy code-- Example: Concatenating employee names by department
    SELECT department, GROUP_CONCAT(employee_name) AS employees
    FROM employees
    GROUP BY department;

1. How do you add a new column to an existing table in SQL?

   • Answer: Use the ALTER TABLE statement to add a new column.

    sqlCopy code-- Example: Adding an 'email' column to the 'employees' table
    ALTER TABLE employees
    ADD COLUMN email VARCHAR(255);

Explain the purpose of the COMMIT and ROLLBACK statements.

• Answer: COMMIT is used to save changes made during a transaction, and ROLLBACK is used to undo changes if an error occurs.

sqlCopy code-- Example: Using COMMIT and ROLLBACK in a transaction
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
-- COMMIT; -- to save changes
ROLLBACK; -- to undo changes

2: Querying and Retrieval:

1. Write a query to retrieve the top 5 highest-paid employees.

   • Answer: Use the ORDER BY clause in descending order and LIMIT to retrieve the top 5 highest-paid employees.

    sqlCopy code-- Example: Retrieve the top 5 highest-paid employees
    SELECT * FROM employees
    ORDER BY salary DESC
    LIMIT 5;

1. How can you concatenate two columns in a SELECT statement?

   • Answer: Use the CONCAT function to concatenate two columns in a SELECT statement.

    sqlCopy code-- Example: Concatenate 'first_name' and 'last_name' into a single column 'full_name'
    SELECT CONCAT(first_name, ' ', last_name) AS full_name
    FROM employees;

1. Explain the concept of a self-join in SQL.

   • Answer: A self-join occurs when a table is joined with itself. It is useful for querying hierarchical data or comparing rows within the same table.

    sqlCopy code-- Example: Self-join to find employees and their managers
    SELECT e.employee_id, e.employee_name, m.employee_name AS manager_name
    FROM employees e
    JOIN employees m ON e.manager_id = m.employee_id;

1. Write a query to find duplicate records in a table.

   • Answer: Use GROUP BY and HAVING to identify and filter duplicate records.

    sqlCopy code-- Example: Find duplicate email addresses in the 'users' table
    SELECT email, COUNT(*)
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1;

1. How can you use the LIMIT clause to paginate results?

   • Answer: Use the LIMIT clause with OFFSET to paginate results, specifying the number of rows to skip and the number of rows to return.

    sqlCopy code-- Example: Paginate results, retrieving rows 11 to 20
    SELECT * FROM products
    LIMIT 10 OFFSET 10;

1. What is the purpose of the LIKE operator in SQL?

   • Answer: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

    sqlCopy code-- Example: Retrieve products with 'apple' in the product name
    SELECT * FROM products
    WHERE product_name LIKE '%apple%';

1. Write a query to calculate the total number of orders for each customer.

   • Answer: Use the GROUP BY clause to group results by customer and calculate the total number of orders.

    sqlCopy code-- Example: Calculate total orders for each customer
    SELECT customer_id, COUNT(order_id) AS total_orders
    FROM orders
    GROUP BY customer_id;

1. Explain the concept of subquery nesting in SQL.

   • Answer: Subquery nesting involves placing one or more subqueries within another query to retrieve data. It can be used in SELECT, FROM, WHERE, and HAVING clauses.

    sqlCopy code-- Example: Subquery nesting to find employees with salaries above the average
    SELECT * FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);

1. How do you use the BETWEEN operator in a SQL query?

   • Answer: The BETWEEN operator is used to filter results within a specific range (inclusive).

    sqlCopy code-- Example: Retrieve orders placed between two dates
    SELECT * FROM orders
    WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

Write a query to find the third most common value in a column.

• Answer: Use the ROW_NUMBER() window function to assign ranks to values and filter for the third most common value.

sqlCopy code-- Example: Find the third most common value in the 'category' column
SELECT category, COUNT(*) AS category_count
FROM products
GROUP BY category
ORDER BY category_count DESC
LIMIT 1 OFFSET 2;

3: Joins

1. Explain the CROSS JOIN in SQL with an example.

   • Answer: CROSS JOIN returns the Cartesian product of two tables, generating all possible combinations of rows.

    sqlCopy code-- Example: Cross join to get all combinations of employees and departments
    SELECT employee_name, department_name
    FROM employees
    CROSS JOIN departments;

1. Write a query to retrieve all employees and their managers.

   • Answer: Use a self-join to match employees with their managers based on the manager's ID.

    sqlCopy code-- Example: Retrieve employees and their managers
    SELECT e.employee_name, m.employee_name AS manager_name
    FROM employees e
    LEFT JOIN employees m ON e.manager_id = m.employee_id;

1. What is the difference between an equi-join and a non-equi-join?

   • Answer: An equi-join uses equality (=) in the join condition, while a non-equi-join involves a condition other than equality (e.g., <, >, <=, >=).

    sqlCopy code-- Example: Equi-join
    SELECT * FROM orders
    JOIN customers ON orders.customer_id = customers.customer_id;

    -- Example: Non-equi-join
    SELECT * FROM employees e
    JOIN promotions p ON e.salary BETWEEN p.lower_limit AND p.upper_limit;

1. How do you perform a self-join on a table?

   • Answer: Use an alias to distinguish between the different roles of the same table in a self-join.

    sqlCopy code-- Example: Self-join to find employees and their managers
    SELECT e.employee_name, m.employee_name AS manager_name
    FROM employees e
    JOIN employees m ON e.manager_id = m.employee_id;

1. Explain the concept of a natural join in SQL.

   • Answer: A natural join is based on matching columns with the same name in both tables, implicitly using the equality operator.

    sqlCopy code-- Example: Natural join to get common columns between employees and departments
    SELECT * FROM employees
    NATURAL JOIN departments;

1. Write a query to find the names of employees who do not have a manager.

   • Answer: Use a LEFT JOIN and check for NULL values in the manager-related columns.

    sqlCopy code-- Example: Find employees without a manager
    SELECT employee_name
    FROM employees
    LEFT JOIN employees ON employees.manager_id = employees.employee_id
    WHERE employees.manager_id IS NULL;

1. What is the purpose of the USING clause in a JOIN statement?

   • Answer: The USING clause is used to specify a list of columns for the join condition, avoiding redundancy when column names are the same in both tables.

    sqlCopy code-- Example: Using clause in a join
    SELECT * FROM employees
    JOIN departments USING (department_id);

1. Explain the concept of a theta join in SQL.

   • Answer: A theta join involves using any comparison operator other than equality in the join condition, allowing for more flexible join conditions.

    sqlCopy code-- Example: Theta join to get employees with a salary greater than their manager
    SELECT e.employee_name, m.employee_name AS manager_name
    FROM employees e
    JOIN employees m ON e.manager_id = m.employee_id AND e.salary > m.salary;

1. Write a query to find common records between two tables.

   • Answer: Use a INNER JOIN or a WHERE clause to find common records based on a specific condition.

    sqlCopy code-- Example: Inner join to get common records between employees and projects
    SELECT * FROM employees
    INNER JOIN projects ON employees.employee_id = projects.employee_id;

How do you use the EXISTS keyword in a subquery?

• Answer: EXISTS is used to check for the existence of rows returned by a subquery.

sqlCopy code-- Example: Use EXISTS to find employees who have orders
SELECT employee_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.employee_id = e.employee_id
);

4: Aggregation:

1. Write a query to find the maximum salary in each department.

   • Answer: Use the GROUP BY clause to find the maximum salary for each department.

    sqlCopy code-- Example: Find the maximum salary in each department
    SELECT department, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department;

1. Explain the purpose of the GROUPING SETS clause in SQL.

   • Answer: GROUPING SETS allows grouping by multiple sets of columns in a single query, providing a way to aggregate data at different levels.

    sqlCopy code-- Example: Using GROUPING SETS to get total sales by region and product
    SELECT region, product, SUM(sales) AS total_sales
    FROM sales
    GROUP BY GROUPING SETS ((region, product), (region), ());

1. How do you calculate the median in SQL?

   • Answer: Use the window function ROW_NUMBER() to calculate the median.

    sqlCopy code-- Example: Calculate the median salary
    WITH ranked_salaries AS (
        SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn,
                     COUNT(*) OVER () AS total_rows
        FROM employees
    )
    SELECT AVG(salary) AS median_salary
    FROM ranked_salaries
    WHERE rn = (total_rows + 1) / 2 OR rn = (total_rows + 2) / 2;

1. Write a query to find the department with the highest average salary.

   • Answer: Use the ORDER BY and LIMIT clauses to find the department with the highest average salary.

    sqlCopy code-- Example: Find the department with the highest average salary
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
    ORDER BY avg_salary DESC
    LIMIT 1;

1. Explain the concept of the ROLLUP clause in SQL.

   • Answer: ROLLUP is used for creating subtotals and grand totals in a result set, producing a hierarchy of grouping sets.

    sqlCopy code-- Example: Using ROLLUP to get total sales by region and product
    SELECT region, product, SUM(sales) AS total_sales
    FROM sales
    GROUP BY ROLLUP (region, product);

1. How can you use the GROUP_CONCAT function to concatenate strings?

   • Answer: GROUP_CONCAT is used to concatenate values from multiple rows into a single string.

    sqlCopy code-- Example: Concatenate employee names by department
    SELECT department, GROUP_CONCAT(employee_name) AS employees
    FROM employees
    GROUP BY department;

1. Write a query to find the employee with the second-lowest salary.

   • Answer: Use the ROW_NUMBER() window function to find the second-lowest salary.

    sqlCopy code-- Example: Find the employee with the second-lowest salary
    WITH ranked_salaries AS (
        SELECT employee_name, salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn
        FROM employees
    )
    SELECT employee_name, salary
    FROM ranked_salaries
    WHERE rn = 2;

1. Explain the difference between the GROUP BY and PARTITION BY clauses.

   • Answer: GROUP BY is used for aggregating data at the entire result set level, while PARTITION BY is used with window functions to perform aggregations within partitions of the result set.

    sqlCopy code-- Example: Using PARTITION BY to calculate the average salary by department
    SELECT employee_name, department, salary,
           AVG(salary) OVER (PARTITION BY department) AS avg_salary_by_dept
    FROM employees;

1. How do you use the COALESCE function in SQL?

   • Answer: COALESCE returns the first non-null expression in the list.

    sqlCopy code-- Example: Using COALESCE to handle null values in salary
    SELECT employee_name, COALESCE(salary, 0) AS salary
    FROM employees;

Write a query to calculate the running total of a column.

• Answer: Use the SUM() window function with the ORDER BY clause to calculate the running total.

sqlCopy code-- Example: Calculate the running total of sales
SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS running_total
FROM daily_sales;

5: Subqueries

1. Explain the concept of correlated subqueries.

   • Answer: Correlated subqueries reference columns from the outer query, and the subquery is executed once for each row processed by the outer query.

    sqlCopy codeSELECT employee_name
    FROM employees e
    WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

1. Write a query to find the employees who have a salary higher than their department's average salary.

   • Answer: Use a correlated subquery to compare each employee's salary with their department's average salary.

    sqlCopy codeSELECT employee_name
    FROM employees e
    WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

1. What is the purpose of the ANY and ALL keywords in subqueries?

   • Answer: ANY and ALL are used to compare a value to a set of values returned by a subquery. ANY returns true if any of the subquery results satisfy the condition, while ALL returns true if all subquery results satisfy the condition.

    sqlCopy codeSELECT employee_name
    FROM employees
    WHERE salary > ANY (SELECT salary FROM managers);

1. How do you use the EXISTS operator in a subquery?

   • Answer: EXISTS is used to check if a subquery returns any rows. If at least one row is returned, EXISTS evaluates to true.

    sqlCopy codeSELECT employee_name
    FROM employees e
    WHERE EXISTS (SELECT 1 FROM salaries s WHERE s.employee_id = e.employee_id);

1. Write a query to find the customers who made more than one purchase.

   • Answer: Use a subquery with GROUP BY and HAVING to filter customers who made more than one purchase.

    sqlCopy codeSELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(order_id) > 1;

1. Explain the difference between a scalar subquery and a table subquery.

   • Answer: A scalar subquery returns a single value and can be used wherever an expression is expected, while a table subquery returns multiple rows and can be used as a table in the FROM clause.

    sqlCopy code-- Scalar subquery
    SELECT column_name, (SELECT MAX(salary) FROM employees) AS max_salary
    FROM table;

    -- Table subquery
    SELECT column_name
    FROM (SELECT * FROM employees WHERE department = 'IT') AS it_employees;

1. How can you use the IN operator in a subquery?

   • Answer: The IN operator is used to check if a value matches any value in a set returned by a subquery.

    sqlCopy codeSELECT employee_name
    FROM employees
    WHERE department IN (SELECT department FROM managers);

1. Write a query to find the products with prices higher than the average price.

   • Answer: Use a subquery to calculate the average price and filter products with prices higher than the average.

    sqlCopy codeSELECT product_name, price
    FROM products
    WHERE price > (SELECT AVG(price) FROM products);

1. What is the purpose of the LATERAL keyword in SQL?

   • Answer: LATERAL is used with subqueries to reference columns from preceding tables in the FROM clause, enabling correlated subqueries to reference columns from the preceding tables.

    sqlCopy codeSELECT e.employee_name, m.manager_name
    FROM employees e
    LEFT JOIN LATERAL (
        SELECT manager_name FROM managers WHERE manager_id = e.manager_id
    ) m ON true;

Explain the concept of a common table expression (CTE) in SQL.

• Answer: A CTE is a temporary result set defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It simplifies complex queries and can be referenced multiple times in the same query.

sqlCopy codeWITH cte AS (
    SELECT employee_name, salary
    FROM employees
    WHERE department = 'HR'
)
SELECT * FROM cte;

6: Indexing and Performance

1. How can you optimize a SQL query?

   • Answer: Optimization strategies include creating indexes, avoiding unnecessary joins, using appropriate data types, and optimizing WHERE clauses.

2. Explain the purpose of covering indexes.

   • Answer: Covering indexes include all columns needed for a query, allowing the database engine to satisfy the query without accessing the actual table.

3. What is the difference between a clustered and non-clustered index?

   • Answer: A clustered index determines the physical order of data in a table, while a non-clustered index does not affect the physical order and is stored separately.

4. How do you check the execution plan of a SQL query?

   • Answer: Use the EXPLAIN keyword (in some databases like PostgreSQL) or use tools like SQL Server Management Studio to view the execution plan.

5. Explain the concept of index fragmentation.

   • Answer: Index fragmentation occurs when index pages are out of order, leading to inefficient storage and decreased query performance.

6. How can you use the INDEX hint in SQL?

   • Answer: The INDEX hint is used to suggest the use of a specific index in a query.

    sqlCopy codeSELECT * FROM table1
    INNER JOIN table2 INDEX(index_name) ON table1.column = table2.column;

1. What is the purpose of the FILLFACTOR option in SQL Server indexes?

   • Answer: FILLFACTOR determines the percentage of space on each index page to be filled with data, helping reduce page splits and fragmentation.

2. How do you create an index on multiple columns?

   • Answer: Use the CREATE INDEX statement with a list of columns.

    sqlCopy codeCREATE INDEX index_name ON table_name (column1, column2);

1. Explain the importance of statistics in SQL Server.

   • Answer: Statistics help the query optimizer make informed decisions about the most efficient way to execute a query by providing information about the distribution of data.

2. How can you identify and resolve blocking in a SQL Server database?

   • Answer: Identify blocking using tools like SQL Server Profiler or DMVs, and resolve it by tuning queries, optimizing indexes, and adjusting transaction isolation levels.

7: Constraints

1. How do you enforce data integrity using constraints?

   • Answer: Constraints, such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK, ensure data integrity by defining rules that must be satisfied for data manipulation.

2. What is the purpose of the CHECK constraint?

   • Answer: CHECK ensures that values in a column meet specified conditions.

    sqlCopy codeCREATE TABLE employees (
        salary INT CHECK (salary > 0)
    );

1. How do you add a foreign key constraint to a table?

   • Answer: Use the FOREIGN KEY constraint in the CREATE TABLE statement or ALTER TABLE statement.

    sqlCopy codeCREATE TABLE orders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    );

1. Explain the concept of a composite primary key.

   • Answer: A composite primary key consists of multiple columns, and its values uniquely identify a row.

    sqlCopy codeCREATE TABLE employees (
        department_id INT,
        employee_id INT,
        PRIMARY KEY (department_id, employee_id)
    );

1. What is the CASCADE option in referential integrity?

   • Answer: CASCADE automatically propagates changes to the referencing table when the referenced table is modified.

    sqlCopy codeCREATE TABLE orders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
    );

1. How can you disable a foreign key constraint temporarily?

   • Answer: Use the DISABLE TRIGGER statement or the NOCHECK option with ALTER TABLE.

    sqlCopy codeALTER TABLE table_name NOCHECK CONSTRAINT constraint_name;

1. What is the purpose of the UNIQUE constraint in a column?

   • Answer: UNIQUE ensures that all values in a column are distinct.

    sqlCopy codeCREATE TABLE employees (
        email VARCHAR(255) UNIQUE
    );

1. How do you alter a table to add a NOT NULL constraint?

   • Answer: Use the ALTER TABLE statement with the ADD CONSTRAINT clause.

    sqlCopy codeALTER TABLE employees
    ALTER COLUMN column_name data_type NOT NULL;

1. Explain the concept of a deferred constraint in SQL.

   • Answer: A deferred constraint is checked only at the end of a transaction, allowing temporary violations during the transaction.

    sqlCopy codeSET CONSTRAINTS ALL DEFERRED;

How do you drop a primary key constraint from a table?

• Answer: Use the ALTER TABLE statement with the DROP CONSTRAINT clause.

sqlCopy codeALTER TABLE table_name
DROP CONSTRAINT constraint_name;

8: Transactions

Write a query to rollback a transaction in SQL.

sqlCopy code-- Example: Rollback a transaction
ROLLBACK;

What is the purpose of the SAVEPOINT statement?

The SAVEPOINT statement sets a point within a transaction to which you can later roll back.

sqlCopy code-- Example: Use SAVEPOINT in a transaction
BEGIN TRANSACTION;
INSERT INTO employees (employee_name) VALUES ('John');
SAVEPOINT my_savepoint;
INSERT INTO employees (employee_name) VALUES ('Jane');
ROLLBACK TO my_savepoint;
COMMIT;

How can you set the isolation level for a transaction?

Use the SET TRANSACTION ISOLATION LEVEL statement to set the isolation level for a transaction.

sqlCopy code-- Example: Set the isolation level to SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Explain the concept of optimistic concurrency control.

Optimistic concurrency control allows multiple transactions to access and modify data concurrently. Conflicts are detected at the time of commit.

How do you handle exceptions in a SQL Server transaction?

Use the TRY...CATCH block to handle exceptions in a SQL Server transaction.

sqlCopy code-- Example: Handle exceptions in a transaction
BEGIN TRY
    BEGIN TRANSACTION;
    -- Your SQL statements here
    COMMIT;
END TRY
BEGIN CATCH
    -- Handle exceptions
    ROLLBACK;
END CATCH;

What is a distributed transaction in SQL?

A distributed transaction involves multiple databases and ensures that either all changes are committed or none are.

Explain the concept of a two-phase commit.

The two-phase commit is a protocol used to ensure the atomicity of distributed transactions, involving a prepare phase and a commit phase.

How can you use the SET TRANSACTION ISOLATION LEVEL statement?

Use the SET TRANSACTION ISOLATION LEVEL statement to set the isolation level for a transaction.

sqlCopy code-- Example: Set the isolation level to READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

What is the purpose of the @@TRANCOUNT variable in SQL Server?

@@TRANCOUNT is a system variable that returns the number of open transactions.

How do you use the BEGIN TRANSACTION and COMMIT TRANSACTION statements?

sqlCopy code-- Example: Begin and commit a transaction
BEGIN TRANSACTION;
-- Your SQL statements here
COMMIT;

9: Views:

1. How do you update data through a view in SQL?

   • Answer: In SQL, you can update data through a view by using the UPDATE statement on the underlying base table(s) of the view.

    sqlCopy code-- Example: Update the salary of employees through a view
    UPDATE employees_view
    SET salary = salary * 1.1
    WHERE department = 'IT';

1. What is the purpose of the WITH CHECK OPTION clause in a view?

   • Answer: The WITH CHECK OPTION clause is used to ensure that all data modifications through the view satisfy the conditions specified in the WHERE clause of the view.

    sqlCopy code-- Example: Create a view with CHECK OPTION
    CREATE VIEW high_salary_employees AS
    SELECT * FROM employees
    WHERE salary > 50000
    WITH CHECK OPTION;

1. How can you create an indexed view in SQL Server?

   • Answer: To create an indexed view in SQL Server, you can use the CREATE INDEX statement along with the WITH SCHEMABINDING option on the view.

    sqlCopy code-- Example: Create an indexed view
    CREATE VIEW indexed_view
    WITH SCHEMABINDING
    AS
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department;

    CREATE UNIQUE CLUSTERED INDEX idx_indexed_view
    ON indexed_view (department);

1. Explain the concept of a materialized view.

   • Answer: A materialized view is a database object that stores the result set of a query. Unlike regular views, a materialized view physically persists the data, providing improved performance but may require periodic refreshing.

2. How do you drop a view from a database?

   • Answer: To drop a view from a database, you can use the DROP VIEW statement.

    sqlCopy code-- Example: Drop a view
    DROP VIEW view_name;

1. What is the difference between a view and a stored procedure?

   • Answer: A view is a virtual table based on the result set of a SELECT query, while a stored procedure is a named set of SQL statements that can have input and output parameters, performing a specific task.

2. How can you modify the definition of an existing view?

   • Answer: To modify the definition of an existing view, you can use the CREATE OR REPLACE VIEW statement, which allows you to redefine the view without dropping and recreating it.

    sqlCopy code-- Example: Modify the definition of a view
    CREATE OR REPLACE VIEW employees_view AS
    SELECT employee_id, employee_name FROM employees WHERE department = 'IT';

1. Explain the concept of view resolution in SQL.

   • Answer: View resolution in SQL is the process of determining which base tables and columns are referenced by a view. It involves mapping the column references in the view to the corresponding columns in the underlying tables.

2. How do you grant and revoke permissions on a view?

   • Answer: Permissions on a view can be granted or revoked using the GRANT and REVOKE statements.

    sqlCopy code-- Example: Grant SELECT permission on a view
    GRANT SELECT ON employees_view TO user_name;

    -- Example: Revoke SELECT permission on a view
    REVOKE SELECT ON employees_view FROM user_name;

What is the purpose of the SCHEMABINDING option in a view?

• Answer: The SCHEMABINDING option in a view ensures that the underlying tables or columns referenced by the view cannot be modified or dropped, providing stability to the view's structure.

sqlCopy code-- Example: Create a view with SCHEMABINDING
CREATE VIEW schema_bound_view
WITH SCHEMABINDING
AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

10: Stored Procedures and Functions:

1. How do you pass parameters to a stored procedure in SQL?

   • Answer: Parameters can be passed to a stored procedure in SQL using the IN keyword for input parameters and the OUT or INOUT keywords for output parameters.

    sqlCopy code-- Example: Create a stored procedure with input parameters
    CREATE PROCEDURE get_employee_info(IN employee_id INT)
    AS
    BEGIN
        SELECT * FROM employees WHERE id = employee_id;
    END;

1. Explain the concept of a stored procedure with OUTPUT parameters.

   • Answer: A stored procedure with OUTPUT parameters allows the procedure to return values back to the calling program or script.

    sqlCopy code-- Example: Create a stored procedure with OUTPUT parameter
    CREATE PROCEDURE calculate_tax(IN salary DECIMAL, OUT tax DECIMAL)
    AS
    BEGIN
        SET tax = salary * 0.2;
    END;

1. What is the purpose of the RETURN statement in a stored procedure?

   • Answer: The RETURN statement in a stored procedure is used to return an integer value to the calling program. It indicates the status of the procedure execution.

    sqlCopy code-- Example: Create a stored procedure with RETURN statement
    CREATE PROCEDURE check_salary_range(IN salary DECIMAL)
    AS
    BEGIN
        IF salary > 50000 THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

1. How can you handle errors in a stored procedure?

   • Answer: Errors in a stored procedure can be handled using TRY...CATCH blocks in SQL Server, which allow you to catch and handle exceptions.

    sqlCopy code-- Example: Handle errors in a stored procedure
    CREATE PROCEDURE insert_data(IN data INT)
    AS
    BEGIN
        BEGIN TRY
            -- Code that may cause an error
            INSERT INTO my_table VALUES (data);
        END TRY
        BEGIN CATCH
            -- Code to handle the error
            PRINT 'An error occurred. Rollback.';
            ROLLBACK;
        END CATCH
    END;

1. Write a stored procedure to update the salary of an employee.

   • Answer:

    sqlCopy code-- Example: Create a stored procedure to update employee salary
    CREATE PROCEDURE update_employee_salary(IN employee_id INT, IN new_salary DECIMAL)
    AS
    BEGIN
        UPDATE employees SET salary = new_salary WHERE id = employee_id;
    END;

1. Explain the difference between a scalar function and a table function.

   • Answer: A scalar function returns a single value, while a table function returns a table.

2. How do you create a user-defined function in SQL Server?

   • Answer: Use the CREATE FUNCTION statement to create a user-defined function.

    sqlCopy code-- Example: Create a scalar user-defined function
    CREATE FUNCTION dbo.AddTwoNumbers(@num1 INT, @num2 INT)
    RETURNS INT
    AS
    BEGIN
        RETURN @num1 + @num2;
    END;

1. What is the purpose of the EXECUTE AS clause in a stored procedure?

   • Answer: The EXECUTE AS clause in a stored procedure specifies the security context under which the procedure is executed, allowing control over permissions.

    sqlCopy code-- Example: Create a stored procedure with EXECUTE AS
    CREATE PROCEDURE my_procedure
    WITH EXECUTE AS 'user_name'
    AS
    BEGIN
        -- Procedure logic here
    END;

1. How can you encrypt the definition of a stored procedure?

   • Answer: Use the WITH ENCRYPTION option in the CREATE PROCEDURE statement to encrypt the definition of a stored procedure.

    sqlCopy code-- Example: Create an encrypted stored procedure
    CREATE PROCEDURE encrypted_procedure
    WITH ENCRYPTION
    AS
    BEGIN
        -- Procedure logic here
    END;

How do you drop a stored procedure from a database?

• Answer: Use the DROP PROCEDURE statement to remove a stored procedure from a database.

sqlCopy code-- Example: Drop a stored procedure
DROP PROCEDURE procedure_name;

11: Triggers

What is the purpose of an INSTEAD OF trigger?

• Answer: An INSTEAD OF trigger is used to replace the default action of the triggering statement (INSERT, UPDATE, DELETE) with a user-defined action. It is often used with views to implement complex logic.

How do you disable a trigger temporarily?

• Answer: You can disable a trigger temporarily using the following syntax:

    sqlCopy codeALTER TABLE your_table DISABLE TRIGGER your_trigger;
  

Explain the concept of the INSERTED and DELETED tables in triggers.

• Answer: In triggers, the INSERTED table contains the new values that were inserted or modified, and the DELETED table contains the old values that were deleted or modified. These tables are used in UPDATE and DELETE triggers.

How can you prevent trigger recursion in SQL Server?

• Answer: To prevent trigger recursion, you can use the DISABLE TRIGGER statement within the trigger code before making changes that would otherwise re-trigger the same trigger. Be cautious with this approach, and ensure that your business logic is not affected.

Write a trigger to log changes to a table.

• Answer: Here's an example of a trigger that logs changes to a table:

    sqlCopy codeCREATE TRIGGER LogChangesTrigger
    ON your_table
    AFTER INSERT, UPDATE, DELETE
    AS
    BEGIN
        INSERT INTO ChangeLog (ChangeType, Timestamp, Column1, Column2, ...)
        SELECT
            CASE
                WHEN EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED) THEN 'UPDATE'
                WHEN EXISTS (SELECT * FROM INSERTED) THEN 'INSERT'
                WHEN EXISTS (SELECT * FROM DELETED) THEN 'DELETE'
            END,
            GETDATE(),
            Column1, Column2, ...
        FROM INSERTED
        UNION
        SELECT
            'DELETE', GETDATE(), Column1, Column2, ...
        FROM DELETED;
    END;
  

What is the difference between a DML trigger and a DDL trigger?

• Answer: DML triggers (Data Manipulation Language) respond to data changes (INSERT, UPDATE, DELETE), whereas DDL triggers (Data Definition Language) respond to changes in the database structure, such as CREATE, ALTER, or DROP statements.

How do you view the definition of a trigger in SQL Server?

• Answer: You can view the definition of a trigger using the following query:

    sqlCopy codeSELECT OBJECT_DEFINITION(OBJECT_ID('your_trigger')) AS TriggerDefinition;
  

What is the purpose of the TRIGGER_NESTLEVEL function?

• Answer: The TRIGGER_NESTLEVEL function returns the nesting level of the current trigger. It is useful to determine if a trigger is being executed directly or if it is invoked as a result of another trigger.

How can you create a trigger that fires after an update?

• Answer: To create a trigger that fires after an update, use the AFTER UPDATE clause:

    sqlCopy codeCREATE TRIGGER AfterUpdateTrigger
    ON your_table
    AFTER UPDATE
    AS
    BEGIN
        -- Trigger logic here
    END;
  

Explain the concept of trigger cascading in SQL Server.

• Answer: Trigger cascading refers to the situation where one trigger action causes another trigger to fire. This can result in a chain reaction of trigger executions. Care should be taken to avoid unintended consequences and infinite loops in such scenarios.

12: Advance SQL

Write a query to implement pagination in SQL.

• Answer: To implement pagination, you can use the OFFSET and FETCH clauses in SQL Server:

    sqlCopy codeSELECT * FROM your_table
    ORDER BY your_column
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;
  

What is the purpose of the LEAD and LAG functions in SQL?

• Answer: The LEAD and LAG functions in SQL are used to access data from subsequent or previous rows within the result set. They are useful for comparing values across rows and performing calculations based on the values of neighboring rows.

Explain the concept of a recursive common table expression (CTE).

• Answer: A recursive CTE is used to perform recursive operations, such as walking through hierarchical data or navigating relationships. It includes an anchor member and a recursive member, and the recursion continues until no more rows are produced.

How can you pivot data in SQL?

• Answer: Pivoting data in SQL involves transforming rows into columns. You can use the PIVOT keyword along with aggregation functions to achieve this. Here's a simple example:

    sqlCopy codeSELECT *
    FROM your_table
    PIVOT (
        MAX(column_to_pivot)
        FOR pivot_column IN ([value1], [value2], [value3])
    ) AS PivotTable;
  

Write a query to calculate the moving average of a column.

• Answer: To calculate the moving average, you can use the AVG window function with the OVER clause:

    sqlCopy codeSELECT
        your_column,
        AVG(your_column) OVER (ORDER BY your_order_column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAverage
    FROM your_table;
  

Explain the concept of the MERGE statement in SQL.

• Answer: The MERGE statement in SQL is used to perform multiple operations (INSERT, UPDATE, DELETE) based on a specified condition. It simplifies the implementation of upsert (update or insert) logic.

How do you use the ROW_NUMBER() function in SQL?

• Answer: The ROW_NUMBER() function assigns a unique number to each row within a partition of a result set. Here's an example:

    sqlCopy codeSELECT
        your_columns,
        ROW_NUMBER() OVER (ORDER BY your_order_column) AS RowNum
    FROM your_table;
  

What is the purpose of the OVER() clause in window functions?

• Answer: The OVER() clause defines the window of rows over which a window function operates. It specifies the partitioning and ordering of the result set for the window function.

How can you use the UNPIVOT operation in SQL?

• Answer: The UNPIVOT operation is used to transform columns into rows. Here's an example:

    sqlCopy codeSELECT
        ID,
        Attribute,
        Value
    FROM your_table
    UNPIVOT (
        Value FOR Attribute IN ([Column1], [Column2], [Column3])
    ) AS UnpivotedTable;
  

Write a query to find the top N records in each group.

• Answer: To find the top N records in each group, you can use the ROW_NUMBER() function with the PARTITION BY clause:

    sqlCopy codeSELECT *
    FROM (
        SELECT
            your_columns,
            ROW_NUMBER() OVER (PARTITION BY your_group_column ORDER BY your_order_column) AS RowNum
        FROM your_table
    ) AS Ranked
    WHERE RowNum <= N;