Chapter 6
Creating tables
---------------------
p. 212

create table sec0601_foods
(menu_item      byte,
 supplier_id     varchar(3),
 product_code    varchar(2),
 description     varchar(20),
 price           money,
price_increase   money);

select *
from sec0601_foods;
(works in Access)

-----------------------
p. 220 - 221

create table sec0603_text_datatypes
(row_id                  varchar(3),
 variable_length_string  varchar(3),
 fixed_length_string     char(3),
 long_string             memo);


Good Inputs

insert into sec0603_text_datatypes
values ('A', '1', '1', '1');

insert into sec0603_text_datatypes
values ('B', '22', '22', string (1000, '2'));

insert into sec0603_text_datatypes
values ('C', '333', '333', string (1000, '3'));


Truncation in variable length field (BAD!!)

insert into sec0603_text_datatypes
values ('D', '4444', '22', string (1000, '4'));


Truncation in fixed length field (BAD!!)

insert into sec0603_text_datatypes
values ('E', '22', '444', string (1000, '5'));

Memory overflow in memo field (BAD!!!)
insert into sec0603_text_datatypes
values ('F', '22', '22', string (1000000, '6'));


Checking the length of fields in the table (length function)

select row_id,
       len(variable_length_string) as length_of_vl_string,
       len(fixed_length_string) as length_of_fl_string,
       len(long_string) as length_of_long_string
from sec0603_text_datatypes;

-----------------------
p. 223  Putting data into a new table from an old table

insert into sec0601_foods
select menu_item,
       supplier_id, 
       product_code,
       description,
       price, 
       price_increase
from l_foods;

-----------------------
p. 227 - Adding a primary key to a table

alter table sec0609_foods
add constraint pk_sec0609_foods
primary key (supplier_id, product_code);

-----------------------
p. 228  Changing a primary key in Access

a) 
alter table sec0610_foods
drop constraint pk_sec0610_foods;

b) 
alter table sec0610_foods
add constraint pk_sec0610_foods
primary key (menu_item);

-----------------------
p. 230 - adding a column to a table

alter table sec0611_foods
add column date_introduced datetime;

-----------------------
p. 232  Altering a column length

alter table sec0612_foods
alter column description varchar(25);

-----------------------
p. 233  Removing a column from the table

alter table sec0613_foods
drop column price_increase;
-----------------------
p. 234  Making other changes to a table

a) make new table

select first_name,
       last_name,
       phone_number as ext,
       '             ' as notes
into sec0614_phone_list
from l_employees
where employee_id between 203 and 206;

b) change ext for woods

update sec0614_phone_list
set ext = '9408'
where last_name = 'WOODS';
-----------------------
p. 239 Eliminate duplicate rows

select distinct *
into sec0616_no_duplicate_rows
from sec0615b;
-----------------------
p. 241  Distinguish duplicate rows

a) create new table

select *
into sec0617c
from sec0615a;

b) add column for row counter

alter table sec0617c
add column row_id counter

c) create new table with line numbers

select row_id,
       object_bought,
       price
into sec0617_with_line_numbers
from sec0617c;