Interview Questions

SQL commands are categorized into four types: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language).

Example:

	DDL: CREATE TABLE table_name (column1 datatype, column2 datatype);
	DML: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
	DCL: GRANT SELECT ON table_name TO user;
	TCL: COMMIT;

A database is an organized collection of structured information or data, typically stored electronically in a computer system.

        
Example: 
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100));

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table, establishing a relationship between the two.

Example:
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id));

A unique key constraint ensures that all values in a column are distinct from each other.

        
Example: 

CREATE TABLE users (username VARCHAR(50) UNIQUE);

SQL is a language for managing databases, while MySQL is a specific database management system that uses SQL.

Normalization is the process of organizing data to minimize redundancy. Types include:
1NF (First Normal Form)
2NF (Second Normal Form)
3NF (Third Normal Form)

Denormalization is the process of combining tables to improve read performance, often at the expense of write performance.

An index is a database object that improves the speed of data retrieval operations on a table.

        
Example: 
CREATE INDEX idx_name ON table_name(column_name);

A clustered index sorts and stores the data rows in the table based on the indexed column(s), while a non-clustered index creates a separate object that points to the data rows.

A view is a virtual table based on the result of a query. It does not store data physically and can simplify complex queries.

Example:
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name;

A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit and can be re-used again which gets saved in the cache.

Example:

CREATE PROCEDURE proc_name AS
BEGIN
  SELECT * FROM table_name;
END;

A trigger is a special type of stored procedure that automatically runs when certain events occur in a table.

Example:
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
  INSERT INTO log_table (log_entry) VALUES ('Row inserted');
END;

A transaction is a sequence of operations performed as a single logical unit of work. ACID properties ensure reliability:
Atomicity
Consistency
Isolation
Durability

UNION combines the result of two queries and removes duplicates, while UNION ALL includes all duplicates.

Example:
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

A subquery is a query nested inside another query.

Example:
SELECT name FROM employees WHERE id IN (SELECT employee_id FROM orders);

INNER JOIN returns only matching rows, while LEFT JOIN returns all rows from the left table and matched rows from the right.

        
Example: 
SELECT a.column1, b.column2 
FROM table_a a 
LEFT JOIN table_b b ON a.id = b.a_id;

Indexes speed up data retrieval. Advantages include improved query performance and faster search results.

A composite key is a primary key made up of two or more columns to uniquely identify a record.

        
Example:
CREATE TABLE orders (
  order_id INT,
  product_id INT,
  PRIMARY KEY (order_id, product_id));

You can find duplicates using GROUP BY and HAVING clauses.

        
Example: 
SELECT column1, COUNT(*) 
FROM table_name 
GROUP BY column1 
HAVING COUNT(*) > 1;

A self-join is a join of a table to itself.

   
Example:    
SELECT a.name, b.name 
FROM employees a, employees b 
WHERE a.manager_id = b.id;

Aggregate functions perform calculations on a set of values and return a single value. Examples include SUM(), AVG(), COUNT(), MAX(), and MIN().

  
Example:    
SELECT COUNT(*) FROM employees;

Data types define the type of data a column can hold, such as INT, VARCHAR, DATE, etc.

Constraints enforce rules on data in a table. Types include: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY

        
Example:  
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE);

Partitioning divides a table into smaller, more manageable pieces while still being treated as a single table.

Optimization techniques include using indexes, avoiding SELECT *, and analyzing execution plans.

A CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

    
Example:      
WITH cte_name AS (
  SELECT column1 FROM table_name
)
SELECT * FROM cte_name;

ROLLUP and CUBE are used to generate subtotals and grand totals in query results.

        
Example: 
SELECT department, SUM(salary)
FROM employees
GROUP BY department WITH ROLLUP;

Window functions perform calculations across a set of rows related to the current row without collapsing the result set.

        
Example: 
SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM employees;

Dynamic SQL is SQL code that is generated and executed at runtime. It is useful for building queries dynamically based on user input.

        
Example:   
EXECUTE IMMEDIATE 'SELECT * FROM ' || table_name;

Indexes speed up data retrieval but can slow down data insertion and updates due to the overhead of maintaining the index.

A materialized view stores the result of a query physically, allowing for faster access but requiring maintenance to keep it updated.

        
Example:  
CREATE MATERIALIZED VIEW mv_name AS SELECT * FROM table_name;

You can use TRY...CATCH blocks (in some databases like SQL Server) to handle errors.

        
Example:   
BEGIN TRY
  -- SQL statements
END TRY
BEGIN CATCH
  -- Error handling
END CATCH;

A deadlock occurs when two or more transactions are waiting for each other to release locks. You can resolve it by using proper transaction management and timeout settings.

You can use the LIMIT clause (or TOP in SQL Server).

Example: 
SELECT * FROM table_name LIMIT N;  -- For MySQL
SELECT TOP N * FROM table_name;    -- For SQL Server

You can use a CTE or a temporary table to identify and delete duplicates.

 Example:
 WITH cte AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY (SELECT NULL)) AS rn
  FROM table_name
)
DELETE FROM cte WHERE rn > 1;

You can use a CASE statement in the UPDATE query.

Example: 
UPDATE table_name
SET column_name = CASE 
  WHEN condition1 THEN value1 
  WHEN condition2 THEN value2 
  END
WHERE condition;

Use BEGIN TRANSACTION, COMMIT, and ROLLBACK to manage transactions.

 Example: 
 BEGIN TRANSACTION;
-- SQL statements
COMMIT;  -- or ROLLBACK;

Analyze the query plan, check for missing indexes, optimize the query, and consider database statistics.

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

 
 SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

 
SELECT *
FROM (SELECT year, department, salary FROM salaries) AS SourceTable
PIVOT
(SUM(salary) FOR department IN ([HR], [IT], [Sales])) AS PivotTable;

 
SELECT MONTH(sale_date) AS sale_month, SUM(amount) AS total_sales
FROM sales
GROUP BY MONTH(sale_date);

Use the bcp command in SQL Server or the SELECT ... INTO OUTFILE statement in MySQL.

Example:
SELECT * INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;

SELECT d.department_name, AVG(e.salary) AS average_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING AVG(e.salary) > 50000;



                        

                                
SELECT e.name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id); 

                                
SELECT o.customer_id, SUM(o.amount) AS total_spent
FROM orders o
WHERE o.order_date >= DATEADD(MONTH, -6, GETDATE())
GROUP BY o.customer_id
HAVING COUNT(o.order_id) > 5;

                                
SELECT p.product_name, c.category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id;

                                
SELECT employee_id, COUNT(*) AS promotion_count
FROM promotions
GROUP BY employee_id
HAVING COUNT(*) > 1;

(This should be based on your personal experience.)

Follow online courses, read documentation, and participate in forums or user groups.

(This should be based on your personal experience.)

Use open communication, listen to all viewpoints, and seek a collaborative solution.

Use query execution plans, logging, and SQL profiling tools to analyze and debug queries.