1) What is MySQL?
MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation.
MySQL is a database system that runs on a server
MySQL is very fast, reliable, and easy to use
MySQL is a multithreaded, multi-user SQL database management system
MySQL Server works in client/server or embedded systems.
MySQL is free to download and use
2) What is Mysql Storage Engine?
Stroage engine to use its own strategies for storing, locking and retrieving data
MyISAM:
* Table Locking
* Disk Storage
* No Transactions
* B-Tree Indices
* R-Tree Indices
* Full-Text Searches
InnoDB:
InnoDB was first released as part of MySQL in mid-2001.
Beginning with version 4.0, InnoDB is a standard component of MySQL.
* Row Locking
* Disk Storage
* Transactions
* B-Tree Indices
* Foreign Key Constraints
* Crash Recovery
BDB:
* Transactions
* Page-level locking
Heap:
* Heap table don't use disk storage at all. They are stored completely in memory.
* Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables.
* They are used for high speed storage on temporary basis.
* They do not allow BLOB or TEXT fields.
3) How many Triggers are possible in MySQL?
There are only six Triggers allowed to use in MySQL database.
* Before Insert
* After Insert
* Before Update
* After Update
* Before Delete
* After Delete
4) What is SubQuery?
- Query inside another query is called Sub Query.
- There are two kind of subquery in SQL one is called non-correlated and other is called correlated subquery.
- Subquery normally return an scaler value as result or result from one column if used along with IN Clause.
Inner query:
SQL query which is on inner part of main query is called inner query.
Outer query:
SQL query which is on outer part of main query is called outer query
5) What is Noncorrelated SubQuery?
- In non corelated subquery, inner query doesn't depend on outer query and can run as stand alone query.
- Subquery used along-with IN or NOT IN sql clause is good examples of Noncorrelated subquery in SQL.
Example 1:
mysql> select * from stock;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
| INDIGO | INDIGO Airlines | NULL |
| INFY.BO | InfoSys | BO |
| VOD.L | Vodafone Group PLC | L |
+---------+-------------------------+--------------------+
6 rows in set (0.00 sec)
mysql> select from Market;
+------+-------------------------+---------------+
| RIC | NAME | COUNTRY |
+------+-------------------------+---------------+
| T | Tokyo Stock Exchange | Japan |
| O | NASDAQ | United States |
| N | New York Stock Exchange | United States |
| BO | Bombay Stock Exchange | India |
+------+-------------------------+---------------+
4 rows in set (0.00 sec)
Query : Find all stocks from Japan?
mysql> SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE = (SELECT RIC FROM Market WHERE COUNTRY='Japan');
+---------+
| COMPANY |
+---------+
| Sony |
+---------+
1 row IN SET (0.02 sec)
Example 2:
Query: Find all stocks from United States and India
NonCorrelated Subquery Example with IN Clause SQL
mysql> SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE IN (SELECT RIC FROM Market WHERE COUNTRY='United States' OR COUNTRY= 'INDIA');
+-------------------------+
| COMPANY |
+-------------------------+
| Google Inc |
| Goldman Sachs GROUP Inc |
| InfoSys |
+-------------------------+
6) What is Correlated SubQuery?
- Inner query or subquery reference outer query is called Correlated SubQuery.
- Outer query needs to be executed before inner query.
- One of the most common example of correlated subquery is using keywords exits and not exits.
- An important point to note is that correlated subqueries are slower queries and one should avoid it as much as possible.
Example:
Here is an example of Correlated subquery “Return all markets which has at least one stock listed on it.”
mysql> SELECT m.NAME FROM Market m WHERE m.RIC = (SELECT s.LISTED_ON_EXCHANGE FROM Stock s WHERE s.LISTED_ON_EXCHANGE=m.RIC);
+-------------------------+
| NAME |
+-------------------------+
| Tokyo Stock Exchange |
| NASDAQ |
| New York Stock Exchange |
| Bombay Stock Exchange |
+-------------------------+
4 rows IN SET (0.00 sec)
7) Difference between Correlated and NonCorrelated Subquery?
1.In case of correlated subquery inner query depends on outer query while in case of noncorrelated query inner query or subquery doesn't depends on outer query and run by its own.
2.In case of correlated subquery, outer query executed before inner query or subquery while in case of NonCorrelated subquery inner query executes before outer query.
3.Correlated Sub-queries are slower than non correlated subquery and should be avoided in favor of sql joins.
4.Common example of correlated subquery is using exits and not exists keyword while non correlated query mostly use IN or NOT IN keywords.
8) Usage of "=", "IN or NOT IN", "EXISTS or NOT EXISTS" in Subquery?
Use "=" , when subquery return single value on (single column)
Use "IN or NOT IN" clause, when subquery return multiple value on (single column)
Use "EXISTS or NOT" EXISTS clause, when subquery return multiple value on (multiple column)
9) How to get the second highest salary from the Employee table ?
Employee table
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
+----+--------+
Solution 1:
Second Highest Salary in MySQL without LIMIT
SELECT MAX(salary) FROM Employee WHERE salary NOT IN (SELECT MAX(salary) FROM Employee);
Solution 2:
Second Highest Salary in MySQL using sub query
SELECT MAX(Salary) From Employee WHERE Salary < (SELECT Max(Salary) FROM Employee);
Solution 3:
Second Highest Salary in MySQL with LIMIT
Formula : nth highest salary n=n-1, Here we need to fine 2th higest salary, So n is 1
SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT 1,1
Solution 4:
Second Highest Salary in MySQL using Correlated SubQuery
SELECT Salary FROM Employee e WHERE 2=(SELECT COUNT(DISTINCT Salary) FROM Employee p WHERE e.Salary<=p.Salary)
Solution 5:
Second Highest Salary in MySQL using Correlated SubQuery
Here we need to fine 2th higest salary, So N is 2
SELECT * FROM Employee Emp1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)
Solution 6:
Second Highest Salary in MySQL using <> operator
select MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee )
Solution 7:
Second Highest Salary in MySQL using limit
SELECT Salary FROM (SELECT distinct(Salary) FROM Employee ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1
Solution 8:
Second Highest Salary using SQL Server Top Keyword
SELECT TOP 1 Salary FROM ( SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS MyTable ORDER BY Salary ASC;
Solution 9:
Second Highest Salary in mysql using LIMIT WITH OFFSET
Formula : OFFSET (n - 1), So n is 1
SELECT distinct(salary) FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1
Solution 10:
Get the third highest salary in mysql using @rank
SELECT * FROM (SELECT @rank := @rank + 1 AS rank, salary FROM employee,(SELECT @rank := 0) r order by salary desc ) m
WHERE rank=3