Question

Microsoft SQL Server Management Studio

1. Write SQL statements to insert, update, and delete a record in each of the Clients, Assist, Task, Outreach, and Specialist tables. Finish by inserting records into the tables (sample data follows). 5 points

2. Write Select statements to organize data from different tables. 4 points each

• Select all clients who have had an assist; include client name, assist date and type.
• List all assists including the name of the Specialist.
• List client names having tasks needing follow up.
• List all clients having an assist in 2003; include client name and specialist name in the result.
• List all clients having an open assist; include client name, assist date and type, and specialist name in the result.

Copy the SQL statements from #1 and #2 into a Word document and submit to Canvas.

Sample Data

Clients
First	Last	Message Phone	Home Phone	Street*	City*	State*	Zip*
Terry	Bradshaw	3603333333	3603333333	303 4th	Olympia	WA	98501
Sam	Jones	3605545555		1525 Pacific	Olympia	WA	98501
Ginger	Baker	3608787878	3608787877
Sarah	Hughes	3604443333		8702 54th	Lacey	WA
Lovey	Howell	3607777777	3607777755	4226 X St.	Tumwater	WA	98501

*residence address

Specialists
First	Last	Title
BB	King	Specialist II
Ann	Wilson	Specialist I

Assists
Client	Open	Close	Specialist	Type	Referral	Ref. Phone	Confidential
3	10/2/04	12/12/04	1	Counseling	Dr. Jones		Yes
3	10/5/04	10/5/04	1	Referral	Dr. Barnes		No
4	11/21/04	1/6/05	1	Forms assistance
4	11/10/04	12/7/04	1	Counseling
3	12/1/04	12/1/04	2	Referral
3	12/18/04		2	Dr. Eligibility
4	12/8/04	1/10/05	2	Forms Assistance	Dr. Jason	3605555555
3	12/15/04	12/18/04	2	Counseling
1	12/12/04		1	Referral	DSHS		No
1	12/29/04	1/29/05	1	Advocacy
2	12/27/04	1/15/05	1	Dr. Eligibility

Tasks
Assist	Date	Followup	Activity	Status
2	10/05/04		Phone call	Complete
3	12/15/04		Phone call	Complete
4	12/1/04	Yes	Phone call
6	12/1/04		Letter	Complete
6	12/6/04		Phone call	Complete
7	1/5/05	Yes	Letter
10	1/4/05	Yes	Letter

Answer 1

Consideration : Given tables have no proper mapping among each other so we can not use different join apart from Cartesian Join so i have used Cartesian Join only in the SQL queries .

Question 1 :

Write SQL statements to insert, update, and delete a record in each of the Clients, Assist, Task, Outreach, and Specialist tables. Finish by inserting records into the tables (sample data follows).

Query :  

(a) Insert , Update and Delete queries for Clients Table

INSERT INTO Clients (First, Last, Message_Phone, Home_Phone , 
Street , City , S )
VALUES ('Terry','Bradshaw',3603333333 ,3603333333,'303 4th','Olympia','W' ); 

UPDATE Clients
SET First = 'Alfred', 
WHERE Message_Phone=3603333333 ;

DELETE FROM Clients WHERE First='Terry';

(b) Insert , Update and Delete queries for Assists table

INSERT INTO Assists (Client, Open, Close, SpecialistType ,Referral , Ref.Phone , Confidential )
VALUES (3 , '10/2/04', '12/12/04' , 1 , 'Counseling','Dr.Jones',3605555555,'Yes' );

UPDATE Assists
SET SpecialistType = 'Regerral', 
WHERE Close='12/12/04' ;

DELETE FROM Assists WHERE Close='12/12/04';

(c) Insert , Update and Delete queries for Specialist table

INSERT INTO Specialist (First, Last, Title)
VALUES ('BB','King','Specialist II'); 

UPDATE Specialist
SET First = 'Wilson', 
WHERE First='BB';

DELETE FROM Specialist WHERE First='BB';

(c) Insert , Update and Delete queries for Task table

INSERT INTO Tasks(Assist, Date, Followup, Activity, Status)
VALUES (2, '10/05/04',NULL , 'Phone call','Complete'); 

UPDATE Tasks
SET Followup = 'No', 
WHERE Date='10/05/04';

DELETE FROM Task WHERE Date='10/05/04';

Question 2 :

(a) Select all clients who have had an assist; include client name, assist date and type.

SELECT 
          Clients.First ,
      Clients.Last ,
      Assists.Open ,
      Assists.Close 
      FROM Assists ,Clients where Assists.Client=(SELECT Client FROM Assists);

(b) List all assists including the name of the Specialist.

SELECT * ,
        Specialists.First ,
        Specialists.Last 
        FROM Assists , Specialists ;

(c) List client names having tasks needing follow up.

SELECT 
        Clients.First,
        Clients.Last,
        FROM Assists JOIN Tasks ON (Assits.Open = Tasks.Date) , Clients ;

(d) List all clients having an assist in 2003; include client name and specialist name in the result.

        SELECT 
                Clients.First , 
            Clients.Last ,
            Specialists.First ,
            Specialists.Last 
            FROM Clinents , Specialists , Assists WHERE 
            YEAR(Assists.Open) = 03 ;

(e) List all clients having an open assist; include client name, assist date and type, and specialist name in the result.

SELECT 
                Client.First ,
        Client.Last ,
        Assists.Open ,
        Assists.SpecialistType,
        Specialists.First ,
        Specialists.Last 
        FROM Clients , Specialists , Assists ;

Hope you got your answer !

if you still have any doubts please let me know in comment box . Thanks ! happy learning ;)