Hi Guys,

I've been teaching myself, very slowly, the whole webdesign thing using VWD as my prefered tool. You'll be happy to here I can now design, build, and deploy sites, and I think VWD is a fantastic tool for doing so. My next challenge is to integrate dbs onto sites, the kind of thing where people enter data onto the db via the site, and others can access the data in a similar fashion - you know, FaceBook, Bebo, YouTube, dating sites etc. The problem is that SQL and dbs in general are my bette noir - I'm terrified of them. I've got the VWD manuals (Idiots etc), and I've done the VWD/SQL video training, but what I really need is a good, ground up, comprehensive - but idiot proof (for I am that person) project/s that will take me through designing and integrating SQL dbs into web projects. I'm pretty sure that doing so, just like designing websites, is pretty simple once you know it, but my fear of such coupled with my inability to find the perfect resource for doing so have brought me to a standstill. If anyone can point me in the direction of a project or simple guide that will lead me to my goal I'd be very gratefull.

Michael.

Per email - we will be working in VB. I'm a C# guy so this was a good chance to jump the fence to the other side.

1. Open Visual Web Developer, go to File, and on the dropdown choose "New Web Site...".

1a. It will default to some name like WebSite2, WebSite3, and you probably want to change this to something more descriptive (you may soon have several web sites).

2. In "Solution Explorer" on the right near the top, look for an icon that is a gray folder with a yellow cylinder in front of it and the words "App_Data".  Right click it.

2a. Choose "Add New Item".

3. Under Visual Studio installed templates choose "SQL Server Database".

3a. Since this is the first SQL database it will name it Database.mdf by default and yes, you want it to have the name Database.mdf (it will then be compatible with the connection string in the examples).

3b. Click the "Add" button.

3c. You should see it in the Solution Explorer window under App_Data now.

You should be ready now to do the work in the examples that follow.

This space is reserved to be an Index.  I see this thread growing to 20-30 posts (maybe even a few more).  I look forward working with Michael, and expect a few others may come here too, and for them, I'd like an index here to provide an overview on what they can find that will help them.

-Larry

 INDEX

1a. The aspx file for INSERT_SQL (TextMode="MultiLine" is needed because in most cases there will be several lines of SQL code)

1b. The aspx.vb file for INSERT_SQL (key features include: Imports, SqlConnection, SqlCommand, ExecuteNonQuery() which takes the commands in "mystring" and directions them to the table specified by the connection "conn") {notice that the connection conn is opened: conn.Open(), and closed: conn.Close() }

2. SQL is used to create a table (I use the line with id int IDENTITY with almost every table I make--it is automatic at this point; I didn't put a NOT NULL on Population because I didn't have the time to find all the answer--we can also use this later to show how SQL can add data to an existing record)

3. SQL Code is used to add records to an empty table (INSERT INTO puts new records into a table; the first part names the columns; the part after the word VALUES lists what goes into the record)

4a (aspx), 4b (aspx.vb). A dropdown menu lets you select a city for which the population has not yet been added (e.g. Instanbul) and a textbox lets you type in the number for the population (e.g. 11200000).  When you press the button, the code behind page has a subroutine that creates a SQL statement your input and runs it through an ExecuteNonQuery().  You will see ExecuteNonQuery() quite a few times in future examples.  New vocabulary: UPDATE, SET, WHERE. 

5. SQL code is provided to create a new table, Hotels and to give it several records.  An INNER JOIN is used to connect the two tables (Cities, Hotels) together.  An ON is used to make the join occur where the entry in the field "City" in table Cities is the same as the entry in the field "City" in the table "Hotels". An AND is used to combine two statements for the WHERE (you want the search to find a number between two extremes defined by variables).  The SQL statement is broken into segments with the & character between segments.  The segments are either text or variables.  Apostrophes are added to the text and placed so that they will encapsulate the number provided by the variable.  Data is taken from both tables to construct sentences.  New vocabulary: INNER JOIN, ON, AND.

 5c. The INNER JOIN was rather complicated (but introduced early because it is so helpful).  Another section provides a little more explanation about it.

After 5, a review/overview page will provide more discussion on the various new features introduced in 1 through 5.

6. TRUNCATE TABLE removes the contents of a table but the structure of the table remains.

7. ALTER TABLE, ADD and ALTER TABLE, DROP COLUMN  (7a. and 7b)

8. INNER JOINs are used to connect together 4 tables.

9. GROUP BY (with explanation of how to avoid common error message) - an upgrade to this is needed and will be coming

10. Math Functions: SUM, AVG, STDEV, ExecuteScalar()

11. ORDER BY

12. A Dropdownlist is used to change the SQL command (WHERE, ORDER BY); the commands are not new but the format is new

13. Aggregate functions (SUM, COUNT, AVG, MIN, MAX) are compared using the dropdownlist-to-change-SQL-command pages introduced in 12.

14. A stored procedure is introduced; it uses the word EXECUTE

15. a stored procedure includes two parameters and is used to  insert new records into a table.

16. FULL JOIN, LEFT JOIN, RIGHT JOIN (cousins of INNER JOIN)

17. FOREIGN KEY (a cousin to PRIMARY KEY)

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Inject_SQL.aspx.vb" Debug="true" Inherits="Inject_SQL" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Beginner SQL Injection Box</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:TextBox ID="SQL_Inject_Box" Width="700px" Height="200px" runat="server" 
            TextMode="MultiLine"></asp:TextBox><br />
        
        <asp:Button ID="SQL_Inject_Button" runat="server" Text="Inject" />
        
        <p>Warning: Do not let any other person have access to this web page! Someone else (hacker) might use this!  Keep this web page secret!  The content on this page is kept simple for purposes of helping a beginner.</p>
        
        <p>Your complimentary link to what you will want to learn: <a href="http://msdn2.microsoft.com/en-us/magazine/cc163917.aspx">Microsoft paper on SQL Injection Attacks</a></p>
    
    </div>
    </form>
</body>
</html>

Imports System.Data.SqlClient

Imports System.Data

Partial Class Inject_SQL

     Inherits System.Web.UI.Page

     Protected Sub SQL_Inject_Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SQL_Inject_Button.Click

       Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")

       Dim mystring As String = SQL_Inject_Box.Text

       Dim myCommand As New SqlCommand(mystring, conn)

       conn.Open()

       myCommand.ExecuteNonQuery()

       conn.Close()

       SQL_Inject_Box.Text = ""

     End Sub

End Class

CREATE TABLE Cities
(id int IDENTITY(1,1) PRIMARY KEY,
City nchar(20) NOT NULL,
Country nchar(20) NOT NULL,
Latitude int NOT NULL,
Population int)

INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Oslo','Norway','59','560000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Stockholm','Sweden','59','800000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Moscow','Russia','55','8300000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Berlin','Germany','52','3400000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Amsterdam','Netherlands','52','730000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('London','United Kingdom','51','7000000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Praque','Czech Republic','50','1200000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Paris','France','48','2200000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Vienna','Austria','48','1500000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Munich','Germany','48','740000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Budapest','Hungary','47','1800000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Zurich','Switzerland','47','380000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Montreal','Canada','45','1600000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Florence','Italy','43','370000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Toronto','Canada','43','2500000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Marseille','France','43','1300000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Boston','USA','42','4300000')	
INSERT INTO Cities (City, Country, Latitude) VALUES  ('Rome','Italy','41')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Instanbul','Turkey','41')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Denver','USA','39')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Athens','Greece','38')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Seoul','South Korea','37')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Algiers','Algeria','36')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Tokyo','Japan','35')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Baghdad','Iraq','33')

The aspx page below (everything below the ***) has a dropdownlist with the names of cities to which a population has not been added yet (the value in the table is NULL).  Population data is provided for the five remaining cities:

Rome: 2,700,000. Instanbul : 11,200,000. Athens : 750,000. Denver : 2,400,000. Seoul : 10,400,000.

(Note: this page has two ?issues? that I want to improve upon them later?for now it will serve the purpose of introducing several things: connecting a dropdown list to the code behind page, using the selection of the dropdown list to put the name of a city in a SQL statement, using a number typed into a textbox to put a number into a SQL statement)

I hope that this is example is small enough that for just looking at it, you can see what everything is doing.  If there is any question, don't hesitate to ask.

*************

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="DropDown.aspx.vb" Inherits="DropDown" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Drop Down</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:DropDownList ID="AddPopulationDropDownList" runat="server" AutoPostBack="True"
            DataSourceID="AddPopulationSqlDataSource" DataTextField="City" 
            DataValueField="id">
        </asp:DropDownList>
        
        <asp:SqlDataSource ID="AddPopulationSqlDataSource" runat="server"  
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            SelectCommand="SELECT * FROM [Cities] WHERE [Population] IS NULL"></asp:SqlDataSource>
            
                
    
        Update population to: <asp:TextBox ID="PopulationTextBox" runat="server"></asp:TextBox>
        
        <asp:Button ID="PopulationUpdateButton" runat="server" Text="Update Population" /> <br /><br />
        
        <div id="commandline" runat="server">You need to choose a city from the dropdown menu</div>
       
    </div>
    </form>
</body>
</html>

Imports System.Data.SqlClient
Imports System.Data

Partial Class DropDown
    Inherits System.Web.UI.Page

    Protected Sub AddCity(ByVal sender As Object, ByVal e As System.EventArgs) Handles PopulationUpdateButton.Click

        Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")

        Dim thecommand As String = "UPDATE Cities SET Population='" + PopulationTextBox.Text + "' WHERE id='" + AddPopulationDropDownList.SelectedValue.ToString() + "'"
        Dim myCommand As New SqlCommand(thecommand, conn)

        commandline.InnerHtml = "<i>The SQL command executed just now was: </i>" + thecommand

        conn.Open()
        myCommand.ExecuteNonQuery()
        conn.Close()


    End Sub

End Class

Assume that after traveling to several of the cities, you decide you want to buy several hotels.  You later want to list the hotels between two values, an upper latitude and a lower latitude.  If there was one hotel in each city you would want to just add a new column to your Cities table.  However, for the large cities you have found several hotels that interest you, so you want to make new table, Hotels, that can be used to associate several hotels to a city.  Each hotel will be a separate record (a separate row) in the table.
 
 
CREATE TABLE Hotels
(id int IDENTITY(1,1) PRIMARY KEY,
City nchar(20) NOT NULL,
Hotel nchar(50) NOT NULL)

INSERT INTO Hotels (City, Hotel) VALUES ('Paris','Hotel de Crillon')
INSERT INTO Hotels (City, Hotel) VALUES ('Paris','Hotel de Vendome')
INSERT INTO Hotels (City, Hotel) VALUES ('Paris','Hotel Regina')
INSERT INTO Hotels (City, Hotel) VALUES ('Berlin','The Regent Hotel Berlin')
INSERT INTO Hotels (City, Hotel) VALUES ('Berlin','Rocco Forte Hotel de Rome')
INSERT INTO Hotels (City, Hotel) VALUES ('Berlin','Grand Hyatt Berlin')
INSERT INTO Hotels (City, Hotel) VALUES ('Moscow','Renaissance Moscow Hotel')
INSERT INTO Hotels (City, Hotel) VALUES ('Rome','Hotel Marco Polo')
INSERT INTO Hotels (City, Hotel) VALUES ('Stockholm','Birger Jarl')
INSERT INTO Hotels (City, Hotel) VALUES ('Zurich','Hotel Eden au Lac Zurich')
INSERT INTO Hotels (City, Hotel) VALUES ('Tokyo','Mandarin Oriental Tokyo')
INSERT INTO Hotels (City, Hotel) VALUES ('Algiers','Sofitel Alger')

You can get information from both tables if you use an INNER JOIN.

For an INNER JOIN, both tables will be named in the SQL statement and identified with letters a and b (a and b were arbitrary choices).  Two textboxes are used to collect an upper and a lower latitude, and the INNER JOIN will get all hotels with latitudes that are numbers between the two numbers that you selected.  For example, if you chose 38 for lower and 52 for higher then all hotels between the latitudes of 38 and 52 will be listed.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="INNER_JOIN.aspx.vb" Debug="true" Inherits="INNER_JOIN" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>INNER JOIN</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        Lower Latitude: <asp:TextBox ID="LowerLatitudeTextBox" runat="server" />
    
        <br /><br />
        
        Upper Latitude: <asp:TextBox ID="UpperLatitudeTextBox" runat="server" />
        
        <br /><br />
        
        <asp:Button ID="Button1" runat="server" Text="Done" />
        
        <br /><br />
        
        <div id="sentences" runat="server" />
        
    </div>
    </form>
</body>
</html>

Imports System.Data.SqlClient
Imports System.Data

Partial Class INNER_JOIN
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")

        Dim lowerlatitude As Double = CDbl(LowerLatitudeTextBox.Text)
        Dim upperlatitude As Double = CDbl(UpperLatitudeTextBox.Text)

        Dim sb As StringBuilder = New StringBuilder()

        conn.Open()

        Dim sqlstatement As String = "SELECT *, a.Latitude AS Latitude, a.City AS City, b.Hotel AS Hotel FROM Cities a INNER JOIN Hotels b ON a.City=b.City WHERE a.Latitude < '" & upperlatitude & "' AND a.Latitude > '" & lowerlatitude & "'"
        Dim command As SqlCommand = New SqlCommand(sqlstatement, conn)
        Dim reader As SqlDataReader = command.ExecuteReader()

        While reader.Read()
            sb.Append("The hotel <i>" & reader("Hotel") & "</i>, located near Latitude " & reader("Latitude") & ", is in the city of " & reader("City") & ".<br />")
        End While
        reader.Close()

        conn.Close()

        sentences.InnerHtml = sb.ToString()

    End Sub
End Class

Most likely you will have more than one table.  The theory for this is abstract, and perhaps fun, but I think right now you want to have something that works for you.  A story problem is used below for constructing an INNER JOIN statement very similar to the one used in example 5.  This is in case someone finds the example to be confusing because it is rather long.

If you are a business, you might have a table that has a record for each transaction (purchase).  Each record might list the sales person or cashier who managed the transaction.  You would probably have a separate table with data about each cashier or sales person.  You might need an INNER JOIN to take information from both tables so that after the computer calculates the bonus check (from math on data in the Transactions table) it can use data in the Cashier table to write the address to where the bonus check will be mailed. 

For this scenario, assume that we have two tables: Cashier, Transactions.  Assume each table has a field (column) labeled ?cashier?.  Assume that table Cashier has fields ?FirstName? and ?LastName? and that table Transactions has a field ?TransactionTotal?. 

For the purpose of abbreviation, I?ll use the letter a to designate the table Cashier and the letter b to designate the table Transaction.  When the INNER JOIN statement is written, the abbreviation letters are written after the table name; this may be confusing because after that the letter precedes the name of a field (solution: write down a complete statement in your notes so you can refer to it quickly).

The statement will read ?Cashier a INNER JOIN Transactions b? in the part that uses the INNER JOIN to connect together the two tables.

The ON statement specifies where the join will occur:

a.Cashier ON b.Cashier

AS ? the word AS is used so you define that City should be the value from the field City in the a table (Cashier) rather than the b table (Trnasactions).  It is written:

 a.City AS City

You may need to use AS several times.  Separate the AS statements with commas, but don?t put a comma between the last AS statement and the word FROM.

The words defined by AS statements can be used in the ?reader? commands to take content from fields in the table.

The last part of string will be the WHERE portion and we want it to find all values in table Transactions in the field ?TransactionTotal? that have values between 10,000 and 100,000.  Instead of hard-coding these values, we will put textboxes on the front page into which you can type 10000 and 100000 and later change them to other numbers.  The values in the textboxes are converted to the type ?double? and put into variables ?lowertotal? and ?uppertotal? defined in the VB code.  We?ll come back to them shortly.

The structure is written:

WHERE b.TransactionTotal < ?? & uppertotal & ?? AND  b.TransactionTotal > ?? & lowertotal & ???

(the above might be hard to  read?it is spelled out as)

(apostrophe quote & uppertotal & quote apostrophe AND b.TransactionTotal > apostrophe quote & lowertotal & quote apostrophe quote)

Because you are mixing text with variables, the & signals that you are changing from one to the other.  A quote signals either the start or end of a text segment.  The apostrophes are placed so that they will encapsulate (i.e. surround) the numbers produced by the variables uppertotal and lowertotal.

When you run this, the computer will fill in the numbers and the computer will see this:

Where b.TransactionTotal < ?100000? AND b.TransactionTotal > ?10000?

You will get quite a bit of practice with using apostrophes, quotes and ampersands (&).  Don?t feel bad if after working hard to get the apostrophes and quotes done correctly you get an error and discover that you forgot an ampersand.

   
ASP.NET CONTROLS & OTHER FRONT PAGE STRUCTURES
 
The control, asp:Textbox, is used to take input from the person viewing the page.  The TextBox used in SQL_Inject_Box used Width and Height to make the TextBox considerably larger.  TextMode="Multiline" was needed so that the box would accept and display multiple lines of SQL code.</p>
 
An asp:Button is used with simple properties (hopefully intuitive).
 
A div is given an ID (example: ID="someDivID") and on the code behind page  we can put text in the div using, someDivID.InnerHtml = "some text typed here."  
 
Everything below is placed on the VB code behind page:
 
IMPORTS 
 
Two Imports lines are used to gain access to System.Data.SqlClient and System.Data.
 
SUBROUTINES 
 
Subroutines are used with the following structure: 
     
Protected Sub SubroutineNameHere(ByVal sender as Object, ByVal e As System.EventArgs) Handels SomeButtonNameHere.Click
 
MAKING THE CONNECTION TO THE TABLE IN THE DATABASE AND THEN DOING SOMETHING
 
The Sqlconnection was written as 
 
Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
       
For what we are doing here, we want to open a connection and then issue a SqlCommand.  We will need  a string as part of the definition of the SqlCommand.  
 
A string, with the arbitrary name "mystring", was given the contents of a textbox as follows:
 
Dim mystring As String = SomeTextBoxNameHere.Text
 
The SqlCommand is defined using the contents of a string (mystring) and the directions for a connection (conn):
 
Dim myCommand As New Sqlcommand(mystring, conn)
 
[authors often use conn for the connection string and cmd for the SqlCommand.]
 
conn.Open() opens the connection string "conn" that we previously defined.  
Conn.Close() closes it.
 
Please notice that there is also a conn.Close().  I recommend typing the line conn.Close() imediately after typing conn.Open() so that you don't forget to have the connection closed.
 
The actions takes place with the line
 
myCommand.ExecuteNonQuery()
 
[comment: you will also be introduced to .ExecuteReader() and .ExecuteScalar()]
 
STRINGBUILDER (collecting lines of text for use in a div)
 
 
Dim sb As StringBuilder = New StringBuilder()
 
sb.Append("You always use append when adding new content a stringbuilder. ")
sb.Append("I'm hoping that from your seeing it used in an example here it will make sense. ")
sb.Append("Notice that it uses parentheses and quotes.")
sb.Append("If it doesn't click with you now, it might after you have seen it several times and you see the pattern to how it is used.")
someDivID.InnerHtml = sb.ToString()
 
 
CURRENT SQL VOCABULARY
1. CREATE TABLE - used to create a table
2. IDENTITY - used in a line along with PRIMARY KEY when choosing a field to be the primary key
3. PRIMARY KEY - used in a line along with IDENTITY when choosing a field to be the primary key
4. NOT NULL - If permitted, a field entry starts out as NULL until you put something into it; you may want to find these (see 11)
5. INSERT INTO - used to start a command that will add a new record to a table
6. VALUES - used to precede a set of parentheses holding the values that will be added to a record when INSERT INTO is used
7. SELECT - used to start a command that will find a record based on criteria defined later in the SQL statement. It gets a little more complicated if a JOIN is used.
8. FROM - used in a statement starting with SELECT-used to "point" to a table
9. WHERE - used in a statement to define the criteria of selection (not limited to SELECT though)
10. AND - can be used if you have two (or more) conditions to add to the WHERE portion
11. IS NULL - If you only want records where the field value is NULL, you type IS NULL (rather than =NULL)
12. INNER JOIN - this creates a "bridge" between two tables (other joins exist but this will do plenty for now)
13. ON - this identifies which fields should be used to make a JOIN
14. AS - used in a JOIN statement to use a single word as a short cut for something larger; a.Name AS Name 
 

Assume that you want to put new data into a table, Cities, but first you need to delete all the data that is there--without destroying the table.

TRUNCATE TABLE Cities 

It's very easy.  However, I STRONGLY recommend that you back up the data you have before using the above line -- just in case.

We are going to be expanding our operation soon to include a program for customers who give repeat business to our hotels.  We will add a table to hold VIP customer information, and a table to hold the dates and places of their visits. 

We need a way to identify a hotel without having to type out a long name like 'Renaissance Moscow Hotel' every time.  We will label hotels with an eight character identity: the first six characters are text and the last two are numeric (hopefully we won't go over the limit of 99 hotels in a particular city...

We want to add a column with the name 'HotelID' and the type 'nchar(8)'.

ALTER TABLE Hotels ADD HotelID nchar(8)

UPDATE Hotels SET HotelID='Paris01' WHERE Hotel='Hotel de Crillon'
UPDATE Hotels SET HotelID='Paris02' WHERE Hotel='Hotel de Vendome'
UPDATE Hotels SET HotelID='Paris03' WHERE Hotel='Hotel Regina'
UPDATE Hotels SET HotelID='Berlin01' WHERE Hotel='The Regent Hotel Berlin'
UPDATE Hotels SET HotelID='Berlin02' WHERE Hotel='Rocco Forte Hotel de Rome'
UPDATE Hotels SET HotelID='Berlin03' WHERE Hotel='Grand Hyatt Berlin'
UPDATE Hotels SET HotelID='Moscow01' WHERE Hotel='Renaissance Moscow Hotel'
UPDATE Hotels SET HotelID='Rome01' WHERE Hotel='Hotel Marco Polo'

CREATE TABLE VIPcustomer
(id int IDENTITY(1,1) PRIMARY KEY,
CustomerID nchar(8) NOT NULL,
FirstName nchar(20),
LastName nchar(20) NOT NULL)

CREATE TABLE VIPvisits
(id int IDENTITY(1,1) PRIMARY KEY,
VisitID nchar(8) NOT NULL,
CustomerID nchar(8) NOT NULL,
date datetime NOT NULL,
HotelID nchar(8) NOT NULL)

The table VIPvisits should be defined as follows:

CREATE TABLE VIPvisits
(id int IDENTITY(1,1) PRIMARY KEY,
VisitID nchar(8) NOT NULL,
CustomerID nchar(8) NOT NULL,
date datetime NOT NULL,
HotelID nchar(8) NOT NULL)

As a result of an error, if you copied the code before I caught the mistake, your table will have a field "Population" that should not be there.

You can remove this unwanted column with:

ALTER TABLE VIPvisits DROP COLUMN Population

 Once this is done, you will have four tables and we can try some more advanced SQL.  Soon we will have a join that takes data from all four tables.  You will notice below that three VIPs have the same name, hence the value of our having a CustomerID in the table. (Fix: the apostrophes are the vertical kind now, rather than the curl type--the curl type will not be accepted)

****

INSERT INTO VIPcustomer (CustomerID, FirstName, LastName) VALUES ('00000100', 'Clinton', 'McDonald')
INSERT INTO VIPcustomer (CustomerID, FirstName, LastName) VALUES ('00000200', 'Andrew', 'Stevenson')
INSERT INTO VIPcustomer (CustomerID, FirstName, LastName) VALUES ('00000300', 'Bill', 'Smith')
INSERT INTO VIPcustomer (CustomerID, FirstName, LastName) VALUES ('00000400', 'Bill', 'Smith')
INSERT INTO VIPcustomer (CustomerID, FirstName, LastName) VALUES ('00000500', 'Bill', 'Smith')

INSERT INTO VIPvisits (VisitID, CustomerID, HotelID, date) VALUES ('00000101','00000100','Paris01','12/31/07')
INSERT INTO VIPvisits (VisitID, CustomerID, HotelID, date) VALUES ('00000102','00000300','Berlin02','12/31/07')
INSERT INTO VIPvisits (VisitID, CustomerID, HotelID, date) VALUES ('00000103','00000200','Paris01','12/31/07')
INSERT INTO VIPvisits (VisitID, CustomerID, HotelID, date) VALUES ('00000104','00000500','Rome01','01/12/07')
INSERT INTO VIPvisits (VisitID, CustomerID, HotelID, date) VALUES ('00000105','00000100','Moscow01','03/15/07')
INSERT INTO VIPvisits (VisitID, CustomerID, HotelID, date) VALUES ('00000106','00000200','Moscow01','03/15/07')
INSERT INTO VIPvisits (VisitID, CustomerID, HotelID, date) VALUES ('00000107','00000400','Berlin02','04/03/07')