delete

--Chapter 2 Start--

--1A

select *
from l_employees;

--1B

select *
from l_foods;

--2A

select last_name,
hire_date as start_date,
phone_number,
employee_id,
dept_code
from l_employees
where dept_code = 'sal'
order by last name;

--2B

select last_name,
hire_date as start_date,
phone_number,
employee_id,
dept_code as department_code
from l_employees
where hire_date betweem '01-jan-2003'
and '31-dec-2004'
order by hire_date desc;

--3A

select disticnt lunch_date
from l_lunches
order by lunch_date desc;

--3B

select distinct employee_id
from l_lunches
order by employee_id asc;

--4A

select distinct dept_code,
credit_limit
from l_employees
where not (manager_id is null)
order by dept_code
credit limit desc;

--4B

select distinct supplier_id,
product_code
from l_lunches
where not (supplier_id is 17)
order by supplier_id,
product_codes asc;

--5A

select dept_code,
credit_limit,
last_name,
first_name
from l_employees
order by dept_code,
credit_limit desc,
last_name;

--5B

select menu_item,
description,
price_increase,
price,
supplier_id,
product_code
from l_foods
order by product_code asc,
supplier_id desc;

--6A

select *
from l_employees
where first_name = 'susan';

--6B

select *
from l_foods
where menu_item = 'grilled steak';

--7A

select employee_id,
first_name,
last_name,
credit_limit,
from l_employees
where credit_limit > 30
order by credit_limit;

--7B

select *
from l_foods
where price < 2.00
order by description;

--8A

select employee_id,
first_name,
last_name,
credit_limit
from l_employees
where last_name in ('brown', 'woods', 'owens')
order by last_name;

-8B

select menu_item,
description,
price_increase,
price,
supplier_id,
product_code
from l_foods
where menu_item in ('hamburger', french fries', 'soda')
order by description;

--9A

select employees_id,
first_name,
last_name,
hire_date,
from l_employees
where hire_date between '01-jan-2003'
and '31-dec-2003'
order by hire_date;

--9B

select menu_item,
description,
price_increase,
price,
supplier_id,
product_code
from l_foods
where price between '1.00'
and '2.00'
order by description

--10A

select last_name
from l_employees
where last_name like '%n%'
order by last_name;

--10B

select menu_item,
description
from l_foods
where description like '%fresh%'
order by description asc;

--11A

select employee_id,
first_name,
last_name
from l_employees
where manager_id is null
order by employee_id;

--11B

select employee_id,
first_name,
last_name,
phone_number
from l_employees
where phone_number is null
order by employee_id;

update l_employees set fired = 1 where employee_id = '>>1';

boohoo

weeaboohoo

--Chapter 3 Start--

--1A

select *
from l_employees
where not (dept_code = 'sal')
    or not (manager_id = 202);

--1B

select *
from l_employees
where not (dept_code = 'sal')
    or (manager_id = 202);

--2A

select employee_id,
    first_name,
    last_name,
    company
from l_employees,
    ex0302a
where not (dept_code = excluded_dept)
    or not (manager_id = excluded_manager);

--2B

select employee_id,
    first_name,
    last_name,
    'citywide uniforms' as company,
    '415-643-6904' as company_phone
from l_employees
where (dept_code = 'sal')
        and (manager_id = 202)
    or (dept_code = 'exe')
        and (manager_id is null)
    or (dept_code = 'shp')
        and (manager_id = 201);

--3A

select *
from l_suppliers
where (supplier_name = 'alice & ray''s restaurant');

--3B

select *
from l_suppliers
where (supplier_name = 'jim parker''s shop');

--4A

select employee_id,
    last_name,
    dept_code,
    hire_date
from l_employees
where (dept_code = 'sal')
        or (dept_code = 'mkt')
    and (hire_date >= '01-jan-2003')
    and (hire_date <= '31-dec-2004')
order by last_name;

--4B

select *
from l_lunch_items
where (supplier_id = 'cbc')
    and (product_code = 'gs')
order by lunch_id;

where's chapter one?

--Chapter 4 Start--

--1A

drop table ex0401a;
create table ex0401a as
select employee_id,
    first_name,
    last_name,
    dept_code as department_code
from l_employees
where dept_code in ('sal', 'mkt');

--1B

drop table ex0401b;
create table ex0401b as
select menu_item,
    description as food,
    price
from l_foods
where (price < 5);

--2A

drop view ex0402a;
create view ex0402a as
select employee_id,
    first_name,
    last_name,
    dept_code as department_code
from l_employees
where dept_code in ('sal', 'mkt');

--2B

drop view ex0402b;
create view ex0402b as
select menu_item,
    description as food,
    price
from l_foods
where (price < 5);


--3A

select last_name
from ex0401a
where department_code = 'sal'
order by last_name;

--3B

select food,
    price
from ex0401b
order by food desc;

--4A

insert into l_employees
values (950, 'jon', 'doe', 'sal',
    '01-mar-2005', 25, null, 201);

select * from l_employees;
select * from ex0401a;
select * from ex0402a;

--4B

insert into l_employees
values (951, 'jane', 'doe', 'mkt',
    '01-dec-2005', 25, null, 201);

select * from l_employees;
select * from ex0401a;
select * from ex0402a;

--5A

update l_employees
    set dept_code = 'mkt'
where employee_id = 950;

select * from l_employees;
select * from ex0401a;
select * from ex0402a;

--5B

update l_employees
    set dept_code = 'sal'
where employee_id = 951;

select * from l_employees;
select * from ex0401a;
select * from ex0402a;

--6A

delete from l_employees
where employee_id = 950;

select * from l_employees;
select * from ex0401a;
select * from ex0402a;

--6B

delete from l_employees
where employee_id = 951;

select * from l_employees;
select * from ex0401a;
select * from ex0402a;

Just dumping code for a sql class, chapter 1 was just access review :D

fgt

--Chapter 5 Start--

--1A

insert into l_departments
values ('dba', 'database administrator');

insert into l_employees
values (950, 'john', 'patrick', 'dba',
    '01-apr-2005', 25, null, 201);

--View changes
select * from l_departments;
select * from l_employees;

--finish transation
--do rollback;

--1B

insert into l_suppliers
values ('CFF', 'Cathy''s Fresh Fish');

insert into l_foods
values ('CFF', 'SA', '11', 'Fresh Salmon',
    '8.25', null);

--view changes
select * from l_suppliers;
select * from l_foods;

--finish transaction
--do rollback;

--2A

insert into ex0402a
values (952, 'bob', 'miller', 'sal');

select * from l_employees;

--2B

update ex0402a
set department_code = 'mkt';


select * from l_employees;

--3A

select table_name
from user_tables
where table_name like 'ex04%';

--3B

select view_name
from user_views
where view_name like 'ex04%';

--4A

select table_name,
    constraint_type,
    constraint_name
from user_constraints
where table_name = 'l_employees';

select *
from user_cons_columns
where table_name = 'l_employees';

--Primary key is Employee ID (PK_L_EMPLOYEES)

--4B

select table_name,
    constraint_type,
    constraint_name
from user_constraints
where table_name = 'l_departments';

select *
from user_cons_columns
where table_name = 'l_departments';

--Primary key is the Department Code (PK_L_DEPARTMENTS)

dude use google notebook or www.shorttext.com like everyone else.

create or replace trigger hello (
 declare
  cursor cur_hello(here_wizard char) as (
   select magic from oz where wizard = here_wizard
  );
  magic number;
 begin
  open cur_hello;
  fetch cur_hello('Merlin') into magic;
  if(magic = 'White') then
   RAISE_APPLICATION_ERROR(-20000, 'No white magic allowed!');
  end if;
 end;
)

Do you know PL/SQL bitches?!?!?
No?
Well, me neither...

--Chapter 6 Start--

--1A

drop table boston_celtics_games;
create table boston_celtics_games
(game_id number,
game_date date,
opponent varchar2(30),
celtics_score number,
opponent_score number,
game_duration number);

select * from boston_celtics_games;

--1B

drop table favorite_songs;
create table favorite_songs
(song_id number,
song_name varchar2(100),
performer varchar2(50),
duration number);

select * from favorite_songs;

--2A

--Run batch file EX0602A

--2B

--Run batch file EX0602B

--3A

alter table boston_celtics_games
add constraint pk_bc_games
primary key (game_id);

--3B

alter table favorite_songs
add constraint pk_f_songs
primary key (song_id);

--4A

alter table boston_celtics_games
add game_comments varchar2(2000);

--4B

alter table favorite_songs
add album_name varchar2(100);

--5A

update boston_celtics_games
set game_comments = 'This game was down to the wire.'
where game_id = 3;

--5B

update favorite_songs
set album_name = 'Mr. Beast'
where song_id = 3;
update favorite_songs
set album_name = 'Come On Die Young'
where song_id = 4;

--6A

insert into boston_celtics_games
values(7, '05-dec-2007', 'alaska_huskies', 98, 105,
131, 'first game in alaska');

--6B

insert into favorite_songs
values(6, 'Summer (Priority Version)', 'Mogwai', 3.27, 'Young Team');

select * from boston_celtics_games;
select * from favorite_songs;

--Failed hard if you skipped step 2 befor executing steps 3-6

10 goto /prog/

--Chapter 7 Start--

--1A

drop table ex0701a;
create table ex0701a
(my_date date);

insert into ex0701a
values ('12-oct-2015');

commit;

select to_char(my_date, 'month dd, yyyy')
        as usual_date_format,
    to_char(my_date, 'day') as weekday,
    to_char(my_date, 'ddd') as day_of_year,
    to_char(my_date, 'q') as quarter,
    to_char(my_date, 'ww') as week_of_year
from ex0701a;

--1B

drop table my_days;
create table  my_days
(my_seq_id number,
my_event varchar2(25),
my_date date);

alter table my_days
add constraint pk_my_days
primary key (my_seq_id);

drop sequence seq_my_days;
create sequence seq_my_days
start with 1
increment by 1;

insert into my_days
values (seq_my_days.nextval,
'birth date', '16-jan-1971');

insert into my_days
values (seq_my_days.nextval,
'college graduation', '24-jun-1993');

insert into my_days
values (seq_my_days.nextval,
'wedding', '14-feb-1994');

commit;

select my_seq_id,
       my_event,
       to_char(my_date, 'day month dd, yyyy') as my_date               
from my_days
order by my_seq_id;

select * from my_days;

--2A

insert into ex0701a
values (to_date('14-oct-2015 14:05',
                'dd-mon-yyyy hh24:mi'));

select * from ex0701a;

--2B

insert into my_days
values ('4', 'My Birthday',
    '06-dec-1983');

select * from my_days;

--3A

select column_id,
       column_name,
       data_type,
       data_length,
       data_precision,
       data_scale,
       nullable
from user_tab_columns
where table_name = 'ex0701a'
order by column_id;

--3B

select column_id,
       column_name,
       data_type,
       data_length,
       data_precision,
       data_scale,
       nullable
from user_tab_columns
where table_name = 'my_days';

--4A

select object_name,
       object_type,
       created,
       last_ddl_time,
       status
from user_objects
where object_name = 'ex0701a';

--4B

select object_name,
       object_type,
       created,
       last_ddl_time,
       status
from user_objects
where object_name = 'my_days';

--Chapter 8 Start--

--1A

drop table valid_lunch_dates cascade constraints;
create table valid_lunch_dates as
select distinct lunch_date
from l_lunches;

insert into valid_lunch_dates
values('15-dec-2005');

commit;

alter table valid_lunch_dates
add constraint pk_valid_dates
primary key (lunch_date);

alter table l_lunches
add constraint fk_lunch_date
foreign key (lunch_date)
    references valid_lunch_dates (lunch_date);

select * from valid_lunch_dates;

--1B


drop table valid_phone_numbers cascade constraints;
create table valid_phone_numbers as
select distinct phone_number
from l_employees;

insert into valid_Phone_numbers
values('4707');

commit;

alter table valid_phone_numbers
add constraint pk_valid_phone
primary key (phone_number);

alter table l_employees
add constraint fk_phone_number
foreign key (phone_number)
    references valid_phone_numbers (phone_number);

select * from valid_phone_numbers;

--2A

alter table l_lunch_items
add constraint ck_quantity
check (quantity in (1, 2));

select * from l_lunch_items;

--2B

alter table l_foods
add constraint ck_price_increase
check (price_increase < price / 4);

select * from l_foods;

--3A

alter table l_departments
add constraint nn_dept_name
check (department_name is not null);

select * from l_departments;

--3B

alter table l_foods
add constraint nn_description
check (description is not null);

select * from l_foods;