------------------------------------------------------------------------
-- John David Ayers ----------------------------------------------------
-- September 17, 2009 --------------------------------------------------
-- Homework 2 ----------------------------------------------------------
------------------------------------------------------------------------

/*
All queries will retrieve data from the EntertainmentAgency database.
*/

--1
/*
Display the following information from the Agents table in this order:
The agents last name, first name, date hired and salary. The records should
be in alphabetical order by last name and first name.
*/

SELECT AgtLastName, AgtFirstName, DateHired, Salary
FROM Agents
ORDER BY AgtLastName, AgtFirstName;

--2
/*
Display the following information from the Members table in this order:
The members last name, first name, and gender. The records should
be ordered by gender. Within gender the records should be in order by
last name and first name.
*/

SELECT MbrLastName, MbrFirstName, Gender
FROM Members
ORDER BY Gender, MbrLastName, MbrFirstName;

--3
/*
Print a listing of style names from the Musical_styles table. The list should
be in alphabetical order by the style name.
*/

SELECT StyleName
FROM Musical_Styles
ORDER BY StyleName;
--4
/*
Where do the agents live? Produce a listing from the Agents table of the
city and state. If more than one agent lives in the same city/state, it
should be listed only one time.
*/

SELECT DISTINCT AgtCity, AgtState
FROM Agents;

--5
/*
What are the two highest salaries paid to agents? List the agents last name
and the salary.
*/

SELECT TOP 2 AgtLastName, Salary
FROM Agents
ORDER BY Salary DESC;

--6
/*
List the start date and end date of all engagements in descending order
based on the start date.
*/

SELECT StartDate, EndDate
FROM Engagements
ORDER BY StartDate DESC;

--7
/*
Display the following information from the Customers table in this order:
the customers last name, first name, steet address, city, state and zip code.
Save the query as a view named CustomerList.
*/

CREATE VIEW CustomerList
AS
SELECT CustLastName ,CustFirstName, CustStreetAddress, CustCity, CustState, CustZipCode
FROM Customers
GO

--8
/*
Display a list of the stage name of all the Entertainers and the date they
joined the agency (the date the data was entered in the database). Order the
list by the date joined, with the oldest group first.
*/

SELECT EntStageName, DateEntered
FROM Entertainers
ORDER BY DateEntered;

--9
/*
Display the stage name and date joined of the first entertainer signed by
the agency (based on when the data was entered in the table).
*/

SELECT TOP 1 EntStageName, DateEntered
FROM Entertainers
ORDER BY DateEntered;

--10
/*
Display all the information in the Members table.
*/

SELECT *
FROM Members;
