--q1
drop table course;
drop table student;
drop table grade;

CREATE TABLE course
(num	VARCHAR2 (20) primary key,
name	VARCHAR2 (50),
credit  VARCHAR2 (20));

CREATE TABLE student
(num 	number (20)primary key,
fname	VARCHAR2 (20),
lname	VARCHAR2 (20),
gender	VARCHAR2 (1),
major	VARCHAR2 (20));

CREATE TABLE GRADE
(
  STUDENT NUMBER(20, 0) NOT NULL,
  COURSE VARCHAR2(20) NOT NULL,
  GRADE VARCHAR2(2)
, CONSTRAINT GRADE_PK PRIMARY KEY
  (
    STUDENT,
    COURSE
  ));
--q2
insert into student values ('111222333', 'Doe', 'Jane', 'F', 'IST');
insert into student values ('123123123', 'Smith', 'William', 'M', 'Computer Science');
insert into student values ('333222111', 'Nehru', 'WDhaval', 'M', 'English');
insert into student values ('321321321', 'Jones', 'Nathaniel', 'M', 'IST');
insert into student values ('333333333', 'Robbins', 'Ashley', 'F', 'Computer Science');
--q3
insert into course values ('IST210', 'Organization of Data', '4');
insert into course values ('MATH140', 'Calculus with Analytic Geometry I', '4');
insert into course values ('ENGL015', 'Rhetoric and Composition', '3');
insert into course values ('IST230', 'Language, Logic, and Discrete Mathematics', '3');
insert into course values ('ECON001', 'Principles of Economics', '3');
insert into course values ('PHIL012', 'Symbolic Logic', '3');
insert into course values ('CSE121', 'Introduction to Programming Techniques', '4');

--q4
insert into grade values ('111222333','IST 210','B+');
insert into grade values ('111222333','MATH 140','A-');
insert into grade values ('111222333','ENGL 015','A');
insert into grade values ('111222333','IST 230','B');
insert into grade values ('123123123','MATH 140','B-');
insert into grade values ('123123123','ENGL 015','C+');
insert into grade values ('123123123','IST 210','B');
insert into grade values ('123123123','ECON 001','B+');
insert into grade values ('333222111','ENGL 015','A');
insert into grade values ('333222111','ECON 001','B');
insert into grade values ('333222111','PHIL 012','B+');
insert into grade values ('321321321','IST 210','B+');
insert into grade values ('321321321','MATH 140','B');
insert into grade values ('321321321','ENGL 015','A-');
insert into grade values ('333333333','CSE 121','A');
insert into grade values ('333333333','MATH 140','B+');
insert into grade values ('333333333','ENGL 015','C');

select *
From grade;

--q5
drop table grade_value;
CREATE TABLE GRADE_VALUE
(
  GRADE VARCHAR2(2) NOT NULL,
  VALUE NUMBER(3, 2)
, CONSTRAINT GRADE_VALUE_PK PRIMARY KEY
  (
    GRADE
  )
)
;

--q6
insert into grade_value values ('A','4');
insert into grade_value values ('A-','3.67');
insert into grade_value values ('B+','3.33');
insert into grade_value values ('B','3');
insert into grade_value values ('B-','2.67');
insert into grade_value values ('C+','2.33');
insert into grade_value values ('C','2');
insert into grade_value values ('D','1');
insert into grade_value values ('F','0');

select *
From grade_value;

--q7