Access Query Practice--Lots of Colleges
- Download this Excel data source (taken from Data.gov) --> CollegesSC.xlsx (download as Excel document from Google)
- Create a new database called Lots of Colleges. Import the Excel spreadsheet into the database as a new table and set the Primary key to the UNITID field.
- Run the following queries:
- Institution, City, State fields listing institutions in Chicago, IL sorted by Institution Name **save as Chicago Schools (should get 76)
- Record #25 should be Harrington College of Design
- Institution and State field listing institutions that have Troy beginning the name *save as Troy Schools (should get 6 schools)
- Institution, City, State fields listing schools starting with the word Cape, sorted by State *save as Cape Schools (should get 5 schools and #5 should be Cape Fear in NC)
- Institution, City, State fields listing schools with Cape in the name, sorted by State *save as Anywhere Cape Schools (should get 11 schools and #10 should be in NJ)
- Institution and State fields for schools that start with the letter X, sorted on school name *save as X Schools (should get 7 total; first record is a college of nursing)
- Institution and URL showing ONLY for schools that are in Arlington, Virginia sorted on school name *save as Arlington URLs (should get 9 total, 3rd record is DeVry)
- Institution and City fields ONLY showing listing institutions in New Mexico that have "San" anywhere in the title *save as New Mex San Schools (should get 5)
- Create a summary query that lists (in alpha order by State) the States and a count of how many schools are in that state (should get 59 records, Missouri has 187 schools, and final entry should be Wyoming with 11) *save as Summary by State
