Table Expressions Part 1

--Derived tables
Use CommunityAssist

Select Personkey, [Last], [First], City from (Select p.Personkey, PersonLastName [Last],
   PersonFirstName [First], City
   From Person p
   inner Join PersonAddress pa
   on p.PersonKey=pa.personKey
   where city = 'Bellevue') as BellevueResidents

   Select RequestMonth, ServiceName, Count(ServiceName) as [Count] From
   (Select month(GrantDate) RequestMonth, ServiceName
   From ServiceGrant sg
   inner join CommunityService cs
   on sg.ServiceKey=cs.ServiceKey) as ServiceCount
   Group by RequestMonth, ServiceName

   --Common Table Expressions

   with BellevueResidents as
  (
  Select p.Personkey, PersonLastName [Last],
     PersonFirstName [First], City
     From Person p
     inner Join PersonAddress pa
     on p.PersonKey=pa.personKey
     where city = 'Bellevue'
  )
  Select [Last], [First], City From BellevueResidents

  with ServiceCount as
  (
    (Select month(GrantDate) RequestMonth, ServiceName
   From ServiceGrant sg
   inner join CommunityService cs
   on sg.ServiceKey=cs.ServiceKey)
  )
  Select RequestMonth, ServiceName, Count(ServiceName) as [Count] From ServiceCount
  Group by RequestMonth, ServiceName

  Declare @EmployeeKey int;
  Set @EmployeeKey=4;

  with EmployeeServiceGrants as
  (
  Select Month(GrantDate) as [Month], GrantKey
  From ServiceGrant
  Where EmployeeKey=@EmployeeKey
  )
  Select [Month], Count(GrantKey) as Total
  From EmployeeServiceGrants
  Group by [Month];

What to do with first Service Assignment

1. New Empty Site:

2. ADo Entities: connects to database

Artist
Venue
Show
ShowDetail
save

3. wcf service: name it --IService.cs, Service.cs. Service.srv

4. Set up Interface

List GetArtists();
ListGetShows();
ListGetVenues();
List GetShowByVenue(string venuename);
ListGetShowByArtist(string artistname);

.

5. Build the service

Subqueries

Use CommunityAssist
--subqueries

Select PersonLastName, GrantAllocation
From Person p
Inner join ServiceGrant sg
on p.PersonKey=sg.PersonKey
Where GrantAllocation=
    (Select Min(GrantAllocation) from serviceGrant)

Select PersonLastName, PersonFirstName
From Person where Personkey in
(Select PersonKey from Employee) 

Select PersonLastName, PersonFirstName
From Person where Personkey in
 (Select PersonKey from Employee
  where employeeKey in 
   (Select EmployeeKey from ServiceGrant))

--The total and count GrantTotal granted and the percent
--of the grants for each service

Select ServiceName, Sum(GrantAllocation) as Total, 
(Select max(GrantAllocation) from ServiceGrant) as Biggest,
(Select Sum(GrantAllocation) from ServiceGrant) GrandTotal
From ServiceGrant sg
Inner join CommunityService cs
on sg.ServiceKey=cs.ServiceKey
Group by ServiceName

Select ServiceName,count(GrantAllocation) as number, Sum(GrantAllocation) as Total, 
(Select max(GrantAllocation) from ServiceGrant) as Biggest,
(Select Sum(GrantAllocation) from ServiceGrant) GrandTotal,
format(Sum(GrantAllocation) / (Select Sum(GrantAllocation) from ServiceGrant), '###.##%') [Percent]
From ServiceGrant sg
Inner join CommunityService cs
on sg.ServiceKey=cs.ServiceKey
Group by ServiceName

Select ServiceName, Max(GrantAllocation) as largest
From CommunityService cs
Inner Join ServiceGrant sg
on cs.ServiceKey =sg.ServiceKey
Group by ServiceName

 Select ServiceKey, avg(GrantAllocation)
 From ServiceGrant
 Group by ServiceKey

 Select ServiceKey, GrantAllocation
 From ServiceGrant sg1
 Where GrantAllocation >
 (Select Avg(GrantAllocation) 
 From ServiceGrant sg2
 Where sg1.ServiceKey=sg2.ServiceKey)

 Select Avg(GrantAllocation) from ServiceGrant
 Where ServiceKey =2

Normalization

Here is the diagram for the Book Database

Here are pictures of the Access Database

Book table

Publisher table

Author table

AuthorBook table

Here is the example of a weak entity

Joins

Select * From sys.Databases
Use CommunityAssist
Select name from sys.procedures

--joins
-- cross join
Select PersonLastName, Street, City from Person, PersonAddress

Select PersonLastName, Street, City from Person
Cross Join PersonAddress

--inner joins
Select PersonLastName, ContactInfo, ContactType.ContactTypeKey,
[ContactTypename]
From Person, PersonContact, ContactType
Where Person.PersonKey=PersonContact.Personkey
And ContactType.ContactTypeKey=PersonContact.ContactTypeKey

Select PersonLastName, ct.ContactTypeKey, ContactInfo, ContactTypeName
From Person as p
inner join PersonContact as pc
On p.PersonKey=pc.PersonKey
Inner join ContactType ct
on pc.ContactTypeKey=ct.ContactTypeKey

Select Distinct PersonLastname,  donationDate, sum(DonationAmount)
from Person p
join PersonContact pc
on p. PersonKey = pc.PersonKey
join donation d
on d.PersonKey=p.PersonKey
Group by PersonLastname,  donationDate


--outer Joins
Select Distinct ServiceName, sg.ServiceKey from CommunityService cs
left outer join ServiceGrant sg
on cs.serviceKey=sg.ServiceKey
Where sg.ServiceKey is null

Select Distinct ServiceName, sg.ServiceKey from CommunityService cs
full outer join ServiceGrant sg
on cs.serviceKey=sg.ServiceKey
Where sg.ServiceKey is null

Select Distinct PersonLastName, ContactInfo, ContactTypeName
From Person p
Inner Join PersonContact pc
on p.PersonKey=pc.PersonKey
right join ContactType ct
On Ct.ContactTypeKey=pc.ContactTypeKey
Where ContactInfo is null

Selects 2

Use CommunityAssist;

Select EmployeeKey, EmployeeMonthlySalary, 
   EmployeeMonthlySalary * 12 as [Annual Salary]
   From Employee;

Select Avg(EmployeeMonthlySalary) from Employee
Where Not EmployeeMonthlySalary =0;

Select Sum(EmployeeMonthlySalary) from Employee;
Select Min(EmployeeMonthlySalary) from Employee;
Select Max(EmployeeMonthlySalary) from Employee;
Select Count(EmployeeMonthlySalary) from Employee;

Select * From ServiceGrant;

Select ServiceKey, sum(GrantAllocation) as Total from ServiceGrant
Where ServiceKey > 2
Group by ServiceKey
having sum(GrantAllocation) > 2500;

Select ServiceKey, sum(GrantAllocation) as Total from ServiceGrant
Group by ServiceKey
having sum(GrantAllocation) > 2000
Order by total desc;

Select Distinct Year(GrantDate) from ServiceGrant;
Select Distinct Month(GrantDate) from ServiceGrant;

Select Year(GrantDate) as Year, Month(GrantDate) as [Month], 
case Month(GrantDate)
when 8 then 'August'
when 9 then 'September'
end as [word Month],
Sum(GrantAllocation) as Total from ServiceGrant
Group by Year(GrantDate), Month(GrantDate);

Select Distinct DatePart(mm,GrantDate), Datepart(dd,GrantDate) from serviceGrant
Select Distinct DatePart(hour, GrantDate) From ServiceGrant
Where grantKey=2;

Select GrantDate, GrantReviewDate, DateDiff(dd,GrantDate,GrantReviewDate) 
as [Processing Time]
From ServiceGrant;

Select DateDiff(minute,'1/14/2016','1/14/2020');

Select GetDate() as Today;

Select DateAdd(dd, 365, GetDate());

Select * from Employee;

Select EmployeeKey, EmployeeHireDate
From Employee
Where Year(EmployeeHireDate) between 2003 and 2008;

--###-##-####
Select substring(EmployeeSSNumber,1,3) + '-' 
+ substring(EmployeeSSNumber,4,2) + '-' 
+ substring(EmployeeSSNumber,6,4)  From Employee;

Select format(cast(EmployeeSSNumber as int),'000-00-0000') from Employee;

Selects 1 Examples

use CommunityAssist

Select * from Person;

Select PersonFirstName as [First Name], PersonLastName [Last Name], PersonUserName as [User Name]
From Person
Order by PersonLastName Desc, PersonFirstName Desc;

Select * From PersonAddress
Where City='Kent';

Select * From PersonAddress
Where Not City='Seattle';

Select * From PersonAddress
Where City !='Seattle';

Select * From PersonAddress
Where City <>'Seattle';

Select * From PersonAddress 
Where Apartment is not null
And (City ='Seattle' Or City ='Bellevue');

Select GrantKey, GrantAmount, GrantAllocation from ServiceGrant
Where GrantAmount>GrantAllocation;


Select * From ServiceGrant
Where GrantDate Between '2013-08-09' and '2013-08-09';

Select PersonLastName 
from Person 
where PersonLastName like '_anner_';

Select Distinct Top(5) GrantAmount from ServiceGrant
Order by GrantAmount desc;


Select Distinct  GrantAmount from ServiceGrant
Order by GrantAmount desc
Offset 5 rows Fetch next 5 rows only;

Creating and altering tables

Create database Assignment1Example;

use Assignment1Example;
/*
This is a multi line
comment
*/

Create Table Person
(
  -- identity autoincrements the integer
      PersonKey int identity(1,1) primary key,
   PersonLastName nvarchar(255) not null,
   PersonFirstname nvarchar(255) null,
   PersonAddress nvarchar(255) not null,
   PersonCity nvarchar(255) default 'Seattle',
   PersonState nchar(2),
   PersonZip nchar(10) not null

);

Create table Donation
(
 DonationKey int identity(1,1),
 PersonKey int not null,
 DonationDonationDate date default GetDate(),
 DonationAmount decimal(10,2),
 constraint PK_Donation  Primary key (DonationKey),
 constraint Fk_Person Foreign Key (PersonKey)
             references Person(PersonKey)

);

Create table Volunteer
(
 VolunteerKey int identity(1,1),
 PersonKey int not null,
 VolunteerStartDate Date not null,
 VolunteerEndDate Date not null
)

Alter table Volunteer 
Add Constraint PK_Volunteer Primary Key (VolunteerKey)

Alter Table Volunteer
Add Constraint FK_VolunteerPerson Foreign Key (PersonKey)
   references Person(PersonKey)

   Create table LinkToSomething
   (
      PersonKey int,
   DonationKey int,
   Constraint pK_Link primary key(PersonKey, DonationKey),
   constraint FK_PersonLink foreign key(PersonKey)
       references Person(PersonKey),
   Constraint FK_Donor Foreign key (DonationKey)
        references Donation(donationKey)
   )


   Alter table Person
   Drop column PersonState

   Alter Table Volunteer
   add VolunteerLocation Nvarchar(255)

   Begin tran

   Rollback tran
   Commit tran

Scope from in class

History

---

Client list for a lawyer

The practice was small but grew, and it became harder to track people and billable time. Originally on paper, but paper gets lost and paper is hard to search. A database would tracking and keeping track of client.

Scope

The database must track client information. It should track all the paperwork associated with each client. Track case deadlines. Add and archive clients. Track cases and case types. Track lawyers and which cases they work on. Track each lawyer’s billable hours by case. Track retainers. Generate Bills, Reports, deadline alert. Only firm and secretaries lawyers should be able to access the database.

Constraints.

Will not write front end. Won’t talk directly to the state databases.

Time line and deliverables

Gather information—
Requirements and Business Rules
Design
Build prototype and add sample data
Test sql