I have the following queries for sql and need assistance with how to write the c

Question

I have the following queries for sql and need assistance with how to write the code for #3, 4, and 5 (I have all the others working just fine).

**I do apologize, but I need this done by tomorrow 4/19/2018**

here are the numbered items (code can only be sorted by descending if stated in the numbered task):      **Schema I am going off of is pasted below the numbered tasks**

--1. Insert a new instructor: Mr. Hugo Reyes with an ID of 815. His address is 2342 Oceanic Way, Bayonne, NJ, 07002. He doesn't have a phone number .

--2. Create a new section ID of 48 for Mr. Reyes. He'll be teaching section 4 of the Project Management Course (142) beginning on September 22, 2011 at 8:15am in Room L211. The capacity is 15 students.

--3. Enroll students 375, 137, 266 and 382 in the course. Their enrollment date should be the current date. (Use ONE INSERT statement)

--4. Remove the enrollment for student 147 from section 120.
--Note the error. You cannot delete the enrollment record because there are foreign keys in the Grade table that depend on the Enrollment table records, thus creating an integrity constraint violation.
--First remove all grades for student 147, section 120 and then remove the enrollment for student 147, section 120. (Use two DELETE statements)

--5. Use the same procedure to remove the enrollment record for student 180 in section 119.

--6. Change Mr. Reyes’ (ID 815) phone number to be 4815162342.

--7. Change the grade on the first homework (HM) assignment for each student in section 119 to 100.

--8. Increase the final exam (FI) score for each student in section 119 by 10 percent.

--9. For each section of the Project Management course, list the section ID, location and number of students enrolled. Sort by section ID

--10. Provide an alphabetic list of instructor names and addresses for instructors that teach the Project Managment course.

****Student Schema****

Show transcribed image text GRADE CONVERSION GRADE TYPE LETTER GRADE(PK) VARCHAR) NOTNULL NMBER3,2) NOT NULL NMBER3,0) NOT NULL MME 3,0) NOT NULL GRADE TYPE CODE(PHO CHAR2) CREATED DATE CREATED DATE DATE MODFIED DATE MODIFIED DATE DATE NUMBERB0) NOT NULL NUMBERS,0 NOT NULL GRADE TYPE WEIGHT SECTON IDCPKXFK) NOT NOT NULLIGR,0Rw.FK| GRADE_TYPE.CODE(PKOK) CHARI) NIMBER PER SECTION NMBER0 NOT NULL PERCENT OF FINAL GRADE NMBER30 NOT NULL VAFCHAR30) NOT NULL MODIF -DATE GRADE-TYPE-CODE(PKKFK) CHAR ENROLLMENT SECTION D(P NMBERB,0) NOT NULL COURSE NO (F NIMER)NOT NULL START DATE TME DATE INSTRUCTOR D(FI) NMBERB0 NOT NULL SECTION D(P(F) NMBER8D) NOT NULL NIMBER3,0) NOT NULL MARCHAR200) NOT NULL CREATED DATE DATE MODFIED BY MODFIED DATE DATE CREATED DATE DATE MODFED DATE DATE SECT CRSELFK COURSE NO CPO NMBED NOT ULL DESCRIPTION VARCHAR0) NOT NLL INS TRUCTOR NSTRUCTOR DCPK) NUMBERD) NOT NULL ENR STU FK CREATED BY ARCHAR200) NOT NULL CREATED DATE DATE MODFED 0ATE DATE BYVARCHAR0) NOT NULL CREATED DATE DATE MODIFIED BY MDOIFIED DATE DATE STUDENT STUDENT ID(PK NMBE8p) NOT NULL STREET ADDRESS MARCHAR230) NULL VARCHARaC5) NOT NULL REGISTRATON DATE DATE CRSATED DATE DATE MODFIDBY MDDFI?-DATE CREATED BY VARCHARO30) NOT NULL CREATED DATE DATE MODIFED-BY VARCHAR2(30) NOT NULL MODIFI?-DATE DATE

GRADE CONVERSION GRADE TYPE LETTER GRADE(PK) VARCHAR) NOTNULL NMBER3,2) NOT NULL NMBER3,0) NOT NULL MME 3,0) NOT NULL GRADE TYPE CODE(PHO CHAR2) CREATED DATE CREATED DATE DATE MODFIED DATE MODIFIED DATE DATE NUMBERB0) NOT NULL NUMBERS,0 NOT NULL GRADE TYPE WEIGHT SECTON IDCPKXFK) NOT NOT NULLIGR,0Rw.FK| GRADE_TYPE.CODE(PKOK) CHARI) NIMBER PER SECTION NMBER0 NOT NULL PERCENT OF FINAL GRADE NMBER30 NOT NULL VAFCHAR30) NOT NULL MODIF -DATE GRADE-TYPE-CODE(PKKFK) CHAR ENROLLMENT SECTION D(P NMBERB,0) NOT NULL COURSE NO (F NIMER)NOT NULL START DATE TME DATE INSTRUCTOR D(FI) NMBERB0 NOT NULL SECTION D(P(F) NMBER8D) NOT NULL NIMBER3,0) NOT NULL MARCHAR200) NOT NULL CREATED DATE DATE MODFIED BY MODFIED DATE DATE CREATED DATE DATE MODFED DATE DATE SECT CRSELFK COURSE NO CPO NMBED NOT ULL DESCRIPTION VARCHAR0) NOT NLL INS TRUCTOR NSTRUCTOR DCPK) NUMBERD) NOT NULL ENR STU FK CREATED BY ARCHAR200) NOT NULL CREATED DATE DATE MODFED 0ATE DATE BYVARCHAR0) NOT NULL CREATED DATE DATE MODIFIED BY MDOIFIED DATE DATE STUDENT STUDENT ID(PK NMBE8p) NOT NULL STREET ADDRESS MARCHAR230) NULL VARCHARaC5) NOT NULL REGISTRATON DATE DATE CRSATED DATE DATE MODFIDBY MDDFI?-DATE CREATED BY VARCHARO30) NOT NULL CREATED DATE DATE MODIFED-BY VARCHAR2(30) NOT NULL MODIFI?-DATE DATE

Explanation / Answer

3.

--> insert into Enrollment (STUDENT_ID,SECTION_ID,ENROLL_DATE,FINAL_GRADE,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE)

Select 375,1,'GETDATE',4,'USER1','11-04-2018','ADMIN','GETDATE'

UNION ALL

SELECT 137,2,'GETDATE',3.5,'USER10','11-04-2018','ADMIN1','GETDATE'

UNION ALL

SELECT 266,1,'GETDATE',4.5,'USER6','17-04-2018','ADMIN','GETDATE'

UNION ALL

SELECT 382,2,'GETDATE',3,'USER2','15-04-2018','ADMIN1','GETDATE';

--> insert into Enrollment(STUDENT_ID,SECTION_ID,ENROLL_DATE,FINAL_GRADE,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE)

VALUES (375,1,'GETDATE',4,'USER1','11-04-2018','ADMIN','GETDATE'),(137,2,'GETDATE',3.5,'USER10','11-04-2018','ADMIN1','GETDATE'),(266,1,'GETDATE',4.5,'USER6','17-04-2018','ADMIN','GETDATE'),

(382,2,'GETDATE',3,'USER2','15-04-2018','ADMIN1','GETDATE');

Above query will insert the data of 3 students in the table.

4. delete * from GRADE where STUDENT_ID=147 and SECTION_ID=120;
delete * from ENROLLMENT where STUDENT_ID=147 and SECTION_ID=120;

The above 2 queries will delete the student 147 belongs to section 120 in grade and enrollment tables.

5. delete * from GRADE where STUDENT_ID=180 and SECTION_ID=119;
delete * from ENROLLMENT where STUDENT_ID=180 and SECTION_ID=119;

The above queries will delete student no 180 in section 119 from grade and enrollment tables.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---