MariaDB [dreamhome]> select * from Guest ; +---------+--------------+-----------
ID: 3812355 • Letter: M
Question
MariaDB [dreamhome]> select * from Guest ;
+---------+--------------+-----------------------+
| guestno | guestname | guestaddress |
+---------+--------------+-----------------------+
| 10001 | John Kay | 56 High St, London |
| 10002 | Mike Ritchie | 18 Tain St, London |
| 10003 | Mary Tregear | 5 Tarbot Rd, Aberdeen |
| 10004 | Joe Keogh | 2 Fergus Dr, Aberdeen |
| 10005 | Carol Farrel | 6 Achray St, Glasgow |
| 10006 | Tina Murphy | 63 Well St, Glasgow |
| 10007 | Tony Shaw | 12 Park Pl, Glasgow |
+---------+--------------+-----------------------+
select * from Staff;
+---------+-------+-------+------------+------+------------+----------+----------+
| staffNo | fName | lName | position | sex | DOB | salary | branchNo |
+---------+-------+-------+------------+------+------------+----------+----------+
| SA9 | Mary | Howe | Assistant | F | 1970-02-19 | 9000.00 | B007 |
| SG14 | David | Ford | Supervisor | M | 1958-03-24 | 18000.00 | B003 |
| SG37 | Ann | Beech | Assistant | F | 1960-11-10 | 12000.00 | B003 |
| SG5 | Susan | Brand | Manager | F | 1940-06-03 | 24000.00 | B003 |
| SL21 | John | White | Manager | M | 1945-10-01 | 30000.00 | B005 |
| SL41 | Julie | Lee | Assistant | F | 1965-06-13 | 9000.00 | B005 |
+---------+-------+-------+------------+------+------------+----------+----------+
6 rows in set (0.00 sec)
stored procedure please create the following stored routines on CPS3740_2017 database using the dream home database is your email id.
1. Implement a stored procedure p4Q21_xxxx to display the guest name who address contain a given city name.
1a. write SQL statement to run your program with a parameter London and its output on.
2. Implement a stored functions f4Q22_xxxx that will return total number of staff who has salary less than a given number. IF the input salary is a negative number , please return a NULL value.
2a. write the SQL statement to run your program with an input salary 25000 and its output.
3. Implement a stored function f4Q23_XXXX that will take an input number and return the sum of the even numbers that are greater than 0 and less than equal to the input number.
3b. write the SQL statement to run your program with an input number 6 and its output on this paper.
Explanation / Answer
Hi,
Please find below the answers-
1a) create or replace procedure p4Q21_xxxx(city varchar2)
as
declare
var1 varchar2;
v_name varchar2;
begin
select guestname into v_name from Guest where guestaddress like '%city%';
end
Ans 2-
create or replace function f4Q22_xxxx(sal in number) return number is
declare
v_num number;
begin
if sal<0 then
return NULL;
else
select count(*) into v_num from staff where salary<sal;
return v_num;
end if;
end
2a) select f4Q22_xxxx(25000) from staff;
Ans 3-
Ans 3
create or replace function f4Q23_XXXX(input in number) return number is
declare
ctr number:=0;
ctr1 number:=0;
begin
for i in 1..input-1 loop
if i%2==0 then
ctr=ctr+1;
else
ctr=ctr+1;
end if;
return ctr;
end