[Q.3] Answer the following questions based on the following simple university da

Question

[Q.3] Answer the following questions based on the following simple university database COURSE Course_name Intro to Computer Science Data Structures CS1310 CS3320 MATH2410 CS3380 3 MATH Database SECTION Section_identifier Course_number Semester Year King Fall Fall 85 92 102 112 119 135 MATH2410 CS1310 C$3320 MATH2410 C$1310 Fall Fall Fall 05 Chang Stone GRADE REPORT 17 17 8 8 8 8 112 119 92 102 135 PREREQUISITE CS3380 CS3380 CS332o CS3320 MATH2410 CS131o STUDENT | Name | StudentNumber | Class l M (a) Write SQL statement to create and populate SECTION table with referential constraint. You need to show how to insert one tuple into the table (b) Write a SQL statement to retrieve the names of all senior students majoring in CS. (c) Write a SQL statement to retrieve the names of all courses taught by Professor Andersorn in 2004 and 2005. (d) Retrieve the name and major departments of all students who do not have any grade A in any of their courses

Explanation / Answer

a)

create table section(
section_identifier int(11),
course_number varchar(20),
semester varchar(20),
year int,
instructor varchar(120),
PRIMARY KEY (section_identifier),
FOREIGN KEY(course_number) REFERENCES COURSE(course_number)
);
INSERT INTO SECTION values(90,'MATH2410','Fall',04,'King');

b)

select name from student where major='CS'

c)

select course_name from course where course_number in(select course_number from section c where c.instructor='Anderson' and c.year in(04,05));

d)

select name,major from student where studentNumber in(select student_number from grade_report where grade<>'A' );

use this != or <> according to sql version

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.