Day1

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

SQL operators -----------------------ARITH :+,-,*,/ LOGICAL : and,or,not

---------------------sql>select * from emp; sql>select empno,ename,sal for emp; sql>select sal,empno,from emp; sql>select sal as emp_sal,empno "emp no",ename emp_name from emp; where clause: ----------------------is a conidtion on select stmt. select * from emp where sal >2000;// from table emp gives all the entrys whos salary is greater than 2000 ARITH: --------------Write a query to display the salary by giving a raise fo 10% as raised salary sql>select sal,sal*1.1 as raised_sal from emp; logical: ---------------select * from emp where sal>2000 and deptno= 20;// both the conditions have to be satisfied select * from emp where not deptno=20;\\ other that dept 20 all others are listed out select * from emp where not job="manager";\\ data stored is case sensitiveso be careful relational: ----------------

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

relational: -----------------select * from emp where sal>2000 and deptnp!=20; or select * from emp where sal>2000 and deptno<>20; MISC -----------SELECT * FROM EMP WHERE DEPTNO IN(10,20); SELECT * FROM EMP WHERE JOB IN('MANAGER','ANALYST'); // IN IS USED TO COMPARE MULTIPLE VALUES NOT IN SELECT * FROM EMP WHERE JOB NOT IN ('SALESMAN'); BETWEEN SELECT * FROM EMP WHERE SAL BETWEEN 2450 AND 5000;// THE VALUES ARE INCLUSIVE LIKE'%' SELECT ENAME FROM EMP WHERE ENAME LIKE'A%';// STARTS WITH A SELECT ENAME FROM EMP WHERE ENAME LIKE'%S';// ENDS WITH S // SUPPOSE I WANT FOUR CHARECTER NAME SELECT ENAME FROM EMP WHERE ENAME LIKE'____';// FOUR UNDERSCORES ASSIGNMENT: ----------------------ENAME --------A%NIL ANC%HAN WRITE A QUERY TO DISPLAY THE EMP NAME WITH SPECIAL CHARECTERS HINT: WILD CARDS IN SQL ARE % AND _ UPDATE EMP SET ENAME='A%NIL' WHERE EMPNO =7369; UPDATE EMP SET ENAME ='ANC%HAN' WHERE EMPNO=7788;

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

IS NULL WRITE A QUERY TO DISPLAY THE EMPLOYNAMES WHOS COMM IS NULL SELECT ENAME FROM EMP WHERE COMM IS NULL; // WHAT IS NULL? NULL IS NO VALUE SOMETHING NOT DEFINED LIKE UNDEF 2ND CODD LAW--> SYSTEMATIC HANDLING OF NULL IS REQUIRED NULL CAN BE INSERTED USING NULL OR '' BUT NOT 'NULL' OR WE CAN '&COMM'-------><ENTER> REMAINING THINGS WILL BE COVERED IN SUB QUERIES LATER SQL CLAUSES: ********************** WHERE GROUP BY AGGREGATES FNS: min() MAX() AVG() SUM() COUNT() COUNT() COUNT(*) COUNT(COLNAME) COUNT(DISTINCT COLNAME) HAVING ORDER BY WHERE CLAUSE --------------------------IS A CONDITION ON SELECT STMT. DISTINCT ------------SELECT JOB FORM EMP; SELECT DISTINCT JOB FROM EMP;// ELIMINATES DUPLICATES AND GIVES IN ASCENDING ORDER LIMITATIONS --------------------SELECT ENAME,DISTINCT JOB FROM EMP; IT SHOULD BE FIRST STATEMENT IN THE STATEMENT SELECT DISTINCT JOB,ENAME FROM EMP;

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

DISTINCT CAN NOT BE USED TO TWICE COUNT --------------COUNT(*) :INCL_DUPL+INCL NULL COUNT(COLNAME) :INCL DUPL-EXCL NULL COUNT(DISTINCT COLNAME) :EXCL DUPL-EXCL NULL --> DISTINCT OF COLNAME INCLUDES ONE NULL IF THE COLUMN HAS MANY NULLS GROUP BY -------------------SELECT MAX(SAL) FROM EMP; SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO;// GIVES MAX SAL FROM EACH DEPT SELECT MAX(SAL) FROM EMP GROUP BY JOB; // GROUP BY DOESN'T SORT THE RESULTS SELECT DEPTNO,MAX(SAL),MIN(SAL),AVG(SAL),SUM(SAL) FROM EMP GROUP BY DEPTNO; LIMITATION: -----------------what it does? first groups using the group column and gives the max of that group similarly for all other functions SELECT ENAME ,MAX(SAL) FROM EMP GROUP BY DEPTNO; ----------> ERROR CUZ SELECT ENAME ,MAX(SAL) FROM EMP GROUP BY DEPTNO,ENAME; if we use two columns and one of them doesn't use aggregates group by eliminates the duplicates and arranges the o/ps based on the columns selected in the group by HAVING ---------------IS A CONDITION ON THE GROUP BY CLASS SELECT COUNT(*),DEPTNO,MAX(SAL),MIN(SAL),AVG(SAL),SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING COUNT (*)>5; OR SELECT COUNT(*),DEPTNO,MAX(SAL),MIN(SAL),AVG(SAL),SUM(SAL) FROM EMP HAVING COUNT (*)>5 GROUP BY DEPTNO ; ORDERBY: ------------SELECT * FROM EMP ORDER BY ENAME; SELECT * FROM EMP ORDER BY ENAME DESC; SELECT * FROM EMP ORDER BY JOB,ENAME;

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

INTRODUCTION TO SQL CREATE A USER SQL OPERATORS SQL CLAUSES

Generated by Foxit PDF Creator © Foxit Software http://www.foxitsoftware.com For evaluation only.

Generated by Foxit PDF Creator © Foxit Software http://www.foxitsoftware.com For evaluation only.

Generated by Foxit PDF Creator © Foxit Software http://www.foxitsoftware.com For evaluation only.

Generated by Foxit PDF Creator © Foxit Software http://www.foxitsoftware.com For evaluation only.

Generated by Foxit PDF Creator © Foxit Software http://www.foxitsoftware.com For evaluation only.