440 likes | 730 Views
ACTION QUERIES ( SQL COMMANDS ). STRUCTURED QUERY LANGUAGE. ACTION QUERIES (SQL COMMANDS). DATA DEFINITION LANGUAGE DATA MANIPULATION LANGUAGE TRANSACTION CONTROL LANGUAGE DATA CONTROL LANGUAGE. DATA DEFINITION LANGUAGE (DDL ). CREATE ALTER DROP TRUNCATE.
E N D
ACTION QUERIES (SQL COMMANDS) STRUCTURED QUERY LANGUAGE
ACTION QUERIES (SQL COMMANDS) • DATA DEFINITION LANGUAGE • DATA MANIPULATION LANGUAGE • TRANSACTION CONTROL LANGUAGE • DATA CONTROL LANGUAGE
DATA DEFINITION LANGUAGE (DDL) • CREATE • ALTER • DROP • TRUNCATE
DATA MANIPULATION LANGUAGE (DML) • INSERT • UPDATE • DELETE
TRANSACTION CONTROL LANGUAGE (TCL) • COMMIT • ROLLBACK • SAVEPOINT
DATA CONTROL LANGUAGE (DCL) • GRANT • REVOKE
NUMBER (FLOAT) VARCHAR2 DATE 40 DIGITS +SPACE FOR A DECIMAL AND A SIGN UPTO 4000 BYTES 7 DIGITS DATA TYPES
TO CREATE A TABLE CREATE TABLE TABLENAME (ATTRIBUTE1 DATATYPE, ATTRIBUTE2 DATATYPE,....);
TO ALTER A TABLE & ADD A COLUMN ALTER TABLE TABLENAME ADD ATTRIBUTE DATATYPE;
TO VIEW THE TABLE • SELECT * FROM TABLENAME;
TO ALTER A TABLE AND MODIFY DATATYPE Alter table tablename modify attribute datatype; (PROVIDED THERE IS NO VALUE IN THE COLUMN)
TO RENAME A TABLE Rename oldtablename to newtablename;
TO DROP A TABLE Drop table tablename;
TO TRUNCATE A TABLE TRUNCATE TABLE TABLENAME;
To insert values into a table INSERT INTO TABLENAME VALUES(VALUE1,VALUE2,VALUE3....);
TO UPDATE VALUES Update tablename set column1=value1, column2=value2 where columnname=value;
To delete values from a table Delete from tablename where columnname=value;
TO SAVE CHANGES PERMANENT/ TEMPORARY COMMIT; ROLLBACK; SAVEPOINT;
EXAMPLES FOR COMMIT AND ROLLBACK SELECT * FROM EMP; DELETE FROM EMP; Insert into emp(empno) values(1000);
COMMIT; (CHANGES ARE PERMANENT) Insert into emp(empno) values(4000); DELETE FROM EMP;
ROLLBACK; (CHANGES MADE AFTER THE COMMIT OR DDL WILL GET ERASED)
EXAMPLES OF COMMIT, ROLLBACK AND SAVEPOINT UPDATE EMP SET EMPNO=4000 WHERE EMPNO=5000; SAVEPOINT A; INSERT INTO EMP(EMPNO) VALUES(1000); SAVEPOINT B;
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)
TO VIEW ROWS WITH SALARY BETWEEN 2000 & 4000 • Select ename, sal from ex16 where sal between 2000 and 4000;
TO VIEW PERSONS WHOSE NAME HAS LETTER “A” • Select ename from ex16 where ename like ‘%a%’;
TO VIEW PERSONS WHOSE NAME STARTS WITH LETTER “A” • Select ename from ex16 where ename like ‘a%’;
FUNCTIONS AGGREGATE FUNCTIONS
MAX (TO FIND THE MAXIMUM VALUE ON THE SET OF VALUES) Select max(salary) from emp;
MIN (TO FIND THE MINIMUM VALUE ON THE SET OF VALUES) SELECT MIN(SALARY) FROM EMP;
MORE EXAMPLES • SELECT ENAME, HIREDATE FROM EMP WHERE ENAME<>’RAMAN’ AND DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME=‘ASHOK’;
MORE EXAMPLES • SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP) ORDER BY SAL DESC;
MORE EXAMPLES • SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE ‘%T%’);
MORE EXAMPLES • SELECT ENAME, DEPTNO, JOB WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC=‘DALLAS’);
MORE EXAMPLES • SELECT ENAME, SAL FROM EMP WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME=‘KING’);
MORE EXAMPLES • SELECT DEPTNO, ENAME, JOB FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=‘SALES’);
MORE EXAMPLES • SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE=(SELECT MAX(HIREDATE) FROM EMP;
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));
MORE EXAMPLES • SELECT ENAME, DEPTNO, SAL FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL FROM EMP WHERE COMM IS NOT NULL);
MORE EXAMPLES • SELECT ENAME, JOB, SAL FROM EMP WHERE SAL>ANY(SELECT SAL FROM EMP WHERE JOB=‘CLERK’) ORDER BY SAL DESC;