SQL Interview Questions
- INNER JOIN returns only the rows that have matching values in both tables based on the join condition specified,
- OUTER JOIN returns all rows from one or both tables, with unmatched rows filled with NULL values where the join condition is not met.
MySQL query optimization involves various techniques such as indexing, using appropriate data types, minimizing the number of queries, optimizing table structure, avoiding unnecessary calculations, and utilizing query caching.
The JOIN statement in MySQL is used to retrieve data from multiple tables based on a related column between them.
Different types of joins include:
1- INNER JOIN (returns rows when there is a match in both tables),
2- LEFT JOIN (returns all rows from the left table and matching rows from the right table),
3- RIGHT JOIN (returns all rows from the right table and matching rows from the left table),
4- FULL JOIN (returns all rows when there is a match in either table).
- MyISAM is a storage engine in MySQL known for its simplicity and speed, but lacks support for transactions and foreign keys.
- InnoDB, on the other hand, is a more robust storage engine that supports transactions, foreign keys, and row-level locking, making it suitable for mission-critical applications.
1- Flexibility: MySQL runs on all operating systems
2- Power: MySQL focuses on performance
3- Enterprise-Level SQL Features: MySQL had for some time been lacking in advanced features such as subqueries, views, and stored procedures.
4- Full-Text Indexing and Searching
5- Query Caching: This helps enhance the speed of MySQL greatly
6- Replication: One MySQL server can be duplicated on another, providing numerous advantages
7- Configuration and Security
SIX triggers are available in MySQL table.
1- BEFORE INSERT
2- AFTER INSERT
3- BEFORE UPDATE
4- AFTER UPDATE
5- BEFORE DELETE
6- AFTER DELETE
There are three types of relationships used in MySQL.
1- One-to-one:
- Elements with a one to one relationship can be included as columns in the table.
2- One-to-many:
- One to many or many to one relationships both are same. It will occur when one row in a table is related to multiple rows in different table.
3- Many-to-many:
- Many rows in a table are related to many rows in different table is called many to many relationship.
There are several storage engines in MySQL, and the table types depend on which storage engine is used. The common storage engines are:
1- InnoDB (default in MySQL for transactional data)
2- MyISAM (older, non-transactional storage engine)
3- MEMORY (stores data in memory)
4- CSV (stores data in CSV files)
5- ARCHIVE (used for storing large amounts of data, read-only)
They are a set of syntax used in SQL that have a specific job, but they have a little different
- Function: Must return a value and accept parameters only, like ( max(), min(), rand(), upper(), lower() ).
- Stored Procedures: It's optional to return a value or accept parameters, like ( rename, change, help ).
- DDL: Stands for (Data Definition Language),
It's a language to deal with database structure, like (Create, Drop, Alter)
- DML: stands for (Data Manipulation Language),
It's a language to deal with database information, like (Select, Insert, Update, Delete)