Oracle Live SQL

.Oracle Live SQL.md

SQL Exercises

Databases for Developers

• Analytic Functions - Databases for Developers.sql
• Creating Tables: Databases for Developers.sql
• Introduction to SQL.sql
• Subqueries - Databases for Developers.sql

Analytic Functions - Databases for Developers.sql

--0. Data Setup
create table bricks (
  brick_id integer,
  colour   varchar2(10),
  shape    varchar2(10),
  weight   integer
);

insert into bricks values ( 1, 'blue', 'cube', 1 );
insert into bricks values ( 2, 'blue', 'pyramid', 2 );
insert into bricks values ( 3, 'red', 'cube', 1 );
insert into bricks values ( 4, 'red', 'cube', 2 );
insert into bricks values ( 5, 'red', 'pyramid', 3 );
insert into bricks values ( 6, 'green', 'pyramid', 1 );

commit;

--1. Introduction
select count(*) from bricks;

select count(*) over () from bricks;

select b.*, 
       count(*) over () total_count 
from   bricks b;

--2. Partition By
select colour, count(*), sum ( weight )
from   bricks
group  by colour;

select b.*, 
       count(*) over (
         partition by colour
       ) bricks_per_colour, 
       sum ( weight ) over (
         partition by colour
       ) weight_per_colour
from   bricks b;

/*
Group by produces aggregate vs partition produces statistic per row 
e.g. 1 row with the count vs all rows with the count
*/


--3. Try it!
select b.*, 
       count(*) over (
         partition by shape
       ) bricks_per_shape, 
       median ( weight ) over (
         partition by shape
       ) median_weight_per_shape
from   bricks b
order  by shape, weight, brick_id;

--4. Order By
select b.*, 
       count(*) over (
         order by brick_id
       ) running_total, 
       sum ( weight ) over (
         order by brick_id
       ) running_weight
from   bricks b;

--5. Try it!
select b.brick_id, b.weight,
       round ( avg ( weight ) over (
         order by brick_id


       ), 2 ) running_average_weight
from   bricks b
order  by brick_id;

--6. Partition By + Order By
select b.*, 
       count(*) over (
         partition by colour
         order by brick_id
       ) running_total, 
       sum ( weight ) over (
         partition by colour
         order by brick_id
       ) running_weight
from   bricks b;

--7. Windowing Clause
select b.*, 
       count(*) over (
         order by weight
       ) running_total, 
       sum ( weight ) over (
         order by weight
       ) running_weight
from   bricks b
order  by weight;

/*
By default the order by returns all the rows with a value less than or equal to that of the current row.
This includes values from rows after the current row.
This is not expected behaviour as running totals should not be summing future values.
*/

select b.*, 
       count(*) over (
         order by weight
         rows between unbounded preceding and current row
       ) running_total, 
       sum ( weight ) over (
         order by weight
         rows between unbounded preceding and current row
       ) running_weight
from   bricks b
order  by weight;

/*
Rows can have the same weight can result in different running totals.
Thus there has to be another unique column value to sort by to reproduce the same order.
*/

select b.*, 
       count(*) over (
         order by weight, brick_id
         rows between unbounded preceding and current row
       ) running_total, 
       sum ( weight ) over (
         order by weight, brick_id
         rows between unbounded preceding and current row
       ) running_weight
from   bricks b
order  by weight, brick_id;

--8. Sliding Windows
select b.*, 
       sum ( weight ) over (
         order by weight
         rows between 1 preceding and current row
       ) running_row_weight, 
       sum ( weight ) over (
         order by weight
         range between 1 preceding and current row
       ) running_value_weight
from   bricks b
order  by weight, brick_id;

/*
aka "moving weight" sum
*/

select b.*, 
       sum ( weight ) over (
         order by weight
         rows between 1 preceding and 1 following
       ) sliding_row_window, 
       sum ( weight ) over (
         order by weight
         range between 1 preceding and 1 following
       ) sliding_value_window
from   bricks b
order  by weight;

select b.*, 
       count (*) over (
         order by weight
         range between 2 preceding and 1 preceding 
       ) count_weight_2_lower_than_current, 
       count (*) over (
         order by weight
         range between 1 following and 2 following
       ) count_weight_2_greater_than_current
from   bricks b
order  by weight;

--9. Try It!
select b.*, 
       min ( colour ) over (
         order by brick_id
         rows between 2 preceding and 1 preceding
       ) first_colour_two_prev, 
       count (*) over (
         order by weight
         range between current row and 1 following
       ) count_values_this_and_next
from   bricks b
order  by weight;

--10. Filtering Analytic Functions
select colour from bricks
group  by colour
having count(*) >= 2;


/*
select colour from bricks
where  count(*) over ( partition by colour ) >= 2;
--note
--this produces an error as oracle db will first filter the query 
--with the where clause and then apply the partition
the solution is to use a subquery so that the partition occurs before the filter
*/

select * from (
  select b.*,
         count(*) over ( partition by colour ) colour_count
  from   bricks b
)
where  colour_count >= 2;

--11. Try It!
with totals as (
  select b.*,
         sum ( weight ) over ( 
           partition by shape
         ) weight_per_shape,
         sum ( weight ) over ( 
           order by brick_id, weight
           rows between unbounded preceding and current row
         ) running_weight_by_id
  from   bricks b
)
  select * from totals
  where weight_per_shape > 4
  and running_weight_by_id > 4
  order  by brick_id
  
--12. More Analytic Functions
select brick_id, weight, 
       row_number() over ( order by weight ) rn, 
       rank() over ( order by weight ) rk, 
       dense_rank() over ( order by weight ) dr
from   bricks;

/*
Rank - Rows with the same value in the order by have the same rank. The next row after a tie has the value N, where N is its position in the data set.
Dense_rank - Rows with the same value in the order by have the same rank, but there are no gaps in the ranks
Row_number - each row has a new value
*/

select b.*,
       lag ( shape ) over ( order by brick_id ) prev_shape,
       lead ( shape ) over ( order by brick_id ) next_shape
from   bricks b;

/*
-get prior or next value
*/

select b.*,
       first_value ( weight ) over ( 
         order by brick_id 
       ) first_weight_by_id,
       last_value ( weight ) over ( 
         order by brick_id 
       ) last_weight_by_id
from   bricks b;

/*
-get first/last value
-recall default windowing clause stops at the current row. 
-need explicit "unbounded following"
*/

select b.*,
       first_value ( weight ) over ( 
         order by brick_id 
       ) first_weight_by_id,
       last_value ( weight ) over ( 
         order by brick_id 
         range between current row and unbounded following
       ) last_weight_by_id
from   bricks b;

Creating Tables: Databases for Developers.sql

--1. Creating a Table

create table toys (
  toy_name varchar2(100),
  weight   number
);

--2. Viewing Table Information

select table_name, iot_name, iot_type, external, 
       partitioned, temporary, cluster_name
from   user_tables;

--3. Try It!

create table bricks (
  colour    varchar2(10),
  shape     varchar2(10)
);

select table_name
from   user_tables
where  table_name = 'BRICKS';

--4. Table Organization

create table toys_heap (
  toy_name varchar2(100)
) organization heap;

select table_name, iot_name, iot_type, external, 
       partitioned, temporary, cluster_name
from   user_tables
where  table_name = 'TOYS_HEAP';

--5. Index-Organized Tables

create table toys_iot (
  toy_id   integer primary key,
  toy_name varchar2(100)
) organization index;

select table_name, iot_type
from   user_tables
where  table_name = 'TOYS_IOT';

--6. Try It!

create table bricks_iot (
  bricks_id integer primary key
) organization index;

select table_name, iot_type
from   user_tables
where  table_name = 'BRICKS_IOT';

--7. External Tables

create or replace directory toy_dir as '/path/to/file';

create table toys_ext (
  toy_name varchar2(100)
) organization external (
  default directory tmp
  location ('toys.csv')
);

--8. Temporary Tables

create global temporary table toys_gtt (
  toy_name varchar2(100)
);

create private temporary table ora$ptt_toys (
  toy_name varchar2(100)
);

select table_name, temporary
from   user_tables
where  table_name in ( 'TOYS_GTT', 'ORA$PTT_TOYS' );

--9. Partitioning Tables

create table toys_range (
  toy_name varchar2(100)
) partition by range ( toy_name ) (
  partition p0 values less than ('b'),
  partition p1 values less than ('c')
);

create table toys_list (
  toy_name varchar2(100)
) partition by list ( toy_name ) (
  partition p0 values ('Sir Stripypants'),
  partition p1 values ('Miss Snuggles')
);

create table toys_hash (
  toy_name varchar2(100)
) partition by hash ( toy_name ) partitions 4;

create table toys_part_iot (
  toy_id   integer primary key,
  toy_name varchar2(100)
) organization index 
  partition by hash ( toy_id ) partitions 4;
  
select table_name, partitioned 
from   user_tables
where  table_name in ( 'TOYS_HASH', 'TOYS_LIST', 'TOYS_RANGE', 'TOYS_PART_IOT' );

select table_name, partition_name
from   user_tab_partitions;

--10. Try It!

create table bricks_hash (
  brick_id integer
) partition by hash ( brick_id ) partitions 8;

select table_name, partitioned 
from   user_tables
where  table_name = 'BRICKS_HASH';

--11. Table Clusters

create cluster toy_cluster (
  toy_name varchar2(100)
);

create table toys_cluster_tab (
  toy_name varchar2(100)
) cluster toy_cluster ( toy_name );

create table toy_owners_cluster_tab (
  owner    varchar2(20),
  toy_name varchar2(100)
) cluster toy_cluster ( toy_name );

select cluster_name from user_clusters;

select table_name, cluster_name
from   user_tables
where  table_name in ( 'TOYS_CLUSTER_TAB', 'TOY_OWNERS_CLUSTER_TAB' );

--12. Dropping Tables

select table_name
from   user_tables
where  table_name = 'TOYS_HEAP';

drop table toys_heap;

select table_name
from   user_tables
where  table_name = 'TOYS_HEAP';

--13. Try It!

drop table toys ;

select table_name
from   user_tables
where  table_name = 'TOYS';

Introduction to SQL.sql

--1. Creating Tables
create table DEPARTMENTS (  
  deptno        number,  
  name          varchar2(50) not null,  
  location      varchar2(50),  
  constraint pk_departments primary key (deptno)  
);

create table EMPLOYEES (  
  empno             number,  
  name              varchar2(50) not null,  
  job               varchar2(50),  
  manager           number,  
  hiredate          date,  
  salary            number(7,2),  
  commission        number(7,2),  
  deptno           number,  
  constraint pk_employees primary key (empno),  
  constraint fk_employees_deptno foreign key (deptno) 
      references DEPARTMENTS (deptno)  
);

--2. Creating Triggers
create or replace trigger  DEPARTMENTS_BIU
    before insert or update on DEPARTMENTS
    for each row
begin
    if inserting and :new.deptno is null then
        :new.deptno := to_number(sys_guid(), 
          'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end;
/

create or replace trigger EMPLOYEES_BIU
    before insert or update on EMPLOYEES
    for each row
begin
    if inserting and :new.empno is null then
        :new.empno := to_number(sys_guid(), 
            'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end;
/

--3. Inserting Data
insert into departments (name, location) values
   ('Finance','New York');

insert into departments (name, location) values
   ('Development','San Jose');
   
select * from departments;


insert into EMPLOYEES 
   (name, job, salary, deptno) 
   values
   ('Sam Smith','Programmer', 
    5000, 
  (select deptno 
  from departments 
  where name = 'Development'));

insert into EMPLOYEES 
   (name, job, salary, deptno) 
   values
   ('Mara Martin','Analyst', 
   6000, 
   (select deptno 
   from departments 
   where name = 'Finance'));

insert into EMPLOYEES 
   (name, job, salary, deptno) 
   values
   ('Yun Yates','Analyst', 
   5500, 
   (select deptno 
   from departments 
   where name = 'Development'));
   
--4. Indexing Columns
select table_name "Table", 
       index_name "Index", 
       column_name "Column", 
       column_position "Position"
from  user_ind_columns 
where table_name = 'EMPLOYEES' or 
      table_name = 'DEPARTMENTS'
order by table_name, column_name, column_position

create index employee_dept_no_fk_idx 
on employees (deptno)

create unique index employee_ename_idx
on employees (name)

--5. Querying Data
select * from employees;

select e.name employee,
           d.name department,
           e.job,
           d.location
from departments d, employees e
where d.deptno = e.deptno(+)
order by e.name;

select e.name employee,
          (select name 
           from departments d 
           where d.deptno = e.deptno) department,
           e.job
from employees e
order by e.name;

--6. Adding Columns
alter table EMPLOYEES 
add country_code varchar2(2);

--7. Querying the Oracle Data Dictionary
select table_name, tablespace_name, status
from user_tables
where table_Name = 'EMPLOYEES';

select column_id, column_name , data_type
from user_tab_columns
where table_Name = 'EMPLOYEES'
order by column_id;

--8. Updating Data
update employees
set country_code = 'US';

update employees
set commission = 2000
where  name = 'Sam Smith';

select name, country_code, salary, commission
from employees
order by name;

--9. Aggregate Queries
select 
      count(*) employee_count,
      sum(salary) total_salary,
      sum(commission) total_commission,
      min(salary + nvl(commission,0)) min_compensation,
      max(salary + nvl(commission,0)) max_compensation
from employees;

--10. Compressing Data
alter table EMPLOYEES compress for oltp; 
alter table DEPARTMENTS compress for oltp; 

--11. Deleting Data
delete from employees 
where name = 'Sam Smith';

--12. Dropping Tables
drop table departments cascade constraints;
drop table employees cascade constraints;

--13. Un-dropping Tables
select object_name, 
       original_name, 
       type, 
       can_undrop, 
       can_purge
from recyclebin;

flashback table DEPARTMENTS to before drop;
flashback table EMPLOYEES to before drop;
select count(*) departments 
from departments;
select count(*) employees
from employees;

Subqueries - Databases for Developers.sql

--0. Data Setup
create table bricks (
  brick_id integer,
  colour   varchar2(10)
);

create table colours (
  colour_name           varchar2(10),
  minimum_bricks_needed integer
);

insert into colours values ( 'blue', 2 );
insert into colours values ( 'green', 3 );
insert into colours values ( 'red', 2 );
insert into colours values ( 'orange', 1);
insert into colours values ( 'yellow', 1 );
insert into colours values ( 'purple', 1 );

insert into bricks values ( 1, 'blue' );
insert into bricks values ( 2, 'blue' );
insert into bricks values ( 3, 'blue' );
insert into bricks values ( 4, 'green' );
insert into bricks values ( 5, 'green' );
insert into bricks values ( 6, 'red' );
insert into bricks values ( 7, 'red' );
insert into bricks values ( 8, 'red' );
insert into bricks values ( 9, null );

commit;

--1. Introduction
select * from bricks;

select * from colours;

--2. Inline Views
select * from (
  select * from bricks
)

select * from (
  select colour, count(*) c
  from   bricks
  group  by colour
) brick_counts

/*
-which colours meet the minimum?
*/

select * from (
  select colour, count(*) c
  from   bricks
  group  by colour
) brick_counts
join   colours
on     brick_counts.colour = colours.colour_name
and    brick_counts.c < colours.minimum_bricks_needed

--3. Try it!
select * from (
    select colour, min(brick_id) as min_brick_id, max(brick_id) as max_brick_id
    from bricks
    group by colour
) brick_colours

--4. Nested Subqueries
select * from colours c
where  c.colour_name in (
  select b.colour from bricks b
);

/*
-select the bricks with colours
*/

select * from colours c
where  exists (
  select null from bricks b
  where  b.colour = c.colour_name
);

select * from colours c
where  c.colour_name in (
  select b.colour from bricks b
  where  b.brick_id < 5
);

/*
-select bricks with colours and id less than 5
*/

select * from colours c
where  exists (
  select null from bricks b
  where  b.colour = c.colour_name 
  and    b.brick_id < 5
);

--5. Correlated vs. Uncorrelated
select * from colours
where  exists (
  select null from bricks
);

/*
-correlated subquery = joins to table from parent query
-uncorrelated = does not join to table from parent query
-exists returns only rows from parent query
-different than in
-exists subquery can select anything as it is irrelevant
-to find all colours with at least 1 brick of the same colour, join in subquery needed
*/

select * from colours
where  exists (
  select 1 from bricks
);

--6. NOT IN vs NOT EXISTS
select * from colours c
where  not exists (
  select null from bricks b
  where  b.colour = c.colour_name
);

/*
-find all colours without a brick
*/

select * from colours c
where  c.colour_name not in (
  select b.colour from bricks b
);

/*
-no data returned as there is a brick with a null colour
*/

select * from colours c
where c.colour_name not in (
  'red', 'green', 'blue', 
  'orange', 'yellow', 'purple',
  null
);

/*
-true NOT IN requires ALL parent table rows to return false
-recall null cannot return true/false
-null returns unknown
-use NOT EXISTS or where to ignore null in subquery
*/

select * from colours c
where c.colour_name not in (
  select b.colour from bricks b
  where  b.colour is not null
);

--7.Try it!
select * from bricks b
where  b.colour in (
   select colour_name
   from colours c
   where c.minimum_bricks_needed = 2
);

--8. Scalar Subqueries
select colour_name, (
         select count(*) 
         from   bricks b
         where  b.colour = c.colour_name
         group  by b.colour
       ) brick_counts
from   colours c;

/*
-scalar subqueries return only 1 col and max 1 row
-count # of bricks by colour
-nulls are returned
*/

select colour_name, nvl ( (
         select count(*) 
         from   bricks b
         where  b.colour = c.colour_name
         group  by b.colour
       ), 0 ) brick_counts
from   colours c;

/*
-to show zero instead of null, use NVL or coalesce
*/

select colour_name, coalesce ( (
         select count(*) 
         from   bricks b
         where  b.colour = c.colour_name
         group  by b.colour
       ), 0 ) brick_counts
from   colours c;

select c.colour_name, (
         select count(*) 
         from   bricks b
         group  by colour
       ) brick_counts
from   colours c;

/*
-this query returns 4 counts which will not work for a scalar
-need to join bricks with colours in subquery (i.e. correlate)
-HAVING can use scalar instead of join
*/

select colour, count(*) count
from   bricks b
group  by colour
having count(*) < (
  select c.minimum_bricks_needed 
  from   colours c
  where  c.colour_name = b.colour
);

--9. Try it!
select c.colour_name, (
    select min(brick_id)
    from   bricks b
    where  b.colour = c.colour_name
    group by colour
    ) min_brick_id
from   colours c
where  c.colour_name is not null;

--10. Common Table Expressions
with brick_colour_counts as (
  select colour, count(*) 
  from   bricks
  group  by colour
) 
  select * from brick_colour_counts ;

--11. CTEs: Reusable Subqueries
select c.colour_name, 
       c.minimum_bricks_needed, (
         select avg ( count(*) )
         from   bricks b
         group  by b.colour
       ) mean_bricks_per_colour
from   colours c
where  c.minimum_bricks_needed < (
  select count(*) c
  from   bricks b
  where  b.colour = c.colour_name
  group  by b.colour
);

/*
-group by colour appears twice
-assign name with CTE e.g. brick_counts bc
*/

with brick_counts as (
  select b.colour, count(*) c
  from   bricks b
  group  by b.colour
)
  select c.colour_name, 
         c.minimum_bricks_needed, (
           select avg ( bc.c )
           from   brick_counts bc
         ) mean_bricks_per_colour
  from   colours c
  where  c.minimum_bricks_needed < (
    select bc.c
    from   brick_counts bc
    where  bc.colour = c.colour_name
  );
  
--12. Literate SQL
select brick_id 
from   bricks 
where  colour in ('red', 'blue');

select colour
from   bricks
group  by colour  
having count (*) < (
  select avg ( colour_count ) 
  from   (
    select colour, count (*) colour_count
    from   bricks
    group  by colour  
  )
);

/*
Count the bricks by colour
Take the average of these counts
Return those rows where the value in step 1 is greater than in step 2
-step 1 is at the bottom
-solution is to use CTE
*/

with brick_counts as ( 
  -- 1. Count the bricks by colour
  select b.colour, count(*) c
  from   bricks b
  group  by b.colour
), average_bricks_per_colour as ( 
  -- 2. Take the average of these counts
  select avg ( c ) average_count
  from   brick_counts
)
  select * from brick_counts bc  
  join   average_bricks_per_colour ac
  -- 3. Return those rows where the value in step 1 is less than in step 2
  on     bc.c < average_count; 
  
--13. Testing Subqueries
with brick_counts as (
  select b.colour, count(*) count
  from   bricks b
  group  by b.colour
), average_bricks_per_colour as (
  select avg ( count ) average_count
  from   brick_counts
)
  select * from brick_counts bc;
  
--much more robust than inline views

--14. Try it!
--count how many rows there are in colours
with colour_count as (
    select count(*) as count
    from colours c
)
  select * from colour_count;