DML- Data Manipulation Language
DML Insert
Syntax:
INSERT INTO table_name(column1,column2,column3) VALUES(value1,value2,value3);
Table: STUDENT(ROLLNO,NAME,LOC)
Examples:
INSERT INTO student(rollno,name,loc) VALUES(1,’RAKESH’,’SALTLAKE’);
INSERT INTO student (rollno,name) VALUES (2,’ROHIT’);
INSERT INTO student VALUES (3,’RAMESH’,NULL);
INSERT INTO customer VALUES (&rollno, ‘&name’,‘&loc’);
INSERT INTO student_bkp (SELECT * FROM student);
COMMIT;
DML Update
Syntax:
UPDATE table_name set column1=value1, column2=value2 where column3=value3;
UPDATE student SET loc=‘GARIA’ WHERE rollno=3;
UPDATE customer SET loc=‘RAJARHAT’, cname=‘RAKESH PAL’ WHERE rollno=1;
UPDATE student SET loc=(SELECT loc FROM student WHERE rollno=1) WHERE rollno=2;
COMMIT;
DML Delete
Syntax:
DELETE FROM table_name WHERE column1=value1;
DELETE FROM student;
DELETE FROM student WHERE rollno=3;
DELETE FROM student WHERE rollno=1 and loc like ‘G%’;
Merging Data in Oracle Tables(9i)
Syntax:
MERGE INTO table1 USING table2 ON (join_condition)
WHEN MATCHED THEN UPDATE SET col1=value1
WHEN NOT MATCHED INSERT (column list) VALUES(column_values);
MERGE INTO Target_table Tgt USING Source_table Src
on(Src.name = Tgt.name)
WHEN MATCHED then update set Tgt.loc=Src.loc
WHEN NOT MATCHED then insert (Tgt.rollno,Tgt.name,Tgt.loc) Values (Src.rollno, Src.name, Src.loc);
COMMIT;
DQL-Data Query Language
DQL SELECT
Syntax: SELECT col1,col2.... or <*> FROM
Example: To display the records in the Employee table
SELECT * FROM emp;
Example : List the Empno and Ename from Employee table
SELECT empno “Employee Number”, ename AS Employee Name FROM emp;
WHERE Clause
The WHERE Clause is used to specify the criteria:
Example: List all employees who belong to department number 40
SELECT empno, ename, deptno FROM Emp WHERE deptno=40;
DISTINCT Clause
The DISTINCT Clause is used to eliminate duplicate values and to display only the unique values.
Example: To list the unique jobs in the employee table
SELECT DISTINCT job FROM Emp;
Operators
•Arithmetic
+,-,*,/
•Relational
=,<,>,<=,>=,<>,!=,^=
•Logical or Boolean
AND,OR,NOT
•Set Operators
UNION,UNION ALL,INTERSECT, MINUS
•Others
IN, BETWEEN, LIKE , IS NULL
Arithmetic Operator
SELECT sal,sal*0.5 AS Bonus FROM emp;
SELECT sysdate-1 AS Yesterday,sysdate Today, sysdate+1 Tomorrow FROM DUAL;
Relational Operator
Example: List the employee name whose employee number is 7900
SELECT ename FROM Emp WHERE empno = 7900;
Example: List the employees whose hire date is before 28-SEP-81
SELECT empno, ename, hiredate, deptno FROM Emp WHERE Hiredate < = ‘28- SEP-81’;
Logical Operators
Example: List the employees who are getting salary in the range of 1500 and 3000
SELECT empno, ename, sal FROM Emp WHERE sal>= 1500 AND sal <= 3000; Example: List the employee number and names of department 10, department 20 SELECT empno, ename, sal FROM Emp WHERE deptno=10 OR deptno=20;
Set Operators
•Each of these operations combines the results of two select statements into a single result set
**data types should be same of both the tables
•The union operator returns the records retrieved by either of the queries
•By default, the union operator eliminates duplicate records
•To retain duplicates, we use union all instead of union
•The intersect operator returns those rows which are retrieved by both the queries
•The minus operator returns all rows retrieved by the first query but not by the second query
Example: List distinct employees in Kolkata and Mumbai location
SELECT * FROM emp_kol
UNION
SELECT * FROM emp_mum;
Note: The structure of emp_kol and emp_mum table must be exactly the same.
Example: List employees in Kolkata and Mumbai location
SELECT * FROM emp_kol
UNION ALL
SELECT * FROM emp_mum;
Example: List employees having entry in both Kolkata and Mumbai location
SELECT * FROM emp_kol
INTERSECT
SELECT * FROM emp_mum;
Example: List employees having entry in Kolkata but not in Mumbai location
SELECT * FROM emp_kol
MINUS
SELECT * FROM emp_mum;
Other Operators
Examples:
SELECT empno, ename, deptno FROM Emp WHERE sal BETWEEN 1500 AND 2500;
SELECT empno, ename, sal FROM Emp WHERE deptno IN(10,20);
SELECT empno, ename FROM Emp WHERE ename LIKE ‘S%’;
SELECT empno, ename,comm FROM Emp WHERE comm IS NULL;
*NULL means absence of information
Order By clause
•Sorts records using specified criteria in ascending or descending order
Syntax: ORDER BY col1 ASC, col2 DESC
Examples:
SELECT * FROM Employees ORDER BY Last_Name ASC;
SELECT empno,ename,sal FROM emp ORDER BY ename;
SELECT ename FROM emp ORDER BY ename DESC;
SELECT job,ename FROM emp ORDER BY job,ename;
SELECT job,ename FROM emp ORDER BY job, ename DESC;
SELECT ename,job FROM emp ORDER BY 1 DESC;
