MySql Interview Question & Answer

❮ Previous Next ❯

What is Mysql?

MySQL is a widely used relational database management system (RDBMS).

MySQL is free and open-source.

SQL-based: Uses Structured Query Language (SQL) to manage and manipulate data.

MySQL was first released in 1995

MySQL was founded by Michael "Monty" Widenius, along with David Axmark and Allan Larsson

Type of SQl Commands?

A Data Definition Language(DDL) Commands like create, alter and drop to define database structure.

Data Manipulation Language(DML)Commands like select, insert,update and delete for data operatioins

Data Control Language(DCL) Commands like grant and revoke to manage permissions

Transaction control language(TCL) Commands like commit , rollback and savepoint to maange transaction.

How is DATE different from DATETIME in MySQL?

The DATE function in MySQL stores the date in year, month, and day format: YYYY-MM-DD

However, the DATETIME function stores the date with the time, and it looks like this: YYYY-MM-DD HH:MM:SS

What are the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

INNER JOIN: Returns rows where there is a match in both tables.

LEFT JOIN: Returns all rows from the left table and matching rows from the right table. If there’s no match, NULL is returned for the right table's columns.

RIGHT JOIN: Similar to LEFT JOIN, it returns all rows from the right table and matching rows from the left.

FULL JOIN: Combines the LEFT JOIN and RIGHT JOIN results, including unmatched rows from both tables.

Self JOIN:Self join is a join in which a table is joined with itself.

Select a.column1,b.column2 from table_name a join table_name b on a.column= b.column

What is the difference between DELETE, TRUNCATE, and DROP in MySQL?

DELETE: Removes rows from a table based on a condition. It can be rolled back if inside a transaction.

TRUNCATE: Deletes all rows from a table, but the table structure remains intact. It is faster than DELETE and cannot be rolled back.

DROP: Completely removes the table structure and data, along with any dependencies like indexes.

What is a subquery in MySQL?

A subquery (also known as a nested query) is nested inside another query.

SELECT first_name, last_name, salary

FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees );

What is a view in MySQL?

A view is a saved query that works like a virtual table.

you can treat a view like regular table in select, inset, update and delete.

Create view view_name as select column1, column2, from table_name where condition and drop view view_name

 CREATE VIEW employee_details AS SELECT e.id, e.name, d.department_name,  
  FROM employees e JOIN departments d ON e.department_id = d.department_id;

What is a trigger in MySQL?

a trigger is a set of actions that run when a database event occurs. Triggers can be configured to execute before or after events like INSERT, UPDATE, or DELETE.

 CREATE TRIGGER after_order_insert 

  {BEFORE|AFTER(INSERT|UPDATE|DELETE)} INSERT ON orders FOR EACH  ROW 

  BEGIN 
  

  INSERT INTO order_history (order_id, action, timestamp) VALUES (NEW.order_id, 'inserted', NOW()); 
END;

What are transactions in MySQL?

A transaction is a sequence of operations performed as a single unit. It follows the ACID properties.


  START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

How do you optimize a slow query in MySQL??

Use EXPLAIN to analyze the query.

Use Add appropriate indexes.

Avoid SELECT *, Remove unnecessary join, remove unnecessary database connection need of, use limit when possible.

Avoid subqueries when possible, Normalize the database

some data store in locatization store local

Fetch data need of

What is store engine in database?

A storage engine (also called a table type) is the software component of a database that handles how data isstored, managed, and retrieved at the physical level..

Common Storage Engines in MySQL:

InnoDB:Default engine, supports transactions, row-level locking, foreign keys, and crash recovery.

it supports foreign key constraints for relational integrity

ACID Compliance Ensure Atomicity, consistency, Isolation, and Durability

MyiSAM: it is doesnot support transaction meaning it does not have rollback capability.

MEMORY: Stores data in RAM for fast access. Volatile (data lost after restart).

CSV: Stores data in comma-separated text files. Simple but not efficient for large data..

ARCHIVE: Good for storing large historical data with high compression. No indexes.

BLACKHOLE: Accepts data but does not store it (used for replication or testing).

What are the ACID properties in MySQL?

Atomicity The transfer is an all-or-nothing operation. If $100 is deducted from Account A, the same $100 will be added to Account B, or nothing happens (if a failure occurs).

Consistency After the transaction, both accounts must reflect the correct balance according to the rules of the system. The balances must not go negative (if not allowed), and all business rules must be respected.

Isolation If another transaction tries to read or modify the same accounts while the first one is in progress, the second transaction must either wait or operate on a snapshot of the data to avoid inconsistencies. The changes from the first transaction are invisible to the second until it is completed.

Durability After the transfer is committed, even if the database crashes, the changes to the account balances will be recovered and will not be lost.

What is Store Procedure in database?

A Stored Procedure is a predefined set of SQL statements that are stored in the database and can be executed (called) repeatedly. It is like a function in programming and To improve performance

Syntax (MySQL Example):


    DELIMITER // 

    CREATE PROCEDURE getUsers() 

    BEGIN 

    SELECT * FROM users; 

    END 

    // DELIMITER ;
    
    CALL getUsers();

What is an Index in a Database?

Index Indexes are used to retrive data from the database very fast. the use cannot see the indexes , they are just used to speed up searches query.

An index in a database is a data structure that improves the speed of data retrieval operations on a table.

To increase query performance, especially with large tables.

To speed up searches, sorting, filtering, and JOIN operations.

To enforce constraints like UNIQUE.

Types of Indexes:.

Primary Index: Automatically created on the primary key column, and Ensures uniqueness.

CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100) );
Unique Index: nsures all values in the column are unique (no duplicates).

CREATE UNIQUE INDEX idx_email ON users(email);
Composite (Multi-column) Index: An index on two or more columns.

CREATE INDEX idx_name_email ON users(name, email);
Full-Text Index:Used for searching text, like articles, blogs, etc.

CREATE FULLTEXT INDEX idx_content ON posts(content);
Spatial Index: Used for geographic data (e.g., maps, coordinates).
Available in spatial databases like MySQL with GIS support.

CREATE FULLTEXT INDEX idx_content ON posts(content);

What is SQL Injection?

SQL Injection is a security vulnerability that allows an attacker to interfere with the SQL queries your application sends to the database.

Use Prepared Statements / Parameterized Queries

$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?"); 

    
  $stmt->bind_param("ss", $username, $password); 

  $stmt->execute();

Unauthorized access to sensitive data (like passwords, credit card numbers)

Bypassing login authentication

Modifying or deleting data

Taking full control of the database server

SQL injection is a code injection technique that might destroy your database.

SQL Injection is one of the most common web hacking technique get request string ('userid').

THe sql above is valid and will return all rows from the user table since or 1=1 is always true.

What is an alter table in a Database?

Alter Table statment is used to add, delete or modify columns in an existing table.

Alter table table_name add colomn varchar(255).

Alter table table_name Drop colomn varchar(255).

Alter table table_name rename colomn varchar(255).

Common separate value find in sql?

Group_concatgroup_concat(product_name order by product_name form orders group by order_id).

What is Unique, Primary key, not null, foreign key?

Not null column cannot have a null value , no value.

Uniqu All value in a column are defferent.

Primary key Uniquely identifies each row in table and combination of not null and unique.

Foreign Key: When the foreign key creat other table is primary key.

What is DeadLock?

Dead Lock in a database happens when two or more transaction are waiting on each other to release lock , evething get stuck consistant locking order.

Person A picks up Pen X and waits for Pen Y.
Person B picks up Pen Y and waits for Pen X.
Now, A wants Y, and B wants X.
Both are waiting on each other — forever.
Result: Deadlock.

Update field name female to F and male to M and other mail = "" and namuber=0

 
UPDATE your_table_name

SET

    gender = CASE

        WHEN gender = 'female' THEN 'F'

        WHEN gender = 'male' THEN 'M'

        ELSE gender

    END,

    mail = ""

    namuber = 1;

What is Normalization in DBMS?

Normalization is the process of organizing data in a database to:

Reduce data redundancy (repeating data)

Improve data integrity

Ensure efficient storage and retrieval

1NF Remove duplicate comuns and use atomic (indivisible) values.

2NF Remove partial dependencies (non-key fields must depend on whole key).

3NF Remove transitive dependencies (non-key fields should not depend on other non-key fields).

BCNF Strengthens 3NF by ensuring that all non-trivial dependencies are on superkeys.

What is sql thread?

SQL thread same as database systems, a SQL thread usually refers to a server-side execution thread that is handling an SQL query or task. Most commonly, the term is used in replication or multi-threaded operations.

In Mysql : SHOW PROCESSLIST

In Postgre SQL : select * from pg_stat_activity

Write Max Salary and second max salary in mysql?

Max Salary

Select Max(salary) from table_name order by salary desc limit 1

Socond Salary

Select salary from table_name order by salary desc limit 1 OFFSET 1

Third Salary

Select salary from table_name order by salary desc limit 1 OFFSET 2

Third Salary

 Select max(salary) as third_highest_salary from table_name 

    where salary < ( select max(salary) from table_name 

    where salary < (select max(salary) from table_name))

Write query who user amount greater than 5000rs?

Select user.name from user join order on users.id= order.user_id where order.amount > 5000

Write query if get user name,total order amount greated than 5000 and find amount last six months?

 Select user.name sum(order.amount) as total_order from users join order on users.id= orders.user_id group by users.name  having total_order>5000 and order.created > date_sub(now(), interval 6 months)

What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation..

HAVING filters groups after aggregation.


      SELECT department, COUNT(*) 

FROM employees 

GROUP BY department 

HAVING COUNT(*) > 5;

What is the difference between CHAR and VARCHAR

CHAR datatype is used to store character strings of fixed length

CHAR stands for "Character"

CHAR takes 1 byte for each character

Uses a fixed memory space (for example, CHAR(10) if I store 3 bytes. That means 7 bytes of memory is wasted.

Can waste space if data is shorter than the specified length.

VARCHAR Variable length. Reserves only required space.

Uses only the amount of storage needed for the actual string.

ARCHAR(10) stores up to 10 characters but uses only as much space as required..

What is the difference between primary key and unique constraints?

Aspect	Primary Key	Unique Key
Uniqueness	Ensures uniqueness for the column(s).	Ensures uniqueness for the column(s).
Nullability	Cannot accept `NULL` values.	Can accept `NULL` values.
Number per Table	A table can only have one primary key.	A table can have multiple unique keys.
Example	`EmployeeID INT PRIMARY KEY`	`Email VARCHAR(100) UNIQUE`

What is the difference between TRUNCATE and DROP statements?

DROP	TRUNCATE
The DROP command is used to remove the table definition and its contents.	Whereas the TRUNCATE command is used to delete all the rows from the table.
In the DROP command, table space is freed from memory.	While the TRUNCATE command does not free the table space from memory.
DROP is a DDL(Data Definition Language) command.	Whereas the TRUNCATE is also a DDL(Data Definition Language) command.
In the DROP command, a view of the table does not exist.	While in this command, a view of the table exists.
In the DROP command, integrity constraints will be removed.	While in this command, integrity constraints will not be removed.
In the DROP command, undo space is not used.	While in this command, undo space is used but less than DELETE.
The DROP command is quick to perform but gives rise to complications.	While this command is faster than DROP.

write a Queue in MySQL?

Queue Queue is not a build in feature but can be implement using standard database operations like inserting, updating and selecting data.

Queue are processed in a First in first out (FIFO) feature.

Queue feture are use sending email, prossing updated.



      create table job_queue (

        id int auto_increment primary ky,

        task_name varchar (255) not null,

        status enum('pending','processing','completed','faild'),

        created_at timestamp default current_timestamp,

        updated_at timestamp default current_timestamp,

      
      )

write query second table not exist record MySQL?


      Select table1.* from table1 where table1.id not in (select table2id form table2)

How to one increase sttaus not in table2?

table1
id name status
1 a 2
2 b 0
3 c 1
4 d 4
5 e 6
6 f 9
table2
id user_id
1 4
2 3


      Update table1 set status = status +1 where id not in (select id from table2)

Difference between Commit and Rollback commands?

COMMIT	ROLLBACK
COMMIT permanently saves the changes made by the current transaction.	ROLLBACK undo the changes made by the current transaction.
The transaction can not undo changes after COMMIT execution.	Transaction reaches its previous state after ROLLBACK.
When the transaction is successful, COMMIT is applied.	When the transaction is aborted, ROLLBACK occurs.

How to select query not in table2 ?

user
id name status
1 a 2
2 b 0
3 c 1
4 d 4
5 e 6
6 f 9
table2
id user_id
1 4,5
2 3,2
3 6


      Select a.id, a.name, from user not find_in_set(a.id, (select id from table where id=1))

How to create table dynamics in sql?


      schema::create('table_name', function(Blueprint $table){


        $table->increments('id');

        $table->varchar('name',255)->nullable();

        $table->varchar('email',255)->nullable();

        $table->string('mobile');

        $table->tinyInterger('demo_attended');

        $table->text('remarks');

        $table->timestamp('deleted_at')->nullable();

      });