Sub query example and Create and Alter Tables

Here is the subquery example

--get total number of grants, count denied, count reduced, percents

Select count(GrantKey) [Total Grants],
(Select count(GrantKey) From ServiceGrant where GrantApprovalStatus='denied') [Total Denied],
(Select count(GrantKey) From ServiceGrant where GrantApprovalStatus='reduced')[Total Reduced],
cast(
Cast((Select count(GrantKey) From ServiceGrant where GrantApprovalStatus='denied')as decimal(5,2))
/cast
(count(GrantKey)as Decimal(5,2)) * 100 as Decimal(5,2)) as [Percent Denied],
cast(
Cast((Select count(GrantKey) From ServiceGrant where GrantApprovalStatus='reduced')as decimal(5,2))
/cast
(count(GrantKey)as Decimal(5,2)) * 100 as Decimal(5,2)) as [Percent Reduced]

From ServiceGrant

---

Here are the Table and alter table examples with comments

Use Master
--create a new database according to the model template
Create Database BookReview
--make sure you change the context to the new
--database
Use BookReview

--create a table
Create table Book
(
--identity creates an autonumber, in this case
--starting at 1 and incrementing by 1s
--the primary key is declared inline which
--means SQL Server names it
 BookKey int identity(1,1) primary Key,
 BookTitle NVarchar(255) not null,
 BookISBN NChar(13) null,
 BookPublishYear Int 
)

Create Table Author
(
 AuthorKey int Identity(1,1) not null,
 --declaring the key in a constraint
 --allows you to name it
 Constraint PK_Author Primary Key(AuthorKey),
 AuthorName Nvarchar(255) not null,
 AuthorDates Nvarchar(255)
)

Create Table AuthorBook
(
 BookKey int not null,
 AuthorKey int not null,
 --a composite primary key
 Constraint PK_AuthorBook Primary Key(bookKey, AuthorKey),
 --two foreign keys
 --the foriegn key takes a field in this table and relates it
 --"references" the field in another table
 Constraint FK_Book Foreign Key(BookKey) references Book(BookKey),
 
 Constraint FK_Author Foreign Key(AuthorKey) references Author(AuthorKey)
 
)

Create Table Review
(
 ReviewKey int identity(1,1) not null,
 BookKey int not null,
 --a default constraint
 ReviewDate Date default GetDate(),
 ReviewerKey int not null,
 ReviewRating int not null,
 --a check constraint lets you set a set or range of 
 --acceptable values for a field
 Constraint chk_Rating Check (ReviewRating between 0 and 5),
 --NVarchar(max) lets you store up to 2 gigs of text, but
 --the text is not stored in the table itself, only a pointer to
 --the text is stored in the table. You can retrieve the contents,
 --but you cannot query the contents of the field or use it in a 
 --where clause
 ReviewText Nvarchar(max) not null
 

)

Create table Reviewer
(
 ReviewerKey int identity(1,1) not null,
 Reviewername nvarchar(255) not null,
 --the unique constraint forces the email
 --to be unique, to never repeat
 ReviewerEmail nvarchar(255) not null unique

)

--to change an existing table you either need to drop and 
--recreate it or alter it

--adds a primary key after the table has been made
Alter table Review
Add constraint PK_Review Primary Key(ReviewKey)

--add a foreign key
Alter Table Review
Add Constraint fk_BookRev Foreign Key(bookKey) References Book(BookKey)

Alter table Reviewer
Add constraint PK_Reviewer Primary Key(ReviewerKey)

Alter Table Review
Add Constraint fk_Reviewer Foreign Key(ReviewerKey) References Reviewer(ReviewerKey)

--add a column
Alter table Reviewer
Add ReviewerPhone nvarchar(13) not null

--Drop a column
Alter Table Reviewer
Drop Column ReviwerPhone

--drop the table itself
Drop Table Review