SQL Step by Step With Images

Basics of Sql

You can display all the column of a table, for example, to display all eight columns of EMP, enter. Entering all the column names for large table would be time consuming.

After entering this command Dba can see all the record in Employee table like above.

SQL> Select * from Emp;

You can display specify column name form this command.

SQL> SELECT empno, ename, job, sal FROM Emp;

Select command with WHERE CLAUSE.

To see the specific job person through WHERE CLAUSE in department.

SQL> SELECT * FROM Dept WHERE DNAME = ‘SALES’;

To list all information about employees in department 30, enter:

To list all information about employees in Department 30, enter;

SQL> select * from emp where deptno=30;

Select and WHERE CLAUSE command Working with + operator.

To calculate the Net Salary with Plus (+) operator.

Although SAL+ 15 is not the column in the EMP table, SQL displays the calculate result as if it were.

SQL> SELECT empno, ename, Job, SAL, SAL + 15 FROM Emp;

To calculate annual compensation for all salespeople, enter;

SQL> select ename,sal,comm, 12*(sal+comm) from emp where job=’SALESMAN’;

SQL> SELECT Ename, job, deptno From Emp WHERE JOB = ‘Manager’;

Add the new row (Value) in emp table through this command.

After entering the record one record is added.

SQL> INSERT into Emp Values

(8000, ‘Jack’, ‘ Manager’, 8700, ‘7-DEC-09’, 1000, NULL, 30);

See the last record MILLER is updated through this command.

SQL> Update Emp set MGR=7781, Deptno=20 where Ename = ‘MILLER’;

To list all the jobs in the table EMP, enter:

This displays a list of all the jobs, but some job types are listed more than once. To eliminate the duplicate rows in the result, include DISINCT clause in the SELECT command:

To list the distinct jobs in EMP, enter:

SQL> select distinct job from emp;

To change the headings of the DNAME column, enter:

SQL> select dname DEPARTMENT, deptno from dept;

The alias defined by the SELECT command.

SQL> Select department, deptno from dept;

It will produce the same result.

To compare a column to a constant value, include a condition in your WHERE clause.

To find all department names with department numbers greater than 20, enter;

SQL> select dname, deptno from dept where deptno > 20;

You can compare a column with another column in the same row as well as with a constant value, For example to find the employees whose commission their salary, enter;

SQL> select ename, sal, comm from emp where comm>sal;

To find the all sales people in Department 30 whose salary is greater than or equal to $1,500, enter:

SQL> Select ename, sal, deptno from emp where job=’SALESMAN’ and deptno=30 and sal>=1500;

To find all employees who either are managers or have a salary greater than $3,000.

SQL> Select ename, job, sal from emp where job=’MANAGER’ OR sal>3000;

Operator Precedence;

You can combine AND and OR in the same logical operator expression, when AND and OR appear in the same WHERE clause.

To list information about all the manages, as well as the clerks in Department 10, enter;

SQL> Select * from emp where job=’MANAGER’ OR (job=’CLERK’ AND deptno=10);

Suppose you want a list of all the Employees who earn between $1200 and $1300.

Selecting Rows within a Range, the between operator lets you select rows in which a column contains a value within a range.

SQL> Select ename, job, sal from emp where sal between 1200 and 1300;

Matching a Value in a List (the IN operator lets you select rows that match one of the values in a list) For example.

SQL> select ename, job, deptno from emp where sal not between 1200 AND 1400;

To list employees and jobs in department 30, in order of their salary.

SQL> select sal, job, ename from emp where deptno=30 order by sal;

To list employees and jobs in departments 30 in descending order by salary, enter:

SQL> Select sal, job, ename from Emp where Deptno = 30 order by SAL Desc;