Day1 Sql

[email protected] +91 99720 15559 http://10.177.55.196:5560/ISQLPLUS TNSNAMES.ORA --------------------------C:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN LISTENER.ORA C:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN DEFAULT ---------------ADMIN -----------SYS -> CHANGE_ON_INSTALL SYSTEM -> MANAGER USER ---------SCOTT -> TIGER 'WORKING ON SQL' ----------------------------1. isqlplus

-> HTTP://LOCALHOST:5560/isqlplus

2. SQL*PLUS -> START -----> SQL*PLUS 3. DOS

-> C:/> SQLPLUS SCOTT/TIGER@ORCL

LOGIN AS ADMIN -> FOR UNLOCKING THE SCOTT ACCOUNT ------------------------SQL> SHO USER USER is "SYSTEM" SQL> ALTER USER SCOTT ACCOUNT UNLOCK; User Altered. SQL> CONN SCOTT/TIGER TIGER TIGER Connected. SQL> SQL> HELP INDEX

-> SQL*PLUS COMMANDS ARE FORMATTING COMMANDS OR REPORTING COMMANDS. -> NO SEMICOLON REQUIRED SQL> HELP SQL> SELECT * FROM EMP; SQL> SET PAGESIZE 100 SQL> SET LINESIZE 100 SQL> / SQL> ED

-> REPEATS/RUNS PREV EXEC SQL STMT. -> AFIEDT.BUF

SESSION: SET OPTIONS ARE VALID FOR A SESSION. SQL> SELECT * FROM EMP; SQL> SAVE Q5 -> ORACLE_HOME/BIN -> FILE -> OPEN SQL> GET Q5.sql ------SQL> / SQL> @ Q5.sql OR SQL> STA Q5.sql TO CREATE A USER: -----------------------------LOGIN AS ADMIN SQL> CONN SYSTEM/ORACLE CREATE USER IDENTIFIED BY ; SQL> CREATE USER ANIL IDENTIFIED BY ANCHAN; SQL> CONN ANIL/ANCHAN --> ERROR LOGIN AS ADMIN SQL> GRANT CONNECT,RESOURCE TO ANIL; ---> ROLE -> SET OF PRIVILEGES SQL> CONN ANIL/ANCHAN Connected. DATA DICTIONARY VIEWS: (METADATA) --------------------------------------TAB USER_TABLES ------SQL> SELECT * FROM TAB; No rows selected. SQL> CONN SCOTT/TIGER SQL> SELECT * FROM TAB; EMP DEPT SALGRADE -> DEMOBLD.sql BONUS DUMMY SQL> CONN ANIL/ANCHAN TO BUILD THE DEMO TABLES: ---------------------------------------------

SQL> @ C:\Oracle\product\10.2.0\db_1\odp.net\samples\DataSet\RelationalData\setup\DEMOBLD .sql OR SQL> STA C:\Oracle\product\10.2.0\db_1\odp.net\samples\DataSet\RelationalData\setup\DEMOBLD .sql SQL> SELECT * FROM TAB; EMP DEPT QUERYING THE DATABASE: WRT 1 TABLE ----------------------------------------- --------------------

ARITH LOGICAL RELATIONAL MISC

SQL OPERATORS --------------------------: +,-,*,/ : AND,OR,NOT : <,>,=,<=,>=,!= OR <> : IN,NOT IN,BETWEEN,LIKE'%',IS NULL, ANY,ALL,EXISTS,NOT EXISTS,.....

SQL> SELECT * FROM EMP; SQL> SELECT EMPNO,ENAME,SAL FROM 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 CONDITION ON SELECT STMT. SELECT * FROM EMP WHERE SAL>2000; ARITH: ------------WRITE A QUERY TO DISPLAY THE SAL BY GIVING A RAISE OF 10% AS RAISE_SAL: -----------------------------SELECT SAL,SAL+SAL*.10 AS RAISE_SAL FROM EMP; LOGICAL: --------------SELECT * FROM EMP WHERE SAL > 2000 AND DEPTNO = 20; SELECT * FROM EMP WHERE SAL > 2000 OR DEPTNO = 20; SELECT * FROM EMP WHERE NOT DEPTNO = 20; SELECT * FROM EMP WHERE NOT JOB = 'MANAGER'; RELATIONAL: -------------------SELECT * FROM EMP WHERE SAL > 2000 AND DEPTNO != 20; OR SELECT * FROM EMP WHERE SAL > 2000 AND DEPTNO <> 20;

MISC --------IN SELECT * FROM EMP SELECT * FROM EMP NOT IN SELECT * FROM EMP BETWEEN SELECT * FROM EMP LIKE'%' SELECT ENAME FROM SELECT ENAME FROM SELECT ENAME FROM SELECT ENAME FROM

WHERE DEPTNO IN(10,20); WHERE JOB IN('MANAGER','ANALYST'); WHERE JOB NOT IN('SALESMAN'); WHERE SAL BETWEEN 2450 AND 5000; EMP EMP EMP EMP

WHERE WHERE WHERE WHERE

ENAME ENAME ENAME ENAME

LIKE'A%'; LIKE'%S'; LIKE'A%S'; LIKE'____';->4 CHAR

CLASS ASSIGNMENT: -------------------------------ENAME -----------A%NIL ANC%HAN WRITE A QUERY TO DISPLAY THE ENAMES WITH SPECIAL CHARS IN IT. HINT : WILD SQL> UPDATE SQL> UPDATE WHERE

CARDS: %,_ EMP SET ENAME = 'A%NIL' WHERE EMPNO=7369; EMP SET ENAME = 'ANC%HAN' EMPNO=7788;

SQL> SELECT * FROM EMP WHERE ENAME LIKE '%X%%' ESCAPE 'X'; IS NULL -------------WRITE A QUERY TO DISPLAY THE EMP ENAMES WHOSE COMM ARE NULL: ------------------------------------------------------------------------------------------SELECT ENAME,COMM FROM EMP WHERE COMM = NULL; ---> WRONG SELECT ENAME,COMM FROM EMP WHERE COMM IS NULL; ---> RIGHT WHAT IS NULL? NULL IS DEFINED AS NO VALUE. NOTE: ----------NULL '' '&COMM' ----> <ENTER> SQL CLAUSES: ---------------------WHERE GROUP BY AGGREGATE 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 --------------ELIMINATES DUPLICATES. ARRANGE THE OUTPUT IN ASCENDING ORDER. SQL> SELECT JOB FROM EMP; SQL> SELECT DISTINCT JOB FROM EMP; SQL> SELECT DISTINCT JOB,ENAME FROM EMP; LIMITATIONS: ----------------------SELECT ENAME,DISTINCT JOB FROM EMP; SELECT DISTINCT ENAME,DISTINCT JOB FROM EMP; COUNT() COUNT(*) : INCL DUPL + INCL NULL COUNT(COLNAME) : INCL DUPL - EXCL NULL COUNT(DISTINCT COLNAME) : EXCL DUPL - EXCL NULL SELECT COUNT(*),COUNT(MGR),COUNT(DISTINCT MGR) FROM EMP; COUNT(*) COUNT(MGR) COUNT(DISTINCTMGR) ---------------------------------13 12 5 NOTE:

DISTINCT COLNAME IF COL HAS MANY NULLS

-> INCL 1 NULL

GROUP BY: ----------------ELIMINATES DUPLICATES GROUPS THE OUTPUT BASED ON THE COLUMN SELECTED IN THE GROUP BY. SELECT MAX(SAL) FROM EMP; 5000 SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO; SELECT MAX(SAL) FROM EMP GROUP BY JOB; SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO; SELECT COUNT(*),DEPTNO,MAX(SAL),MIN(SAL),AVG(SAL), SUM(SAL) FROM EMP GROUP BY DEPTNO; LIMITATIONS

-------------------SELECT ENAME,MAX(SAL) FROM EMP GROUP BY DEPTNO; --> ERROR SELECT ENAME,MAX(SAL) FROM EMP GROUP BY ENAME; --> SOLN OR SELECT ENAME,MAX(SAL) FROM EMP GROUP BY DEPTNO,ENAME; --> SOLN HAVING ------------IS A CONDITION ON THE GROUP BY CLAUSE. SELECT COUNT(*),DEPTNO,MAX(SAL),MIN(SAL),AVG(SAL), SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING COUNT(DEPTNO)>=5; OR 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 ; ORDER BY: ---------------ARRANGE THE OUTPUT IN ASCENDING ORDER(DEFAULT) SELECT * FROM EMP ORDER BY ENAME; SELECT * FROM EMP ORDER BY ENAME DESC; SELECT * FROM EMP ORDER BY JOB,ENAME;