Due date: by 11:59pm on April 18, 2017 Please write stored procedures and stored

Question

Due date: by 11:59pm on April 18, 2017 Please write stored procedures and stored functions based on the dream home database. You must create and test your programs on database CPS3740_2017S at server imc.kean.edu. Your program names must EXACTLY follow each question format. The program name is case sensitive. XXXX means your email ID in the test case examples. The test cases in each question provide as the testing SQL queries with the corresponding results. You should test your programs with different input values and make sure the results are correct. The title of your output should also match the test cases. Please write a stored function named fHW2_l_XXXX (...) based on the Staff table. It should take one argument branch no and it should meet the following 2 test cases. If the given branchno is in the Staff table, please return the average salary of the staff with the given branchno. mysql> select fHW2_l_XXXX('B003') as output: If the given branchno is not in the Staff table, please return a NULL value. mysql> select fHW2_l_XXXX('B009') as output; Please write a stored procedure named pHW2_2_XXXX (...) based on the Hotel, Room and Booking tables. Your program should take one argument hotel name and it should meet the following 2 test cases. If the given hotel name is in the Hotel table, please display the room type, and the number of room hooked for each room type for the given hotel name. mysql> call pHW2_2_XXXX('Grosvenor'); If the given hotel name is empty or null, please display an error message "invalid hotel name". mysql> call pHW2_2_XXXX(NULL); mysql> call pHW2_2_XXXX(");

Explanation / Answer

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
class CStudent:
age = 0
marks = []
name = ""
total = 0
  
def coryza (self, name, age, marks):
self.name = name
self.age = age
self.marks = marks
self.total = self.marks[0] + self.marks[1] + self.marks[2] + self.marks[3] + self.marks[4]
  
#This operate PrintMarks_Mapping isn't employed in this program and it explains a way to use mapping in print statements
def PrintMarks_Mapping(self):
print "%(name)-12s" %,
print "%(age)-4d" %,
print "%(mark)-4d" %,
print "%(mark)-4d" %,
print "%(mark)-4d" %,
print "%(mark)-4d" %,
print "%(mark)-4d" %,
print "%(total)-4d" %

def PrintMarks(self):
print "%-12s" you become familiar.name,
print "%-4d" you become familiar.age,
print "%-4d %-4d %-4d %-4d %-4d" %(self.marks[0],self.marks[1],self.marks[2],self.marks[3],self.marks[4] ),
print "%-4d" you become familiar.total

print "Enter range of Students: ",
maxstudents = zero
maxstudents = input()
i = 0
studentlist = []
#import random as r

for i in range(0, maxstudents):
marks = []
  
name = raw_input("Enter Student Name: ")
  
print "Enter Age: ",
age = input()
print "Enter Mark for Subject1: ",
marks.append(input())
print "Enter Mark for Subject2: ",
marks.append(input())
print "Enter Mark for Subject3: ",
marks.append(input())
print "Enter Mark for Subject4: ",
marks.append(input())
print "Enter Mark for Subject5: ",
marks.append(input())
  
#age = r.randint(16,19)
#for j in range(0, 5):
# marks.append(r.randint(30,99))
  
s = CStudent(name, age, marks);
studentlist.append(s)

print " "
print "Name Age Sub1 Sub2 Sub3 Sub4 Sub5 Total"
for i in range(0, len(studentlist)):
studentlist[i].PrintMarks()



#>>> import b
#Enter range of Students: three
#Enter Student Name: Kathir
#Enter Age: thirty three
#Enter Mark for Subject1: five
#Enter Mark for Subject2: seven
#Enter Mark for Subject3: ten
#Enter Mark for Subject4: twenty
#Enter Mark for Subject5: twenty one
#Enter Student Name: Lachu
#Enter Age: five
#Enter Mark for Subject1: eighty
#Enter Mark for Subject2: eighty five
#Enter Mark for Subject3: eighty nine
#Enter Mark for Subject4: ninety
#Enter Mark for Subject5: ninety two
#Enter Student Name: Aishu
#Enter Age: two
#Enter Mark for Subject1: ninety
#Enter Mark for Subject2: ninety two
#Enter Mark for Subject3: ninety five
#Enter Mark for Subject4: ninety seven
#Enter Mark for Subject5: ninety nine

#Name Age Sub1 Sub2 Sub3 Sub4 Sub5 Total
#Kathir thirty three five seven ten twenty twenty one sixty three
#Lachu five eighty eighty five eighty nine ninety ninety two 436
#Aishu two ninety ninety two ninety five ninety seven nine

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.