SET AUTOCOMMIT = 0;
DROP USER HU@localhost;
CREATE USER HU@localhost identified by 'PWD';
grant ALL ON HU.* to HU@localhost with grant option;
connect(localhost,HU,PWD,test,MYSQL_PORT,MYSQL_SOCK);
DROP SCHEMA IF EXISTS HU;
CREATE SCHEMA HU ;
USE HU ;
CREATE TABLE STAFF
(EMPNUM   CHAR(3) NOT NULL UNIQUE,
EMPNAME  CHAR(20),
GRADE    DECIMAL(4),
CITY     CHAR(15));
CREATE TABLE PROJ
(PNUM     CHAR(3) NOT NULL UNIQUE,
PNAME    CHAR(20),
PTYPE    CHAR(6),
BUDGET   DECIMAL(9),
CITY     CHAR(15));
INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');
INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');
INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');
INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');
INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');
INSERT INTO PROJ VALUES  ('P1','MXSS','Design',10000,'Deale');
INSERT INTO PROJ VALUES  ('P2','CALM','Code',30000,'Vienna');
INSERT INTO PROJ VALUES  ('P3','SDP','Test',30000,'Tampa');
INSERT INTO PROJ VALUES  ('P4','SDP','Design',20000,'Deale');
INSERT INTO PROJ VALUES  ('P5','IRM','Test',10000,'Vienna');
INSERT INTO PROJ VALUES  ('P6','PAYR','Design',50000,'Deale');
COMMIT WORK;
SELECT COUNT(*) FROM PROJ;
COUNT(*)
6
SELECT COUNT(*) FROM STAFF;
COUNT(*)
5
SELECT EMPNUM, GRADE*1000
FROM HU.STAFF WHERE GRADE * 1000 > ANY
(SELECT SUM(BUDGET) FROM HU.PROJ
GROUP BY CITY, PTYPE
HAVING HU.PROJ.CITY = HU.STAFF.CITY);
EMPNUM	GRADE*1000
E3	13000
SELECT EMPNUM, GRADE*1000
FROM STAFF WHERE GRADE * 1000 > ANY
(SELECT SUM(BUDGET) FROM PROJ
GROUP BY CITY, PTYPE
HAVING PROJ.CITY = STAFF.CITY);
EMPNUM	GRADE*1000
E3	13000
ROLLBACK WORK;