№5, Sql Server - rextester

Language: Layout:

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 * from person
where hiredate = (select hiredate from person where first_name = 'John' and last_name = 'Smith')

SELECT * FROM product WHERE product_price <
(SELECT AVG(product_price) FROM product);

SELECT * from
  product as pr
where
  1 < (select sum(quantity) from purchase as pu where pr.product_name = pu.product_name)
  
 SELECT product_name, product_price * 1.15 as 'New Price' from
  product as pr
where
  1 < (select sum(quantity) from purchase as pu where pr.product_name = pu.product_name);
  
SELECT last_name, first_name FROM person
WHERE EXISTS
 (SELECT * FROM purchase
WHERE person.person_code = purchase.salesperson);

SELECT * FROM product
WHERE product_price < any
  (SELECT pr.product_price
   FROM purchase as pu, product as pr
   WHERE pu.product_name = pr.product_name AND pu.salesperson = 'GA');
   
   SELECT * FROM product
WHERE product_price < some
  (SELECT pr.product_price
   FROM purchase as pu, product as pr
   WHERE pu.product_name = pr.product_name AND pu.salesperson = 'GA');

View schema
Execution time: 0,08 sec, rows selected: 28, rows affected: 25, absolute service time: 0,24 sec