ITC 222 Assignment 3

Aggregate Functions

Outcomes

Use SQL to retrieve database information

---

Problems

1. How many Ford vehicles are there? (Remember the LIKE operator)

2. What is the highest price for a service?

3. What is the lowest price for a service?

4. What is the average price for a service?

5. Although it doesn't make much sense, what it the total of all the service prices?

6. What is the count of cars served in each location?

7. list only the locations and counts of those locations that have served more than 15 customers

8. How many employees does each supervisor supervise?

9. What is the count of vehicles served on the first of March 2010?

10. What is the total number of vehicles served per month?

---

What I am looking for

I want to see the SQL that would answer each problem. I do not need to see the actual answers, though you can include them if you wish

Each problem is worth 1 point for a total of 10 points

---

What to turn in

Paste your answers into a Google doc and share it with spconger@gmail.com

ITC 222 Assignment 2

Scalar Functions

Outcomes

Use SQL to retrieve database information

---

The Problems

1. Select the distinct years in which employees were hired. Sort them so that the newest are on top

2. Display the names in person as (Last name, first name)

3. Return only the first word of each vehicle make

4. How many years difference is there between the newest and oldest hire date. (Just enter the dates manually. don't try to get them from the rows)

5. How many months are between the newest and oldest hires?

6. Format the phone numbers of the locations so they look like (206)555-1000

7. Format the prices of the auto services so that they have a $ sign

8.. Display the discount and tax percents in service detail as percents with a % sign. (You will have to multiply them by 100)

---

What I am looking for

I want to see the SQL for each problem

Each problem is worth 1 point except for 6 and 8 which are worth 2 points each for a total of 10 points

---

deally, copy and paste the code into a Google doc and share it with spconger@gmail.com. I will comment on the document

ITC 222 Assignment 1

Simple Select Statements

Outcomes

Use SQL to retrieve database information

---

The problems

1. List all the people in the person table.

2. List all the people in the person table in alphabetical order by last name

3. List all the people in reverse order. Alias each field.

4. List all the locations in Seattle.

5. List all the services with a price of over $100.

6. List all the last names of persons with the letters 'tan' in them.

7. List all the services provided between 3/1/2010 and 3/15/2010.

8. List all the employees who don't have supervisors

9. List all the employees who do have supervisors.

10. List all the employees who have supervisors and were hired in 2007 (between 1/1 and 12/31/2007)

---

What I am looking for

I want just the SQL statement. I don't need the results. None of the questions involve joins or other more advanced SQL.

Each question is worth 1 point for a total of 10 pts

---

What to turn in

Ideally, copy and paste the code into a Google doc and share it with spconger@gmail.com. I will comment on the document

ITC 220 Presentation

Here are the guidelines for the presentation:

1. Introduce the group members
2. Show your Entity Diagram of the database and explain briefly what tables you have developed and how the work
3. Explain what part of the project was the most challenging for the group and why
4. Explain what part of the project you are most proud of or excited about

ITC 220 Evaluation

The class evaluation will be Wednesday 12/7/2011

The URL is http://www.seattlecolleges.com/facultyevaluations

The course number is 3206

The password is "database" (no quotes)

The evaluation will be available between 10:30 AM and 11:30 AM.

View, Stored Procedures, Triggers

Here is the code for the Views, Stored Procedures and Triggers

Use CommunityAssist
Go
--this is a view. A view is used to provide
--a particular "view" of the data
--usually taylored to a particular set of users
--views are behave like tables
--they are really filtered queries. No
--data is actually stored in the view itself
Alter view vw_Employees
As
Select LastName [Last Name],
FirstName [First Name],
Street,
City,
[State],
Zip [zip code]
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
Inner join Employee e
on e.PersonKey=p.PersonKey

--using the view like a table
Select * from vw_Employees

--the aliases in a view become the 
--actual column names
Select [Last Name], [First Name], Street
From vw_Employees

go
--a store procedure is an executable
--that consist of one or more sql statements
--they can be used to make dangerous
--commands like updates safe
Create proc usp_UpdateAddress
--parameters provided by the user
--probablly through a form
@PersonAddressKey int ,
@street nvarchar(255) ,
@City nvarchar(255),
@Zip nvarchar(10) 
AS --beginning of the procedure proper
--check to see the the address exists
if exists
 (Select PersonAddressKey 
 from PersonAddress
 Where PersonAddressKey = @PersonAddressKey)
Begin --if it does
--update the address with the new values
Update PersonAddress
Set Street = @Street,
City=@City,
Zip = @Zip
Where PersonAddressKey=@PersonAddressKey
End --end if true
Else --if false
--print a message
print 'Address doesnt exist'

Select * From Personaddress

--calling the stored procedure and 
--providing the parameter values
--the keyword exec is optional
exec usp_UpdateAddress
@PersonAddressKey = 70,
@Street='10 South Mann Street',
@City='Tacoma',
@Zip='98000'

Go
--triggers are procedures that execute 
--on an event
--the following executes on inserts into
--the Donation table
Alter trigger tr_DonationAmount on Donation
for Insert 
As
--check to see if the receipt table exists
--if not create it
If not exists
(Select name from sys.tables 
 where name='receipt')
Begin
Create Table receipt
(

DonationDate DateTime, 
DonationAmount money, 
PersonKey int, 
EmployeeKey int
)
End
--declare a variable
Declare @DonationAmt money
--assign a value to the variable from the inserted
--table. the Inserted table is a temporary table
--created to store insertions for the length of
--the transaction
Select @DonationAmt = DonationAmount from inserted
--check on the size of the new donation
if (@DonationAmt >=200)
Begin --if greater than 199
--insert it into the receipt table
Insert into receipt(
DonationDate, 
DonationAmount, 
PersonKey, 
EmployeeKey)
--the values to be inserted are selected
--from the inserted table
Select 
DonationDate, 
DonationAmount, 
PersonKey, 
EmployeeKey
From Inserted
End

--using the key
Insert into Donation(DonationDate, DonationAmount, 
PersonKey, EmployeeKey)
Values(GETDATE(),250,1, 1)

Select * From Receipt
Select * From Donation