Create Database and Tabe

Create Database
CREATE DATABASE  -----> syntax
*****you can use the CREATE statement only to create objects onthe local server


CREATE DATABASE
[ON [PRIMARY]
([NAME = <’logical file name’>,]
FILENAME = <’file name’>
[, SIZE = ]
[, MAXSIZE = size in kilobytes, megabytes, gigabytes, or terabytes>]
[, FILEGROWTH = ])]
[LOG ON
([NAME = <’logical file name’>,]
FILENAME = <’file name’>
[, SIZE = ]
[, MAXSIZE = size in kilobytes, megabytes, gigabytes, or terabytes>]
[, FILEGROWTH = ])]
[ COLLATE ]
[ FOR ATTACH [WITH ]| FOR ATTACH_REBUILD_LOG| WITH DB_CHAINING
ON|OFF | TRUSTWORTHY ON|OFF]
[AS SNAPSHOT OF ]
[;]

CREATE DATABASE Accounting
ON
(NAME = ‘Accounting’,
FILENAME = ‘c:\Program Files\Microsoft SQL Server\
MSSQL.1\mssql\data\AccountingData.mdf’,
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5)
LOG ON
(NAME = ‘AccountingLog’,
FILENAME = ‘c:\Program Files\Microsoft SQL Server\
MSSQL.1\mssql\data\AccountingLog.ldf’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB)
GO

Create Table:::::::::::::::::::::

CREATE TABLE [database_name.[owner].]table_name
(
[[DEFAULT ]
|[IDENTITY [(seed, increment) [NOT FOR REPLICATION]]]]
[ROWGUIDCOL]
[COLLATE ]
[NULL|NOT NULL]
[]
|[column_name AS computed_column_expression]
|[]
[,...n]
)
[ON {|DEFAULT}]
[TEXTIMAGE_ON {|DEFAULT}]


USE Accounting
CREATE TABLE Customers
(
CustomerNo int IDENTITY NOT NULL,
CustomerName varchar(30) NOT NULL,
Address1 varchar(30) NOT NULL,
Address2 varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Contact varchar(25) NOT NULL,
Phone char(15) NOT NULL,
FedIDNo varchar(9) NOT NULL,
DateInSystem smalldatetime NOT NULL
)

table name::::::::::::::
--capitalize the first letter and use lowercase for the remaining letters.
--Limit the use of abbreviations.
--When building tables based on other tables (usually called linking or associate tables), you
should include the names of all of the parent tables in your new table name.
--When you have two words in the name, do not use any separators (run the words together)—

Default::::::::::::::
this is the value you want to be used for any rows that are inserted without a user-supplied value for this particular column.

Identity (almost like primary Key Constraints but wid one difference):::::::::::::::::::::::::::::::::
when you make a column an identity column, SQL Server automatically assigns a sequenced number to this column with
every row you insert. The number that SQL Server starts counting from is called the seed value, and the
amount that the value increases or decreases by with each row is called the increment.An identity column must be numeric, and, in practice, it is almost always implemented with an integer
or bigint datatype.
*******an IDENTITY column and a PRIMARY KEY are completely separate notions— in IDENTITY columns you can reset the seed value and count back up through values you’ve used before

Column Constraints::::::::::::::::::::::::::::
they are restrictions and rules that you place on individual columns about the data that can be inserted into that column. (for ex. if a column shd have the month(1 to 12) it should not allow 13 to be inserted.

Computed Columns::::::::::::::::::::::::::::::::::
its value is derived from other columns in the table by executing some .
syntax:
AS
EX: ExtendedPrice AS Price * Quantity

Table Constraints:::::::::::::::::::::::::::::::::::
table-level constraints include PRIMARY and FOREIGN KEY constraints, as well as CHECK constraints.

Creating a Table::::::::::::::::::::::::::::::::::::
Ex:
CREATE TABLE Customers