1. Add Column Age and Desg in Emp Prof Table

TYBCOM

MySQL Practical-3

(This practical is depend on MySQl Practical No-2 so please solve 2nd question of MySQl Practical No-2 and then follow this practical.)

1. Add column age and desg in emp_prof table.

Mysql> use profile;

Mysql>alter table emp_prof add column age int;

Mysql> alter table emp_prof add column desg char(10);

2. Update all records of emp_prof.(desg= actn,officer)

Mysql> use profile;

Mysql>update emp_prof set age=24,desg=’officer’ where emp_id=101;

Mysql>update emp_prof set age=21,desg=’actn’ where emp_id=102;

(follow above queries for other records)

3. Display all records from emp_prof where age is 24 and designation is actn.

Mysql> use profile;

Mysql>select * from emp_prof where age=24 and desg=’actn’;

4. Display all records from emp_prof where age is 24 or designation is actn.

Mysql> use profile;

Mysql>select * from emp_prof where age=24 or desg=’actn’;

5.Display emp_name and age where age is not below 25

Mysql> use profile;

Mysql>select emp_name from emp_prof where not age<25;

6.Display emp_name and age where age between 21 and 25

Mysql> use profile;

Mysql> select emp_name from emp_prof where age between 21 and 25;

7.Display name and age where age is not between 23 and 26.

Mysql> use profile;

Mysql>select emp_name from emp_prof where not age between 23 and 26;

8.Display emp_name and country where reperesenting country (‘india’,’australia’)

Mysql> use profile;

Mysql>select emp_name,country from emp_prof where country in (‘india’,’australia’);

9. Display emp_name from emp_prof where emp_name starting with alphabet s.

Mysql> use profile;

Mysql>select emp_name from emp_prof where emp_name like ‘s%’;

10. Display all records from table cust_prof where fname is ending with alphabet s.

Mysql> use profile;

Mysql>select fname from cust_prof where fname like ‘%s’;

11. Display all records from table cust_prof where fname contains alphabet z.

Mysql> use profile;

Mysql>select * from cust_prof where fname like ‘%z%’;

12. Display all records from table cust_prof where lname contains alphabet ‘is’.

Mysql> use profile;

Mysql>select * from cust_prof where lname like ‘%is%’;

13. Display all records from table cust_prof where uppercase ‘A’ is present in fname.

Mysql> use profile;

Mysql>select * from cust_prof where fname like binary ‘%A%’;

14. Display fname and lname from cust_prof where lowercase ‘t’ is present in fname.

Mysql> use profile;

Mysql>select fname,lname from cust_prof where fname like binary ‘%t%’;

15. Display fname and lname from cust_prof table where 2nd character of fname is ‘a’.(like ‘_a%’)

Mysql> use profile;

Mysql>select fname,lname from cust_prof where fname like ‘_a%’;

16. Display emp_names from emp_prof where 2nd last character of the name is ‘e’. (like ‘%e_’)

Mysql> use profile;

Mysql>select emp_name from emp_prof where emp_name like ‘%e_’;

17. Display emp_name from emp_prof where emp_name has exact 5 charecters. ( like ‘_ _ _ _ _ ‘)

Mysql> use profile;

Mysql>select emp_name from emp_prof where emp_name like ‘_ _ _ _ _’;

18. Display emp_name from emp_prof where name contains ‘s’ first and then ‘i’ somewhere thereafter.

Mysql> use profile;

Mysql>select emp_name from emp_prof where emp_name like ‘s%i%’;

19. Display emp_name from emp_prof where emp_name second character of name is ‘a’ and contains ‘p’ somewhere after thereafter.(like ‘_a%p%’)

Mysql> use profile;

Mysql>select emp_name from emp_prof where emp_name like ‘_a%p%’;

20. Display emp_names from emp_prof where emp_name second character of emp_name is ‘a’ and last character of name is ‘s’. (like ‘_a%s’)

Mysql> use profile;

Mysql>select emp_name from emp_prof where emp_name like ‘_a%s’;