SQL questions

In MySQL, JOIN operations are used to combine rows from two or more tables based on related columns. Use `INNER JOIN` to return records with matching values in both tables, `LEFT JOIN` to return all records from the left table and matched records from the right table, and `RIGHT JOIN` for the opposite. For example, `SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id` retrieves orders with customer details.

The `mysqli` (MySQL Improved) extension in PHP provides an interface to interact with MySQL databases. It offers improved functionality over the older `mysql` extension, including support for prepared statements, transactions, and multi-query execution. For example, you can connect to a database with `mysqli_connect('localhost', 'user', 'password', 'database')`. `mysqli` provides both procedural and object-oriented interfaces for database operations.

'mysqli_fetch_assoc()' fetches a result row as an associative array from a MySQL database query. For example: `while ($row = mysqli_fetch_assoc($result)) { echo $row['column_name']; }` retrieves rows from a result set where each row is an associative array with column names as keys. This function is useful for accessing query results in a readable format.

'mysqli_prepare()' is used in PHP to prepare an SQL statement for execution, allowing for parameterized queries that enhance security. For example: `$stmt = mysqli_prepare($conn, 'SELECT * FROM users WHERE email = ?');`. Placeholders (like `?`) are used in the query, and then parameters are bound using `mysqli_stmt_bind_param()`. This approach helps prevent SQL injection by separating the SQL code from the data.

SQL Injection occurs when an attacker inserts malicious SQL code into a query, which can compromise the database. Prevent SQL Injection by using parameterized queries or prepared statements, validating and escaping user input, and implementing robust input validation and sanitization practices.

To back up a PostgreSQL database, use the `pg_dump` utility. For example, to back up a database named 'mydb', you would run `pg_dump mydb > mydb_backup.sql`. This creates a SQL file with the database structure and data. You can restore this backup using the `psql` command with `psql mydb < mydb_backup.sql`.

`EXISTS` is used in SQL to test for the existence of rows returned by a subquery. It returns `TRUE` if the subquery returns one or more rows and `FALSE` otherwise. For example: `SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE departments.dept_id = employees.dept_id);` checks if there are corresponding departments for employees.

To create a function in PostgreSQL, use the `CREATE FUNCTION` statement along with PL/pgSQL or another procedural language. For example: `CREATE FUNCTION get_employee_name(emp_id INT) RETURNS TEXT AS $$ BEGIN RETURN (SELECT name FROM employees WHERE id = emp_id); END; $$ LANGUAGE plpgsql;`. Use the function by calling `SELECT get_employee_name(1);`.

In PostgreSQL, large objects (LOBs) are handled using the `pg_largeobject` system catalog and associated functions. You can store large objects like files or images using `lo_create()`, `lo_write()`, and `lo_read()` functions. For example, to store a file: `SELECT lo_create(0);` to create a new large object, and then use `lo_write()` to write data. You can retrieve it with `lo_read()` and manage large objects using the `pg_largeobject` catalog.

PostgreSQL extensions add additional functionality to the database system. You can install extensions using the `CREATE EXTENSION` command. For example, to install the `pg_trgm` extension for trigram-based text search, use `CREATE EXTENSION pg_trgm;`. Extensions can be managed via the `pg_extension` catalog. Some extensions come with PostgreSQL distributions, while others may need to be downloaded and installed separately.

MyISAM is a non-transactional storage engine, ideal for read-heavy applications, while InnoDB supports transactions, foreign keys, and row-level locking, making it suitable for high-concurrency environments. For instance, InnoDB is preferable for e-commerce sites where data integrity is critical.

User-defined functions (UDFs) allow users to create custom functions to encapsulate reusable logic in SQL. UDFs can take parameters and return values. For example, a UDF to calculate tax could be defined as `CREATE FUNCTION CalculateTax(amount DECIMAL) RETURNS DECIMAL BEGIN RETURN amount * 0.1; END;`.

The CASE statement allows conditional logic in SQL queries, returning values based on specified conditions. It works like an IF statement. For example, `SELECT name, CASE WHEN score >= 60 THEN 'Pass' ELSE 'Fail' END AS result FROM exams;` assigns 'Pass' or 'Fail' based on the score.

Key differences between SQL databases and MongoDB include data structure, schema, and query language. SQL uses tables with fixed schemas, while MongoDB uses collections of documents with flexible schemas. SQL queries are structured in SQL language, whereas MongoDB uses a JSON-like query syntax, making it more intuitive for developers familiar with JSON.