SQL Tables, Views, Backups, and Interview Questions with Answers

Posted on: January 19, 2025 Posted by: rahulgite Comments: 0

1. Tables in SQL

Tables are the basic structure in SQL to store data in rows and columns.

Creating a Table

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Modifying a Table

  • Add a column: ALTER TABLE employees ADD age INT;
  • Modify a column: ALTER TABLE employees MODIFY salary DECIMAL(12, 2);
  • Drop a column: ALTER TABLE employees DROP COLUMN age;

Deleting a Table

DROP TABLE employees;

2. Views in SQL

A view is a virtual table based on the result of a SELECT query.

Creating a View

CREATE VIEW high_salary_employees AS
SELECT name, department, salary
FROM employees
WHERE salary > 50000;

Using a View

SELECT * FROM high_salary_employees;

Updating a View

CREATE OR REPLACE VIEW high_salary_employees AS
SELECT name, salary
FROM employees
WHERE salary > 60000;

Deleting a View

DROP VIEW high_salary_employees;

3. Copying Databases and Tables

Copying a Table

  • Structure only: CREATE TABLE new_table LIKE old_table; This copies only the structure (column definitions) without any data.
  • Structure and data: CREATE TABLE new_table AS SELECT * FROM old_table; This copies both the structure and the data of the original table.

Copying a Database

Using a tool like mysqldump in MySQL:

mysqldump -u username -p original_database > backup.sql
mysql -u username -p new_database < backup.sql
  • Clarification: Copying a database with mysqldump includes both the structure and data unless explicitly specified to exclude data.
    • To copy only structure: mysqldump -u username -p --no-data original_database > backup_structure.sql

4. Backing Up and Restoring Databases

Backup a Database

Using mysqldump:

mysqldump -u username -p database_name > backup.sql

Restore a Database

mysql -u username -p database_name < backup.sql

Backup Specific Tables

mysqldump -u username -p database_name table1 table2 > tables_backup.sql

5. Common SQL Interview Questions with Answers

General Questions

  1. What is the difference between SQL and NoSQL?
    • SQL databases are relational, use structured query language, and have a predefined schema. NoSQL databases are non-relational, schema-less, and support unstructured or semi-structured data.
  2. Explain the ACID properties in SQL.
    • Atomicity: Ensures all operations in a transaction are completed.
    • Consistency: Maintains database integrity before and after a transaction.
    • Isolation: Ensures transactions do not interfere with each other.
    • Durability: Guarantees that completed transactions remain permanent.
  3. What are primary keys and foreign keys?
    • Primary Key: Uniquely identifies a row in a table.
    • Foreign Key: Links two tables, referencing a primary key in another table.
  4. What is the difference between DELETE, TRUNCATE, and DROP?
    • DELETE: Removes specific rows; can be rolled back.
    • TRUNCATE: Deletes all rows; cannot be rolled back.
    • DROP: Deletes the table structure and data.
  5. What are the different types of joins in SQL?
    • INNER JOIN: Returns rows with matching values in both tables.
    • LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
    • RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
    • FULL JOIN: Returns rows when there is a match in either table.
    • CROSS JOIN: Returns the Cartesian product of two tables.

Performance Questions

  1. How do indexes work in SQL?
    • Indexes create a data structure that improves query performance by reducing the data scanned for retrieval.
  2. What are the advantages and disadvantages of indexing?
    • Advantages: Faster query performance, efficient data retrieval, and reduced I/O operations.
    • Disadvantages: Slower write operations, increased storage, and maintenance overhead.
  3. How would you optimize a slow query?
    • Use indexes.
    • Avoid SELECT *.
    • Use query execution plans.
    • Optimize JOINs and WHERE conditions.
  4. What is the difference between a clustered and non-clustered index?
    • Clustered Index: Determines the physical order of data in the table. Each table can have only one clustered index. The data rows are stored in the same order as the index.
      • Example: CREATE CLUSTERED INDEX idx_employee_id ON employees(id); SELECT * FROM employees ORDER BY id;
      • Use Case: Suitable for queries that involve sorting or range scans, such as retrieving rows within a specific range of values.
    • Non-Clustered Index: Creates a separate structure for the index, with pointers to the actual data rows in the table. A table can have multiple non-clustered indexes.
      • Example: CREATE INDEX idx_employee_name ON employees(name); SELECT * FROM employees WHERE name = 'John';
      • Use Case: Ideal for queries that filter data based on columns not used in sorting, such as searching for a specific value.

Query-Based Questions

  1. Write a query to find duplicate records in a table. SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;
  2. Write a query to fetch the nth highest salary. SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET n-1;
  3. How would you find employees with salaries above the department average? SELECT name, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE e.department = e2.department );

Advanced Questions

  1. Explain the difference between a view and a materialized view.
    • A view is a virtual table generated dynamically when queried. A materialized view stores the data physically for faster access.
  2. How would you implement database replication?
    • Use master-slave or master-master replication setups to ensure data is copied and synchronized across multiple servers.
  3. What are common practices for securing an SQL database?
    • Use strong passwords.
    • Restrict user privileges.
    • Regularly update the database software.
    • Enable encryption for sensitive data.
    • Use firewalls and network security.
  4. Explain the use of transactions in SQL.
    • Transactions ensure data consistency, allowing multiple SQL statements to execute as a single unit. Use COMMIT to save changes and ROLLBACK to undo.

Leave a Comment