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

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