· 8 years ago · Apr 02, 2018, 02:38 AM
1Trigger
21. Write a trigger that prints the message "Record inserted successfully" as soon as you insert the
3record in DEPT table.
4----------------
5
6drop trigger if exists t1;
7delimiter $
8create trigger t1 after insert on deptartment for each row
9begin
10select "Record inserted successfully" ;
11end $
12delimiter ;
13--------------------
14 source Z:\DBT\Assignment\tr1.sql
15Query OK, 0 rows affected, 1 warning (0.00 sec)
16
17ERROR 1415 (0A000): Not allowed to return a result set from a trigger
18mysql>
19
20
21
22---------------------------------------------------------------------------------------------
232. Write a trigger on EMP table, that as when we INSERT a record in EMP table the same record
24should get duplicated (INSERTED) in EMP_LOG table. (Create EMP_LOG table, having the same
25structure as EMP table).
26----------------
27drop trigger if exists tr2;
28delimiter $
29create trigger tr2 before insert on employee for each row
30begin
31insert into emp_log values(new.empno,new.firstname,new.lastname,new.gender,new.designation, new.mgr, new.hiredate,new.sal,new.comm,new.deptno);
32end $
33delimiter ;
34--------------------
35create table employee_log as select * from employee;
36Query OK, 30 rows affected (0.15 sec)
37Records: 30 Duplicates: 0 Warnings: 0
38
39mysql> select * from employee_log;
40+-------+-------------+----------+--------+--------------------+------+------------+------+------+--------+
41| empno | firstname | lastname | gender | designation | mgr | hiredate | sal | comm | deptno |
42+-------+-------------+----------+--------+--------------------+------+------------+------+------+--------+
43| 6001 | Denis | Murphy | M | Sr.Assistant | 7654 | 1980-05-25 | 2675 | NULL | 80 |
44| 6002 | Jenny | Ross | F | Sr.Assistant | 7902 | 1980-05-02 | 2675 | NULL | 80 |
45| 6003 | David | Ross | M | Sr.Assistant | 7654 | 1980-05-02 | 2675 | NULL | 80 |
46| 6004 | Fred | NULL | M | Sr.Assistant | 7521 | 1980-05-02 | 2675 | NULL | 80 |
47| 6005 | Helen | Taylor | F | Sr.Assistant | 7902 | 1980-05-02 | 2675 | NULL | 80 |
48| 7369 | Rosaleen | Smith | F | Head Clerk | 7654 | 1980-12-17 | 800 | NULL | 40 |
49| 7415 | Kent | Clark | F | Head Clerk | 7654 | 1981-12-31 | 3350 | NULL | 60 |
50| 7499 | Sharon | Allen | F | Sr.SALESMAN | 7782 | 1981-02-20 | 1600 | 300 | 10 |
51| 7521 | Stacy | Ward | F | Sr.SALESMAN | 7782 | 1981-02-22 | 1250 | 500 | 20 |
52| 7566 | Betty | Jones | F | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 30 |
53| 7654 | Philip | Martin | M | OPERATIONS MANAGER | 7566 | 1981-09-28 | 1250 | 1400 | 40 |
54| 7698 | Lisa | Blake | F | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 20 |
55| 7717 | David | Bell | M | OPERATIONS ANALYST | 7839 | 1980-10-27 | 2800 | NULL | 10 |
56| 7771 | Peter | Anderson | M | BUSINESS MANAGER | 7839 | 1981-01-03 | 3500 | NULL | 60 |
57| 7782 | Julia | Clark | F | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
58| 7788 | Alexander | Scott | M | Sr.Analyst | 7654 | 1982-12-09 | 3000 | NULL | 50 |
59| 7818 | Emma | Forster | F | BUSINESS MANAGER | 7839 | 1981-11-19 | 3500 | NULL | 20 |
60| 7839 | Kerr | King | F | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
61| 7844 | Sylvia | Turner | F | Sr.SALESMAN | 7782 | 1981-09-08 | 1500 | 0 | 20 |
62| 7876 | Adams | Baldwin | M | Head Clerk | 7521 | 1983-01-12 | 1100 | NULL | 50 |
63| 7900 | Jenny | James | F | Sr.SALESMAN | 7782 | 1981-12-03 | 950 | NULL | 10 |
64| 7902 | Louise | Ford | F | Sr.Analyst | 7654 | 1981-12-03 | 3000 | NULL | 30 |
65| 7919 | Brandon | Routh | M | MANAGER | 7654 | 1982-03-24 | 4150 | NULL | 30 |
66| 7920 | Christopher | Reeve | M | Sr.SALESMAN | 7949 | 1980-02-14 | 2575 | 2700 | 10 |
67| 7934 | Roland | Miller | M | Head Clerk | 7654 | 1982-01-23 | 1300 | NULL | 30 |
68| 7945 | Christopher | Rose | M | Sr.SALESMAN | 7654 | 1980-02-14 | 1350 | 2700 | 50 |
69| 7949 | George | Michael | M | MANAGER | 7654 | 1982-01-24 | 1250 | 500 | 20 |
70| 8008 | Christopher | Maxfield | M | Sr.SALESMAN | 7771 | 1980-12-17 | 3000 | 2300 | 10 |
71| 8110 | Andrew | Kind | M | Head Clerk | 7521 | 1981-12-20 | 1200 | NULL | 20 |
72| 8181 | Mark | Forster | M | OFFICE MANAGER | 7771 | 1980-07-19 | 2120 | NULL | 30 |
73+-------+-------------+----------+--------+--------------------+------+------------+------+------+--------+
7430 rows in set (0.00 sec)
75
76
773. Write a trigger on EMP table, that as soon as we UPDATE any column data in EMP table, the
78update record should get inserted in EMP_LOG table.
79
80--------------------
81
82 desc emp_log;
83+--------------+-------------+------+-----+---------+-------+
84| Field | Type | Null | Key | Default | Extra |
85+--------------+-------------+------+-----+---------+-------+
86| originalName | varchar(20) | YES | | NULL | |
87| changdeName | varchar(20) | YES | | NULL | |
88+--------------+-------------+------+-----+---------+-------+
892 rows in set (0.00 sec)
90
91mysql> select * from emp_log;
92+--------------+-------------+
93| originalName | changdeName |
94+--------------+-------------+
95| Alexander | abc |
96| abc | saleel |
97| saleel | Alexander |
98+--------------+-------------+
993 rows in set (0.00 sec)
100
101
102Joins
103Solve the following queries using DEPT, EMP, HOBBIES, PHONEDETAILS, DESIGNATIONHISTORY,
104and BONUS tables:
105
106-- get the name (first name, last name) for those employees who gets more salary than the employee
107whose ID is 7788.
108select concat(firstname,lastname) name,sal from employee where sal > (select sal from employee where empno=7788);
109
110
111-- get the name (first name, last name), salary, deptno, DESIGNATION for those employees who works in
112the same designation as the employee works whose id is 7566.
113select concat(firstname,lastname) name,sal,deptno,designation from employee where *****
114
115
116-- get the average salary of all the employee.
117select avg(sal) from employee;
118
119
120-- get the sum of all employee salary.
121select sum(sal) from employee;
122
123-- get the first name, last name, department number, and department name for each employee.
124select e.firstname, e.lastname,e.deptno,d.name from employee e,deptartment d where d.id=e.deptno;
125
126
127-- get the first and last name, department, city, and state province for each employee
128
129
130-- get the first name, last name, salary, and DESIGNATION grade for all employees.
131
132
133-- get the first name, last name, department number and department name, for all employees for
134departments 20 or 40.
135select e.firstname, e.lastname,e.deptno,d.name from employee e,deptartment d where d.id=e.deptno and e.deptno=20 or e.deptno=40;
136
137
138-- get those employees who contain a letter Z to their first name and also display their last name,
139department, city, and state province.
140
141
142
143-- get all departments including those where does not have any employee
144select d.name from deptartment d, employee e where d.id=e.deptno group by e.deptno having count(e.deptno)>0;*******
145
146
147
148-- get the first and last name and salary for those employees who earn less than the employee earn whose
149number is 7499.
150 select firstname,lastname,sal from employee where sal< (select sal from employee where empno=7499) ;
151
152
153-- get the first name of all employees including the first name of their -- manager.
154 select e.firstname,d.firstname as manager from employee e, employee d where d.empno=e.mgr;
155
156-- get the department name, city, and state province for each department.
157
158
159-- get the first name, last name, and department number for those employees who works in the same
160department as the employee who holds the last name as `Michael`.
161select e.firstname,e.lastname,e.deptno from employee e,(select deptno from employee where lastname='michael') d where e.deptno=d.deptno;
162
163-- get employee firstname, lastname, depname, loc of empno is7788.
164
165
166-- get employee firstname and phone no empno is 7771.
167select e.firstname, p.phonenumber from employee e, phonedetails p where e.empno=p.empno and e.empno=7771 ;
168
169-- get employee name, DESIGNATION details.
170select e.firstname,e.designation,d.name from employee e,deptartment d where e.deptno=d.id;
171
172-- get employee details with hobbies.
173
174
175-- get employee details and DESIGNATION details.
176select e.empno,e.firstname,e.lastname,e.designation,d.name from employee e,deptartment d where e.deptno=d.id;
177
178-- get employee details and DESIGNATION details and hobbies.
179select e.firstname,e.designation,h.name from employee e,hobbies h where h.empno=e.empno ;
180
181
182-- get employee name with DESIGNATION details with dept name is research.
183-- get employee name, dept name whose salary is highest.
184-- get employee phone number along with DESIGNATION details.
185-- get employee details whose location is ‘CICAGO’.
186-- get employee salary, current DESIGNATION, hobbies.
187-- get the location of ‘SMITH’.
188-- get employee avg salary, DESIGNATION details.
189-- get employee details whose DESIGNATION is clerk and hobbies is ‘FOOTBALL’.
190Infoway Technologies, 3rd Floor Commerce Centre, Rambaug Colony, Paud Road Pune 411038
191-- get the list of employees having hobby is ‘PLAYING CHESS’.
192-- get the list of employees whose salary is below 2000 and department name is ‘ACCOUNTING’.
193-- get the list of female employees whose location is ‘New York’.
194-- get the count of employees working in ‘ACCOUNTING’ department.
195-- get the count the number of dept who’s working as ‘Head Clerk’.
196-- get firstname, current DESIGNATION is ‘Head Clerk’.
197-- get employee with phone number and employee having highest salary.
198-- get employee firstname and salary, loc whose hire date is 1980’
199-- get employee firstname and salary, DESIGNATION whose hire date is 17’
200-- get employee firstname and hobbies whose hire date is 1982-12-09 00:00:00.
201-- get employee details and dept details and the hire date is 17 05 1990 format.
202-- get the count of hiredate of employee joined in the month of DECEMBER.
203-- get employee details and whose DESIGNATION is ‘PRESIDENT’.
204-- get employee firstname, salary and hobbies as (KING : 5000 : CHESS);
205-- get employee details with hobbies whose department is ‘ACCOUNTING’.
206-- get employee details along with phone numbers of deptno 10.
207-- get all employees which phone numbers starting with 8.
208-- get the count the number of employees having hobby of ‘PLAYING CRICKET’.
209-- get the number of dept having the hobby of ‘PLAYING FOOTBALL’.
210
211
212-- get employee details along with number of employees working in department number 20.
213
214
215-- get how many employees belong to ‘CHICAGO’.
216
217
218-- get how many of male employees working in dept ‘ACCOUNTING’.
219
220
221-- get employee details and DESIGNATION details who working as a ‘SALARYESMAN’.
222select * from employee where designation='sr.salesman';
223
224
225
226-- get the mgrs who are senior to KING and who are junior to ‘SMITH’.
227select e.firstname,d.mgr from employee e, employee d (select hiredate from employee where firstname='king') (select hiredate from employee where firstname='smith') ) where e.empno=d.mgr****
228
229
230
231
232-- get the total information of EMP table along with DNAME and LOC of all the emps Working Under
233‘ACCOUNTING’ & ‘RESEARCH’ in the asc department number.
234
235
236-- get total remuneration (salary + comm.) of all employees of SALES department
237 select e.*,sal + ifnull(comm,0) remuneration from employee e, deptartment d where e.deptno=d.id and d.name='sales';
238
239-- get all employees whose salgrade is 1.
240 select e.* from employee e, salarygrade s where s.name='grade1' and e.sal between s.lowsalary and s.highsalary;
241
242-- get FIRSTNAME, LASTNAME, CURRENTDESIGNATION, and his previous DESIGNATION details.
243select e.firstname,e.lastname,e.designation,d.designation previous_des from employee e,designationhistory d where e.empno=d.empno;
244
245
246
247String and Aggregate Functions
248Solve the following queries using DEPT, EMP, HOBBIES, PHONEDETAILS, JOBHISTORY, and BONUS
249tables:
250
251
252-- get employee FIRSTNAME with how many characters are there in their FIRSTNAME.
253select firstname, length(firstname) as no_of_chars from employee;
254
255-- get employee details whose FIRSTNAME is having at least 4 characters.
256select * from employee where length(firstname)>=4;
257
258-- get the ASCII value of the 3rd character of FIRSTNAME column.
259 select firstname,substr(firstname,3,1) as 3_char, ascii(substr(firstname,3,1)) as ascii_no from employee;
260
261-- get FIRSTNAME and LASTNAME in lowercase.
262select Lcase(firstname) as firstname, lcase(lastname) as lastname from employee;
263
264-- get all 7 letter hobbies.
265select name from hobbies where length(name)=7;
266
267-- get first 3 letters of FIRSTNAME.
268select left(firstname,3) as name from employee;
269
270-- get last 3 letters of FIRSTNAME.
271select right(firstname,3) as name from employee;
272
273-- get all PHONENUMBER, whose PHONENUMBER starts with 9850.
274select phonenumber from phonedetails where substr(phonenumber,1,4) = '9850';
275
276-- get lowest salary of employee.
277
278
279-- get average sal of employee.
280select avg(sal) from employee;
281
282-- get sum of salary of employees.
283select sum(sal) from employee;
284
285-- get employee details of first 5 employees.
286select * from employee limit 0,5;
287
288-- get employee details of last 5 employees.
289
290
291-- get employee details in ascending order.
292select * from employee order by empno;
293
294-- get employee details in descending order.
295select * from employee order by empno desc;
296
297-- get the character length of particular string.
298select firstname, length(firstname) from employee;
299
300-- Combine to display employee FIRSTNAME and LASTNAME.
301select concat(firstname,' ',lastname) as name from employee;
302
303-- get employee FIRSTNAME and LASTNAME in upper case.
304select ucase(firstname) fname, ucase(lastname) lname from employee;
305
306-- get employee FIRSTNAME and LASTNAME in lower case.
307select lcase(firstname) fname, lcase(lastname) lname from employee;
308
309-- get employee FIRSTNAME and LASTNAME in reverse order.
310select reverse(firstname) fname, reverse(lastname) lname from employee;
311
312-- get first 4 letters of employee FIRSTNAME.
313select firstname,substr(firstname,1,4) from employee;
314
315-- get second letter of employee to second last letter of employee.
316select firstname,substr(firstname,2,length(firstname)-2);
317
318-- get ASCII character of employee name.
319select firstname, ascii(firstname) from employee;
320
321-- get 5 letter of the employee FIRSTNAME.
322select firstname,substr(firstname,1,5) from employee;
323
324
325-- get highest commission of employee.
326select *, max(comm) from employee;
327
328-- get second highest salary of employee.
329select sal ,count(*) from employee group by sal order by sal desc limit 3,1;
330
331-- Print salary in this format 3000***.
332select sal , rpad(sal,7,'*') from employee;
333
334-- get the count of employees present.
335
336
337-- get the count that how many employees are falling in salgrade 3.
338select e.firstname,e.lastname,e.sal from employee e,salarygrade s where s.name='grade3' and e.sal between s.lowsalary and s.highsalary ;