Question

##Relation Work: workld customerName totalHours jobCompleted equipment countOfWorkers day price Dog True hammer 100 Cat 2 False saw 90 Horse 1 True nailgun 160 Bat drill 30 2 2 False True Pig impact 2 75 ##Relation Customer ID customerName custo customerPhone customerAddress 10 Dog 1231231234 11 st 11 Cat 3213213210 12 st 12 Horse 2462462468 13 st 3693693691 14 st 13 14 Bat Pig 4812481216 15 st ##Create a MySQL Function that has no arguments and no return values. Show the day, customerName, customerAddress, customerPhone, customerID, countOfWorkers, equipment, totalHours, price attributes in the display. Attributes should be sorted by the day of the week Monday to Friday.

Answer 1

We use stored procedure because it may or may not return value but function must return a value.

We have first change delimiter as // to mark end of strored procedure

Then write stored procedure and then change delimiter as ;

after that call procedure to display records from tables.

mysql>DELIMITER //

mysql>CREATE PROCEDURE simple()

BEGIN

SELECT day, customerName, customerAddress ,customerPhone, ID, countOfWorkers,equipment, totalHours, price FROM Work,Customer ORDER BY FIELD(day,'M','T','W','H','F');

END//

mysql>DELIMITER ;

mysql>call simple();