USE g1111;

--CUST_ORDERS Table Build and Fill

IF OBJECT_ID('CUST_ORDERS', 'U') IS NOT NULL DROP TABLE CUST_ORDERS;

CREATE TABLE CUST_ORDERS (

    Order_ID INT NOT NULL,
    Cust_ID INT NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    Payment VARCHAR(20) NOT NULL,
    Tran_DT DATE NOT NULL,
    Dept VARCHAR(20) NOT NULL,
    PRIMARY KEY (Order_ID)
);

INSERT INTO CUST_ORDERS (Order_ID, Cust_ID, Price, Payment, Tran_DT, Dept) VALUES
(1000, 732046, 155.97, 'Cash', '2013-03-17', 'Dairy'),
(1001, 732027, 837.00, 'Check', '2011-02-08', 'Clothing'),
(1500, 908132, 665.00, 'Cash', '2018-03-08', 'Electronics'),
(1003, 876661, 325.00, 'Cash', '2019-02-19', 'Electronics'),
(1004, 908588, 247.00, 'Check', '2018-02-05', 'Clothing'),
(1005, 876551, 579.00, 'Check', '2002-06-10', 'Dairy'),
(1006, 908586, 708.00, 'Credit Card', '2017-03-29', 'Electronics'),
(1007, 732018, 751.00, 'Check', '1998-10-19', 'Furniture'),
(1008, 720410, 422.00, 'Cash', '2020-11-10', 'Furniture'),
(1009, 908588, 1178.00, 'Cash', '2021-07-15', 'Furniture'),
(1010, 600230, 747.00, 'Credit Card', '2021-12-04', 'Toys'),
(1011, 908591, 1226.00, 'Credit Card', '2018-08-15', 'Clothing'),
(1012, 908132, 900.00, 'Check', '2012-05-27', 'Electronics'),
(1013, 720410, 554.00, 'Credit Card', '2010-08-29', 'Furniture'),
(1014, 732046, 789.00, 'Check', '2019-08-09', 'Furniture'),
(1015, 876551, 1014.00, 'Check', '2018-02-24', 'Electronics'),
(1016, 720390, 1287.00, 'Cash', '2017-07-10', 'Electronics'),
(1017, 903140, 1052.00, 'Check', '2001-10-12', 'Dairy'),
(1018, 720410, 645.00, 'Cash', '2017-01-01', 'Dairy'),
(1019, 876551, 346.00, 'Check', '1999-04-14', 'Dairy'),
(1020, 908588, 1264.00, 'Check', '2021-04-10', 'Dairy'),
(1021, 732046, 496.00, 'Cash', '2022-01-17', 'Furniture'),
(1501, 876661, 128.00, 'Credit Card', '2016-05-18', 'Toys'),
(1023, 732027, 776.00, 'Credit Card', '2017-10-08', 'Dairy'),
(1024, 908586, 483.00, 'Credit Card', '2012-12-18', 'Furniture'),
(1600, 908132, 665.00, 'Cash', '2018-03-08', 'Electronics'),
(1026, 908132, 437.00, 'Cash', '2020-03-05', 'Toys'),
(1027, 720410, 782.00, 'Credit Card', '2018-03-02', 'Dairy'),
(1028, 720410, 118.00, 'Credit Card', '2018-02-01', 'Furniture'),
(1029, 600230, 148.00, 'Cash', '2001-08-18', 'Furniture'),
(1030, 732027, 434.00, 'Cash', '2017-01-23', 'Clothing'),
(1031, 732018, 1144.00, 'Cash', '1998-09-26', 'Dairy'),
(1032, 600264, 573.00, 'Credit Card', '2020-05-14', 'Dairy'),
(1033, 720410, 1024.00, 'Check', '2022-06-08', 'Dairy'),
(1034, 908132, 338.00, 'Cash', '2021-09-12', 'Electronics'),
(1035, 876661, 947.00, 'Credit Card', '2017-12-07', 'Clothing'),
(1036, 908586, 1275.00, 'Credit Card', '2013-04-10', 'Furniture'),
(1037, 732018, 937.00, 'Check', '2010-12-14', 'Clothing'),
(1038, 732046, 454.00, 'Check', '2019-05-29', 'Dairy'),
(1039, 600230, 840.00, 'Credit Card', '2018-05-26', 'Clothing'),
(1040, 876551, 1255.00, 'Cash', '2018-04-22', 'Clothing'),
(1041, 876661, 1087.00, 'Credit Card', '2002-07-04', 'Furniture'),
(1042, 903140, 828.00, 'Credit Card', '2017-01-17', 'Dairy'),
(1043, 908588, 383.00, 'Credit Card', '1998-03-27', 'Furniture'),
(1044, 908588, 849.00, 'Credit Card', '2020-04-28', 'Furniture'),
(1045, 908586, 1156.00, 'Cash', '2022-01-15', 'Electronics'),
(1046, 732018, 625.00, 'Cash', '2021-08-29', 'Toys'),
(1047, 876551, 902.00, 'Credit Card', '2018-07-31', 'Toys'),
(1048, 908591, 913.00, 'Cash', '2013-02-02', 'Dairy'),
(1049, 600264, 342.00, 'Credit Card', '2011-02-12', 'Furniture'),
(1050, 908588, 1251.00, 'Cash', '2019-05-12', 'Furniture'),
(1051, 903140, 169.00, 'Check', '2019-02-03', 'Dairy'),
(1052, 908588, 427.00, 'Cash', '2018-01-03', 'Dairy'),
(1053, 600264, 855.00, 'Cash', '2001-09-27', 'Dairy'),
(1054, 908591, 218.00, 'Cash', '2017-02-08', 'Furniture'),
(1055, 908586, 732.00, 'Credit Card', '1998-12-28', 'Furniture'),
(1056, 720390, 431.00, 'Check', '2020-09-26', 'Electronics'),
(1057, 600230, 517.00, 'Cash', '2021-09-28', 'Toys'),
(1058, 720390, 1176.00, 'Credit Card', '2022-02-07', 'Clothing'),
(1059, 876661, 804.00, 'Credit Card', '2018-08-04', 'Toys'),
(1060, 732027, 559.00, 'Check', '2013-04-05', 'Clothing'),
(1061, 908586, 192.00, 'Cash', '2010-08-07', 'Dairy'),
(1062, 903140, 630.00, 'Credit Card', '2019-08-17', 'Electronics'),
(1063, 732046, 1137.00, 'Cash', '2018-03-08', 'Electronics'),
(1064, 600264, 357.00, 'Check', '2018-03-25', 'Electronics'),
(1065, 908591, 1038.00, 'Check', '2002-04-09', 'Dairy'),
(1066, 732027, 1102.00, 'Credit Card', '2017-02-14', 'Electronics'),
(1067, 908132, 427.00, 'Credit Card', '1998-08-22', 'Toys'),
(1068, 908588, 1040.00, 'Credit Card', '2020-03-13', 'Clothing'),
(1069, 908591, 190.00, 'Check', '2022-01-29', 'Furniture'),
(1070, 908586, 487.00, 'Credit Card', '2021-02-06', 'Furniture'),
(1071, 908132, 941.00, 'Cash', '2018-07-18', 'Toys'),
(1072, 732027, 557.00, 'Cash', '2013-01-29', 'Electronics'),
(1073, 908591, 940.00, 'Credit Card', '2010-12-13', 'Toys'),
(1074, 908588, 139.00, 'Cash', '2020-01-13', 'Furniture'),
(1075, 908591, 817.00, 'Cash', '2018-09-19', 'Clothing'),
(1076, 600230, 1173.00, 'Cash', '2017-12-20', 'Clothing'),
(1077, 720390, 716.00, 'Credit Card', '2002-06-24', 'Furniture'),
(1078, 908132, 863.00, 'Credit Card', '2016-12-25', 'Toys'),
(1079, 876661, 1250.00, 'Credit Card', '1999-03-03', 'Furniture'),
(1080, 720410, 754.00, 'Check', '2021-02-24', 'Electronics'),
(1081, 908132, 840.00, 'Check', '2021-08-13', 'Dairy'),
(1082, 908588, 1117.00, 'Cash', '2021-12-12', 'Toys'),
(1083, 876661, 643.00, 'Credit Card', '2017-11-21', 'Electronics'),
(1084, 600230, 1206.00, 'Check', '2012-04-25', 'Dairy'),
(1085, 876661, 716.00, 'Credit Card', '2010-09-26', 'Toys'),
(1086, 720410, 525.00, 'Check', '2019-10-04', 'Dairy'),
(1087, 600264, 567.00, 'Cash', '2019-01-11', 'Dairy'),
(1088, 732046, 623.00, 'Cash', '2018-06-03', 'Electronics'),
(1089, 908132, 192.00, 'Cash', '2001-11-09', 'Furniture'),
(1090, 876661, 287.00, 'Credit Card', '2016-04-07', 'Furniture'),
(1091, 908132, 1246.00, 'Cash', '1999-02-05', 'Toys'),
(1092, 732027, 351.00, 'Credit Card', '2021-01-16', 'Furniture'),
(1093, 908588, 683.00, 'Credit Card', '2021-09-18', 'Dairy'),
(1094, 732046, 784.00, 'Cash', '2021-05-23', 'Toys'),
(1095, 876551, 461.00, 'Cash', '2017-11-04', 'Electronics'),
(1096, 720390, 1112.00, 'Credit Card', '2013-01-06', 'Furniture'),
(1097, 903140, 277.00, 'Check', '2010-07-15', 'Toys'),
(1098, 908586, 197.00, 'Cash', '2019-06-17', 'Electronics'),
(1099, 600264, 520.00, 'Cash', '2018-12-21', 'Dairy'),
(1900, 908132, 665.00, 'Cash', '2018-03-08', 'Electronics'),
(1666, 720390, 874.00, 'Check', '2022-01-05', 'Toys'),
(1667, 908132, 675.00, 'Cash', '2019-09-30', 'Electronics'),
(1709, 600264, 37.00, 'Check', '2021-07-28', 'Dairy'),
(1999, 908132, 665.00, 'Cash', '2018-03-08', 'Electronics'),
(1901, 876661, 128.00, 'Credit Card', '2016-05-18', 'Toys');

--CREATE VET_VISIT table

IF OBJECT_ID('VET_VISITS', 'U') IS NOT NULL DROP TABLE VET_VISITS;
CREATE TABLE VET_VISITS (
    Visit_ID INT NOT NULL,
    Pet VARCHAR(50) NOT NULL,
    Breed VARCHAR(50) NOT NULL,
    Species VARCHAR(20) NOT NULL,
    Owner VARCHAR(50) NOT NULL,
    Reason VARCHAR(50) NOT NULL
);

INSERT INTO VET_VISITS (Visit_ID, Pet, Breed, Species, Owner, Reason) VALUES
(200, 'Daisy', 'Calico', 'Feline', 'Olivia', 'Stomach upset'),
(201, 'Lucy', 'Rottweiler', 'Canine', 'Mark', 'Grooming'),
(202, 'Bailey', 'Chihuahua', 'Canine', 'Stephanie', 'Routine exam'),
(203, 'Nala', 'Rottweiler', 'Canine', 'Julia', 'Weight loss'),
(204, 'Zoey', 'Black Lab', 'Canine', 'Krista', 'Skin rash'),
(205, 'Mocha', 'Labrador', 'Canine', 'Amy', 'Injury'),
(206, 'Luna', 'Chihuahua', 'Canine', 'Sarah', 'Skin rash'),
(207, 'Lucy', 'Jack Russell', 'Canine', 'Ashley', 'Swollen Eye'),
(208, 'Sam', 'Golden Retriever', 'Canine', 'Greg', 'Injury'),
(209, 'Penny', 'Rottweiler', 'Canine', 'James', 'Swollen Eye'),
(210, 'Lucy', 'Dachshund', 'Canine', 'Amy', 'Routine exam'),
(211, 'Harley', 'Scottish Fold', 'Feline', 'Mark', 'Ear infection'),
(212, 'Rocky', 'Poodle', 'Canine', 'Amy', 'Skin rash'),
(213, 'Oliver', 'Tabby', 'Feline', 'Mike', 'Vaccine'),
(214, 'Daisy', 'Persian', 'Feline', 'Karen', 'Grooming'),
(215, 'Zoey', 'German Shepherd', 'Canine', 'Greg', 'Stomach upset'),
(216, 'Lucy', 'Pug', 'Canine', 'Julia', 'Skin rash'),
(217, 'Sam', 'Dachshund', 'Canine', 'Krista', 'Allergy'),
(218, 'Lily', 'Poodle', 'Canine', 'Ashley', 'Stomach upset'),
(219, 'Jake', 'Boxer', 'Canine', 'Amy', 'Stomach upset'),
(220, 'Chloe', 'Scottish Fold', 'Feline', 'Brian', 'Weight loss'),
(221, 'Jake', 'Persian', 'Feline', 'Brian', 'Weight loss'),
(222, 'Chloe', 'Rottweiler', 'Canine', 'Daniel', 'Injury'),
(223, 'Chloe', 'Boxer', 'Canine', 'Olivia', 'Injury'),
(224, 'Sophie', 'Boxer', 'Canine', 'James', 'Ear infection'),
(225, 'Sophie', 'Beagle', 'Canine', 'Julia', 'Ear infection'),
(226, 'Sadie', 'Calico', 'Feline', 'Emma', 'Check up'),
(227, 'Coco', 'Rottweiler', 'Canine', 'Sarah', 'Arthritis'),
(228, 'Jake', 'Poodle', 'Canine', 'Rachel', 'Allergy'),
(229, 'Sophie', 'Sphynx', 'Feline', 'Michelle', 'Stomach upset'),
(230, 'Rocky', 'German Shepherd', 'Canine', 'Greg', 'Vaccine'),
(231, 'Sophie', 'German Shepherd', 'Canine', 'Julia', 'Arthritis'),
(232, 'Toby', 'Ragdoll', 'Feline', 'Sarah', 'Allergy'),
(233, 'Simba', 'Labrador', 'Canine', 'Daniel', 'Vaccine'),
(234, 'Leo', 'Beagle', 'Canine', 'Laura', 'Stomach upset'),
(235, 'Coco', 'Labrador', 'Canine', 'Michelle', 'Skin rash'),
(236, 'Chloe', 'Pug', 'Canine', 'Brian', 'Dental cleaning'),
(237, 'Harley', 'Calico', 'Feline', 'Sarah', 'Check up'),
(238, 'Chloe', 'Bulldog', 'Canine', 'Greg', 'Skin rash'),
(239, 'Mittens', 'Golden Retriever', 'Canine', 'Julia', 'Check up'),
(240, 'Jake', 'Bengal', 'Feline', 'Trevor', 'Swollen Eye'),
(241, 'Mittens', 'Golden Retriever', 'Canine', 'Brian', 'Swollen Eye'),
(242, 'Zoey', 'Pug', 'Canine', 'Chris', 'Routine exam'),
(243, 'Leo', 'Bengal', 'Feline', 'Chris', 'Swollen Eye'),
(244, 'Sophie', 'Beagle', 'Canine', 'Michelle', 'Ear infection'),
(245, 'Mocha', 'Jack Russell', 'Canine', 'Sarah', 'Ear infection'),
(246, 'Mittens', 'Black Lab', 'Canine', 'Olivia', 'Arthritis'),
(247, 'Leo', 'Black Lab', 'Canine', 'Mike', 'Swollen Eye'),
(248, 'Milo', 'Husky', 'Canine', 'Barry', 'Dental check'),
(249, 'Mocha', 'Rottweiler', 'Canine', 'Mark', 'Swollen Eye');