Insert SQL 1 PM Class

Use Exampledb

Insert into Customer(CustomerID, CustomerLastName, 
CustomerFirstName, CustomerEmail)
Values(5,'Nelson','Jeb','JNelson@msn.com'),
(6,'Johnson','Leah','Ljohnson@hotmail.com'),
(7, 'Manning','Josh','Jmanning@outlook.com')

Select * From Customer

Insert into Sale(SaleDate, CustomerId)
Values(GetDate(),6)
Insert into SaleDetail(SaleNumber, ItemID, 
DiscountPercent)
Values (IDENT_CURRENT('Sale'), 3, .05)

Select * from Sale
Select * from SaleDetail

First SQL

--Select, Join, inserts
--fourth generation language
--2nd generation assembly
--3rd generation Cobol C C++ C# Python procedural print
--fourth generation how, what to do

use Community_Assist

Select * from Person

Select PersonFirstName, 
PersonLastName, 
PersonEmail
From Person
Order by PersonLastName;

Select PersonFirstName, 
PersonLastName, 
PersonEmail
From Person
Order by PersonLastName desc

Select * from PersonAddress
Where PersonAddressCity='Kent'

Select * from PersonAddress
Where PersonAddressCity='Seattle'

Select * from PersonAddress
Where Not PersonAddressCity='Seattle'

Select * from PersonAddress
Where PersonAddressApt is Not null

Select * from Donation 
where DonationDate between'8/10/2015' and '8/11/2015'

Select Count(DonationKey) as [Count] from Donation
Where DonationDate between'8/10/2015' and '8/11/2015'

Select sum(DonationAmount) as Total from Donation
Where DonationDate between'8/10/2015' and '8/11/2015'

Select * from Donation

Select PersonFirstName, PersonLastName,
PersonEmail, DonationDate, DonationAmount
From Person
inner join Donation
On Person.PersonKey=Donation.PersonKey

Select * from GrantRequest

Select GrantRequestDate, PersonLastName, 
GrantTypeName, GrantRequestAmount
From GrantRequest
inner join Person
on Person.PersonKey=GrantRequest.PersonKey
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey

Insert into Person(PersonLastName, 
PersonFirstName, PersonEmail,  
PersonEntryDate)
Values('Phalan','Declan','pd@gmail.com',getDate())

Insert into PersonAddress(PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, PersonAddressState, 
PersonAddressZip, PersonKey)
Values(null,'1234 South','Marysville','WA', '98100', 
IDENT_CURRENT('Person'))

Select * from PersonAddress

Insert into Donation(PersonKey, DonationDate, 
DonationAmount)
Values(130,getDate(),5.00)

Select * from Position

Insert into Position(PositionName, PositionDescription)
Values('Professor','Teaches economics'),
('HR', 'Human Resources Manager'),
('Advertising', 'Publicize the charity')

Set Operators and Modifying Data

--set operators--

--union
Use metroAlt

Select EmployeeLastName lastname, EmployeeFirstName firstname, 
EmployeeEmail email, EmployeeCity City
From Employee
Union
Select PersonLastName, PersonFirstName, PersonEmail, PersonAddressCity
From Community_Assist.dbo.person p
inner join Community_Assist.dbo.PersonAddress pa
on p.PersonKey=pa.PersonKey

Select EmployeeCity City 
From Employee
Intersect
Select PersonAddressCity
From Community_Assist.dbo.PersonAddress


Select PersonAddressCity city
From Community_Assist.dbo.PersonAddress
Intersect
Select EmployeeCity  
From Employee

Select PersonAddressCity city
From Community_Assist.dbo.PersonAddress
Except
Select EmployeeCity  
From Employee

Select EmployeeCity  
From Employee
Except
Select PersonAddressCity city
From Community_Assist.dbo.PersonAddress

--Modifying Data--
Use Community_Assist

Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonPassWord, PersonEntryDate, 
PersonPassWordSeed)
Values('Jordan','Michael','mj@gmail.com',null,GetDate(),null)

Insert into PersonAddress(PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, PersonAddressState, 
PersonAddressZip, PersonKey)
Values(Null,'10002 Broad Street','Charlotte','NC', '90110', IDENT_CURRENT('Person'))

Insert into Donation(PersonKey, DonationDate, 
DonationAmount)
values(3, getDate(),100),
(109, getDate(),400),
(20, getDate(),250)

Create table Person2
(
PersonKey int, 
PersonLastName nvarchar(255), 
PersonFirstName nvarchar(255), 
PersonEmail nvarchar(255), 
PersonEntryDate DateTime
)

Insert into Person2(PersonKey, PersonLastName, 
PersonFirstName, PersonEmail, PersonEntryDate)
Select PersonKey, PersonLastName, 
PersonFirstName, PersonEmail, PersonEntryDate
From Person

Update Person2
Set PersonFirstName='Jason'
Where personKey = 1

Update Person2
set PersonlastName='Manning',
PersonEmail = 'LManning@gmail.com'
Where PersonKey=3

Begin tran

Update Person2
Set PersonLastName='Smith'


Select * from Person2
rollback tran
Commit tran

Truncate table Person2

Begin tran

Delete from Person2 where Personkey=130

Rollback tran

Drop table Person2

Select * from GrantType

Update Granttype
Set GrantTypeMaximum=GrantTypeMaximum * 1.05


Update Granttype
Set GrantTypeLifetimeMaximum=GrantTypeLifetimeMaximum * 1.05

NewPersonClass

Add this to your model and use it for the registration

    public class NewPerson
    {
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }
        public string PlainPassword { get; set; }
        public string Apartment{ get; set; }
        public string Street{ get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zipcode { get; set; }


    }
}

Table Expressions (Queries in the FROM), VIews

Use MetroAlt

Select Year(BusScheduleAssignmentDate) [Year], Sum(riders) Annual,
Avg(riders) Average,
(Select sum(riders) From Ridership) Total,
(Cast(Sum(riders)as decimal(10,2))/(Select sum(riders) From Ridership))*100 [Percent]
From ridership
Inner Join BusScheduleAssignment
on ridership.BusScheduleAssigmentKey=BusScheduleAssignment.BusScheduleAssignmentKey
Group by Year(BusScheduleAssignmentDate)
Order by Year

--table expressions
Use Community_Assist

Select LastName, FirstName, Email, City
From (Select PersonLastName [LastName],
PersonFirstName FirstName,
PersonEmail Email,
PersonAddressCity City
From Person
Inner join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey
Where PersonAddressCity='Bellevue') as BellevueResidents

Select RequestMonth, GrantTypeName, count(GrantTypekey) as [Count] From
   (Select month(GrantRequestDate) RequestMonth, sg.GrantTypeKey, GrantTypeName
   From GrantRequest sg
   inner join GrantType cs
   on sg.GrantTypeKey=cs.GrantTypeKey) as ServiceCount
   Group by RequestMonth, GrantTypeName
   Order by RequestMonth
   
--Common table expressions
with BellevueResidents as
(
   Select PersonLastName LastName,
   PersonFirstName FirstName,
   PersonEmail Email,
   PersonAddressCity City
   From Person
   Inner Join PersonAddress
   On person.PersonKey=PersonAddress.PersonKey
   where PersonAddressCity='Bellevue'
)
Select LastName, FirstName, Email, City
From BellevueResidents;

with ServiceCount as
(
 Select month(GrantRequestDate) RequestMonth, sg.GrantTypeKey, GrantTypeName
   From GrantRequest sg
   inner join GrantType cs
   on sg.GrantTypeKey=cs.GrantTypeKey
)
Select RequestMonth, GrantTypeName, Count(GrantTypeKey) [Count]
From ServiceCount
Group by RequestMonth, GrantTypeName
Order by RequestMonth;

Declare @City Nvarchar(255)='Kent';
--Set @City='Kent';

with BellevueResidents as
(
   Select PersonLastName LastName,
   PersonFirstName FirstName,
   PersonEmail Email,
   PersonAddressCity City
   From Person
   Inner Join PersonAddress
   On person.PersonKey=PersonAddress.PersonKey
   where PersonAddressCity=@City
)
Select LastName, FirstName, Email, City
From BellevueResidents;
go

create function fx_CityFunction
(@City nvarchar(255))
returns table
As
return
   Select PersonLastname , 
   PersonFirstName ,
   PersonEmail ,
   PersonAddressCity
   From Person
   inner join PersonAddress
   on person.PersonKey = PersonAddress.PersonKey
   Where PersonAddressCity=@City


Select * from dbo.fx_CityFunction('Shoreline')

go
Create view Vw_HumanResouces
As
Select PersonFirstName [First name],
PersonLastname [Last Name],
PersonEmail [Email],
EmployeeHireDate [Hire Date],
EmployeeAnnualSalary [Annual Salary],
PositionName [Position]
From Person
Inner Join Employee
On Person.PersonKey=Employee.PersonKey
Inner Join EmployeePosition
On Employee.EmployeeKey = EmployeePosition.Employeekey
inner join Position
on Position.PositionKey=EmployeePosition.PositionKey

go

Select * From Vw_HumanResouces
Where Position='Associate'

Select [Last Name] from Vw_HumanResouces

Go
Alter view Vw_HumanResouces with Schemabinding
As
Select PersonFirstName [First name],
PersonLastname [Last Name],
PersonEmail [Email],
EmployeeHireDate [Hire Date],
EmployeeAnnualSalary [Annual Salary],
PositionName [Position]
From dbo.Person
Inner Join dbo.Employee
On dbo.Person.PersonKey=dbo.Employee.PersonKey
Inner Join dbo.EmployeePosition
On dbo.Employee.EmployeeKey = dbo.EmployeePosition.Employeekey
inner join dbo.Position
on dbo.Position.PositionKey=dbo.EmployeePosition.PositionKey

go
Begin tran
Alter table Person
Drop column PersonEmail
Rollback tran

Vince's Requirements (1 PM Class)

Requirements for Vince's Database
Album(AlbumID, Title, Year, studio)<=> Artist(ArtistID Artistname)
Track inventory (InventoryID, AlbumID, condition, pricepaid, DateAquired, Sale Price)
Record Purchases (vince purchasing)
Employees (employeeId, name, email)
Sales(SaleNumber, Date, EmployeeID, CustomerID (null))
SaleDetail(SaleDetailId, SaleNumber, InventoryID, Discount)
Customer(CustomerID, CustomerName, CustomerEmail)
Requests(Albumid, Date, CustomerID)

Requirements--something the database has to do to work successfully
Business Rules 150%, 25% discount for 10

Adding Github to Visual Studio

1. Launch Visual Studio
2. Go to TOOLS on the Menu
3. Choose EXTENSIONS AND UPDATES
4. In the Dialog Box click ONLINE
5. In the search bar type "Github"
6. Choose GITHUB EXTENSIONS FOR VISUAL STUDIO
7. Download them
8. Close Dialog and Visual Studio
9. Installer will come up
10. Say yes to allow changes
11. MODIFY
12. This can take a couple of minutes
13. When done close dialog and open Visual Studio
14. In the START window under OPEN click GITHUB
15. Enter your github user name and password
16. Close dialog
17. Start a new project
18. There should be a toolbar at the bottom of Visual Studio with the name, master and an up arrow with commits
19. Click the up arrow.
20. Publish to GitHub

SubQueries

Use Community_Assist

Select Max(DonationAmount) [Max]
From Donation

-- simple subquery in where clause
Select PersonFirstName, PersonLastName, DonationAmount
from person 
inner join Donation 
on person.PersonKey=donation.PersonKey
Where DonationAmount = (Select Max(donationAmount) from Donation)

Select PersonAddressStreet, PersonAddressCity, 
PersonKey, PersonAddressZip
From PersonAddress
where PersonAddressCity in ('Bellevue', 'Kent', 'Shoreline')

Select PersonLastName, PersonfirstName, PersonEmail
From person
Where Personkey in (Select personkey from Employee)

Select PersonLastName, PersonfirstName, PersonEmail
From person
Where Personkey in 
(Select personkey from Employee
where EmployeeKey in 
(Select EmployeeKey from GrantReview))

Select GrantTypeName 
from GrantType
Where GrantTypeKey not in 
(Select GrantTypeKey from GrantRequest)

--sub queries in the select
Select GrantTypeName, 
Sum(GrantRequestAmount) Total,
(Select sum(GrantRequestAmount) From GrantRequest) GrantTotal,
(Sum(GrantRequestAmount) / 
    (Select Sum(GrantRequestAmount) from GrantRequest)) * 100  [Percent]
From GrantType
inner join GrantRequest
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName

--Coordinated subqueries are queries in which the subquery 
--references the outer query. The subquery matches
--the granttypekey from gr1, the outer query with the granttypeKey
--from gr2 inside the subquery. The effect is to make sure
--that like is matched with like. GrantTypeKey 1 (Food) is matched 
--only against other granttypeKey 1 values, GrantTypeKey 2 (Rent)
--is matched only against other grantTypeKey 2 values, etc.
--a coordinated subquery is the SQL equivalent of a recursive
--function in other programming languages, and like them  
-- requires a lot of processor time 


Select GrantTypeKey, GrantRequestAmount
From GrantRequest gr1
Where GrantRequestAmount > 
(Select avg(GrantRequestAmount)
from GrantRequest gr2
Where gr1.GrantTypeKey=gr2.GrantTypeKey)

Select GrantTypeKey, avg(GrantRequestAmount) Average from GrantRequest
Group by GrantTypeKey

use Community_Assist
Create table PersonZ
(
  PersonKey int primary key,
  Lastname nvarchar(255),
  FirstName nvarchar(255),
  Email nvarchar(255)
)

Insert into PersonZ(PersonKey, Lastname, FirstName, Email)
Select [PersonKey],[PersonLastName],[PersonFirstName],[PersonEmail]
From Person
Where PersonLastName Like 'Z%'

Select * From Personz

Book ERD

Normalization.

Normalization

--standard white papers

Anomalies Update anomaly--update info in more than one place Insert Anomaly: can't insert one thing because it depends on another, and can't insert the other because it depends on the first (a loop) Deletion Anomaly: If you delete something you unintentionally lose something else.

First normal form

 no repeating groups and no arrays
CD Tracks Track1, Track2, track3
Tracks (My Generation, Substitute, pinball wizard)
anything that can have multiple values, should be seperated out into its own table
--solution
CD(CdID, Title, Year)
Track(TrackID, CDID, TrackTitle)

Second Normal Form

Every entity should be about only one thing.
Should not have any functional dependencies
Remove Blocks of Attributes that relate to each other but not
the main topic (the key)
For example if these attributes were in CD
StudioName, StudioAddress, StudioURL, 

Third normal form

Every entity should be about only one thing.
Every attribute should describe the main thing.
transitive dependency
artist, artistCountry

Vincent Vinyl Requirements Morning

Requirements:

• Track purchases: from who, amount paid, date
• Album title artist genres
• Track inventory: what's in stock, condition
• Track Customers (if willing, or if request)
• Employees
• Returns?
• Sales album sales, What they sold for, Customer,
• discount percentage
• Request album date

Business Rules

• Customer information only if willing
or if making request
• Always get information about person when
• Purchases 150 % markup though vince can offer discounts
• Customers who buy 10 albums get a 25%
• Each album treated individually with its own condition and price

Joins

Use Community_Assist

Select * from Employee
--basic inner join syntax
Select PersonLastName, PersonFirstName,
EmployeeHireDate, EmployeeAnnualSalary
From Person
Inner Join Employee
On Person.PersonKey=Employee.PersonKey

--alternate, older syntax
Select PersonLastName, PersonFirstName,
EmployeeHireDate, EmployeeAnnualSalary
From Person, Employee
Where person.PersonKey=Employee.PersonKey

--cross joins, cartesian join
--old syntax, easy to do by accident
--a cross join matches every record from the first
--table with every record in the second table
Select PersonLastName, PersonFirstName,
EmployeeHireDate, EmployeeAnnualSalary
From Person, Employee
Order by PersonLastName

--new syntax (explicit cross join)
Select PersonLastName, PersonFirstName,
EmployeeHireDate, EmployeeAnnualSalary
From Person 
Cross join Employee
Order by PersonLastName

--multi table joins
Select * from GrantRequest

Select GrantRequestDate, PersonLastName, 
GrantTypeName, GrantRequestAmount
From GrantRequest
inner Join Person
On Person.PersonKey=GrantRequest.Personkey
Inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey

--with table alias
Select GrantRequestDate, p.PersonKey, PersonLastName, 
GrantTypeName, GrantRequestAmount
From GrantRequest gr
inner Join Person p
On p.PersonKey=gr.Personkey
Inner join GrantType gt
on gt.GrantTypeKey=gr.GrantTypeKey

--with functions and aggregate functions
Select Year(GrantRequestDate) [Year],
GrantTypeName, Sum(GrantRequestAmount) Total
From GrantRequest gr
inner Join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Group by Year(GrantRequestDate), GrantTypeName
Order by [Year]

--outer joins
--left outer join returns everything from 
--the first table listed and only matching records
--from the second table
Select Distinct gt.GrantTypeName, gr.GrantTypeKey
From GrantType gt
left outer join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
Where gr.GrantTypeKey is null

--right outer join
--same but reversed
Select Distinct gt.GrantTypeName, gr.GrantTypeKey
From GrantRequest gr
right outer join GrantType gt
on gt.GrantTypeKey=gr.GrantTypeKey
Where gr.GrantTypeKey is null

--full join returns everything from both tables
Select Distinct gt.GrantTypeName, gr.GrantTypeKey
From GrantType gt
full join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
Where gr.GrantTypeKey is null

---

Here is the code for updating the MetroAlt ridership table. Download the riders.txt from Canvas files. Right click on MetroAlt, Select TASKS/IMPORT FLAT FILES. Find Riders.Text, just click through until loaded. Then run this insert

Insert into Ridership([BusScheduleAssigmentKey],
[Riders])
Select BusScheduleAssignmentKey, Riders from [Riders]

Then run this Update Code

Use MetroAlt
Go
Alter Table ridership
Add FareKey int
Go
update ridership
set Farekey=1
Where [BusScheduleAssigmentKey] between 1 and 104348
Go
Update Ridership
set FareKey = 2
Where [BusScheduleAssigmentKey] between  104349 and 235028
Go
Update Ridership
set FareKey = 3
Where [BusScheduleAssigmentKey] between 235029 and 365708
Go
Update Ridership
set FareKey = 4
Where FareKey is Null

Selects Functions

Use Community_Assist
--math
Select 5 * 2 /3 % 2

Select DonationAmount,
DonationAmount * .80 as Charity,
DonationAmount * .20 as Maintenance
from Donation

Select DonationAmount,
DonationAmount * .80  Charity,
DonationAmount * .20  Maintenance
from Donation;
--scaler
--Date Functions
Select * from Donation;
Select Distinct Year(DonationDate) [Year] from Donation;
Select Distinct Month(DonationDate) [Month] from Donation;
Select Distinct Day(DonationDate) [Day] from Donation;
Select DonationDate, DatePart(Minute,DonationDate) as [Minute] from Donation;

Select * from Donation
Where Year(DonationDate)=2015
And Month(donationDate) between 8 and 9;

Select GetDate()
Select * from Employee
Select DateDiff(Month,'2/21/2005','4/21/2013')[Difference]
Select DateAdd(day, 13,GetDate())

--formats
Select * from Donation
Select DonationDate, Format(DonationAmount,'$#,##0.00') [DonationAmount] from Donation
Select * from Contact
Select '(' + substring(ContactNumber,1,3) + ')' + 
substring(ContactNumber,4,3) 
+ '-' + substring(ContactNumber, 7,4) [Phone] from Contact

Select ContactNumber, 
Format(Cast(ContactNumber as bigint),
'(000)000-0000') from Contact
--Aggregate --operate across multiple rows
Select sum(DonationAmount) Total from Donation
Select Avg(DonationAmount) Total from Donation
Select count(DonationAmount) Total from Donation
Select Max(DonationAmount) Total from Donation
Select Min(DonationAmount) Total from Donation

Select Year(DonationDate) [Year],
format(Sum(DonationAmount),'$#,##0.00') Total
From Donation
Group by Year(DonationDate)

Select Year(DonationDate) [Year], Month(DonationDate) [Month],
format(Sum(DonationAmount),'$#,##0.00') Total
From Donation
Group by Year(DonationDate), Month(DonationDate)
Order by Year(DonationDate),Month(DonationDate)

Select Year(DonationDate) [Year],
Case Month(DonationDate)
   when 8  then 'August'
   when 9  then 'September'
   when 2 then 'February'
   when 4 then 'April'
end  as [Month],
DonationAmount
From Donation

Select Year(donationDate) [Year],
datename(Month,donationDate) [Month],
DonationAmount
From Donation

Select name from Sys.Databases
Select name from sys.Tables

I forgot to cover this in class. If you have an aggregate function in the criteria, you have to use "having" instead of "where"

--You must use HAVING with any criteria that uses an
--aggregate function
--these examples have both a having and a where clause
Select EmployeeKey, AVG(DonationAmount) Average
From Donation
Where EmployeeKey > 2
Group by EmployeeKey
Having AVG(DonationAmount) < 1000

Identifying the problem
Interview with main stakeholder
What kinds of things use it for: 
Help desk: Inventory of computers (where, who, what), printers, and related hardware, supplies
Users, faculty and staff,
Open Tickets--Tickets--What computer, what the problem is, who is assigned (Techs) resolution, date
Process for assigning and solving problems
Interview other stakeholders: faculty and staff, techs, 
Job shadowing
Review Documents--forms, Reports, manuals and procedures
Pick out all the nouns
Computer, Printer, Inventory, Ticket, Software, Techs, Users(faculty, staff) 
Computer--make, manufacturer, model, CSC@,Location
Tech--name, specialty, phone, email, group
Ticket--Date, user, problem, status, solutionDate, computer, Tech Assigned

Sample stuff

Model class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebApplication2.Models
{
    public class ModelTest
    {
        public int ID { get; set; }

        public string ItemName { get; set; }

        public double Price { get; set; }

        

    }
}

Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication2.Models;

namespace WebApplication2.Controllers
{
    public class TestController : Controller
    {
        // GET: Test
        public ActionResult Index()
        {
            ModelTest mt = new ModelTest();
            mt.ID = 1;
            mt.ItemName = "item1";
            mt.Price = 25.00;
            ModelTest mt1 = new ModelTest();
            mt1.ID = 2;
            mt1.ItemName = "item2";
            mt1.Price = 5.00;
            ModelTest mt2 = new ModelTest();
            mt2.ID = 3;
            mt2.ItemName = "item3";
            mt2.Price = 13.00;
            List<ModelTest> items = new List<ModelTest>();
            items.Add(mt);
            items.Add(mt1);
            items.Add(mt2);

            return View(items);
        }
    }
}

the web page

@model  IEnumerable<WebApplication2.Models.ModelTest>
@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>
<table class="table">
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Price</th>
    </tr>
    @foreach (var item in Model)
    {
    <tr>
        <td>@Html.DisplayFor(model => item.ID)</td>
        <td>@Html.DisplayFor(model => item.ItemName)</td>
        <td>@Html.DisplayFor(model => item.Price)</td>
    </tr>
    }
</table>

The general requirements: faculty and staff Topics :what things in general Does the database need to keep track of. Floor departments All the machines Who is authorized to use the machines List all the all the faculty and staff list tickets and results changes in hardward and software Description of problems and solutions List of Techs and their specialties Doesn't track human resources Gathering Information machine descriptions Type of machine Talk to the Stakeholders Review documents Forms--fields--Reports Look at all the nouns computers, software, techs, staff, faculty, office, Department, printers, ticket, elmos, ports Computers, facultyStaff, techs, software, Department, printers, type name company manufacturer id name OS email type department license

First Selects

Use Community_Assist;

SELECT 
  donationDate,
  donationAmount 
From 
 Donation;

Select * From Person
order by PersonLastName Desc

Select * From Person
order by PersonLastName, PersonFirstName

Select * From Person
order by PersonLastName desc, PersonFirstName 

Select * From Person
Where PersonLastName='Tanner'

Select * from PersonAddress

Select * from PersonAddress 
Where Not PersonAddressCity='Seattle'--!=, <>

Select * from PersonAddress
Where PersonAddressApt is null

Select * from PersonAddress
Where PersonAddressApt is not null

Select * from Donation
Where DonationDate > '1/1/2016'

Select * from Donation
Where DonationDate between '2/14/2016'
And '2/27/2016'

Select * from Donation 
Where DonationAmount >500 -- <, <=, >=

Select * from Person
Where PersonLastName Like 'F%'

Select * from Person
Where PersonLastName Like 'n%n'

Select * from Person
Where PersonLastName Like 'F_r%'

Select Top 5 DonationDate, Donationamount
From Donation
Order by Donationamount desc

Select DonationDate, Donationamount
From Donation
Order by Donationamount desc 
Offset 5 rows fetch next 5 rows only

Select Distinct personKey from Donation
order by PersonKey