/*
---------------------------------------------------------------------
Programmer:		John "David" Ayers
Course:			CSCI 2030 A70
Date:			December 10, 2009
---------------------------------------------------------------------
Purpose:	Updating, Inserting and Deleting Homework
---------------------------------------------------------------------
*/

Use EntertainmentAgency

/*
Display the SELECT statements used to verify data as well as the UPDATE,
INSERT and DELETE statements.
*/


--1
/*
In the Members table, correct the data for Member 122 to reflect a first name
of Julius and a Gender of male.
*/
SELECT MemberID ,MbrFirstName, Gender
FROM Members;

UPDATE Members
SET MbrFirstName = 'Julius', Gender = 'M'
WHERE MemberID = 122;


--2
/*
For all Customers who live in Redmond, Washington, change the name of the city
to Gates.
*/

SELECT CustCity, CustState
FROM Customers
WHERE CustCity = 'Redmond';

UPDATE Customers
SET CustCity = 'Gates'
WHERE CustCity = 'Redmond';


--3
/*
Change the style name of style 12 to Elvis Impersonator.
*/

SELECT StyleID, StyleName
FROM Musical_Styles
WHERE StyleID = 12;

UPDATE Musical_Styles
SET StyleName = 'Elvis Impersonator'
WHERE StyleID = 12;

--4
/*
Add yourself to the Customers table. Use a listing and provide data for every
column. Use the next sequential Customer ID number.
*/

SELECT *
FROM Customers;

INSERT INTO Customers
VALUES (10016, 'John', 'Ayers', '126 Dumfries Rd.', 'Bristol', 'TN', '37620', '555-1234');

--5
/*
Add someone else as an Customer. Do not use a column listing and provide only
the next sequential customer number, a first name and a last name
*/

SELECT *
FROM Customers;

INSERT INTO Customers
VALUES (10017, 'John', 'Ayers', Null, Null, Null, Null, Null);

--6
/*Use SELECT to insert all agents into the customer table. Use the agentID as
the customer ID and use the appropriate column in the agent table for the
corresponding column in the Customers table
*/

SELECT *
FROM Customers;

INSERT INTO Customers
SELECT AgentID, AgtFirstName, AgtLastName, AgtStreetAddress, AgtCity, AgtState, AgtZipCode, AgtPhoneNumber
FROM Agents;

--7
/*
Write nine UPDATE statements to change customer ID 1 to customer ID 10021, 2 to 10022,
3 to 10023, etc. in the Customers table.
*/

SELECT *
FROM Customers;

UPDATE Customers
SET CustomerID = 10021
WHERE CustomerID = 1;

UPDATE Customers
SET CustomerID = 10022
WHERE CustomerID = 2;

UPDATE Customers
SET CustomerID = 10023
WHERE CustomerID = 3;

UPDATE Customers
SET CustomerID = 10024
WHERE CustomerID = 4;

UPDATE Customers
SET CustomerID = 10025
WHERE CustomerID = 5;

UPDATE Customers
SET CustomerID = 10026
WHERE CustomerID = 6;

UPDATE Customers
SET CustomerID = 10027
WHERE CustomerID = 7;

UPDATE Customers
SET CustomerID = 10028
WHERE CustomerID = 8;

UPDATE Customers
SET CustomerID = 10029
WHERE CustomerID = 9;

--8
/*
Delete all customers in the Customers table with ID's from 10021 to 10029.
*/

SELECT *
FROM Customers;

DELETE
FROM Customers
WHERE CustomerID BETWEEN 10021 AND 10029;

--9
/*
Delete all data in the Musical_Preferences table
*/

SELECT *
FROM Musical_Preferences;

DELETE
FROM Musical_Preferences;

--10
/*
Delete all customers who have never booked an engagement.
*/

SELECT *
FROM Customers;

DELETE FROM Customers
WHERE CustomerID NOT IN
(SELECT CustomerID
	FROM Engagements);
