Other DB theory

What is a database and what types of databases do you know?

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Databases allow for efficient storage, retrieval, and management of data.

Types of Databases:

• Relational Databases (RDBMS): Store data in tables (rows and columns). Examples include MySQL, PostgreSQL, Oracle, SQL Server.
• NoSQL Databases: Designed for unstructured or semi-structured data. Includes document stores (e.g., MongoDB), key-value stores (e.g., Redis), column-family stores (e.g., Cassandra), and graph databases (e.g., Neo4j).
• In-Memory Databases: Store data directly in memory (RAM) for faster access. Examples: Redis, Memcached.
• Distributed Databases: Data is distributed across multiple nodes or locations. Examples: Apache Cassandra, Google Bigtable.
• Object-Oriented Databases: Store data in the form of objects, as used in object-oriented programming. Examples: db4o, ObjectDB.
• Hierarchical Databases: Organize data in a tree-like structure. Example: IBM Information Management System (IMS).

What is normalization? Explain normal forms.

Normalization is the process of organizing data in a database to minimize redundancy and improve data integrity.

Normal Forms:

• First Normal Form (1NF): Ensures that the table has no repeating groups or arrays. Each field contains only atomic values.
• Second Normal Form (2NF): Achieved when the table is in 1NF and all non-key attributes are fully functionally dependent on the primary key.
• Third Normal Form (3NF): Achieved when the table is in 2NF and all non-key attributes are only dependent on the primary key and not on any other non-key attribute.
• Boyce-Codd Normal Form (BCNF): A stronger version of 3NF where every determinant is a candidate key.
• Fourth Normal Form (4NF): Achieved when the table is in BCNF and has no multi-valued dependencies.
• Fifth Normal Form (5NF): Ensures that the data is reconstructed correctly after splitting and addresses join dependency.

What is denormalization and when should it be used?

Denormalization is the process of combining normalized tables into fewer tables to improve database performance. It is used to reduce the complexity of queries and improve read performance at the cost of increased redundancy and potential data anomalies.

When to Use:

• When read operations are more frequent than write operations.
• In data warehousing and reporting systems where performance is critical.
• When complex joins are causing performance bottlenecks.

Explain the difference between SQL and NoSQL databases.

SQL Databases:

• Schema: Have a fixed schema with tables, columns, and data types.
• Data Model: Relational, stores data in tables.
• Scalability: Vertical scaling (increasing power of a single server).
• Transactions: Support ACID (Atomicity, Consistency, Isolation, Durability) transactions.
• Examples: MySQL, PostgreSQL, Oracle.

NoSQL Databases:

• Schema: Schema-less, allowing flexible data models.
• Data Model: Non-relational, includes document, key-value, column-family, and graph models.
• Scalability: Horizontal scaling (adding more servers).
• Transactions: Generally support BASE (Basically Available, Soft state, Eventual consistency) principles, though some support ACID transactions.
• Examples: MongoDB, Cassandra, Redis.

What is a primary key? Provide examples.

A primary key is a unique identifier for a record in a database table. It ensures that each record in the table is unique and can be referenced uniquely.

Examples:

• Single-column Primary Key:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
);

• Composite Primary Key:

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

What is a foreign key and how is it used?

A foreign key is a field in a table that uniquely identifies a row in another table, establishing a relationship between the two tables and enforcing referential integrity.

Usage:

Example:

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

Explain the difference between JOIN and UNION.

JOIN:

• Combines rows from two or more tables based on a related column.
• Types include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.

UNION:

• Combines the result sets of two or more SELECT queries.
• Eliminates duplicate records unless UNION ALL is used.

Example:

• JOIN:

SELECT customers.name, orders.order_id
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

• UNION:

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

What types of indexes do you know and how do they improve query performance?

Indexes are data structures that improve the speed of data retrieval operations on a database table.

Types of Indexes:

• Clustered Index: Determines the physical order of data in a table. There can be only one clustered index per table.
• Non-Clustered Index: Does not alter the physical order of the data but creates a separate structure to store the order of data. Multiple non-clustered indexes can be created on a table.
• Unique Index: Ensures that all values in a column or a combination of columns are unique.
• Full-Text Index: Used for full-text searches in large text fields.
• Bitmap Index: Efficiently handles columns with a limited number of unique values, often used in data warehousing.

Performance Improvement:
Indexes reduce the amount of data the database engine needs to scan to find the desired data, thus speeding up query performance, especially in large databases.

What is a transaction and what are its properties (ACID)?

A transaction is a sequence of one or more SQL operations that are executed as a single unit of work, ensuring data integrity.

ACID Properties:

• Atomicity: Ensures all operations are completed successfully or none at all.
• Consistency: Ensures the database remains in a valid state before and after the transaction.
• Isolation: Ensures transactions are isolated from each other.
• Durability: Ensures once a transaction is committed, it remains permanent even in case of system failures.

How to create and use a stored procedure?

A stored procedure is a precompiled collection of SQL statements stored in the database, which can be executed as a single unit.

Example:

• Creating a Stored Procedure:

CREATE PROCEDURE getCustomerOrders (IN customerId INT)
BEGIN
  SELECT order_id, order_date FROM orders WHERE customer_id = customerId;
END;

• Using a Stored Procedure:

CALL getCustomerOrders(1);

Explain the difference between clustered and non-clustered indexes.

Clustered Index:

• Determines the physical order of data in a table.
• Only one clustered index is allowed per table.
• Example: The primary key column is often used as a clustered index.

Non-Clustered Index:

• Does not alter the physical order of the data but creates a separate structure to store the order of data.
• Multiple non-clustered indexes can be created on a table.
• Example: Index on a non-primary key column like email.

What is the role of triggers in a database? Provide examples of usage.

A trigger is a stored procedure that automatically executes in response to specific events on a table or view, such as INSERT, UPDATE, or DELETE operations.

Examples of Usage:

• Audit Log: Automatically records changes made to a table.

CREATE TRIGGER audit_log_trigger
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(user_id, action, timestamp) VALUES(OLD.user_id, 'UPDATE', NOW());
END;

• Enforcing Business Rules: Prevents invalid data from being inserted.

CREATE TRIGGER prevent_negative_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
  IF NEW.balance < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Balance cannot be negative';
  END IF;
END;

What is a CTE (Common Table Expression) and how is it used?

A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

Example:

WITH Sales_CTE (salesperson_id, total_sales) AS (
  SELECT salesperson_id, SUM(sales) FROM orders GROUP BY salesperson_id
)
SELECT * FROM Sales_CTE WHERE total_sales > 10000;

In this example, the CTE Sales_CTE is used to calculate total sales for each salesperson, which is then queried for further processing.

What is database replication and what types of replication exist?

Database replication is the process of copying and maintaining database objects, such as tables, in multiple database servers to ensure consistency and availability.

Types of Replication:
Master-Slave Replication:

• Data is copied from a master server to one or more slave servers.
• Slaves can be read-only, and the master handles all write operations.

Master-Master Replication:

• Multiple servers are masters and can accept write operations.
• Data is synchronized between all masters.

Synchronous Replication:

• Changes are applied to both the primary and secondary servers simultaneously.
• Ensures data consistency but may have higher latency.

Asynchronous Replication:

• Changes are first applied to the primary server and then propagated to secondary servers.
• Improves performance but may risk data inconsistency in case of a failure.

Log-Based Replication:

• Uses database logs to replicate changes from the primary to secondary servers.

What is sharding and how does it help scale databases?

Sharding is a database architecture pattern in which data is horizontally partitioned across multiple database servers. Each partition, or "shard," contains a subset of the total data, allowing the database to scale out by distributing the load.

Benefits of Sharding:

• Improves Performance: Reduces the amount of data each server needs to handle, improving read and write performance.
• Scalability: Allows adding more shards to handle increased load.
• Fault Tolerance: If one shard fails, others can continue to operate, reducing the impact of the failure.

Example:

• A database with users from different regions can be sharded by region, with each shard handling users from a specific region.

How do you perform database backups?

Performing database backups is essential for data recovery in case of data loss or corruption.

Methods for Performing Backups:

• Full Backup: A complete copy of the entire database.

mysqldump -u username -p database_name > backup.sql

• Incremental Backup: Only backs up the data that has changed since the last backup.

• Differential Backup: Backs up data that has changed since the last full backup.

• Point-in-Time Recovery: Uses transaction logs to restore the database to a specific point in time.

• Database Snapshots: Capture the state of the database at a specific moment, often used in cloud environments.

• Automated Backups: Scheduling regular backups using cron jobs or database management tools.

What is a cursor in a database and when should it be used?

A cursor is a database object that allows row-by-row processing of the result set from a query. Cursors are typically used in situations where row-level operations are necessary, such as when iterating over a result set to perform calculations or updates.

Example:

DECLARE cursor_name CURSOR FOR
SELECT column_name FROM table_name;

OPEN cursor_name;

FETCH NEXT FROM cursor_name INTO variable_name;

CLOSE cursor_name;

When to Use:

• When processing each row individually is required.
• When complex operations are needed for each row.
• For operations that cannot be performed in a set-based approach.

Explain the difference between DELETE, TRUNCATE, and DROP.

DELETE:

• Removes rows from a table based on a condition.
• Can be rolled back using a transaction.
• Does not reset auto-increment counters.

DELETE FROM table_name WHERE condition;

TRUNCATE:

• Removes all rows from a table.
• Cannot be rolled back.
• Resets auto-increment counters.

TRUNCATE TABLE table_name;

DROP:

• Removes the entire table or database.
• Cannot be rolled back.
• All data, structure, and dependencies are removed.

DROP TABLE table_name;

How do you approach query optimization in a database? Provide examples.

Query optimization involves improving the efficiency of SQL queries to reduce execution time and resource consumption.

Approaches: Indexing: Creating indexes on frequently queried columns to speed up data retrieval.

CREATE INDEX idx_name ON table_name (column_name);

Query Refactoring: Simplifying complex queries by breaking them down or rewriting them for efficiency.

SELECT COUNT(*) FROM (SELECT DISTINCT column_name FROM table_name);

Use of Joins: Using appropriate joins (e.g., INNER JOIN) to minimize the data processed.

SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.id;

Avoiding SELECT *:: Only select the columns you need to reduce the amount of data transferred.

SELECT column_name FROM table_name WHERE condition;

Query Caching: Caching the results of frequently run queries to avoid repeated execution.

• Example: Implementing query caching at the application level. Analyzing Execution Plans: Using database tools to analyze and optimize the query execution plan.

EXPLAIN SELECT * FROM table_name WHERE condition;

Partitioning: Splitting large tables into smaller, more manageable pieces to improve query performance.

What is a database management system (DBMS)?

DBMS stands for Database Management System. DBMS is a system software responsible for the creation, retrieval, updation, and management of the database. It ensures that our data is consistent, organized, and is easily accessible by serving as an interface between the database and its end-users or application software.

What is a relational database management system (RDBMS)? How is it different from a DBMS?

RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.

What is SQL?

SQL stands for Structured Query Language. It is the standard language for relational database management systems. It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data.

What is the difference between SQL and MySQL?

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

What are tables and fields?

A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.

What are constraints in SQL?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:

• NOT NULL - Restricts NULL value from being inserted into a column.
• CHECK - Verifies that all values in a field satisfy a condition.
• DEFAULT - Automatically assigns a default value if no value has been specified for the field.
• UNIQUE - Ensures unique values to be inserted into the field.
• INDEX - Indexes a field providing faster retrieval of records.
• PRIMARY KEY - Uniquely identifies each record in a table.
• FOREIGN KEY - Ensures referential integrity for a record in another table.

What is a UNIQUE constraint?

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeably.

CREATE TABLE Students (   /* Create table with a single field as unique */
   ID INT NOT NULL UNIQUE
   Name VARCHAR(255)
);

CREATE TABLE Students (   /* Create table with multiple fields as unique */
   ID INT NOT NULL
   LastName VARCHAR(255)
   FirstName VARCHAR(255) NOT NULL
   CONSTRAINT PK_Student
   UNIQUE (ID, FirstName)
);

ALTER TABLE Students   /* Set a column as unique */
ADD UNIQUE (ID);
ALTER TABLE Students   /* Set multiple columns as unique */
ADD CONSTRAINT PK_Student   /* Naming a unique constraint */
UNIQUE (ID, FirstName);

What is a self-join?

A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query.

SELECT A.emp_id AS "Emp_ID",A.emp_name AS "Employee",
B.emp_id AS "Sup_ID",B.emp_name AS "Supervisor"
FROM employee A, employee B
WHERE A.emp_sup = B.emp_id;

What is a cross-join?

Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of the number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN.

SELECT stu.name, sub.subject
FROM students AS stu
CROSS JOIN subjects AS sub;

What is data integrity?

Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle and is a critical aspect of the design, implementation, and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

What is a query?

A query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.

SELECT fname, lname    /* select query */
FROM myDb.students
WHERE student_id = 1;

UPDATE myDB.students    /* action query */
SET fname = 'Captain', lname = 'America'
WHERE student_id = 1;

What is a subquery? What are its types?

A subquery is a query within another query, also known as a nested query or inner query. It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively. For example, here we fetch the contact information for students who have enrolled for the maths subject:

SELECT name, email, mob, address
FROM myDb.contacts
WHERE roll_no IN (
 SELECT roll_no
 FROM myDb.students
 WHERE subject = 'Maths');

There are two types of subquery - Correlated and Non-Correlated.

What is the SELECT statement?

SELECT operator in SQL is used to select data from a database. The data returned is stored in a result table, called the result-set.

SELECT * FROM myDB.students;

What are some common clauses used with SELECT queries in SQL?

Some common SQL clauses used in conjuction with a SELECT query are as follows:

• WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.
• ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).

SELECT *
FROM myDB.students
WHERE graduation_year = 2019
ORDER BY studentID DESC;

• GROUP BY clause in SQL is used to group records with identical data and can be used in conjunction with some aggregation functions to produce summarized results from the database.
• HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.

SELECT COUNT(studentId), country
FROM myDB.students
WHERE country != "INDIA"
GROUP BY country
HAVING COUNT(studentID) > 5;

What are UNION, MINUS, and INTERSECT commands?

The UNION operator combines and returns the result-set retrieved by two or more SELECT statements.

The MINUS operator in SQL is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first.

The INTERSECT clause in SQL combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.

Certain conditions need to be met before executing either of the above statements in SQL -

• Each SELECT statement within the clause must have the same number of columns
• The columns must also have similar data types
• The columns in each SELECT statement should necessarily have the same order

SELECT name FROM Students   /* Fetch the union of queries */
UNION
SELECT name FROM Contacts;
SELECT name FROM Students   /* Fetch the union of queries with duplicates*/
UNION ALL
SELECT name FROM Contacts;

SELECT name FROM Students   /* Fetch names from students */
MINUS                       /* that aren't present in contacts */
SELECT name FROM Contacts;

SELECT name FROM Students   /* Fetch names from students */
INTERSECT                   /* that are present in contacts as well */
SELECT name FROM Contacts;

What are entities and relationships?

• Entity: An entity can be a real-world object, either tangible or intangible, that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities. Each entity has some associated properties that provide it an identity.

• Relationships: Relations or links between entities that have something to do with each other. For example - The employee's table in a company's database can be associated with the salary table in the same database.

What is an alias in SQL?

An alias is a feature of SQL that is supported by most, if not all, RDBMSs. It is a temporary name assigned to the table or table column for the purpose of a particular SQL query. In addition, aliasing can be employed as an obfuscation technique to secure the real names of database fields. A table alias is also called a correlation name.

An alias is represented explicitly by the AS keyword but in some cases, the same can be performed without it as well. Nevertheless, using the AS keyword is always a good practice.

SELECT A.emp_name AS "Employee"  /* Alias using AS keyword */
B.emp_name AS "Supervisor"
FROM employee A, employee B   /* Alias without AS keyword */
WHERE A.emp_sup = B.emp_id;

What is a view?

A view in SQL is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

What are the TRUNCATE, DELETE, and DROP statements?

DELETE statement is used to delete rows from a table.

DELETE FROM Candidates
WHERE CandidateId > 1000;

TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

TRUNCATE TABLE Candidates;

DROP command is used to remove an object from the database. If you drop a table, all the rows in the table are deleted and the table structure is removed from the database.

DROP TABLE Candidates;

What is the difference between DROP and TRUNCATE statements?

If a table is dropped, all things associated with the tables are dropped as well. This includes - the relationships defined on the table with other tables, the integrity checks and constraints, access privileges and other grants that the table has. To create and use the table again in its original form, all these relations, checks, constraints, privileges and relationships need to be redefined. However, if a table is truncated, none of the above problems exist and the table retains its original structure.

What is the difference between DELETE and TRUNCATE statements?

The TRUNCATE command is used to delete all the rows from the table and free the space containing the table. The DELETE command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

What are aggregate and scalar functions?

An aggregate function performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. Following are the widely used SQL aggregate functions:

• AVG() - Calculates the mean of a collection of values.
• COUNT() - Counts the total number of records in a specific table or view.
• MIN() - Calculates the minimum of a collection of values.
• MAX() - Calculates the maximum of a collection of values.
• SUM() - Calculates the sum of a collection of values.
• FIRST() - Fetches the first element in a collection of values.
• LAST() - Fetches the last element in a collection of values.

Note: All aggregate functions described above ignore NULL values except for the COUNT function.

A scalar function returns a single value based on the input value. Following are the widely used SQL scalar functions:

• LEN() - Calculates the total length of the given field (column).
• UCASE() - Converts a collection of string values to uppercase characters.
• LCASE() - Converts a collection of string values to lowercase characters.
• MID() - Extracts substrings from a collection of string values in a table.
• CONCAT() - Concatenates two or more strings.
• RAND() - Generates a random collection of numbers of a given length.
• ROUND() - Calculates the round-off integer value for a numeric field (or decimal point values).
• NOW() - Returns the current date & time.
• FORMAT() - Sets the format to display a collection of values.

What is a user-defined function? What are its various types?

The user-defined functions in SQL are like functions in any other programming language that accept parameters, perform complex calculations, and return a value. They are written to use the logic repetitively whenever required. There are two types of SQL user-defined functions:

• Scalar Function: As explained earlier, user-defined scalar functions return a single scalar value.
• Table-Valued Functions: User-defined table-valued functions return a table as output.
  • Inline: returns a table data type based on a single SELECT statement.
  • Multi-statement: returns a tabular result-set but, unlike inline, multiple SELECT statements can be used inside the function body.

What is OLTP?

OLTP stands for Online Transaction Processing, is a class of software applications capable of supporting transaction-oriented programs. An essential attribute of an OLTP system is its ability to maintain concurrency. To avoid single points of failure, OLTP systems are often decentralized. These systems are usually designed for a large number of users who conduct short transactions. Database queries are usually simple, require sub-second response times, and return relatively few records/

What are the differences between OLTP and OLAP?

OLTP stands for Online Transaction Processing, is a class of software applications capable of supporting transaction-oriented programs. An important attribute of an OLTP system is its ability to maintain concurrency. OLTP systems often follow a decentralized architecture to avoid single points of failure. These systems are generally designed for a large audience of end-users who conduct short transactions. Queries involved in such databases are generally simple, need fast response times, and return relatively few records. A number of transactions per second acts as an effective measure for such systems.

OLAP stands for Online Analytical Processing, a class of software programs that are characterized by the relatively low frequency of online transactions. Queries are often too complex and involve a bunch of aggregations. For OLAP systems, the effectiveness measure relies highly on response time. Such systems are widely used for data mining or maintaining aggregated, historical data, usually in multi-dimensional schemas.

What is collation? What are the different types of collation sensitivity?

Collation refers to a set of rules that determine how data is sorted and compared. Rules defining the correct character sequence are used to sort the character data. It incorporates options for specifying case sensitivity, accent marks, kana character types, and character width. Below are the different types of collation sensitivity:

• Case sensitivity: A and a are treated differently.
• Accent sensitivity: a and á are treated differently.
• Kana sensitivity: Japanese kana characters Hiragana and Katakana are treated differently.
• Width sensitivity: Same character represented in single-byte (half-width) and double-byte (full-width) are treated differently.

What is a recursive stored procedure?

A stored procedure that calls itself until a boundary condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required. Some SQL programming languages limit the recursion depth to prevent an infinite loop of procedure calls from causing a stack overflow, which slows down the system and may lead to system crashes.

DELIMITER $$                             /*Set a new delimiter => $$ */
CREATE PROCEDURE calctotal(              /* Create the procedure */
  IN number INT,                         /* Set Input and Ouput variables */
  OUT total INT
) BEGIN
DECLARE score INT DEFAULT NULL;          /* Set the default value => "score" */
SELECT awards FROM achievements          /* Update "score" via SELECT query */
WHERE id = number INTOscore;
IF score IS NULL THEN SET total = 0;     /* Termination condition */
ELSE
CALL calctotal(number+1);                /* Recursive call */
SET total = total + score;               /* Action after recursion */
END IF;
END $$                                   /* End of procedure */
DELIMITER ;                              /* Reset the delimiter */

How to create empty tables with the same structure as another table?

Creating empty tables with the same structure can be done smartly by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records. Hence, SQL prepares the new table with a duplicate structure to accept the fetched records but since no records get fetched due to the WHERE clause in action, nothing is inserted into the new table.

SELECT * INTO Students_copy
FROM Students WHERE  1 = 2;

What is pattern matching in SQL?

SQL pattern matching provides for pattern search in data if you have no clue as to what that word should be. This kind of SQL query uses wildcards to match a string pattern, rather than writing the exact word. The LIKE operator is used in conjunction with SQL Wildcards to fetch the required information.

• Using the % wildcard to perform a simple search The % wildcard matches zero or more characters of any type and can be used to define wildcards both before and after the pattern. Search a student in your database with first name beginning with the letter K:

SELECT *
FROM students
WHERE first_name LIKE 'K%'

• Omitting the patterns using the NOT keyword Use the NOT keyword to select records that don't match the pattern. This query returns all students whose first name does not begin with K.

SELECT *
FROM students
WHERE first_name NOT LIKE 'K%'

• Matching a pattern anywhere using the % wildcard twice
  Search for a student in the database where he/she has a K in his/her first name.

SELECT *
FROM students
WHERE first_name LIKE '%Q%'

• Using the _ wildcard to match pattern at a specific position
  The _ wildcard matches exactly one character of any type. It can be used in conjunction with % wildcard. This query fetches all students with letter K at the third position in their first name.

SELECT *
FROM students
WHERE first_name LIKE '__K%'

• Matching patterns for a specific length
  The _ wildcard plays an important role as a limitation when it matches exactly one character. It limits the length and position of the matched results. For example -

SELECT *                                 /* Matches first names with three or more letters /
FROM students
WHERE first_name LIKE '___%'

SELECT *                                /* Matches first names with exactly four characters /
FROM students
WHERE first_name LIKE '____'

What is PostgreSQL?

PostgreSQL was first called Postgres and was developed by a team led by Computer Science Professor Michael Stonebraker in 1986. It was developed to help developers build enterprise-level applications by upholding data integrity by making systems fault-tolerant. PostgreSQL is therefore an enterprise-level, flexible, robust, open-source, and object-relational DBMS that supports flexible workloads along with handling concurrent users. It has been consistently supported by the global developer community. Due to its fault-tolerant nature, PostgreSQL has gained widespread popularity among developers.

How do you define indexes in PostgreSQL?

Indexes are the inbuilt functions in PostgreSQL which are used by the queries to perform search more efficiently on a table in the database. Consider that you have a table with thousands of records and you have the below query that only a few records can satisfy the condition, then it will take a lot of time to search and return those rows that abide by this condition as the engine has to perform the search operation on every single to check this condition. This is undoubtedly inefficient for a system dealing with huge data. Now if this system had an index on the column where we are applying search, it can use an efficient method for identifying matching rows by walking through only a few levels. This is called indexing.

Select * from some_table where table_col=120

What is the command used for creating a database in PostgreSQL?

The first step of using PostgreSQL is to create a database. This is done by using the createdb command as shown below: createdb db_name.

After running the above command, if the database creation was successful, then the below message is shown:

CREATE DATABASE

How can we start, restart, and stop the PostgreSQL server?

To start the PostgreSQL server, we run:

service postgresql start

Once the server is successfully started, we get the below message:

Starting PostgreSQL: ok

To restart the PostgreSQL server, we run:

service postgresql restart

Once the server is successfully restarted, we get the message:

Restarting PostgreSQL: server stopped ok

To stop the server, we run the command:

service postgresql stop

Once stopped successfully, we get the message:

Stopping PostgreSQL: server stopped ok

What are partitioned tables called in PostgreSQL?

Partitioned tables are logical structures that are used for dividing large tables into smaller structures that are called partitions. This approach is used for effectively increasing the query performance while dealing with large database tables. To create a partition, a key called partition key which is usually a table column or an expression, and a partitioning method needs to be defined. There are three types of inbuilt partitioning methods provided by Postgres:

• Range Partitioning: This method is done by partitioning based on a range of values. This method is most commonly used upon date fields to get monthly, weekly or yearly data. In the case of corner cases like value belonging to the end of the range, for example: if the range of partition 1 is 10-20 and the range of partition 2 is 20-30, and the given value is 10, then 10 belongs to the second partition and not the first.
• List Partitioning: This method is used to partition based on a list of known values. Most commonly used when we have a key with a categorical value. For example, getting sales data based on regions divided as countries, cities, or states.
• Hash Partitioning: This method utilizes a hash function upon the partition key. This is done when there are no specific requirements for data division and is used to access data individually. For example, you want to access data based on a specific product, then using hash partition would result in the dataset that we require. The type of partition key and the type of method used for partitioning determines how positive the performance and the level of manageability of the partitioned table are.

Define tokens in PostgreSQL.

A token in PostgreSQL is either a keyword, identifier, literal, constant, quotes identifier, or any symbol that has a distinctive personality. They may or may not be separated using a space, newline or a tab. If the tokens are keywords, they are usually commands with useful meanings. Tokens are known as building blocks of any PostgreSQL code.

What is the importance of the TRUNCATE statement?

TRUNCATE TABLE name_of_table statement removes the data efficiently and quickly from the table. The truncate statement can also be used to reset values of the identity columns along with data cleanup as shown below:

TRUNCATE TABLE name_of_table
RESTART IDENTITY;

We can also use the statement for removing data from multiple tables all at once by mentioning the table names separated by comma as shown below:

TRUNCATE TABLE
   table_1,
   table_2,
   table_3;

What is the capacity of a table in PostgreSQL?

The maximum size of PostgreSQL is 32TB.

Define sequence in PostgreSQL.

A sequence is a schema-bound, user-defined object which aids to generate a sequence of integers. This is most commonly used to generate values to identity columns in a table. We can create a sequence by using the CREATE SEQUENCE statement as shown below:

CREATE SEQUENCE serial_num START 100;

To get the next number 101 from the sequence, we use the nextval() method as shown below:

SELECT nextval('serial_num');

We can also use this sequence while inserting new records using the INSERT command:

INSERT INTO ib_table_name VALUES (nextval('serial_num'), 'interviewbit');

What are string constants in PostgreSQL?

They are character sequences bound within single quotes. These are using during data insertion or updation to characters in the database. There are special string constants that are quoted in dollars. Syntax: $tag$<string_constant>$tag$ The tag in the constant is optional and when we are not specifying the tag, the constant is called a double-dollar string literal.

How can you get a list of all databases in PostgreSQL?

This can be done by using the command \l -> backslash followed by the lower-case letter L.

How can you delete a database in PostgreSQL?

This can be done by using the DROP DATABASE command as shown in the syntax below:

DROP DATABASE database_name;

If the database has been deleted successfully, then the following message would be shown:

DROP DATABASE

What are ACID properties? Is PostgreSQL compliant with ACID?

ACID stands for Atomicity, Consistency, Isolation, Durability. They are database transaction properties which are used for guaranteeing data validity in case of errors and failures.

• Atomicity: This property ensures that the transaction is completed in all-or-nothing way.
• Consistency: This ensures that updates made to the database is valid and follows rules and restrictions.
• Isolation: This property ensures integrity of transaction that are visible to all other transactions.
• Durability: This property ensures that the committed transactions are stored permanently in the database. PostgreSQL is compliant with ACID properties

What do you understand by multi-version concurrency control (MVCC)?

MVCC or Multi-version concurrency control is used for avoiding unnecessary database locks when 2 or more requests tries to access or modify the data at the same time. This ensures that the time lag for a user to log in to the database is avoided. The transactions are recorded when anyone tries to access the content.

What do you understand by the command enable-debug?

The command enable-debug is used for enabling the compilation of all libraries and applications. When this is enabled, the system processes get hindered and generally also increases the size of the binary file. Hence, it is not recommended to switch this on in the production environment. This is most commonly used by developers to debug the bugs in their scripts and help them spot the issues.

What can you tell about WAL (Write-Ahead Logging)?

Write Ahead Logging is a feature that increases the database reliability by logging changes before any changes are done to the database. This ensures that we have enough information when a database crash occurs by helping to pinpoint to what point the work has been complete and gives a starting point from the point where it was discontinued.

What is the main disadvantage of deleting data from an existing table using the DROP TABLE command?

DROP TABLE command deletes complete data from the table along with removing the complete table structure too. In case our requirement entails just remove the data, then we would need to recreate the table to store data in it. In such cases, it is advised to use the TRUNCATE command.

How do you perform case-insensitive searches using regular expressions in PostgreSQL?

To perform case insensitive matches using a regular expression, we can use POSIX (~*) expression from pattern matching operators. For example:

'interviewbit' ~* '.*INTervIewBit.*'

How will you take backup of the database in PostgreSQL?

We can achieve this by using the pg_dump tool for dumping all object contents in the database into a single file. The steps are as follows:

Step 1: Navigate to the bin folder of the PostgreSQL installation path.

C:\>cd C:\Program Files\PostgreSQL\10.0\bin

Step 2: Execute pg_dump program to take the dump of data to a .tar folder as shown below:

pg_dump -U postgres -W -F t sample_data > C:\Users\admin\pgbackup\sample_data.tar

The database dump will be stored in the sample_data.tar file on the location specified.

Does PostgreSQL support full-text search?

Full-Text Search is the method of searching single or collection of documents stored on a computer in a full-text based database. This is mostly supported in advanced database systems like SOLR or ElasticSearch. However, the feature is present but is pretty basic in PostgreSQL.

Differentiate between commit and checkpoint.

The commit action ensures that the data consistency of the transaction is maintained and it ends the current transaction in the section. Commit adds a new record in the log that describes the COMMIT to the memory. Whereas, a checkpoint is used for writing all changes that were committed to disk up to SCN which would be kept in datafile headers and control files.

What is MongoDB?

• MongoDB is an open-source NoSQL database written in C++ language. It uses JSON-like documents with optional schemas.
• It provides easy scalability and is a cross-platform, document-oriented database.
• MongoDB works on the concept of Collection and Document.
• It combines the ability to scale out with features such as secondary indexes, range queries, sorting, aggregations, and geospatial indexes.
• MongoDB is developed by MongoDB Inc. and licensed under the Server Side Public License (SSPL).

What are some of the advantages of MongoDB?

Some advantages of MongoDB are as follows:

• MongoDB supports field, range-based, string pattern matching type queries. for searching the data in the database
• MongoDB support primary and secondary index on any fields
• MongoDB basically uses JavaScript objects in place of procedures
• MongoDB uses a dynamic database schema
• MongoDB is very easy to scale up or down
• MongoDB has inbuilt support for data partitioning (Sharding).

What is a document in MongoDB?

A Document in MongoDB is an ordered set of keys with associated values. It is represented by a map, hash, or dictionary. In JavaScript, documents are represented as objects:

{"greeting" : "Hello world!"}

Complex documents will contain multiple key/value pairs:

{"greeting" : "Hello world!", "views" : 3}

What is a collection in MongoDB?

A collection in MongoDB is a group of documents. If a document is the MongoDB analog of a row in a relational database, then a collection can be thought of as the analog to a table. Documents within a single collection can have any number of different “shapes.”, i.e. collections have dynamic schemas. For example, both of the following documents could be stored in a single collection:

{"greeting" : "Hello world!", "views": 3}
{"signoff": "Good bye"}

What are databases in MongoDB?

MongoDB groups collections into databases. MongoDB can host several databases, each grouping together collections. Some reserved database names are as follows:

admin

local

config

What is the Mongo Shell?

It is a JavaScript shell that allows interaction with a MongoDB instance from the command line. With that one can perform administrative functions, inspecting an instance, or exploring MongoDB.

To start the shell, run the mongo executable:

$ mongod
$ mongo
MongoDB shell version: 4.2.0
connecting to: test
>

The shell is a full-featured JavaScript interpreter, capable of running arbitrary JavaScript programs. Let’s see how basic math works on this:

> x = 100;
200
> x / 5;
20

How does scale-out occur in MongoDB?

The document-oriented data model of MongoDB makes it easier to split data across multiple servers. Balancing and loading data across a cluster is done by MongoDB. It then redistributes documents automatically.

The mongos acts as a query router, providing an interface between client applications and the sharded cluster.

Config servers store metadata and configuration settings for the cluster. MongoDB uses the config servers to manage distributed locks. Each sharded cluster must have its own config servers.

What are some features of MongoDB?

• Indexing: It supports generic secondary indexes and provides unique, compound, geospatial, and full-text indexing capabilities as well.
• Aggregation: It provides an aggregation framework based on the concept of data processing pipelines.
• Special collection and index types: It supports time-to-live (TTL) collections for data that should expire at a certain time
• File storage: It supports an easy-to-use protocol for storing large files and file metadata.
• Sharding: Sharding is the process of splitting data up across machines.

How to add data in MongoDB?

The basic method for adding data to MongoDB is “inserts”. To insert a single document, use the collection’s insertOne method:

> db.books.insertOne({"title" : "Start With Why"})

For inserting multiple documents into a collection, we use insertMany. This method enables passing an array of documents to the database.

How do you update a document in MongoDB?

Once a document is stored in the database, it can be changed using one of several update methods: updateOne, updateMany, and replaceOne. updateOne and updateMany each takes a filter document as their first parameter and a modifier document, which describes changes to make, as the second parameter. replaceOne also takes a filter as the first parameter, but as the second parameter replaceOne expects a document with which it will replace the document matching the filter.

For example, in order to replace a document:

{
   "_id" : ObjectId("4b2b9f67a1f631733d917a7a"),
   "name" : "alice",
   "friends" : 24,
   "enemies" : 2
}

How do you delete a document in MongoDB?

The CRUD API in MongoDB provides deleteOne and deleteMany for this purpose. Both of these methods take a filter document as their first parameter. The filter specifies a set of criteria to match against in removing documents.

For example:

> db.books.deleteOne({"_id" : 3})

How to perform queries in MongoDB?

The find method is used to perform queries in MongoDB. Querying returns a subset of documents in a collection, from no documents at all to the entire collection. Which documents get returned is determined by the first argument to find, which is a document specifying the query criteria.

Example:

> db.users.find({"age" : 24})

What are the data types in MongoDB?

MongoDB supports a wide range of data types as values in documents. Documents in MongoDB are similar to objects in JavaScript. Along with JSON’s essential key/value–pair nature, MongoDB adds support for a number of additional data types. The common data types in MongoDB are:

Null {"x" : null}

Boolean {"x" : true}

Number {"x" : 4}

String {"x" : "foobar"}

Date {"x" : new Date()}

Regular expression {"x" : /foobar/i}

Array {"x" : ["a", "b", "c"]}

Embedded document {"x" : {"foo" : "bar"}}

Object ID {"x" : ObjectId()}

Binary Data: Binary data is a string of arbitrary bytes.

Code {"x" : function() { /* ... */ }}

When to use MongoDB?

You should use MongoDB when you are building internet and business applications that need to evolve quickly and scale elegantly. MongoDB is popular with developers of all kinds who are building scalable applications using agile methodologies. MongoDB is a great choice if one needs to:

• Support a rapid iterative development.
• Scale to high levels of read and write traffic - MongoDB supports horizontal scaling through Sharding, distributing data across several machines, and facilitating high throughput operations with large sets of data.
• Scale your data repository to a massive size.
• Evolve the type of deployment as the business changes.
• Store, manage and search data with text, geospatial, or time-series dimensions.

How is querying done in MongoDB?

The find method is used to perform queries in MongoDB. Querying returns a subset of documents in a collection, from no documents at all to the entire collection. Which documents get returned is determined by the first argument to find, which is a document specifying the query criteria.

For example: If we have a string we want to match, such as a "username" key with the value "alice", we use that key/value pair instead:

> db.users.find({"username" : "alice"})

Explain the term "indexing" in MongoDB.

In MongoDB, indexes help in efficiently resolving queries. What an Index does is that it stores a small part of the data set in a form that is easy to traverse. The index stores the value of the specific field or set of fields, ordered by the value of the field as specified in the index. MongoDB’s indexes work almost identically to typical relational database indexes.

Indexes look at an ordered list with references to the content. These in turn allow MongoDB to query orders of magnitude faster. To create an index, use the createIndex collection method.

For example:

> db.users.find({"username": "user101"}).explain("executionStats")

Here, executionStats mode helps us understand the effect of using an index to satisfy queries.

What are Geospatial indexes in MongoDB?

MongoDB has two types of geospatial indexes: 2dsphere and 2d. 2dsphere indexes work with spherical geometries that model the surface of the earth based on the WGS84 datum. This datum model the surface of the earth as an oblate spheroid, meaning that there is some flattening at the poles. Distance calculations using 2sphere indexes, therefore, take the shape of the earth into account and provide a more accurate treatment of distance between, for example, two cities, than do 2d indexes. Use 2d indexes for points stored on a two-dimensional plane.

2dsphere allows you to specify geometries for points, lines, and polygons in the GeoJSON format. A point is given by a two-element array, representing longitude, latitude:

{
   "name" : "New York City",
   "loc" : {
       "type" : "Point",
       "coordinates" : [50, 2]
   }
}

A line is given by an array of points:

{
   "name" : "Hudson River",
   "loc" : {
       "type" : "LineString",
       "coordinates" : [[0,1], [0,2], [1,2]]
   }
}

Explain the process of sharding in MongoDB.

Sharding is the process of splitting data up across machines. We also use the term “partitioning” sometimes to describe this concept. We can store more data and handle more load without requiring larger or more powerful machines, by putting a subset of data on each machine. In the figure below, RS0 and RS1 are shards. MongoDB’s sharding allows you to create a cluster of many machines (shards) and break up a collection across them, putting a subset of data on each shard. This allows your application to grow beyond the resource limits of a standalone server or replica set.

Explain the SET modifier in MongoDB.

If the value of a field does not yet exist, the "$set" sets the value. This can be useful for updating schemas or adding user-defined keys.

Example:

> db.users.findOne()
{
   "_id" : ObjectId("4b253b067525f35f94b60a31"),
   "name" : "alice",
   "age" : 23,
   "sex" : "female",
   "location" : "India"
}

To add a field to this, we use “$set”:

> db.users.updateOne({"_id" :
ObjectId("4b253b067525f35f94b60a31")},
... {"$set" : {"favorite book" : "Start with Why"}})

What do you mean by transactions in MongoDB?

A transaction is a logical unit of processing in a database that includes one or more database operations, which can be read or write operations. Transactions provide a useful feature in MongoDB to ensure consistency.

MongoDB provides two APIs to use transactions.

• Core API: It is a similar syntax to relational databases (e.g., start_transaction and commit_transaction)
• Call-back API: This is the recommended approach to using transactions. It starts a transaction, executes the specified operations, and commits (or aborts on the error). It also automatically incorporates error handling logic for "TransientTransactionError" and"UnknownTransactionCommitResult".

What are MongoDB Charts?

MongoDB Charts is a new, integrated tool in MongoDB for data visualization.

MongoDB Charts offers the best way to create visualizations using data from a MongoDB database. It allows users to perform quick data representation from a database without writing code in a programming language such as Java or Python.

The two different implementations of MongoDB Charts are:

• MongoDB Charts PaaS (Platform as a Service)
• MongoDB Charts Server

What is the Aggregation Framework in MongoDB?

• The aggregation framework is a set of analytics tools within MongoDB that allow you to do analytics on documents in one or more collections.
• The aggregation framework is based on the concept of a pipeline. With an aggregation pipeline, we take input from a MongoDB collection and pass the documents from that collection through one or more stages, each of which performs a different operation on its inputs (See figure below). Each stage takes as input whatever the stage before it produced as output. The inputs and outputs for all stages are documents—a stream of documents.

Explain the concept of a pipeline in the MongoDB aggregation framework.

An individual stage of an aggregation pipeline is a data processing unit. It takes in a stream of input documents one at a time, processes each document one at a time, and produces an output stream of documents one at a time (see figure below).

What is a replica set in MongoDB?

To keep identical copies of your data on multiple servers, we use replication. It is recommended for all production deployments. Use replication to keep your application running and your data safe, even if something happens to one or more of your servers.

Such replication can be created by a replica set with MongoDB. A replica set is a group of servers with one primary, the server taking writes, and multiple secondaries, servers that keep copies of the primary’s data. If the primary crashes, the secondaries can elect a new primary from amongst themselves.

Explain the replication architecture in MongoDB.

The following diagram depicts the architecture diagram of a simple replica set cluster with only three server nodes – one primary node and two secondary nodes:

• In the preceding model, the PRIMARY database is the only active replica set member that receives write operations from database clients. The PRIMARY database saves data changes in the Oplog. Changes saved in the Oplog are sequential—that is, saved in the order that they are received and executed.
• The SECONDARY database is querying the PRIMARY database for new changes in the Oplog. If there are any changes, then Oplog entries are copied from PRIMARY to SECONDARY as soon as they are created on the PRIMARY node.
• Then, the SECONDARY database applies changes from the Oplog to its own datafiles. Oplog entries are applied in the same order they were inserted in the log. As a result, datafiles on SECONDARY are kept in sync with changes on PRIMARY.
• Usually, SECONDARY databases copy data changes directly from PRIMARY. Sometimes a SECONDARY database can replicate data from another SECONDARY. This type of replication is called Chained Replication because it is a two-step replication process. Chained replication is useful in certain replication topologies, and it is enabled by default in MongoDB.

What are some utilities for backup and restore in MongoDB?

The mongo shell does not include functions for exporting, importing, backup, or restore. However, MongoDB has created methods for accomplishing this, so that no scripting work or complex GUIs are needed. For this, several utility scripts are provided that can be used to get data in or out of the database in bulk. These utility scripts are:

• mongoimport
• mongoexport
• mongodump
• mongorestore