Dbmsday2.txt

INSERT INTO CUSTOMERS_2011 VALUES('&CUST_ID','&CUST_FIRSTNAME','&CUST_LASTNAME','&TERRITORY','&CREDIT_LIMIT','&MGR_ID','&DOB','&MARITAL_STATUS','&GENDER','&INCOME');
ALTER TABLE CUSTOMERS_2011 RENAME COLUMN GENDER TO SEX;
ALTER TABLE CUSTOMERS_2011 RENAME COLUMN CUSTOMER_ID TO CUST_ID;
SELECT CUST_ID,CUST_FIRSTNAME,CUST_LASTNAME,MARITAL_STATUS,SEX,INCOME FROM CUSTOMERS_2011;
INSERT INTO CUSTOMERS_2011 VALUES('&CUST_ID','&CUST_FIRSTNAME','&CUST_LASTNAME','&TERRITORY','&CREDIT_LIMIT','&MGR_ID','&DOB','&MARITAL_STATUS','&SEX','&INCOME');
SELECT CUST_ID,CUST_FIRSTNAME,CUST_LASTNAME,MARITAL_STATUS,INCOME FROM CUSTOMERS_2011 WHERE SEX='F';
SELECT CUST_FIRSTNAME,CUST_LASTNAME,MARITAL_STATUS,INCOME FROM CUSTOMERS_2011 WHERE SEX='M' AND MARITAL_STATUS='Single';
SELECT TO_CHAR(DOB,'YYYY') FROM CUSTOMERS_2011;
SELECT CUST_ID,CUST_FIRSTNAME,CUST_LASTNAME FROM CUSTOMERS_2011 WHERE TO_CHAR(DOB,'YYYY')<2000;
SELECT INCOME,INCOME+2000 FROM CUSTOMERS_2011;
UPDATE CUSTOMERS_2011 SET INCOME=INCOME+2000;
SELECT CUST_ID,MGR_ID FROM CUSTOMERS_2011;

//SELF-JOIN EXAMPLE-> SELECT C.CUST_FIRSTNAME,C.CUST_LASTNAME,M.CUST_FIRSTNAME,M.CUST_LASTNAME FROM CUSTOMER C,CUSTOMER M WHERE C.MGR_ID=M.CUSTOMER_ID;

ALTER TABLE CUSTOMERS_2011 ADD CITY VARCHAR2(25);
UPDATE CUSTOMERS_2011 SET CITY='KOLKATA';//SET AS ->UPDATE CUSTOMERS_2011 SET CITY='&CITY' WHERE CUST_ID='&CUST_ID';
INSERT INTO CUSTOMERS_2011 VALUES('&CUST_ID','&CUST_FIRSTNAME','&CUST_LASTNAME','&TERRITORY','&CREDIT_LIMIT','&MGR_ID','&DOB','&MARITAL_STATUS','&SEX','&INCOME','&CITY');
INSERT INTO CUSTOMERS_2011(CUST_ID,CUST_FIRSTNAME,CUST_LASTNAME,CITY) VALUES('&CUST_ID','&CUST_FIRSTNAME','&CUST_LASTNAME','&CITY');
DELETE FROM CUSTOMERS_2011 WHERE CUST_FIRSTNAME LIKE 'A%';
UPDATE CUSTOMER SET CREDIT_LIMIT=1000 WHERE INCOME_LEVEL>=100000 AND INCOME_LEVEL<=120000;
DROP TABLE CUSTOMER_2011;
FLASHBACK TABLE CUSTOMER_2011 TO BEFORE DROP RENAME TO CUSTOMER_2024;
CREATE TABLE CUSTOMER1 AS SELECT * FROM CUSTOMER_2024;
TRUNCATE TABLE CUSTOMER_2024;