some mysql queries

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%’;

1. 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 2^nd 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 2^nd 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’;