OU Site

RDBMS

1. Create Table Student with the following fields (StudentNumber, StudentName, marks in DBMS , Ecommerce , FIT, WebProgramming)
 i) Insert five records.
ii) calculate total by adding a total column.
iii) calculate average by adding a average column.
 
SQL> create table student(sno number(13) ,sname varchar2(8),dbms number(13),ecommerce number(13),fit number(13),webprogramming number(13));
Table created.
SQL> insert into student values(100,'raju',50,40,30,54);
1 row created.
SQL> insert into student values(101,'ramu',60,80,55,64);
1 row created.
SQL> insert into student values(102,'BALU',40,60,55,60);
1 row created.
SQL> insert into student values(103,'srinu',45,70,66,50);
1 row created.
SQL> insert into student values(104,'kranthi',55,60,55,70);
1 row created.
SQL> select * from student;
       SNO SNAME          DBMS  ECOMMERCE        FIT              WEBPROGRAMMING
---------- -------- ---------- ---------- ---------- ---------------
       100 raju             50         40         30              54
       101 ramu           60         80         55              64
       102 BALU          40         60         55              60
       103 srinu            45         70         66              50
       104 kranthi        55         60         55              70

SQL> alter table student add(total number(3));
Table altered.


SQL> select * from student;


       SNO SNAME          DBMS  ECOMMERCE        FIT WEBPROGRAMMING      TOTAL
---------- -------- ---------- ---------- ---------- --------------- ----------
       100 raju             50         40         30              54
       101 ramu             60         80         55              64
       102 BALU             40         60         55              60
       103 srinu            45         70         66              50
       104 kranthi          55         60         55              70
SQL> update student set total=dbms+fit+ecommerce+webprogramming;
5 rows updated.
SQL> select * from student;
       SNO SNAME          DBMS  ECOMMERCE        FIT WEBPROGRAMMING      TOTAL
---------- -------- ---------- ---------- ---------- --------------- ----------
       100 raju             50         40         30              54        174
       101 ramu             60         80         55              64        259
       102 BALU             40         60         55              60        215
       103 srinu            45         70         66              50        231
       104 kranthi          55         60         55              70        240
SQL> alter table student add(average number(3));
Table altered.
SQL> select * from student;
       SNO SNAME          DBMS  ECOMMERCE        FIT  WEBPROGRAMMING      TOTAL  AVERAGE
-------------------- -------- ---------- ---------- ---------- --------------- ----------
       100 raju                50             40                           30                 54             174
       101 ramu             60              80                          55                 64              259
       102 BALU           40              60                          55                 60             215
       103 srinu              45             70                          66                  50             231
       104 kranthi          55             60                           55                70              240

SQL> update student set average=total/4;
5 rows updated.
SQL> select * from student;

    SNO SNAME     DBMS  ECOM        FIT   WEB-PROG.    TOTAL  AVERAGE
---------- -------- ---------- ---------- ---------- --------------- -------------------
       100 raju                50           40           30             54              174                        44
       101 ramu              60           80           55             64             259                         65
       102 BALU            40           60           55             60             215                         54
       103 srinu              45           70           66              50            231                         58

       104 kranthi          55           60           55              70            240                          60



2. Create Table Student with the following fields (StudentNumber, StudentName,marks in DBMS , Ecommerce, FIT , WP) 
 i) insert five records.
ii) increase 10 marks in dbms for all students who got less than 50 in dbms.
iii) List the details of student in ascending order of their name.
SQL> create table student(sno number(13),sname varchar2(8),dbms number(13),ecommerce number(13),fit number(13),webprogramming number(13));
Table created.
SQL> insert into student values(100,'raju',50,40,30,54);
1 row created.
SQL> insert into student values(101,'ramu',60,80,55,64);
1 row created.
SQL> insert into student values(102,'BALU',40,60,55,60);
1 row created.
SQL> insert into student values(103,'srinu',45,70,66,50);
1 row created.
SQL> insert into student values(104,'kranthi',55,60,55,70);
1 row created.
SQL> select * from student;

       SNO SNAME          DBMS  ECOMMERCE        FIT  WEBPROG
---------- -------- ---------- ---------- ---------- ---------------
       100 raju                          50               40                   30              54
       101 ramu                        60               80                   55              64
       102 BALU                      40               60                   55              60
       103 srinu                        45                70                  66              50
       104 kranthi                    55                60                  55              70
SQL> update student set dbms=dbms+10 where dbms<50;
2 rows updated.
SQL> select * from student;

       SNO SNAME          DBMS  ECOMMERCE        FIT        WEBPROGRAMMING    
---------- -------- ---------- ---------- ---------- --------------- ----------
  
       100 raju                    50                   40                      30              54       
       101 ramu                  60                  80                       55              64      
      102 BALU                 50                  60                       55              60     
      103 srinu                   55                  70                       66              50      
      104 kranthi               55                 60                        55              70      
      
SQL> select * from student order by sname;

       SNO SNAME          DBMS  ECOMMERCE        FIT              WEBPROGRAMMING   
---------- -------- ---------- ---------- ---------- --------------- ----------
  
       102 BALU              50                      60                       55                       60      
       104 kranthi            55                      60                       55                      70      
       100 raju                 50                       40                       30                      54       
       101 ramu               60                       80                       55                      64      

       103 srinu                55                       70                      66                       50       



 3.Create Table Student with the following fields (StudentNumber, StudentName, DBMS , ECommerce, FIT,WP) 
 i) insert 5 records.

ii) calculate percentage by adding a percentage column.
iii) list the student’s name and number who have failed. 
SQL> create table student(sno number(13),sname varchar2(8),dbms number(13),ecommerce number(13),fit number(13),webprogrammingnumber(13));
Table created.
SQL> insert into student values(100,'raju',50,40,30,54);
1 row created.
SQL> insert into student values(101,'ramu',60,80,55,64);
1 row created.
SQL> insert into student values(102,'BALU',40,60,55,60);
1 row created.
SQL> insert into student values(103,'srinu',45,70,66,50);
1 row created.
SQL> insert into student values(104,'kranthi',55,60,55,70);
1 row created.
SQL> select * from student;

  

    SNO SNAME          DBMS  ECOMMERCE        FIT WEB_PROGRAMMING
---------- -------- ---------- ---------- ---------- ---------------
       100 raju             50         40         30              54
       101 ramu             60         80         55              64
       102 BALU             40         60         55              60
       103 srinu            45         70         66              50
       104 kranthi          55         60         55              70

SQL> alter table student add(total number(3));
Table altered.
SQL> select * from student;

       SNO SNAME          DBMS  ECOMMERCE        FIT WEBPROGRAMMING     TOTAL
---------- -------- ---------- ---------- ---------- --------------- ----------
       100 raju             50         40         30              54
       101 ramu             60         80         55              64
       102 BALU             40         60         55              60
       103 srinu            45         70         66              50
       104 kranthi          55         60         55              70
SQL> update student set total=dbms+fit+ecommerce+web_programming;
5 rows updated.
SQL> select * from student;
       SNO SNAME          DBMS  ECOMMERCE        FIT WEBPROGRAMMING     TOTAL
---------- -------- ---------- ---------- ---------- --------------- ----------
       100 raju             50         40         30              54        174
       101 ramu             60         80         55              64        259
       102 BALU             40         60         55              60        215
       103 srinu            45         70         66              50        231
       104 kranthi          55         60         55              70        240

SQL> alter table student add(percentage number(6,2));
Table altered.
SQL> update student set percentage=(total/4)*100;
5 rows updated.
SQL> select * from student;
       SNO SNAME          DBMS  ECOMMERCE        FIT WEBPROGRAMMING     TOTAL                PERCENTAGE
---------- -------- ---------- ---------- ---------- --------------- ------------------- ----------
       100 raju             50         40         30              54        174                          43.50
       101 ramu             60         80         55              64        259        64.75
       102 BALU             50         60         55              60        215       53.75
       103 srinu            55         70         66              50        231                         57.75
       104 kranthi          55         60         55              70        240       60.00
SQL> select sno,sname from student where dbms<36 OR ecommerce<36 OR fit<36 OR web_programming<36;

       SNO SNAME
---------- --------
       100 raju 



5. Create Tableemp with the following fields ( EmpNo , EmpName, Salary , DateOfJoining)EmpNo should be primary key.
i) Insert 5 records.
ii) increase salary of  for all the employees by Rs 1000/- whose salary less than Rs6,000. 
iii) List the employee details with highest salary.
SQL> create table emp(eno number(10)primary key,ename varchar2(15),salary number(10),dateofjoin date);
Table created.
SQL> insert into emp values(101,'raju',10000,'10-dec-16');
1 row created.
SQL> insert into emp values(102,'ravi',5000,'10-nov-16');
1 row created.
SQL> insert into emp values(103,'ramesh',6000,'10-nov-16');
1 row created.
SQL> insert into emp values(104,'rajesh',12000,'10-jun-16');
1 row created.
SQL> insert into emp values(105,'nani',2000,'10-aug-16');
1 row created.

SQL> select * from emp;
       ENO ENAME               SALARY DATEOFJOIN
---------- --------------- ---------- ---------
       101 raju                 10000            10-DEC-16
       102 ravi                  5000              10-NOV-16
       103 ramesh                6000        10-NOV-16
       104 rajesh               12000          10-JUN-16
       105 nani                  2000             10-AUG-16

SQL> update emp set  salary=(salary+1000) where salary<6000;
2 rows updated.
SQL> select * from emp;
       ENO ENAME               SALARY DATEOFJOIN
---------- --------------- ---------- ---------
       101 raju                 10000            10-DEC-16
       102 ravi                  6000              10-NOV-16
       103 ramesh                6000        10-NOV-16
       104 rajesh               12000          10-JUN-16
       105 nani                  3000             10-AUG-16

SQL> select max(salary) from emp;
MAX(SALARY)
-----------

      12000


7. Create table Emp_detailswith the following fields (EmpNo , Name ,Department , Net Salary )
i) Insert Five records
ii) Display all employee details who work in ‘sales’ department.
iii)Calculate annual_salary by adding a column. 
SQL> create table emp1(eno number(10) primary key,ename varchar2(15),department varchar2(15),netsal number(10),jobdesig varchar2(15));
Table created.
SQL> insert into emp1 values(101,'nani','marketing',50000,'supllier');
1 row created.
SQL> insert into emp1 values(102,'rani','account',60000,'admin');
1 row created.
SQL> insert into emp1 values(103,'ravi','sales',50000,'retail');
1 row created.
SQL> insert into emp1 values(104,'raju','marketing',90000,'customer');
1 row created.
SQL> insert into emp1 values(105,'ramesh','hr',70000,'account');
1 row created.
SQL> select *from emp1;
       ENO ENAME           DEPARTMENT          NETSAL JOBDESIG
---------- --------------- --------------- ---------- ---------------
       101 nani            marketing            50000 supllier
       102 rani            account              60000 admin
       103 ravi            sales                50000 retail
       104 raju            marketing            90000 customer
       105 ramesh          hr                   70000 account
SQL> select *from emp1 where department='sales';
       ENO ENAME           DEPARTMENT          NETSAL JOBDESIG
---------- --------------- --------------- ---------- ---------------
       103 ravi            sales                50000 retail
SQL> alter table emp1 add(annualsal varchar2(15));
Table altered.
SQL> update emp1 set  annualsal=(netsal*12);
5 rows updated.
SQL> select *from emp1;
       ENO ENAME           DEPARTMENT          NETSAL JOBDESIG          ANNUALSAL                      
---------- --------------- --------------- ---------- ---------------
       101 nani            marketing            50000 supllier 600000
       102 rani            account              60000 admin                      720000
       103 ravi            sales                50000 retail                             600000
       104 raju            marketing            90000 customer            1080000
       105 ramesh          hr                   70000 account    840000

8. Create table Empwith the following fields (EmpNo (PK), Name ,Department ,Job_Desig, Net Salary )
i) Insert Five records
ii) Display all employee details who Job_desig=Accountant.
iii)Display the names of employee whose annual salary is more than 3 lacs
SQL> create table emp1(eno number(10) primary key,ename varchar2(15),department varchar2(15),netsal number(10),jobdesig varchar2(15));
Table created.
SQL> insert into emp1 values(101,'nani','marketing',50000,'supllier');
1 row created.
SQL> insert into emp1 values(102,'rani','account',60000,'admin');
1 row created.
SQL> insert into emp1 values(103,'ravi','sales',50000,'retail');
1 row created.
SQL> insert into emp1 values(104,'raju','marketing',90000,'customer');
1 row created.
SQL> insert into emp1 values(105,'ramesh','hr',70000,'account');
1 row created.
SQL> select *from emp1;
       ENO ENAME           DEPARTMENT          NETSAL JOBDESIG
---------- --------------- --------------- ---------- ---------------
       101 nani            marketing            50000 supllier
       102 rani            account              60000 admin
       103 ravi            sales                50000 retail
       104 raju            marketing            90000 customer
       105 ramesh          hr                   70000 account

SQL> select *from emp1 where jobdesig='account';

       ENO ENAME           DEPARTMENT          NETSAL JOBDESIG         
---------- --------------- --------------- ---------- ---------------
       105 ramesh          hr                   70000     account               

SQL> alter table emp1 add(annualsal varchar2(15));
Table altered.
SQL> update emp1 set  annualsal=(netsal*12);
5 rows updated.
SQL> select *from emp1;
       ENO ENAME           DEPARTMENT          NETSAL JOBDESIG          ANNUALSAL                      
---------- --------------- --------------- ---------- ---------------
       101 nani            marketing            50000 supllier 600000
       102 rani            account              60000 admin                      720000
       103 ravi            sales                50000 retail                             600000
       104 raju            marketing            90000 customer            1080000
       105 ramesh          hr                   70000 account    840000
SQL> select ename from emp1 where annualsal>300000;
ENAME
---------------
nani
rani
ravi
raju
ramesh

9. Create table Employee with the following fields (EmpNo (PK), Name ,Department ,Job_Desig, Net Salary )
i) Insert Five records
ii)Calculate annual_salary by adding a column . 
iii) Delete all employee whose annual_salary is more than 500000
SQL> create table emp1(eno number(10) primary key,ename varchar2(15),department varchar2(15),netsal number(10),jobdesig varchar2(15));
Table created.
SQL> insert into emp1 values(101,'nani','marketing',50000,'supllier');
1 row created.
SQL> insert into emp1 values(102,'rani','account',60000,'admin');
1 row created.
SQL> insert into emp1 values(103,'ravi','sales',50000,'retail');
1 row created.
SQL> insert into emp1 values(104,'raju','marketing',90000,'customer');
1 row created.
SQL> insert into emp1 values(105,'ramesh','hr',70000,'account');
1 row created.
 SQL> select *from emp1;
       ENO ENAME           DEPARTMENT          NETSAL JOBDESIG
---------- --------------- --------------- ---------- ---------------
       101 nani            marketing            50000 supllier
       102 rani            account              60000 admin
       103 ravi            sales                50000 retail
       104 raju            marketing            90000 customer
       105 ramesh          hr                   70000 account
SQL> alter table emp1 add(annualsal varchar2(15));
Table altered.
SQL> update emp1 set  annualsal=(netsal*12);
5 rows updated.
SQL> select *from emp1;
       ENO ENAME           DEPARTMENT          NETSAL JOBDESIG          ANNUALSAL                      
---------- --------------- --------------- ---------- ---------------
       101 nani            marketing            50000 supllier 600000
       102 rani            account              60000 admin                      720000
       103 ravi            sales                50000 retail                             600000
       104 raju            marketing            90000 customer            1080000
       105 ramesh          hr                   70000 account    840000
SQL> delete from emp1 where annualsal>500000;
5 rows deleted.

 10. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Display supplier details for all suppliers with more than 75000 turnover
iii) Get the details of the supplier who operate from Hyderabad with turnover > 50000
SQL> create table supplier(sno number(12)primary key,sname varchar2(15),scity varchar2(12),turnover number(15));
Table created.
SQL> insert into supplier values(101,'ramesh','hyd',76000);
1 row created.
SQL> insert into supplier values(102,'rajesh','mumbai',50000);
1 row created.
SQL> insert into supplier values(103,'ramu','pune',85000);
1 row created.
SQL> insert into supplier values(104,'ravi','ban',25000);
1 row created.
SQL> insert into supplier values(105,'raju','chennai',20000);
1 row created.
SQL> select * from supplier;

       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       101 ramesh          hyd               76000
       102 rajesh          mumbai            50000
       103 ramu            pune              85000
       104 ravi            ban               25000
       105 raju            chennai            20000

SQL> select * from supplier where turnover>75000;

       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       101 ramesh          hyd               76000
       103 ramu            pune              85000

SQL> select * from supplier where scity='hyd' AND turnover>50000;

       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       101 ramesh          hyd               76000

11. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier table in descending order of turnover
iii)Get the total number of suppliers
iv) Get the supplier details whose turnover is between 25000 and 35000

SQL> create table supplier(sno number(12)primary key,sname varchar2(15),scity varchar2(12),turnover number(15));
Table created.
SQL> insert into supplier values(101,'ramesh','hyd',76000);
1 row created.
SQL> insert into supplier values(102,'rajesh','mumbai',50000);
1 row created.
SQL> insert into supplier values(103,'ramu','pune',85000);
1 row created.
SQL> insert into supplier values(104,'ravi','ban',25000);
1 row created.
SQL> insert into supplier values(105,'raju','chennai',20000);
1 row created.
SQL> insert into supplier values(106,'charan','chennai',30000);
1 row created.
SQL> select * from supplier;
       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       101 ramesh          hyd               76000
       103 ramu            pune              85000
       102 rajesh          mumbai            50000
       104 ravi            ban               25000
       105 raju            chennai            20000
       106 charan          chennai           30000

6 rows selected.
SQL> select * from supplier order by turnover desc;

       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       103 ramu            pune              85000
       101 ramesh          hyd               76000
       102 rajesh          mumbai            50000
       106 charan          chennai           30000
       104 ravi            ban               25000
       105 raju            chennai           20000

6 rows selected.
SQL> select count(*) from supplier;

  COUNT(*)
----------
         6
SQL> select * from supplier where turnover>25000 AND turnover<35000;

       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       106 charan          chennai           30000

12. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier number of supplier who located Hyderabad
iii) Get the names and cities of supplier whose names begin with ‘C’
 SQL> create table supplier(sno number(12)primary key,sname varchar2(15),scity varchar2(12),turnover number(15));
Table created.
SQL> insert into supplier values(101,'ramesh','hyd',76000);
1 row created.
SQL> insert into supplier values(102,'rajesh','mumbai',50000);
1 row created.
SQL> insert into supplier values(103,'ramu','pune',85000);
1 row created.
SQL> insert into supplier values(104,'ravi','ban',25000);
1 row created.
SQL> insert into supplier values(105,'raju','chennai',20000);
1 row created.
SQL> insert into supplier values(106,'charan','chennai',30000);
1 row created.
SQL> select * from supplier;
       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       101 ramesh          hyd               76000
       103 ramu            pune              85000
       102 rajesh          mumbai            50000
       104 ravi            ban                      25000
       105 raju            chennai            20000
       106 charan          chennai           30000

6 rows selected.
SQL> select count(*) from supplier where scity='hyd';

  COUNT(*)
----------
         1
SQL> select sname,scity from supplier where sname like 'c%';

SNAME           SCITY
--------------- ------------
charan          chennai

13. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Increase the turnover by 10% for suppliers who operate from Hyderabad
iii) change the city to ‘Bangalore’ for S_NO 109


SQL> create table supplier(sno number(12)primary key,sname varchar2(15),scity varchar2(12),turnover number(15));
Table created.
SQL> insert into supplier values(101,'ramesh','hyd',76000);
1 row created.
SQL> insert into supplier values(102,'rajesh','mumbai',50000);
1 row created.
SQL> insert into supplier values(103,'ramu','pune',85000);
1 row created.
SQL> insert into supplier values(104,'ravi','ban',25000);
1 row created.
SQL> insert into supplier values(105,'raju','chennai',20000);
1 row created.
SQL> insert into supplier values(109,'charan','chennai',30000);
1 row created.
SQL> select * from supplier;
       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       101 ramesh          hyd               76000
       103 ramu            pune              85000
       102 rajesh          mumbai            50000
       104 ravi            ban                      25000
       105 raju            chennai            20000
       109 charan          chennai           30000
6 rows selected.
SQL> update supplier set turnover=turnover+(turnover*10/100) where scity='hyd';
1 row updated.
SQL> select * from supplier;
       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       101 ramesh          hyd               83600
       102 rajesh          mumbai            50000
       103 ramu            pune              85000
       104 ravi            ban               25000
       105 raju            chennai           20000
       109 charan          chennai           30000
6 rows selected.
SQL> update supplier set scity='bangalore' where sno=109;
1 row updated.
SQL> select * from supplier;
       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       101 ramesh          hyd               83600
       102 rajesh          mumbai            50000
       103 ramu            pune              85000
       104 ravi            ban               25000
       105 raju            chennai           20000
       109 charan          bangalore          30000

14. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii)Display the average turnover by suppliers in the city Hyderabad
iii) Delete all rows from supplier where city is Kolkata
SQL> create table supplier(sno number(12)primary key,sname varchar2(15),scity varchar2(12),turnover number(15));
Table created.
SQL> insert into supplier values(101,'ramesh','hyd',76000);
1 row created.
SQL> insert into supplier values(102,'rajesh','mumbai',50000);
1 row created.
SQL> insert into supplier values(103,'ramu','pune',85000);
1 row created.
SQL> insert into supplier values(104,'ravi','ban',25000);
1 row created.
SQL> insert into supplier values(105,'raju','chennai',20000);
1 row created.
SQL> insert into supplier values(109,'charan','kolkata',30000);
1 row created.
SQL> select * from supplier;
       SNO SNAME           SCITY          TURNOVER
---------- --------------- ------------ ----------
       101 ramesh          hyd               76000
       103 ramu            pune              85000
       102 rajesh          mumbai            50000
       104 ravi            ban                      25000
       105 raju            chennai            20000
       109 charan          kolkata           30000
6 rows selected.
------------------------------------------------------------------------------------------------------------------------------------------

SQL> delete from supplier where scity='kolkata';

1 row deleted.
15. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price, Selling_price)
i) Insert Five records
ii) Calculate profit by adding a column
iii) Display the details of product which Is sold in loss.
SQL> create table product(pno number(5)primary key,pname varchar2(15),cp number(10),sp number(10),quantity number(10));
Table created.
SQL> insert into product values(103,'galaxy',100,200,300);
1 row created.
SQL> insert into product values(104,'trousers',100,2000,90);
1 row created.
SQL> insert into product values(101,'samsung',2000,4000,200);
1 row created.
SQL> insert into product values(102,'karbonn',200,500,300);
1 row created.
SQL> insert into product values(107,'nokia',2000,100,400);
1 row created.
SQL> select *from product;

       PNO PNAME                   CP         SP    QUANTITY
---------- --------------- ---------- ---------- ----------
       103 galaxy                 100        200        300
       104 trousers               100       2000         90
       101 samsung               2000       4000        200
       102 karbonn                200        500        300
       107 nokia                 2000        100        400
SQL> alter table product add profit number(10);
Table altered.
SQL> select *from product;
       PNO PNAME                   CP         SP    QUANTITY     PROFIT
---------- --------------- ---------- ---------- ---------- ----------
       103 galaxy                 100        200        300
       104 trousers               100       2000         90
       101 samsung               2000       4000        200
       102 karbonn                200        500        300
       107 nokia                 2000        100        400
SQL> update product set profit =sp-cp;
5 rows updated.
SQL> select *from product;
       PNO PNAME                   CP         SP    QUANTITY     PROFIT
---------- --------------- ---------- ---------- ---------- ----------
       103 galaxy                 100        200        300        100
       104 trousers               100       2000         90       1900
       101 samsung               2000       4000        200       2000
       102 karbonn                200        500        300        300
       107 nokia                 2000        100        400      -1900
SQL> select * from product where sp<cp;
       PNO PNAME                   CP         SP    QUANTITY     PROFIT
---------- --------------- ---------- ---------- ---------- ----------
       107 nokia                 2000        100        400      -1900

16. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price, Selling_price, Quantity)
i) Insert Five records
ii) Display the product details whose selling_price is double the cost_price
iii) Display the product details whose product_name starts with G and quantity is more then 200
SQL> create table product(pno number(5)primary key,pname varchar2(15),cp number(10),sp number(10),quantity number(10));
Table created.
SQL> insert into product values(103,'galaxy',100,200,300);
1 row created.
SQL> insert into product values(104,'trousers',100,2000,90);
1 row created.
SQL> insert into product values(101,'samsung',2000,4000,200);
1 row created.
SQL> insert into product values(102,'karbonn',200,500,300);
1 row created.
SQL> insert into product values(107,'nokia',2000,100,400);
1 row created.
SQL> select *from product;
       PNO PNAME                   CP         SP    QUANTITY
---------- --------------- ---------- ---------- ----------
       103 galaxy                 100        200        300
       104 trousers               100       2000         90
       101 samsung               2000       4000        200
       102 karbonn                200        500        300
       107 nokia                 2000        100        400
SQL> select * from product where sp=cp*2;
       PNO PNAME                   CP         SP    QUANTITY    
---------- --------------- ---------- ---------- ---------- ----------
       103 galaxy                 100        200        300       
       101 samsung               2000       4000        200      
SQL> select * from product where  pname like 'g%' AND Quantity>200;

       PNO PNAME                   CP         SP    QUANTITY    
---------- --------------- ---------- ---------- ---------- ----------
       103 galaxy                 100        200        300       

17. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price, Selling_price, Quantity)
i) Insert Five records
ii) Increase the cost and selling price for  product_no 107 by 10%
iii) Get product table in descending order on Quantity
iv) Display the total quantity of products available.
SQL> create table product(pno number(5)primary key,pname varchar2(15),cp number(10),sp number(10),quantity number(10));
Table created.
SQL> insert into product values(103,'galaxy',100,200,300);
1 row created.
SQL> insert into product values(104,'trousers',100,2000,90);
1 row created.
SQL> insert into product values(101,'samsung',2000,4000,200);
1 row created.
SQL> insert into product values(102,'karbonn',200,500,300);
1 row created.
SQL> insert into product values(107,'nokia',2000,100,400);
1 row created.
SQL> select *from product;

       PNO PNAME                   CP         SP    QUANTITY
---------- --------------- ---------- ---------- ----------
       103 galaxy                 100        200        300
       104 trousers               100       2000         90
       101 samsung               2000       4000        200
       102 karbonn                200        500        300
       107 nokia                 2000        100        400

SQL> update product set cp=cp+(cp*10/100) ,sp=(sp+sp*10/100) where pno=107;
1 row updated.

SQL> select * from product;

       PNO PNAME                   CP         SP    QUANTITY     
---------- --------------- ---------- ---------- ---------- ----------
       103 galaxy                 100        200        300       
       104 trousers               100       2000         90      
       101 samsung               2000       4000        200      
       102 karbonn                200        500        300       
       107 nokia                 2200        110        400     
SQL> select * from product order by quantity desc;

       PNO PNAME                   CP         SP    QUANTITY    
---------- --------------- ---------- ---------- ---------- ----------
       107 nokia                 2200        110        400     
       103 galaxy                 100        200        300       
       102 karbonn                200        500        300       
       101 samsung               2000       4000        200    
       104 trousers               100       2000         90      
SQL> select count(quantity) from product;

COUNT(QUANTITY)
--------------
             5

18. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price, Selling_price, Quantity)
i) Insert Five records
ii) Change the cost_price of ‘Trousers’ to 1000
iii) delete all rows where quantity is greater than 100

SQL> create table product(pno number(5)primary key,pname varchar2(15),cp number(10),sp number(10),quantity number(10));
Table created.
SQL> insert into product values(103,'galaxy',100,200,300);
1 row created.
SQL> insert into product values(104,'trousers',100,2000,90);
1 row created.
SQL> insert into product values(101,'samsung',2000,4000,200);
1 row created.
SQL> insert into product values(102,'karbonn',200,500,300);
1 row created.
SQL> insert into product values(107,'nokia',2000,100,400);
1 row created.
SQL> select *from product;
       PNO PNAME                   CP         SP    QUANTITY
---------- --------------- ---------- ---------- ----------
       103 galaxy                 100        200        300
       104 trousers               100       2000         90
       101 samsung               2000       4000        200
       102 karbonn                200        500        300
       107 nokia                 2000        100        400
SQL> update product set cp=1000 where pname='trousers';
1 row updated.
SQL> select * from product ;

       PNO PNAME                   CP         SP    QUANTITY   
---------- --------------- ---------- ---------- ---------- ---------
       103 galaxy                 100        200        300       
       104 trousers              1000       2000         90    
       101 samsung               2000       4000        200 
       102 karbonn                200        500        300    
       107 nokia                 2200        110        400     

SQL> delete from product where quantity>100;

4 rows deleted.


19. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price, Selling_price, Quantity)
i) Insert Five records
ii)Get the product details whose selling price is between 2000 to 5000
iii) Increase quantity by 25% for all products whose cost price is less than Rs 250

SQL> create table product(pno number(5)primary key,pname varchar2(15),cp number(10),sp number(10),quantity number(10));
Table created.
SQL> insert into product values(103,'galaxy',100,200,300);
1 row created.
SQL> insert into product values(104,'trousers',100,2000,90);
1 row created.
SQL> insert into product values(101,'samsung',2000,4000,200);
1 row created.
SQL> insert into product values(102,'karbonn',200,500,300);
1 row created.
SQL> insert into product values(107,'nokia',2000,100,400);
1 row created.
SQL> select *from product;
       PNO PNAME                   CP         SP    QUANTITY
---------- --------------- ---------- ---------- ----------
       103 galaxy                 100        200        300
       104 trousers               100       2000         90
       101 samsung               2000       4000        200
       102 karbonn                200        500        300
       107 nokia                 2000        100        400
SQL> select * from product where sp>2000 AND sp<5000;

       PNO PNAME                   CP         SP    QUANTITY    
---------- --------------- ---------- ---------- ---------- ----------
       101 samsung               2000       4000        200      

SQL> update product set quantity=quantity+(quantity*25/100) where cp<250;

2 rows updated.

SQL> select * from product;

       PNO PNAME                   CP         SP    QUANTITY    
---------- --------------- ---------- ---------- ---------- ----------
       103 galaxy                 100        200        375      
       104 trousers              1000       2000         90    
       101 samsung               2000       4000        200 
       102 karbonn                200        500        375    
       107 nokia                 2200        110        400     


20. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price, Selling_price, Quantity)
i) Insert Five records
ii) Get the product number that costs 200,300 or 400
iii) Get the product details whose cost_price is maximum
iv) Count all product whose cost_price is less than 100

SQL> create table product(pno number(5)primary key,pname varchar2(15),cp number(10),sp number(10),quantity number(10));
Table created.
SQL> insert into product values(103,'galaxy',100,200,300);
1 row created.
SQL> insert into product values(104,'trousers',100,2000,90);
1 row created.
SQL> insert into product values(101,'samsung',2000,4000,200);
1 row created.
SQL> insert into product values(102,'karbonn',200,500,300);
1 row created.
SQL> insert into product values(107,'nokia',90,100,400);
1 row created.
SQL> select *from product;

       PNO PNAME                   CP         SP    QUANTITY
---------- --------------- ---------- ---------- ----------
       103 galaxy                 100        200        300
       104 trousers               100       2000         90
       101 samsung               2000       4000        200
       102 karbonn                200        500        300
       107 nokia                     90        100        400

SQL> select *from product where cp=200 OR cp=300 OR cp=400;

       PNO PNAME                   CP         SP    QUANTITY    
---------- --------------- ---------- ---------- ---------- ----------
       102 karbonn                200        500        375       

SQL> select max(cp) from product;

   MAX(CP)
----------
      2200
SQL> select count(*) from product where cp<100;

COUNT(*)
------------
           1
21. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price, Selling_price)
i) Insert Five records
ii) Calculate profit_percentage by adding a column
iii) Get the total number of product in the table
iv)  Change the product_name by ‘Cap’ for productNo 10 and increase the selling price by 5%

SQL> create table product(pno number(5)primary key,pname varchar2(15),cp number(10),sp number(10),quantity number(10));
Table created.
SQL> insert into product values(103,'galaxy',100,200,300);
1 row created.
SQL> insert into product values(104,'trousers',100,2000,90);
1 row created.
SQL> insert into product values(101,'samsung',2000,4000,200);
1 row created.
SQL> insert into product values(102,'karbonn',200,500,300);
1 row created.
SQL> insert into product values(107,'nokia',2000,100,400);
1 row created.
SQL> select *from product;



       PNO PNAME                   CP         SP    QUANTITY
---------- --------------- ---------- ---------- ----------
       103 galaxy                 100        200        300
       104 trousers               100       2000         90
       101 samsung               2000       4000        200
       102 karbonn                200        500        300
       107 nokia                 2000        100        400
SQL> alter table product add profit number(10);
Table altered.
SQL> select *from product;
       PNO PNAME                   CP         SP    QUANTITY     PROFIT
---------- --------------- ---------- ---------- ---------- ----------
       103 galaxy                 100        200        300
       104 trousers               100       2000         90
       101 samsung               2000       4000        200
       102 karbonn                200        500        300
       107 nokia                 2000        100        400
SQL> update product set profit =sp-cp;
5 rows updated.
SQL> select *from product;
       PNO PNAME                   CP         SP    QUANTITY     PROFIT
---------- --------------- ---------- ---------- ---------- ----------
       103 galaxy                 100        200        300        100
       104 trousers               100       2000         90       1900
       101 samsung               2000       4000        200       2000
       102 karbonn                200        500        300        300
       107 nokia                 2000        100        400      -1900

SQL> select count(*) from product;
  COUNT(*)
----------
         5
 22. Create table client with the following fields (ClientNo (pk) , name, City, State, PinCode,Bal_Due)

i) Insert Five records
ii) List all client who are located in Timil Nadu state
iii) Change the city of clientNo ‘c005’ to Mumbai
SQL> create table client(cno number(10)primary key,cname varchar2(15),city varchar2(15),state varchar2(15),pincode number(10),baldue number(10));
Table created.
SQL> insert into client values(001,'naresh','mumbai','maharashtra',503187,7000);
1 row created.
SQL> insert into client values(002,'nani','chennai','tamilnadu',503306,8000);
1 row created.
SQL> insert into client values(1099,'raju','maduri','tamilnadu',503306,8000);
1 row created.
SQL> insert into client values(005,'rajesh','newdelhi','delhi',600013,4000);
1 row created.
SQL> insert into client values(006,'mahesh','newdelhi','delhi',713304,5000);
1 row created.
SQL> select *from client;
       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         1 naresh          mumbai          maharashtra         503187       7000
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306       8000
         5 rajesh          newdelhi        delhi               600013       4000
         6 mahesh          newdelhi        delhi               713304       5000

SQL> select *from client where state='tamilnadu';
       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306       8000

SQL> update client set city='mumbai' where cno=005;
1 row updated.
SQL> select *from client;
       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         1 naresh          mumbai          maharashtra         503187       7000
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306       8000
         5 rajesh          mumbai          delhi               600013       4000
         6 mahesh          newdelhi        delhi               713304       5000

23. Create table client with the following fields (ClientNo (pk) , name, City, State, PinCode,Bal_Due)

i) Insert Five records
ii) List the client details for all clients whose Bal_due is less than 5000
iii) get the client table details in ascending order of Bal_due
iv) Count the number of clients from the city Chennai

SQL> create table client(cno number(10)primary key,cname varchar2(15),city varchar2(15),state varchar2(15),pincode number(10),baldue number(10));
Table created.
SQL> insert into client values(001,'naresh','mumbai','maharashtra',503187,7000);
1 row created.
SQL> insert into client values(002,'nani','chennai','tamilnadu',503306,8000);
1 row created.
SQL> insert into client values(1099,'raju','maduri','tamilnadu',503306,8000);
1 row created.
SQL> insert into client values(005,'rajesh','newdelhi','delhi',600013,4000);
1 row created.
SQL> insert into client values(006,'mahesh','newdelhi','delhi',713304,5000);
1 row created.
SQL> select *from client;
       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         1 naresh          mumbai          maharashtra         503187       7000
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306       8000
         5 rajesh          newdelhi        delhi               600013       4000
         6 mahesh          newdelhi        delhi               713304       5000

SQL> select *from client where baldue<5000;
       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         5 rajesh          mumbai          delhi               600013       4000

SQL> select *from client order by baldue;
       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         5 rajesh          mumbai          delhi               600013       4000
         6 mahesh          newdelhi        delhi               713304       5000
         1 naresh          mumbai          maharashtra         503187       7000
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306       8000

SQL> select count(*) from client where city='chennai';
  COUNT(*)
----------
         1

24. Create table client with the following fields (ClientNo (pk) , name, City, State, PinCode,Bal_Due)

i) Insert Five records
ii) Change the bal_due for clientNo 1099
iii) Delete all clients who are from the city New Delhi
SQL> create table client(cno number(10)primary key,cname varchar2(15),city varchar2(15),state varchar2(15),pincode number(10),baldue number(10));
Table created.
SQL> insert into client values(001,'naresh','mumbai','maharashtra',503187,7000);
1 row created.
SQL> insert into client values(002,'nani','chennai','tamilnadu',503306,8000);
1 row created.
SQL> insert into client values(1099,'raju','maduri','tamilnadu',503306,8000);
1 row created.
SQL> insert into client values(005,'rajesh','newdelhi','delhi',600013,4000);
1 row created.
SQL> insert into client values(006,'mahesh','newdelhi','delhi',713304,5000);
1 row created.
SQL> select *from client;
       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         1 naresh          mumbai          maharashtra         503187       7000
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306       8000
         5 rajesh          newdelhi        delhi               600013       4000
         6 mahesh          newdelhi        delhi               713304       5000

SQL> update client set baldue=10000 where cno=1099;
1 row updated.

SQL> select *from client;
       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         1 naresh          mumbai          maharashtra         503187       7000
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306      10000
         5 rajesh          mumbai          delhi               600013       4000
         6 mahesh          newdelhi        delhi               713304       5000
SQL> delete from client where city='newdelhi';
1 row deleted.
SQL> select *from client;
       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         1 naresh          mumbai          maharashtra         503187       7000
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306      10000
         5 rajesh          mumbai          delhi               600013       4000
25. Create table client with the following fields (ClientNo (pk) , name, City, State, PinCode,Bal_Due)
i) Insert Five records
ii) Display the client details whose having the minimum bal_due
iii) Display the total bal_due in the table
iv) delete all clients who are from Maharashtra state

SQL> create table client(cno number(10)primary key,cname varchar2(15),city varchar2(15),state varchar2(15),pincode number(10),baldue number(10));
Table created.
SQL> insert into client values(001,'naresh','mumbai','maharashtra',503187,7000);
1 row created.
SQL> insert into client values(002,'nani','chennai','tamilnadu',503306,8000);
1 row created.
SQL> insert into client values(1099,'raju','maduri','tamilnadu',503306,8000);
1 row created.
SQL> insert into client values(005,'rajesh','newdelhi','delhi',600013,4000);
1 row created.
SQL> insert into client values(006,'mahesh','newdelhi','delhi',713304,5000);
1 row created.
SQL> select *from client;
       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         1 naresh          mumbai          maharashtra         503187       7000
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306       8000
         5 rajesh          newdelhi        delhi               600013       4000
         6 mahesh          newdelhi        delhi               713304       5000

SQL> select min(baldue) from client;
MIN(BALDUE)
-----------
       4000

SQL> select sum(baldue) from client;

SUM(BALDUE)
-----------
      32000
SQL> delete from client where state='maharashtra';

1 row deleted.

26. Create table client with the following fields (ClientNo (pk) , name, City, State, PinCode,Bal_Due)

i) Insert Five records
ii) Display all client details for all client whose name starts with M
iii) delete the client tuples whose pin code is 713304
iv) Increase the bal_due by 7% for all whosepin_code= 600013

SQL> create table client(cno number(10)primary key,cname varchar2(15),city varchar2(15),state varchar2(15),pincode number(10),baldue number(10));
Table created.
SQL> insert into client values(001,'naresh','mumbai','maharashtra',503187,7000);
1 row created.
SQL> insert into client values(002,'nani','chennai','tamilnadu',503306,8000);
1 row created.
SQL> insert into client values(1099,'raju','maduri','tamilnadu',503306,8000);
1 row created.
SQL> insert into client values(005,'rajesh','newdelhi','delhi',600013,4000);
1 row created.
SQL> insert into client values(006,'mahesh','newdelhi','delhi',713304,5000);
1 row created.
SQL> select *from client;


       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         1 naresh          mumbai          maharashtra         503187       7000
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306       8000
         5 rajesh          newdelhi        delhi               600013       4000
         6 mahesh          newdelhi        delhi               713304       5000

SQL> select *from client where cname like 'm%';

       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         6 mahesh          newdelhi        delhi               713304       5000

SQL> delete from client where pincode=713304;
1 row deleted.
SQL> update client set baldue=baldue+(baldue*7/100) where pincode=600013;
1 row updated.
SQL> select *from client;

       CNO CNAME           CITY            STATE              PINCODE     BALDUE
---------- --------------- --------------- --------------- ---------- ----------
         1 naresh          mumbai          maharashtra         503187       7000
         2 nani            chennai         tamilnadu           503306       8000
      1099 raju            maduri          tamilnadu           503306      10000
         5 rajesh          mumbai          delhi               600013       4280

32) Create a Course table with the  following fields
(  Student Name, Course ,College ,College Address,,Principal Name ,Fee )

i ) Insert five records
ii ) Display the Maximum course fee along with  the Course
iii) Display the name of students, their college name, Principal name where  the course is M.C.A..

SQL> create table course1(sname varchar2(15),coursename varchar2(15),college varchar2(15),collegeaddress varchar2(15),principalname varchar2(15),fee number(15));
Table created.
SQL>  insert into  course1 values('raju','mca','avanthi','hyd',' rajesh',40000);
1 row created.
SQL> insert into  course1 values('ravi','mca','mlr','hyd',' rajareddy',50000);
1 row created.
SQL> insert into  course1 values('ramesh','mba','technohitec','hyd',' subbareddy',50000);
1 row created.
SQL> insert into  course1 values('mahesh','mca','technohitec','hyd',' subbareddy',60000);
1 row created.
SQL>  insert into  course1 values('anil','btech','technohitec','hyd',' subbareddy',50000);
1 row created.
SQL> select *from course1;

SNAME           COURSENAME      COLLEGE         COLLEGEADDRESS   PRINCIPALNAME                    FEE
--------------- --------------- --------------- --------------- -------------------------
raju            mca             avanthi         hyd              rajesh                                           40000
ravi            mca             mlr             hyd              rajareddy                                          50000
ramesh          mba             technohitec     hyd              subbareddy                     50000
mahesh          mca             technohitec     hyd              subbareddy                     60000
anil            btech           technohitec     hyd              subbareddy                           50000

SQL> select max(fee) from course1;

  MAX(FEE)
----------
     60000
SQL> select sname,college,principalname from course1 where coursename='mca';

SNAME           COLLEGE         PRINCIPALNAME
--------------- --------------- ---------------
raju            avanthi          rajesh
ravi            mlr              rajareddy
mahesh          technohitec      subbareddy


33) Create a Course table with the  following fields
(  Student Name, Course ,College ,College Address,,Principal Name ,Fee )

i ) Insert five records
ii) Count the number of courses taught in TechnoHitec College
iii) Delete all records where college name is TechnoHitec

SQL> create table course1(sname varchar2(15),coursename varchar2(15),college varchar2(15),collegeaddress varchar2(15),principalname varchar2(15),fee number(15));
Table created.
SQL>  insert into  course1 values('raju','mca','avanthi','hyd',' rajesh',40000);
1 row created.
SQL> insert into  course1 values('ravi','mca','mlr','hyd',' rajareddy',50000);
1 row created.
SQL> insert into  course1 values('ramesh','mba','technohitec','hyd',' subbareddy',50000);
1 row created.
SQL> insert into  course1 values('mahesh','mca','technohitec','hyd',' subbareddy',60000);
1 row created.
SQL>  insert into  course1 values('anil','btech','technohitec','hyd',' subbareddy',50000);
1 row created.
SQL> select *from course1;
SNAME           COURSENAME      COLLEGE         COLLEGEADDRESS   PRINCIPALNAME                    FEE
--------------- --------------- --------------- --------------- -------------------------
raju            mca             avanthi         hyd              rajesh                                           40000
ravi            mca             mlr             hyd              rajareddy                                          50000
ramesh          mba             technohitec     hyd              subbareddy                     50000
mahesh          mca             technohitec     hyd              subbareddy                     60000
anil            btech           technohitec     hyd              subbareddy                           50000

SQL> select count(coursename) from course1 where college='technohitec';
COUNT(COURSENAME)
-----------------
                3
SQL> delete from course1 where college='technohitec';
3 rows deleted.



34. Create a Course table with the  following fields
(  Student Name, Course ,College ,College Address,,Principal Name ,Fee )

i ) Insert five records
ii) get the course details in descending order of fee
iii) List the college name whose course fee is greater than 20000 for MBA course.

SQL> create table course1(sname varchar2(15),coursename varchar2(15),college varchar2(15),collegeaddress varchar2(15),principalname varchar2(15),fee number(15));
Table created.
SQL>  insert into  course1 values('raju','mca','avanthi','hyd',' rajesh',40000);
1 row created.
SQL> insert into  course1 values('ravi','mca','mlr','hyd',' rajareddy',50000);
1 row created.
SQL> insert into  course1 values('ramesh','mba','technohitec','hyd',' subbareddy',50000);
1 row created.
SQL> insert into  course1 values('mahesh','mca','technohitec','hyd',' subbareddy',60000);
1 row created.
SQL>  insert into  course1 values('anil','btech','technohitec','hyd',' subbareddy',50000);
1 row created.
SQL> select *from course1;
SNAME           COURSENAME      COLLEGE         COLLEGEADDRESS   PRINCIPALNAME                    FEE
--------------- --------------- --------------- --------------- -------------------------
raju            mca             avanthi         hyd              rajesh                                           40000
ravi            mca             mlr             hyd              rajareddy                                          50000
ramesh          mba             technohitec     hyd              subbareddy                     50000
mahesh          mca             technohitec     hyd              subbareddy                     60000
anil            btech           technohitec     hyd              subbareddy                           50000
SQL> select * from course1 order by fee desc;
SNAME           COURSENAME      COLLEGE         COLLEGEADDRESS   PRINCIPALNAME             FEE
--------------- --------------- --------------- --------------- --------------- ----------
mahesh          mca             technohitec     hyd              subbareddy                     60000
anil            btech           technohitec     hyd              subbareddy                             50000
ravi            mca             mlr             hyd              rajareddy                                          50000
ramesh          mba             technohitec     hyd              subbareddy                     50000
raju            mca             avanthi         hyd              rajesh                                            40000

SQL> select  college from course1 where fee>20000 AND coursename='mba';
COLLEGE
---------------
technohitec

41. create the table book with following details (bookid, author, title, price, quantity)

i ) Insert  five records
ii) Display the books of title rdbms
iii) delete all rows where author name is MattWall

SQL> create table book(bid number(10),author varchar2(15),title varchar2(15),price number(10),quantity number(10));
Table created.
SQL> insert into book values(101,'mattwall','dbms',1000,50);
1 row created.
SQL> insert into book values(102,'ramakrishna','dbms',500,10);
1 row created.
SQL> insert into book values(103,'hansen','rdbms',1000,20);
1 row created.
SQL> insert into book values(104,'peter','rdbms',500,10);
1 row created.
SQL> insert into book values(955,'robert','dbms',100,10);
1 row created.
SQL> select *from book;

       BID AUTHOR          TITLE                PRICE   QUANTITY
---------- --------------- --------------- ---------- ----------
       101 mattwall         dbms                  1000         50
       102 ramakrishna      dbms                   500         10
       103 hansen          rdbms                 1000         20
       104 peter           rdbms                  500         10
       955 robert          dbms                   100         10

SQL> select *from book where title='rdbms';

       BID AUTHOR          TITLE                PRICE   QUANTITY
---------- --------------- --------------- ---------- ----------
       103 hansen          rdbms                 1000         20
       104 peter           rdbms                  500         10

SQL> delete from book where author='mattwall';
1 row deleted.


42. create the table book with following details (bookid, author, title, price, quantity)

i ) Insert  five records
ii) Which title of book is having maximum quantity
iii) Increase the price all books by 10% whose author is Ramakrishna

SQL> create table book(bid number(10),author varchar2(15),title varchar2(15),price number(10),quantity number(10));
Table created.
SQL> insert into book values(101,'mattwall','dbms',1000,50);
1 row created.
SQL> insert into book values(102,'ramakrishna','dbms',500,10);
1 row created.
SQL> insert into book values(103,'hansen','rdbms',1000,20);
1 row created.
SQL> insert into book values(104,'peter','rdbms',500,10);
1 row created.
SQL> insert into book values(955,'robert','dbms',100,10);
1 row created.
SQL> select *from book;

       BID AUTHOR          TITLE                PRICE   QUANTITY
---------- --------------- --------------- ---------- ----------
       101 mattwall         dbms                  1000         50
       102 ramakrishna      dbms                   500         10
       103 hansen          rdbms                 1000         20
       104 peter           rdbms                  500         10
       955 robert          dbms                   100         10


SQL> select  max(quantity) from book;
MAX(QUANTITY)
-------------
           50
SQL> update book set price=price+(price*10/100) where author='ramakrishna';
1 row updated.
SQL> select  *from book;

       BID AUTHOR          TITLE                PRICE   QUANTITY
---------- --------------- --------------- ---------- ----------
       102 ramakrishna      dbms                   550         10
       103 hansen          rdbms                 1000         20
       104 peter           rdbms                  500         10
       955 robert          dbms                   100         10
       101 mattwall         dbms                  1000         50

43.create the table book with following details (bookid, author, title, price, quantity)

i ) Insert  five records
ii) Display the book with maximum price
iii) Change the title of bookid 955 to ‘C Skills’
SQL> create table book(bid number(10),author varchar2(15),title varchar2(15),price number(10),quantity number(10));
Table created.
SQL> insert into book values(101,'mattwall','dbms',1000,50);
1 row created.
SQL> insert into book values(102,'ramakrishna','dbms',500,10);
1 row created.
SQL> insert into book values(103,'hansen','rdbms',1000,20);
1 row created.
SQL> insert into book values(104,'peter','rdbms',500,10);
1 row created.
SQL> insert into book values(955,'robert','dbms',100,10);
1 row created.
SQL> select *from book;

       BID AUTHOR          TITLE                PRICE   QUANTITY
---------- --------------- --------------- ---------- ----------
       101 mattwall         dbms                  1000         50
       102 ramakrishna      dbms                   500         10
       103 hansen          rdbms                 1000         20
       104 peter           rdbms                  500         10
       955 robert          dbms                   100         10

SQL> select  max(price) from book;
MAX(PRICE)
----------
      1000
SQL> update book set title='C Skills' where bid=955;
1 row updated.
SQL> select  *from book;

       BID AUTHOR          TITLE                PRICE   QUANTITY
---------- --------------- --------------- ---------- ----------
       102 ramakrishna      dbms                   550         10
       103 hansen          rdbms                 1000         20
       104 peter           rdbms                  500         10
       955 robert          C Skills               100         10
       101 mattwall         dbms                  1000         50

44. create the table book with following details (bookid, author, title, price, quantity)

i ) Insert  five records
ii) display the book details in ascending order for title
iii) calculate total price by adding a column

SQL> create table book(bid number(10),author varchar2(15),title varchar2(15),price number(10),quantity number(10));
Table created.
SQL> insert into book values(101,'mattwall','dbms',1000,50);
1 row created.
SQL> insert into book values(102,'ramakrishna','dbms',500,10);
1 row created.
SQL> insert into book values(103,'hansen','rdbms',1000,20);
1 row created.
SQL> insert into book values(104,'peter','rdbms',500,10);
1 row created.
SQL> insert into book values(955,'robert','dbms',100,10);
1 row created.
SQL> select *from book;

       BID AUTHOR          TITLE                PRICE   QUANTITY
---------- --------------- --------------- ---------- ----------
       101 mattwall         dbms                  1000         50
       102 ramakrishna      dbms                   500         10
       103 hansen          rdbms                 1000         20
       104 peter           rdbms                  500         10
       955 robert          dbms                   100         10

SQL> select  *from book order by title;

       BID AUTHOR          TITLE                PRICE   QUANTITY
---------- --------------- --------------- ---------- ----------
       955 robert          C Skills               100         10
       102 ramakrishna      dbms                   550         10
       101 mattwall         dbms                  1000         50
       103 hansen          rdbms                 1000         20
       104 peter           rdbms                  500         10

SQL> alter table book add(total number(10));
Table altered.
SQL> update book set total=price*quantity;
5 rows updated.
SQL> select  *from book;

       BID AUTHOR          TITLE                PRICE   QUANTITY      TOTAL
---------- --------------- --------------- ---------- ---------- ----------
       102 ramakrishna      dbms                   550         10       5500
       103 hansen          rdbms                 1000         20      20000
       104 peter           rdbms                  500         10       5000
       955 robert          C Skills               100         10       1000
       101 mattwall         dbms                  1000         50      50000


45. create the table book with following details (bookid, author, title, price, quantity)

i ) Insert  five records
ii) Increase the quantity of all books of rdbms by 10%
iii)calculate the total number of books.

SQL> create table book(bid number(10),author varchar2(15),title varchar2(15),price number(10),quantity number(10));
Table created.
SQL> insert into book values(101,'mattwall','dbms',1000,50);
1 row created.
SQL> insert into book values(102,'ramakrishna','dbms',500,10);
1 row created.
SQL> insert into book values(103,'hansen','rdbms',1000,20);
1 row created.
SQL> insert into book values(104,'peter','rdbms',500,10);
1 row created.
SQL> insert into book values(955,'robert','dbms',100,10);
1 row created.
SQL> select *from book;

       BID AUTHOR          TITLE                PRICE   QUANTITY
---------- --------------- --------------- ---------- ----------
       101 mattwall         dbms                  1000         50
       102 ramakrishna      dbms                   500         10
       103 hansen          rdbms                 1000         20
       104 peter           rdbms                  500         10
       955 robert          dbms                   100         10
SQL> update book set price=price+(price*10/100) where title='rdbms';
2 rows updated.
SQL> select  *from book;

       BID AUTHOR          TITLE                PRICE   QUANTITY      TOTAL
---------- --------------- --------------- ---------- ---------- ----------
       102 ramakrishna      dbms                   550         10       5500
       103 hansen          rdbms                 1100         20      20000
       104 peter           rdbms                  550         10       5000
       955 robert          C Skills               100         10       1000
       101 mattwall         dbms                  1000         50      50000

SQL> select  sum (quantity)from book;

SUM(QUANTITY)
-------------
          100
  

No comments:

Post a Comment