[Oracle SQL Exercise 1] #Oracle #SQL

1_Problem_Assignment.sql

--1.
SET AUTO OFF;

DROP TABLE TOY_STORE_1579349 CASCADE CONSTRAINTS;
DROP TABLE TOY_DTLS_1579349 CASCADE CONSTRAINTS; 
DROP TABLE TOY_REL_1579349 CASCADE CONSTRAINTS;
DROP SEQUENCE TOY_STORE_SN_1579349;
DROP SEQUENCE TOY_DTLS_SN_1579349;

CREATE TABLE TOY_STORE_1579349
(
  TOY_STORE_ID NUMBER(3) PRIMARY KEY,
  TOY_STORE_NAME VARCHAR2(30) NOT NULL,
  CITY VARCHAR2(30) DEFAULT 'Delhi',
  PHONENUMBER NUMBER(10) UNIQUE NOT NULL,
  STORE_OPENING_TIME TIMESTAMP CHECK(EXTRACT(MINUTE FROM STORE_OPENING_TIME) >= 0 AND (EXTRACT(HOUR FROM STORE_OPENING_TIME)) >= 8),
  STORE_CLOSING_TIME TIMESTAMP CHECK(EXTRACT(HOUR FROM STORE_CLOSING_TIME) < 22)
);

--3.
INSERT INTO TOY_STORE_1579349 VALUES(1, 'Kid''s Cave', 'Delhi', 9912312312, TO_TIMESTAMP('09:10:12', 'HH24:MI:SS'), TO_TIMESTAMP('21:42:05', 'HH24:MI:SS'));
INSERT INTO TOY_STORE_1579349 VALUES(2, 'Kid''s Corner', 'Mumbai', 9912312313, TO_TIMESTAMP('09:00:00', 'HH24:MI:SS'), TO_TIMESTAMP('20:00:00', 'HH24:MI:SS'));
INSERT INTO TOY_STORE_1579349 VALUES(3, 'Play and Grow', 'Mumbai', 9912312301, TO_TIMESTAMP('09:00:00', 'HH24:MI:SS'), TO_TIMESTAMP('20:00:00', 'HH24:MI:SS'));
INSERT INTO TOY_STORE_1579349 VALUES(4, 'Puzzles and More', 'Delhi', 8112312301, TO_TIMESTAMP('08:15:00', 'HH24:MI:SS'), TO_TIMESTAMP('20:00:00', 'HH24:MI:SS'));
INSERT INTO TOY_STORE_1579349 VALUES(5, 'Uncle Sam Toys Den', 'Delhi', 8112312314, TO_TIMESTAMP('08:01:00', 'HH24:MI:SS'), TO_TIMESTAMP('20:00:00', 'HH24:MI:SS'));
INSERT INTO TOY_STORE_1579349 VALUES(6, 'Mickey Toys', 'Delhi', 8222312301, TO_TIMESTAMP('09:00:00', 'HH24:MI:SS'), TO_TIMESTAMP('20:00:00', 'HH24:MI:SS'));

CREATE TABLE TOY_DTLS_1579349
(
  TOY_ID NUMBER(4),
  TOY_NAME VARCHAR2(30),
  TOY_PRICE NUMBER(7,2),
  DISCOUNT_PERCENT NUMBER(5,2),
  AGEGROUP NUMBER(3) default 5,
  TOY_RATING NUMBER(1),
  TOY_CATEGORY CHAR(1),
  TOY_PIC BLOB
);

--2.
CREATE SEQUENCE TOY_STORE_SN_1579349;
CREATE SEQUENCE TOY_DTLS_SN_1579349;

ALTER TABLE TOY_DTLS_1579349 ADD 
(
  CONSTRAINT pk_1 PRIMARY KEY(TOY_ID),
  CONSTRAINT uc1_ UNIQUE(TOY_NAME),
  CONSTRAINT chk_TOY_NAME CHECK(TOY_NAME IS NOT NULL),
  CONSTRAINT chk_TOY_PRICE CHECK(TOY_PRICE > 10 AND TOY_PRICE IS NOT NULL),
  CONSTRAINT chk_DISCOUNT_PERCENT CHECK(DISCOUNT_PERCENT < 90),
  CONSTRAINT chk_TOY_RATING CHECK(TOY_RATING BETWEEN 1 AND 5),
  CONSTRAINT chk_TOY_CATEGORY CHECK(TOY_CATEGORY IN ('I', 'O', 'B', 'Indoor', 'Outdoor', 'Both'))
);

--4.
INSERT INTO TOY_DTLS_1579349 VALUES(1, 'Bat', 500, NULL, 5, NULL, NULL, NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(2, 'Ball', 50, NULL, 5, NULL, 'o', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(3, 'Brainvita', 250, 2, 10, 5, 'I', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(4, 'Scrabble', 100, 80, 15, 1, 'I', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(5, 'Snakes and Ladders', 40, 0, 5, 2, 'I', NULL);

SAVEPOINT S1;
COMMIT;

INSERT INTO TOY_DTLS_1579349 VALUES(6, 'Chess', 50, 5, 10, 4, 'I', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(7, 'Basket Ball', 150, 5, 10, 4, 'I', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(8, 'Volley Ball', 150, 5, 10, 4, 'o', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(9, 'Ping pong Ball', 30, 2, 5, 2, 'b', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(10, 'Skates', 450, NULL, 10, 3, 'o', NULL);

SAVEPOINT S1;
COMMIT;

INSERT INTO TOY_DTLS_1579349 VALUES(11, 'Racing _-_ cars', 50, NULL, 5, 3, 'I', NULL);

CREATE TABLE TOY_REL_1579349
(
  TOY_ID NUMBER(4),
  TOY_STORE_ID NUMBER(3),
  IN_STOCK CHAR(1) DEFAULT 'N' CHECK(IN_STOCK IN ('Y', 'N')),
  AVAILABLE_QTY NUMBER(3) DEFAULT 0,
  PRIMARY KEY(TOY_ID, TOY_STORE_ID),
  CONSTRAINT fk_RelDtls FOREIGN KEY(TOY_ID) REFERENCES TOY_DTLS_1579349,
  CONSTRAINT fk_RelStore FOREIGN KEY(TOY_STORE_ID) REFERENCES TOY_STORE_1579349
);

--4.
INSERT INTO TOY_REL_1579349 VALUES(1, 1, 'N', 0);
INSERT INTO TOY_REL_1579349 VALUES(2, 2, 'Y', 4);
INSERT INTO TOY_REL_1579349 VALUES(3, 2, 'Y', 6);
INSERT INTO TOY_REL_1579349 VALUES(1, 4, 'N', 0);
INSERT INTO TOY_REL_1579349 VALUES(3, 4, 'Y', 12);

SAVEPOINT S1;
COMMIT;

INSERT INTO TOY_REL_1579349 VALUES(4, 4, 'N', 0);
INSERT INTO TOY_REL_1579349 VALUES(5, 5, 'Y', 4);
INSERT INTO TOY_REL_1579349 VALUES(6, 6, 'Y', 6);
INSERT INTO TOY_REL_1579349 VALUES(7, 1, 'N', 0);
INSERT INTO TOY_REL_1579349 VALUES(8, 2, 'Y', 12);

SAVEPOINT S1;
COMMIT;

INSERT INTO TOY_REL_1579349 VALUES(9, 3, 'N', 0);
INSERT INTO TOY_REL_1579349 VALUES(10, 4, 'Y', 4);
INSERT INTO TOY_REL_1579349 VALUES(6, 5, 'Y', 6);
INSERT INTO TOY_REL_1579349 VALUES(7, 6, 'N', 0);
INSERT INTO TOY_REL_1579349 VALUES(8, 4, 'Y', 12);

SAVEPOINT S1;
COMMIT;

INSERT INTO TOY_REL_1579349 VALUES(4, 4, 'Y', 16);

SAVEPOINT S1;

--5.
UPDATE TOY_REL_1579349 SET IN_STOCK = 'N', AVAILABLE_QTY = 0 WHERE TOY_STORE_ID = 1 OR TOY_ID = 1;

SAVEPOINT S2;

--6.
INSERT INTO TOY_REL_1579349 VALUES(11, 6, 'Y', 1);

--SELECT td.TOY_ID, ts.TOY_STORE_ID, TOY_PRICE FROM TOY_REL_1579349 tr 
--  JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
--  JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
--WHERE TOY_STORE_NAME = 'Mickey Toys' OR TOY_NAME = 'Racing _-_ cars';

DELETE FROM (
SELECT td.TOY_ID, ts.TOY_STORE_ID, TOY_PRICE FROM TOY_REL_1579349 tr 
  JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
  JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
WHERE TOY_STORE_NAME = 'Mickey Toys' OR TOY_NAME = 'Racing _-_ cars'
);

SAVEPOINT S3;

--7.
--ROLLBACK TO S1;
--ROLLBACK TO S2;

--8.
--SELECT td.TOY_ID, ts.TOY_STORE_ID, TOY_PRICE FROM TOY_REL_1579349 tr 
--  JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
--  JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
--  WHERE TOY_STORE_NAME = 'Puzzles and More';
UPDATE (
  SELECT td.TOY_ID, TOY_PRICE FROM TOY_REL_1579349 tr 
  JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
  JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
  WHERE TOY_STORE_NAME = 'Puzzles and More'
) 
SET TOY_PRICE = TOY_PRICE*1.05;

--9.
SET AUTO ON;

UPDATE TOY_STORE_1579349 SET 
  STORE_OPENING_TIME = LOCALTIMESTAMP,
  STORE_CLOSING_TIME = LOCALTIMESTAMP;
  
SELECT STORE_OPENING_TIME, STORE_CLOSING_TIME FROM TOY_STORE_1579349;

--10.
ALTER TABLE TOY_REL_1579349 
  ADD SOLD_QUANTITY NUMBER(3);
  
--11.
ALTER TABLE TOY_REL_1579349 MODIFY (SOLD_QUANTITY DEFAULT 0);

--12.
ALTER TABLE TOY_REL_1579349 MODIFY (SOLD_QUANTITY NUMBER(5));

--13.
UPDATE TOY_REL_1579349 SET SOLD_QUANTITY = ROWNUM;
SELECT SOLD_QUANTITY, ROWNUM FROM TOY_REL_1579349;

--14.
ALTER TABLE TOY_REL_1579349 MODIFY (SOLD_QUANTITY DEFAULT NULL);

--15.
ALTER TABLE TOY_REL_1579349 DROP COLUMN SOLD_QUANTITY;

--16.
ALTER TABLE TOY_REL_1579349 DROP CONSTRAINT fk_RelDtls;
ALTER TABLE TOY_REL_1579349 DROP CONSTRAINT fk_RelStore;

ALTER TABLE TOY_REL_1579349 ADD (
  CONSTRAINT fk_RelDtls FOREIGN KEY(TOY_ID)
  REFERENCES TOY_DTLS_1579349
  ON DELETE CASCADE,
  CONSTRAINT fk_RelStore FOREIGN KEY(TOY_STORE_ID)
  REFERENCES TOY_STORE_1579349
  ON DELETE CASCADE
);

--17.
ALTER TABLE TOY_REL_1579349 DISABLE CONSTRAINT fk_RelStore; 
TRUNCATE TABLE TOY_STORE_1579349;
--ALTER TABLE TOY_REL_1579349 ENABLE CONSTRAINT fk_RelStore;