SQL

use Automart;

Select Firstname, Lastname from Person;

Select * From Person;

--Distinct applies to whole row
Select Distinct locationID from Employee.VehicleService;

--calculations and scalar functions (operates on each row)
Select serviceName, servicePrice, 
cast((ServicePrice * 1.15)as Decimal(7,2)) as [New Price]
From Customer.AutoService;

Select * from Person
Order by Lastname Desc, Firstname;

Select * from Person 
Where LastName= 'Smith';

--=, >, <, >=, <= ,!=, <>,
Select * From Customer.AutoService
Where ServicePrice > 100;

Select * from Employee.VehicleService
Where ServiceDate >='4/1/2010'

Select * From Employee.VehicleServiceDetail
where ServiceDate Between '3/1/2010' and '3/31/2010'
--equivalent of ServiceDate >= '3/1/2010' and ServiceDate <= '3/31/2010'
 
 --and, or, not

Use CommunityAssist 

Select * From PersonAddress
Where Apartment is Not null

--scalar function
use Automart
Select GETDATE()
Select Distinct MONTH(ServiceDate) From Employee.VehicleService

--aggregate functions

Select COUNT(Distinct VehicleID) From Employee.VehicleService

Select AVG(ServicePRice) From Customer.AutoService

Select MAX(ServicePrice) From Customer.Autoservice

Select MIN (ServicePrice) From customer.AutoService

Select AVG(ServicePrice) From Customer.AutoService
where ServicePrice < 
(Select MAX(ServicePrice) from Customer.Autoservice)
and ServicePrice > 
(Select MIN(ServicePRice) from Customer.Autoservice)

Select Servicename, ServicePrice From Customer.Autoservice
Where ServicePrice = (Select MIN(ServicePrice) from Customer.AutoService)
--
Select MONTH(ServiceDate) as [Month], 
COUNT(VehicleID) as [Vehicles]
From Employee.VehicleService
Group by MONTH(ServiceDate) --any field that isn't a part of the aggregate
Having COUNT(VehicleID) >10


Select LastName, Firstname,Email, licenseNumber, VehicleMake
From Person
inner join Customer.RegisteredCustomer
on Person.Personkey=Customer.RegisteredCustomer.PersonKey
inner join Customer.vehicle
on Person.Personkey=Customer.vehicle.PersonKey

Select LastName, rc.PersonKey
From Person as p
left outer join Customer.RegisteredCustomer as rc
on p.Personkey=rc.PersonKey
Where rc.PersonKey is null