Activity 3.5-1

Normally, we'd have to install sql along with other programs in order to work with sql on databases. To avoid this, we will use the website http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all. This page will allow you to put in SQL statements into an interpreter, and work with a database without having to install programs locally. If anything goes wrong, you can hit the "Restore Database" button and the default tables will be created for the database. You should hit this button once before you start the activity. After you type in a statement in the interpreter window, hit the "Run SQL" button to run your code. Note that you can only run one statement at a time!

The first thing we have to do is create a table! Create a table named "Senators" with the following attributes:

1. A "Name" column that is a varchar, which represent the name of a senator.
2. A "State" column that is a varchar, which represent state abbreviations.
3. An "Affiliation" column that is a varchar, which should be either a "D" for democrat or "R" for Republican (we'll assume senators are only of these two affiliations).
4. An "Age" column that is an int, which represents a senator's age.

Now, we'll practice using the INSERT command. Insert the following senators into our new table. You can always check what your table looks like by running SELECT * from Senators; Note that you can only run one statement at a time!

Insert the senators listed below into your table, along with their attributes. Be sure your table looks like this afterwards.

1. Barack Obama - a 54 year old Democratic senator representing Illinois (IL)
2. John McCain - a 79 year old Republican senator representing Arizona (AZ)
3. Paul Rand - a 52 year old Republican senator representing Kentucky (KY)
4. Tom Cotton - a 38 year old Republican senator representing Arkansas (AR)
5. Dianne Feinstein - an 82 year old Democratic senator representing California (CA)

(We know Barack Obama is no longer a senator; we'll take care of this soon!)

Now, we'll practice using the SELECT command. Follow the instructions at each step, and take a look at the results that show up after running your command (Remember you can only run one at a time). Compare the results that show up to the original table, and make sure your answers/table make sense at each step! Ask a TA for help if you are unsure about anything.

1. Show the entire Orders table. Note that this is not a table we built, but came with the website. What do you see?
2. Show the entire Senators table. How many columns and rows do you see? Everything should match up with the original table we started with after inserting our five senators.
3. Now, make your table show only information on the senators' names and states, and nothing else (so your table should only have two columns).
4. Now, let's look at the Age of the senators, in addition to their names and states. Make the orders of your column: Name, Age, State.

Here, things will get a little complicated, so be sure to compare with the original table, and make sure your answers/table make sense.

5. Select and display the Name, Age, and State of all senators whose age is less than 40 (use the Age attribute).
6. Select and display the Name, Age, and State of all senators whose age is greater than 80 (use the Age attribute).
7. Select and display the Name, Age, and State of all senators who are Democrats (use the Affiliation attribute).
8. Select and display the Name, Age, and State of all senators who are Republicans (use the Affiliation attribute).
9. Select and display the Name and Age of all senators who are between the ages of 50 and 60, inclusive (use the age attribute along with AND).

Again, be sure to double check your answers. For the last task for example, look at the original table and see which senators are between the ages of 50 and 60. Are these, only these, senators the ones that show up after running your select statement?

Now, we're going to practice using the update command on some fictional senators! First we'll have to insert these senators into the table.

Insert the senators listed below into your table, along with their attributes. Be sure your table looks like this afterwards.

1. Flip FLop - a 50 year old Democratic senator representing Flip-Flopia (FF)
2. Ben Button - a 70 year old Republican senator representing Ben-Buttonland (BB)
3. Ben Flop - a 70 year old Republican senator representing Flip-Buttontown (FB)

Now, we can work with the update command to alter these senators' attributes. Update the table to reflect the following changes:

1. Flip Flop changed has now declared that he is a Republican.
2. Ben Button's age changed to 40.
3. Ben Flop has now declared that he is a Democrat, and has also just turned 40.

Note that you can find different ways to change the above, but use the name as the 'selecting' factor. Compare your new table to what it looked like before, and make sure the changes from above are reflected in the new table.

Now, we'll delete the elements of our table systematically to practice using the DELETE command. Make your table reflect the changes below:

1. Barack Obama is no longer a senator.
2. Our fake senators all have to be removed from the table. Use the fact that, after our table updates from Task 4, all the fake senators are either 50 years old or 40 years old. (Be sure to use the OR keyword.)
3. Delete our entire table. You should not have to use the WHERE keyword!

If you try to look at your table now using a select statement, you should see the words "No Result" in the result box.