CRUD operation in SQL:

(INSERT):

Create table dept:

CREATE TABLE departments ( deptno NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY, dname VARCHAR2(14), loc VARCHAR2(13));

Insert a single row into a table:

INSERT INTO dept VALUES (10, ‘Branding’, ‘Dhaka’);

Insert rows from one table into another table:

INSERT INTO emp2 SELECT * FROM emp;

CREATE TABLE emp3 AS SELECT * FROM emp;

Insert multiple rows into a table with a single statement:

INSERT ALL

INTO dept (deptno, dname, loc) VALUES (11, ‘Sales’, ‘Dhaka’)

INTO dept (deptno, dname, loc) VALUES (12, ‘Branding’, ‘Chittagong’)

SELECT 1 FROM dual;

INSERT INTO dept

SELECT 13, ‘Sales’, ‘Dhaka’ FROM dual UNION

SELECT 14, ‘Branding’, ‘Chittagong’ FROM dual;

Insert multiple rows into different tables with a single statement:

CREATE SEQUENCE dept_seq:

INSERT ALL

WHEN type=1 THEN INTO table1 VALUES (dept_seq.NEXTVAL, val)

WHEN type=2 THEN INTO table2 VALUES (dept_seq.NEXTVAL, val)

WHEN type IN (3,4,5) THEN INTO table3 VALUES (dept_seq.NEXTVAL, val)

ELSE INTO tab4 VALUES (myseq.NEXTVAL, val)

SELECT type, val FROM source_tab;

(SELECT)

Create table EMP:

CREATE TABLE emp ( empno NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), manager_id NUMBER(4), hiredate DATE, sal NUMBER(7,2), commission NUMBER(7,2), department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id));

INSERT into EMP:

INSERT INTO emp VALUES (7369,’SMITH’,’CLERK’,7902,to_date(’17-12-1980′,’dd-mm-yyyy’),800,NULL,20);

INSERT INTO emp VALUES (7499,’ALLEN’,’SALESMAN’,7698,to_date(’20-2-1981′,’dd-mm-yyyy’),1600,300,30);

INSERT INTO emp VALUES (7521,’WARD’,’SALESMAN’,7698,to_date(’22-2-1981′,’dd-mm-yyyy’),1250,500,30);

INSERT INTO emp VALUES (7566,’JONES’,’MANAGER’,7839,to_date(‘2-4-1981′,’dd-mm-yyyy’),2975,NULL,20);

INSERT INTO emp VALUES (7654,’MARTIN’,’SALESMAN’,7698,to_date(’28-9-1981′,’dd-mm-yyyy’),1250,1400,30);

INSERT INTO emp VALUES (7698,’BLAKE’,’MANAGER’,7839,to_date(‘1-5-1981′,’dd-mm-yyyy’),2850,NULL,30);

INSERT INTO emp VALUES (7782,’CLARK’,’MANAGER’,7839,to_date(‘9-6-1981′,’dd-mm-yyyy’),2450,NULL,10);

INSERT INTO emp VALUES (7788,’SCOTT’,’ANALYST’,7566,to_date(’13-JUL-87′,’dd-mm-rr’)-85,3000,NULL,20);

INSERT INTO emp VALUES (7839,’KING’,’PRESIDENT’,NULL,to_date(’17-11-1981′,’dd-mm-yyyy’),5000,NULL,10);

INSERT INTO emp VALUES (7844,’TURNER’,’SALESMAN’,7698,to_date(‘8-9-1981′,’dd-mm-yyyy’),1500,0,30);

INSERT INTO emp VALUES (7876,’ADAMS’,’CLERK’,7788,to_date(’13-JUL-87′, ‘dd-mm-rr’)-51,1100,NULL,20);

INSERT INTO emp VALUES (7900,’JAMES’,’CLERK’,7698,to_date(‘3-12-1981′,’dd-mm-yyyy’),950,NULL,30);

INSERT INTO emp VALUES (7902,’FORD’,’ANALYST’,7566,to_date(‘3-12-1981′,’dd-mm-yyyy’),3000,NULL,20);

INSERT INTO emp VALUES (7934,’MILLER’,’CLERK’,7782,to_date(’23-1-1982′,’dd-mm-yyyy’),1300,NULL,10);COMMIT;

Select all columns (* = all columns) and all rows from the emp table:

SELECT * FROM emp;

Select the salary column for employee number 7788:

SELECT sal FROM emp WHERE empno = 7788;

Sort rows and return them in order:

SELECT ename, sal FROM emp ORDER BY sal;

Group rows together:

SELECT deptno, COUNT(*) “Employees in department”, SUM(sal) “Total salaries for department”, AVG(sal) “Avarage salary for department” FROM emp GROUP BY deptno;

Group rows together:

PL/SQL and some precompiler languages allow one to select the column values into program variables with the INTO-clause. Typical syntax is:

SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7788;

This will only work if the query return a single row.

(UPDATE)

Update is a SQL DML command used to change data in a table. Note that it’s important to specify a WHERE-clause, if not ALL rows in the table will be updated.

Give employees in department 10 an 10% raise:

UPDATE emp SET sal = sal*1.1 WHERE deptno = 10;

Give employee number 7844 a $100 commission:

UPDATE emp SET comm = 100 WHERE empno = 7844;

(DELETE)

Delete is a SQL DML command used to remove one or more rows from a table. Note that it’s important to specify a WHERE-clause, if not ALL rows in the table will be removed.

Remove selective rows from a table – in this case only one:

DELETE FROM emp WHERE empno = 7844;COMMIT;

Remove all rows from a table (also compare to TRUNCATE):

DELETE FROM emp;ROLLBACK;

Remove records in a specific partition:

DELETE FROM emp_part PARTITION p1;

Remove rows from a remote database (via a database link):

DELETE FROM emp@remote_db;

Remove rows from a SELECT statement:

DELETE FROM (SELECT * FROM emp WHERE deptno = 10);

Delete with RETURNING clause (can only be used from PL/SQL):

DECLARE v_rowid urowid; BEGIN DELETE FROM emp WHERE empno = 7844 RETURNING rowid INTO v_rowid;END;/

ORACLE

CRUD operation in SQL:

Comments

Click here to cancel reply.

CONTACT US

We are social:

Visitor Counter