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

Enabling TLS 1.2 in Java 7

I am working on web application that uses third party API and apparently those guys have stopped using TLS 1.0 and switched TLS 1.2 I was getting the following error Apparently Java 1.8 have TLS 1.2 by default and for Java 1.7 you have to enable it. I have been searching online for few days to enable TLS 1.2. I have tried all the following things but it did not work For each application, enter this command: java ... -Dhttps.protocols=TLSv1.1,TLSv1.2 ... Using JAVA_OPTS, enter the following command in your .profile, shell rc (to affect all Java programs run by the user), or conductor.sh (to just affect Datameer): export JAVA_OPTS="${JAVA_OPTS} -Dhttps.protocols=TLSv1.1,TLSv1.2" This is what I did and boom it worked

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