MySQL Interview Questions

Question: What are the different types of joins in MySQL?

Answer:

In MySQL, joins are used to combine rows from two or more tables based on a related column between them. There are several types of joins, each serving different purposes:

1. INNER JOIN

• Description: The INNER JOIN returns only the rows that have matching values in both tables. If no match is found, the row is excluded from the result set.
• Usage: It is used when you want to retrieve data that exists in both tables, based on a shared column (usually a primary key and foreign key relationship).
• Example:

  SELECT employees.name, departments.department_name
  FROM employees
  INNER JOIN departments ON employees.department_id = departments.id;

2. LEFT JOIN (or LEFT OUTER JOIN)

• Description: The LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.
• Usage: It is useful when you want to retrieve all records from the left table and matching records from the right table, including rows from the left table that do not have corresponding rows in the right table.
• Example:

  SELECT employees.name, departments.department_name
  FROM employees
  LEFT JOIN departments ON employees.department_id = departments.id;

3. RIGHT JOIN (or RIGHT OUTER JOIN)

• Description: The RIGHT JOIN is the opposite of the LEFT JOIN. It returns all rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned for columns from the left table.
• Usage: It is used when you want to return all records from the right table and matching records from the left table, including rows from the right table without corresponding rows in the left table.
• Example:

  SELECT employees.name, departments.department_name
  FROM employees
  RIGHT JOIN departments ON employees.department_id = departments.id;

4. FULL JOIN (or FULL OUTER JOIN)

• Description: The FULL JOIN returns all rows when there is a match in either the left table or the right table. If there is no match, NULL values are returned for the missing side. MySQL does not natively support the FULL JOIN, but it can be simulated by combining a LEFT JOIN and a RIGHT JOIN with UNION.
• Usage: It is useful when you want to retrieve all records from both tables, with NULL values for unmatched rows.
• Example (simulating FULL JOIN in MySQL):

  SELECT employees.name, departments.department_name
  FROM employees
  LEFT JOIN departments ON employees.department_id = departments.id
  UNION
  SELECT employees.name, departments.department_name
  FROM employees
  RIGHT JOIN departments ON employees.department_id = departments.id;

5. CROSS JOIN

• Description: The CROSS JOIN returns the Cartesian product of the two tables. This means it will combine each row of the first table with every row of the second table, resulting in a potentially very large number of rows.
• Usage: It is used when you want to pair each row from one table with every row from another table, regardless of any condition or relationship.
• Example:

  SELECT products.name, categories.category_name
  FROM products
  CROSS JOIN categories;

6. SELF JOIN

• Description: A SELF JOIN is a join where a table is joined with itself. This can be useful when you want to compare rows within the same table.
• Usage: It is often used to compare rows in a table that are related to other rows in the same table, typically with different aliases to differentiate the two instances of the same table.
• Example:

  SELECT A.name AS Employee, B.name AS Manager
  FROM employees A
  INNER JOIN employees B ON A.manager_id = B.id;

Summary:

• INNER JOIN: Retrieves rows with matching values in both tables.
• LEFT JOIN: Retrieves all rows from the left table, and matching rows from the right table.
• RIGHT JOIN: Retrieves all rows from the right table, and matching rows from the left table.
• FULL JOIN: Retrieves all rows from both tables, with NULL for unmatched rows (simulated in MySQL).
• CROSS JOIN: Retrieves the Cartesian product of two tables, i.e., every combination of rows.
• SELF JOIN: Joins a table with itself, typically for comparing rows within the same table.

Each type of join is used depending on the relationship between the tables and the desired result.

Question: What is normalization? Explain the different normal forms.

Answer:

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The goal of normalization is to ensure that the database is efficient, logical, and free from certain types of data anomalies (such as update, insert, and delete anomalies). This process involves dividing large tables into smaller, more manageable ones and defining relationships between them.

Normalization typically involves applying a set of rules called normal forms, which aim to organize the data in progressively stricter ways to eliminate redundancy and ensure consistency.

Different Normal Forms:

1. First Normal Form (1NF):

• Definition: A table is in 1NF if it meets the following conditions:

  • It only contains atomic (indivisible) values.
  • Each column contains values of a single type.
  • Each column has a unique name.
  • The order in which data is stored does not matter.
  • There are no repeating groups of columns.

• Goal: Eliminate duplicate columns and ensure that each record (row) is unique.

• Example: If we have a table of students and their courses:

  StudentID	StudentName	Courses
  1	Alice	Math, Science
  2	Bob	History, English

  To make this table 1NF-compliant, we need to split the courses into separate rows:

  StudentID	StudentName	Course
  1	Alice	Math
  1	Alice	Science
  2	Bob	History
  2	Bob	English

2. Second Normal Form (2NF):

• Definition: A table is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the primary key.

• Goal: Eliminate partial dependency, i.e., when non-key attributes depend on only a part of the composite primary key.

• Example: Consider a table where the primary key is a combination of StudentID and CourseID:

  StudentID	CourseID	StudentName	Instructor
  1	101	Alice	Dr. Smith
  1	102	Alice	Dr. Johnson
  2	101	Bob	Dr. Smith

  In this case, the StudentName is only dependent on StudentID and not on the full primary key (StudentID, CourseID). To convert the table to 2NF, we need to remove the partial dependency:

  • Create one table for students and another for courses:

    Students Table:

    StudentID	StudentName
    1	Alice
    2	Bob

    Courses Table:

    StudentID	CourseID	Instructor
    1	101	Dr. Smith
    1	102	Dr. Johnson
    2	101	Dr. Smith

3. Third Normal Form (3NF):

• Definition: A table is in 3NF if it is in 2NF and all non-key attributes are non-transitively dependent on the primary key. This means that non-key attributes should not depend on other non-key attributes.

• Goal: Eliminate transitive dependency, i.e., when non-key attributes depend on other non-key attributes.

• Example: Consider a table with the following structure:

  StudentID	CourseID	Instructor	InstructorPhone
  1	101	Dr. Smith	123-456
  1	102	Dr. Johnson	789-012

  The InstructorPhone is dependent on the Instructor, not directly on the StudentID or CourseID. This creates a transitive dependency. To convert the table to 3NF, we should separate the Instructor and InstructorPhone into a new table:

  Students and Courses Table:

  StudentID	CourseID	Instructor
  1	101	Dr. Smith
  1	102	Dr. Johnson

  Instructors Table:

  Instructor	InstructorPhone
  Dr. Smith	123-456
  Dr. Johnson	789-012

4. Boyce-Codd Normal Form (BCNF):

• Definition: A table is in BCNF if it is in 3NF and if for every functional dependency (X → Y), X is a superkey. This ensures that there are no exceptions to the rule of key dependencies.

• Goal: Remove any remaining anomalies in 3NF where a non-prime attribute (an attribute not part of the candidate key) determines a candidate key.

• Example: Consider a table with the following structure:

  StudentID	CourseID	Instructor	Department
  1	101	Dr. Smith	CS
  1	102	Dr. Johnson	Math

  Here, Instructor determines Department, but Instructor is not a superkey. To convert this table into BCNF, we need to decompose it into two tables:

  Courses Table:

  CourseID	Instructor	Department
  101	Dr. Smith	CS
  102	Dr. Johnson	Math

  Students Table:

  StudentID	CourseID
  1	101
  1	102

5. Fourth Normal Form (4NF):

• Definition: A table is in 4NF if it is in BCNF and has no multi-valued dependencies. This means that a table should not contain two or more independent multi-valued facts about an entity.

• Goal: Eliminate multi-valued dependencies where an attribute can have multiple values for a single record.

• Example: Consider a table of students with multiple phone numbers and multiple email addresses:

  StudentID	PhoneNumber	EmailAddress
  1	123-456	[email protected]
  1	789-012	[email protected]
  2	345-678	[email protected]

  This table has two multi-valued dependencies. To convert it to 4NF, we separate the phone numbers and email addresses into separate tables:

  Students Table:

  StudentID
  1
  2

  PhoneNumbers Table:

  StudentID	PhoneNumber
  1	123-456
  1	789-012
  2	345-678

  EmailAddresses Table:

  StudentID	EmailAddress
  1	[email protected]
  1	[email protected]
  2	[email protected]

Summary of Normal Forms:

• 1NF: Eliminate repeating groups and ensure atomicity.
• 2NF: Eliminate partial dependencies; every non-key attribute must depend on the whole primary key.
• 3NF: Eliminate transitive dependencies; non-key attributes must not depend on other non-key attributes.
• BCNF: Ensure that every functional dependency involves a superkey.
• 4NF: Eliminate multi-valued dependencies.

Each normal form eliminates certain types of redundancy and anomalies, resulting in a well-structured database design that supports efficient data management and consistency.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as MySQL interview questions, MySQL interview experiences, and details about various MySQL job positions. Click here to check it out.