Grand Test SQL

Select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S';

Select empno, ename, loc from emp, dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S';

Select empno, ename, loc from emp join dept where emp.deptno = dept.deptno and substr(emp.ename,1,1) = 'S';

Select empno, ename, loc from emp join dept on emp.deptno = dept.deptno and substr(emp.ename,1,1) = 'S';

You can change data in Oracle using select statements.

You can remove data from Oracle using select statements.

You can create a table with the contents of another using select statements.

You can truncate tables using select statements

Column clause

Table clause

The DUAL table

The where clause

. select * from dual;

Update emp set empno = 6543 where ename = 'SMITHERS';

Set define ?

Create table employees (empid varchar2(10) primary key);

Tables

Sequences

Indexes

Views

Select

From

Where

Having

. where product_name = 'GAS GRILL' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001';

Where product_type = 'APPLIANCE' and product_name = 'GAS GRILL' and qtr_end_date ='31-JAN-2001' or '30-JUN-2001';

. where product_type = 'APPLIANCE' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001';

Where product_name = 'GAS GRILL' and qtr_end_date = '01-JAN-2001' or '30-JUN-2001';

Oracle returns zero as the result

Oracle returns EMPTY as the result.

Oracle returns NULL as the result

Oracle returns an error as the result

Where customer = 'LESLIE';

Where customer = 'LESLIE' and order_amt < 2700;

Where customer = 'LESLIE' or order_amt > 2700;

Where customer = 'LESLIE' and order_amt > 2700;

Select trim(trailing '-dog' from ename) as ename from emp;

Select rpad(ename, 10, '-dog') as ename from emp;

Select substr(ename, 1, 10) as ename from emp;

Select lpad(ename, 10, '-dog') as ename from emp;

Abs( )

Ceil( )

Floor( )

Round( )

6

13

30

60

Ceil( )

Floor( )

Round( )

Trunc( )

Ceil( )

Floor( )

.round( )

Trunc( )

14

-14

168

-168

Select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.prod_id = b.prod_id and prod_name = 'WIDGET';

Select p.prod_id, p.prod_name, b.box_loc fromproduct p, storage_box b where prod_name = 'WIDGET';

Select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.stor_box_num = b.stor_box_num and p.prod_name = 'WIDGET';

Select prod_id, prod_name, box_loc from product, storage_box where stor_box_num = stor_box_num and prod_name = 'WIDGET'

.Two

Three

Four

Five

Select product.id, product.name, storage.loc from product, storage where product.box# = storage.box#;

Select product.id, product.name, storage.loc from product join storage on product.box# = storage.box#;

Select product.id, product.name, storage.loc from product natural join storage on product.box# = storage.box#;

Select product.id, product.name, storage.loc from product natural join storage;

Because outer join operations permit NULL values from one of the tables, you do not have to specify equality comparisons to join those tables.

In outer join statements on tables A and B, you specify the right outer join when you want all of table A's rows, even when no corresponding record exists in table B.

In outer join statements on tables A and B, you specify the left outer join when you want all of table B's rows, even when no corresponding record exists in table A.

Even though outer join operations permit NULL values from one of the tables, you still need to specify equality comparisons to join those tables.

Select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET'(+);

Select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET';

Select p.prod_id, p.prod_name, b.box_loc from product p right outer join storage_box b where b.stor_box_num = p.stor_box_num (+) and p.prod_name = 'WIDGET';

Select p.prod_id, p.prod_name, b.box_loc from product p full outer join storage_box b on p.stor_box_num = b.stor_box_num where b.stor_box_num is NULL;

Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e outer join addr a on e.empno = a.empno where a.state = 'TEXAS';

. select e.ename, a.street_address, a.city, a.state, a.post_code from emp e left outer join addr a on e.empno = a.empno where a.state = 'TEXAS'

Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a on e.empno = a.empno where a.state = 'TEXAS';

Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a where e.empno = a.empno (+) and a.state = 'TEXAS';

Full outer join

Left outer join

Right outer join

Equijoin

Select deptno, job, sum(sal) from emp group by job, deptno;

Select sum(sal), deptno, job from emp group by job, deptno;

Select deptno, job, sum(sal) from emp;

Select deptno, sum(sal), job from emp group by job, deptno;

DEPTNO

JOB

LOC

Sum(A.SAL)

Select region, prod_type, period, avg(profit) from profits where avg(profit) > 100000 group by region, prod_type, period;

Select region, prod_type, period, avg(profit) from profits where avg(profit) > 100000 order by region, prod_type, period;

Select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) > 100000;

Select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) < 100000;