============================================================================================== Part 1 - Due Feb 15, 11:59 PM (Total points 60: 4 each) ============================================================================================== 0. List all state names and their 2-letter codes. Output columns: name, statecode Order by the state name. === 0 select name, statecode from states order by name; **************** RESULT *************************************************************** NAME ST -------------------- -- Alabama AL Alaska AK Arizona AZ Arkansas AR California CA ... ============================================================================================= 1. Write a query to change your password. Ofcourse when submitting the answer, don't write the new password (write "****" instead). === 1 alter user identified by ****; **************** RESULT *************************************************************** ============================================================================================= 2. Figure out how to find out all the attributes and their types for a given table in SQL*Plus, and write down the statement and its result on the "senators" table. This kind of a statement is usually unique to the client, and is not part of standard SQL. === 2 describe senators; **************** RESULT *************************************************************** Name Null? Type ----------------------------------------- -------- ---------------------------- STATECODE CHAR(2) NAME NOT NULL CHAR(40) AFFILIATION CHAR(1) TOOK_OFFICE NUMBER(38) BORN NUMBER(38) ============================================================================================= 3. Write a query to report the information for all counties whose names start with "Prince" (Hint: Use "like"). Output columns: name, statecode, populate_1950, populateion_2010 Order by state code. === 3 select * from counties where name like 'Prince%' order by statecode; **************** RESULT *************************************************************** NAME ST POPULATION_1950 POPULATION_2010 ------------------------------ -- --------------- --------------- Prince of Wales-Hyder AK 0 5559 Prince Georges MD 194182 863420 Prince George VA 19679 35725 Prince William VA 22612 402002 Prince Edward VA 15398 23368 ============================================================================================= 4. 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 select population_2010 from senators, states where states.statecode = senators.statecode and senators.name = 'Richard Lugar'; **************** RESULT *************************************************************** POPULATION_2010 --------------- 6483802 ============================================================================================= 5. 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. === 5 select count(*) from counties, states where states.name = 'Maryland' and counties.statecode = states.statecode; **************** RESULT *************************************************************** COUNT(*) ---------- 24 ============================================================================================= 6. Write a single query to find the name of the state that was admitted last into the union. === 6 select name from states where admitted_to_union = (select max(admitted_to_union) from states); **************** RESULT *************************************************************** NAME -------------------- Hawaii ============================================================================================= 7. Write a query to find the names of the counties whose names are at least 20 characters long. Keep in mind that county name is defined to be a fixed-length 30 character string, so you must elimiated the padded whitespaces to correctly answer the query. Output columns: name (of the county) Order by: name. === 7 select trim(name) as name from counties where length(trim(name)) >= 20 order by trim(name); **************** RESULT *************************************************************** NAME ------------------------------ Fairbanks North Star Prince of Wales-Hyder St. John the Baptist Yellowstone National Park ============================================================================================= 8. (Using the WITH Construct) Write a query to find the name of the state with the minimum number of counties by first creating a temporary table using the "WITH" construct. If there are multiple such states, all should be reported. Output column: name (of the state) === 8 with counties_counts as ( select states.name, count(*) as num_counties from states, counties where states.statecode = counties.statecode group by states.name ) select name from counties_counts where num_counties = (select min(num_counties) from counties_counts); **************** RESULT *************************************************************** NAME -------------------- Delaware ============================================================================================= 9. Create a view called HighPopulationCounties containing the high-population counties (defined to be counties with at least 2.5M population in 2010), and their state names. View columns: countyname, statename, population_2010 Return result of: "select * from HighPopulationCounties order by population_2010 desc;" === 9 drop view HighPopulationCounties; create view HighPopulationCounties as select counties.name as countyname, states.name as statename, counties.population_2010 from states, counties where states.statecode = counties.statecode and counties.population_2010 > 2500000; select * from HighPopulationCounties order by population_2010 desc; **************** RESULT *************************************************************** COUNTYNAME STATENAME POPULATION_2010 ------------------------------ -------------------- --------------- Los Angeles California 9818605 Cook Illinois 5194675 Harris Texas 4092459 Maricopa Arizona 3817117 San Diego California 3095313 Orange California 3010232 Kings New York 2504700 ============================================================================================= 10. Write a query to find if any senators represent a state that was admitted to the union after they were born. Hint: Use "extract" function that operates on the dates. Output columns: senator_name, year_born, state_name, year_state_admitted Order by senator name. You might want to increase the output width in sqlplus using "set linesize 200;". === 10 select senators.name as senator_name, born as year_born, states.name as state_name, extract(year from admitted_to_union) as year_state_admitted from states, senators where states.statecode = senators.statecode and extract(year from admitted_to_union) > born order by senator_name; ***************e RESULT *************************************************************** SENATOR_NAME YEAR_BORN STATE_NAME YEAR_STATE_ADMITTED ---------------------------------------- ---------- -------------------- ------------------- Daniel Akaka 1924 Hawaii 1959 Daniel Inouye 1924 Hawaii 1959 Lisa Murkowski 1957 Alaska 1959 ============================================================================================= 11. Find all democratic (i.e., with affiliation = 'D') senators that are not chairman of any committee or sub-committee. Output columns: name Order by name. === 11 select name from senators where name not in (select chairman from committees) and affiliation = 'D' order by name; -- You can also use "not exists". select name from senators s where not exists (select * from committees c where c.chairman = s.name) and affiliation = 'D' order by name; **************** RESULT *************************************************************** NAME ---------------------------------------- Harry Reid Jeff Merkley Joe Manchin Richard Blumenthal Tom Udall ============================================================================================= 12. Create a table (call it PopulationGrowth), using "create as" that contains three columns: statename, growth_1900_to_1950, growth_1950_to_2000, where the growth_1900_to_1950 is defined as: population_1950/population_1900, and the second number is defined similarly. List the result of: "select * from PopulationGrowth where growth_1950_to_2000 > 5 order by statename;" === 12 create table PopulationGrowth as select name as statename, population_1950/population_1900 as growth_1900_to_1950, population_2000/population_1950 as growth_1950_to_2000 from states; select * from PopulationGrowth where growth_1950_to_2000 > 5 order by statename; **************** RESULT *************************************************************** STATENAME GROWTH_1900_TO_1950 GROWTH_1950_TO_2000 -------------------- ------------------- ------------------- Arizona 6.09762387 6.84461177 Florida 5.24330138 5.76709456 Nevada 3.78133932 12.4826309 ============================================================================================= 13. If you do "select name, admitted_to_union from states order by admitted_to_union", you will notice that the only the last two digits of the year are output. Write a query that outputs the result of the query more appropriately, i.e., with 4 digits of the year. Output columns: name, admitted_to_union Order by: admitted_to_union. Truncate the answre to 5 rows when pasting the answer below. === 13 select name, to_char(admitted_to_union, 'MM-DD-YYYY') from states order by admitted_to_union; **************** RESULT *************************************************************** NAME TO_CHAR(AD -------------------- ---------- Delaware 12-07-1787 Pennsylvania 12-12-1787 New Jersey 12-18-1787 Georgia 01-02-1788 Connecticut 01-09-1788 ============================================================================================= 14. Write a query to find all the states whose growth rate was higher by a factor of 2 or more in the period 1900 to 1950, than in the period from 1950 to 2000? Use PopulationGrowth table created above. For example, if a state grew by a factor of 3 between 1900 and 1950, but only by a factor of 1.45 between 1950 and 2000, then it would qualify for the answer. Output columns: name, growth_1900_to_1950, growth_1950_to_2000 === 14 select * from PopulationGrowth where 2 * growth_1950_to_2000 < growth_1900_to_1950; **************** RESULT *************************************************************** STATENAME GROWTH_1900_TO_1950 GROWTH_1950_TO_2000 -------------------- ------------------- ------------------- California 7.12851528 3.19959706 West Virginia 2.09173133 .901668967 ============================================================================================= 15. Write a query to find the day when the 30th state was admitted to the union, and the name of the state. Output columns: name, admitted_to_union === 15 select name, admitted_to_union from states s1 where 30 = (select count(*) from states s2 where s1.admitted_to_union >= s2.admitted_to_union); **************** RESULT *************************************************************** NAME ADMITTED_ -------------------- --------- Wisconsin 26-MAY-48 =============================================================================================