SQL Real Time Interview Questions with solutions

SQL_Query: In this MySQL challenge, your query should return the names of the people who are reported to (excluding null values), the number of members that report to them, and the average age of those members as an integer. The rows should be ordered by the names in alphabetical order. Your output should look like the following table.

Solution:

SELECT ReportsTo, count(ReportsTo) as Members,

 round(avg(Age), 0) as 'Average Age' 

 FROM maintable_MS9H0

Where ReportsTo is not null

Group by ReportsTo

order by ReportsTo

or

SELECT ReportsTo,COUNT(FirstName) as Members,ROUND(AVG(Age)) as "Average Age" 

FROM maintable_MS9H0 

WHERE ReportsTo IS NOT NULL 

GROUP BY ReportsTo

SQL_Query: In this MySQL challenge, the table provided shows all new users signing up on a specific date in the format YYYY-MM-DD. Your query should output the change from one month to the next. Because the first month has no preceding month, your output should skip that row. Your output should look like the following table.

Solution:

Select Month, MonthToMonthChange

from 

    (select yyyymm, Month,

    if(@last_entry = 0, 0, y.count - @last_entry) as MonthToMonthChange  ,

    @last_entry := y.count

    from

    (select @last_entry := 0) x,

    (SELECT Monthname(DateJoined) as Month,EXTRACT( YEAR_MONTH FROM DateJoined )  as yyyymm,  Count(*) as count

FROM maintable_D03TI

Group by yyyymm

order by yyyymm) y) a

where MonthToMonthChange!=0

order by yyyymm

or

SELECT MONTHNAME(STR_TO_DATE(e1.Month, '%m')) AS "Month",

e1.Total - e2.Total AS "MonthToMonthChange" FROM (

SELECT MONTH(Datejoined) AS "Month", COUNT(ID) AS "Total"

FROM maintable_D03TI

GROUP BY MONTH(Datejoined)

ORDER BY Month

) AS e1

LEFT JOIN 

(

SELECT MONTH(Datejoined) AS "Month", COUNT(ID) AS "Total"

FROM maintable_D03TI

GROUP BY MONTH(Datejoined)

ORDER BY Month

) AS e2

ON e1.Month = e2.Month + 1

Where e1.Month > 1

SQL_Query: In this MySQL challenge, your query should return the information for the employee with the third highest salary. Write a query that will find this employee and return that row, but then replace the DivisionID column with the corresponding DivisionName from the table cb_companydivisions. You should also replace the ManagerID column with the ManagerName if the ID exists in the table and is not NULL.Your output should look like the following table.

Solution:

select a.ID, a.Name, d.DivisionName as DivisionName, b.Name as ManagerName, a.Salary

from maintable_GAHHO a

left join cb_companydivisions d on d.ID = a.DivisionID

left join maintable_GAHHO b on b.ID = a.ManagerID

order by a.Salary desc limit 2,1

or

SELECT A.ID,A.Name, b.DivisionName,c.Name 'ManagerName', a.Salary FROM (

  SELECT  * FROM maintable_GAHHO 

  ORDER BY SALARY DESC

  LIMIT 3) A

  JOIN CB_COMPANYDIVISIONS B

  ON A.DIVISIONID = B.ID

left JOIN maintable_GAHHO c on a.managerid =c.id

  ORDER BY a.SALARY ASC

  LIMIT 1

SQL_Query: In this MySQL challenge, your query should return the vendor information along with the values from the table cb_vendorinformation. You should combine the values of the two tables based on the GroupID column. The final query should consolidate the rows to be grouped by FirstName, and a Count column should be added at the end that adds up the number of times that person shows up in the table. The output table should be sorted by the Count column in ascending order and then sorted by CompanyName in alphabetical order. Your output should look like the following table.

Solution:

SELECT 

  ma.GroupID as GroupID,

  FirstName,

  LastName,

  Job,

  ExternalID,

  CompanyName,

  Count(*) as "Count"

 FROM maintable_BXB84 ma JOIN

  cb_vendorinformation cb ON ma.groupid = cb.groupid

 GROUP BY 2 DESC

 ORDER BY 7 ASC

or

SELECT c.GroupID, FirstName, LastName, Job, ExternalID, CompanyName,  count(*) as Count FROM maintable_BXB84 m

inner join cb_vendorinformation c on c.GroupID=m.GroupID

group by FirstName

order by Count asc,CompanyName

SQL_Query: In this MySQL challenge, your query should return the vendor information along with the values from the table cb_vendorinformation. You should combine the values of the two tables based on the GroupID column. The final query should only print out the GroupID, CompanyName, and final count of all rows that are grouped into each company name under a column titled Count. The output table should be then sorted by the Count column and then sorted by GroupID so that a higher number appears first. Your output should look like the following table.

Solution:

SELECT A.GroupID,  CompanyName , Count(A.GroupID) Count

FROM maintable_NONR0 A LEFT JOIN cb_vendorinformation B 

ON A.GroupID = B.GroupID

GROUP BY 1,2

ORDER BY Count(A.GroupID)

or

SELECT m.GroupId, c.CompanyName, count(*) as count 

FROM maintable_NONR0 m

join cb_vendorinformation c on c.GroupID = m.GroupID

group by CompanyName

order by count, GroupID ASC

SQL_Query: In this MySQL challenge, your query should return the number of rows from your table where FirstName contains "e" and LastName has more than 5 characters. Your output should look like the following table.

Solution:

SELECT COUNT(*)

FROM maintable_8KPJZ

WHERE FirstName LIKE '%e%'

AND LENGTH(LASTNAME) > 5;

or

SELECT count(*) FROM maintable_8KPJZ

where FirstName like '%e%' and 

CHAR_LENGTH(LastName) > 5;

SQL_Query: In this MySQL challenge, your query should return all the people who report to either Jenny Richards or have a NULL value in ReportsTo. The rows should be ordered by Age. Your query should also add a column at the end with a title of Boss Title where the value is either None or CEO. Your output should look like the following table.

Solution:

SELECT FirstName,LastName,ReportsTo,Position,Age,

case reportsto when 'Jenny Richards' then 'CEO' else 'None' end "Boss Title" 

FROM maintable_E4WVA v

where reportsto='Jenny Richards' or reportsto is null

order by age

or

SELECT t1.FirstName, t1.LastName, t1.ReportsTo, t1.Position, t1.Age, 

IFNULL(t2.Position, 'None') 'Boss Title' 

FROM maintable_E4WVA t1 

LEFT JOIN (

  SELECT CONCAT(FirstName, ' ', LastName) 'FullName', Position

  FROM maintable_E4WVA) t2

  ON t1.ReportsTo = t2.FullName

WHERE ReportsTo = 'Jenny Richards' OR ReportsTo IS NULL

ORDER BY Age;

SQL_Query: In this MySQL challenge, your query should return the rows from your table where LastName = Smith or FirstName = Robert and the rows should be sorted by Age in ascending order. Your output should look like the following table.

Solution:

SELECT * FROM maintable_0EL1V 

where LastName = 'Smith' or FirstName = 'Robert' 

order by age

SQL_Query: In this MySQL challenge, your query should return LastName and the sum of Age from your table for all users with a LastName = Smith. The column title of the summed ages should be SumAge. Your output should look like the following table.

Solution:

SELECT LastName, SUM(Age) SumAge

FROM maintable_YSLUZ

WHERE LastName='Smith'

SQL_Query: In this MySQL challenge, your query should return FirstName, LastName, and the Age from your table for all users who are above the age of 25 ordered by ID in ascending order. Your output should look like the following table.

Solution:

SELECT FirstName, LastName, Age

FROM maintable_6V2JJ

WHERE Age > 25

ORDER BY ID ASC;