Question 1

What are the different types of SQL JOINs, and when would you use each one?

Accepted Answer

SQL JOINs are used to combine rows from two or more tables based on a related column between them. Understanding the different JOIN types is fundamental for any database administrator because choosing the right JOIN directly affects query correctness and performance.

An INNER JOIN returns only the rows where there is a matching value in both tables. This is the most commonly used JOIN type. For example, if you join a customers table with an orders table on customer_id, the result includes only customers who have placed orders. Customers without orders and orders without valid customers are

Question 2

What is the difference between the WHERE clause and the HAVING clause in SQL?

Accepted Answer

The WHERE and HAVING clauses both filter data in SQL queries, but they operate at different stages of query execution and serve distinct purposes. Understanding this difference is essential for writing correct and efficient queries.

The WHERE clause filters individual rows before any grouping or aggregation takes place. It operates on raw table data and can reference any column in the tables being queried. For example, if you want to find all orders placed after January 1st 2024, you would write WHERE order_date > '2024-01-01'. The database engine applies this filter first, reducing the num

Question 3

What are the key differences between popular relational database management systems like PostgreSQL, MySQL, and SQL Server, and how do you choose between them?

Accepted Answer

Understanding the differences between major relational database management systems helps a junior DBA make informed recommendations and work effectively across different environments. Each system has strengths that make it suitable for different use cases.

PostgreSQL is an open-source, feature-rich database known for its standards compliance and extensibility. It supports advanced data types like JSON, arrays, and geometric types natively. Its query optimizer is sophisticated, and it handles complex queries well. PostgreSQL excels at transactional workloads with complex data models and is p

Question 4

Walk through the key steps you would follow when installing a new PostgreSQL or MySQL instance on a Linux server.

Accepted Answer

Installing a database on a Linux server is a foundational task for a junior DBA. While the specific commands vary between database systems and Linux distributions, the overall process follows a consistent pattern that ensures a secure and properly configured installation.

The first step is to prepare the server. Verify that the server meets the minimum hardware requirements for the expected workload. Check available disk space, memory, and CPU resources. Ensure the operating system is up to date with security patches. For production servers, use dedicated disks or partitions for the databas

Question 5

What are the different types of database backups, and when would you use each one?

Accepted Answer

Understanding backup types is one of the most critical skills for a database administrator. The right combination of backup types determines how much data you could lose in a disaster and how quickly you can recover. Each type serves a different purpose and has different trade-offs in terms of storage space, backup speed, and recovery time.

A full backup captures the entire database at a point in time. It copies every data file, table, index, and system object. This is the most comprehensive backup type and serves as the foundation for all recovery scenarios. A full backup is self-contained

Question 6

What is the difference between logical and physical database backups?

Accepted Answer

Logical and physical backups are two fundamentally different approaches to protecting database data. Each has strengths and weaknesses, and a well-designed backup strategy often uses both.

A logical backup exports the database content as SQL statements or a structured data format that can recreate the database objects and their data. Tools like pg_dump for PostgreSQL, mysqldump for MySQL, and expdp for Oracle produce logical backups. The output is typically a text file containing CREATE TABLE statements, INSERT statements for data, and other DDL and DML commands. Logical backups are human-r

Question 7

What is the principle of least privilege, and how do you apply it to database user management?

Accepted Answer

The principle of least privilege states that every user, application, and process should have only the minimum permissions necessary to perform their intended function. This is the most important security principle a junior DBA should understand and apply because it limits the potential damage from both accidental mistakes and malicious actions.

In database terms, this means granting users access only to the specific databases, schemas, tables, and operations they need. A reporting application that only reads data should have SELECT permissions and nothing else. A web application that reads

Question 8

What are the different authentication methods available in PostgreSQL, and when would you use each one?

Accepted Answer

PostgreSQL supports multiple authentication methods configured in the pg_hba.conf file. Each method has different security characteristics and is suited for different use cases. Understanding these options is essential for configuring secure database access.

The pg_hba.conf file controls client authentication by matching incoming connections to rules based on the connection type, database, user, and client address. Each rule specifies an authentication method. Rules are evaluated top to bottom, and the first match is used, so ordering matters.

Password-based authentication is the most co

Question 9

What are the main categories of data types in SQL databases, and how do you choose the right data type for a column?

Accepted Answer

Choosing the right data type for each column is a fundamental skill for database administrators because data types affect storage efficiency, query performance, data integrity, and application behavior. Using inappropriate data types leads to wasted storage, incorrect calculations, and performance problems that are difficult to fix later.

Numeric types store numbers and come in several varieties. INTEGER and its variants SMALLINT and BIGINT store whole numbers with different ranges. SMALLINT uses 2 bytes and stores values up to about 32,000. INTEGER uses 4 bytes and stores values up to abou

Question 10

What is database normalization, and can you explain the first three normal forms with examples?

Accepted Answer

Database normalization is the process of organizing a database schema to reduce data redundancy and improve data integrity. Each normal form builds on the previous one, adding stricter rules about how data should be structured. Understanding at least the first three normal forms is essential for any DBA involved in schema design or review.

First Normal Form requires that each column in a table contains only atomic, indivisible values, and that each row is unique. A table violates 1NF when a column contains multiple values. For example, a contacts table with a phone_numbers column storing "5

Database Administrator

Topics

SQL Fundamentals & Query Writing

Database Installation & Configuration

Backup & Recovery Basics

User Management & Security Basics

Data Types & Schema Design

Mock Interview

Quick Stats