how do u create the following table in sql-server?

Question

i have a seql server database. i need to create the following table.
columun names( firsname, email, location)
create table mytable(firstname varchar(30),email varchar(40), location(100)); this is normal creating table.
but i need to add (city,state, and country,)under location column, so how can i do that)
so that under the "location" column i will have three column under it.
these are "city, state, and country" so if possible how can i do that?
thanks

Answer 1

What you are asking is impossible. You are trying to create a "type" or "class" called "location" which contains 3 strings and databases don't handle that sort of thing. Your only option is to create them separately like this: create table mytable(firstname varchar(30),email varchar(40), city varchar(50), state varchar(2), zip varchar(10));



When you get back data you can do it like this:

select

firstname,

email,

city + state + zip as location

from

mytable



so that city, state, and zip will appear as one column called "location"

Answer 2

To add columns, you need to use the ALTER TABLE SQL command. Like this:

ALTER TABLE mytable

ADD city varchar(30)

ADD state varchar(2)

ADD country varchar(30)



I capitalize the SQL keywords, and defined the "state" column to be a 2 character string to allow for the abbreviation instead of the full state name.

Answer 3

You need two tables where location in mytable be the foreign key referencing to another table with city,state, and country columns.