69. Examples - 2

I. Employee Table

   1. List the names of the employee who have a salary less

   than Rs.3000 from employee table

   Q. select ename from employee where basic_sal<3000 br="">

   2. List the employee name, job and department number of

      everyone whose name fall in the alphabetical range 

      'C' to 'L' from employee table.

   Q.

   3. List all the employees whose name starts with the letter

      'K' from employee table.

   Q. select ename from employee where ename like 'K%';

   4. List the employee name working in department D002, D003 

      from employee table.

   Q. select ename from employee where dept_no='D002' 

                                    or dept_no='D003';

   5. List all employee whose name start with 'A' and end with

      'd' from employee table.

   Q. select ename from employee where ename like 'A%d';

   6. List all managers and salesman with salary over 2500 from

      employee table.

   Q. select ename from employee where

        (job='Manager' or job='Sales man') and basic_sal>2500;

   7. Display all the employee names in the ascending order of

      their date of joining from employee table.

   Q. select ename from employee order by hiredate asc;

   8. Display all the employees in alphabetical order from 

      employee table.

   Q. select ename from employee order by ename;

   9. List all employee who where hired during 1999 from

      employee table.

   Q. select ename from employee

            where to_char(hiredate,'yy')=99;

  10. List all employees whose commission is more than Rs.300

      from employee table.

   Q. select ename from employee where comm>300;

  11. Change the basic salary Rs.3000 where basic salary less than 2500 from
      employee table
   Q. update employee set basic_sal=3000 where basic_sal<2500 br="">


   12. Change the basic_sal=3000 where job in clerk from employee table.
   Q. update employee set basic_sal=3000 where job='Clerk';



   13. Change the basic_salary of employee number E004 to Rs. 3500 from 
      employee table.
   Q. update employee set basic_sal=3500 where empno='E0004';

II.Client_mast Table

   1. Find out the names of all the clients from client_mast 

      table.

   Q. select name from client_mast;

   2. Retrieve all the records from client_mast table

   Q. select * from client_mast;

   3. Retrieve all the list of names, address and city of all

      the clients from client_mast

   Q. select name,address,city from client_mast;

   4. List all the clients who are staying in Florida from

      client_mast table

   Q. select name from client_mast where state="Florida";

   5. Change the city of client_no 'B001' from 'Gainesville' to 'Paul Street'
      from client_mast table.
   Q. update client_mast set city='Paul Street' where client_no='B001';




   6. Change the bal_due of client no B005 to Rs.2000 from client_mast table.
   Q. update client_mast set bal_due=2000 where client_no='B005';




   7. Change the name to 'infospace' of client_no B004 in client_mast table.
   Q. update client_mast set name='infospace' where client_no='B004';




   8. Change the client_no 'B004' to 'B009' in the table client_mast.
   Q. update client_mast set client_no='B009' where client_no='B004';
 

III.Dept Table

   1. List the department name which is located in Noida 

      and Rockey Creek from Dept table.

   Q. select dname from depttable where loc='Noida' or

                                        loc='Rocky Creek';

   2. Change the department name to 'sales' from the  DEPT tale where deptno 

      is 'd004'.

   Q. update depttable set dname='sales' where deptno='D004';

IV.salesman_mast table

   1. Change the city of the salesman from 'Jacksonville' to 'Huston' from 

      salesman_mast table.

   Q. update salesman_mast set city='Huston' where city='Jacksonville';

V. product_mast table

   1. Change the description of product number 'PR065' to AC in the 

      product_mast table.

   Q. update product_mast set description='AC' where product_no='PR065';

   2. Change the profit percent of product number 'PR065' to 25% in the

      product_mast table.

   Q. update product_mast set profit_perc=25 where product_no='PR065';

   3. Change the available quantity of product number 'PR065'

      to 120 in the product_mast table.

   Q. update product_mast set qty_available=120 where product_no='PR065';

   4. Change the cost price and selling price of product number 'PR065' to 

      5000 and 6250 in the product_mast table.

   Q. update product_mast set sell_price=5000, cost_price=6250  where

      product_no='PR065';

   5. Change the units where product number 'PR065' to 'pack of 10' in the

      product_mast table.

   Q. update product_mast set units='Pack of 10' where product_no='PR065';