Tables

CREATE TABLE Classification (
      Classif_ID         integer  NOT NULL PRIMARY KEY,
      Classification    varchar(25))


 INSERT into Classification VALUES( 1,'Pop')
 INSERT into Classification VALUES( 2,'Country')
 INSERT into Classification VALUES( 3,'Alternative')
 INSERT into Classification VALUES( 4,'Metal')




 CREATE TABLE CD (
      CD_ID              integer  NOT NULL,
     CD_Title           varchar(40),
      Composer_ID        integer  NOT NULL,
      Classif_ID         integer  NOT NULL,
      SalesPrice        money,
      AverageCost       money)

 INSERT into CD VALUES(2000,'John',100,1,16.99,6.99)
 INSERT into CD VALUES(2001,'Chica 16',107,1,14.99,5.99)
 INSERT into CD VALUES(2002,'Chica 17',107,1,14.99,5.99)
 INSERT into CD VALUES(2003,'Chica 18',107,1,14.99,5.99)
 INSERT into CD VALUES(2004,'Greatest Hits',107,1,16.99,7.99)
 INSERT into CD VALUES(2005,'Midnight',101,3,14.99,5.99)
 INSERT into CD VALUES(2006,'Mode',115,3,14.99,5.99)
 INSERT into CD VALUES(2007,'Ultra',115,3,15.99,5.99)
 INSERT into CD VALUES(2008,'Mindcrime',102,4,14.99,5.99)
 INSERT into CD VALUES(2009,'Empire',102,4,14.99,5.99)
 INSERT into CD VALUES(2010,'Land',102,4,12.99,4.99)
 INSERT into CD VALUES(2011,'Night',103,4,11.99,3.99)
 INSERT into CD VALUES(2012,'Pyromania',103,4,14.99,5.99)
INSERT into CD VALUES(2013,'Hysteria',103,4,14.99,5.99)
 INSERT into CD VALUES(2014,'Hits',103,4,13.99,4.99)
 INSERT into CD VALUES(2015,'Hits 2',104,2,15.99,6.99)
 INSERT into CD VALUES(2016,'Greatest',105,2,14.99,5.99)
 INSERT into CD VALUES(2017,'Hits 3',106,1,13.99,5.99)
 INSERT into CD VALUES(2018,'Deep',108,1,12.99,2.99)
INSERT into CD VALUES(2019,'Turning',109,1,14.99,5.99)
 INSERT into CD VALUES(2020,'TheHits',109,1,16.99,7.99)
 INSERT into CD VALUES(2021,'Cars',110,1,9.99,3.99)
 INSERT into CD VALUES(2022,'Anthology',110,1,25.99,11.99)
 INSERT into CD VALUES(2023,'City',110,1,14.99,5.99)
 INSERT into CD VALUES(2024,'Rick',111,1,11.99,2.99)
 INSERT into CD VALUES(2025,'Live',112,1,19.99,8.99)
 INSERT into CD VALUES(2026,'Pat',113,1,16.99,6.99)
 INSERT into CD VALUES(2027,'Big',114,1,14.99,5.99)
 INSERT into CD VALUES(2028,'Hurting',114,1,11.99,3.99)
 INSERT into CD VALUES(2029,'Vol 1',116,1,9.99,2.99)
 INSERT into CD VALUES(2030,'Vol 2',116,1,9.99,2.99)

 select * from Classification

 select * from CD

 select * into classificationdummy from Classification

   INSERT into classification VALUES( 7,'cad')
 INSERT into classification VALUES( 8,'auto') 

 --intersect

 select * from Classification 
  intersect
 select * from  classificationdummy 

 --union
select * from  classificationdummy 
union
 select * from Classification 
  
 --Except
  select * from Classification 
    EXCEPT 
  select * from  classificationdummy
  
 SELECT CONVERT(varchar(30),SalesPrice) from CD
 UNION
 Select Classification from Classification
--
create table Billings (
     BankerID           INTEGER,
     BillingNumber      INTEGER,
     BillingDate        datetime,
    BillingTotal       INTEGER,
     TermsID            INTEGER,
     BillingDueDate     datetime ,
     PaymentTotal       INTEGER,
     CreditTotal        INTEGER

 );


 INSERT INTO Billings VALUES (1, 1, '2005-01-22', 165, 1,'2005-04-22',123,321);



 INSERT INTO Billings VALUES (2, 2, '2001-02-21', 165, 1,'2002-02-22',123,321);



 INSERT INTO Billings VALUES (3, 3, '2003-05-02', 165, 1,'2005-04-12',123,321);



 INSERT INTO Billings VALUES (4, 4, '1999-03-12', 165, 1,'2005-04-18',123,321);



 INSERT INTO Billings VALUES (5, 5, '2000-04-23', 165, 1,'2005-04-17',123,321);



 INSERT INTO Billings VALUES (6, 6, '2001-06-14', 165, 1,'2005-04-18',123,321);



 INSERT INTO Billings VALUES (7, 7, '2002-07-15', 165, 1,'2005-04-19',123,321);



 INSERT INTO Billings VALUES (8, 8, '2003-08-16', 165, 1,'2005-04-20',123,321);



 INSERT INTO Billings VALUES (9, 9, '2004-09-17', 165, 1,'2005-04-21',123,321);



 INSERT INTO Billings VALUES (0, 0, '2005-10-18', 165, 1,'2005-04-22',123,321);





 create table Bankers(
    BankerID             Integer,
    BankerName           VARCHAR(20),
    BankerContactLName   VARCHAR(20),
    BankerContactFName   VARCHAR(20),
    BankerCity           VARCHAR(20),
    BankerState          VARCHAR(20),
    BankerZipCode        VARCHAR(20),
    BankerPhone          VARCHAR(20)
 )


 insert into Bankers values (1, 'ABC Inc.','Joe','Smith','Vancouver','BC','11111','111-111-1111');



 insert into Bankers values (2, 'DEF Inc.','Red','Rice', 'New York', 'DE','22222','222-222-2222');



 insert into Bankers values (3, 'HJI Inc.','Kit','Cat',  'Paris',    'CA','33333','333-333-3333');



 insert into Bankers values (4, 'QWE Inc.','Git','Black','Regina',   'ER','44444','444-444-4444');



 insert into Bankers values (5, 'RTY Inc.','Wil','Lee',  'Toronto',  'YU','55555','555-555-5555');



 insert into Bankers values (6, 'YUI Inc.','Ted','Larry','Calgary',  'TY','66666','666-666-6666');



 insert into Bankers values (7, 'OIP Inc.','Yam','Act',  'San Franc','FG','77777','777-777-7777');



 insert into Bankers values (8, 'SAD Inc.','Hit','Eat',  'Orland',   'PO','88888','888-888-8888');



 insert into Bankers values (9, 'DFG Inc.','Sad','Lee',  'Wisler',   'PL','99999','999-999-9999');



 insert into Bankers values (0, 'GHJ Inc.','Bit','Lee',  'Ticker',   'MN','00000','000-000-0000');


select * from Billings

 SELECT 'Active' AS Source, BillingNumber, BillingDate, BillingTotal
     FROM Billings
     WHERE BillingTotal - PaymentTotal - CreditTotal < 0
 UNION
     SELECT 'Paid' AS Source, BillingNumber, BillingDate, BillingTotal
     FROM Billings
     WHERE BillingTotal - PaymentTotal - CreditTotal <= 0
 ORDER BY BillingTotal DESC

 select * from Billings where BillingDueDate>'04/20/2005'
Except
 select * from Billings where BillingDueDate<'04/20/2005'

 select * from Billings
 case
 when (select Billings.BillingDueDate from Billings where BillingDueDate<'02/02/2003') then 'it is below 2003'
 end