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.
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;
SQL> create table supplier(sno
number(12)primary key,sname varchar2(15),scity varchar2(12),turnover
number(15));
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.
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.
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.
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’
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