Basics of Sql
|The Select Command:
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;
|Selecting Specific COLUMNS from a TABLE:
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;
|Arithmetic Expression :
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;
|Expression with several Operators:
To calculate annual compensation for all salespeople, enter;
SQL> select ename,sal,comm, 12*(sal+comm) from emp where job=’SALESMAN’;
|To list the name and Departments of all the Employees who are Manager, Enter
SQL> SELECT Ename, job, deptno From Emp WHERE JOB = ‘Manager’;
|Inserting Rows into a Table:
Add the new row (Value) in emp table through this command.
SQL> INSERT into Emp Values
(8000, ‘Jack’, ‘ Manager’, 8700, ‘7-DEC-09’, 1000, NULL, 30);
|Update Rows in a Table:
See the last record MILLER is updated through this command.
SQL> Update Emp set MGR=7781, Deptno=20 where Ename = ‘MILLER’;
|Preventing the Selection of DUPLICATE Rows.
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;
|Creating and using Column ALIASES:
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.
|Specifying a Condition:
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;
|Compound Logical Expression with AND:
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;
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);
|Selecting ROWS within a RANGE.
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;
|You can modify BETWEEN, like other operators, with NOT BETWEEN means that only rows outside the range with be selected.
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;
|Controlling the Order of Displayed ROW; the Order by Clause.
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;