Database Admin Interview Questions and Answers for Freshers

Postgres Architecture is a client-server model with a multi-process approach.

• Postgres uses a multi-process approach with a master process and worker processes.

• The master process manages shared memory and worker processes handle client connections.

• Postgres also has a background process for maintenance tasks and a WAL writer for data durability.

• Postgres supports ACID transactions and has a query planner for optimizing queries.

• Postgres also has a storage manager for managing dis...read more

A database is a structured collection of data. SQL is a language used to manage and manipulate databases.

• A database is a software system that stores and organizes data.

• It allows users to create, read, update, and delete data.

• SQL (Structured Query Language) is a programming language used to communicate with and manage databases.

• There are different types of databases such as relational databases, object-oriented databases, and NoSQL databases.

• Relational databases use tables to ...read more

Use SQL queries to segregate alphabet and numbers in a table

• Use the SQL function REGEXP to filter out alphabets and numbers

• For example, to select only alphabets: SELECT column_name FROM table_name WHERE column_name REGEXP '^[a-zA-Z]'

• For numbers: SELECT column_name FROM table_name WHERE column_name REGEXP '^[0-9]'

A view is a virtual table based on the result-set of an SQL statement. A materialized view is a physical copy of a view.

• A view is a logical representation of data from one or more tables.

• A materialized view is a table that contains the result of a query.

• A view is updated dynamically when the underlying tables are updated.

• A materialized view is updated periodically or manually.

• Views are used to simplify complex queries and provide security.

• Materialized views are used to improv...read more

SQL command of which database?

• Specify the name of the database you want the SQL command for

• Provide the specific query you want to execute

• Ensure that the syntax is correct and the query is optimized

• Examples: SELECT * FROM table_name; INSERT INTO table_name (column1, column2) VALUES (value1, value2);

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

• Normalization involves breaking down a table into smaller tables and defining relationships between them.

• There are different levels of normalization, known as normal forms, with each level having specific rules to follow.

• The most commonly used normal forms are first normal form (1NF), second normal form (2NF), and third normal form (3NF).

• Normalization helps to prevent...read more

SQL queries to retrieve data from a database

• Use SELECT statement to retrieve data from tables

• Use WHERE clause to filter results based on specific conditions

• Use JOIN clause to combine data from multiple tables