We have 2 tables Country and City as shown below: Country (countryid, countryname ) City (countryid, cityname ) Q1. How do you get the countries that has no cities? Q2. How do you get the countries that has less than 3 cities and also make sure the countries with no cities also show up? Solution: … Read More →
Mysql Query – Set 1
We have Employee,Department,Region tables as shown below: Employee(EmpID, EmpName, Salary) Department(DeptID, EmpID, DeptName) Region(RegnID, DeptID, RegnName ) Now get all employee details according to the region (RegnName) they belong? Solution: If you want to have all of the employees in the results, despite missing department and region data then you need to use OUTER joins:
Explain advantages of MyISAM over InnoDB?
MyISAM manages nontransactional tables. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file … Read More →
Find the Nth Highest Salary in a table.
In previous database posts we have seen how to retrieve 2nd highest salary (in whole table and for each department). But, what if we want to find the Nth highest salary, where N can be any number whether it’s the 3rd highest, 4th highest, 5th highest, 10th highest, etc?. EmpName Salary Steve 150 Mark 100 … Read More →
Find second Highest Salary for each department.
In previous mysql post, we found the second highest salary in employee table. In this problem we would like to find second highest salary for each department. EMPLOYEE TABLE EmpName Salary Department Steve 150$ Finance James 120$ IT Andrew 120$ Finance Mark 100$ Back-Office Adam 110$ IT Lewis 130$ Finance Smith 125$ Back-Office Solution : … Read More →
Find second highest salary in employee table
Our problem is to find 2nd highest salary from the employee table using mysql . EMPLOYEE TABLE Steve 150$ James 120$ Andrew 120$ Mark 100$ First Method: We will sort the salaries in descending order and will limit the solution by one. But this will not give all the results if there are two employees … Read More →
What’s going on ?