Skip to main content

Mind Blogging SQL Challenges



  1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A)ADoctorName(D)AProfessorName(P), and ASingerName(S).
  2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format: 
    There are total [occupation_count] [occupation]s.
    
    where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.
Note: There will be at least two entries in the table for each type of occupation.
Input Format
The OCCUPATIONS table is described as follows:Occupation will only contain one of the following values: DoctorProfessorSinger or Actor.
Sample Input
An OCCUPATIONS table that contains the following records:
Sample Output
Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are total 2 doctors.
There are total 2 singers.
There are total 3 actors.
There are total 3 professors.

QUERY
select concat(name,"(",substring(occupation,1,1),")")from occupations order by name asc;
select concat("There are total ",count(occupation),' ',lower(occupation),'s.') from occupations group by occupation order by count(occupation) asc,occupation asc;

Comments

Popular posts from this blog

Adding loading spinner to JSP Page using pure CSS and JS

I wanted to add loading spinner to the web application that I was working on. This application is a legacy application and I was looking into a way to add the loading spinner whenever the user performs certain actions such as the following Clicks a button which behind the scene performs CRUD operation When user request data  As the web application was based of JSP and I was looking for a way to adding it to the webpage and finally came across the following solution  Create a div  Add the CSS for the div Create a JS function which gets called on button click.  Here is the code HTML JS      CSS

Simple IOT Project - Electric Imp Led Light Controller

Recently I went to IBM IOT Hackathon and got Electic IMP device and thought of playing with it and this is the first application I created LED Light controller using Electric IMP device and REST webservice. Overview Skill Level: Beginner Below are detailed steps on how to connect an Electric Imp with environmental sensors to the Watson IoT Platform in order to visualize and monitor your refrigerator in real time. Ingridients   Your WIFI *network name* and *password*  A computer with a web browser  Smartphone with the Electric Imp app I OS  or Android A free Electric Imp developer account    An Electric Imp Explorer kit - purchase from Amazon    Three AA batteries STEP BY STEP  Step 1 - What is demonstrated in this example? Use an Electric Imp to switch on/off light sensor and change the color of light using combination of RGB.  Step 2 - Connect your Electric Imp to the Internet  Set Up Hardware  Plug the Imp001 into the Explorer Kit Board .Power up y

Attaching remote debugger in Netbeans

I am working on a web application based on Ant,Struts and Java. I wanted a way to debug the live application in my local environment and I found out that attaching remote debugger is the best option. It helps in debugging live application. What is Remote Debugging? The client/server design of the Java  debugger  allows you to launch a Java program from computer on your network and  debug  it from the workstation running the platform. This is particularly useful when you are developing a program for a device that cannot host the development platform [source: eclipse community] How to enable it in Netbeans Open Netbeans IDE Go to Debug option  Click on Attach Debugger  Enter the host name and port number Click OK Note: Make sure you start the application first before attaching the remote debugger