Question

Given the following table:

CREATE TABLE PERSON (
ID INT PRIMARY KEY ,
FIRST_NAME VARCHAR(25) NOT NULL ,
MIDDLE_NAME VARCHAR(25) ,
LAST_NAME VARCHAR(25) NOT NULL,
DATE_OF_BIRTH DATE NOT NULL,
DATE_OF_DEATH DATE );

Write the complete SQL command to list the frequency of all the middle names, most frequent first and alphabetically for same frequency (i.e. for each middle name show how many persons have that middle name).
Write the name of the SQL keywords in upper cases. Do not use aliases. Include each part (clause) of the command on a separate line as indicated.

[select] # columns clause
[from] # tables clause
[group] # grouping clause
[order] # ordering clause

Answer 1

SELECT MIDDLE_NAME, COUNT(*) AS FREQUENCY

FROM PERSON

GROUP BY MIDDLE_NAME

ORDER BY FREQUENCY DESC, MIDDLE_NAME; This SQL command lists the frequency of middle names from the PERSON table, ordering them by frequency in descending order and alphabetically for the same frequency.

SELECT MIDDLE_NAME, COUNT(*) AS FREQUENCY

FROM PERSON

GROUP BY MIDDLE_NAME

ORDER BY FREQUENCY DESC, MIDDLE_NAME;

Elaboration has been done below.

- SELECT: Specifies the columns to be retrieved (MIDDLE_NAME and the count of occurrences).

- FROM: Specifies the table from which to retrieve the data (PERSON).

- GROUP BY: Groups the result set by the MIDDLE_NAME column.

- ORDER BY: Orders the result set first by frequency in descending order and then alphabetically by middle name in case of ties.