Answer to Question #158949 in Databases | SQL | Oracle | MS Access for Umair

Question

TOPIC: SQL Stored procedures:

S_ID

101

201

301

401

501

601

Name

Ella

Christian

Sofia

Lillian

John

Joseph

Gender

F

M

F

F

M

M

City

California

Texas

Arizona

Maryland

California

Hawaii

Attendance %

69

88

68

82

60

72

Quiz  1 (10)

5

6

5

6

7

8

Quiz  2 (10)

8

7

2

3

5

6

Quiz  3 (10)

6

7

5

4

8

Assign 1(10)

5

5

8

5

9

5

Assign 2 (15)

12

11

10

9

8

14

Assign 3 (10)

7

6

3

7

6

4

Final (45)

34

43

26

34

41

22

 

T_ID CourseName S_ID

1101 Digital marketing 101

1102 AI and data science. 301

1103 Web development 401

1104 Cyber Security. 201

 

CASE: The above tables are extracted an examination department of an institution. The examination department wants to have a consolidated total of students’ scores in each subject, where, the weightage of quizzes, assignments and attendance are 25%, 35% and 5% respectively. The remaining weightage belongs to the final term result. Also, they requested the IT department for a procedure which generates Letter Grades and respective Grade Points (as mentioned in the table below). As the institution has many campuses over the country, they also need to have a City-wise students’ progress for campus-wise comparison.

Marks Grade Points

88-100 A. 4

81-87. B+ 3.5

74-80. B. 3

67-73. C+ 2.5

60-66. C 2

Below 60 F. 0

Answer 1

CREATE TABLE `University`.`Student`
( `S_ID` INT NULL , `Name` VARCHAR NOT NULL , 
`Gender` VARCHAR NOT NULL , `City` VARCHAR NOT NULL ,
 `Attendance %` INT NOT NULL , PRIMARY KEY (`S_ID`))
 ENGINE = InnoDB;


INSERT INTO `student` (`S_ID`, `Name`, `Gender`, `City`, `Attendance %`) 
VALUES ('101', 'Ella', 'F', 'California ', '69'), 
('201', 'Christian', 'M', 'Texas', '88'),
('301', 'Sofia','F', 'Arizona ', '68'),
('401', 'Lillian', 'F', 'Maryland', '82'),
('501', 'John', 'M', 'California ', '60'),
('601', 'Joseph', 'M', 'Hawaii ', '72');

Exactly it is not mentioned what we have to do, so as per assumption,
it seems that student required the create table and insert query.