INSERT
DELETE
UPDATE
SELECT
SUB QUERIES
JOIN
INSERT
SQL> insert into Shope values('&store_name','&sales','&date');
Enter value for store_name: Chitra Textiles
Enter value for sales: $500
Enter value for date: 23-dec-2009
old 1: insert into Shope values('&store_name','&sales','&date')
new 1: insert into Shope values('Chitra Textiles','$500','23-dec-2009')
1 row created.
SQL> insert into Shope values('&store_name','&sales','&date');
Enter value for store_name: VVL Medical
Enter value for sales: $250
Enter value for date: 07-jan-1999
old 1: insert into Shope values('&store_name','&sales','&date')
new 1: insert into Shope values('VVL Medical','$250','07-jan-1999')
1 row created.
SQL> insert into Shope values('&store_name','&sales','&date');
Enter value for store_name: Chitra Textiles
Enter value for sales: $300
Enter value for date: 08-jan-1999
old 1: insert into Shope values('&store_name','&sales','&date')
new 1: insert into Shope values('Chitra Textiles','$300','08-jan-1999')
1 row created.
SQL> insert into Shope values('&store_name','&sales','&date');
Enter value for store_name: VP Store
Enter value for sales: &700
Enter value for date: 08-jan-1999
old 1: insert into Shope values('&store_name','&sales','&date')
new 1: insert into Shope values('VP Store ','&700','08-jan-1999')
1 row created.
SQL> select * from Shope;
STORE_NAME SALES SALE_DATE
-------------------- ---------- -----------------
Chitra Textiles $500 23-DEC-09
VVL Medical $250 07-JAN-99
Chitra Textiles $300 08-JAN-99
VP Store $700 08-JAN-99
DELETE
SQL> delete from Shope where STORE_NAME='Chitra Textiles';
2 rows deleted.
SQL> select * from Shope;
STORE_NAME SALES SALE_DATE
-------------------- --------- -----------------
VVL Medical $500 07-JAN-99
VP Store $500 08-JAN-99
UPDATE
SQL> update Shope set sales=500 ;
4 rows updated.
SQL> select * from Shope;
STORE_NAME SALES SALE_DATE
-------------------- ---------- -----------------
Chitra Textiles $500 23-DEC-09
VVL Medical $500 07-JAN-99
Chitra Textiles $500 08-JAN-99
VP Store $500 08-JAN-99
SQL> update Shope set sales=400 where store_name='Chitra Textiles';
2 rows updated.
SELECT
SQL> select * from Shope;
STORE_NAME SALES SALE_DATE
-------------------- ---------- -----------------
Chitra Textiles $400 23-DEC-09
VVL Medical $500 07-JAN-99
Chitra Textiles $400 08-JAN-99
VP Store $500 08-JAN-99
DROP
SQL> create table Shope(store_name varchar2(20),sales varchar2(10),Sale_Date date);
Table created.
SQL> select * from Shope;
STORE_NAME SALES SALE_DATE
-------------------- ---------- ------------------
VVL Medical 500 07-JAN-99
VP Store 500 08-JAN-99
SQL> drop table Shope;
Table dropped.
SQL> select * from Shope;
select * from Shope
SUB QUERIES
STORE_NAME SALES SALE_DATE
-------------------- ---------- -----------------
Chitra Textiles 250 23-DEC-99
VVL Medical 250 08-JAN-99
Chitra Textiles 250 08-JAN-99
VP Store 700 08-JAN-99
SQL> select sum(sales) from Shope where store_name in (select store_name from Geography where
2 region_name='west');
SUM(SALES)
-----------------
500
SQL> select sum(sales) from Shope where exists(select * from geography where
2 region_name='west');
SUM(SALES)
------------------
1450
JOIN
SQL> select A1.region_name REGION, SUM(A2.Sales)SALES
2 FROM Geography A1, Shope A2
3 WHERE A1.store_name=A2.store_name
4 GROUP BY A1.region_name;
REGION SALES
------------ ----------
east 700
west 500
OUTER JOIN
SQL> SELECT A1.store_name,SUM(A2.Sales)SALES
2 FROM Geography A1,Shope A2
3 WHERE A1.store_name=A2.store_name(+)
4 GROUP BY A1.store_name;
STORE_NAME SALES
---------- ---------- ----------
VP Store 700
Chitra Textiles 500
New York
VVL Medical
