· 8 years ago · Apr 02, 2018, 03:42 AM
11. Write a program to read in a number and print it out digit by digit, as a series of words.
2-------------
3DROP PROCEDURE IF EXISTS pro19;
4DELIMITER $$
5 CREATE PROCEDURE pro19(in i int)
6begin
7
8 declare no int;
9 declare rev int default 0;
10 while (i>0) do
11 set no=i%10;
12 set rev= (rev*10)+no;
13 set i=floor(i/10);
14 end while;
15
16
17 while (rev>0) do
18
19 set no=rev%10;
20
21 if no=1 then
22 select "one ";
23 elseif no=2 then
24 select "two ";
25
26 elseif no=3 then
27 select "three ";
28
29 elseif no=4 then
30 select "four ";
31
32 elseif no=5 then
33 select "five ";
34
35 elseif no=6 then
36 select "six ";
37
38 elseif no=7 then
39 select "seven ";
40
41 elseif no=8 then
42 select "eight ";
43
44 elseif no=9 then
45 select "nine ";
46
47 elseif no=0 then
48 select "zero ";
49 end if;
50 set rev=floor(rev/10);
51 end while;
52
53
54end$$
55-------------
56mysql> source Z:\DBT\Assignment\pl14.sql
57Query OK, 0 rows affected (0.00 sec)
58
59Query OK, 0 rows affected (0.00 sec)
60
61mysql> call pro19(378);
62+--------+
63| three |
64+--------+
65| three |
66+--------+
671 row in set (0.00 sec)
68
69+--------+
70| seven |
71+--------+
72| seven |
73+--------+
741 row in set (0.00 sec)
75
76+--------+
77| eight |
78+--------+
79| eight |
80+--------+
811 row in set (0.00 sec)
82
83Query OK, 0 rows affected (0.01 sec)
84------------------------------------------------------------------------------------------------
852. Write a program to calculate total and percentage of marks of the students in four subjects (maths,
86science, English, and history).
87-------------
88DROP PROCEDURE IF EXISTS pro11;
89DELIMITER $$
90 CREATE PROCEDURE pro11(in m1 int,in m2 int,in m3 int,in m4 int)
91begin
92 declare total int;
93 declare per float;
94 set total=m1+m2+m3+m4;
95 set per=total/4;
96 select total as total, per as percentage;
97
98end$$
99delimiter ;
100
101-------------
102mysql> source Z:\DBT\Assignment\pl6.sql
103Query OK, 0 rows affected (0.00 sec)
104
105Query OK, 0 rows affected (0.00 sec)
106
107mysql> call pro11(80,80,80,80);
108+-------+------------+
109| total | percentage |
110+-------+------------+
111| 320 | 80 |
112+-------+------------+
1131 row in set (0.00 sec)
114
115Query OK, 0 rows affected (0.00 sec)
116------------------------------------------------------------------------------------------------
1173. Write a block to accept values for NUM1 and NUM2 at run time. Store the remainder of NUM1
118divided by NUM2 in a Sql variable called RESULT. Check the contents of RESULT variable for
119correctness.
120-------------
121DROP PROCEDURE IF EXISTS pro12;
122DELIMITER $$
123 CREATE PROCEDURE pro12(in no1 int,in no2 int)
124begin
125 declare result float;
126 set result =no1%no2;
127 select result as result;
128
129end$$
130delimiter ;
131
132-------------
133mysql> source Z:\DBT\Assignment\pl7.sql
134Query OK, 0 rows affected, 1 warning (0.00 sec)
135
136Query OK, 0 rows affected (0.00 sec)
137
138mysql> call pro12(80,8);
139+--------+
140| result |
141+--------+
142| 0 |
143+--------+
1441 row in set (0.00 sec)
145
146Query OK, 0 rows affected (0.00 sec)
147
148mysql> call pro12(7,8);
149+--------+
150| result |
151+--------+
152| 7 |
153+--------+
1541 row in set (0.00 sec)
155
156Query OK, 0 rows affected (0.00 sec)
157------------------------------------------------------------------------------------------------
1584. Build a block that computes the total compensation for one year. The annual salary and the annual
159bonus percentage are passed to the PL/SQL block through SQL substitution variables. If the salary
160is null set it to zero before computing the total compensations.
161-------------
162-------------
163------------------------------------------------------------------------------------------------
1645. Create a block that selects the maximum department number from the department table and
165stores it in a variable.
166-------------
167DROP PROCEDURE IF EXISTS pro14;
168DELIMITER $$
169 CREATE PROCEDURE pro14()
170begin
171 declare max_id int;
172 select max(id) from deptartment into max_id;
173 select max_id;
174end$$
175delimiter ;
176
177-------------
178mysql> source Z:\DBT\Assignment\pl9.sql
179Query OK, 0 rows affected (0.00 sec)
180
181Query OK, 0 rows affected (0.00 sec)
182
183mysql> call pro14();
184+--------+
185| max_id |
186+--------+
187| 100 |
188+--------+
1891 row in set (0.00 sec)
190
191Query OK, 0 rows affected (0.00 sec)
192------------------------------------------------------------------------------------------------
1936. Create a block that inserts a new department into the DEPT table.
194-------------
195DROP PROCEDURE IF EXISTS pro13;
196DELIMITER $$
197 CREATE PROCEDURE pro13(in x int,in name varchar(20),in loc varchar(20))
198begin
199 insert into deptartment values(x,name,loc);
200 select * from deptartment;
201
202end$$
203delimiter ;
204
205-------------
206mysql> select * from deptartment;
207+----+------------+----------+
208| id | name | loc |
209+----+------------+----------+
210| 10 | ACCOUNTING | NEW YORK |
211| 20 | RESEARCH | DALLAS |
212| 30 | SALES | CHICAGO |
213| 40 | OPERATIONS | BOSTON |
214| 50 | HRD | OHIO |
215| 60 | FINANCE | FLORIDA |
216| 70 | PLANNING | HAWAII |
217| 80 | MARKETING | INDIANA |
218| 90 | NULL | ALASKA |
219+----+------------+----------+
2209 rows in set (0.04 sec)
221
222mysql> source Z:\DBT\Assignment\pl8.sql
223Query OK, 0 rows affected, 1 warning (0.00 sec)
224
225Query OK, 0 rows affected (0.00 sec)
226
227mysql> call pro13(100,"Developement","new york");
228+-----+--------------+----------+
229| id | name | loc |
230+-----+--------------+----------+
231| 10 | ACCOUNTING | NEW YORK |
232| 20 | RESEARCH | DALLAS |
233| 30 | SALES | CHICAGO |
234| 40 | OPERATIONS | BOSTON |
235| 50 | HRD | OHIO |
236| 60 | FINANCE | FLORIDA |
237| 70 | PLANNING | HAWAII |
238| 80 | MARKETING | INDIANA |
239| 90 | NULL | ALASKA |
240| 100 | Developement | new york |
241+-----+--------------+----------+
24210 rows in set (0.04 sec)
243
244Query OK, 0 rows affected (0.05 sec)
245
246------------------------------------------------------------------------------------------------
2477. Create a block that updates the location for an existing department. Accept the input from the
248user. Display the details of the updated department.
249-------------
250DROP PROCEDURE IF EXISTS pro15;
251DELIMITER $$
252 CREATE PROCEDURE pro15(in i int,in l varchar(20))
253begin
254 select * from deptartment where id=i;
255 update deptartment set loc= l where id=i;
256 select * from deptartment where id=i;
257end$$
258delimiter ;
259
260-------------
261mysql> source Z:\DBT\Assignment\pl10.sql
262Query OK, 0 rows affected (0.00 sec)
263
264Query OK, 0 rows affected (0.00 sec)
265
266mysql> call pro15(30,"Chicago");
267+----+-------+-------+
268| id | name | loc |
269+----+-------+-------+
270| 30 | SALES | Paris |
271+----+-------+-------+
2721 row in set (0.00 sec)
273
274+----+-------+---------+
275| id | name | loc |
276+----+-------+---------+
277| 30 | SALES | Chicago |
278+----+-------+---------+
2791 row in set (0.03 sec)
280
281Query OK, 0 rows affected (0.03 sec)
282
283------------------------------------------------------------------------------------------------
2848. Create a block that deletes the department created in the previous exercise. Print on the screen the
285number of rows affected. Also test the condition if you enter a department number that does not
286exist.
287-------------
288DROP PROCEDURE IF EXISTS pro18;
289DELIMITER $$
290 CREATE PROCEDURE pro18(in i int)
291begin
292 declare x int;
293 select * from deptartment where id=i;
294 select count(*) from deptartment where id=i into x;
295 if x>=1 then
296 delete from deptartment where id=i;
297 end if;
298 select x "rows affected";
299 select * from deptartment where id=i;
300end$$
301delimiter ;
302
303-------------
304mysql> source Z:\DBT\Assignment\pl13.sql
305Query OK, 0 rows affected, 1 warning (0.00 sec)
306
307Query OK, 0 rows affected (0.00 sec)
308
309mysql> call pro18(100);
310+-----+--------------+----------+
311| id | name | loc |
312+-----+--------------+----------+
313| 100 | Developement | new york |
314+-----+--------------+----------+
3151 row in set (0.00 sec)
316
317+---------------+
318| rows affected |
319+---------------+
320| 1 |
321+---------------+
3221 row in set (0.02 sec)
323
324Empty set (0.02 sec)
325
326Query OK, 0 rows affected (0.02 sec)
327
328------------------------------------------------------------------------------------------------
3299. Write a procedure to accept a parameter as employee id and display the information of that
330employee.
331-------------
332DROP PROCEDURE IF EXISTS pro17;
333DELIMITER $$
334 CREATE PROCEDURE pro17(in id int)
335begin
336 select * from employee where empno=id;
337end$$
338delimiter ;
339
340-------------
341mysql> source Z:\DBT\Assignment\pl12.sql
342Query OK, 0 rows affected, 1 warning (0.00 sec)
343
344Query OK, 0 rows affected (0.00 sec)
345
346mysql> call pro17(7902);
347+-------+-----------+----------+--------+-------------+------+------------+------+------+--------+
348| empno | firstname | lastname | gender | designation | mgr | hiredate | sal | comm | deptno |
349+-------+-----------+----------+--------+-------------+------+------------+------+------+--------+
350| 7902 | Louise | Ford | F | Sr.Analyst | 7654 | 1981-12-03 | 3000 | NULL | 30 |
351+-------+-----------+----------+--------+-------------+------+------------+------+------+--------+
3521 row in set (0.00 sec)
353
354Query OK, 0 rows affected (0.01 sec)
355------------------------------------------------------------------------------------------------
35610. Write a procedure to accept job from the user and return sum of salary with his job name. Hint
357(job, sal should be returned as OUT parameter).
358-------------
359-------------
360------------------------------------------------------------------------------------------------
36111. Create a table TEMP (col1 int, col2 varchar (20)) from the procedure
362-------------
363DROP PROCEDURE IF EXISTS pro16;
364DELIMITER $$
365 CREATE PROCEDURE pro16()
366begin
367 Create temporary table t1 (col1 int, col2 varchar (20)) ;
368 desc t1;
369end$$
370delimiter ;
371
372-------------
373mysql> source Z:\DBT\Assignment\pl11.sql
374Query OK, 0 rows affected, 1 warning (0.00 sec)
375
376Query OK, 0 rows affected (0.00 sec)
377
378mysql> call pro16();
379+-------+-------------+------+-----+---------+-------+
380| Field | Type | Null | Key | Default | Extra |
381+-------+-------------+------+-----+---------+-------+
382| col1 | int(11) | YES | | NULL | |
383| col2 | varchar(20) | YES | | NULL | |
384+-------+-------------+------+-----+---------+-------+
3852 rows in set (0.01 sec)
386
387Query OK, 0 rows affected (0.01 sec)
388
389------------------------------------------------------------------------------------------------