Creating and Altering Tables

--create a new database
--we are not adding any options so
--it will be a copy of the model
--database
Create Database SoftwareTracker

Use SoftwareTracker

--create a table
--identity gives you an auto number
--the first digit (1,1) is the starting number
--the second is the increment, what you want
--to count by
--nvarchar is variable length unicode
--nchar is fixed length unicode
--char and varchar are ascii
Create table Software
(
SoftwareID int identity(1,1),
Constraint PK_Software Primary Key (SoftwareID),--set and name a primary key,
SoftwareName nvarchar(255) Not null, --not null means required
SoftwareCompany nvarchar(255) not null,
SoftwarePrice Decimal(10,2) not null --could use money (4 decimal places)
)

Create Table SoftwareUsers
(
userID int identity(1,1) primary key,--let SQL Server Name key
userEmployeeNumber nvarchar(10) unique, --unique constraint
UserLastName nvarchar(255) not null,
UserDept nvarchar(255) default 'IT', --default constraint
Constraint ck_userDept Check (UserDept in ('IT', 'HR', 'ACC')),
UserPhone Nchar(10)
)

--could have done and named all our constraints
--Create Table SoftwareUsers
--(
-- userID int identity(1,1),
-- userEmployeeNumber nvarchar(10),
-- UserLastName nvarchar(255) not null,
-- UserDept nvarchar(255) default 'IT',
-- UserPhone Nchar(10)
-- Constraint ck_userDept Check (UserDept in ('IT', 'HR', 'ACC')),
-- Constraint PK_SoftwareUser Primary Key (userID),
-- Constraint UQ_EmpNumber Unique(userEmployeeNumber)
--)

--for this table we left the primary key
--to the alter table statement
--it is possible to make the tables and then
--add all the constraints after with alter table
--statements
Create table Computers
(
ComputerID int Identity(1,1),
UserID int,
Location nvarchar(255),
Constraint FK_User Foreign Key(UserID)
references SoftwareUsers(userID)
)

--alter the table to add the primary key
Alter Table Computers
Add Constraint PK_Computers Primary Key (ComputerID)

--alter the table to drop the check contstraint
Alter Table SoftwareUsers
Drop Constraint ck_userDept

--alter the table to add a new column
Alter Table Computers
Add ComputerOS nvarchar(255)

--alter a table to drop a column
Alter Table Softwareusers
Drop Column UserPhone

--create the linking table between software and
--computer
Create Table SoftWareComputer
(
ComputerID int,
SoftwareID int,
Primary Key (ComputerID, SoftwareID),--composite primary key
Constraint FK_Computer Foreign Key (ComputerID)
References Computers(ComputerID),
Constraint FK_Software Foreign Key(SoftwareID)
References Software(SoftwareID)

)