Add a new project to the database. The name of the project is ‘Aspen’, the project number is 8765, the project’s value is 1000000 and the manager of the project is Jefferson Blake. You may NOT use his employee number in the insert statement – you MUST use a subquery to select his employee number from the Employee table.

Update the Job table to change the Salg_Grade of the Manager from 4 to 5. You must use the name of the job title ‘Manager’ in your UPDATE statement.

Sample Database for Query Examples: SalGrade SALG GRADE SALG LOSAL SALG HISAL Job JOB TITLE SALG GRADE 1200 Clek 700 Salesp 1

Assignment P Assign_No Sal Grade Project Proj No Assign Date Assign_Hours Assign_Charge EF Emp No SalG Grade Salo LoSal Salo

Sample Database Model: Assignment P Assign_No Assign_Date Assign_Hours Sal Grade Project SalG Grade Salo_LoSal SalG_HiSal P P
Sample Database for Query Examples: Assignment ASSIGN CHARGE ASSIGN HOURS EMP NO ASSIGN NO ASSIGN DATE PROJ NO 1 05-MAY-07 5
Examples: Sample Database for Query SalGrade Job JOB TITLE SALG_GRADE SALG GRADE SALG_LOSAL SALG_HISAL Clerk 700 1200 Salesre Sample Database for Query Examples: SalGrade SALG GRADE SALG LOSAL SALG HISAL Job JOB TITLE SALG GRADE 1200 Clek 700 Salesp 1201 1400 Manager 3 1401 2000 Anglyst 2001 3000 President 3001 9999 Department DEPT NO DEPT NAME DEPT LOC 10 Accountng Now York Dalas 20 Research 30 Sales 40 Operations Chicago Boston Employee PNO e NAE eATUS eSALARY co ATE PNGRe o me pEPT NO 7829 ark King actae 5000 10 7e etteron 772 e 250 Teange 30 sake actae ina) 03– T ange Clark actaver — 10 e m -a- 7566 ar 2ns 20 ectae actave eth 2–4 56 e 20 ni) 20–N 564 Ce dn a00 20 ctaves s – – s00ek es mo ave 724 Sue Mler s300 0 artave s2– Ales atae 752 om Mard 1250 soe 3– 30 aieace 440 urse s00 e– s0 actave aleacep 654 Sandy artss s2s0 400 0-02- s0 actae 72 ford actaver s00 12–s 14 Sos actave 3000 ISQA 3310/ISQA 8050 Sample Database for Query Examples: Assignment ASSICN OAGE PROJ NO ASSIGN BATE ASSIGN HOURS EP NO ASSIGN NO 105-MAY-0 305 7790 1234 2 06-MAY 07 125 1234 1234 3 06-MAY-0 778 1234 4 07MAY-07 4 125 25 s 07-MAY-07 756 1234 6 06-MAY-07 7664 234 7566 2345 7 7MAY/ 125 8 12-MAY-0 25 7844 3456 7902 3456 9 12-MAY-07 225 Project PROJ VALUE EMP 😯 PROJ NAME PROJ NO 00000 566 1234 Evgre 2345 Male 50000 7566 3456 En 79 Assignment P Assign_No Sal Grade Project Proj No Assign Date Assign_Hours Assign_Charge EF Emp No SalG Grade Salo LoSal Salo HiSal Proj Name Proj Value F Emp No HI is worked via F Proj No l applies to assigned to Job Employee P Job Title -IH P Emp No -IH manages Salo Grade assigned to Emp_Fname Emp Lname Emp Status Emp_Salary Emp Comm Emp HireDate Emp Mgr Emp_No F Job_Title Dept No Department Dept No U Dept Name Dept Loc P HG- HG employs manages Sample Database Model: Assignment P Assign_No Assign_Date Assign_Hours Sal Grade Project SalG Grade Salo_LoSal SalG_HiSal P Proj No Proj Name Proj Value Assign_Charge EF Emp No HI- is worked via F Proj No Emp_No lapplies to Lasigned to Job Employee P . Job Title -IHF Salo_Grade P Emp No Emp_Fname Emp Lname Emp_Status Emp Salary Emp_Comm Emp_HireDate O4F Emp Mg_Emp_No -IH assigned to manages Department P Dept No u Dept Name Dept Loc HG HG employs manages F Job_Title Dept No HHe Sample Database for Query Examples: Assignment ASSIGN CHARGE ASSIGN HOURS EMP NO ASSIGN NO ASSIGN DATE PROJ NO 1 05-MAY-07 5 305 7788 1234 2 05-MAY-07 125 7566 1234 3 06-MAY-07 2 305 7788 1234 4 07-MAY-07 305 7788 1234 5 07-MAY-07 12.5 7566 1234 29.5 6 06-MAY-07 7654 2345 7 07-MAY-07 12.5 7566 2345 205 7844 8 12-MAY-07 3456 9 12-MAY-07 225 7902 3456 4 Project PROJ NAME PROJ VALUE PROJ NO EMP NO 1234 Evergeen 2345 Maple 1000000 1500000 7566 7698 3456 Elm 2000000 Examples: Sample Database for Query SalGrade Job JOB TITLE SALG_GRADE SALG GRADE SALG_LOSAL SALG_HISAL Clerk 700 1200 Salesrep 2 1201 1400 Manager 4 2000 3 1401 Analyst 4 4 2001 3000 President 5 3001 6666 Department DEPT NO DEPT NAME DEPT_LOC 10 Accounting New York 20 Research Dallas 30 Sales Chicago 40 Operations Boston Employee EMPNO EMP FNAMEEMP LNAME EMP STATUS EMP SALARYBMP COMM 5000 EMPHIREDATEEMP MGR EMP NOJOe TTLE DEPT NO King (all) 17-NOv-81 10 7839 Mark active (nall) Preaident 7698 Jetferson Blake 2050 (nall) 01-MAY-91 7839 Manager 30 active 7782 Jane (nall) 09-JUN-91 7839 Manager clark active 2450 10 7544 Mary Jones 2975 (all) 02-APR-92 7839 Manager 20 active 7369 John Sith (all) 20-SEP-9 7566 Clerk 20 active 7876 Joe Adans 1100 (null) 20-FEB-94 7566 Clerk 20 active 7900 Jack 950 7698 Clerk 30 Janes active (nall) 08-SEP-91 7934 Sue (nal1) 03-DEC-95 7782 Clerk 10 Miller active 1300 7499 Steve 769 Salesrep Allen 1400 300 22-FEB-92 30 active 30 7521 Tom Ward 500 03-DEC- 7698 Salearep 1250 30 active 11 1844 Joe 7690 Salearep Turner active 1500 017-DEC-90 30 1400 09-CEC- 12 7654 Sandy Martin 1250 7698 Salearep 30 active 13 (all) 12-JA-95 7566 Analyat 7902 Robert Ford active 3000 20 14 (all) 23-JAN-92 7788 Hichael Scott active 3000 7566 Analyst 20



Source link

Leave a Reply

Your email address will not be published. Required fields are marked *