DataProcessing.sql

SELECT CUSTNO, CUSTNAME, PHONE, CITY FROM CUSTOMER
WHERE STATE='CO';

SELECT * FROM EVENTREQUEST
WHERE ESTCOST > 4000
ORDER BY DATEHELD;

SELECT EVENTNO, DATEHELD,ESTAUDIENCE FROM EVENTREQUEST
WHERE (STATUS ='Approved' AND ESTAUDIENCE>9000)
OR (STATUS ='Pending' AND ESTAUDIENCE>7000);

SELECT * FROM EVENTREQUEST;

SELECT EVENTNO, DATEHELD, CUSTOMER.CUSTNO, CUSTNAME FROM CUSTOMER INNER JOIN
EVENTREQUEST ON CUSTOMER.CUSTNO=EVENTREQUEST.CUSTNO
WHERE dateheld between '2018-12-01' and '2018-12-31' and CITY='Boulder';

SELECT PLANNO,AVG(NUMBERFLD) AS AVG_NUMBERFLD FROM EVENTPLANLINE
WHERE LOCNO='L100' GROUP BY PLANNO;

SELECT * FROM EVENTPLANLINE;

SELECT PLANNO, AVG(NUMBERFLD) AS AVG_NUMBERFLD FROM EVENTPLANLINE
WHERE LOCNO='L100' GROUP BY PLANNO HAVING COUNT(LOCNO) > 1;

SELECT PLANNO, AVG(NUMBERFLD) AS AVG_NUMBERFLD FROM EVENTPLANLINE
WHERE LOCNO='L100' GROUP BY PLANNO HAVING COUNT(*) > 1;

SELECT * FROM CUSTOMER;

/*POSTGRES*/
SELECT DISTINCT CITY, STATE, ZIP FROM CUSTOMER;


/*POSTGRES*/
SELECT EMPNAME, DEPARTMENT, PHONE, EMAIL FROM EMPLOYEE
WHERE PHONE LIKE '3%';

/*POSTGRES*/
SELECT * FROM RESOURCETBL
WHERE RATE BETWEEN 10 AND 20
ORDER BY RATE;

/*POSTGRES*/
SELECT EVENTNO, DATEAUTH, STATUS FROM EVENTREQUEST
WHERE DATEAUTH BETWEEN '2018-07-01' AND '2018-07-31';

/*POSTGRES*/
SELECT LOCNO, LOCATION.FACNO FROM LOCATION INNER JOIN FACILITY
ON FACILITY.FACNO=LOCATION.FACNO
WHERE FACNAME='Basketball arena';

/*POSTGRES*/
SELECT PLANNO, COUNT(PLANNO) AS EVENT_PLAN_LINES_COUNT, 
SUM(NUMBERFLD) AS NUM_RESOURCES
FROM EVENTPLANLINE
GROUP BY PLANNO;

/*POSTGRES*/
SELECT EVENTREQUEST.EVENTNO, DATEHELD, CUSTOMER.CUSTNO, CUSTNAME, FACILITY.FACNO,FACNAME 
FROM EVENTREQUEST,CUSTOMER, FACILITY
WHERE EVENTREQUEST.CUSTNO=CUSTOMER.CUSTNO
AND EVENTREQUEST.FACNO=FACILITY.FACNO
AND EXTRACT (YEAR FROM DATEHELD)=2018 AND CITY='Boulder' AND STATE='CO';

/*POSTGRES*/
SELECT EVENTREQUEST.EVENTNO, DATEHELD, CUSTOMER.CUSTNO, CUSTNAME, 
FACILITY.FACNO,FACNAME, ESTCOST/ESTAUDIENCE AS COST_PER_PERSON
FROM EVENTREQUEST,CUSTOMER, FACILITY
WHERE EVENTREQUEST.CUSTNO=CUSTOMER.CUSTNO
AND EVENTREQUEST.FACNO=FACILITY.FACNO
AND EXTRACT (YEAR FROM DATEHELD)=2018
AND ESTCOST/ESTAUDIENCE<0.2;

/*POSTGRES*/
SELECT CUSTOMER.CUSTNO, CUSTNAME, SUM(ESTCOST) AS TOTAL 
FROM CUSTOMER, EVENTREQUEST
WHERE EVENTREQUEST.CUSTNO=CUSTOMER.CUSTNO
AND STATUS='Approved'
GROUP BY CUSTOMER.CUSTNO, CUSTNAME;

/*POSTGRES*/
Insert into CUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C999','Swimming','Box 352200','Y','Kathy Manager','6857100','Bethesda','MD','20814');
SELECT * FROM CUSTOMER;

/*POSTGRES*/
UPDATE RESOURCETBL
SET Rate=Rate*1.1
WHERE RESNAME='nurse';

/*POSTGRES*/
DELETE FROM Customer
  WHERE CustNo = 'C999';

/*POSTGRES*/
SELECT EVENTREQUEST.EVENTNO, DATEHELD, COUNT(PLANNO) AS EVEN_PLAN_COUNT
FROM EVENTREQUEST, EVENTPLAN
WHERE EVENTREQUEST.EVENTNO=EVENTPLAN.EVENTNO
AND WORKDATE BETWEEN '2018-12-01' AND '2018-12-31'
GROUP BY EVENTREQUEST.EVENTNO
HAVING COUNT(PLANNO)>1;

/*POSTGRES*/
SELECT EVENTPLAN.PLANNO, EVENTPLAN.EVENTNO, WORKDATE, ACTIVITY 
FROM EVENTREQUEST, EVENTPLAN, FACILITY
WHERE EVENTPLAN.EVENTNO=EVENTREQUEST.EVENTNO
AND EVENTREQUEST.FACNO=FACILITY.FACNO
AND WORKDATE BETWEEN '2018-12-01' AND '2018-12-31'
AND FACNAME='Basketball arena';

/*POSTGRES*/
SELECT EVENTNO, DATEHELD, STATUS, ESTCOST 
FROM EVENTREQUEST, CUSTOMER, FACILITY
WHERE EVENTREQUEST.CUSTNO=CUSTOMER.CUSTNO
AND FACILITY.FACNO=EVENTREQUEST.FACNO
AND CONTACT='Mary Manager'
AND FACNAME='Basketball arena'
AND DATEHELD BETWEEN '2018-10-01' AND '2018-12-31';
SELECT * FROM CUSTOMER;

/*POSTGRES*/
SELECT EVENTPLAN.PLANNO, LINENO, RESNAME, NUMBERFLD, LOCNAME, TIMESTART, TIMEEND 
FROM EVENTPLANLINE, RESOURCETBL, LOCATION, FACILITY, EVENTPLAN
WHERE EVENTPLANLINE.RESNO=RESOURCETBL.RESNO
AND LOCATION.LOCNO=EVENTPLANLINE.LOCNO
AND FACILITY.FACNO=LOCATION.FACNO
and EVENTPLAN.PLANNO=EVENTPLANLINE.PLANNO
AND FACNAME='Basketball arena'
AND ACTIVITY='Operation'
AND WORKDATE BETWEEN '2018-10-01' AND '2018-12-31';

/*POSTGRES*/
INSERT INTO FACILITY (FACNO,FACNAME) VALUES ('1234', 'Swimming Pool');
SELECT * FROM FACILITY;

/*POSTGRES*/
INSERT INTO LOCATION (LOCNO, FACNO, LOCNAME) VALUES ('L107', '1234', 'DOOR');
SELECT * FROM LOCATION;

/*POSTGRES*/
INSERT INTO LOCATION (LOCNO, FACNO, LOCNAME) VALUES ('L108', '1234', 'Locker Room');
SELECT * FROM LOCATION;

/*POSTGRES*/
UPDATE LOCATION
SET LOCNAME='Gate'
WHERE LOCNO='L107';
SELECT * FROM LOCATION;

/*POSTGRES*/
DELETE FROM LOCATION 
WHERE LOCNO='L108';
SELECT * FROM LOCATION;

/*POSTGRES*/
/*semantic error, eventrequest.eventno=eventplan.eventno missing*/
SELECT eventrequest.eventno, dateheld, status, estcost
FROM eventrequest, employee, facility, eventplan
WHERE estaudience > 5000
  AND eventplan.empno = employee.empno 
  AND eventrequest.facno = facility.facno
  AND eventrequest.eventno=eventplan.eventno
  AND facname = 'Football stadium' 
  AND empname = 'Mary Manager';

/*POSTGRES*/
/*semantic error, no need for GROUP BY */
SELECT DISTINCT eventrequest.eventno, dateheld, status, estcost
FROM eventrequest, eventplan
WHERE estaudience > 4000
  AND eventplan.eventno = eventrequest.eventno; 

/*POSTGRES*/
/*redundancy*/
SELECT DISTINCT eventrequest.eventno, dateheld, status, estcost
FROM eventrequest, employee, facility, eventplan
WHERE estaudience > 5000
  AND eventrequest.facno = facility.facno
  AND facname = 'Football stadium';
/
/*POSTGRES*/
SELECT DISTINCT eventno, dateheld, status, estcost
FROM eventrequest, employee, eventplan
WHERE estaudience BETWEEN 5000 AND 10000
  AND eventplan.empno = employee.empno 
  AND eventrequest.eventno = eventplan.eventno
  AND empname = 'Mary Manager';
  
/*POSTGRES*/
SELECT eventplan.planno, lineno, resname, numberfld, timestart, timeend
FROM eventrequest, facility, eventplan, eventplanline, resourcetbl
WHERE estaudience = '10000'
  AND facname = 'Basketball arena' 
  AND eventplan.planno =eventplanline.planno 
  AND eventrequest.facno = facility.facno
  AND eventplanline.resno = resourcetbl.resno
  AND eventrequest.eventno = eventplan.eventno;