SQL Queries - Data Definition Language(DDL Statements create table emp(empno number(5 primary key name varchar2(20 sal number(10,2 job varchar2(20 mgr

SQL Queries - Data Definition Language(DDL Statements...

This preview shows page 1 - 4 out of 10 pages.

Data Definition Language (DDL) Statements create table emp (empno number(5) primary key, name varchar2(20), sal number(10,2), job varchar2(20), mgr number(5), Hiredate date, comm number(10,2)); Now Suppose you have emp table now you want to create a TAX table with the following structure and also insert rows of those employees whose salary is above 5000. create table tax (empno number(5), tax number(10,2)); insert into tax select empno,(sal-5000)*0.40 from emp where sal > 5000; Instead of executing the above two statements the same result can be achieved by giving a single CREATE TABLE AS statement. create table tax as select empno,(sal-5000)*0.4 as tax from emp where sal>5000 You can also use CREATE TABLE AS statement to create copies of tables. Like to create a copy EMP table as EMP2 you can give the following statement. create table emp2 as select * from emp; To copy tables without rows i.e. to just copy the structure give the following statement create table emp2 as select * from emp where 1=2; To add new columns addr, city, pin, ph, fax to employee table you can give the following statement alter table emp add (addr varchar2(20), city varchar2(20), pin varchar2(10),ph varchar2(20)); To modify the datatype and width of a column. For example we you want to increase the length of the column ename from varchar2(20) to varchar2(30) then give the following command. alter table emp modify (ename varchar2(30)) To decrease the width of a column the column can be decreased up to largest value it holds.
Image of page 1
alter table emp modify (ename varchar2(15)); SQL Queries, SELECT Statement select * from emp where sal < 5000; SELECT * FROM emp WHERE NOT (salary BETWEEN 1000 AND 2000); SELECT * FROM employees WHERE ename ='SAMI' AND sal=3000; SELECT * FROM emp WHERE ename = 'SAMI' OR sal >= 1000; SELECT * FROM emp WHERE deptno IN (10,20); SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE city=’HYD’); SELECT * FROM emp WHERE ename IS NOT NULL; SELECT deptno FROM dept d WHERE EXISTS (SELECT * FROM emp e WHERE d.deptno = e.deptno); select * from emp where ename like ‘S%’ ; select * from emp where ename like ‘%a%’; Data Manipulation Language (DML) Statements Insert into emp values (101,’Sami’,’G.Manager’, ’8-aug- 1998’,2000); Insert into emp (empno,ename,sal) values (102,’Ashi’,5000); update emp set name=’Mohd Sami’, sal=sal+(sal*10/100) where empno=102;
Image of page 2
Joins Equijoins An equijoin is a join with a join condition containing an equality operator ( = ). An equijoin combines rows that have equivalent values for the specified columns. For example the following query returns empno,name,sal,deptno and department name and city from department table. select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.city from emp,dept where emp.deptno=dept.deptno; The above query can also be written like, using aliases, given below. select e.empno, e.ename, e.sal, e.deptno, d.dname, d.city from emp e, dept d where emp.deptno=dept.deptno; The above query can also be written like given below without using table qualifiers.
Image of page 3
Image of page 4

You've reached the end of your free preview.

Want to read all 10 pages?

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture