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 operationWhen 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 divCreate 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.

OverviewSkill 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 IOS or AndroidA 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 your Imp with the AA batteries.



When the imp is fi…