FOR FREE CONTENT

CREATE TABLES AND INSERT VALUES

1. CREATE TABLE STORE_INFORMATION

(

STORE_NAME VARCHAR2(20) CONSTRAINT PK_STORE_INFORMATION PRIMARY KEY,

SALES NUMBER (4),

SALE_DATE DATE

);

1. DESC STORE_INFORMATION;

1. INSERT INTO STORE_"INFORMATION" TABLE :

INSERT INTO STORE_INFORMATION (STORE_NAME, SALES, SALE_DATE)

VALUES ('BOSTON',250,'06-11-2022');

INSERT INTO STORE_INFORMATION (STORE_NAME, SALES, SALE_DATE)

VALUES ('ILLINOIS',450,'06-05-2022');

INSERT INTO STORE_INFORMATION (STORE_NAME, SALES, SALE_DATE)

VALUES ('KANSAS',300,'10-25-2022');

INSERT INTO STORE_INFORMATION (STORE_NAME, SALES, SALE_DATE)

VALUES ('SAN DIEGO',800,'02-01-2022');

INSERT INTO STORE_INFORMATION (STORE_NAME, SALES, SALE_DATE)

VALUES ('LOS ANGELES',930,'12-28-2022');

1. SELECT *FROM STORE_INFORMATION;

2. CREATE TABLE GEOGRAPHY

(

REGION_NAME VARCHAR2(20),

STORE_NAME VARCHAR2(20) CONSTRAINT FK_GEOGRAPHY REFERENCES STORE_INFORMATION

(STORE_NAME)

);

2. DESC GEOGRAPHY;

2. INSERT INTO "GEOGRAPHY" TABLE :

INSERT INTO GEOGRAPHY(REGION_NAME , STORE_NAME)

VALUES('NORTH','BOSTON');

INSERT INTO GEOGRAPHY(REGION_NAME , STORE_NAME)

VALUES('SOUTH','ILLINOIS');

INSERT INTO GEOGRAPHY(REGION_NAME , STORE_NAME)

VALUES('EAST','KANSAS');

INSERT INTO GEOGRAPHY(REGION_NAME , STORE_NAME)

VALUES('WEST','LOS ANGELES');

INSERT INTO GEOGRAPHY(REGION_NAME , STORE_NAME)

VALUES('EAST','SAN DIEGO');

2. SELECT *FROM GEOGRAPHY;

QUERIES :

1. SALES OF ALL STORES IN WEST REGION :

SELECT STORE_INFORMATION.SALES, STORE_INFORMATION.STORE_NAME FROM STORE_INFORMATION

,GEOGRAPHY WHERE STORE_INFORMATION.STORE_NAME = GEOGRAPHY.STORE_NAME

AND GEOGRAPHY.REGION_NAME = 'WEST' ;

2. THE TOTAL SALE whose REGION STORE NAME PRESENT IN BOTH TABLE:

SELECT SUM(SALES) FROM STORE_INFORMATION, GEOGRAPHY WHERE

STORE_INFORMATION.STORE_NAME = GEOGRAPHY.STORE_NAME ;

3. THE TOTAL SALE IF THERE HAVE ONE EAST REGION IN THE TABLE GEOGRAPHY:

SELECT SUM(SALES)

FROM STORE_INFORMATION, GEOGRAPHY

WHERE STORE_INFORMATION.STORE_NAME = GEOGRAPHY.STORE_NAME

AND GEOGRAPHY.REGION_NAME='EAST';

4. TO MULTIPLY SALES AMOUNT FROM LOS ANGELES BY 2 AND SAN DIEGO BY 1.5:

UPDATE STORE_INFORMATION SET SALES=SALES*2

WHERE STORE_NAME = 'LOS ANGELES';

UPDATE STORE_INFORMATION SET SALES=SALES*1.5

WHERE STORE_NAME = 'SAN DIEGO';

SELECT * FROM STORE_INFORMATION ;

5. TO SHOW FIRST TWO SALES AMOUNTS IN TABLE TORE_INFORMATION :

SELECT sales FROM STORE_INFORMATION where rownum<= 2;