Identify the following Errors in Queries:

 a. Identify errors in the following SQL statement and label errors with error type. To simplify your work, the statement has only one type of error. Rewrite the statement to remove the error.

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 facname = 'Football stadium'

AND empname = 'Mary Manager';

CORRECTION:

SELECT DISTINCT eventrequest.eventno, dateheld, status, estcost FROM eventrequest, employee, facility, eventplan

WHERE estaudience > 5000

AND eventplan.empno = employee.empno

AND eventrequest.facno = facility.facno

AND facname = 'Football stadium'

AND empname = 'Mary Manager';

ERROR: 

Missing join between event-plan and event-request tables. It results in incorrect number of rows.

ERROR TYPE:

Semantic Error.

b. Identify errors in the following SQL statement and label errors with error type. To simplify your work, the statement has only one type of error. Rewrite the statement to remove the error. 

SELECT DISTINCT eventrequest.eventno, dateheld, status, estcost

 FROM eventrequest, eventplan WHERE estaudience > 4000 

AND eventplan.eventno = eventrequest.eventno 

GROUP BY eventrequest.eventno, dateheld, status, estcost;

CORRECTION: 

SELECT DISTINCT eventrequest.eventno, dateheld, status, estcost 

FROM eventrequest, eventplan 

WHERE estaudience > 4000 

AND eventplan.eventno = eventrequest.eventno;

ERROR:

Group by classes are not needed.

ERROR TYPE:

Redundancy error.

c. Identify areas in which the SQL statement has poor coding practices and rewrite the statement to improve the coding practices. You do not need to search for errors.

SELECT eventplan.planno, lineno, resname, numberfld, timestart, timeend

FROM eventrequest, facility, eventplan, eventplanline, resourcetbl

WHERE estaudience = '10000'

AND eventplan.planno = eventplanline.planno

AND eventrequest.facno = facility.facno

AND facname = 'Basketball arena'

AND eventplanline.resno = resourcetbl.resno

AND eventrequest.eventno = eventplan.eventno 

Better Coding:

    SELECT eventplan.planno, lineno, resname, numberfld, timestart, timeend

    FROM eventrequest, facility, eventplan, eventplanline, resourcetbl

    WHERE estaudience = 10000

    AND eventplan.planno = eventplanline.planno

    AND eventrequest.facno = facility.facno

    AND eventplanline.resno = resourcetbl.resno

    AND eventrequest.eventno = eventplan.eventno

Our Official Website : Web Conquerors (https://www.webconquerors.com/) 

Want to get digital services? Contact US

Want to know about our services? Our Services