50 MySQL Interview Questions For Beginners for JOB and EXAM | SQL Questions

Main ideas / lessons conveyed

• The video is a practice/learning session featuring 50 MySQL interview-style questions, progressing from basic to intermediate difficulty.
• It emphasizes that most questions are conceptual, focusing on understanding how different MySQL topics work.
• The instructional format is:
  • Question shown first
  • Answer shown on the next slide

Method / learning approach described

Before the video starts, viewers are encouraged to:

• Use a paper and pen to write answers.
• Pause the video on each question to try answering before viewing the slide answer.
• Speak or write their answers if they prefer active recall.

The questions are presented in numerical order (Q1 to Q50), covering core SQL/MySQL fundamentals and more advanced topics such as:

• joins
• grouping
• subqueries
• routines
• triggers
• deadlocks

Detailed concepts + example commands mentioned

MySQL and configuration basics

• What is MySQL?

  • MySQL is an open-source RDBMS (Relational Database Management System) that uses SQL to manage and retrieve data.

• Difference between SQL and MySQL

  • SQL: standard language for accessing/manipulating databases.
  • MySQL: a specific system/tool (an RDBMS) that uses SQL.

• Purpose of my.cnf / my.ini (main config file)

  • Stores server configuration settings such as:
    • port
    • data directory
    • maximum connections

• What are tables and fields

  • Table: organized data in rows and columns
  • Columns are called fields (e.g., ID, name)

• Default MySQL port

  • Default port is 3306 (configurable via settings)

Working with databases, tables, and structure

• Show all databases

  • SHOW DATABASES;

• Select a database

  • USE <database_name>;

• Show all tables in the selected database

  • SHOW TABLES;

• See table structure/details

  • DESCRIBE <table_name>; (or DESC <table_name>;)

• Primary key

  • A unique identifier for records (example given: employee_id)

Creating and relating tables

• Create a table with a primary key

  • Example structure shown conceptually:
    • CREATE TABLE employees ( employee_id INT, PRIMARY KEY (employee_id) );

• Foreign key

  • A field (or fields) that uniquely identifies/enforces a link between two tables, ensuring data integrity.
  • Example concept:
    • customers.customer_id is referenced by an orders foreign key.

• Constraints

  • Rules applied to columns (or column sets) to restrict allowable values.
  • Examples mentioned:
    • Primary key constraint: unique, typically not null
    • Unique constraint
    • Default
    • Data type constraints

• Data types

  • Define what kind of values can be stored (e.g., numbers vs strings, decimals/dates).

• Difference between CHAR and VARCHAR

  • CHAR: fixed length; reserves specified space
  • VARCHAR: variable length up to a maximum

• Data type for product prices

  • Use DECIMAL or FLOAT (prices with decimal points)

• Count total records

  • Use COUNT()

• Get only top N records

  • Use LIMIT (example idea: show only top 3)

• Format date/time

  • Use a built-in date formatting function (referenced as DATE_FORMAT)

• CASE expression

  • Used for conditional logic in queries.
  • Example idea: categorize age ranges into minor/adult/senior

Constraint-related question

• Check constraint
  • Limits allowed values in a column.
  • Example: mobile number must be length ≥ 10

Relationships and schema design

• Types of SQL relationships

  • One-to-one
  • One-to-many
  • Many-to-many

• Examples described

  • employees ↔ employee details (one-to-one)
  • employees ↔ employee tasks (one-to-many)
  • books ↔ authors (many-to-many)

• Normalization

  • Organize data to reduce redundancy and improve integrity by splitting data into appropriate tables.

Stored routines, views, and automation

• Stored routines

  • SQL statements stored on the server and callable multiple times.
  • Types mentioned:
    • Stored procedures
    • User-defined functions (UDFs)

• Stored procedure

  • Stores and runs a set of SQL statements (example concept: update book stock when needed)

• View

  • A virtual table based on the result set of an SQL query

• Triggers

  • Automatically executed statements in response to events on a table/view.
  • Example idea: when a product is sold, trigger updates inventory stock.

Backup/import and file-based data movement

• Backup a MySQL database

  • Use mysqldump (referred to as “myAL dump” in subtitles)
  • Conceptual flow:
    • provide credentials
    • select database
    • dump into a backup file

• Import data from a text file

  • Use MySQL import utility (referenced in subtitles)

Query composition: joins and selection

• What is a JOIN

  • Combines rows from two or more tables based on related columns.

• Join types listed

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN (mentioned as a concept; behavior can depend on MySQL version)
  • CROSS JOIN

• Inner/left/right join explanation

  • Inner: common matches only
  • Left: all left table rows + matches
  • Right: all right table rows + matches

Performance and data retrieval operations

• Index

  • Speeds up queries by avoiding scanning the entire table.
  • Example concept: WHERE id = ... becomes faster with an index.

• How to add an index

  • Conceptual syntax:
    • CREATE INDEX <index_name> ON <table_name>(<column_name>);

• NULL values

  • Represent missing/unknown values
  • If no value is provided, a column may become NULL by default (as described)

• Select only unique values

  • SELECT DISTINCT <column>;

• Sorting

  • ORDER BY <column> ASC|DESC

• Update operation

  • Example pattern:
    • UPDATE <table> SET email = <new_email> WHERE customer_id = 5;

• Delete operation

  • Example pattern:
    • DELETE FROM orders WHERE order_id = 01001;

Aggregation and advanced filtering

• GROUP BY

  • Groups rows by one or more columns.
  • Example concept: count employees per department.

• Subquery

  • A query nested inside another query.
  • Example concept: employees whose salary is above the average salary.

• HAVING vs WHERE

  • WHERE: filters rows before grouping
  • HAVING: filters after grouping/aggregation

• ROLLUP

  • Extension of GROUP BY producing:
    • subtotals
    • grand totals
  • Adds additional aggregated rows

Data types and string/date functions

• Heap tables

  • Mentioned as in-memory tables for high-speed temporary storage.

• TIMESTAMP use

  • Stores values containing both date and time
  • Example concept: automatic updating when rows are modified.

• CONCAT

  • Concatenates multiple strings (e.g., first_name + last_name into a full name)

DDL (schema changes) and naming

• Add a new column to an existing table

  • ALTER TABLE:
    • ALTER TABLE employees ADD COLUMN birthday DATE;

• Remove/delete a table

  • DROP TABLE <table_name>;

• Rename a table

  • RENAME TABLE old_name TO new_name;
  • Also noted: ALTER TABLE can be used.

Concurrency / locking issue

• Deadlock
  • Two or more transactions prevent each other from completing due to locks/waits, causing a stuck state.

Speakers / sources featured

• No specific speaker name is provided in the subtitles.
• The source appears to be the video creator/instructor delivering the questions and answers (speaker identity not stated).