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

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;

A single-row subquery

A multirow subquery

A from clause subquery

A multicolumn subquery

select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS';

Select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU') and product = 'TOYS');

Select sum(profit) from profits where region = ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS';

Select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU') , product = 'TOYS';

Select p.prod_name, p.profit from (select prod_name, profit from profits order by profit desc) where rownum

Select p.prod_name, p.profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) subq where p.prod_name = subq.prod_name;

Select prod_name, profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) where rownum

Select prod_name, profit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum

Create table cats (c_name varchar2(10), c_weight number, c_owner varchar2(10));

Create table my_cats as select * from cats where owner = 'ME';

Create temp_cats (c_name varchar2(10), c_weight number, c_owner varchar2(10));

Create table cats-over-5-lbs as select c_name, c_weight from cats where c_weight > 5;

5392845.324

871039453.1

75439289.34

60079829.25

The row for LACKEY in the JOB table already existed with JOB_WAGE set to 35.

A default clause on the JOB_WAGE column defined when the table was created specified the value when the row was inserted

The values clause in the insert statement contained a hidden value that was added when the row was added.

The only possible explanation is that a later update statement issued against the JOB table added the JOB_WAGE value.

Copy column records to a temporary storage location

Set the NUMBER column to NULL for all rows.

Create a temporary location for NUMBER data.

Copy column records from the temporary location back to the main table.

Set the VARCHAR2 column to NULL for all rows.

Create a temporary location for VARCHAR2 data.

Copy the column records from the temporary location back to the main table.

Nothing; the statement is executed automatically.

Alter table sales add (product_type varchar2(10));

Alter table sales modify product_type varchar2(10));

Alter table sales set unused column product_type varchar2(10));

Alter table sales drop column product_type;

One

Two

Three

NULL

ALLEN

MARTIN

TURNER

WARD

ALLEN

MARTIN

TURNER

WARD

True

False

True

False

True

False