Title: SQL And Databases — LearnCoachAssist

Q: What is a database?
A: A database is an organized collection of structured data stored and accessed electronically from a computer system. Databases allow efficient storage, retrieval, and management of data using software called a Database Management System (DBMS). Common examples include relational databases like MySQL and non-relational ones like MongoDB.

Q: What is a Relational Database Management System (RDBMS)?
A: An RDBMS is a software system that manages relational databases, organizing data into tables with rows and columns related by keys. It supports SQL for querying and ensures data integrity through constraints. Examples include PostgreSQL, Oracle, and SQL Server.

Q: What are the main types of databases?
A: Databases are broadly classified into relational (SQL-based, like MySQL) and non-relational (NoSQL, like MongoDB for documents or Cassandra for wide-column stores). Other types include hierarchical, network, and graph databases for specific use cases.

Q: What is SQL?
A: SQL stands for Structured Query Language, a standard language for managing and manipulating relational databases. It includes sublanguages like DDL for schema definition, DML for data manipulation, and DCL for access control.

Q: What are common SQL database dialects?
A: Popular SQL dialects include MySQL, PostgreSQL, SQLite, Oracle SQL, and Microsoft SQL Server, each with slight syntax variations but adhering to ANSI SQL standards. PostgreSQL is closest to the standard, while MySQL has extensions like GROUP BY shortcuts.

Q: What is a table in a database?
A: A table is a structured data storage unit in a relational database, consisting of rows (records) and columns (attributes). Each column has a defined data type, and tables relate via keys.

Q: What is a primary key?
A: A primary key is a unique identifier for each record in a table, ensuring no duplicates and enabling efficient lookups. It can be a single column or composite, like PRIMARY KEY (id).

Q: What is a foreign key?
A: A foreign key is a column or set of columns in one table that references the primary key of another table, enforcing referential integrity. For example, an orders.customer_id foreign key links to customers.id.

Q: What does DDL stand for in SQL?
A: DDL stands for Data Definition Language, used to define and manage database structures like tables, indexes, and views. Common commands: CREATE, ALTER, DROP.

Q: What is the CREATE TABLE statement?
A: The CREATE TABLE statement defines a new table with specified columns, data types, and constraints. Example: CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));.

Q: What does ALTER TABLE do?
A: The ALTER TABLE command modifies an existing table's structure, such as adding/dropping columns or constraints. Example: ALTER TABLE users ADD email VARCHAR(100);.

Q: What is DROP TABLE?
A: DROP TABLE permanently deletes a table and all its data from the database. Use with caution: DROP TABLE temp_table;.

Q: What are SQL data types?
A: SQL data types define column storage formats, including numeric (INT, FLOAT), string (VARCHAR, TEXT), date (DATE, TIMESTAMP), and boolean (BOOLEAN). Types vary slightly by DBMS.

Q: What is a CHECK constraint?
A: A CHECK constraint enforces domain integrity by limiting column values, e.g., CHECK (age >= 18). It prevents invalid data insertion.

Q: What is a UNIQUE constraint?
A: A UNIQUE constraint ensures all values in a column are distinct, like email addresses. It differs from PRIMARY KEY by allowing NULLs.

Q: What does DML stand for?
A: DML stands for Data Manipulation Language, used for querying and modifying data in tables. Commands: SELECT, INSERT, UPDATE, DELETE.

Q: What is the basic SELECT statement?
A: The SELECT statement retrieves data from a table: SELECT column1, column2 FROM table_name;. Use * for all columns.

Q: How do you filter rows with WHERE?
A: WHERE clause filters rows based on conditions: SELECT * FROM users WHERE age > 18;. Supports operators like =, >, LIKE, IN.

Q: What is ORDER BY?
A: ORDER BY sorts query results ascending (ASC) or descending (DESC): SELECT * FROM products ORDER BY price DESC;.

Q: What does LIMIT do?
A: LIMIT restricts the number of returned rows: SELECT * FROM users LIMIT 10;. Use OFFSET for pagination.

Q: How do you insert data with INSERT?
A: INSERT INTO adds new rows: INSERT INTO users (name, age) VALUES ('Alice', 25);. Supports multiple rows.

Q: What is UPDATE used for?
A: UPDATE modifies existing rows: UPDATE users SET age = 26 WHERE id = 1;. Always use WHERE to avoid updating all rows.

Q: How does DELETE work?
A: DELETE FROM removes rows: DELETE FROM users WHERE id = 1;. Without WHERE, it deletes all rows.

Q: What are aggregate functions in SQL?
A: Aggregate functions perform calculations on groups of rows, like COUNT(), SUM(), AVG(), MAX(), MIN(). Example: SELECT COUNT(*) FROM users;.

Q: What is GROUP BY?
A: GROUP BY groups rows with the same values for aggregation: SELECT department, AVG(salary) FROM employees GROUP BY department;.

Q: What does HAVING do?
A: HAVING filters groups after GROUP BY, unlike WHERE which filters rows: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;.

Q: What is an INNER JOIN?
A: An INNER JOIN returns rows with matching values in both tables: SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;.

Q: What is a LEFT JOIN?
A: A LEFT JOIN returns all rows from the left table and matching rows from the right, with NULLs for non-matches: SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;.

Q: Explain RIGHT JOIN and FULL OUTER JOIN.
A: RIGHT JOIN returns all right table rows and matches from left (NULLs for non-matches). FULL OUTER JOIN returns all rows from both with NULLs where no match.

Q: What is a subquery?
A: A subquery is a nested SELECT inside another query, used in WHERE, FROM, or SELECT: SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);.

Q: What are correlated subqueries?
A: Correlated subqueries reference columns from the outer query, executing per outer row: SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = u.id);.

Q: What is an index in databases?
A: An index is a data structure that improves query speed by allowing faster lookups on columns, like a book index. Created with CREATE INDEX idx_name ON table(column);.

Q: What are the types of indexes?
A: Common types: B-tree (default for equality/range), Hash (exact matches), Bitmap (low cardinality), Full-text (search). Clustered indexes sort table data.

Q: What is a view?
A: A view is a virtual table from a SELECT query, simplifying complex queries and enhancing security: CREATE VIEW active_users AS SELECT * FROM users WHERE active = 1;.

Q: What are database transactions?
A: Transactions are units of work ensuring data consistency via ACID properties: Atomicity, Consistency, Isolation, Durability. Use BEGIN, COMMIT, ROLLBACK.

Q: Explain ACID properties.
A: ACID ensures reliable transactions: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent safety), Durability (persistence post-commit).

Q: What is normalization?
A: Normalization organizes data to reduce redundancy and dependency using normal forms (1NF, 2NF, 3NF). It minimizes anomalies during CRUD operations.

Q: What is 1NF (First Normal Form)?
A: 1NF requires atomic values, no repeating groups, and a primary key. Each column holds single values, eliminating lists in cells.

Q: What is 2NF and 3NF?
A: 2NF builds on 1NF by removing partial dependencies (non-key attributes depend on full key). 3NF removes transitive dependencies (non-key not depending on other non-keys).

Q: What are stored procedures?
A: Stored procedures are precompiled SQL routines stored in the database, reusable and efficient: CREATE PROCEDURE GetUser(IN userId INT) ...;. They reduce network traffic.

Q: What are triggers?
A: Triggers are special procedures auto-executing on events like INSERT/UPDATE/DELETE: CREATE TRIGGER update_timestamp BEFORE UPDATE ON table FOR EACH ROW SET NEW.updated = NOW();.

Q: What is a transaction isolation level?
A: Isolation levels control concurrency: READ UNCOMMITTED (dirty reads), READ COMMITTED (no dirty), REPEATABLE READ (no non-repeatable), SERIALIZABLE (full isolation). Set with SET TRANSACTION ISOLATION LEVEL;.

Q: How do you handle NULL values?
A: IS NULL or IS NOT NULL checks NULLs (not =). Use COALESCE(value, default) or IFNULL(value, default) to replace NULLs.

Q: What is a self-join?
A: A self-join joins a table to itself, useful for hierarchies: SELECT e1.name, e2.name AS manager FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;.

Q: Explain UNION and UNION ALL.
A: UNION combines result sets and removes duplicates; UNION ALL keeps duplicates. Both require compatible columns.

Q: What are window functions?
A: Window functions compute over row sets related to current row: ROW_NUMBER() OVER (ORDER BY score DESC), RANK(), LAG() for analytics.

Q: What is database partitioning?
A: Partitioning divides large tables into smaller, manageable pieces by range, list, or hash, improving performance and maintenance on big data.

Q: Compare SQL vs NoSQL databases.
A: SQL databases are relational, schema-fixed, ACID-compliant for structured data. NoSQL are non-relational, schema-flexible, scalable for unstructured/semi-structured data like JSON.

Q: What is query optimization?
A: Query optimization uses EXPLAIN plans, indexes, rewriting queries, and statistics to minimize execution time. Tools like EXPLAIN ANALYZE in PostgreSQL help identify bottlenecks.

Q: What are common SQL injection prevention methods?
A: Prevent SQL injection with prepared statements/parameterized queries, stored procedures, input validation, and escaping. Never concatenate user input directly into SQL.