SQL> select * from Consultant;

C_ID C_LAST C_FIRST C ---------- -------------------- -------------------- - C_ADD C_CITY C_ C_ZIP ------------------------------ -------------------- -- ---------- C_PHONE C_EMAIL --------------- ------------------------------ 100 Myers Mark F 1383 Alexander Ave. Eau Claire WI 54703 7155559652 mmyers@swexpert.com

101 Hernandez Sheila R 3227 Brian Street Eau Claire WI 54702 7155550282 shernandez@earthware.com

C_ID C_LAST C_FIRST C ---------- -------------------- -------------------- - C_ADD C_CITY C_ C_ZIP ------------------------------ -------------------- -- ---------- C_PHONE C_EMAIL --------------- ------------------------------

102 Zhang Brian 2227 Calumet Place Altoona WI 54720 7155558383 zhang@swexpert.com

103 Carlson Sarah J 1334 Water Street Eau Claire WI 54703

C_ID C_LAST C_FIRST C ---------- -------------------- -------------------- - C_ADD C_CITY C_ C_ZIP ------------------------------ -------------------- -- ---------- C_PHONE C_EMAIL --------------- ------------------------------ 7155558008 carlsons@swexpert.com

104 Courtlandt Paul R 1911 Pine Drive Eau Claire WI 54701 7155555225 courtlpr@yamail.com

105 Park Janet S

C_ID C_LAST C_FIRST C ---------- -------------------- -------------------- - C_ADD C_CITY C_ C_ZIP ------------------------------ -------------------- -- ---------- C_PHONE C_EMAIL --------------- ------------------------------ 2333 157th Street Chippewa Falls WI 54712 7155554944 jpark@swexpert.com

6 rows selected.

SQL> select * from skill;

SKILL_ID SKILL_DESCRIPTION ---------- -------------------------------------------------- 1 Visual Basic Programming 2 COBOL Programming 3 Java Programming 4 Project Management 5 Web Application Programming 6 Oracle Developer Programming 7 Oracle Database Administration 8 Windows NT Network Administration 9 Windows 2000 Network Administration

9 rows selected.

SQL> select * from Project;

P_ID PROJECT_NAME CLIENT_ID MGR_ID PARENT_P_ID ---------- ------------------------------ ---------- ---------- ----------- 1 Hardware Support Intranet 2 105 2 Hardware Support Interface 2 103 1 3 Hardware Support Database 2 102 1 4 Teller Support System 4 105 5 Internet Advertising 6 105 6 Network Design 6 104 5 7 Exploration Database 5 102

7 rows selected.

SQL>

Use Software Experts Database You may execute your solution in SQL Plus Please do 1) Write pl/sql code that will list ONE Projects's name and the total hours and total number of days not open previous projects and solutions spent by each consultant on that project and the total hours for projects that the consultant worked on. User will enter the P ID. Use one Cursor and any other variable you may need to solve the problem. Points 20 Sample outpput P ID I Hardware Support Intranet total hours Total days C ID 101 104 103 105 175 103 245 344 50 151 45 344 Total project hours 515 2) Write PL/SQL code to list and count the consultants who are certified in a skill (Skill id) that the user enters. Use one cursor and any other variable you may need to solve the problem. Points 15 For example: Consultants Certified in Skill : Java Programming C_?D Certification 100 103 Count: 2 certified consultants ) Write PL/SQL code that will allow a user to assign a consultant to a project. our program will check if the consultant is already working on three or more projects that case too many projects exception will be raised and an appropriate message will be printed herwise the consultant will be assigned to the project and a row will be inserted in the oject consultant table. User will provide the p. id, and C_id. ints 15