mysql queries
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%’;
- 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’;
Recent Comments