Database Admin Interview Preparation Guide

Q99. What is normalization?

Normalization is the process of organizing data to reduce redundancy and improve consistency. It splits data into logical tables and defines relationships between them. The goal is to make updates safer and reduce repeated data.

Q100. Why is normalization important?

Normalization helps prevent anomalies during insert, update, and delete operations. It improves data integrity and makes the design cleaner and easier to manage. In short, it keeps the database from becoming cluttered and inconsistent.

Q101. What is the first normal form (1NF)?

A table is in 1NF when it has atomic values, meaning each cell contains only one value and there are no repeating groups. Every row should also be uniquely identifiable. In simple words, one box should hold one piece of information.

Q102. What is the second normal form (2NF)?

A table is in 2NF when it is already in 1NF and all non-key columns depend on the whole primary key, not just part of it. This matters mainly when the table has a composite key. It removes partial dependency.

Q103. What is partial dependency?

Partial dependency happens when a non-key column depends on only part of a composite primary key instead of the full key. This creates redundancy and can lead to inconsistencies. Removing partial dependency is the main goal of 2NF.

Q104. What is the third normal form (3NF)?

A table is in 3NF when it is already in 2NF and non-key columns depend only on the key, not on other non-key columns. This removes transitive dependency. It helps keep data facts stored in the right place.

Q105. What is transitive dependency?

Transitive dependency happens when a non-key column depends on another non-key column instead of directly on the primary key. For example, if employee_id determines department_id and department_id determines department_name, then department_name is transitively dependent on employee_id. This is a sign the design may need to be split into separate tables.

Q106. What is BCNF?

BCNF stands for Boyce-Codd Normal Form. It is a stricter version of 3NF that handles some edge cases where 3NF is not enough. In BCNF, every determinant must be a candidate key.

Q107. Is BCNF always required in practical systems?

No, BCNF is not always required in every real-world system. Sometimes 3NF is good enough, especially when performance, simplicity, or business constraints matter more. Interviewers usually care that you understand the concept and trade-offs rather than forcing BCNF everywhere.

Q1. Why is SQL important for data scientists?
SQL is the universal language for querying structured data. Almost every company stores its core business data in relational databases — MySQL, PostgreSQL, Snowflake, BigQuery, Redshift — and data scientists must extract, clean, and analyze that data independently without relying on a data engineer. SQL is tested in 90%+ of data science interviews. In 2026, proficiency in SQL is considered as fundamental as Python — not optional.

Q2. What is the order of execution of a SQL query?
SQL executes in this order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Understanding this is critical because it explains why you cannot use a SELECT alias in a WHERE clause (WHERE runs before SELECT), but you can use it in ORDER BY (which runs after SELECT). Many interview mistakes come from not knowing this execution order.

Q3. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping — it operates on individual rows and cannot use aggregate functions. HAVING filters groups after the GROUP BY — it operates on aggregated results and can use aggregate functions like SUM(), COUNT(), AVG(). Use WHERE to filter raw data and HAVING to filter aggregated results. For example: WHERE salary > 50000 filters individual employees; HAVING AVG(salary) > 50000 filters departments whose average salary exceeds 50,000.

Q4. What are the different types of JOINs in SQL?
INNER JOIN returns only rows with matching values in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right — unmatched right rows are NULL. RIGHT JOIN returns all rows from the right table and matching left rows. FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no match. CROSS JOIN returns the Cartesian product — every combination of rows from both tables. SELF JOIN joins a table with itself, useful for hierarchical data like employee-manager relationships.

Q5. What is the difference between UNION and UNION ALL?
UNION combines the results of two queries and removes duplicate rows — it performs a DISTINCT operation which is slower. UNION ALL combines results and keeps all rows including duplicates — it is faster because no deduplication is needed. Both require the same number of columns with compatible data types. Use UNION ALL when you know there are no duplicates or when duplicates are acceptable, for better performance.

Q6. What is a subquery and what are the different types?
A subquery is a query nested inside another query. A scalar subquery returns a single value used in SELECT or WHERE. A row subquery returns one row. A table subquery (derived table) returns multiple rows and columns, used in FROM as a temporary table. A correlated subquery references the outer query’s columns and re-executes for each row of the outer query — powerful but slow. CTEs (Common Table Expressions) are the modern, readable alternative to complex subqueries.

Q7. What is a CTE (Common Table Expression) and when do you use it?
A CTE is a named temporary result set defined with the WITH clause that can be referenced within the same query. WITH cte_name AS (SELECT …) creates the CTE, which you then query like a regular table. CTEs improve readability by breaking complex queries into named logical steps. They can be recursive (for hierarchical data). Unlike subqueries, a CTE defined once can be referenced multiple times in the same query. In 2026, CTEs are the preferred style over deeply nested subqueries in professional SQL code.

Q8. What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes specific rows matching a WHERE condition — it logs each row deletion and can be rolled back. TRUNCATE removes all rows from a table instantly without logging individual rows — it is much faster than DELETE for clearing a table but cannot be rolled back in most databases. DROP removes the entire table structure and all its data permanently. In data science, you rarely use DROP or TRUNCATE — understanding the difference is tested conceptually.

Q9. What is indexing in SQL and how does it improve performance?
An index is a data structure (typically a B-tree) that allows the database to find rows matching a condition without scanning every row in the table. A query on an un-indexed column requires a full table scan — O(n). An index reduces this to O(log n). Indexes speed up SELECT queries with WHERE, JOIN, and ORDER BY clauses but slow down INSERT, UPDATE, and DELETE because the index must be maintained. Always index foreign key columns and columns frequently used in WHERE filters.

Q10. What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each row in a table — it cannot be NULL and must be unique. A foreign key in one table references the primary key of another table, establishing a relationship between them. Foreign keys enforce referential integrity — you cannot insert a foreign key value that does not exist in the referenced table. In data science interviews, understanding keys is important for writing correct JOINs and understanding database schema design.

Q11. How do you find duplicate records in a SQL table?
Use GROUP BY on the columns that should be unique and filter groups with COUNT > 1:

sql

SELECT email, COUNT(*) as count

FROM users

GROUP BY email

HAVING COUNT(*) > 1;

To retrieve the full duplicate rows, wrap this in a subquery or CTE and JOIN back to the original table. Identifying and handling duplicates is one of the most common data cleaning tasks in SQL-based data science work.

Q12. How do you find the second highest salary in a table?
This is one of the most classic SQL interview questions. Use a subquery:

sql

SELECT MAX(salary)

FROM employees

WHERE salary < (SELECT MAX(salary) FROM employees);

Or use DENSE_RANK() window function:

sql

SELECT salary FROM (

  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk

  FROM employees

) t WHERE rnk = 2;

The window function approach generalizes easily to nth highest salary.

Q13. What is the difference between COUNT(), COUNT(column), and COUNT(DISTINCT column)?
COUNT(*) counts all rows including NULLs. COUNT(column) counts non-NULL values in that column — NULLs are excluded. COUNT(DISTINCT column) counts the number of unique non-NULL values in the column. In data analysis, use COUNT(DISTINCT user_id) to count unique users, COUNT(*) to count total events, and COUNT(column) to check for NULLs by comparing it against COUNT(*).

Q14. How do you pivot data in SQL?
Pivoting transforms row values into column headers. Most databases support conditional aggregation for this:

sql

SELECT

  product,

  SUM(CASE WHEN region = ‘North’ THEN sales END) AS North,

  SUM(CASE WHEN region = ‘South’ THEN sales END) AS South,

  SUM(CASE WHEN region = ‘East’ THEN sales END) AS East

FROM sales_table

GROUP BY product;

Some databases (SQL Server, Snowflake) have a native PIVOT operator. In data science, pivoting is used frequently for creating summary dashboards and feature tables.

Q15. How do you handle NULL values in SQL?
NULL represents missing or unknown data. Use IS NULL and IS NOT NULL to filter NULLs — never use = NULL. Use COALESCE(column, default_value) to replace NULLs with a fallback value. Use NULLIF(a, b) to return NULL when two values are equal. In aggregations, NULLs are automatically ignored by SUM(), AVG(), and COUNT(column) but not COUNT(*). Always handle NULLs explicitly in data science queries to avoid silent errors.

Q23. How do you optimize a slow SQL query?
Start by running EXPLAIN or EXPLAIN ANALYZE to see the query execution plan. Look for full table scans and replace them with indexed lookups. Avoid SELECT * — select only needed columns. Filter early in CTEs and subqueries to reduce rows before JOINs. Replace correlated subqueries with JOINs or window functions. Avoid functions on indexed columns in WHERE clauses (WHERE YEAR(date) = 2026 disables the index — use WHERE date BETWEEN ‘2026-01-01’ AND ‘2026-12-31’ instead). Partition large tables on commonly filtered columns.

Q24. What is query execution plan and how do you read it?
An execution plan shows how the database engine will execute your query — which indexes it will use, in what order it will join tables, and the estimated cost of each operation. In PostgreSQL, EXPLAIN ANALYZE shows both the plan and actual execution times. Key things to look for: Seq Scan (full table scan — bad on large tables), Index Scan (using an index — good), Hash Join vs Nested Loop (join strategy), and rows estimates versus actual rows. Understanding execution plans is an advanced skill that distinguishes senior data scientists in interviews.

Q25. What is the difference between a correlated and non-correlated subquery in terms of performance?
A non-correlated subquery executes once and its result is used by the outer query — efficient. A correlated subquery references the outer query and re-executes for every row of the outer query — it can be extremely slow on large tables. For example, finding each employee’s salary versus their department average using a correlated subquery runs the inner query once per employee. The same result using a window function (AVG(salary) OVER (PARTITION BY department)) executes in a single pass. Always prefer window functions over correlated subqueries.

Q26. What is the difference between a temporary table and a CTE?
A CTE exists only within the scope of the single query that defines it — it is not materialized to disk in most databases. A temporary table is physically created (in a temp schema), persists for the session or transaction, can be indexed, and can be referenced by multiple subsequent queries. CTEs are preferred for single complex queries for readability. Temporary tables are better when you need to query the intermediate result multiple times, when the result is very large, or when you want to add an index to the intermediate result.

Q27. What is database normalization and why does a data scientist need to understand it?
Normalization organizes a database to reduce redundancy and improve data integrity. First Normal Form (1NF) eliminates repeating groups. Second Normal Form (2NF) removes partial dependencies. Third Normal Form (3NF) removes transitive dependencies. Data scientists need to understand normalization to write correct JOINs on normalized schemas, explain why certain columns are in different tables, and know when to denormalize for analytical performance. Most OLTP databases are normalized; data warehouses often use denormalized star schemas.

Q28. What is a star schema and how is it different from a snowflake schema?
A star schema has a central fact table (containing measurable events like sales transactions) surrounded by dimension tables (containing descriptive attributes like customers, products, dates). It is denormalized for fast analytical queries. A snowflake schema further normalizes dimension tables into sub-dimensions — it uses less storage but requires more JOINs. Star schemas are standard in data warehouses (Snowflake, BigQuery, Redshift) because analytical queries prioritizing read speed benefit from fewer JOINs.

Q29. How do you solve a real-world SQL problem: Find users who made a purchase in January 2026 but not in February 2026?
This is a classic retention analysis problem. Use LEFT JOIN with a NULL check:

sql

SELECT DISTINCT jan.user_id

FROM orders jan

LEFT JOIN orders feb

  ON jan.user_id = feb.user_id

  AND feb.order_date BETWEEN ‘2026-02-01’ AND ‘2026-02-28’

WHERE jan.order_date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’

  AND feb.user_id IS NULL;

Alternatively, use EXCEPT or NOT IN. The LEFT JOIN approach is generally most efficient and is the pattern most interviewers expect.

Q30. What are modern SQL features important for data scientists in 2026?
Modern SQL has evolved significantly. JSON support — PostgreSQL and Snowflake support querying JSON columns directly with -> and ->> operators, essential for semi-structured data. Array functions — querying array-type columns without unnesting. QUALIFY clause — in Snowflake/BigQuery, filters window function results without a subquery (like HAVING for window functions). PIVOT/UNPIVOT — native pivoting in Snowflake and SQL Server. Approximate aggregates — APPROX_COUNT_DISTINCT() in BigQuery for fast cardinality estimation on billions of rows. Familiarity with the SQL dialect of your target company’s data warehouse (BigQuery, Snowflake, Redshift) is increasingly tested in 2026 interviews.

Q1. Tell me about yourself.
This is not a behavioral question but it opens every interview. Your answer should follow this structure: (1) Current role/status in 1 sentence, (2) Your data science background and key skills in 2 sentences, (3) A standout project or achievement in 1 sentence, (4) Why you are excited about this specific role in 1 sentence. Total: 60–90 seconds. Never recite your resume — tell a compelling professional story that ends with why you are here today.

Q2. Tell me about a time you worked with a large or messy dataset.
STAR Framework:

• S: Describe the project — size of data, what domain it was from, why it was messy (duplicates, missing values, inconsistent formats)
• T: Your job was to clean and prepare it for a model or analysis
• A: Walk through your specific steps — how you identified problems using df.info(), df.describe(), df.isna().sum(), what imputation or removal strategy you chose and why, how you validated the cleaned data
• R: What the clean dataset enabled — a model that hit a target accuracy, an analysis that led to a business decision, time saved for the team

Q3. Describe a time you had to explain a technical concept to a non-technical stakeholder.
STAR Framework:

• S: A specific meeting or presentation where the audience was business, management, or clients with no data background
• T: You needed to explain a model, a finding, or a technical limitation in a way they could act on
• A: Describe how you used analogies, visualizations, or simplified language — avoided jargon, focused on business impact, used charts instead of metrics
• R: The stakeholder understood, made a decision, approved a project, or took action based on your explanation

Q4. Tell me about a time a model you built did not perform as expected.
STAR Framework:

• S: A project where the model underperformed on validation data or in production
• T: You needed to diagnose and fix the issue under deadline or business pressure
• A: How you investigated — checked for data leakage, analyzed the confusion matrix, examined feature importance, discovered class imbalance or distribution shift, tried different algorithms or regularization
• R: What you learned, how performance improved after your changes, and how you communicated the failure and fix to your team

Q5. Describe a time you had to work under a tight deadline.
STAR Framework:

• S: A specific project with a compressed timeline — a hackathon, sprint deadline, client presentation, or urgent business request
• T: Delivering quality analysis or a working model within the timeframe
• A: How you prioritized — what you cut, what you automated, how you communicated scope trade-offs to stakeholders, how you managed your time
• R: What you delivered on time, any trade-offs made, and what the outcome was for the business or team

Q6. Tell me about a time you disagreed with a team member or manager.
STAR Framework:

• S: A specific technical or project disagreement — different model choice, different data interpretation, different project priority
• T: You believed a different approach was better and needed to navigate the disagreement professionally
• A: How you raised your concern — data or evidence you used to support your view, how you listened to their perspective, the compromise or decision process
• R: The outcome — whether your view prevailed, a middle ground was found, or you agreed to disagree and what you learned from it

Q7. Describe a situation where you had to learn a new tool or technology quickly.
STAR Framework:

• S: A project that required a tool or technology you had not used before — a new cloud platform, a new ML framework, a new database
• T: You had to become productive quickly without slowing down the project
• A: How you learned — documentation, courses, community forums, building a small prototype first, asking colleagues strategically
• R: How quickly you became productive, what you delivered with the new tool, and whether you shared your learning with the team

Q8. Tell me about your most impactful data science project.
STAR Framework:

• S: The business context — what problem existed, what was at stake, who was affected
• T: Your specific role and ownership in the project
• A: The full technical journey — data sourcing, exploration, feature engineering, modeling decisions, evaluation approach, deployment or presentation
• R: Quantified business impact — revenue impact, cost savings, accuracy improvement, time saved, decisions influenced. This is the most important answer you will give — prepare it thoroughly.

Q9. Describe a time you identified a problem that nobody else noticed.
STAR Framework:

• S: A data quality issue, a flawed metric, a biased model, or a process inefficiency that was not on anyone’s radar
• T: You spotted it during your own analysis or by asking questions others hadn’t
• A: How you investigated to confirm the problem was real, how you documented and communicated it, how you proposed a solution
• R: What happened when the problem was fixed — improved model fairness, corrected reporting, prevented a bad business decision

Q10. Tell me about a time you failed and what you learned from it.
STAR Framework:

• S: A real failure — a model that went into production and degraded, an analysis with a mistake, a missed deadline
• T: What you were responsible for and what went wrong
• A: How you handled the failure — acknowledged it, communicated proactively, did a root cause analysis, fixed the issue
• R: What you concretely changed in your process afterward. Interviewers want to see self-awareness and growth — never blame others or minimize the failure

Q11. How do you prioritize when you have multiple projects at once?
STAR Framework:

• S: A period where you had competing demands — multiple stakeholders, multiple deadlines
• T: You needed to manage your time and deliver on all commitments
• A: Your prioritization framework — business impact, urgency, dependencies, stakeholder communication about timelines
• R: How you successfully delivered and what your system for managing priorities looks like today

Q12. Tell me about a time you used data to change a decision that was being made on gut feel.
STAR Framework:

• S: A business decision being made without data — a product change, a strategy, a resource allocation
• T: You believed data could improve or challenge the decision
• A: What data you gathered, how you analyzed it, how you presented the findings persuasively to decision-makers
• R: Whether the decision changed, what the outcome was, and the impact of having data-driven thinking in the process

Q13. Describe how you ensure the quality of your analysis before presenting it.
STAR Framework:

• S: Any significant analysis project where quality checks were critical
• T: Delivering accurate, reproducible, and trustworthy results
• A: Your quality checklist — sanity checks on row counts and distributions, cross-validation of results, peer review or code review, testing edge cases, verifying against known benchmarks, documenting assumptions
• R: A time your quality checks caught an error before it reached stakeholders, and the impact that prevention had

Q14. Tell me about a time you collaborated with engineers or product managers.
STAR Framework:

• S: A cross-functional project involving non-data team members
• T: Working effectively with people who have different technical backgrounds and priorities
• A: How you communicated requirements, aligned on timelines, translated data needs into engineering tickets, navigated different priorities
• R: A successful deployment or product outcome, and what you learned about cross-functional collaboration

Q15. Where do you see yourself in 3 years?
This is not a trick question — it tests ambition, self-awareness, and alignment with the role. A strong answer: (1) Shows genuine career direction — not just “I want to grow,” but specifically in which direction (ML engineering, research, team lead, domain specialization), (2) Connects your goals to what this company or role offers, (3) Shows you have thought about the path from where you are today. Avoid generic answers like “I want to be a senior data scientist” — be specific about the kind of problems you want to be solving.

Q16. Why do you want to work at this company?
Research the company before every interview. A strong answer mentions: (1) A specific product, dataset, or technical challenge the company works on that genuinely excites you, (2) The company’s data maturity or tech stack and why it aligns with your skills, (3) The team culture, growth opportunity, or mission. Never give a generic answer about “learning and growing.” Show that you have done research and that this specific opportunity is meaningful to you.

Q17. What is your greatest strength as a data scientist?
Pick one genuine strength and prove it with a brief example. Strong data science strengths to highlight: ability to translate business problems into data problems, end-to-end ownership from data to deployment, communication of complex findings to non-technical audiences, or a specific technical specialty like NLP or time series forecasting. Back it with a one-sentence example. Avoid generic answers like “I’m a hard worker” — be specific and technical.

Q18. What is your greatest weakness?
Pick a real weakness that is not a core requirement of the role. Frame it as something you are actively working on — with a specific action you have taken. Good examples: “I used to spend too long perfecting analyses before sharing them — I’ve started setting time-boxed checkpoints to share work-in-progress earlier.” Avoid fake weaknesses like “I work too hard” — interviewers see through them immediately.

Q19. Tell me about a time you had to deal with ambiguous requirements.
STAR Framework:

• S: A project where the problem statement, data availability, or success metric was unclear
• T: You needed to make progress despite the ambiguity
• A: How you clarified — questions you asked stakeholders, assumptions you documented, how you scoped an MVP approach to learn quickly
• R: How the project moved forward, what you validated, and how handling ambiguity well led to a better final outcome

Q20. Do you have any questions for us?
Always prepare 3 thoughtful questions. Strong questions for a data science interview: “What does the first 90 days look like for this role?” / “What is the biggest data quality challenge your team currently faces?” / “How do data scientists and engineers collaborate on model deployment here?” / “What does the model review or production deployment process look like?” / “How is the impact of the data science team measured at this company?” Asking nothing signals disinterest. Asking generic questions signals lack of preparation.

1. “Ask me 10 Python interview questions for a data science role, one at a time. Wait for my answer before giving feedback.”
2. “I answered this SQL question: [paste your answer]. Grade it and tell me what a perfect answer would include.”
3. “Explain the bias-variance tradeoff as if I am a hiring manager with no technical background.”
4. “Quiz me on NumPy broadcasting with progressively harder questions. Start easy.”
5. “Give me a pandas coding problem involving groupby, merge, and pivot table on a sales dataset. I will write the code.”
6. “Act as a senior data scientist interviewer at a product company. Ask me 5 machine learning conceptual questions.”
7. “I said XGBoost is always better than Random Forest. What would a strong interviewer say back to challenge that?”
8. “Give me a statistics problem involving A/B test design with sample size calculation. I will solve it step by step.”
9. “Explain what SHAP values are. Then ask me to explain it back and tell me what I missed.”
10. “Give me a real-world data cleaning scenario with missing values, duplicates, and outliers. I will write the Pandas code.”
11. “Quiz me on transformer architecture — attention mechanism, positional encoding, and multi-head attention.”
12. “Ask me to design a RAG pipeline for a company’s internal knowledge base. Give feedback on my answer.”
13. “Give me a window function SQL problem: find the top 3 products by revenue in each region.”
14. “I will explain gradient boosting to you. Tell me if my explanation is correct and what I missed.”
15. “Ask me five deep learning questions about CNNs, RNNs, and LSTMs alternating difficulty levels.”
16. “Give me a case study: a churn prediction model’s AUC dropped from 0.87 to 0.71 after deployment. How do I investigate?”
17. “Quiz me on the difference between precision, recall, and F1 with three different scenarios where each matters most.”
18. “Ask me to design an ML pipeline from raw data to production deployment. Critique my answer.”
19. “Give me 5 GenAI interview questions about LLMs, RAG, and responsible AI as asked at top tech companies in 2026.”
20. “I will explain what a feature store is. Score my explanation out of 10 and tell me how to improve it.”