Lab Cycle program 1

Create a table iiibscit with fields (regno, name, aspdotnet, java and rdbms).  Insert 10 records. Write SQL queries

1. a)Create table 

Create table IIIbscit (regno varchar2 (8), name varchar2 (16), aspdotnet number (3), java number (3), rdbms number (3));

1.  b).Insert 10 records and display it

insert into iiibscit(regno,name,aspdotnet,java,rdbms) values (‘c1s77156’,’alagar’,71,69,68);

2.  c)Add new field total with number data type

alter table iiibscit add(total number(3));

3d) Update total field with aspdotnet + java + rdbms

update iiibscit set total= aspdotnet + java + rdbms;

Fe).Find  the largest value from the fields aspdotnet, java and rdbms

select  max(aspdotnet) from iiibscit;

select max(java) from iiibscit;

select max(rdbms) from iiibscit;

5. f) Find the smallest value from the fields aspdotnet, java and rdbms

select min(aspdotnet) from iiibscit;                            

select min(java) from iiibscit;

select min(rdbms) from iiibscit;

6.  g).Find the sum value from the fields java

select sum(java) from iiibscit;

7.  h)Find the average value from the fields rdbms

select avg(rdbms) from iiibscit;

8i)Find number of records in the iiibscit table

select count(*) from iiibscit;

9j)Find the name that start with S

      SELECT name FROM iiibscit

       WHERE name LIKE 'S%'

   k). Find the name that end with y

      SELECT name FROM iiibscit

  WHERE name LIKE '%y'

l).Find the name that has ee in the name

SELECT name FROM iiibscit

  WHERE name LIKE '%ee%'

Lab Cycle Program No: 2

          Create a table employee with fields (empid, ename, salary, department and age).  Insert 10 records. Write SQL queries

   Create table employee (empid number (3), empname varchar2 (16), salary number (10, 2), deparment varchar2 (10), age number (3));

   Insert into employee (empid, ename, salary, department, age) values (100,’kannan’,10,000,’sales’,30);

a)    a)Display the total number of employees

select count (*) from employee;

b)    b)Display the name and age of the oldest employee of each department

   select empname, deparment, age from employee where age in ( select max(age) from employee) group by department;

c)     c)Display the average age of employees of  each department

select department, avg(age) from employee group by department;

d)    d)Display departments and the average salaries

select department, avg(salarsy) from employee group by department;

e)    e)Display the lowest salary in  employee table

        select min(salary) from employee;

f)     f) Display the number of employees working in purchase department

        select count(*) from employee where department =’sales’;

g)    g) Display the highest salary in sales department

       Select max(salary) from employee where department =’sales’;

h)     h) Display the difference between highest and lowest salary

     Select max(salary) – min (salary) from employee