SQL And Databases Practice Exam

Question 1 SQL And Databases

What are window functions?

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);. Stored procedures are precompiled SQL routines stored in the database, reusable and efficient: CREATE PROCEDURE GetUser(IN userId INT) ...;. They reduce network traffic. Window functions compute over row sets related to current row: ROW_NUMBER() OVER (ORDER BY score DESC), RANK(), LAG() for analytics. 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);.

Question 2 SQL And Databases

Explain RIGHT JOIN and FULL OUTER JOIN.

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. INSERT INTO adds new rows: INSERT INTO users (name, age) VALUES ('Alice', 25);. Supports multiple rows. 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;. DDL stands for Data Definition Language, used to define and manage database structures like tables, indexes, and views. Common commands: CREATE, ALTER, DROP.

Question 3 SQL And Databases

What are database transactions?

Transactions are units of work ensuring data consistency via ACID properties: Atomicity, Consistency, Isolation, Durability. Use BEGIN, COMMIT, ROLLBACK. LIMIT restricts the number of returned rows: SELECT * FROM users LIMIT 10;. Use OFFSET for pagination. Query optimization uses EXPLAIN plans, indexes, rewriting queries, and statistics to minimize execution time. Tools like EXPLAIN ANALYZE in PostgreSQL help identify bottlenecks. 1NF requires atomic values, no repeating groups, and a primary key. Each column holds single values, eliminating lists in cells.

Question 4 SQL And Databases

What is a CHECK constraint?

DROP TABLE permanently deletes a table and all its data from the database. Use with caution: DROP TABLE temp_table;. IS NULL or IS NOT NULL checks NULLs (not =). Use COALESCE(value, default) or IFNULL(value, default) to replace NULLs. A CHECK constraint enforces domain integrity by limiting column values, e.g., CHECK (age >= 18). It prevents invalid data insertion. 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;.

Question 5 SQL And Databases

What is a subquery?

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. 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. 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. 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);.

Question 6 SQL And Databases

What is normalization?

Partitioning divides large tables into smaller, manageable pieces by range, list, or hash, improving performance and maintenance on big data. 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. 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. Normalization organizes data to reduce redundancy and dependency using normal forms (1NF, 2NF, 3NF). It minimizes anomalies during CRUD operations.

Question 7 SQL And Databases

What is the basic `SELECT` statement?

Window functions compute over row sets related to current row: ROW_NUMBER() OVER (ORDER BY score DESC), RANK(), LAG() for analytics. The SELECT statement retrieves data from a table: SELECT column1, column2 FROM table_name;. Use * for all columns. WHERE clause filters rows based on conditions: SELECT * FROM users WHERE age > 18;. Supports operators like =, >, LIKE, IN. Prevent SQL injection with prepared statements/parameterized queries, stored procedures, input validation, and escaping. Never concatenate user input directly into SQL.

Question 8 SQL And Databases

What is a foreign key?

Query optimization uses EXPLAIN plans, indexes, rewriting queries, and statistics to minimize execution time. Tools like EXPLAIN ANALYZE in PostgreSQL help identify bottlenecks. IS NULL or IS NOT NULL checks NULLs (not =). Use COALESCE(value, default) or IFNULL(value, default) to replace NULLs. 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. DELETE FROM removes rows: DELETE FROM users WHERE id = 1;. Without WHERE, it deletes all rows.

Question 9 SQL And Databases

Explain UNION and UNION ALL.

Aggregate functions perform calculations on groups of rows, like COUNT(), SUM(), AVG(), MAX(), MIN(). Example: SELECT COUNT(*) FROM users;. WHERE clause filters rows based on conditions: SELECT * FROM users WHERE age > 18;. Supports operators like =, >, LIKE, IN. UNION combines result sets and removes duplicates; UNION ALL keeps duplicates. Both require compatible columns. Transactions are units of work ensuring data consistency via ACID properties: Atomicity, Consistency, Isolation, Durability. Use BEGIN, COMMIT, ROLLBACK.

Question 10 SQL And Databases

What is a self-join?

Aggregate functions perform calculations on groups of rows, like COUNT(), SUM(), AVG(), MAX(), MIN(). Example: SELECT COUNT(*) FROM users;. 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. 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;. 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);.

Question 11 SQL And Databases

What is `DROP TABLE`?

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));. Transactions are units of work ensuring data consistency via ACID properties: Atomicity, Consistency, Isolation, Durability. Use BEGIN, COMMIT, ROLLBACK. UPDATE modifies existing rows: UPDATE users SET age = 26 WHERE id = 1;. Always use WHERE to avoid updating all rows. DROP TABLE permanently deletes a table and all its data from the database. Use with caution: DROP TABLE temp_table;.

Question 12 SQL And Databases

What is `ORDER BY`?

ORDER BY sorts query results ascending (ASC) or descending (DESC): SELECT * FROM products ORDER BY price DESC;. 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;. 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);. Prevent SQL injection with prepared statements/parameterized queries, stored procedures, input validation, and escaping. Never concatenate user input directly into SQL.

Question 13 SQL And Databases

What does `LIMIT` do?

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). 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;. INSERT INTO adds new rows: INSERT INTO users (name, age) VALUES ('Alice', 25);. Supports multiple rows. LIMIT restricts the number of returned rows: SELECT * FROM users LIMIT 10;. Use OFFSET for pagination.

Question 14 SQL And Databases

What are the types of indexes?

Common types: B-tree (default for equality/range), Hash (exact matches), Bitmap (low cardinality), Full-text (search). Clustered indexes sort table data. DML stands for Data Manipulation Language, used for querying and modifying data in tables. Commands: SELECT, INSERT, UPDATE, DELETE. Partitioning divides large tables into smaller, manageable pieces by range, list, or hash, improving performance and maintenance on big data. 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.

Question 15 SQL And Databases

What is `GROUP BY`?

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. GROUP BY groups rows with the same values for aggregation: SELECT department, AVG(salary) FROM employees GROUP BY department;. Normalization organizes data to reduce redundancy and dependency using normal forms (1NF, 2NF, 3NF). It minimizes anomalies during CRUD operations. 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.

Question 16 SQL And Databases

What is a Relational Database Management System (RDBMS)?

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. 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. A CHECK constraint enforces domain integrity by limiting column values, e.g., CHECK (age >= 18). It prevents invalid data insertion. 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.

Question 17 SQL And Databases

What is a table in a database?

Prevent SQL injection with prepared statements/parameterized queries, stored procedures, input validation, and escaping. Never concatenate user input directly into SQL. 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. 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. 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;.

Question 18 SQL And Databases

What is `UPDATE` used for?

A UNIQUE constraint ensures all values in a column are distinct, like email addresses. It differs from PRIMARY KEY by allowing NULLs. UPDATE modifies existing rows: UPDATE users SET age = 26 WHERE id = 1;. Always use WHERE to avoid updating all rows. Normalization organizes data to reduce redundancy and dependency using normal forms (1NF, 2NF, 3NF). It minimizes anomalies during CRUD operations. 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.

Question 19 SQL And Databases

What are common SQL database dialects?

LIMIT restricts the number of returned rows: SELECT * FROM users LIMIT 10;. Use OFFSET for pagination. DROP TABLE permanently deletes a table and all its data from the database. Use with caution: DROP TABLE temp_table;. WHERE clause filters rows based on conditions: SELECT * FROM users WHERE age > 18;. Supports operators like =, >, LIKE, IN. 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.

Question 20 SQL And Databases

What is a primary key?

GROUP BY groups rows with the same values for aggregation: SELECT department, AVG(salary) FROM employees GROUP BY department;. DDL stands for Data Definition Language, used to define and manage database structures like tables, indexes, and views. Common commands: CREATE, ALTER, DROP. 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). Normalization organizes data to reduce redundancy and dependency using normal forms (1NF, 2NF, 3NF). It minimizes anomalies during CRUD operations.

Question 21 SQL And Databases

What does DDL stand for in SQL?

Prevent SQL injection with prepared statements/parameterized queries, stored procedures, input validation, and escaping. Never concatenate user input directly into SQL. 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;. UPDATE modifies existing rows: UPDATE users SET age = 26 WHERE id = 1;. Always use WHERE to avoid updating all rows. DDL stands for Data Definition Language, used to define and manage database structures like tables, indexes, and views. Common commands: CREATE, ALTER, DROP.

Question 22 SQL And Databases

What is database partitioning?

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. A CHECK constraint enforces domain integrity by limiting column values, e.g., CHECK (age >= 18). It prevents invalid data insertion. Partitioning divides large tables into smaller, manageable pieces by range, list, or hash, improving performance and maintenance on big data. Transactions are units of work ensuring data consistency via ACID properties: Atomicity, Consistency, Isolation, Durability. Use BEGIN, COMMIT, ROLLBACK.

Question 23 SQL And Databases

Explain ACID properties.

ACID ensures reliable transactions: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent safety), Durability (persistence post-commit). 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;. Common types: B-tree (default for equality/range), Hash (exact matches), Bitmap (low cardinality), Full-text (search). Clustered indexes sort table data. 1NF requires atomic values, no repeating groups, and a primary key. Each column holds single values, eliminating lists in cells.

Question 24 SQL And Databases

What does `HAVING` do?

Transactions are units of work ensuring data consistency via ACID properties: Atomicity, Consistency, Isolation, Durability. Use BEGIN, COMMIT, ROLLBACK. Normalization organizes data to reduce redundancy and dependency using normal forms (1NF, 2NF, 3NF). It minimizes anomalies during CRUD operations. HAVING filters groups after GROUP BY, unlike WHERE which filters rows: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;. Prevent SQL injection with prepared statements/parameterized queries, stored procedures, input validation, and escaping. Never concatenate user input directly into SQL.

Question 25 SQL And Databases

Compare SQL vs NoSQL databases.

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. 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);. UPDATE modifies existing rows: UPDATE users SET age = 26 WHERE id = 1;. Always use WHERE to avoid updating all rows. 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.

Question 26 SQL And Databases

What are SQL data types?

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. 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);. The SELECT statement retrieves data from a table: SELECT column1, column2 FROM table_name;. Use * for all columns. Transactions are units of work ensuring data consistency via ACID properties: Atomicity, Consistency, Isolation, Durability. Use BEGIN, COMMIT, ROLLBACK.

Question 27 SQL And Databases

How do you handle NULL values?

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. LIMIT restricts the number of returned rows: SELECT * FROM users LIMIT 10;. Use OFFSET for pagination. IS NULL or IS NOT NULL checks NULLs (not =). Use COALESCE(value, default) or IFNULL(value, default) to replace NULLs. A UNIQUE constraint ensures all values in a column are distinct, like email addresses. It differs from PRIMARY KEY by allowing NULLs.

Question 28 SQL And Databases

How do you insert data with `INSERT`?

Prevent SQL injection with prepared statements/parameterized queries, stored procedures, input validation, and escaping. Never concatenate user input directly into SQL. 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;. INSERT INTO adds new rows: INSERT INTO users (name, age) VALUES ('Alice', 25);. Supports multiple rows. 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.

Question 29 SQL And Databases

What are the main types of databases?

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;. 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. Window functions compute over row sets related to current row: ROW_NUMBER() OVER (ORDER BY score DESC), RANK(), LAG() for analytics. 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;.

Question 30 SQL And Databases

What is a database?

WHERE clause filters rows based on conditions: SELECT * FROM users WHERE age > 18;. Supports operators like =, >, LIKE, IN. 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. Partitioning divides large tables into smaller, manageable pieces by range, list, or hash, improving performance and maintenance on big data. 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.

Question 31 SQL And Databases

What is query optimization?

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;. Query optimization uses EXPLAIN plans, indexes, rewriting queries, and statistics to minimize execution time. Tools like EXPLAIN ANALYZE in PostgreSQL help identify bottlenecks. GROUP BY groups rows with the same values for aggregation: SELECT department, AVG(salary) FROM employees GROUP BY department;. 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.

Question 32 SQL And Databases

What is an INNER JOIN?

Common types: B-tree (default for equality/range), Hash (exact matches), Bitmap (low cardinality), Full-text (search). Clustered indexes sort table data. An INNER JOIN returns rows with matching values in both tables: SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;. A UNIQUE constraint ensures all values in a column are distinct, like email addresses. It differs from PRIMARY KEY by allowing NULLs. 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.

Question 33 SQL And Databases

How do you filter rows with `WHERE`?

WHERE clause filters rows based on conditions: SELECT * FROM users WHERE age > 18;. Supports operators like =, >, LIKE, IN. 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. Window functions compute over row sets related to current row: ROW_NUMBER() OVER (ORDER BY score DESC), RANK(), LAG() for analytics. 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;.

Question 34 SQL And Databases

What is a transaction isolation level?

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;. 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;. 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. 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);.

Question 35 SQL And Databases

What is 1NF (First Normal Form)?

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. 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);. 1NF requires atomic values, no repeating groups, and a primary key. Each column holds single values, eliminating lists in cells. 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).

Question 36 SQL And Databases

What is a view?

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;. 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). A UNIQUE constraint ensures all values in a column are distinct, like email addresses. It differs from PRIMARY KEY by allowing NULLs. UPDATE modifies existing rows: UPDATE users SET age = 26 WHERE id = 1;. Always use WHERE to avoid updating all rows.

Question 37 SQL And Databases

What does DML stand for?

DML stands for Data Manipulation Language, used for querying and modifying data in tables. Commands: SELECT, INSERT, UPDATE, DELETE. DROP TABLE permanently deletes a table and all its data from the database. Use with caution: DROP TABLE temp_table;. Common types: B-tree (default for equality/range), Hash (exact matches), Bitmap (low cardinality), Full-text (search). Clustered indexes sort table data. 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.

Question 38 SQL And Databases

What is 2NF and 3NF?

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. 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). 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. 1NF requires atomic values, no repeating groups, and a primary key. Each column holds single values, eliminating lists in cells.

Question 39 SQL And Databases

What are correlated subqueries?

ACID ensures reliable transactions: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent safety), Durability (persistence post-commit). Transactions are units of work ensuring data consistency via ACID properties: Atomicity, Consistency, Isolation, Durability. Use BEGIN, COMMIT, ROLLBACK. 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). 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);.

Question 40 SQL And Databases

What is a LEFT JOIN?

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;. Query optimization uses EXPLAIN plans, indexes, rewriting queries, and statistics to minimize execution time. Tools like EXPLAIN ANALYZE in PostgreSQL help identify bottlenecks. Window functions compute over row sets related to current row: ROW_NUMBER() OVER (ORDER BY score DESC), RANK(), LAG() for analytics. 1NF requires atomic values, no repeating groups, and a primary key. Each column holds single values, eliminating lists in cells.

Question 41 SQL And Databases

How does `DELETE` work?

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. 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));. DELETE FROM removes rows: DELETE FROM users WHERE id = 1;. Without WHERE, it deletes all rows. INSERT INTO adds new rows: INSERT INTO users (name, age) VALUES ('Alice', 25);. Supports multiple rows.

Question 42 SQL And Databases

What does `ALTER TABLE` do?

Aggregate functions perform calculations on groups of rows, like COUNT(), SUM(), AVG(), MAX(), MIN(). Example: SELECT COUNT(*) FROM users;. 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);. INSERT INTO adds new rows: INSERT INTO users (name, age) VALUES ('Alice', 25);. Supports multiple rows. An INNER JOIN returns rows with matching values in both tables: SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;.

Question 43 SQL And Databases

What is an index in databases?

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. UPDATE modifies existing rows: UPDATE users SET age = 26 WHERE id = 1;. Always use WHERE to avoid updating all rows. Prevent SQL injection with prepared statements/parameterized queries, stored procedures, input validation, and escaping. Never concatenate user input directly into SQL. 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);.

Question 44 SQL And Databases

What is the `CREATE TABLE` statement?

GROUP BY groups rows with the same values for aggregation: SELECT department, AVG(salary) FROM employees GROUP BY department;. DROP TABLE permanently deletes a table and all its data from the database. Use with caution: DROP TABLE temp_table;. Stored procedures are precompiled SQL routines stored in the database, reusable and efficient: CREATE PROCEDURE GetUser(IN userId INT) ...;. They reduce network traffic. 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));.

Question 45 SQL And Databases

What is SQL?

ORDER BY sorts query results ascending (ASC) or descending (DESC): SELECT * FROM products ORDER BY price DESC;. 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);. Stored procedures are precompiled SQL routines stored in the database, reusable and efficient: CREATE PROCEDURE GetUser(IN userId INT) ...;. They reduce network traffic. 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.

Question 46 SQL And Databases

What is a UNIQUE constraint?

A UNIQUE constraint ensures all values in a column are distinct, like email addresses. It differs from PRIMARY KEY by allowing NULLs. Aggregate functions perform calculations on groups of rows, like COUNT(), SUM(), AVG(), MAX(), MIN(). Example: SELECT COUNT(*) FROM users;. DML stands for Data Manipulation Language, used for querying and modifying data in tables. Commands: SELECT, INSERT, UPDATE, DELETE. 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.

Question 47 SQL And Databases

What are common SQL injection prevention methods?

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. DDL stands for Data Definition Language, used to define and manage database structures like tables, indexes, and views. Common commands: CREATE, ALTER, DROP. Prevent SQL injection with prepared statements/parameterized queries, stored procedures, input validation, and escaping. Never concatenate user input directly into SQL. DML stands for Data Manipulation Language, used for querying and modifying data in tables. Commands: SELECT, INSERT, UPDATE, DELETE.

Question 48 SQL And Databases

What are stored procedures?

Prevent SQL injection with prepared statements/parameterized queries, stored procedures, input validation, and escaping. Never concatenate user input directly into SQL. Common types: B-tree (default for equality/range), Hash (exact matches), Bitmap (low cardinality), Full-text (search). Clustered indexes sort table data. Stored procedures are precompiled SQL routines stored in the database, reusable and efficient: CREATE PROCEDURE GetUser(IN userId INT) ...;. They reduce network traffic. 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.

Question 49 SQL And Databases

What are aggregate functions in SQL?

Aggregate functions perform calculations on groups of rows, like COUNT(), SUM(), AVG(), MAX(), MIN(). Example: SELECT COUNT(*) FROM users;. 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;. 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. 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);.

Question 50 SQL And Databases

What are triggers?

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;. GROUP BY groups rows with the same values for aggregation: SELECT department, AVG(salary) FROM employees GROUP BY department;. 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();. 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.