Using SQLplus, and the following table, group and count all math majors by class

Question

Using SQLplus, and the following table, group and count all math majors by class and display the count if there are two or more in a class. (Class here refers to freshman, sophomore, junior, and senior, and is recorded as 1, 2, 3, and 4.)

The database is as follows (bold is table name, italics are the PK followed by all attributes of the table)

Student - stno NUMBER(3), sname VARCHAR2(20), major CHAR(4), class NUMBER(1), bdate DATE

Grade_report - student_number NUMBER(3), section_id NUMBER(6), grade CHAR(1)

Section - section_id NUMBER(6), course_num CHAR(8), semester VARCHAR2(6), year CHAR(2), instructor NUMBER(3), room NUMBER(3)

Department_to_major - dcode CHAR(4), dname CHAR(20)

Course - course_number CHAR(8), course_name CHAR(20), credit_hours NUMBER(2), offering_dept CHAR(4)

Room - bldg NUMBER(3), room NUMBER(3), capacity NUMBER(4), ohead CHAR(1)

Prereq - course_number CHAR(8), prereq CHAR(8)

Explanation / Answer

create table student
(STNO NUMBER(3) primary key not null,
SNAME VARCHAR2(20),
MAJOR CHAR(4),
CLASS NUMBER(1),
BDATE DATE)
/
create table grade_report
(STUDENT_NUMBER NUMBER(3) not null,
SECTION_ID NUMBER(6) not null,
GRADE CHAR(1),
primary key(student_number, section_id))
/
create table section
(SECTION_ID NUMBER(6) primary key NOT NULL,
COURSE_NUM CHAR(8),
SEMESTER VARCHAR2(6),
YEAR CHAR(2),
INSTRUCTOR CHAR(10),
BLDG NUMBER(3),
ROOM NUMBER(3))
/
create table department_to_major
(Dcode CHAR(4) primary key not null,
DNAME CHAR(20))
/
create table course
(COURSE_NAME CHAR(20),
COURSE_NUMBER CHAR(8) primary key NOT NULL,
CREDIT_HOURS NUMBER(2),
OFFERING_DEPT CHAR(4))
/
create table room
(BLDG NUMBER(3) not null,
ROOM NUMBER(3) not null,
CAPACITY NUMBER(4),
OHEAD CHAR(1),
primary key (bldg, room))

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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