Employee Id, Employee Name and Manager Id Self Join SQL Query as Interview Question

February 10, 2014 7 comments

One of the most common SQL interview question related to Self Join is that one Employee table with three columns Employee Id, Employee Name and Manager Id.

The interviewer may ask you questions like the following:

  1. Find the employees who are managers.
  2. Find the managers with the count of subordinates
  3. Find the  managers with the count of subordinates greater than 1 (or any other number)

Lets try resolve this  with the basic understanding – what is Self Join?: “A self join is a join in which a table is joined with itself (which is also called Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.” – W3Reaource

At first create an Employee table and insert some dummy records into it. Please refer to the  CREATE and INSERT statements below:

Create Employee Table:

CREATE TABLE employees (

emp_id int(11) NOT NULL,
emp_name varchar(45) DEFAULT NULL,
mng_id int(11) DEFAULT NULL,
PRIMARY KEY (emp_id)
)

Insert Dummy Records:

INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (1,'Joe','2');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (2,'Green',NULL);
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (3,'Jen','2');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (4,'Brown','1');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (5,'Harry','1');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (6,'Jenny','4');
  1. SQL Statement for “Find the employees who are managers”:
    SELECT DISTINCT e.emp_id AS 'mng_id', e.emp_name AS 'mng_name'
    FROM employees e, employees m WHERE e.emp_id = m.mng_id
    

    In this query you can see the join condition  e.emp_id = m.mng_id where both e and m aliases are of Employees table.

  2. SQL Statement for “Find the managers with the count of subordinates”:
    SELECT COUNT(emp.emp_id) AS 'emp_count', mng.mng_name AS 'mng_name'
    FROM employees emp,
    (SELECT DISTINCT e.emp_id AS 'mng_id', e.emp_name AS 'mng_name'
    FROM employees e, employees m
    WHERE e.emp_id = m.mng_id) mng
    WHERE emp.mng_id = mng.mng_id
    GROUP BY mng.mng_id
    

    In this query we are getting all the managers’ names with their subordinates counts.

  3. SQL Statement for “Find the managers with the count of subordinates greater than 1 (or any other number)”:

    SELECT COUNT(emp.emp_id) AS 'emp_count', mng.mng_name AS 'mng_name'
    FROM employees emp,
    (SELECT DISTINCT e.emp_id AS 'mng_id', e.emp_name AS 'mng_name'
    FROM employees e, employees m
    WHERE e.emp_id = m.mng_id) mng
    WHERE emp.mng_id = mng.mng_id
    GROUP BY mng.mng_id HAVING COUNT(emp.emp_id) > 1;
    

    In this query we need to just add additional Having clause to find out all the managers who have more than specific number of employees.

I hope this post would be helpful for you. Please feel free to leave your comments below in case if you have any suggestion or if you have better optimized solution.

Advertisements
Categories: SQL

Spring MVC – Create JSP/JSTL Composite View (Header, Body and Footer)

May 13, 2013 1 comment

Most of the good websites share similar layout but each page is made of different independent pieces, but always placed in the same position across all the site. The Composite View pattern formalizes this typical use, by allowing to create pages that have a similar layout, in which each section of the page vary in different situations. Generally the layout is know as template and each independent piece is know as subview.

The below figure demonstrates how only the Body/Content section of page changes keeping  both header and footer unchanged.

How Composite View Works Image

This behavior  can be achieved  by using various ready to use frameworks like SiteMesh and Apache Tiles  and they provide  easy integration with Spring MVC framework. But if you don’t want to use these frameworks and one to implement your own hack by overriding Spring’s out of the box solution of JSTLView then you can refer to the following steps:

    1. Setup Spring MVC environment: First setup your Spring MVC development environment and modify the dispatcher-servlet.xml  according to the code mentioned below.
      <bean id="jspViewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
      
      <property name="viewClass" value="com.myapp.web.view.JstlView" />
      
      <property name="prefix" value="/WEB-INF/views/" />
      
      <property name="suffix" value=".jsp" />
       </bean>
      

      As you can see online.exam.web.view.JstlView is our own custom implementation which forces each page to stay inside the template in other words maintain the layout.

    2. Write custom JSTLView:  This is a simple java class which extends org.springframework.web.servlet.view.InternalResourceView class and overrides renderMergedOutputModel method. The implementation of the class is given below. Also note the Step # 1 and 2 are interchangeable. You may choose to write the  java class first and then configure the WebApplicationContext.
      package com.myapp.web.view;
      
      import java.util.Map;
      
      import javax.servlet.RequestDispatcher;
      import javax.servlet.http.HttpServletRequest;
      import javax.servlet.http.HttpServletResponse;
      
      import org.springframework.web.servlet.view.InternalResourceView;
      
      public class JstlView extends InternalResourceView {
      
       @Override
       protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
      
       // Determine the path for the request dispatcher.
       String dispatcherPath = prepareForRendering(request, response);
      
       // set original view being asked for as a request parameter
       request.setAttribute("partial", dispatcherPath.substring(dispatcherPath.lastIndexOf("/") + 1));
      
      // force everything to be template.jsp
       RequestDispatcher requestDispatcher = request.getRequestDispatcher("/WEB-INF/views/template.jsp");
       requestDispatcher.include(request, response);
      
       }
      
      }
      
    3. Write JSP Pages: The following JSP files provides the details of how this Composite View pattern works. It required three basic JSP files for layout or template implementation namely template.jsp, header.jsp and footer.jsp.  The following code segments showcase each of these.
      <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
       pageEncoding="ISO-8859-1"%>
      <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
      <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
      <%@taglib uri="http://www.springframework.org/tags" prefix="spring"%>
      <%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
      <html>
      <head>
      <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
      <title><spring:message code="app.title.home" /></title>
      </head>
       <body>
       <jsp:include page="header.jsp" />
       <jsp:include page="${partial}" />
       <jsp:include page="footer.jsp" />
       </body>
      </html>
      

      The ${partial} attribute replaced by the actual JSP file as body section.

      <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
       pageEncoding="ISO-8859-1"%>
      <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
      <html>
      <head>
      <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
      <link rel="stylesheet" href="css/style.css" type="text/css">
      </head>
      <body>
      <div id="header">
       <div>
       <div class="logo">
       <a href="#">Online Test</a>
       </div>
       <ul id="navigation">
       <li class="active">
       <a href="home.html">Home</a>
       </li>
       <li>
       <a href="about.html">About</a>
       </li>
       <li>
       <a href="contact.html">Contact</a>
       </li>
       </ul>
       </div>
       </div>
      </body>
      </html>
      
      <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
       pageEncoding="ISO-8859-1"%>
      <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
      <html>
      <head>
      <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
      
      </head>
      <body>
      <div id="footer">
       <div class="clearfix">
       <div id="connect">
       <a href="#" target="_blank" class="facebook"></a>
       <a href="#" target="_blank" class="googleplus"></a>
       <a href="#" target="_blank" class="twitter"></a>
       </div>
       <p>
       © 9999 Online Test. No Rights Reserved.
       </p>
       </div>
       </div>
      </body>
      </html>
      
    4. Write Subview Pages: Once all the template pages are ready body/content pages or subviews to be created. It can be any JSP page you want to display in your site. The below example a sample JSP page.
      <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
       pageEncoding="ISO-8859-1"%>
      <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
      <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
      <%@taglib uri="http://www.springframework.org/tags" prefix="spring"%>
      <%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
      <html>
      <head>
      <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
      </head>
      <body>
      <div id="contents">
       <div id="tagline" class="clearfix">
       <h1>This is my home page</h1>
       </div>
      </div>
      </body>
      </html>
      
    5. Write Spring Controllers to Navigate:  Spring MVC required controllers to process http requests.  A typical spring controller should look  the following:
      package com.myapp.web.controllers;
      
      import org.springframework.stereotype.Controller;
      import org.springframework.web.bind.annotation.RequestMapping;
      import org.springframework.web.bind.annotation.RequestMethod;
      import org.springframework.web.servlet.ModelAndView;
      
      @Controller
      @RequestMapping("/home")
      public class HomeController {
      
      @RequestMapping(method = RequestMethod.GET)
       public ModelAndView handleRequest() {
      
       return new ModelAndView("home");
       }
      
      }
      

Now the application is ready for initial testing or can build your entire web application based on this layout.

Two-Phase Commit Protocol

January 3, 2011 6 comments
Overview

The two phase commit protocol is a distributed algorithm which lets all sites in a distributed system agree to commit or rollback a transaction based upon consensus of all participating sites. The two phase commit strategy is designed to ensure that either all the databases are updated or none of them, so that the databases remain synchronized. The protocol achieves its goal even in many cases of temporary system.
Read more…

Categories: Database Programming

Test your web application’s UI with JUnit and Selenium

January 1, 2011 8 comments
Background

Selenium is a portable Browser automation framework for web applications. Selenium provides a record/playback tool called Selenium Remote Control (RC) runs your tests in multiple browsers and platforms for authoring tests without learning a test scripting language. Selenium also provides Firefox add-on that records clicks, typing, and other actions to make a test, which you can play back in the browser, known as Selenium IDE.
But it this article I am going to demonstrate you how the use Selenium Java WebDriver API to automate your web application or any third party web application.
Read more…

Apache Commons EqualsBuilder and HashCodeBuilder

December 30, 2010 2 comments
Introduction

Before we go ahead and explore Apache Commons EqualsBuilder and HashCodeBuilder we must know the relationship between equals and hashCode.
java.lang.Object which is the super class of all Java classes has two very important methods defined in it. They are –

  • public boolean equals(Object obj)
  • public int hashCode()

These two methods are very important when our classes deal with other Java classes such as Collection API used for searching, sorting, comparison and eliminate duplicate objects from a set.
Read more…

Categories: Java APIs, Java Know Hows

An Introduction to Bitronix JTA Transaction Manager with MySQL

December 28, 2010 1 comment

The Java Transaction API (JTA) allows applications to perform distributed transactions —to access and update systems having multiple transactional resources: databases, message queues, custom resource, or resources accessed from multiple processes, perhaps on multiple hosts, as participants in a single transaction.

The purpose of this article is to provide a high-level overview of Bitronix JTA Transaction Manager with mySQL XA datasource.
Read more…

Categories: Database Programming

Retrieve Exchangeable Image File Format data using Apache Sanselan

December 28, 2010 1 comment

Exchangeable image file format (Exif) is a specification for the image file format used by digital cameras. The specification uses the existing JPEG, TIFF Rev. 6.0, and RIFF WAV file formats, with the addition of specific metadata tags. As we are now aware of what these things are, why not write a Small Java application to extract these data. But developing a full-fledged API is beyond the scope of this blog. So, I chose to use Sanselan from Apache Foundation. This Pure-Java library reads and writes a variety of image formats, including fast parsing of image info (size, color space, icc profile, etc.) and metadata. You can download it from Apache website at http://commons.apache.org/sanselan/download_sanselan.cgi. Using Sanselan, it is very easy to read Exif tags. I have written a small piece of code to get the basic information of an image. The steps required to perform are as follows:

Read more…

Categories: Java APIs