1. Write the SQL that will display total number of records in this ALLSTATES table

2. Display the state name, DeptName, capital city, deptID and total population for those states that has an USCISoffice. (hint: USCISoffice =Yes)

3. Display all states in the increasing sequence of total population

4. Display all-state where total population is more than 20 million

5. Display the DeptID of the state where Capital city is null

6. Display the state and DeptID of a state where DeptName does not start with State of

7. Display total population of all states in this table

8. Write the update statement to update the Capital City of Idaho to BOISE

9. In the table DEPT, the column AdvDegree indicate the percentage of population that holds advanced degree in that state. Calculate the total population (in number, name this column as AdvDegMA) of adv. Degree holders in state of Mass.

NY * Table: ALLSTATES State DeptName CapitalCity DeptID Totalpopulation USCISoffice MA Commonwealth of Mass Boston 1000 6,500,000 Yes TX State of Texas Austin 1020 25,000,000 No State of New york Albany 1033 19,765,000 No CA State of CA Sacramento 1034 37,324,666 No FL State of FL Tallahassee 1038 18,111,223 No PA State of PA Harrisburg 1040 12,324,200 No IL State of IL Springfield 1044 12,829,014 No OH State of ohio Columbus 1046 11,536,504 No NJ State of New Jersey Trenton 1055 8,791,000 Yes WA State of WA Olympia 1058 6,724,000 Yes CO State of Colorado Denver 1060 5,074,528 Yes ID State of Idaho 1063 Table: DEPT DeptID TotalUniv AdvDegree(in %) 1000 220 0.52 1020 140 0.29 1063 58 0.12 1044 208 0.44