№3, Sql Server - rextester

№3

create table person( 
    person_code varchar(3) NOT NULL PRIMARY KEY, 
    first_name VARCHAR(15) not null,
    last_name varchar(20) not null,
    hiredate date);

create table product(
    product_name varchar(25) not null primary key,
    product_price numeric(8,2),
    quantity_on_hand numeric(5,0),
    laststockdate date);

create table purchase(
    product_name varchar(25) foreign key references product(product_name),
    salesperson varchar(3) FOREIGN KEY references person(person_code),
    purchase_date date,
    quantity numeric(4,2));
    
create table purchase_archive(
    product_name varchar(25),
    salesperson varchar(3),
    purchase_date date,
    quantity numeric(4,2));
    
create table old_item(
    item_id char(20),
    item_desc char(25));
    
    
insert into person(person_code, first_name, last_name, hiredate) values('BB', 'Bobby', 'Barkenhagen', '2010-02-28');
insert into person(person_code, first_name, last_name, hiredate) values('CA', 'Charlene', 'Atlas', '2010-02-01');
insert into person(person_code, first_name, last_name, hiredate) values('DS', 'Dany', 'Smith', '2002-02-15');
insert into person(person_code, first_name, last_name, hiredate) values('GA', 'Gary', 'Anderson', '2010-02-15');
insert into person(person_code, first_name, last_name, hiredate) values('JS', 'John', 'Smith', '2002-02-15');
insert into person(person_code, first_name, last_name, hiredate) values('LB', 'Laren', 'Baxter', '2010-03-01');
insert into product(product_name, product_price, quantity_on_hand, laststockdate) values('Chrome Phoobar', '50.00', '100', '2011-01-15');
insert into product(product_name, product_price, quantity_on_hand, laststockdate) values('Extra Huge Mega Phoobar +', '9.5', '1234', '2012-01-15');
insert into product(product_name, product_price, quantity_on_hand, laststockdate) values('Large Widget', null, '5', '2011-01-11');
insert into product(product_name, product_price, quantity_on_hand, laststockdate) values('Medium Widget', '75.00', '1000', '2010-01-15');
insert into product(product_name, product_price, quantity_on_hand, laststockdate) values('Round Chrome Snaphoo', '25.00', '10000', null);
insert into product(product_name, product_price, quantity_on_hand, laststockdate) values('Small Widget', '99.00', '1', '2011-01-15');
insert into product(product_name, product_price, quantity_on_hand, laststockdate) values('Square Zinculator', '45.00', '1', '2010-12-31');
insert into purchase(product_name, salesperson, purchase_date, quantity) values('Small Widget', 'CA', '2011-07-14', '1.00');
insert into purchase(product_name, salesperson, purchase_date, quantity) values('Medium Widget', 'BB', '2011-07-14', '75.00');
insert into purchase(product_name, salesperson, purchase_date, quantity) values('Chrome Phoobar', 'GA', '2011-07-14', '2.00');
insert into purchase(product_name, salesperson, purchase_date, quantity) values('Small Widget', 'GA', '2011-07-15', '8.00');
insert into purchase(product_name, salesperson, purchase_date, quantity) values('Medium Widget', 'LB', '2011-07-15', '20.00');
insert into purchase(product_name, salesperson, purchase_date, quantity) values('Round Chrome Snaphoo', 'CA', '2011-07-16', '5.00');
insert into purchase_archive(product_name, salesperson, purchase_date, quantity) values('Round Snaphoo', 'BB', '2001-06-21', '10.00');
insert into purchase_archive(product_name, salesperson, purchase_date, quantity) values('Large Half linger', 'GA', '2001-06-22', '50.00');
insert into purchase_archive(product_name, salesperson, purchase_date, quantity) values('Medium Wodget', 'LB', '2001-06-23', '20.00');
insert into purchase_archive(product_name, salesperson, purchase_date, quantity) values('Small Widget', 'ZZ', '2002-06-24', '80.00');
insert into purchase_archive(product_name, salesperson, purchase_date, quantity) values('Chrome Phoobar', 'CA', '2002-06-25', '2.00');
insert into purchase_archive(product_name, salesperson, purchase_date, quantity) values('Small Widget', 'JT', '2002-06-26', '50.00');

select product.*, purchase.* 
from product, purchase;

SELECT product.product_name, product.quantity_on_hand, purchase.quantity
FROM product, purchase
where product.product_name = purchase.product_name;

SELECT purchase.product_name, product.laststockdate, person.first_name, person.last_name
FROM purchase, Person,  Product
WHERE person.person_code = purchase.salesperson AND product.product_name = purchase.product_name;

select purc.product_name, pers.first_name, pers.last_name
from purchase as purc
left join person as pers on pers.person_code = purc.salesperson;

select salesperson from purchase_archive
EXCEPT
select salesperson from purchase

select salesperson from purchase_archive
union all
select salesperson from purchase


run \| edit \| history \| help	0