SQL

Task --> Query a count of the number of cities in CITY having a Population larger than 100,000.

SELECT COUNT(NAME)

FROM CITY

WHERE POPULATION >= 100000;

Task --> Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.

SELECT *

FROM CITY

WHERE POPULATION >= 100000

AND COUNTRYCODE = 'USA';

Task --> Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

SELECT CITY,

LENGTH(CITY)

FROM STATION

ORDER BY LENGTH(CITY) ASC,

CITY limit 1;

SELECT CITY,

LENGTH(CITY)

FROM STATION

ORDER BY LENGTH(CITY) DESC,

CITY limit 1;

Task --> Amber's conglomerate corporation just acquired some new companies. Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

SELECT c.Company_Code,

c.Founder,

COUNT(DISTINCT l.Lead_Manager_Code),

COUNT(DISTINCT s.Senior_Manager_Code),

COUNT(DISTINCT m.Manager_Code),

COUNT(DISTINCT e.Employee_Code)

FROM Company AS c

JOIN Lead_Manager AS l ON c.Company_Code = l.Company_Code

JOIN Senior_Manager AS s ON l.Lead_Manager_Code = s.Lead_Manager_Code

JOIN Manager AS m ON m.Senior_Manager_Code = s.Senior_Manager_Code

JOIN Employee AS e ON e.Manager_Code = m.Manager_Code

GROUP BY c.Company_code,

c.Founder

ORDER BY c.Company_Code;

Task --> 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). 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 a total of [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.

SELECT (NAME || '(' || SUBSTR(OCCUPATION, 1, 1) || ')')

FROM OCCUPATIONS

ORDER BY NAME ASC;

SELECT ('There are a total of' || ' ' || COUNT(OCCUPATION) || ' ' || LOWER(OCCUPATION) || 's' || '.')

FROM OCCUPATIONS

GROUP BY OCCUPATION

ORDER BY COUNT(OCCUPATION,OCCUPATION ASC;

TBA