--
-- Name: TYPE YOUR NAME HERE
--
-- Write your queries below each comment. Please use good style (one clause
-- per line, JOIN syntax, indentation) and make sure all queries end with a
-- semicolon. When necessary, limit the output to the first 200 results.
--
-- DO NOT MODIFY OR DELETE ANY OTHER LINES!
--

-- ------------------------------------------------------------------------------
-- Part 1: world database
USE world;
-- ------------------------------------------------------------------------------

--
SELECT 'Query #1' AS '';
--
-- What cities from Virginia are in the world database?
--
-- Schema: ID, Name, Population
--  Order: Population (descending)


--
SELECT 'Query #2' AS '';
--
-- What cities in the database have a population over 3 million?
--
-- Schema: Population, CityName, District, CountryName, Continent
--  Order: Population


--
SELECT 'Query #3' AS '';
--
-- Which countries have the majority (over half) of their population
-- living in the capital city?
--
-- Schema: CityName, CityPopulation, CountryName, CountryPopulation
--  Order: CountryName, CityName


--
SELECT 'Query #4' AS '';
--
-- Which countries have Spanish as an official language? Also calculate
-- the number of Spanish speakers by multiplying the population and the
-- percentage, rounded to the nearest integer.
--
-- Schema: Name, LocalName, Region, Population, Percentage, Speakers
--  Order: Speakers


--
SELECT 'Query #5' AS '';
--
-- How many languages are spoken in each country? The count should
-- include both official and unofficial languages.
--
-- Schema: Name, Region, Languages
--  Order: Languages (descending), Name (ascending)


-- ------------------------------------------------------------------------------
-- Part 2: employees database
USE employees;
-- ------------------------------------------------------------------------------

--
SELECT 'Query #6' AS '';
--
-- Show the names and dates hired of all employees who were a manager
-- at some point.
--
-- Schema: dept_name, first_name, last_name, hire_date
--  Order: dept_name, last_name, first_name


--
SELECT 'Query #7' AS '';
--
-- For each department, list the total number of employees who have
-- worked at any time.
--
-- Schema: dept_name, emp_count
--  Order: dept_name


--
SELECT 'Query #8' AS '';
--
-- List all employees who worked in the Research department on
-- November 20, 1985 (the day that Microsoft Windows launched)
-- and who were born before the year 1960.
--
-- Schema: emp_no, from_date, to_date, birth_date, first_name, last_name
--  Order: emp_no


--
SELECT 'Query #9' AS '';
--
-- For each year that employees were hired, show the minimum, average,
-- and maximum salary on January 1, 2000. Use the year() function to
-- extract the year from hire_date.
--
-- Schema: hire_year, min(salary), avg(salary), max(salary)
--  Order: hire_year


--
SELECT 'Query #10' AS '';
--
-- List all employees who ever worked for the Development department
-- as Engineers or Senior Engineers.
--
-- Schema: emp_no, first_name, last_name, gender, title
--  Order: emp_no
