CREATE TABLE EmployeeS(
EmployeeNO INTEGER NOT NULL,
NAME CHAR(15) NOT NULL,
INITIALS CHAR(3) NOT NULL,
BIRTH_DATE DATE ,
SEX CHAR(1) NOT NULL,
JOINED SMALLINT NOT NULL,
STREET VARCHAR(30) NOT NULL,
HOUSENO CHAR(4) ,
POSTCODE CHAR(6) ,
TOWN VARCHAR(30) NOT NULL,
PHONENO CHAR(13) ,
LEAGUENO CHAR(4) ,
PRIMARY KEY (EmployeeNO) );
CREATE TABLE PENALTIES
(PAYMENTNO INTEGER NOT NULL,
EmployeeNO INTEGER NOT NULL,
PAYMENT_DATE DATE NOT NULL,
AMOUNT DECIMAL(7,2) NOT NULL,
PRIMARY KEY (PAYMENTNO) );
DELIMITER $$
CREATE PROCEDURE LARGEST
(OUT T CHAR(10))
BEGIN
IF (SELECT COUNT(*) FROM EmployeeS) >
(SELECT COUNT(*) FROM PENALTIES) THEN
SET T = 'EmployeeS';
ELSEIF (SELECT COUNT(*) FROM EmployeeS) =
(SELECT COUNT(*) FROM PENALTIES) THEN
SET T = 'EQUAL';
ELSE
SET T = 'PENALTIES';
END IF;
END$$
DELIMITER ;
drop table EmployeeS;
drop table penalties;
drop procedure LARGEST;
|