SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE module( code Char(6) NOT NULL, title Varchar(500) NOT NULL, CONSTRAINT module_pk PRIMARY KEY (code) ); CREATE TABLE lecturer( code Char(5) NOT NULL, name Varchar(500) NOT NULL, CONSTRAINT lecturer_pk PRIMARY KEY (code) ); CREATE TABLE student( code Char(6) NOT NULL, name Varchar(500) NOT NULL, CONSTRAINT student_pk PRIMARY KEY (code) ); CREATE TABLE sessionModule( session Varchar(50) NOT NULL, module_code Char(6) NOT NULL, lecturer_code Char(5) NOT NULL, CONSTRAINT session_pk PRIMARY KEY (session, module_code), CONSTRAINT module_relationship FOREIGN KEY (module_code) REFERENCES module(code) ON UPDATE CASCADE, CONSTRAINT lecturer_relationship FOREIGN KEY (lecturer_code) REFERENCES lecturer(code) ON UPDATE CASCADE ); CREATE TABLE feedback( id Integer NOT NULL, session Varchar(50) NOT NULL, module_code Char(6) NOT NULL, student_code Char(6) NOT NULL, rating Integer NOT NULL, comment Varchar(1000) NOT NULL, CONSTRAINT sessionModule_relationship FOREIGN KEY (session, module_code) REFERENCES sessionModule(session, module_code) ON UPDATE CASCADE, CONSTRAINT student_relationship FOREIGN KEY (student_code) REFERENCES student(code) ON UPDATE CASCADE, CONSTRAINT rating_options CHECK (rating IN (1,2,3,4,5)) ); INSERT INTO module VALUES('CP2015', 'Web Development'), ('CP2320', 'Database Management'), ('CP2745', 'Web Security'), ('BS3143', 'Business Computing'); INSERT INTO lecturer VALUES('L1234', 'Peter Parker'), ('L2758', 'Miles Morales'), ('L5211', 'Gwen Stacy'); INSERT INTO student VALUES('M17209', 'Norman Osborn'), ('M29292', 'Otto Octavius'), ('M64136', 'William Baker'), ('M99002', 'Wilson Fisk'); INSERT INTO sessionModule VALUES('2019-20', 'CP2015', 'L1234'), ('2019-20', 'CP2320', 'L2758'), ('2019-20', 'CP2745', 'L1234'), ('2020-21', 'BS3143', 'L5211'); INSERT INTO feedback VALUES(1, '2019-20', 'CP2015', 'M17209', 4, 'I really feel I learned a lot from this module.'), (2, '2019-20', 'CP2015', 'M29292', 2, 'The lecturer is not good at multitasking.'), (3, '2019-20', 'CP2015', 'M64136', 3, 'The module was easy, but a bit boring.'), (4, '2019-20', 'CP2015', 'M99002', 4, 'Great lecturer.'), (5, '2019-20', 'CP2320', 'M17209', 1, 'The lecturer is just terrible.'), (6, '2019-20', 'CP2320', 'M29292', 3, 'The labs were good, but the lectures were a bit dry.'), (7, '2019-20', 'CP2320', 'M64136', 4, 'Good module, but the software was a bit buggy.'), (8, '2019-20', 'CP2745', 'M17209', 5, 'My favorite module - I think i could into any system now.'), (9, '2019-20', 'CP2745', 'M29292', 2, 'The lecturer did not understand the subject.'), (10, '2019-20', 'CP2745', 'M64136', 3, 'It was OK, I guess.'), (11, '2020-21', 'BS3143', 'M29292', 3, 'Never got to grips with this module.'), (12, '2020-21', 'BS3143', 'M64136', 2, 'Not my favorite subject.'), (13, '2020-21', 'BS3143', 'M99002', 4, 'This will help me grow my business.'); CREATE PROCEDURE LecturerRating @lect_code VARCHAR(6), @outputName VARCHAR(100) OUTPUT, @outputPercent INT OUTPUT AS DECLARE @setName VARCHAR(100), @setPercent INT SELECT @setName=lecturer.name, @setPercent=CAST(FLOOR((CAST(COUNT(CASE WHEN rating>2 THEN 1 END) AS float)/CAST(COUNT(rating) AS float))*100) AS INT) FROM feedback INNER JOIN sessionModule ON sessionModule.session=feedback.session AND sessionModule.module_code=feedback.module_code INNER JOIN lecturer ON sessionmodule.lecturer_code= lecturer.code WHERE sessionModule.lecturer_code=@lect_code GROUP BY lecturer.name SET @outputPercent = @setPercent, @outputName=@setName; DECLARE @ReturnedName VARCHAR(100) DECLARE @ReturnedPercent INT EXEC LecturerRating @lect_code="L1234", @outputName = @ReturnedName OUTPUT, @outputPercent = @ReturnedPercent OUTPUT SELECT @ReturnedName AS "Output name";
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear