Using the following tables Student (S), Course (C), Takes (T), and GradeValue (G

Question

Using the following tables Student (S), Course (C), Takes (T), and GradeValue (GV).

3. For each course, write a query in SQL to report the DeptName and DeptNumber for that course as well as the average grade letter received (which would be the most occurring letter grade by students in a course) ignoring W grades and the average grade received ignoring W and F grades. Note that you need to make sure that there is at least one student who got a qualifying grade to report these results.

1. Given the following table schemas as used in class (with primary keys shown with over them in SQL Student (S) SID LastName FirstName Course (C) CID DeptName DeptNumber Numcredits Co Takes T) SID CID Semester Grade Grade Value (GV) Grade Value 4.0 A- 3.7 B+ 3.3 3.0 2.7 C+ 2.3 C 2.0 C- 1.7 D+ 1.3 1.0 0.0 0.0

Explanation / Answer

create table STUDENT (SID INTEGER primary key, LastName varchar2(13),FirstName varchar2(13));
insert into student VALUES(10,'suraj', 'sontakke');
insert into student VALUES(11,'sagar', 'sontakke');
insert into student VALUES(12,'rahul', 'sontakke')
desc student;
desc course;
DROP TABLE TAKES;
create table Course(CID INTEGER PRIMARY KEY,DEPTNAME VARCHAR2(22),DEPTNUMBER NUMBER, NUMCREDITS INTEGER, COURSENAME VARCHAR(25));
insert into course values (111,'computer',10,4,'java');
insert into course values (222,'computer',10,2,'java');
insert into course values (333,'electric',11,1,'vlsi');

CREATE TABLE Takes( SID INTEGER ,CID INTEGER, SEMESTER VARCHAR2(20),ID INTEGER);
INSERT into takes values(10,111,'sem1',6);
INSERT into takes values(11,222,'sem1',1);
INSERT into takes values(10,333,'sem1',2);
SELECT FORM TAKES;
drop table gradevalue;
CREATE TABLE GRADEVALUE(ID INTEGER PRIMARY KEY, GRADE VARCHAR2(3),g_VALUE real );
insert into GRADEVALUE values (1,'A', 4.0);
insert into GRADEVALUE values (2,'A-', 3.7);
insert into GRADEVALUE VALUES (3,'B+', 3.3);
insert into GRADEVALUE values (4,'B', 3.0);
insert into GRADEVALUE values (5,'B-', 2.7);
insert into GRADEVALUE values (6,'C+', 2.3);
insert into GRADEVALUE values ( 7,'C', 2.0);
insert into GRADEVALUE values ( 8,'C-', 1.7);
insert into GRADEVALUE values ( 9,'D+', 1.3);
insert into GRADEVALUE values ( 10,'D', 1.0);
insert into GRADEVALUE values ( 11,'F', 0.0);
insert into GRADEVALUE values ( 12,'W', 0.0);

SELECT LASTNAME*FORM STUDENT;
INNER JOIN TAKES ON STUDENT.SID=TAKES.SID;
INNER JOIN COURSE C ON T.CID=C.CID
INNER JOIN GRADEVALUE GV ON T.GRADE_ID=GV.ID;
select *from course;
select *from takes;
select *from student;
select *from GRADEVALUE;

select c.DEPTNAME, c.deptnumber, c.coursename,g.g_value from course c,takes t,gradevalue g
where c.cid=t.cid and t.id=g.id and g.g_value = (select avg(g_value) from gradevalue group by g_value) ;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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