Please write a stored function named which will take two inputs (type, keyword). The keyword is a substring used to search the course name. You must use LIKE for the pattern match.
Please write a stored function named which will take two inputs (type, keyword).
The keyword is a substring used to search the course name. You must use LIKE for the pattern match.
If the type is not 1 or 2, return an error message “Please input a valid type.”
If the keyword is empty or null, return an error message “Please input a valid keyword.”
If there is no course name contains the keyword, return “No course found that contains keyword: zzz.” where zzz
is the input keyword.
If type is 1, return all course ID: course name that course name contains the keyword. If type is 2, return all course ID: student ID who took the courses that course name contains the keyword.
The return should be in one line and your report format must match the test cases.
You need to use group_concat() function to merge multiple rows output into one line.
![MariaDB
+
I sid
1001 I Austin
I 1002 I Mary
I 1003 I Claudia
1 1004 I Andrew
1 1005 I
Helen
I 1007 I Sarah
1
6 rows in set (0.010
+N
MariaDB [dreamhome]>
cid
+
dreamhome]>
1
1
1
1
I first_name
1
1
1
1
+
--
14
———
-+——
▬▬▬▬▬▬▬▬▬▬
+-
————
7 rows in set (0.014
MariaDB [dreamhome]>
I sid
+
name
CPS1231
CPS2231
1 CPS2232
1 CPS3500
I CPS3740
CPS5920 1 Database Systems
CPS5921 I Data Mining
Java1
Java2
| cid
——
Select *
I
I
I last_name
I
I
I
Huang
Smith
--+
Lee
Lin
Wu
Lee
sec)
Data Structure
Web Programming
Select *
————
Database Introductio
sec)
———+
I
1001 I CPS2231 1
1003 I CPS2231 I 2015 I
1004 T CPS2231 1 2014 T
1001 1 CPS2232 1 2016 T
1004 1 CPS2232 1 2014 1
1005 I CPS2231 I
1007 I
2016 I
CPS2231 1 2015
CPS5920 I
2016 I
1003 I
1007 I
1003
1001 I
1003 I
CPS3740 I 2014 1
CPS5921 I 2016 I
CPS3500 I 2017 |
CPS2231 I 2014 |
CPS2232 I 2014 I
1004 I
1004 I CPS2231 | 2013 | Fal1
from Students;
2013 I Fall
---
rows in set (0.001 sec)
-+
Fal1
Fall
from
I
Spring
Spring
Fall
Spring
Spring
Spring
Fall
Fall
Spring
Spring
I birthday
I
I
1990-01-01 I CS
1976-02-08 I IT
1987-03-21
1947-08-25 I
1997-01-25 I CS
1995-09-15 I
IT
-
Courses;
——————
credits I
4 I
--+--
+
year I semester I grade I
Select * from Students_Courses;
T
I
I
+43333
<< < ÖÖ « »u<Õ ÛÛÛ
4 1
I
| C
I
major
Math
1
I
Biology 1
zipcode
07123
08348
91384
07101
I 07083
1
04101
————
I](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F2ec814c7-90f3-4435-9bb8-fcbab3f733ac%2F8a77bdaf-94a5-494a-b681-d19a885874da%2Fzj530vi_processed.png&w=3840&q=75)
Step by step
Solved in 5 steps with 2 images
