MySQL database problem, please help; I will definitelt give a thumb up Thank you

Question

MySQL database problem, please help; I will definitelt give a thumb up Thank you so much!

I have these tables:

some contents in the table:

committess:

counties:

senators:

states:

Create a text file named xx.sql with your SQL statement answers for the following as a working .sql file. Test your script with the command:

source xx.sql

1. List all state names and their 2-letter codes.

2. Write a query to report the following information for all counties whose names start with "Prince". (Hint: Use "like"). Expected Output columns: a. name b. statecode c. populate_1950 d. population_2010

3. Write a single query to list only the population in year 2010 for the state represented by Sen. Richard Lugar. Output column: populate_2010

4. Write a single query to report only the total number of the counties in 'Maryland'. The query should not hard-code the state code for Maryland (join the two tables in the WHERE clause)
5. Write a single query to find the name of the state that was admitted last into the union. Hint: Use nested subquery.

6. Find all democratic (i.e., with affiliation = 'D') senators that are not chairman of any committee or subcommittee. Expected Output columns: name Order by name.

Explanation / Answer

1. List all state names and their 2-letter codes.

Answer:

select name, statecode from states;

2. Write a query to report the following information for all counties whose names start with "Prince". (Hint: Use "like"). Expected Output columns: a. name b. statecode c. populate_1950 d. population_2010

Answer:

select name, statecode, population_1950, population_2010 from states where name like 'Prince%';

3. Write a single query to list only the population in year 2010 for the state represented by Sen. Richard Lugar. Output column: populate_2010

Answer:

select population_2010 from states, senators where senators.statecode = state.statecode and senators.name = 'Richard Lugar';

4. Write a single query to report only the total number of the counties in 'Maryland'. The query should not hard-code the state code for Maryland (join the two tables in the WHERE clause)

Answer:

select count(*) from states, counties where states.statecode = counties.statecode and states.name = 'Maryland';

5. Write a single query to find the name of the state that was admitted last into the union. Hint: Use nested subquery.

Answer:

select name from states where abmitted_to_union = (select max(admitted_to_union) from states);

6. Find all democratic (i.e., with affiliation = 'D') senators that are not chairman of any committee or subcommittee. Expected Output columns: name Order by name.

Answer:

select senators. name from senators where not exists(select * from committes where committees.chairman = senators.name) and affiliation = 'D' order by name;

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.