Basic SQL (DDL-DML-DCL-TCL)

SQL (Structured Query Language)

MS SQL Server

ORACLE

TeraData

DB2

MySQL

Default Tables in SQL:

Note: if you create in SQL Server the use following 2 statements required other DB's not required these 2 statements.

Create Database Test
use Test

CREATE TABLE EMP (
EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATETIME,
SAL NUMERIC(7, 2),
COMM NUMERIC(7, 2),
DEPTNO NUMERIC(2)
)

select * from EMP;

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20)
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30)
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30)
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20)
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30)
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30)
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10)
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20)
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10)
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30)
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20)
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30)
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20)
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10)

CREATE TABLE DEPT (
DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) )

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK')
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS')
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO')
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON')

-------------------
Basic SQL in MS SQL Server

Note: All Default Tables SQL Script available on this link.

Selecting data from entire table:
Syntax: Select * from <Table_Name>
Ex: Select * from emp (Retrieves records of all the employees)
Selecting data from particular columns:
Syntax: Select <col1>, <col2>, …. <coln> from <Table_Name>
Ex: Select empno, ename from emp
Using Arithmetic operators
Operator Description
+ Add
- Subtract
* Multiply
/ Divide

Arithmetic expressions:
Arithmetic expressions are used in queries to retrieve the numeric or date data based on the requirement
Syntax: Select <col1>, <col2+col3>, <col3-2>, <col4*<col5>>, <Col5/3> from <Table_Name>
Ex: Select empno, ename, sal+comm, sal*0.2 from emp
Operator precedence:
• If an arithmetic expression contains more than one operator, multiplication and division are evaluated first. If operators in an expression are of the same priority, then evaluation is done from left to right.
• You can use parentheses to force the expression that is enclosed by parentheses to be evaluated first.
• Rules of Precedence:
• Multiplication and division occur before addition and subtraction.
• Operators of the same priority are evaluated from left to right.
• Parentheses are used to override the default precedence or to clarify the statement.
Examples:
select empno, ename, (sal+comm)*0.2 from emp
select empno, ename, sal+comm, sal*0.2 from EMP
select empno, ename, sal+comm, sal*0.2, sal/100 from EMP
select empno, ename, sal+comm, sal*0.2, sal/100, sal-comm from EMP
Column Aliases:
• Renames a column heading
• Is useful with calculations
• Immediately follows the column name (There can also be the optional AS keyword between the column name and alias.)
Syntax 1: Select <col1> newcol from <table_name>
Syntax 2: Select <col1> as newcol from <table_name>
Syntax 3: Select <col1> ‘New Col’ from <table_name>
Syntax 4: Select <col1> “New Col” from <table_name>
Examples:
Select empno, ename, (sal+comm)*0.2 newsal from emp
select empno, ename, sal+comm salcomm, sal*0.2 salmul from EMP
select empno, ename, sal+comm as salcomm, sal*0.2 as salmul from EMP
select empno, ename, sal+comm 'salcomm', sal*0.2 'salmul' from EMP
select empno, ename, sal+comm as 'salcomm', sal*0.2 as 'salmul' from EMP
select empno, ename, sal+comm "salcomm", sal*0.2 'salmul' from EMP
select empno, ename, sal+comm as "salcomm", sal*0.2 'salmul' from EMP
Concatenation operator:
• Links columns or character strings to other columns
• Is represented by +
• Creates a resultant column that is a character expression
Syntax: Select <col1>+<col2> from <table_name>
Ex: Select ename+job ‘name_job’ from emp
Literal character strings:
• Links columns or character strings to other columns
• Is represented by +
• Creates a resultant column that is a character expression
Syntax: Select <col1>+ ‘literal_string’ +<col2> from <table_name>
Ex: Select ename+ ‘is a’ + job Designation from emp
Ex: select ename+ space(5) +job as 'new_job' from EMP 'both string fields
Ex: select ename+ space(5) +STR(sal) as 'new_job' from EMP 'one is string field another is num field

Usage of ‘Distinct’:
• Avoids duplicate data to be retrieved
Syntax: Select distinct <col1> from <table_name>
Ex: select distinct job as 'available_designations' from EMP
Usage of 'Where' clause to restrict the retrieval based on conditions:
• Using “Where” clause data can be retrieved specific to a particular condition
Syntax: Select * from <table_name> where <Condition>
Ex: Select * from emp where deptno = 10
Character-Based Conditions:
• Conditions determining which rows are selected based on character data, are specified by enclosing character literals in the conditional clause, within single quotes.
Ex: Select * from emp where ename = ‘smith’
• Below query throws an error:
• Select * from emp where ename = smith
• Below query does not return any value as there is no value as such:
Ex: Select * from emp where ename = ‘SMITH’
Date-Based Conditions:
• Date columns are useful when sorting date and time information. Like Character literals Date literals must also be specified by enclosing within single quotes.
Ex: Select * from EMP where HIREDATE = '1980-12-17' 'character based condition
Relational operators / Comparison Operators:
Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to
Between…and Between two values (inclusive)
IN(set) Match any of a list of values
Like Match a character pattern
IS NULL Is a null value
Comparison Conditions:
• Comparison conditions are used in conditions that compare one expression to another value or expression. They are used in the WHERE clause in the following format:

Syntax: ... WHERE expr operator value

Example:
... WHERE hire_date = '01/20/95'
... WHERE salary >= 6000
... WHERE last_name = 'Smith'

• An alias cannot be used in the WHERE clause.

Using Comparison Conditions:

Examples:

select * from EMP where sal = 1250
select * from EMP where sal < 1300
select * from EMP where sal > 1600
select * from EMP where sal <=1250
select * from EMP where sal >=1250
select * from EMP where sal <> 800
-----
Select * FROM emp WHERE job <> ('CLERK');
Select * FROM emp WHERE job NOT IN ('CLERK');
-----
Using BETWEEN…AND Condition:
• The BETWEEN operator tests whether a column or expression value falls within a range of two boundary values.
Ex: Select ename FROM emp
WHERE sal BETWEEN 800 AND 2000
Ex: select * from EMP where SAL between 800 AND 1250;
Using IN Conditions:
• The IN operator tests whether an item is a member of a set of literal values. The set is specified by a comma separating the literals and enclosing them in round brackets.
Examples:
Select * FROM emp WHERE ename in (‘smith’, ‘Allen’, ’Ward’)
select ename from EMP where sal in (800, 1200, 1600)
select * from EMP where JOB in ('MANAGER', 'ANALYST')
select * from EMP where HIREDATE in ('1981-04-02', '1981-06-09')

Using LIKE Conditions:
• LIKE is accompanied by two wildcard characters: the percentage symbol (%) and the underscore character (_). The percentage symbol is used to specify zero or more wildcard characters, while the underscore character specifies one wildcard character.
• A wildcard may represent any character.
Examples:
Select * FROM emp WHERE ename like ‘%mi%’
Select * FROM emp WHERE ename like ‘_mith%’
select * from EMP where ENAME like 'a%';
select * from EMP where ename like '%l%';
select * from EMP where ENAME like '_MITH%'
select * from EMP where ENAME like '___g'
Using IS NULL Conditions:
• The IS NULL operator selects only the rows where a specific column value is NULL.
Examples:
Select * FROM emp WHERE comm IS NULL
select * from EMP where COMM is null
select * from EMP where COMM is not null

Boolean Operators:
• Boolean or logical operators enable multiple conditions to be specified in the WHERE clause of the SELECT statement.
Operator Meaning
AND Returns TRUE if both component conditions are true.
OR Returns TRUE if either component conditions are true.
NOT Returns TRUE if following condition is false.
Using AND Operator:
• The AND operator merges conditions into one larger condition to which a row must conform to be included in the results set.
Example:
Select * FROM emp WHERE sal = 800 AND hiredate = ‘2012-10-28’
select * from emp where JOB = 'MANAGER' and sal > 2000 /*both condiction satisifed */
Using OR Operator:
• The OR operator separates multiple conditions, at least one of which must be satisfied by the row selected to warrant inclusion in the results set.
Example:
Select * FROM emp WHERE sal = 800 OR hiredate = ‘2012-10-28’;
select * from EMP where SAL = 800 OR HIREDATE = '1981-06-09'
select * from EMP where SAL = 900 OR HIREDATE = '1981-06-09'
Using NOT Operator:
• The NOT operator negates conditional operators. A selected row must conform to the logical opposite of the condition in order to be included in the results set.
Example:
Select * FROM emp WHERE sal NOT IN (800, 2000, 1000);
select * from EMP where ENAME not in ('martin','jones','adams')
Rules of Precedence:
• Bracketed expressions are evaluated before multiplication and division operators, which are evaluated before subtraction and addition operators.
Operator Meaning
() Parentheses or brackets
(/,*), (+, -), || Arithmetic Operators in the given sequence
=,<,>,<=,>= Comparison Operators
IS [NOT] NULL, LIKE, [NOT] IN Pattern, null, and Set comparison
[NOT] BETWEEN Range comparison
!=,<> Not equal to
Not Not logical condition
And And logical condition
OR Or logical condition

Example 4 lines: /* Order of Precedency */

Select ename FROM emp
WHERE ename like '%r%' and sal > deptno * (20 +1000)
OR
job in ('clerk','analyst') and comm is null
Using the ORDER BY Clause:
Sort retrieved rows with the ORDER BY clause:
• ASC: ascending order, default
• DESC: descending order
• The ORDER BY clause comes last in the SELECT statement:
Ex:
select *from emp order by sal;
Sorting:
• Sorting in descending order:
Ex: Select * from emp where sal>2000 order by hiredate desc
• Sorting by column alias:
Ex: Select empno, ename (sal+comm)*12 as ‘Annual salary’ from emp order by ‘Annual salary’
• Sorting by multiple columns:
Examples:
Select * from emp order by deptno, sal
select *from emp order by deptno;
or
select *from emp order by deptno asc
-------
ASC is default
DESC
-------
select *from emp order by deptno desc
select *from emp order by comm
select *from emp order by comm desc
select *from emp order by deptno, job
select *, (sal+comm) as salcomm from emp order by sal

**************************************************************************

SQL Server

1. PL-SQL Exercises
----------------------------------------------------------------------------------------------------
DECLARE
N1 NUMBER;
N2 NUMBER;
ANS NUMBER;
BEGIN
N1 := 10;
N2 := 20;
ANS := N1 + N2;
DBMS_OUTPUT.PUT_LINE('The Sum is : ' || ans);
END;
/
----------------------------------------------------------------------------------------------------
DECLARE
N1 NUMBER;
N2 NUMBER;
ANS NUMBER;
BEGIN
N1 := &N1;
N2 := &N2;
ANS := N1 + N2;
DBMS_OUTPUT.PUT_LINE('The Sum is : ' || ans);
END;
/
----------------------------------------------------------------------------------------------------
DECLARE
N1 NUMBER;
N2 NUMBER;
ANS NUMBER;
BEGIN
N1 := &N1;
N2 := &N2;

IF N1 > N2 THEN
DBMS_OUTPUT.PUT_LINE(N1 || ' is Bigger');
ANS := N1 - N2;
ELSE
DBMS_OUTPUT.PUT_LINE(N2 || ' is Bigger');
ANS := N2 - N1;
END IF;
DBMS_OUTPUT.PUT_LINE('The Difference is : ' || ans);
END;
/
----------------------------------------------------------------------------------------------------
DECLARE
NUM NUMBER;
BEGIN
FOR NUM IN 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE(NUM);
END LOOP;
END;
/
----------------------------------------------------------------------------------------------------
DECLARE
NUM NUMBER;
BEGIN
FOR NUM IN REVERSE 1 .. 5 LOOP
DBMS_OUTPUT.PUT_LINE(NUM);
END LOOP;
END;
/
----------------------------------------------------------------------------------------------------
DECLARE
NUM NUMBER;
BEGIN
NUM := &NUM;
IF MOD(NUM,2) = 0 THEN
GOTO even;
ELSE
GOTO odd;
END IF;
<<odd>>
DBMS_OUTPUT.PUT_LINE('The Number is ODD');
return;
<<even>>
DBMS_OUTPUT.PUT_LINE('The Number is EVEN');
END;
/
----------------------------------------------------------------------------------------------------
DECLARE
NUM NUMBER := 0;
BEGIN
WHILE NUM <= 10 LOOP
NUM := NUM + 1;
DBMS_OUTPUT.PUT_LINE(NUM);
END LOOP;
END;
/
----------------------------------------------------------------------------------------------------
DECLARE
NUM NUMBER := 0;
BEGIN
LOOP
NUM := NUM + 1;
DBMS_OUTPUT.PUT_LINE(NUM);
EXIT WHEN NUM = 12;
END LOOP;
END;
/
----------------------------------------------------------------------------------------------------
DECLARE
i number:=0;
BEGIN
while i<=10
LOOP
dbms_output.put_line(i);
i:=i+2;
END LOOP;
END;
-----------------------------------------------------------------------------------------
BEGIN
for i in 1..5
LOOP
for j in 1..10
LOOP
dbms_output.put_line(i||' * '||j||' = '||i*j);
END LOOP;
dbms_output.put_line('------------------------');
END LOOP;
END;
-----------------------------------------------------------------------------------------
DECLARE
p number;
t number;
r number;
si number(6,2);
BEGIN
p:=&p;
t:=&t;
r:=&r;
si:=p*t*r/100;
dbms_output.put_line('the simple int is '|| si);
END;
-----------------------------------------------------------------------------------------
DECLARE
a number;
b number;
c number;
BEGIN
a :=&a;
b :=&b;
c :=&c;

if( a>b AND a>c )then
dbms_output.put_line('a is bigger '||a);
else
if(b>c AND b>a) then
dbms_output.put_line('b is bigger '||b);
else
dbms_output.put_line('c is bigger '||c);
END if;
end if;
END;

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

DECLARE
no emp.empid%type;
name emp.ename%type;
sal emp.salary%type;
ansal number(15);
BEGIN
no := &empno;
SELECT ename, salary INTO name, sal FROM emp WHERE empid like no;
ansal := sal * 12;
dbms_output.put_line('Annual Salary is : ' || ansal);
END;
-----------------------------------------------------------------------------------------
DECLARE
eno emp.empid%type;
name emp.ename%type;
BEGIN
eno := &empno;
SELECT ename INTO name FROM emp WHERE empid like eno;
dbms_output.put_line(ename);
END;
-------------------------------------------------------------------------------------
DECLARE
dno emp.deptno%type;
sal emp.sal%type;
dna dept.dname%type;
nsal number(15);
BEGIN
dno := &deptno;
SELECT sum(sal) INTO nsal FROM emp GROUP BY deptno where deptno LIKE dno;
SELECT dname INTO dna FROM dept WHERE deptno LIKE dno;
dbms_output.put_line(' Department Name : ' ||dna ||' Sum of salary : ' ||nsal);
END;
-----------------------------------------------------------------------------------------
DECLARE
var_empno NUMBER(4) ;
var_comm emp.comm%TYPE;
BEGIN
var_ empno:=&a;
SELECT comm INTO var_comm FROM emp
WHERE empid = var_empno;

IF var_comm IS NULL THEN
UPDATE emp SET comm = 300
WHERE empid = var_empno;
ELSE
var_comm := var_comm + var_comm * 25;
UPDATE emp SET comm = var_comm
WHERE empid = var_empno;
END IF;
dbms_output.put_line('Commission for ' || var_empno || ' has been updated to ' || var_comm);
END;
-----------------------------------------------------------------------------------------
DECLARE
mgr_num emp.mgr%TYPE;
emp_num emp.empid%TYPE;
emp_name emp.ename%TYPE;
mgr_name emp.ename%TYPE;
start_num NUMBER(4) := 7274;

BEGIN
SELECT empid, ename, mgr
INTO emp_num, emp_name, mgr_num
FROM emp
WHERE empid = start_num;

LOOP
SELECT empid, mgr, ename
INTO emp_num, mgr_num, mgr_name
FROM emp
WHERE empid = mgr_num;

dbms_output.put_line(start_num ||' '|| emp_name ||' '|| mgr_name);

EXIT WHEN mgr_num IS NULL;

start_num := emp_num;
emp_num := mgr_num;
emp_name := mgr_name;
END LOOP;

dbms_output.put_line(emp_num ||' '|| mgr_name);

END;
-----------------------------------------------------------------------------------------
DECLARE
a emp.ename%TYPE;
b emp.desig%TYPE;
c emp.salary%TYPE;
BEGIN
SELECT ename,desig,salary INTO a,b,c FROM emp WHERE ename like '&ename';
dbms_output.put_line(A||' '||B||' '||C);
END;
-----------------------------------------------------------------------------------------
DECLARE
R emp%ROWTYPE;
BEGIN
SELECT * INTO R FROM emp WHERE ename like '&ename';
dbms_output.put_line(R.ename||' '||R.desig||' '||R.salary||' '||R.deptno||' '||R.mgr);
END;

-----------------------------------------------------------------------------------------

2. Functions
-----------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION getSq(num IN number)
RETURN number
IS
sq number;
BEGIN
sq := sqrt(num);
RETURN sq;
END;

SQL> SELECT getsq(9) FROM DUAL;

-----------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION getNames(eno emp.empid%TYPE)
RETURN varchar
IS
enm emp.ename%TYPE;
BEGIN
SELECT ename INTO enm FROM emp WHERE empid=eno;
RETURN enm;
end;
-----------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION getSal(eno IN NUMBER)
RETURN NUMBER AS
sal number;
BEGIN
SELECT salary INTO sal FROM EMP WHERE empid LIKE eno;
RETURN sal;
END;
-----------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION getDept(name emp.ename%TYPE)
return number IS
dno emp.deptno%TYPE;
BEGIN
SELECT deptno INTO dno FROM emp WHERE ename = upper(&name);
return dno;
END;

-----------------------------------------------------------------------------------------

3. Procedures
-----------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE getName(eid emp.empid%TYPE)
IS
name emp.ename%TYPE;
BEGIN
SELECT ename INTO name FROM EMP WHERE empid like eid;
dbms_output.put_line('Employee Name is : ' || name);
END;
-----------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE getName(EID emp.empid%TYPE, name OUT emp.ename%TYPE)
IS
BEGIN
SELECT ename INTO name FROM EMP WHERE empid = eid;
END;

~~~~~

DECLARE
eid emp.empid%TYPE;
enm emp.ename%TYPE;
BEGIN
getName(&eid, enm);
dbms_output.put_line('Employee Name is : ' || enm);
END;
-----------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE totsal(dno IN integer, tsal OUT integer, eno OUT integer)
IS
BEGIN
SELECT count(*) INTO eno FROM emp WHERE deptno = dno;
SELECT sum(salary) INTO tsal FROM emp WHERE deptno = dno;
END;

~~~~~

DECLARE
ts integer;
no integer;
BEGIN
totsal(&dept, ts, no);
dbms_output.put_line('Total Salary : ' || ts);
dbms_output.put_line('Total Employees : ' || no);
END;

-----------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE updt(a VARCHAR,b NUMBER)
AS
BEGIN
UPDATE emp SET ename=a WHERE empid LIKE b;
END;
-----------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE ins(X NUMBER,Y varchar2)
AS
BEGIN
INSERT INTO emp(SAL, DESIG) VALUES(X,Y,SYSDATE);
END;
-----------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE del(a number)
AS
BEGIN
DELETE FROM emp WHERE comm=a;
END;

-----------------------------------------------------------------------------------------

4. Exceptions
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section

EXCEPTION
WHEN exception_name1 THEN
[statements]

WHEN exception_name2 THEN
[statements]

. . .

WHEN OTHERS THEN
[statements]

END [procedure_name];

-------------------------------------------------------------------------------------------------
DECLARE
a varchar2(10);
b varchar2(10);
c varchar2(10);

BEGIN
SELECT ename, job, sal INTO a,b,c FROM emp WHERE deptno= '&deptno';
dbms_output.put_line(A||' '||B||' '||C);

EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('USE CURSOR');

END;
-------------------------------------------------------------------------------------------------
DECLARE
eno emp.empno%TYPE;
name emp.ename%TYPE;

BEGIN
eno := &num;
SELECT ename INTO name FROM emp WHERE empno LIKE eno;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('NO DATA FOUND !!!');

END;
-------------------------------------------------------------------------------------------------
DECLARE
num1 integer;
num2 integer;
exp EXCEPTION;

BEGIN
num1 := &n1;
num2 := &n2;

IF num1 > num2 THEN
dbms_output.put_line('NUM1 is Greater');
ELSE
RAISE exp;
END IF;

EXCEPTION
WHEN exp THEN
dbms_output.put_line('NUM2 is Greater');

END;
-----------------------------------------------------------------------------------------
DECLARE
eno number;
excp exception;
eid emp.empid%TYPE;

BEGIN
eno := &en;

SELECT empid INTO eid FROM emp WHERE empid=eno;

IF(eno = empid) THEN
select ename from emp where empid = eno;
dbms_output.put_line(ename);
ELSE
RAISE excp;
END IF;

EXCEPTION
WHEN excp THEN
dbms_output.put_line(' id doesnt match');

END;
-----------------------------------------------------------------------------------------
DECLARE
eno number;
excp exception;
eid emp.empid%TYPE;

BEGIN
eno := &en;

SELECT empid INTO eid FROM emp WHERE empid=eno;

IF(eid = eno) THEN
dbms_output.put_line('Emp ID : ' || eid);
dbms_output.put_line('Emp No : ' || eno);
ELSE
RAISE excp;
END IF;

EXCEPTION
WHEN excp THEN
dbms_output.put_line('id do not match');

END;
-----------------------------------------------------------------------------------------

5. Triggers
-----------------------------------------------------------------------------------------
CREATE TABLE orders (
somecolumn VARCHAR2(20),
numbercol NUMBER(10)
);

CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
ON orders
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders SET somecolumn = 'XYZ';
-----------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders

DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' When Inserting');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' When Updating');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' When Deleting');
END IF;
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders SET somecolumn = 'DEF' WHERE ROWNUM = 1;

DELETE FROM orders WHERE ROWNUM = 1;
-----------------------------------------------------------------------------------------

CREATE OR REPLACE TRIGGER fri_trig
BEFORE INSERT OR UPDATE OR DELETE
ON emp
BEGIN
if(to_char(sysdate,'dy')='fri') then
Raise_Application_Error(-20001,'Not Possible Because day is friday');
end if;
END;
-----------------------------------------------------------------------------------------

CREATE OR REPLACE TRIGGER UCASE
BEFORE INSERT OR UPDATE OF ename
ON emp
FOR EACH ROW
BEGIN
:NEW.ename := upper(:NEW.ename);
END;
-----------------------------------------------------------------------------------------

CREATE TABLE t (
rid NUMBER(5),
col VARCHAR2(3));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid)
USING INDEX;

CREATE SEQUENCE seq_t;

CREATE OR REPLACE TRIGGER row_level
BEFORE INSERT
ON t
FOR EACH ROW

BEGIN
SELECT seq_t.NEXTVAL
INTO:NEW.rid
FROM dual;
dbms_output.put_line(:NEW.rid);
END row_level;
/

INSERT INTO t (col) VALUES ('A');
INSERT INTO t (col) VALUES ('B');
INSERT INTO t (col) VALUES ('C');

SELECT * FROM t;

-----------------------------------------------------------------------------------------
ALTER TABLE <table_name> DISABLE ALL TRIGGERS;
ALTER TABLE <table_name> ENABLE ALL TRIGGERS;
ALTER TRIGGER <trigger_name> ENABLE;
ALTER TRIGGER <trigger_name> RENAME TO <new_name>;
DROP TRIGGER <trigger_name>;

-----------------------------------------------------------------------------------------
Unfortunately you can't use SYSDATE in a check constraint as it is a dynamic Oracle variable. A better way to enforce this constraint would be to use a trigger. For example:

CREATE OR REPLACE TRIGGER trg_emp_dob BEFORE INSERT OR UPDATE ON emp
BEGIN
IF :new.dob > SYSDATE THEN
RAISE_APPLICATION_ERROR (num => -20000, msg => 'DOB Cannot be in the future');
END IF;

END;

6. Cursors
-----------------------------------------------------------------------------------------
DECLARE
name emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT ename FROM emp;

BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO name;
CLOSE emp_cursor;

dbms_output.put_line(name);

END;
-----------------------------------------------------------------------------------------
DECLARE
name emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT ename FROM emp;

BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO name;
dbms_output.put_line(name);
EXIT WHEN emp_cursor%NOTFOUND;
END LOOP;
CLOSE emp_cursor;

dbms_output.put_line(name);

END;
-----------------------------------------------------------------------------------------
DECLARE
name emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT ename FROM emp;

BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO name;
IF emp_cursor%FOUND THEN
dbms_output.put_line(name);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE emp_cursor;
END;
-----------------------------------------------------------------------------------------
DECLARE
name emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT ename FROM emp;

BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO name;
IF emp_cursor%ROWCOUNT > 10 THEN
commit;
END IF;
end loop;
CLOSE emp_cursor;

dbms_output.put_line('name');

END;
-----------------------------------------------------------------------------------------
DECLARE CURSOR emp IS
SELECT ename, JOB, SAL FROM emp WHERE deptno = &deptno;

A VARCHAR2(10);
B VARCHAR2(10);
C EMP.SAL%TYPE;

BEGIN
OPEN emp;
LOOP
dbms_output.put_line(A||' '||B||' '||C);
FETCH emp INTO A,B,C ;
EXIT WHEN emp%NOTFOUND;
END LOOP;
CLOSE emp;
END;
-----------------------------------------------------------------------------------------
DECLARE CURSOR emp2(job emp.JOB%TYPE)
IS
SELECT ename, JOB, SAL FROM emp WHERE JOB LIKE upper('&job');
A VARCHAR2(10);
B VARCHAR2(10);
C NUMBER(10);
job emp.JOB%TYPE;
BEGIN
job:=job;
OPEN emp2(job);
LOOP
dbms_output.put_line(A||' '||B||' '||C);
FETCH emp2 INTO A,B,C ;
EXIT WHEN emp2%NOTFOUND;
END LOOP;
CLOSE emp2;
END;
-----------------------------------------------------------------------------------------
DECLARE CURSOR emp3 is
SELECT * FROM dept;
BEGIN
FOR i IN emp3
LOOP
dbms_output.put_line(i.DEPTNO||' '||i.Dname||' '||i.LOC);
END LOOP;
END;

-----------------------------------------------------------------------------------------

7. Package
-----------------------------------------------------------------------------------------
create or replace package sqrcube as
procedure sqr(a in number,b out number) ;
function cube (a number) return number ;
end;
/

create or replace package body sqrcube as
procedure sqr(a in number,b out number) is
begin
b:=a*a;
end sqr;

function cube (a number) return number is
cb number;
begin
cb:=a*a*a;
return cb;
end cube;
end sqrcube;
/

declare
sq number(7);
cb number(7);
n number(3);
begin
n:=&num;
sqrcube.sqr(n,sq);
cb:=sqrcube.cube(n);
dbms_output.put_line('Square of ' || n || ' is : '||sq);
dbms_output.put_line('Cube of ' || n || ' is : '||cb);
end;
/

-----------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE emps AS
procedure setSal(name emp.ename%TYPE);
function getName(eno emp.empno%TYPE) return VARCHAR ;
END;
/

CREATE OR REPLACE PACKAGE BODY emps AS
PROCEDURE setSal(name emp.ename%TYPE)
IS
BEGIN
UPDATE emp SET sal = 20000 WHERE ename LIKE name;
dbms_output.put_line(' DATA UPDATED !!!');
END setsal;

FUNCTION getName(eno emp.empno%TYPE)
RETURN VARCHAR
AS
name emp.ename%TYPE;
BEGIN
SELECT ename INTO name FROM emp WHERE empno LIKE eno;
RETURN name;
END getname;

END emps;
/

-----------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE pkgdem AS
function getName(eno emp.empno%TYPE) return VARCHAR ;
procedure sumSal(str VARCHAR);
END;
/

CREATE OR REPLACE PACKAGE BODY pkgdem AS
FUNCTION getName(eno emp.empno%TYPE)
RETURN VARCHAR
AS
name emp.ename%TYPE;
BEGIN
SELECT ename INTO name FROM emp WHERE empno LIKE eno;
dbms_output.put_line(' The Employee Name with Employee No ' || eno || ' is : ' || name);
RETURN name;
END getname;

PROCEDURE sumSal(str VARCHAR)
IS
dno NUMBER;
tsal NUMBER;
BEGIN
SELECT deptno INTO dno FROM emp WHERE ename LIKE str;
SELECT sum(sal) INTO tsal FROM emp WHERE deptno=dno;
dbms_output.put_line(' Total SALARY for Department No ' || dno || ' is : ' || tsal);
END sumsal;

END pkgdem;
/

~~~~~

DECLARE
name VARCHAR(20);
BEGIN
name := pkgdem.getName(&num);
pkgdem.sumsal(name);
END;
/

-----------------------------------------------------------------------------------------

8. Grants
----------------------------------------------------------------------------------------------------------
CREATE USER tss IDENTIFIED BY tss;
CREATE USER pydah IDENTIFIED BY pydah;
GRANT RESOURCE, CONNECT TO tss;
GRANT RESOURCE, CONNECT TO pydah;
GRANT ALL ON TAB TO TSS WITH GRANT OPTION;

Connect : TSS/TSS
GRANT SELECT ON emp TO pydah;
GRANT INSERT ON dept TO pydah;

SELECT * FROM tss.emp;
INSERT INTO tss.emp VALUES(111,'AAA');

GRANT ALL ON emp TO PUBLIC;
REVOKE INSERT ON dept FROM pydah;

~~~~~
at : system/manager
------------------------
CREATE USER ramky IDENTIFIED BY ramky;
GRANT CONNECT TO ramky;

CREATE USER kishore IDENTIFIED BY kishore;
GRANT CONNECT TO kishore;

CREATE ROLE deo;
CREATE ROLE prog;

at scott/tiger: GRANT SELECT ON emp TO deo;
at system/manager : GRANT deo TO ramky;

at scott/tiger: GRANT INSERT, UPDATE ON emp TO prog;
at system/manager : GRANT prog TO kishore;

----------------------------------------------------------------------------------------------------------

CREATE TABLE EMP
(
empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10) NOT NULL CONSTRAINT chk_name CHECK(ename=UPPER(ename)),
job VARCHAR2(10) NOT NULL CONSTRAINT chk_job CHECK(job=UPPER(job)),
mgr NUMBER(4) ,
hiredate DATE DEFAULT sysdate,
sal NUMBER(7,2) NOT NULL CONSTRAINT chk_sal CHECK(sal BETWEEN 500 and 50000),
comm NUMBER(4) ,
deptno NUMBER(2) NOT NULL,
FOREIGN KEY (deptno) REFERENCES dept(deptno),
CONSTRAINT chk_comm CHECK((sal+comm)<50000)
);

CREATE TABLE DEPT
(
deptno NUMBER(2) PRIMARY KEY CONSTRAINT chk_dept CHECK(deptno BETWEEN 10 AND 99),
dname VARCHAR2(20) NOT NULL CONSTRAINT chk_dname_up CHECK(dname=UPPER(dname)) DISABLE,
loc VARCHAR2(10) NOT NULL CONSTRAINT chk_loc CHECK(loc IN('DALLAS', 'BOSTON', 'NEW YORK', 'CHICAGO'))
);

----------------------------------------------------------------------------------------------------------

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');

INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '17-DEC-80', 800, NULL, 20);
INSERT INTO emp VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-81', 1600, 300, 30);
INSERT INTO emp VALUES(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-81', 1250, 500, 30);
INSERT INTO emp VALUES(7566, 'JONES', 'MANAGER', 7839, '02-APR-81', 2975, NULL, 20);
INSERT INTO emp VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-81', 1250, 1400, 30);
INSERT INTO emp VALUES(7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-81', 2850, NULL, 30);
INSERT INTO emp VALUES(7782, 'CLARK', 'MANAGER', 7839, '09-JUN-81', 2450, NULL, 10);
INSERT INTO emp VALUES(7788, 'SCOTT', 'ANALYST', 7566, '19-APR-87', 3000, NULL, 20);
INSERT INTO emp VALUES(7839, 'KING', 'PRESIDENT', null, '17-NOV-81', 5000, NULL, 10);
INSERT INTO emp VALUES(7844, 'TURNER', 'SALESMAN', 7698, '08-SEP-81', 1500, 0, 30);
INSERT INTO emp VALUES(7876, 'ADAMS', 'CLERK', 7788, '23-MAY-87', 1100, NULL, 20);
INSERT INTO emp VALUES(7900, 'JAMES', 'CLERK', 7698, '03-DEC-81', 950, NULL, 30);
INSERT INTO emp VALUES(7902, 'FORD', 'ANALYST', 7566, '03-DEC-81', 3000, NULL, 20);
INSERT INTO emp VALUES(7934, 'MILLER', 'CLERK', 7782, '23-JAN-82', 1300, NULL, 10);

----------------------------------------------------------------------------------------------------------

INSERT INTO emp VALUES(9999, 'KILLER', 'ANALYST', 7839, '01-JAN-80', 2700, NULL, 20);

9. Lobs

LOBS(LARGE OBJECTS)
********************

Creating Virtual Directory
---------------------------
USER:SYSTEM
PASSWORD:MANAGER

create directory "images" as 'D:\TSS\IMAGES';

GRANTING Privileges to the user
--------------------------------
GRANT READ ON DIRECTORY "IMAGES" TO SCOTT;

Creating Table
--------------
USER:SCOTT
PASSWORD:TIGER

CREATE TABLE OBJ(NAME VARCHAR2(10),PIC BFILE);

Inserting Values
----------------

INSERT INTO OBJ VALUES('PIC1',BFILENAME('IMAGES','D:\TSS\IMAGES\PIC1.JPG'));

10. Object
-----------------------------------------------------------------------------------
Create type Address as Object(
Street Varchar2(20),
City Varchar2(20)
);

Create type Phone as Object(
Phone1 NUMBER(20),
Phone2 NUMBER(20),
Phone3 NUMBER(20),
Phone4 NUMBER(20),
Phone5 NUMBER(20)
);

Create table Cust(
No Number(2),
Name Varchar2(20),
Adds Address,
Ph Phone
);

Insert into Cust Values (1,'Ramesh',address('kalinga','Vsp'),Phone(5542338,2793584,null,null,null));

Select No,Name from cust;
Select No,Name,Street from cust; // error
Select No,Name,a.adds.Street,a.ph.phone1 from cust a;

-----------------------------------------------------------------------------------
Create type odate as Object(
Bdate date,
member function age return number
);

Create Type Body odate as
member function age
return number
is
begin
return(to_char(sysdate,'yyyy')-to_char(Bdate,'yyyy'));
end;
end;

create table stud(name varchar2(20),
date1 odate);

Insert into Stud values ('Ramesh',Odate('15-dec-1977'));
Insert into Stud values ('Prasad',Odate('10-dec-1986'));

Select Name,a.date1.bdate from stud a;
Select Name,a.date1.bdate ,a.date1.age() from stud a;

-----------------------------------------------------------------------------------

11. Java-sql-Procedures

----------------------------------------------------------------------------------------
public class Demo
{
public static String getName(String name)
{
System.out.println("Inside Method !!!");
System.out.println(name.toUpperCase());
return name;
}

public static void main(String args[])
{
String str="";
System.out.println("Welcome to Java Programming !!!");
Demo d = new Demo();
System.out.println(d.getName(str));
}
}
----------------------------------------------------------------------------------------
loadjava -u scott/tiger -v -resolve Demo.java
----------------------------------------------------------------------------------------
SELECT object_name, object_type, status
FROM user_objects WHERE object_type LIKE 'JAVA%';
----------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE demo(str VARCHAR2)
AS LANGUAGE JAVA
NAME 'Demo.getName(java.lang.String)';
/

----------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION fnDemo(str VARCHAR2)
RETURN VARCHAR2 AS LANGUAGE JAVA
NAME 'Demo.getName(java.lang.String) return str';
/

----------------------------------------------------------------------------------------

SET SERVEROUTPUT ON
CALL dbms_java.set_output(2000);

EXECUTE demo('KILLIS');

----------------------------------------------------------------------------------------

VARIABLE STR VARCHAR2(10);
CALL fndemo('SRINIVAS') INTO :str;

PRINT str

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE getName(EID emp.empno%TYPE, name OUT emp.ename%TYPE)
IS
BEGIN
SELECT ename INTO name FROM EMP WHERE empno = eid;
END;
/

----------------------------------------------------------------------------------

import java.sql.*;

public class Proc
{
public static void main(String args[]) throws SQLException
{
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
Connection cn=DriverManager.getConnection("jdbc:odbc:TSS","scott","tiger");

CallableStatement cs= cn.prepareCall(" { call getName(?,?) } ");
cs.registerOutParameter(2,Types.VARCHAR);
cs.setInt(1,Integer.parseInt(args[0]));
cs.execute();
System.out.println("Employee Name is : " + cs.getString(2));
System.out.println("Procedure is executed..");
cn.close();
}
}
----------------------------------------------------------------------------------

import java.sql.*;

public class JDBCDemo
{
public JDBCDemo()
{
System.out.println("Inside CONSTRUCTOR");
try{
Connection con;
Statement stt;
ResultSet rs;

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("Jdbc:Odbc:TSS", "SCOTT", "TIGER");
stt = con.createStatement();
rs = stt.executeQuery("SELECT ename FROM emp WHERE empno=7788");
rs.next();
System.out.println("Employee Name : " + rs.getString(1));
rs.close(); stt.close(); con.close();
}catch(Exception e) {}
}

public static void main(String args[])
{
System.out.println("Inside MAIN");
JDBCDemo dem = new JDBCDemo();
}
}

----------------------------------------------------------------------------------
Basic SQL in ORACLE

Data Base Oracle

Oracle

PL/SQL for Oracle 10g:

variable num number;

declare
isal number(7,2);
begin
select sal into isal from emp where empno=7900;
:num := isal;
end;
/
------------------------------------------------------------------------------
accept num1 prompt 'Enter a Number : ';
accept num2 prompt 'Enter a Number : ';

declare
n1 number(5) := &num1;
n2 number(5) := &num2;
begin
:num := n1 + n2;
end;
/
------------------------------------------------------------------------------
DECLARE
name VARCHAR2;
dno NUMBER;
BEGIN
accept name prompt 'Enter Employee Name : ';
SELECT deptno INTO dno WHERE ename = name;

CASE
WHEN dno=10 THEN
DBMS_OUTPUT.PUT_LINE(name || ' belongs to Department# 10');
WHEN dno=20 THEN
DBMS_OUTPUT.PUT_LINE(name || ' belongs to Department# 20');
WHEN dno=30 THEN
DBMS_OUTPUT.PUT_LINE(name || ' belongs to Department# 30');
END CASE;
END;

/

EXCEPTIONS IN ORACLE:

Oracle Exception Name	Oracle Error	Explanation
DUP_VAL_ON_INDEX	ORA-00001	You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE	ORA-00051	You were waiting for a resource and you timed out.
TRANSACTION_BACKED_OUT	ORA-00061	The remote portion of a transaction has rolled back.
INVALID_CURSOR	ORA-01001	You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON	ORA-01012	You tried to execute a call to Oracle before logging in.
LOGIN_DENIED	ORA-01017	You tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUND	ORA-01403	You tried one of the following: 1. You executed a SELECT INTO statement and no rows were returned. 2. You referenced an uninitialized row in a table. 3. You read past the end of file with the UTL_FILE package.
TOO_MANY_ROWS	ORA-01422	You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE	ORA-01476	You tried to divide a number by zero.
INVALID_NUMBER	ORA-01722	You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
STORAGE_ERROR	ORA-06500	You ran out of memory or memory was corrupted.
PROGRAM_ERROR	ORA-06501	This is a generic "Contact Oracle support" message because an internal problem was encountered.
VALUE_ERROR	ORA-06502	You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
CURSOR_ALREADY_OPEN	ORA-06511	You tried to open a cursor that is already open.

-----------------------
Basic SQL in MS SQL Server

-----------------

Default Tables in SQL

Data Base

1. Oracle
SQL:
DDL(Data Definition Language)
DML(Data Manipulation Language)
DCL(Data Control Language)
TCL(Transaction Control Language)

PL/SQL:

Exceptions in Oracle

2. SQL Server
1. PL/SQL Exercises
2. Functions
3. Procedures
4. Exceptions
5. Triggers
6. Cursors
7. Packages
8. Grants
9. Lobs
10. Object
11. Java-sql-procedures

--------------------------

DB Front-end Tools

Toad
DB Visualizer
SQL Developer Tool

Menus

Wednesday 15 June 2016

Monday 13 June 2016

Sunday 12 June 2016

Friday 10 June 2016

Basic Characteristics Of persons born in Sravana Nakshatram (Telugu)https://www.youtube.com/watch?v=-SbGB7P7MdA&index=3&list=PLmnLpmkArTODYUbTtfM0hPm_CXRefb3gY

Makara Raasi / Capricorn - Ugadi Panchanga Sravanam 2014 - Jaya Nama Samvatsaramhttps://www.youtube.com/watch?v=XCXKL48dnt0&list=PLmnLpmkArTODYUbTtfM0hPm_CXRefb3gY&index=4

Amazing Hypnosis Makes You High

Sunday 5 June 2016

Friday 13 May 2016

Thursday 5 May 2016

Basic SQL (DDL-DML-DCL-TCL)

SQL (Structured Query Language)

Basic Characteristics Of persons born in Sravana Nakshatram (Telugu)
https://www.youtube.com/watch?v=-SbGB7P7MdA&index=3&list=PLmnLpmkArTODYUbTtfM0hPm_CXRefb3gY

Makara Raasi / Capricorn - Ugadi Panchanga Sravanam 2014 - Jaya Nama Samvatsaram
https://www.youtube.com/watch?v=XCXKL48dnt0&list=PLmnLpmkArTODYUbTtfM0hPm_CXRefb3gY&index=4