In T-SQL, to create a temporary table, you can use the syntax #TableName. To explore SQL, youll find its often easier to create a temporary table with the data you need rather than creating an entire database.

A temporary table is an object that is created by a user or process and functions the same as any other table. It is not in your database, you can think of it as an object that is in a common swap space of the database server. Behind the scenes it is created in the TempDB System database. You reference them with the hashtag, or pound symbol, followed by a unique name. CREATE TABLE #MyTable for example. Some people refer to them as pound tables. When you close your session, your table is dropped. Well explore them further later in the course.

For this assignment, you will be using temporary tables to explore many of the features that were discussed.

Steps:

Create two temporary tables. The first table should have and ID column and a column called State. The second table should have an ID column, a StateID column, and a column called City. Use T-SQL statements for all of the work. Save the SQL statements as Assignment3.sql.

Populate the tables with at least 10 rows in each table. Each of the data elements you are modeling should have information in both tables. So, pick ten states and then at least ten cities that exist in one of the states. If you add (1, Wisconsin) to your first table, a row in the second table could be (1, 1, Stevens Point). Make sure there is data from at least two states. Save the update to your .sql file

Create a third temporary table with the results of both tables. That is, create a table that would hold an ID, a city name and a state name. Create an insert statement to populate the table using the first two temporary tables. The data should be, for example, (1, Stevens Point,Wisconsin). Save the update to your .sql file

Run an update statement to change swap out two states. That is, if you picked California and had rows (1, Los Angeles,California) and (2, Stevens Point, Wisconsin), after your update, wed have Los Angeles, Wisconsin and Stevens Point, California. Save the update to your .sql file

Following the example in the lecture where JP moved from California to Wisconsin, create a fourth temporary table with the changes that you wish to make and construct an UPDATE statement using a join to that third table. For example, your table could have (San Francisco, Montana) and you want to use an update, joining to that table, to move San Francisco to Montana. Include the code in your .sql file.

Use an ALTER TABLE statement to add one new column of data type datetime to one of your tables. Include a default of 1/1/18. Write an INSERT statement to add a row without referring directly to the new column, and a second insert placing 6/1/18 in the new column. Add your code to your .sql file.