ACTION QUERIES ( SQL COMMANDS )

1. ACTION QUERIES (SQL COMMANDS) STRUCTURED QUERY LANGUAGE

2. ACTION QUERIES (SQL COMMANDS) • DATA DEFINITION LANGUAGE • DATA MANIPULATION LANGUAGE • TRANSACTION CONTROL LANGUAGE • DATA CONTROL LANGUAGE

3. DATA DEFINITION LANGUAGE (DDL) • CREATE • ALTER • DROP • TRUNCATE

4. DATA MANIPULATION LANGUAGE (DML) • INSERT • UPDATE • DELETE

5. TRANSACTION CONTROL LANGUAGE (TCL) • COMMIT • ROLLBACK • SAVEPOINT

6. DATA CONTROL LANGUAGE (DCL) • GRANT • REVOKE

7. NUMBER (FLOAT) VARCHAR2 DATE 40 DIGITS +SPACE FOR A DECIMAL AND A SIGN UPTO 4000 BYTES 7 DIGITS DATA TYPES

8. TO CREATE A TABLE CREATE TABLE TABLENAME (ATTRIBUTE1 DATATYPE, ATTRIBUTE2 DATATYPE,....);

9. TO ALTER A TABLE & ADD A COLUMN ALTER TABLE TABLENAME ADD ATTRIBUTE DATATYPE;

10. TO VIEW THE TABLE • SELECT * FROM TABLENAME;

11. TO ALTER A TABLE AND MODIFY DATATYPE Alter table tablename modify attribute datatype; (PROVIDED THERE IS NO VALUE IN THE COLUMN)

12. TO RENAME A TABLE Rename oldtablename to newtablename;

13. TO DROP A TABLE Drop table tablename;

14. TO TRUNCATE A TABLE TRUNCATE TABLE TABLENAME;

15. To insert values into a table INSERT INTO TABLENAME VALUES(VALUE1,VALUE2,VALUE3....);

16. TO UPDATE VALUES Update tablename set column1=value1, column2=value2 where columnname=value;

17. To delete values from a table Delete from tablename where columnname=value;

18. TO SAVE CHANGES PERMANENT/ TEMPORARY COMMIT; ROLLBACK; SAVEPOINT;

19. EXAMPLES FOR COMMIT AND ROLLBACK SELECT * FROM EMP; DELETE FROM EMP; Insert into emp(empno) values(1000);

20. COMMIT; (CHANGES ARE PERMANENT) Insert into emp(empno) values(4000); DELETE FROM EMP;

21. ROLLBACK; (CHANGES MADE AFTER THE COMMIT OR DDL WILL GET ERASED)

22. EXAMPLES OF COMMIT, ROLLBACK AND SAVEPOINT UPDATE EMP SET EMPNO=4000 WHERE EMPNO=5000; SAVEPOINT A; INSERT INTO EMP(EMPNO) VALUES(1000); SAVEPOINT B;

23. INSERT INTO EMP(ENAME) VALUES(‘JAGAN’); ROLLBACK TO B; (CHANGES MADE AFTER THE ROLLBACK B WILL GET ERASED) ROLLBACK TO A; (CHANGES MADE AFTER THE ROLLBACK A WILL GET ERASED)

24. TO VIEW ROWS WITH SALARY BETWEEN 2000 & 4000 • Select ename, sal from ex16 where sal between 2000 and 4000;

25. TO VIEW PERSONS WHOSE NAME HAS LETTER “A” • Select ename from ex16 where ename like ‘%a%’;

26. TO VIEW PERSONS WHOSE NAME STARTS WITH LETTER “A” • Select ename from ex16 where ename like ‘a%’;

27. FUNCTIONS AGGREGATE FUNCTIONS

28. MAX (TO FIND THE MAXIMUM VALUE ON THE SET OF VALUES) Select max(salary) from emp;

29. MIN (TO FIND THE MINIMUM VALUE ON THE SET OF VALUES) SELECT MIN(SALARY) FROM EMP;

30. MORE EXAMPLES • SELECT ENAME, HIREDATE FROM EMP WHERE ENAME<>’RAMAN’ AND DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME=‘ASHOK’;

31. MORE EXAMPLES • SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP) ORDER BY SAL DESC;

32. MORE EXAMPLES • SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE ‘%T%’);

33. MORE EXAMPLES • SELECT ENAME, DEPTNO, JOB WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC=‘DALLAS’);

34. MORE EXAMPLES • SELECT ENAME, SAL FROM EMP WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME=‘KING’);

35. MORE EXAMPLES • SELECT DEPTNO, ENAME, JOB FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=‘SALES’);

36. MORE EXAMPLES • SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE=(SELECT MAX(HIREDATE) FROM EMP;

37. MORE EXAMPLES • SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE ‘%T%’ AND SAL>(SELECT AVG(SAL) FROM EMP));

38. MORE EXAMPLES • SELECT ENAME, DEPTNO, SAL FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL FROM EMP WHERE COMM IS NOT NULL);

39. MORE EXAMPLES • SELECT ENAME, JOB, SAL FROM EMP WHERE SAL>ANY(SELECT SAL FROM EMP WHERE JOB=‘CLERK’) ORDER BY SAL DESC;