------------------------------------------------------------------------
-- John David Ayers ----------------------------------------------------
-- October 7, 2009 --------------------------------------------------
-- Homework 4 ----------------------------------------------------------
------------------------------------------------------------------------

/*
ALL columns are to display user friendly column labels.
*/



--1
/*
Display all available information about customers
who live in zip code 98006.
*/

SELECT *
FROM Customers
WHERE CustZipCode = '98006'
ORDER BY CustFirstName;

--2
/*
Display the names and addresses of all customers
who do not live in Seattle.
*/

SELECT CustFirstName, CustLastName, CustStreetAddress, CustCity, CustState, CustZipCode
FROM Customers
WHERE CustCity != 'Seattle'
ORDER BY CustFirstName;

--3
/*
List the engagement number, start date and contract price for all
engagements starting on or after January 1, 2008. Format the start
date so that it does not display time information. Sort the records
by start date with the oldest first.
*/

SELECT EngagementNumber, CONVERT(CHAR(12),StartDate,111) AS [StartDate], ContractPrice
FROM Engagements
WHERE StartDate >= '01/01/2008'
ORDER BY StartDate;

--4
/*
List the engagement number, start date and contract price for all
engagements starting during the month of January, 2008. (Use BETWEEN...AND)
Sort the records by start date, and display the date as month/day/year.
*/

SELECT EngagementNumber, CONVERT(CHAR(12),StartDate,107) AS [StartDate], ContractPrice
FROM Engagements
WHERE StartDate BETWEEN '01/01/2008' AND '01/31/2008'
ORDER BY StartDate;

--5
/*
Use the Entertainer_Styles table to list the Entertainer ID for all
entertainers with a style ID of 1, 2 or 3. (Use IN)
*/

SELECT EntertainerID
FROM Entertainer_Styles
WHERE StyleID IN(1,2,3);

--6
/*
Use the Entertainers table to list all stage names that contain a space
somewhere in the name. Use LTRIM and  RTRIM in the WHERE statement
to eliminate unwanted spaces at the beginning or end of the stage name.
*/

SELECT EntStageName
FROM Entertainers
WHERE LTRIM(RTRIM(EntStageName)) LIKE '% %';

--7
/*
Use the Entertainers table to list all stage names that do not contain a
space anywhere in the name.
*/

SELECT EntStageName
FROM Entertainers
WHERE LTRIM(RTRIM(EntStageName)) NOT LIKE '% %';

--8
/*
List the stage name, phone number, city and state of all entertainers
who do not have a web page. Display the list alphabetically by stage name.
*/

SELECT EntStageName, EntPhoneNumber, EntCity, EntState, EntWebPage
FROM Entertainers
WHERE EntWebPage IS NULL
ORDER BY EntStageName;

--9
/*
I'd really like to meet the male member of that band.
List the first name, last name and phone number of all Members whose
last name is Patterson and who are male.
*/

SELECT MbrFirstName, MbrLastName, MbrPhoneNumber, Gender
FROM Members
WHERE Gender='M' AND MbrLastName='Patterson';

--10
/*
Use the Entertainer_Styles table to list the Entertainer ID for all
entertainers with a style ID of 1, 2 or 3. (Use OR)
*/

SELECT EntertainerID, StyleID
FROM Entertainer_Styles
WHERE StyleID IN (1,2,3);

--11
/*
List the stage name of all entertainers who live in Seattle and who have
a zip code of either 98105 or 98125.
*/

SELECT EntStageName, EntCity, EntZipCode
FROM Entertainers
WHERE EntCity='Seattle' AND EntZipCode='98105' OR  EntZipCode='98125';

--12
/*
Use the Entertainer_Styles table to list the Entertainer ID for all
entertainers without a style ID of 1, 2 or 3. List each Entertainer ID
one time.
*/

SELECT DISTINCT EntertainerID
FROM Entertainer_Styles
WHERE StyleID>=3;

--13
/*

List the entertainer id and start date of all engagements where the
start date equals the end date and the contract price was greater than
300 dollars. Format the start date so that it does not display time.
*/

SELECT EntertainerID, CONVERT(CHAR(12),StartDate,101)
FROM Engagements
WHERE StartDate=EndDate AND ContractPrice>300;

--14
/*
List the stage name, phone number and e-mail address for all entertainers
who have an e-mail address.
*/

SELECT EntStageName, EntPhoneNumber, EntEMailAddress
FROM Entertainers
WHERE EntEMailAddress IS NOT NULL;

--EXTRA CREDIT--5 POINTS

--15
/*
List the stage name and e-mail address for all entetainers; if the
entertainer does not have an e-mail address, list the phone number
in the column instead. Label the first column Stage Name and the
second column Contact Information.
*/

SELECT EntStageName AS [Stage Name], ISNULL(EntEMailAddress, EntPhoneNumber) [Contact Information]
FROM Entertainers;
