MySQL Interview Questions

SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating relational databases. It allows users to:

• Create, modify, and delete database structures (tables, views, etc.)
• Insert, update, and delete data
• Query and retrieve data from databases
• Control access to data
• Manage transactions

SQL is declarative, meaning you specify what you want to do rather than how to do it.

A database is an organized collection of data stored and accessed electronically. Databases are designed to efficiently manage large amounts of information.

A DBMS (Database Management System) is software that interacts with end users, applications, and the database itself to capture and analyze data. Examples include MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

Key functions of a DBMS:

• Data storage, retrieval, and update
• User access control
• Backup and recovery
• Data integrity enforcement
• Concurrency control

SQL commands are categorized into five main types:

1. DDL (Data Definition Language): Commands that define the database structure.
   • CREATE - Creates database objects (tables, views, etc.)
   • ALTER - Modifies database objects
   • DROP - Deletes database objects
   • TRUNCATE - Removes all records from a table
   • RENAME - Renames database objects
2. DML (Data Manipulation Language): Commands that manipulate data.
   • INSERT - Adds new records
   • UPDATE - Modifies existing records
   • DELETE - Removes records
   • MERGE - Upsert operation (insert or update)
3. DQL (Data Query Language): Commands that query data (primarily SELECT)
4. DCL (Data Control Language): Commands that control access to data.
   • GRANT - Gives privileges
   • REVOKE - Takes back privileges
5. TCL (Transaction Control Language): Commands that manage transactions.
   • COMMIT - Saves transactions
   • ROLLBACK - Undoes transactions
   • SAVEPOINT - Sets a savepoint within a transaction

To install MySQL:

1. Windows:
   • Download MySQL Installer from official website
   • Run the installer and choose "Developer Default" setup type
   • Follow the installation wizard
   • Configure MySQL Server (set root password, etc.)
   • Install MySQL Workbench (optional but recommended)
2. macOS:
   • Download DMG package from MySQL website
   • Run the installer package
   • Follow the installation steps
   • Alternatively, use Homebrew: brew install mysql
3. Linux (Ubuntu/Debian):
   • Update package index: sudo apt update
   • Install MySQL server: sudo apt install mysql-server
   • Run security script: sudo mysql_secure_installation

After installation, verify it's working by connecting to the MySQL server: mysql -u root -p

Online SQL playgrounds allow you to practice SQL without local installation:

• SQLFiddle (http://sqlfiddle.com/) - Supports multiple database systems
• DB Fiddle (https://www.db-fiddle.com/) - Modern interface with schema visualization
• SQLize.online (https://sqlize.online/) - Supports MySQL, PostgreSQL, SQLite
• OneCompiler (https://onecompiler.com/mysql) - Simple MySQL online compiler
• Rextester (https://rextester.com/l/mysql_online_compiler) - MySQL online compiler

Popular SQL IDEs (Integrated Development Environments) include:

1. MySQL Workbench (Official MySQL GUI tool)
   • Visual database design
   • SQL development
   • Database administration
   • Performance tuning
2. DBeaver (Universal database tool)
   • Supports multiple databases (MySQL, PostgreSQL, Oracle, etc.)
   • Free and open-source
   • ER diagrams
   • Data transfer between databases
3. pgAdmin (For PostgreSQL)
   • Feature-rich PostgreSQL administration
   • Query tool with syntax highlighting
   • Server monitoring
4. SQL Server Management Studio (SSMS) (For Microsoft SQL Server)
5. Oracle SQL Developer (For Oracle Database)
6. HeidiSQL (Lightweight MySQL client)
7. TablePlus (Modern, native client with elegant UI)

To create a database in MySQL:

CREATE DATABASE database_name;
-- Example:
CREATE DATABASE company;

To verify the database was created:

SHOW DATABASES;

To use the database:

USE database_name;
-- Example:
USE company;

Basic table creation syntax:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    table_constraints
);

Example:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

To verify the table was created:

SHOW TABLES;

To view the table structure:

DESCRIBE table_name;
-- Or:
SHOW CREATE TABLE table_name;

MySQL supports various data types:

1. Numeric Types:
   • INT - Integer (4 bytes)
   • SMALLINT - Small integer (2 bytes)
   • BIGINT - Large integer (8 bytes)
   • DECIMAL(p,s) - Fixed-point number
   • FLOAT - Single-precision floating-point
   • DOUBLE - Double-precision floating-point
2. String Types:
   • CHAR(n) - Fixed-length string
   • VARCHAR(n) - Variable-length string
   • TEXT - Long text data
   • ENUM - String with one value from a list
   • SET - String with zero or more values from a list
3. Date and Time Types:
   • DATE - Date (YYYY-MM-DD)
   • TIME - Time (HH:MM:SS)
   • DATETIME - Date and time (YYYY-MM-DD HH:MM:SS)
   • TIMESTAMP - Timestamp (Unix epoch)
   • YEAR - Year (4-digit format)
4. Binary Types:
   • BINARY - Fixed-length binary data
   • VARBINARY - Variable-length binary data
   • BLOB - Binary large object
5. Boolean Type:
   • BOOLEAN or BOOL - Synonym for TINYINT(1) (0 = false, 1 = true)

Constraints enforce rules on data columns:

1. PRIMARY KEY - Uniquely identifies each record

   CREATE TABLE table_name (
       id INT PRIMARY KEY,
       ...
   );

2. FOREIGN KEY - Ensures referential integrity

   CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );

3. NOT NULL - Column cannot contain NULL values

   CREATE TABLE table_name (
       column_name data_type NOT NULL,
       ...
   );

4. UNIQUE - All values in column must be different

   CREATE TABLE table_name (
       email VARCHAR(100) UNIQUE,
       ...
   );

5. DEFAULT - Sets a default value for column

   CREATE TABLE table_name (
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       ...
   );

6. CHECK - Ensures values meet specific conditions

   CREATE TABLE employees (
       salary DECIMAL(10,2) CHECK (salary > 0),
       ...
   );

7. AUTO_INCREMENT - Automatically generates sequential numbers (MySQL specific)

   CREATE TABLE table_name (
       id INT PRIMARY KEY AUTO_INCREMENT,
       ...
   );

The SELECT statement retrieves data from one or more tables:

-- Basic syntax
SELECT column1, column2, ...
FROM table_name;

Examples:

-- Select all columns from a table
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name, salary FROM employees;

-- Select with column aliases
SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees;

-- Select with expressions
SELECT first_name, last_name, salary * 12 AS annual_salary FROM employees;

-- Select distinct values
SELECT DISTINCT department_id FROM employees;

-- Select with table alias
SELECT e.first_name, e.last_name, d.department_name 
FROM employees e 
JOIN departments d ON e.department_id = d.department_id;

The WHERE clause filters records based on specified conditions:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Examples:

-- Simple equality
SELECT * FROM employees WHERE department_id = 10;

-- Greater than
SELECT * FROM employees WHERE salary > 50000;

-- Multiple conditions with AND
SELECT * FROM employees 
WHERE salary > 50000 AND department_id = 10;

-- Using OR
SELECT * FROM employees 
WHERE department_id = 10 OR department_id = 20;

-- Using NOT
SELECT * FROM employees 
WHERE NOT department_id = 10;

-- Combining conditions with parentheses
SELECT * FROM employees 
WHERE (department_id = 10 AND salary > 50000) 
   OR (department_id = 20 AND salary > 60000);

Comparison operators compare values in SQL:

The ORDER BY clause sorts the result set:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Examples:

-- Sort by single column ascending (default)
SELECT * FROM employees ORDER BY last_name;

-- Sort by single column descending
SELECT * FROM employees ORDER BY salary DESC;

-- Sort by multiple columns
SELECT * FROM employees 
ORDER BY department_id ASC, salary DESC;

-- Sort by column position (not recommended)
SELECT first_name, last_name, salary 
FROM employees 
ORDER BY 3 DESC; -- 3 refers to salary column

The LIMIT clause restricts the number of rows returned:

SELECT column1, column2, ...
FROM table_name
LIMIT number;

Examples:

-- Get first 10 records
SELECT * FROM employees LIMIT 10;

-- Get records 11-20 (skip 10, take 10)
SELECT * FROM employees LIMIT 10, 10;

-- Alternative syntax (MySQL 5.0+)
SELECT * FROM employees LIMIT 10 OFFSET 10;

-- With ORDER BY (get top 5 highest paid employees)
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 5;

Note: In other databases, similar functionality is achieved with:

• SQL Server: SELECT TOP 10 * FROM employees
• Oracle: SELECT * FROM employees WHERE ROWNUM <= 10
• PostgreSQL: SELECT * FROM employees LIMIT 10 OFFSET 10

The INSERT statement adds new records to a table. You can insert single or multiple rows at once, and specify which columns to populate.

The UPDATE statement modifies existing records in a table. You specify which columns to change and the new values, along with conditions to identify which rows to update.

DELETE removes specific rows from a table based on a condition and can be rolled back. TRUNCATE removes all rows from a table, resets auto-increment counters, and typically can't be rolled back.

IN checks if a value matches any value in a list, while NOT IN checks if a value doesn't match any value in a list. They provide a concise alternative to multiple OR conditions.

BETWEEN filters results within a specified range (inclusive). It's commonly used with numeric values and dates.

LIKE performs pattern matching with wildcards: % matches any sequence of characters, and _ matches any single character. For example, 'A%' matches any string starting with A.

Use IS NULL to find NULL values and IS NOT NULL to find non-NULL values. Regular comparison operators don't work with NULL because NULL represents unknown/undefined.

Common JOIN types are: INNER JOIN (returns matching rows), LEFT JOIN (all rows from left table + matches from right), RIGHT JOIN (all rows from right table + matches from left), and FULL JOIN (all rows when there's a match in either table).

A self-join is when a table is joined with itself, typically to compare rows within the same table or to represent hierarchical relationships.

UNION combines results from multiple SELECT statements and removes duplicates, while UNION ALL keeps all rows including duplicates.

CROSS JOIN produces a Cartesian product, combining each row from the first table with every row from the second table, resulting in m×n rows.

Common aggregate functions include COUNT (count rows), SUM (total of values), AVG (average), MIN (minimum value), and MAX (maximum value).

GROUP BY groups rows that have the same values in specified columns, typically used with aggregate functions to return summary rows for each group.

WHERE filters rows before grouping, while HAVING filters groups after the GROUP BY clause. HAVING can use aggregate functions in its conditions.

A subquery is a query nested inside another query (SELECT, INSERT, UPDATE, or DELETE). It can return a single value, multiple values, or a table result set.

Subqueries can be single-row (return one row), multi-row (return multiple rows), or correlated (reference columns from the outer query). They can appear in SELECT, FROM, WHERE, and HAVING clauses.

Operators like IN, NOT IN, ANY, ALL, and EXISTS work with subqueries. Single-row subqueries can use comparison operators (=, >, <, etc.).

A view is a virtual table based on the result set of a SQL query. It doesn't store data itself but provides a way to simplify complex queries, restrict access to data, or present data in a specific format.

Indexes are special lookup tables that speed up data retrieval. They work like a book index, allowing the database to find data without scanning the entire table. Proper indexing significantly improves query performance.

Create indexes on columns frequently used in WHERE clauses, JOIN conditions, or as sorting criteria. However, avoid over-indexing as indexes slow down INSERT, UPDATE, and DELETE operations.

Common string functions include CONCAT (combine strings), SUBSTRING (extract part of string), UPPER/LOWER (change case), TRIM (remove spaces), and LENGTH (string length).

Common date functions include NOW() (current date/time), DATE() (extract date part), DATEDIFF (difference between dates), DATE_ADD/DATE_SUB (add/subtract time intervals), and DATE_FORMAT (format date).

Common numeric functions include ROUND (round numbers), ABS (absolute value), CEIL/FLOOR (round up/down), MOD (remainder), and RAND (random number).

A transaction is a sequence of operations performed as a single logical unit of work. It either completes entirely (commit) or has no effect at all (rollback), ensuring data integrity.

ACID stands for Atomicity (all or nothing), Consistency (valid state transitions), Isolation (concurrent transactions don't interfere), and Durability (committed changes persist).

Use BEGIN TRANSACTION to start, COMMIT to save changes permanently, and ROLLBACK to undo changes. SAVEPOINT creates points within a transaction to roll back to.

A stored procedure is a prepared SQL code that you can save and reuse. It can accept parameters, perform operations, and return results, reducing network traffic and improving performance.

Advantages include better performance (precompiled), reduced network traffic, improved security (access control), code reusability, and easier maintenance.

Stored procedures perform actions and may return multiple results, while functions return a single value and can be used in SQL statements. Functions are typically used in expressions.