10 SQL Topics

 

SQL Topics: Create Table to Transactions

Table of Contents

  1. Create Table, Insert Data, Fetch Data
  2. Temp Table, Insert Data
  3. Views
  4. GROUP BY, HAVING, ORDER BY
  5. JOINs
  6. Stored Procedures
  7. Functions
  8. Triggers
  9. CTE (Common Table Expression)
  10. Transactions, COMMIT, ROLLBACK, SAVEPOINT

1. Create Table, Insert Data, Fetch Data

Details

Creating a Table

In SQL, tables are created using the CREATE TABLE statement. This defines the structure of the table, including column names, data types, and constraints.

Syntax:

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

Inserting Data

Data is inserted into a table using the INSERT INTO statement.

Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (1, 'John', 'Doe', 'IT', 50000.00);

You can insert multiple rows at once:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES
(2, 'Jane', 'Smith', 'HR', 45000.00),
(3, 'Bob', 'Johnson', 'Finance', 55000.00);

Fetching Data

Data is retrieved from a table using the SELECT statement.

Syntax:

SELECT column1, column2, ...
FROM table_name
[WHERE condition];

Fetch all data:

SELECT * FROM Employees;

Fetch with condition:

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Department = 'IT';

Interview Questions and Answers

Q1: What is the difference between SELECT * and specifying column names?

A: SELECT * retrieves all columns from the table, which can be inefficient if you only need specific columns. Specifying column names improves performance and makes the query more readable.

Q2: How do you insert data into a table with an auto-incrementing primary key?

A: If the primary key is auto-incrementing (e.g., IDENTITY in SQL Server), you can omit it in the INSERT statement:

INSERT INTO Employees (FirstName, LastName, Department, Salary)
VALUES ('Alice', 'Brown', 'Marketing', 48000.00);

Q3: What happens if you try to insert a NULL value into a column with NOT NULL constraint?

A: The INSERT will fail with an error, as the NOT NULL constraint prevents NULL values.

Q4: How can you fetch data in a specific order?

A: Use the ORDER BY clause:

SELECT * FROM Employees ORDER BY Salary DESC;

Q5: Explain the use of WHERE clause in SELECT.

A: The WHERE clause filters rows based on a condition, returning only those that match.

2. Temp Table, Insert Data

Details

Temporary Tables

Temporary tables are used to store intermediate results temporarily. They are automatically dropped when the session ends or can be explicitly dropped. In SQL Server, local temp tables start with #, global with ##.

Syntax:

CREATE TABLE #TempTable (
    column1 datatype,
    column2 datatype,
    ...
);

Example:

CREATE TABLE #TempEmployees (
    EmployeeID INT,
    Name VARCHAR(100),
    Salary DECIMAL(10, 2)
);

Inserting Data into Temp Table

You can insert data directly or from another table.

Insert direct data:

INSERT INTO #TempEmployees (EmployeeID, Name, Salary)
VALUES (1, 'John Doe', 50000.00);

Insert from another table:

INSERT INTO #TempEmployees (EmployeeID, Name, Salary)
SELECT EmployeeID, CONCAT(FirstName, ' ', LastName), Salary
FROM Employees
WHERE Department = 'IT';

Interview Questions and Answers

Q1: What is the difference between a local and global temporary table?

A: Local temp tables (#) are visible only in the current session. Global temp tables (##) are visible to all sessions but are dropped when the last session referencing them ends.

Q2: When should you use temporary tables?

A: For complex queries needing intermediate storage, or when processing large datasets in batches.

Q3: Can you create indexes on temporary tables?

A: Yes, you can create indexes on temp tables to improve performance.

Q4: How do you drop a temporary table?

A: Use DROP TABLE #TempTable;. It's automatically dropped at session end, but explicit drop is good practice.

Q5: What happens if you insert data from a table with more columns than the temp table?

A: You must specify the columns in the INSERT and SELECT to match the temp table's structure.

3. Views

Details

What is a View?

A view is a virtual table based on the result of a SELECT query. It doesn't store data physically but provides a way to simplify complex queries or restrict access to data.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW IT_Employees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Department = 'IT';

Using Views

You can query a view like a regular table:

SELECT * FROM IT_Employees;

Updating Views

Some views are updatable if they meet certain criteria (e.g., no aggregates, GROUP BY, etc.).

Update example:

UPDATE IT_Employees SET Salary = 55000 WHERE EmployeeID = 1;

Interview Questions and Answers

Q1: What are the advantages of using views?

A: Simplify complex queries, provide security by restricting column/row access, and encapsulate logic.

Q2: Can views be indexed?

A: In some databases like SQL Server, you can create indexed views for performance, but they have restrictions.

Q3: What is the difference between a view and a table?

A: A table stores data physically; a view is a virtual representation of data from one or more tables.

Q4: How do you drop a view?

A: DROP VIEW view_name;

Q5: Are views updatable by default?

A: No, only if the view is based on a single table without aggregates or certain clauses.

4. GROUP BY, HAVING, ORDER BY

Details

GROUP BY

Groups rows that have the same values in specified columns. Often used with aggregate functions.

Syntax:

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Example:

SELECT Department, AVG(Salary)
FROM Employees
GROUP BY Department;

HAVING

Filters groups based on a condition, similar to WHERE but for groups.

Syntax:

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

Example:

SELECT Department, AVG(Salary)
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;

ORDER BY

Sorts the result set by one or more columns.

Syntax:

SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Example:

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC, LastName ASC;

Interview Questions and Answers

Q1: What is the difference between WHERE and HAVING?

A: WHERE filters rows before grouping; HAVING filters groups after aggregation.

Q2: Can you use ORDER BY without GROUP BY?

A: Yes, ORDER BY can be used independently to sort results.

Q3: What happens if you select a column not in GROUP BY?

A: It will cause an error unless it's an aggregate function.

Q4: How do you sort by multiple columns?

A: ORDER BY col1, col2 DESC;

Q5: Explain the execution order of clauses.

A: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

5. JOINs

Details

Types of JOINs

  • INNER JOIN: Returns rows with matching values in both tables.
  • LEFT JOIN (LEFT OUTER JOIN): Returns all rows from left table and matching from right; NULL for no match.
  • RIGHT JOIN (RIGHT OUTER JOIN): Opposite of LEFT JOIN.
  • FULL JOIN (FULL OUTER JOIN): Returns all rows from both tables; NULL where no match.
  • SELF JOIN: Joins a table to itself.

Syntax:

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

INNER JOIN Example

SELECT e.FirstName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

LEFT JOIN Example

SELECT e.FirstName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

SELF JOIN Example

SELECT e1.FirstName AS Employee, e2.FirstName AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

Interview Questions and Answers

Q1: What is the difference between INNER JOIN and LEFT JOIN?

A: INNER JOIN returns only matching rows; LEFT JOIN returns all from left plus matches from right.

Q2: When would you use a FULL OUTER JOIN?

A: To get all records from both tables, useful for finding unmatched records.

Q3: Can JOINs be used with more than two tables?

A: Yes, by chaining JOIN clauses.

Q4: What is a Cartesian product?

A: When no JOIN condition is specified, it returns all combinations (CROSS JOIN).

Q5: How do you optimize JOIN queries?

A: Use indexes on join columns, avoid unnecessary columns, and consider query structure.

6. Stored Procedures

Details

What is a Stored Procedure?

A stored procedure is a precompiled collection of SQL statements stored in the database. It can accept parameters and return results.

Syntax:

CREATE PROCEDURE procedure_name (@param1 datatype, @param2 datatype)
AS
BEGIN
    -- SQL statements
END;

Example:

CREATE PROCEDURE GetEmployeeByID (@ID INT)
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @ID;
END;

Executing a Stored Procedure

EXEC GetEmployeeByID @ID = 1;

With Output Parameters

CREATE PROCEDURE GetEmployeeCount (@Dept VARCHAR(50), @Count INT OUTPUT)
AS
BEGIN
    SELECT @Count = COUNT(*) FROM Employees WHERE Department = @Dept;
END;

Interview Questions and Answers

Q1: What are the advantages of stored procedures?

A: Improved performance (precompiled), security, reusability, and reduced network traffic.

Q2: How do you handle errors in stored procedures?

A: Use TRY-CATCH blocks in SQL Server.

Q3: Can stored procedures return multiple result sets?

A: Yes, by executing multiple SELECT statements.

Q4: What is the difference between a stored procedure and a function?

A: Procedures can perform actions and not return values directly; functions must return a value and can't modify data.

Q5: How do you modify a stored procedure?

A: Use ALTER PROCEDURE or drop and recreate.

7. Functions

Details

Types of Functions

  • Scalar Functions: Return a single value.
  • Table-Valued Functions: Return a table.

Scalar Function Example

CREATE FUNCTION GetFullName (@FirstName VARCHAR(50), @LastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;

Usage:

SELECT dbo.GetFullName(FirstName, LastName) AS FullName FROM Employees;

Table-Valued Function Example

CREATE FUNCTION GetEmployeesByDept (@Dept VARCHAR(50))
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Employees WHERE Department = @Dept
);

Usage:

SELECT * FROM dbo.GetEmployeesByDept('IT');

Interview Questions and Answers

Q1: What is the difference between a function and a stored procedure?

A: Functions return values and can be used in SELECT statements; procedures perform actions.

Q2: Can functions modify data?

A: No, functions are read-only; they can't perform INSERT, UPDATE, DELETE.

Q3: What are deterministic and non-deterministic functions?

A: Deterministic always return the same result for same inputs; non-deterministic may vary (e.g., GETDATE()).

Q4: How do you drop a function?

A: DROP FUNCTION function_name;

Q5: Can functions be nested?

A: Yes, functions can call other functions.

8. Triggers

Details

What is a Trigger?

A trigger is a special type of stored procedure that automatically executes when an event occurs (INSERT, UPDATE, DELETE).

Types:

  • AFTER Triggers: Execute after the action.
  • INSTEAD OF Triggers: Execute instead of the action.

Syntax:

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT
AS
BEGIN
    -- SQL statements
END;

Example (Audit Trigger):

CREATE TRIGGER AuditEmployeeInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditLog (Action, TableName, ActionDate)
    VALUES ('INSERT', 'Employees', GETDATE());
END;

Using INSERTED and DELETED Tables

In triggers, INSERTED contains new rows, DELETED contains old rows.

UPDATE Trigger Example:

CREATE TRIGGER AuditEmployeeUpdate
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO AuditLog (Action, OldSalary, NewSalary)
    SELECT 'UPDATE', d.Salary, i.Salary
    FROM DELETED d
    JOIN INSERTED i ON d.EmployeeID = i.EmployeeID;
END;

Interview Questions and Answers

Q1: What is the difference between AFTER and INSTEAD OF triggers?

A: AFTER triggers run after the action completes; INSTEAD OF triggers replace the action.

Q2: Can triggers be recursive?

A: Yes, but it can be controlled with options like RECURSIVE_TRIGGERS.

Q3: How do you disable a trigger?

A: DISABLE TRIGGER trigger_name ON table_name;

Q4: What are the potential issues with triggers?

A: They can slow down operations, cause unexpected side effects, and make debugging harder.

Q5: Can triggers call stored procedures?

A: Yes, triggers can execute stored procedures.

9. CTE (Common Table Expression)

Details

What is a CTE?

A CTE is a temporary named result set defined within a query, using the WITH clause. It's useful for recursive queries and simplifying complex queries.

Syntax:

WITH CTE_Name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM CTE_Name;

Example:

WITH HighSalaryEmployees AS (
    SELECT EmployeeID, FirstName, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT * FROM HighSalaryEmployees;

Recursive CTE

Used for hierarchical data.

Example (Employee Hierarchy):

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, FirstName, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    SELECT e.EmployeeID, e.ManagerID, e.FirstName, eh.Level + 1
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

Interview Questions and Answers

Q1: What is the advantage of using CTE over subqueries?

A: CTEs are more readable and can be referenced multiple times in the query.

Q2: Can CTEs be used for updates?

A: No, CTEs are read-only; use them in SELECT, INSERT, UPDATE, DELETE with caution.

Q3: What is the scope of a CTE?

A: Only within the immediate following query.

Q4: How do recursive CTEs work?

A: They have an anchor member and a recursive member joined with UNION ALL.

Q5: Can you have multiple CTEs in one query?

A: Yes, separated by commas: WITH CTE1 AS (...), CTE2 AS (...)

10. Transactions, COMMIT, ROLLBACK, SAVEPOINT

Details

What is a Transaction?

A transaction is a sequence of operations performed as a single logical unit. It ensures data integrity (ACID properties).

Starting a Transaction:

BEGIN TRANSACTION;

COMMIT

Saves all changes made during the transaction.

COMMIT;

ROLLBACK

Undoes all changes made during the transaction.

ROLLBACK;

SAVEPOINT

Sets a point within a transaction to which you can roll back.

Example:

BEGIN TRANSACTION;
INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe');
SAVEPOINT sp1;
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;
ROLLBACK TO sp1;  -- Rolls back only the UPDATE
COMMIT;  -- Commits the INSERT

Full Example

BEGIN TRANSACTION;
INSERT INTO Accounts (AccountID, Balance) VALUES (1, 1000);
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
IF @@ERROR <> 0
    ROLLBACK;
ELSE
    COMMIT;

Interview Questions and Answers

Q1: What are ACID properties?

A: Atomicity, Consistency, Isolation, Durability.

Q2: What is the difference between COMMIT and ROLLBACK?

A: COMMIT saves changes; ROLLBACK undoes them.

Q3: Can you nest transactions?

A: In SQL Server, nested BEGIN TRANSACTION increases @@TRANCOUNT, but only the outermost COMMIT commits.

Q4: What is a SAVEPOINT used for?

A: To roll back to a specific point in the transaction without rolling back everything.

Q5: How do you handle errors in transactions?

A: Use TRY-CATCH and check @@ERROR or XACT_STATE() to decide whether to COMMIT or ROLLBACK.

Comments

Post a Comment

Popular posts from this blog