sql with examples

Part 0

Sql :

mysql> select * from works;

+------+--------+-------+-------+

| eid | ename | bname | sal |

+------+--------+-------+-------+

| 1 | ram | sbi | 30000 |

| 2 | shayam | ib | 15000 |

| 3 | mohan | sbi | 13000 |

| 4 | mohan | ib | 18000 |

| 5 | sohan | sbi | 10000 |

| 6 | rohan | ib | 9000 |

| 7 | rohan | sbi | 13500 |

+------+--------+-------+-------+

7 rows in set (0.00 sec)

mysql> select bname,count(*) as count from works group by bname order by count(bname);

+-------+-------+

| bname | count |

+-------+-------+

| ib | 3 |

| sbi | 4 |

+-------+-------+

2 rows in set (0.00 sec)

Find the second highest salary from works.

Select ename from works w1 where 2=(select count(w2.sal) from works w2 where w1.works<=w2.works );

mysql> select ename from works w1 where 2=(select count(w2.sal) from works w2 where w1.sal<=w2.sal);

+-------+

| ename |

+-------+

| mohan |

+-------+

1 row in set (0.00 sec)

Drop table: ///delete

mysql> drop table customer;

Query OK, 0 rows affected (0.72 sec)

To delete a row

mysql> DELETE FROM customer where cname='piki';

Query OK, 1 row affected (0.10 sec)

To insert date value.

mysql> create table sales(bno int,bdate date,cid int,iid int,qsold int);

Query OK, 0 rows affected (1.36 sec)

mysql> INSERT INTO sales(bno,bdate,cid,iid,qsold) values(1,'2017-14-6',20661,13,2);

Create table

CREATE TABLE recipes (
recipe_id INT NOT NULL,
recipe_name VARCHAR(30) NOT NULL,
PRIMARY KEY (recipe_id),
UNIQUE (recipe_name)
);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

To drop a PRIMARY KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Persons

DROP PRIMARY KEY;

Create table with null

mysql> create table booking(hotelno varchar(255),guestno varchar(255),datefrom date,dateto date null,roomno varchar(255));

Query to insert date

insert into booking(hotelno,guestno,datefrom,dateto,roomno) values('fb01','10004','2015-04-04','2015-05-04','601');

RANAME COLUMN

ALTER TABLE TABLE_NAME CHANGE COLUMN1 COL VARCHAR(200);

Part 1

mysql> select * from employee;

+------+--------+--------+------+

+------+--------+--------+------+

| 1 | ram | s1 | c1 |

| 2 | shayam | s2 | c2 |

| 3 | mohan | s3 | c3 |

| 4 | mohan | s4 | c4 |

| 5 | sohan | s4 | c4 |

| 6 | rohan | s5 | c6 |

| 7 | rohan | s6 | c6 |

+------+--------+--------+------+

7 rows in set (0.00 sec)

mysql> select * from bank;

+-------+------+

| bname | city |

+-------+------+

| ib | c2 |

| ib | c3 |

| sbi | c1 |

| sbi | c2 |

| ib | c4 |

| sbi | c5 |

| ib | c6 |

+-------+------+

7 rows in set (0.00 sec)

mysql> select * from works;

+------+--------+-------+-------+

| eid | ename | bname | sal |

+------+--------+-------+-------+

| 1 | ram | sbi | 30000 |

| 2 | shayam | ib | 15000 |

| 3 | mohan | sbi | 13000 |

| 4 | mohan | ib | 18000 |

| 5 | sohan | sbi | 10000 |

| 6 | rohan | ib | 9000 |

| 6 | rohan | sbi | 13500 |

+------+--------+-------+-------+

7 rows in set (0.00 sec)

mysql> select * from manages;

+------+--------+-------+

| eid | ename | mname |

+------+--------+-------+

| 1 | ram | m1 |

| 2 | shayam | m2 |

| 3 | mohan | m3 |

| 4 | mohan | m1 |

| 5 | sohan | m2 |

| 6 | rohan | m3 |

| 7 | rohan | m1 |

+------+--------+-------+

7 rows in set (0.00 sec)

1. Find the names and cities of residence of all employees who work for

State Bank of India.

select employee.ename,employee.city FROM employee INNER JOIN works WHERE employee.eid=works.eid AND employee.ename=works.ename AND bname='sbi';

+-------+------+

| ename | city |

+-------+------+

| ram | c1 |

| mohan | c3 |

| sohan | c4 |

| rohan | c6 |

+-------+------+

4 rows in set (0.00 sec)

2. Find the names, street, address and cities of residence of all employees who

work for State Bank of India and earn more than Rs.14, 000.

mysql> select employee.ename,employee.street,employee.city FROM employee INNER JOIN works where employee.eid=works.eid AND employee.ename=works.ename AND works.sal>14000;

+--------+--------+------+

| ename | street | city |

+--------+--------+------+

| ram | s1 | c1 |

| shayam | s2 | c2 |

| mohan | s3 | c3 |

+--------+--------+------+

3 rows in set (0.00 sec)

3. Find all the employees in the database who live in the same cities as the banks

for which they work.

mysql> select e.ename from employee e,works w,bank b where e.ename=w.ename and e.city=b.city and w.bname=b.bname;

+--------+

| ename |

+--------+

| shayam |

| mohan |

| ram |

| mohan |

| rohan |

+--------+

6 rows in set (0.00 sec)

5. Find all the employees in the database who do not work in State Bank of India.

mysql> select ename from works where bname <> 'sbi';

+--------+

| ename |

+--------+

| shayam |

| mohan |

| rohan |

+--------+

3 rows in set (0.00 sec)

6. Find all the employees in the database who earn more than every employee

of Indian Bank.

mysql> select ename from works w1 where sal>(select sum(sal) from works w2 where w2.bname='ib');

Empty set (0.00 sec)

7. Find all employees who earn more than the average salary of all employees of their

bank.

mysql> select ename from works w1 where sal>(select avg(sal) from works w2 where w1.bname=w2.bname);

+--------+

| ename |

+--------+

| ram |

| shayam |

| mohan |

+--------+

3 rows in set (0.00 sec)

8.Find the bank that has the most employees.

mysql> select bname from works group by bname having count(ename)>=all

-> (select count(ename) from works group by bname);

+-------+

| bname |

+-------+

| sbi |

+-------+

1 row in set (0.00 sec)

9. Find the bank that has the smallest payroll.

mysql> select bname from works group by bname having sum(sal)<=all(select sum(sal) from works group by bname);

+-------+

| bname |

+-------+

| ib |

+-------+

1 row in set (0.00 sec)

10.Find those banks whose employees earn a higher salary, on average, than the average salary at State Bank of India.

mysql> select bname from works group by bname having avg(sal) >(select avg(sal) from works where bname='sbi');

Empty set (0.00 sec)

mysql> select * from employee;

+------+--------+--------+------+

+------+--------+--------+------+

| 1 | ram | s1 | c1 |

| 2 | shayam | s2 | c2 |

| 3 | mohan | s3 | c3 |

| 4 | mohan | s4 | c4 |

| 5 | sohan | s4 | c4 |

| 6 | rohan | s5 | c6 |

| 7 | rohan | s6 | c6 |

| 8 | m1 | s1 | c1 |

| 9 | m2 | s2 | c2 |

| 9 | m3 | s3 | c3 |

+------+--------+--------+------+

10 rows in set (0.00 sec)

4. Find all the employees in the database who live in the same cities and on the

same streets as do their managers.

mysql> select P.ename from employee P, employee R, manages M where P.ename = M.ename and M.mname = R.ename and P.street = R.street and P.city = R.city;

+--------+

| ename |

+--------+

| ram |

| shayam |

| mohan |

+--------+

3 rows in set (0.00 sec)

11. Find the number of employees working in each bank.

mysql> select count(*) as count,works.bname from works group by works.bname order by count(works.bname);

+-------+-------+

| count | bname |

+-------+-------+

| 3 | ib |

| 4 | sbi |

+-------+-------+

2 rows in set (0.00 sec)

2. Consider the following relations:

Student(sno: integer, sname: string, major: string, level: string, age:integer)

Class (cname: string, room: integer, fid: integer)

Enrolled (sno: integer, cname: string)

Faculty (fid: integer, fname: string, deptid: integer)

The meaning of these relations is straightforward; for example,

Enrolled has one record per student-class pair such that the student is enrolled in the class.

Write the following queries in SQL. No duplicates should be printed in any of the answers.

1. Find the names of all students (Level=Third Year) who are enrolled in a class

taught by fname=’aaa’.

2. Find the age of the oldest student who is either a CSE major or is enrolled in

a course taught by ‘aaa’.

3. Find the names of all classes that either meet in room R128 or have five or

more students enrolled.

4. Find the names of faculty members who teach in every room in which some class

is taught.

5. Find the names of faculty members for whom the combined enrollment of the

courses that they teach is less than two.

6. Print the level and the average age of students for that level, for each level.

7. Print the level and the average age of students for that level, for all levels except

Third Year.

8. Find the names of students who are enrolled in the maximum number of classes.

9. Find the names of students who are not enrolled in any class.

10. Find the number of students who are enrolled for classes whose age is less

than 20,for each level.

1.Find the names of all students (Level=Third Year) who are enrolled in a class

taught by fname=’aaa’.

mysql> select * from stu;

+-----+-------+-------+-------------+------+

+-----+-------+-------+-------------+------+

| 111 | ram | 1 | third_year | 22 |

| 112 | ram | 2 | second_year | 23 |

| 113 | rama | 3 | third_year | 23 |

| 114 | rama1 | cse | third_year | 19 |

+-----+-------+-------+-------------+------+

4 rows in set (0.00 sec)

mysql> select * from class;

+---------+------+------+

| cname | room | fid |

+---------+------+------+

| math | R128 | aaa |

| science | R129 | bbb |

+---------+------+------+

2 rows in set (0.00 sec)

mysql> select * from faculty;

+------+-------+--------+

| fid | fname | deptid |

+------+-------+--------+

| aaa | aaa | 1123 |

| bbb | bbb | 2345 |

+------+-------+--------+

2 rows in set (0.01 sec)

mysql> select * from enrolled;

+------+---------+

| sno | cname |

+------+---------+

| 111 | math |

| 112 | science |

+------+---------+

2 rows in set (0.00 sec)

mysql> select s1.sname from stu s1,class c1,enrolled e1 where s1.sno=e1.sno and c1.cname=e1.cname and s1.level='third_year';

+-------+

| sname |

+-------+

| ram |

+-------+

1 row in set (0.00 sec)

2. Find the age of the oldest student who is either a CSE major or is enrolled in

a course taught by ‘aaa’.

mysql> select * from stu;

+-----+-------+-------+-------------+------+

+-----+-------+-------+-------------+------+

| 111 | ram | 1 | third_year | 22 |

| 112 | ram | 2 | second_year | 23 |

| 113 | rama | 3 | third_year | 23 |

| 114 | rama1 | cse | third_year | 19 |

+-----+-------+-------+-------------+------+

4 rows in set (0.00 sec)

mysql> select * from class;

+---------+------+------+

| cname | room | fid |

+---------+------+------+

| math | R128 | aaa |

| science | R129 | bbb |

| cse | R128 | aaa |

+---------+------+------+

3 rows in set (0.00 sec)

mysql> select * from faculty;

+------+-------+--------+

| fid | fname | deptid |

+------+-------+--------+

| aaa | aaa | 1123 |

| bbb | bbb | 2345 |

+------+-------+--------+

2 rows in set (0.00 sec)

mysql> select * from enrolled;

+------+---------+

| sno | cname |

+------+---------+

| 111 | math |

| 112 | science |

| 111 | cse |

| 112 | cse |

+------+---------+

4 rows in set (0.00 sec)

mysql> select max(s1.age) from stu s1,enrolled e1,class c1 where s1.sno=e1.sno and c1.cname=e1.cname and (e1.cname='cse' or c1.fid='aaa');

+-------------+

| max(s1.age) |

+-------------+

| 23 |

+-------------+

1 row in set (0.00 sec)

3. Find the names of all classes that either meet in room R128 or have five or

more students enrolled.

mysql> select cname,count(cname) as number from enrolled group by cname;

+---------+--------+

| cname | number |

+---------+--------+

| cse | 2 |

| math | 1 |

| science | 1 |

+---------+--------+

3 rows in set (0.00 sec)

mysql> create view view4 as select cname,count(cname) as number from enrolled group by cname;

Query OK, 0 rows affected (0.05 sec)

mysql> select distinct(c1.cname) from class c1,enrolled e1 where (c1.room='r128' and c1.cname=e1.cname) or c1.cname in (select cname from view4 where number >5);

+-------+

| cname |

+-------+

| math |

| cse |

+-------+

2 rows in set (0.00 sec)

Part 2

1. Customer(Cust id : integer, cust_name: string)

Item(item_id: integer, item_name: string, price: integer)

Sales(bill_no: integer, bill_date: date, cust_id: integer,item_id: integer, qty_sold:

For the above schema, perform the following

1. Create the tables with the appropriate integrity constraints and insert around 5 records

in each of the tables.

2. List all the bills for the current date with the customer names and item_id.

3. List the details of the customer who have bought a product which has a price >200.

4. Give a count of how many products have been bought by each customer.

5. Give a list of products bought by a customer having cust_id as 5.

6. List the item details which are sold as of today.

Solution 1:

mysql> select * from customer;

+------+--------+

| cid | cname |

+------+--------+

| 1 | ram |

| 2 | shayam |

| 3 | mohan |

| 4 | sohan |

| 5 | sita |

| 6 | gita |

| 7 | pinki |

| 8 | piki |

+------+--------+

8 rows in set (0.00 sec)

mysql> select * from item;

+------+--------+-------+

| iid | iname | price |

+------+--------+-------+

| 14 | soap | 2000 |

| 15 | bike | 50000 |

| 16 | iphone | 79000 |

| 17 | laptop | 40000 |

| 18 | cloth | 4000 |

| 18 | PEN | 50 |

+------+--------+-------+

6 rows in set (0.00 sec)

mysql> select * from sales;

+------+------------+------+------+-------+

| bno | bdate | cid | iid | qsold |

+------+------------+------+------+-------+

| 2 | 2017-12-06 | 3 | 14 | 1 |

| 1 | 2017-12-07 | 5 | 14 | 1 |

| 3 | 2017-12-08 | 7 | 15 | 1 |

| 4 | 2018-01-01 | 7 | 16 | 1 |

| 5 | 2018-01-02 | 8 | 18 | 5 |

| 6 | 2018-01-02 | 8 | 17 | 3 |

+------+------------+------+------+-------+

6 rows in set (0.00 sec)

Solution 2:

mysql> select s.bno,s.bdate,s.cid,s.iid,s.qsold from customer c,sales s where c.cid=s.cid and s.bdate='2018-1-2';

+------+------------+------+------+-------+

| bno | bdate | cid | iid | qsold |

+------+------------+------+------+-------+

| 5 | 2018-01-02 | 8 | 18 | 5 |

| 6 | 2018-01-02 | 8 | 17 | 3 |

+------+------------+------+------+-------+

2 rows in set (0.00 sec)

Solution:4

mysql> select s.cid,count(*) as count from sales s group by s.cid order by sum(s.qsold);

+------+-------+

| cid | count |

+------+-------+

| 3 | 1 |

| 5 | 1 |

| 7 | 2 |

| 8 | 2 |

+------+-------+

4 rows in set (0.10 sec)

Solution 5:

mysql> select i.iname from sales s,item i where s.iid=i.iid and s.cid=5;

+-------+

| iname |

+-------+

| soap |

+-------+

1 row in set (0.00 sec)

Solution 6:

mysql> select i.iname from item i,sales s where s.bdate='2018-1-2' and i.iid=s.iid;

+--------+

| iname |

+--------+

| laptop |

| cloth |

| PEN |

+--------+

3 rows in set (0.00 sec)

2. Student(stud_no: integer, stud_name: string, class: string)

Class(class: string, descrip: string)

Lab(mach_no: integer, Lab_no: integer, description: String)

Allotment(stud_no: integer, mach_no: integer, day_of_week: string)

For the above schema, perform the following

i. Create the tables with the appropriate integrity constraints and insert around 5 records in

each of the tables.

ii. List all the machine allotments with the student names, lab and machine numbers.

iii. Display list of student who has not given any machine.

iv. Give a count of how many machines have been allocated to the „CSIT‟ class.

v. Count for how many machines have been allocated in Lab_no 1 for the day of the week

as “Monday”.

i. Create the tables with the appropriate integrity constraints and insert around 5 records in

each of the tables.

Database changed

mysql> show tables;

+------------------+

| Tables_in_lab2_1 |

+------------------+

| allotment |

| class |

| lab |

| student |

+------------------+

4 rows in set (0.00 sec)

mysql> select * from allotment;

+------+------+-----------+

| sno | mno | dow |

+------+------+-----------+

| 1 | 5 | sunday |

| 2 | 6 | monday |

| 3 | 7 | tuesday |

| 4 | 8 | wednesday |

| 5 | 9 | friday |

+------+------+-----------+

5 rows in set (0.07 sec)

mysql> select * from class;

+--------+-------+

| cls | descc |

+--------+-------+

| CSIT | d11 |

| bit | d22 |

| rungta | d33 |

| sankra | d44 |

| nitt | d55 |

+--------+-------+

5 rows in set (0.08 sec)

mysql> select * from lab;

+------+------+------+

| mno | lno | dsc |

+------+------+------+

| 5 | 11 | d1 |

| 6 | 12 | d2 |

| 7 | 13 | d3 |

| 8 | 14 | d4 |

| 9 | 15 | d5 |

+------+------+------+

5 rows in set (0.00 sec)

mysql> select * from student;

+------+--------+--------+

| sno | sname | cls |

+------+--------+--------+

| 1 | ram | CSIT |

| 2 | shayam | CSIT |

| 3 | mohan | bit |

| 4 | sohan | bit |

| 5 | pinki | sankra |

| 6 | piki | CSIT |

| 7 | miki | bit |

+------+--------+--------+

7 rows in set (0.00 sec)

ii. List all the machine allotments with the student names, lab and machine numbers.

mysql> select s1.sname,l1.mno,l1.lno from allotment a1,lab l1,student s1 where

-> a1.mno=l1.mno and a1.sno=s1.sno;

+--------+------+------+

| sname | mno | lno |

+--------+------+------+

| ram | 5 | 11 |

| shayam | 6 | 12 |

| mohan | 7 | 13 |

| sohan | 8 | 14 |

| pinki | 9 | 15 |

+--------+------+------+

5 rows in set (0.00 sec)

iii. Display list of student who has not given any machine.

mysql> select s2.sno,s2.sname,s2.cls from student s2 where s2.sno not in(select s1.sno from allotment a1,student s1 where a1.sno=s1.sno);

+------+-------+------+

| sno | sname | cls |

+------+-------+------+

| 6 | piki | CSIT |

| 7 | miki | bit |

+------+-------+------+

2 rows in set (0.00 sec)

iv. Give a count of how many machines have been allocated to the „CSIT‟ class.

mysql> select count(*) as count from lab l1,allotment a1,student s1 where l1.mno=a1.mno and a1.sno=s1.sno and s1.cls='CSIT';

+-------+

| count |

+-------+

| 2 |

+-------+

1 row in set (0.07 sec)

v. Count for how many machines have been allocated in Lab_no 1 for the day of the week

as “Monday”.

mysql> select count(*) as count from lab l1,allotment a1 where l1.mno=a1.mno

-> and a1.dow='monday';

+-------+

| count |

+-------+

| 1 |

+-------+

3. employee(emp_id : integer, emp_name: string)

department(dept_id: integer, dept_name:string)

paydetails(emp_id : integer, dept_id: integer, basic: integer, deductions: integer,

additions: integer, DOJ: date)

payroll(emp_id : integer, pay_date: date)

For the above schema, perform the following

i. Create the tables with the appropriate integrity constraints and insert around 10 records

in each of the tables.

ii. List the employee details department wise.

iii. List the details of employees whose basic salary is between 10,000 and 20,000.

iv. Give a count of how many employees are working in each department.

v. Give a names of the employees whose netsalary>10,000.

vi. Create a trigger to performs an update operation on an employee table, and

simultaneously create a new table that stores the old value of an employee details before

Updation.

i. Create the tables with the appropriate integrity constraints and insert around 10 records

in each of the tables.

mysql> show tables

-> ;

+------------------+

| Tables_in_lab2_2 |

+------------------+

| department |

| employee |

| paydetail |

| payroll |

+------------------+

4 rows in set (0.00 sec)

mysql> select * from department;

+---------+-----------+

| dept_id | dept_name |

+---------+-----------+

| 11 | cse |

| 12 | it |

| 13 | mech |

| 14 | ec |

| 15 | ee |

| 16 | etc |

| 17 | civil |

| 18 | met |

| 19 | mgmt |

| 20 | prod |

+---------+-----------+

10 rows in set (0.00 sec)

mysql> select * from employee;

+--------+----------+

| emp_id | emp_name |

+--------+----------+

| 1 | ram |

| 2 | shayam |

| 3 | mohan |

| 4 | sohan |

| 5 | pinki |

| 6 | piki |

| 7 | dinesh |

| 8 | mahesh |

| 9 | suresh |

| 10 | dikesh |

+--------+----------+

10 rows in set (0.00 sec)

mysql> select * from paydetail;

+--------+---------+-------+------------+-----------+------------+

+--------+---------+-------+------------+-----------+------------+

| 1 | 11 | 30000 | 2000 | 5000 | 2015-12-01 |

| 2 | 11 | 40000 | 3000 | 8000 | 2014-07-03 |

| 3 | 13 | 20000 | 1000 | 3000 | 2015-07-03 |

| 4 | 13 | 8000 | 600 | 700 | 2017-07-03 |

| 5 | 14 | 12000 | 6000 | 7000 | 2017-07-03 |

| 6 | 15 | 18000 | 600 | 750 | 2017-07-03 |

| 7 | 15 | 70000 | 6000 | 7500 | 2011-12-29 |

| 8 | 19 | 16000 | 6000 | 7500 | 2016-12-13 |

| 9 | 20 | 6000 | 6000 | 7500 | 2018-01-13 |

| 10 | 17 | 11600 | 6000 | 7500 | 2017-05-13 |

| 1 | 16 | 44600 | 5000 | 7500 | 2015-05-13 |

+--------+---------+-------+------------+-----------+------------+

11 rows in set (0.00 sec)

mysql> select * from payroll;

+--------+------------+

| emp_id | pay_date |

+--------+------------+

| 1 | 2018-01-01 |

| 2 | 2018-12-04 |

| 3 | 2018-07-08 |

| 4 | 2018-05-15 |

| 5 | 2018-11-23 |

| 6 | 2018-11-23 |

| 7 | 2018-10-19 |

| 8 | 2018-04-20 |

+--------+------------+

8 rows in set (0.00 sec)

ii. List the employee details department wise.

mysql> select e1.emp_id,e1.emp_name,d1.dept_id,d1.dept_name from employee e1,department d1,paydetail p1 where e1.emp_id=p1.emp_id and d1.dept_id=p1.dept_id order by d1.dept_id;

+--------+----------+---------+-----------+

+--------+----------+---------+-----------+

| 1 | ram | 11 | cse |

| 2 | shayam | 11 | cse |

| 3 | mohan | 13 | mech |

| 4 | sohan | 13 | mech |

| 5 | pinki | 14 | ec |

| 6 | piki | 15 | ee |

| 7 | dinesh | 15 | ee |

| 1 | ram | 16 | etc |

| 10 | dikesh | 17 | civil |

| 8 | mahesh | 19 | mgmt |

| 9 | suresh | 20 | prod |

+--------+----------+---------+-----------+

11 rows in set (0.00 sec)

iii. List the details of employees whose basic salary is between 10,000 and 20,000.

mysql> select e1.emp_id,e1.emp_name,d1.dept_id,d1.dept_name,p1.basic from employee e1,department d1,paydetail p1 where e1.emp_id=p1.emp_id and d1.dept_id=p1.dept_id and p1.basic between 10000 and 2000;

+--------+----------+---------+-----------+-------+

+--------+----------+---------+-----------+-------+

| 3 | mohan | 13 | mech | 20000 |

| 5 | pinki | 14 | ec | 12000 |

| 6 | piki | 15 | ee | 18000 |

| 10 | dikesh | 17 | civil | 11600 |

| 8 | mahesh | 19 | mgmt | 16000 |

+--------+----------+---------+-----------+-------+

5 rows in set (0.00 sec)

iv. Give a count of how many employees are working in each department.

mysql> select p1.dept_id,count(*) no_of_employee from paydetail p1

-> group by p1.dept_id;

+---------+----------------+

| dept_id | no_of_employee |

+---------+----------------+

| 11 | 2 |

| 13 | 2 |

| 14 | 1 |

| 15 | 2 |

| 16 | 1 |

| 17 | 1 |

| 19 | 1 |

| 20 | 1 |

+---------+----------------+

8 rows in set (0.00 sec)

Note:

mysql> select p1.dept_id,count(*) no_of_employee from paydetail p1 group by p1.dept_id order by p1.dept_id desc;

+---------+----------------+

| dept_id | no_of_employee |

+---------+----------------+

| 20 | 1 |

| 19 | 1 |

| 17 | 1 |

| 16 | 1 |

| 15 | 2 |

| 14 | 1 |

| 13 | 2 |

| 11 | 2 |

+---------+----------------+

8 rows in set (0.00 sec)

mysql> select p1.dept_id,count(*) no_of_employee from paydetail p1 group by p1.dept_id order by no_of_employee desc;

+---------+----------------+

| dept_id | no_of_employee |

+---------+----------------+

| 13 | 2 |

| 15 | 2 |

| 11 | 2 |

| 14 | 1 |

| 19 | 1 |

| 20 | 1 |

| 17 | 1 |

| 16 | 1 |

+---------+----------------+

8 rows in set (0.00 sec)

v. Give a names of the employees whose netsalary>10,000.

mysql> select e1.emp_id, p1.basic - p1.deductions + p1.additions as arta from employee e1,paydetail p1 where e1.emp_id=p1.emp_id and p1.basic - p1.deductions + p1.additions>10000;

+--------+-------+

| emp_id | arta |

+--------+-------+

| 1 | 33000 |

| 2 | 45000 |

| 3 | 22000 |

| 5 | 13000 |

| 6 | 18150 |

| 7 | 71500 |

| 8 | 17500 |

| 10 | 13100 |

| 1 | 47100 |

+--------+-------+

9 rows in set (0.00 sec)

//problem here focus on emp_id “1”

mysql> create view v1 as select e1.emp_id, p1.basic - p1.deductions + p1.additions as arta from employee

Query OK, 0 rows affected (0.06 sec)

mysql> select * from v1;

+--------+-------+

| emp_id | arta |

+--------+-------+

| 1 | 33000 |

| 2 | 45000 |

| 3 | 22000 |

| 5 | 13000 |

| 6 | 18150 |

| 7 | 71500 |

| 8 | 17500 |

| 10 | 13100 |

| 1 | 47100 |

+--------+-------+

9 rows in set (0.00 sec)

mysql> select emp_id,sum(arta) from v1 group by emp_id;

+--------+-----------+

| emp_id | sum(arta) |

+--------+-----------+

| 1 | 80100 |

| 2 | 45000 |

| 3 | 22000 |

| 5 | 13000 |

| 6 | 18150 |

| 7 | 71500 |

| 8 | 17500 |

| 10 | 13100 |

+--------+-----------+

8 rows in set (0.00 sec)

Part 3

question->

The following tables form part of a database held in a relational DBMS:

Hotel (HotelNo, Name, City)

Room (RoomNo, HotelNo, Type, Price)

Booking (HotelNo, GuestNo, DateFrom, DateTo, RoomNo)

Guest (GuestNo, GuestName, GuestAddress)

where Hotel contains hotel details and HotelNo is the primary key

Room contains room details for each hotel and (HotelNo, RoomNo) forms the

primary key

Booking contains details of the bookings and the primary key comprises

(HotelNo, GuestNo and DateFrom)

Guest contains guest details and GuestNo is the primary key.

The sample data for the relation is as follows, populate your tables using these data.

1.Using the CREATE TABLE statement, create the Hotel, Room, Booking

and Guest tables.

2. Insert records into each of these tables.

3. Update the price of all rooms by 5%.

4. List all double or family rooms with a price below £40.00 per night, in

ascending order of price.

5. List the bookings for which no date_to has been specified.

6. Write a procedure to display the total revenue per night from all double

rooms?

7. Write a function to display how many different guests have made

bookings for August?

8. What is the total income from bookings for the Grosvenor Hotel today?

9. What is the lost income from unoccupied rooms at the Grosvenor Hotel?

10.List the number of rooms in each hotel in London.

11.What is the most commonly booked room type for each hotel in London?

12.Create a separate table with the same structure as the Booking table to

hold archive records. Using the INSERT statement, copy the records from

the Booking table to the archive table relating to bookings before 1st

January 2008. Delete all bookings before 1st January 2008 from the

Booking table.

13.Write a trigger to archive_Table_History table, reflecting the changes

made every time a row of data is i/d/u in the archive_Table_history.

mysql> select * FROM booking;

+---------+---------+------------+------------+--------+

+---------+---------+------------+------------+--------+

| fb01 | 10001 | 2004-04-01 | 2008-04-08 | 501 |

| fb01 | 10004 | 2004-04-15 | 2004-05-15 | 601 |

| fb01 | 10005 | 2004-05-02 | 2004-05-07 | 501 |

| fb01 | 10002 | 2016-05-04 | 2004-05-29 | 601 |

| fb01 | 10001 | 2004-05-01 | NULL | 701 |

| fb02 | 10005 | 2004-05-12 | 2030-05-04 | 1101 |

| ch01 | 10006 | 2004-04-21 | NULL | 1101 |

| ch02 | 10002 | 2004-04-25 | 2004-04-06 | 801 |

| ch02 | 10002 | 2004-04-25 | 2004-05-06 | 801 |

| dc01 | 10007 | 2004-05-13 | 2004-05-15 | 1001 |

| dc01 | 10003 | 2004-05-20 | NULL | 1001 |

+---------+---------+------------+------------+--------+

11 rows in set (0.00 sec)

mysql> select * from room;

+--------+---------+--------+---------+

+--------+---------+--------+---------+

| 501 | fb01 | single | 19.95 |

| 601 | fb01 | double | 30.45 |

| 701 | fb01 | family | 40.95 |

| 1001 | fb02 | single | 60.9 |

| 1101 | fb02 | double | 90.3 |

| 1001 | ch01 | single | 31.4895 |

| 1101 | ch01 | family | 62.9895 |

| 701 | ch02 | single | 10.5 |

| 801 | ch02 | double | 15.75 |

| 901 | dc02 | single | 18.9 |

| 1001 | dc01 | double | 31.5 |

| 1101 | dc01 | family | 36.75 |

+--------+---------+--------+---------+

12 rows in set (0.00 sec)

mysql> select * from hostel;

ERROR 1146 (42S02): Table 'lab3.hostel' doesn't exist

mysql> select * from hotel;

+---------+---------------+--------+

| hotelno | name | city |

+---------+---------------+--------+

| fb01 | grosvenor | london |

| fb02 | watergate | paris |

| ch01 | omni shoreham | london |

| ch02 | phoenix park | london |

| dc01 | latham | berlin |

+---------+---------------+--------+

5 rows in set (0.01 sec)

3. Update the price of all rooms by 5%.

mysql> update room set price=1.05*price;

Query OK, 12 rows affected (0.00 sec)

Rows matched: 12 Changed: 12 Warnings: 0

4. List all double or family rooms with a price below £40.00 per night, in

ascending order of price.

mysql> select * from room where (type="double" || type="family") and price<40 order by price asc;

+--------+---------+--------+---------+

+--------+---------+--------+---------+

| 801 | ch02 | double | 16.5375 |

| 601 | fb01 | double | 31.9725 |

| 1001 | dc01 | double | 33.075 |

| 1101 | dc01 | family | 38.5875 |

+--------+---------+--------+---------+

4 rows in set (0.00 sec)

5.List the bookings for which no date_to has been specified.

mysql> select * from booking where dateto is not null;

+---------+---------+------------+------------+--------+

+---------+---------+------------+------------+--------+

| fb01 | 10001 | 2004-04-01 | 2008-04-08 | 501 |

| fb01 | 10004 | 2004-04-15 | 2004-05-15 | 601 |

| fb01 | 10005 | 2004-05-02 | 2004-05-07 | 501 |

| fb01 | 10002 | 2016-05-04 | 2004-05-29 | 601 |

| fb02 | 10005 | 2004-05-12 | 2030-05-04 | 1101 |

| ch02 | 10002 | 2004-04-25 | 2004-04-06 | 801 |

| ch02 | 10002 | 2004-04-25 | 2004-05-06 | 801 |

| dc01 | 10007 | 2004-05-13 | 2004-05-15 | 1001 |

+---------+---------+------------+------------+--------+

8 rows in set (0.00 sec)

6. Write a procedure to display the total revenue per night from all double

Rooms?

mysql> delimiter //

mysql> create procedure pr6()

-> begin

-> select sum(price) as total_revenue from room where type='double';

-> end //

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> call pr6();

+---------------+

| total_revenue |

+---------------+

| 176.4 |

+---------------+

1 row in set (0.00 sec)

7. Write a function to display how many different guests have made

bookings for August?

mysql> delimiter //

mysql> create function fun7_1()

-> returns varchar(20) deterministic

-> begin

-> declare no_of_guest varchar(20);

-> set no_of_guest=0;

-> select count(guestno) into no_of_guest from booking where datefrom >= '0000-08-00' and dateto <= CurDATE();

-> return no_of_guest;

-> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select fun7_1();

+----------+

| fun7_1() |

+----------+

| 7 |

+----------+

1 row in set (0.00 sec)

8. What is the total income from bookings for the Grosvenor Hotel today?

mysql> select sum(room.price) from room,hotel,booking where room.hotelno=hotel.hotelno and hotel.hotelno=booking.hotelno and hotel.name='grosvenor' and booking.datefrom >=CurDATE() and booking.dateto <=CurDATE();

+-----------------+

| sum(room.price) |

+-----------------+

| NULL |

+-----------------+

1 row in set (0.00 sec)

9. What is the lost income from unoccupied rooms at the Grosvenor Hotel?

mysql> SELECT SUM(room.price) FROM hotel, room, booking WHERE booking.dateFrom <= '2004-04-17' && booking.DateTo >= '2004-04-17' && booking.hotelno = hotel.hotelno && room.roomno = booking.roomno && hotel.name = 'grosvenor';

+-----------------+

| SUM(room.price) |

+-----------------+

| 52.92 |

+-----------------+

1 row in set (0.00 sec)

10.List the number of rooms in each hotel in London.

mysql> select count(room.roomno) from room,hotel where hotel.hotelno=room.hotelno and hotel.city='london';

+--------------------+

| count(room.roomno) |

+--------------------+

| 7 |

+--------------------+

1 row in set (0.00 sec)

11.What is the most commonly booked room type for each hotel in London?

mysql> create view view3 as select room.type,hotel.name from room,hotel where hotel.hotelno=room.hotelno and hotel.city='london' ;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from view3;

+--------+---------------+

| type | name |

+--------+---------------+

| single | grosvenor |

| double | grosvenor |

| family | grosvenor |

| single | omni shoreham |

| family | omni shoreham |

| single | phoenix park |

| double | phoenix park |

+--------+---------------+

7 rows in set (0.00 sec)

mysql> select max(type),name from view3 group by name;

+-----------+---------------+

| max(type) | name |

+-----------+---------------+

| single | grosvenor |

| single | omni shoreham |

| single | phoenix park |

+-----------+---------------+

3 rows in set (0.00 sec)

Part 4

Working with Procedures & Functions in MySQL

Consider the Employee (EmpNo, EmpName, Sex, Salary, Address, DeptNo)

Department (DeptNo, DeptName, Location)

1. Create a procedure to display the details of an employee record form employee table for a

given employee number.

mysql> DELIMITER //

mysql> CREATE PROCEDURE Procedure4_1(in en varchar(20))

-> BEGIN

-> SELECT * FROM employee where empno=en;

-> END //

Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;

mysql> CALL Procedure4_1('112');

+-------+---------+------+--------+---------+--------+

+-------+---------+------+--------+---------+--------+

| 112 | ram | male | 5000 | londan | cse |

+-------+---------+------+--------+---------+--------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

2. Create a procedure to add details of a new employee into employee table.

mysql> DELIMITER // mysql> create procedure procedure4_22(in empno varchar(20),in empname varchar(20),in sex varchar(20),in salary varchar(20),in address varchar(20),in deptno varchar(20))

-> begin

-> insert into employee values(empno,empname,sex,salary,address,deptno);

-> end //

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> call procedure4_22('118','riya','male','1600','patna','cse');

Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM employee;

+-------+---------+------+--------+---------+--------------+

+-------+---------+------+--------+---------+--------------+

| 112 | ram | male | 5000 | londan | cse |

| 113 | shayam | male | 8000 | dubai | cse |

| 118 | riya | male | 1600 | patna | cse |

+-------+---------+------+--------+---------+--------------+

6 rows in set (0.00 sec)

3. Write a procedure raise_sal which increases the salary of an employee. It accepts an

employee number and salary increase amount. It uses the employee number to find the

current salary from the EMPLOYEE table and update the salary.

mysql> delimiter //

mysql> create procedure procedure4_3(in eno varchar(20),in inc varchar(20))

-> begin

-> update employee set salary=salary + inc where empno=eno;

-> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call procedure4_3('118','20');

Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM employee;

+-------+---------+------+--------+---------+--------------+

+-------+---------+------+--------+---------+--------------+

| 112 | ram | male | 5000 | londan | cse |

| 113 | shayam | male | 8000 | dubai | cse |

| 118 | riya | male | 1620 | patna | cse |

+-------+---------+------+--------+---------+--------------+

6 rows in set (0.01 sec)

4. Create a procedure to delete a record form employee table for a given employee name.

mysql> delimiter //

mysql> create procedure procedure4_4(in name varchar(20))

-> begin

-> delete from employee where empname=name;

-> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call procedure4_4('sohan');

Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM employee;

+-------+---------+------+--------+---------+--------------+

+-------+---------+------+--------+---------+--------------+

| 112 | ram | male | 5000 | londan | cse |

| 113 | shayam | male | 8000 | dubai | cse |

| 118 | riya | male | 1620 | patna | cse |

+-------+---------+------+--------+---------+--------------+

5 rows in set (0.00 sec)

5. Write a function to display maximum salary of employees from the employee table.

mysql> delimiter //

mysql> create function fin()

-> returns varchar(20) deterministic

-> begin

-> declare x varchar(20);

-> set x=0;

-> select max(sal) into x from employee;

-> return x;

-> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select fin();

+-------+

| fin() |

+-------+

| 60000 |

+-------+

1 row in set (0.00 sec)

6. Write a function to display the number of employees working in the Organization.

mysql> delimiter //

mysql> create function function6()

-> returns varchar(20) deterministic

-> begin

-> declare no_of_employee varchar(20);

-> set no_of_employee=0;

-> select count(empname) into no_of_employee from employee;

-> return no_of_employee;

-> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select function6();

+-------------+

| function6() |

+-------------+

| 5 |

+-------------+

1 row in set (0.00 sec)

mysql> select * from employee;

+-------+---------+------+--------+---------+--------------+

+-------+---------+------+--------+---------+--------------+

| 112 | ram | male | 5000 | londan | cse |

| 113 | shayam | male | 8000 | dubai | cse |

| 118 | riya | male | 1620 | patna | cse |

+-------+---------+------+--------+---------+--------------+

5 rows in set (0.00 sec)

7. Write a function to display salary of an employee with the given employee number.

mysql> delimiter //

mysql> create function function7_1(emp_sal varchar(20))

-> returns varchar(20) deterministic

-> begin

-> declare emp_s varchar(20);

-> set emp_s=0;

-> select salary into emp_s from employee where empno=emp_sal;

-> return emp_s;

-> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select function7_1('116');

+--------------------+

| function7_1('116') |

+--------------------+

| 15000 |

+--------------------+

1 row in set (0.03 sec)

8. Write a function average which takes DeptNo as input argument and returns the average

salary received by the employee in the given department.

mysql> delimiter //

mysql> create function function8_1(dno varchar(20))

-> returns varchar(20) deterministic

-> begin

-> declare avg_sal varchar(20);

-> set avg_sal=0;

-> select avg(salary) into avg_sal from employee where deptno=dno;

-> return avg_Sal;

-> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select function8_1('cse');

+--------------------+

| function8_1('cse') |

+--------------------+

| 4873.333333333333 |

+--------------------+

1 row in set (0.00 sec)

9. Write a procedure which takes the DeptNo as input parameter and lists the names of all

employees belonging to that department.

mysql> delimiter //

mysql> create procedure p9(in dno varchar(20))

-> begin

-> select empname from employee where deptno=dno;

-> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call p9('cse');

+---------+

| empname |

+---------+

| ram |

| shayam |

| riya |

+---------+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

10. Write procedure that lists the highest salary drawn by an employee in each of the

departments. It should make use of a named procedure dept_highest which finds the

highest salary drawn by an employee for the given department.

mysql> delimiter //

mysql> create procedure p10_1()

-> begin

-> select max(salary),deptno from employee group by deptno;

-> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call p10_1();

+-------------+--------------+

| max(salary) | deptno |

+-------------+--------------+

| 8000 | cse |

| 15000 | enviromental |

| 13000 | vlsi |

+-------------+--------------+

3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> select * from employee;

+-------+---------+------+--------+---------+--------------+

+-------+---------+------+--------+---------+--------------+

| 112 | ram | male | 5000 | londan | cse |

| 113 | shayam | male | 8000 | dubai | cse |

| 118 | riya | male | 1620 | patna | cse |

+-------+---------+------+--------+---------+--------------+

5 rows in set (0.00 sec)

11. Write a function that will display the number of employees with salary more than 50k.

mysql> insert into employee values('119','pankaj','male','60000','mumbai','electrical');

Query OK, 1 row affected (0.05 sec)

mysql> select * from employee; +-------+---------+------+--------+---------+--------------+

+-------+---------+------+--------+---------+--------------+

| 112 | ram | male | 5000 | londan | cse |

| 113 | shayam | male | 8000 | dubai | cse |

| 118 | riya | male | 1620 | patna | cse |

+-------+---------+------+--------+---------+--------------+

6 rows in set (0.00 sec)

mysql> delimiter //

mysql> create function f11()

-> returns varchar(20) deterministic

-> begin

-> declare no_of_employee varchar(20);

-> set no_of_employee=0;

-> select count(empno) into no_of_employee from employee where salary>50000;

-> return no_of_employee;

-> end //

Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;

mysql> call f11();

ERROR 1305 (42000): PROCEDURE lab4.f11 does not exist

mysql> select f11();

+-------+

| f11() |

+-------+

| 1 |

+-------+

1 row in set (0.00 sec)

12. Write a function that will display the count of the number of employees working in

Chennai.

mysql> select * from employee;

+-------+---------+------+--------+---------+--------------+

+-------+---------+------+--------+---------+--------------+

| 112 | ram | male | 5000 | londan | cse |

| 113 | shayam | male | 8000 | dubai | cse |

| 118 | riya | male | 1620 | patna | cse |

| 120 | raju | male | 12000 | chennai | ece |

+-------+---------+------+--------+---------+--------------+

7 rows in set (0.00 sec)

mysql> delimiter //

mysql> create function f12()

-> returns varchar(20) deterministic

-> begin

-> declare count_emp varchar(20);

-> set count_emp=0;

-> select count(empno) into count_emp from employee where address='chennai';

-> return count_emp; -> end //

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> select f12();

+-------+

| f12() |

+-------+

| 1 |

+-------+

1 row in set (0.01 sec)

Part 5

Working with Triggers

Student Schema

students(Rollno, Lastname, Firstname, Email, classyear, major, phoneno)

1. Create a trigger which will calculate the number of rows we have inserted till now.

mysql> create trigger tr1 after insert on student

-> for each row

-> set @no_of_rows=(select count(rollno) from student);

Query OK, 0 rows affected (0.11 sec)

mysql> select @no_of_rows;

+-------------+

| @no_of_rows |

+-------------+

| NULL |

+-------------+

1 row in set (0.01 sec)

mysql> create trigger tr1 after insert on student

-> for each row

-> set @no_of_rows=(select count(rollno) from student);

Query OK, 0 rows affected (0.11 sec)

mysql> select @no_of_rows;

+-------------+

| @no_of_rows |

+-------------+

| NULL |

+-------------+

1 row in set (0.01 sec)

mysql> select * from student;

+--------+----------+-----------+--------------+-----------+-------+------------+

+--------+----------+-----------+--------------+-----------+-------+------------+

| 111 | kumar | ram | v@gmail.com | 2017 | 1 | 9165570201 |

| 112 | kumar | ram1 | v1@gmail.com | 2017 | 1 | 9165570202 |

| 113 | kumar | ramesh | r1@gmail.com | 2018 | 5 | 9165570204 |

+--------+----------+-----------+--------------+-----------+-------+------------+

3 rows in set (0.00 sec)

2. Create a trigger that displays a message prior to an insert operation on the students table

mysql> DELIMITER //

mysql> create trigger trigger2 before insert on student

-> for each row

-> begin

-> signal sqlstate '02000' set message_text='about to insert';

-> end //

Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;

mysql> insert into student values('114','kumar','mohan','m@gmail.com','2019','3','9165570201');

ERROR 1643 (02000): about to insert

3. Create a Trigger that adds “+91” to all Phone numbers in the students table.

Test and see if the Trigger works properly by inserting and updating some data in the table.

mysql> create trigger tr3 before update on student

-> for each row

-> set new.phoneno=concat('+91',new.phoneno);

Query OK, 0 rows affected (0.13 sec)

mysql> update student set rollno=rollno;

Query OK, 3 rows affected (0.17 sec)

Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from student;

+--------+----------+-----------+--------------+-----------+-------+---------------+

+--------+----------+-----------+--------------+-----------+-------+---------------+

| 111 | kumar | ram | v@gmail.com | 2017 | 1 | +919165570201 |

| 112 | kumar | ram1 | v1@gmail.com | 2017 | 1 | +919165570202 |

| 113 | kumar | ramesh | r1@gmail.com | 2018 | 5 | +919165570204 |

+--------+----------+-----------+--------------+-----------+-------+---------------+

3 rows in set (0.00 sec)

5. Create a trigger that whenever an insert, update, or delete operation occurs on the students

table, a row is added to the studentlog table recording the date, user, and action.

mysql> delimiter //

mysql> create trigger trigger5 before insert on student

-> for each row

-> begin

-> insert into employeelog values(CurDATE(),CURRENT_USER(),'INSERT');

-> end //

Query OK, 0 rows affected (0.22 sec)

mysql> delimiter //

mysql>

mysql> create trigger trigger5_1 before delete on student

-> for each row

-> begin

-> insert into employeelog value(CurDATE(),CURRENT_USER(),'DELETE');

-> end //

Query OK, 0 rows affected (0.14 sec)

mysql> delimiter //

mysql> create trigger trigger5_2 before update on student

-> begin

-> insert into employeelog value(CurDATE(),CURRENT_USER(),'update');

-> end //

mysql> delimiter ;

mysql> insert into student values('114','kumar','rama','v2@gmail.com','2018','2','9165570201');

Query OK, 1 row affected (0.36 sec)

mysql> select * from employeelog;

+------------+----------------+--------+

| op_date | user | action |

+------------+----------------+--------+

| 2018-04-15 | root@localhost | INSERT |

+------------+----------------+--------+

1 row in set (0.00 sec)

mysql> update student set rollno=rollno;

Query OK, 4 rows affected (0.24 sec)

Rows matched: 4 Changed: 4 Warnings: 0

mysql> select * from employeelog;

+------------+----------------+--------+

| op_date | user | action |

+------------+----------------+--------+

| 2018-04-15 | root@localhost | INSERT |

| 2018-04-15 | root@localhost | update |

+------------+----------------+--------+

5 rows in set (0.00 sec)

6. Create a trigger to insert student details into student table only if classyear<2015.

mysql> delimiter //

mysql> create trigger tr6 before insert on student

-> for each row

-> begin

-> if new.classyear>='2016' then

-> signal sqlstate '02000' set message_text='error!';

-> end if;

-> end //

Query OK, 0 rows affected (0.15 sec)

mysql> delimiter ;

mysql> insert into student values('114','kumar','ram','v11@gmail.com','2017','1','9165570201');

ERROR 1643 (02000): error!

mysql> insert into student values('114','kumar','ram','v11@gmail.com','2015','1','9165570201');

Query OK, 1 row affected (0.06 sec)

mysql>

7. Create a trigger to prevent any student named John to be inserted into the table.

mysql> delimiter //

mysql> create trigger tr7 before insert on student

-> for each row

-> begin

-> if new.firstname='john' then

-> signal sqlstate '02000' set message_text='name cant be john';

-> end if;

-> end //

Query OK, 0 rows affected (0.11 sec)

mysql> delimiter ;

mysql> insert into student values('115','kumar','john','v11@gmail.com','2015','1','9165570201');

ERROR 1643 (02000): name cant be john

8. Create a trigger to raise an exception if the rollno is changed.

mysql> delimiter //

mysql> create trigger tr8 before update on student

-> for each row

-> begin

-> if (new.rollno <> old.rollno) then

-> signal sqlstate '02000' set message_text='no possible';

-> end if;

-> end //

Query OK, 0 rows affected (0.12 sec)

mysql> delimiter ;

mysql> update student set rollno='118' where firstname='ram';

ERROR 1643 (02000): no possible

Search This Blog

Vivek kumar Rocky

sql with examples

Comments

Post a Comment

Popular posts from this blog

FRIENDS FOREVER

Mithila Haat 2025

dynamic programming important question