------------------------------------------------------------------------
-- John David Ayers ----------------------------------------------------
-- September 29, 2009 --------------------------------------------------
-- Homework 3 ----------------------------------------------------------
------------------------------------------------------------------------
--1
/*
Write as sql query that retrieves data from the Customers table in the
EntertainmentAgency database. The query should display one column containing
the statement "The phone number for John Doe (replaced by the customer's
first and last name) is 555-1111 (replaced by the customer's phone number).
The column should be labeled Phone Information.
*/

SELECT 'The Phone Number for ' + CustFirstName + ' ' + CustLastName + ' is ' + CAST(CustPhoneNumber AS CHAR(8)) AS [Phone Imformation]
FROM Customers;

--2
/*
Sum the salaries of the agents. Display the answer in a column labeled
Total Agent Salary Expense.

EXTRA CREDIT: (5 points) After the SQL statement is working,
nest it into a CONVERT function that will display the amount as
character data of length 12 that displays commas to the left of the
decimal point. Then, precede the CONVERT function with a string literal
dollar sign.
*/

SELECT '$' + CONVERT(CHAR(12),SUM(Salary),100) AS [Total Agent Salary Expense]
FROM Agents;

--3
/*
How many Customers are in the database? Display the answer in a column
labeled Number of Customers.
*/

SELECT COUNT(CustomerID) AS [Number of Customers]
FROM Customers;

--4
/*
Write one SQL statement that will trim white space from both the right and
left sides of the customer's last name. Display one column.
HINT: You will need to nest functions.
*/

SELECT LTRIM(RTRIM(CustLastName))
FROM Customers;

--5
/*
Create a user name for every customer. The user name will consist of the
customer id, followed by a hyphen, followed by the first four letters of
the customer last name in upper case. There should be no spaces in the
user name. Display the CustomerID column, the customer last name column,
and the calculated user name column (labeled User Name).
*/

SELECT CustomerID, CustLastName,
CAST(CustomerID AS CHAR(5)) + '-' + UPPER(Left(CustLastName, 4)) AS [User Name]
FROM Customers;

--6
/*
Display the stage name and the web page for all entertainers. If the
entertainer does not have a web page, display the words 'Not Applicable'
rather than the default Null. The second column should be labeled Web Page.
*/

SELECT EntStageName, ISNULL(EntWebPage, 'Not Applicable') AS [Web Page]
FROM Entertainers;

--7
/*
The office administrator likes to send a survey to the customers one week
after an engagement ends to determine if they were satisfied. From the
Engagements table, display the StartDate, EndDate and a calculated Letter
Date column. All three columns should display the date in this format:
Sep 29, 2009. The date in the Letter Date column should be one week
after the End Date.
*/

SELECT
CONVERT(CHAR(12),StartDate,107) AS [Start Date],
CONVERT(CHAR(12),EndDate,107) AS [End Date],
CONVERT(CHAR(12),DATEADD(dd,7,EndDate),107) AS [Letter Date]
FROM Engagements;

--8
/*
In a column labeled Days Til Christmas, display the number of days until
this coming Christmas. The column should display the correct number of days
from now until December 25, 2009.
*/

SELECT DATEDIFF(dd, GETDATE(), '12-25-2009') AS [Days Til Christmas];

--9
/*
Display your age by determinng the number of days between your birthday and
the current date, then dividing by 365. Construct the SQL statement so that
the age is always current, no matter what day the statement is run. Save
the statement as a view named MyAge. Next, display all information in the
view My Age.
*/

CREATE VIEW MyAge
AS
SELECT DATEDIFF(dd, '10-4-1989', GETDATE())/365 AS [My Age];

--10
/*
Display the last name, first name, and salary of the agents, as well as a
calculated column labled Salary After Raise, that displays the Salary
increased by 10% (NOT 10% of the salary!)
*/

SELECT AgtLastName ,AgtFirstName, Salary, STR(Salary*1.1,8,2)
FROM Agents;
