ORACLE QUERIES

1.Eliminating duplicate rows where using selected statement.


SELECT DISTINCT CUSTOMER_NAME FROM CUSTOMER

WHERE STATE = 'MAHARASHTRA';




2.SORTING DATA IN THE TABLE (ASCENDING ORDER AND DESCENDING ORDER)

ASCENDING ORDER:

SELECT CUSTOMER_ID FROM CUSTOMER

ORDER BY CUSTOMER_ID ASC;


DESCENDING ORDER:

SELECT CUSTOMER_ID FROM CUSTOMER

ORDER BY CUSTOMER_ID DESC;






3.DELETE OPERATION (REMOVAL OF ALL ROWS AND REMOVAL OF SPECIFIC ROW)

REMOVAL OF ALL ROWS

DELETE * FROM CUSTOMER;


REMOVAL OF SPECIFIC ROW

DELETE FROM CUSTOMER
EHERE CUSTOMER_ID = 1;





4.UPDATING OF ALL ROWS

UPDATE CUSTOMER
SET STATE = 'MAHARASHTRA';




5. UPDATE RECORD CONDITIONAL

UPDATE CUSTOMER
SET STATE = 'MAHARASHTRA' WHERE PIN = 400705;





6.MODIFYING THE STRUCTURE OF TABLE

ALTER TABLE CUSTOMER
ADD (STATE CHAR(20));





7. RENAMING TABLE

RENAME CUSTOMER TO CUSTOMER_DATA;





8.TRUNCATING TABLE

TRUNCATE CUSTOMER;





9.DESTROYING TABLES

DROP TABLE CUSTOMER;






10.COLUMN ALIAS

SELECT CUSTOMER_NAME AS CUST_NAME
FROM CUSTOMER;






11.DISPLAY THE STRUCTURE OF TABLE

DESCRIBE CUSTOMER;







12.PRIMARY KEY FOR CUSTOMER NUMBER

ALTER TABLE CUSTOMER ADD PRIMARY KEY(CUSTOMER_ID);






13.NOT NULL FOR FIRST NAME

ALTER TABLE CUSTOMER ADD CUSTOMER_FNAME(NOT NULL);






14.CHECK CONSTRAINT WITH VALUES FOR STATUS V,I,A

ALTER TABLE CUSTOMER ADD CONSTRAINT STATUS
CHECK (STATUS IN ('V','I','A'));




15.ADDING FOREIGN KEY

ALTER TABLE SALARY
ADD FOREIGN KEY (EMP_NO)
REFERENCE EMPLOYEE(EMP_NAME)






JOINT QUIRES

SELECT PLAYER.PLAYER_NO,PLAYER_NAME,MATCH_DATE,
OPPONENT
FROM PLAYER,MATCH
WHERE PLAYER.PLAYER_NO=MATCH.PLAYER_NO;