Changes between Version 4 and Version 5 of dmlScript-with-help-of-AI.sql


Ignore:
Timestamp:
09/20/26 07:26:08 (9 days ago)
Author:
235018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • dmlScript-with-help-of-AI.sql

    v4 v5  
    1111(8, 'Crochet', 3),
    1212(9, 'Beadwork', 3);
    13 
    14 
    15 -- STORE
    16 INSERT INTO store (store_id, name, date_of_founding, physical_address, store_email, rating) VALUES
    17 ('001', 'WoodCraft Skopje', '2015-03-12', 'st.Ilindenska 45, Skopje 1000, Macedonia', 'contact@woodcraft.mk', 4.6),
    18 ('002', 'Fox Crochets', '2023-06-01', 'st.Kej Makedonija 12, Ohrid 6000, Macedonia', 'ohrid@foxcrochets.mk', 4.8),
    19 ('003', 'Artisan Collective', '2020-01-15', 'st.Goce Delcev 78, Bitola 7000, Macedonia', 'info@artisancollective.mk', 4.5);
    20 
    2113
    2214-- PRODUCT
    … …  
    5749('00200003', 'White');
    5850
     51-- STORE
     52INSERT INTO store (store_ID, name, date_of_founding, physical_address, store_email, rating) VALUES
     53('001', 'WoodCraft Skopje', '2015-03-12', 'st.Ilindenska 45, Skopje 1000, Macedonia', 'contact@woodcraft.mk', 4.6),
     54('002', 'Fox Crochets', '2023-06-01', 'st.Kej Makedonija 12, Ohrid 6000, Macedonia', 'ohrid@foxcrochets.mk', 4.8),
     55('003', 'Artisan Collective', '2020-01-15', 'st.Goce Delcev 78, Bitola 7000, Macedonia', 'info@artisancollective.mk', 4.5);
    5956
    6057-- PERSONAL
    … …  
    6764
    6865-- PERMISSIONS
    69 INSERT INTO permissions (personal_ID, type, authorisation) VALUES
     66INSERT INTO permissions (personal_id, type, authorisation) VALUES
    7067('0010001', 'BOSS', 'admin'),
    7168('0010002', 'EMPLOYEE', 'M.Petrovski'),
    … …  
    7572
    7673-- BOSS
    77 INSERT INTO boss (boss_ID, signature) VALUES
    78 ('0010001', 'M.Petrovski'),
    79 ('0020001', 'S.Vaneva'),
    80 ('0030001', 'D.Ristevski');
     74INSERT INTO boss (boss_ID) VALUES
     75('0010001'),
     76('0020001'),
     77('0030001');
    8178
    8279-- EMPLOYEES
    … …  
    8683
    8784-- CLIENT
    88 INSERT INTO client (client_id, first_name, last_name, email, password) VALUES
     85INSERT INTO client (client_ID, first_name, last_name, email, password) VALUES
    8986(1001, 'Ivan', 'Stojanov', 'ivan@gmail.com', 'hkh689gvgsh%hd'),
    9087(1002, 'Marija', 'Kostova', 'marija@yahoo.com', 'PJdbbh334$djk-hs'),
    … …  
    104101-- ORDER
    105102INSERT INTO "order" (order_num, client_ID, status, last_date_mod, payment_method, discount, delivery_address) VALUES
    106 ('002202500001', 1001, 'placed order', '2025-12-01 10:15:00', 'credit card ****6750', 0.00, 'st.Partizanska 10, Skopje 1000'),
    107 ('002202500002', 1001, 'being processed', '2025-12-10 18:00:00', 'PayPal account user123', 0.00, 'st.Partizanska 10, Skopje 1000'),
    108 ('001202500001', 1002, 'delivered', '2025-12-02 14:30:00', 'cash', 4.00, 'st.Turisticka 5, Bitola 1700'),
    109 ('003202500001', 1003, 'shipping', '2025-12-05 09:45:00', 'credit card ****1234', 10.00, 'Campus Dormitory "Goce Delcev", Room 305, Skopje 1000'),
    110 ('002202500003', 1004, 'canceled', '2025-12-03 16:20:00', 'credit card ****9876', 0.00, 'st.Bul. Kuzman Josifovski Pitu 15, Skopje 1000'),
    111 ('001202500002', 1006, 'delivered', '2025-12-08 11:30:00', 'bank transfer', 5.50, 'st.Makedonska Brigada 22, Ohrid 1600');
     103('00202500001', 1001, 'placed order', '2025-12-01 10:15:00', 'credit card ****6750', 0.00, 'st.Partizanska 10, Skopje 1000'),
     104('00202500002', 1001, 'being processed', '2025-12-10 18:00:00', 'PayPal account user123', 0.00, 'st.Partizanska 10, Skopje 1000'),
     105('00102500001', 1002, 'delivered', '2025-12-02 14:30:00', 'cash', 4.00, 'st.Turisticka 5, Bitola 1700'),
     106('00302500001', 1003, 'shipping', '2025-12-05 09:45:00', 'credit card ****1234', 10.00, 'Campus Dormitory "Goce Delcev", Room 305, Skopje 1000'),
     107('00202500003', 1004, 'canceled', '2025-12-03 16:20:00', 'credit card ****9876', 0.00, 'st.Bul. Kuzman Josifovski Pitu 15, Skopje 1000'),
     108('00102500002', 1006, 'delivered', '2025-12-08 11:30:00', 'bank transfer', 5.50, 'st.Makedonska Brigada 22, Ohrid 1600');
     109
     110-- REVIEW
     111INSERT INTO review (order_num, comment, rating, last_mod_date) VALUES
     112('00102500001', 'Great quality, slightly late delivery', 4.0, '2025-12-05 18:00:32'),
     113('00102500002', 'Beautiful craftsmanship, exactly as pictured', 5.0, '2025-12-10 10:38:04'),
     114('00302500001', '', 4.5, '2025-12-12 14:15:56');
     115
     116-- REFUND
     117INSERT INTO refund (order_num, amount, reason, status) VALUES
     118('00202500003', 199.00, 'Customer changed mind before shipping', 'processed'),
     119('00102500001', 50.00, 'Partial refund for late delivery', 'approved'),
     120('00302500001', 89.99, 'One item damaged during shipping', 'pending'),
     121('00102500002', 75.00, 'Price adjustment after promotion', 'declined');
    112122
    113123-- REPORT
    114124INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES
    115 ('2024-11-30 23:59:59', '001', 125000.00, 'Increasing', 'Stable growth', 'M.Petrovski'),
    116 ('2024-11-30 23:59:59', '002', 98000.00, 'Stable', 'Moderate growth', 'S.Vaneva'),
    117 ('2024-11-30 23:59:59', '003', 75000.00, 'Growing', 'Rapid growth', 'D.Ristevski'),
    118 ('2024-12-31 23:59:59', '001', 135000.00, 'Increasing', 'Good growth', 'M.Petrovski'),
    119 ('2024-12-31 23:59:59', '002', 105000.00, 'Stable', 'Moderate growth', 'S.Vaneva');
     125('2025-11-30 23:59:59', '001', 525000.00, 'Increasing', 'Stable growth', 'M.Petrovski'),
     126('2025-11-30 23:59:59', '002', 987000.00, 'Stable', 'Moderate growth', 'S.Vaneva'),
     127('2025-11-30 23:59:59', '003', 715000.00, 'Growing', 'Rapid growth', 'D.Ristevski'),
     128('2025-12-31 23:59:59', '001', 1305000.00, 'Increasing', 'Good growth', 'M.Petrovski'),
     129('2025-12-31 23:59:59', '002', 1023000.00, 'Stable', 'Moderate growth', 'S.Vaneva');
    120130
    121131-- MONTHLY_PROFIT
    122132INSERT INTO monthly_profit (report_date, store_ID, month_and_year profit) VALUES
    123 ('2024-11-30 23:59:59', '001', 'November 2024', 12500.00),
    124 ('2024-11-30 23:59:59', '002', 'November 2024', 8000.00),
    125 ('2024-11-30 23:59:59', '003', 'November 2024', 6500.00),
    126 ('2024-12-31 23:59:59', '001', 'December 2024', 14500.00),
    127 ('2024-12-31 23:59:59', '002', 'December 2024', 9000.00);
     133('2025-11-30 23:59:59', '001', 'November 2025', 12500.00),             
     134('2025-11-30 23:59:59', '002', 'November 2025', 8000.00),
     135('2025-11-30 23:59:59', '003', 'November 2025', 6500.00),
     136('2025-12-31 23:59:59', '001', 'December 2025', 14500.00),
     137('2025-12-31 23:59:59', '002', 'December 2025', 9000.00);
     138
     139-- EXCHANGES_DATA
     140INSERT INTO exchanges_data (report_date, store_ID, monthly_profit, date, sales, damages) VALUES       
     141('2025-11-30 23:59:59', '001', 38750.00, '2025-11-30 20:00:00', 52, 750.00),
     142('2025-11-30 23:59:59', '002', 26150.00, '2025-11-30 20:00:00', 40, 0.00),
     143('2025-11-30 23:59:59', '003', 19500.00, '2025-11-30 20:00:00', 35, 250.00),
     144('2025-12-31 23:59:59', '001', 41250.00, '2025-12-31 20:00:00', 58, 1200.00),
     145('2025-12-31 23:59:59', '002', 28500.00, '2025-12-31 20:00:00', 45, 500.00);
     146
    128147
    129148-- REQUEST
    … …  
    155174('00112202500201', '001');
    156175
    157 -- REVIEW
    158 INSERT INTO review (order_num, comment, rating, last_mod_date) VALUES
    159 ('001202500001', 'Great quality, slightly late delivery', 4.0, '2024-12-05 18:00:00'),
    160 ('001202500002', 'Beautiful craftsmanship, exactly as pictured', 5.0, '2024-12-10 10:30:00'),
    161 ('003202500001', '', 4.5, '2024-12-12 14:15:00');
    162 
    163176-- CHANGE
    164177INSERT INTO "change" (date_and_time, product_code, changes) VALUES
    165 ('2024-11-10 09:00:00', '00100001', 'FROM aprox_production_time=14 TO aprox_production_time=10'),
    166 ('2024-11-12 15:30:00', '00200001', 'Added new color options: Purple, Pink'),
    167 ('2024-12-01 11:00:00', '00100002', 'Price increased from 1100 to 1200 due to material costs'),
    168 ('2024-12-05 14:20:00', '00200002', 'Production time reduced from 16 to 14 days');
     178('2025-11-10 09:00:00', '00100001', 'FROM aprox_production_time=14 TO aprox_production_time=10'),
     179('2025-11-12 15:30:00', '00200001', 'Added new color options: Purple, Pink'),
     180('2025-12-01 11:00:00', '00100002', 'Price increased from 1100 to 1200 due to material costs'),
     181('2025-12-05 14:20:00', '00200002', 'Production time reduced from 16 to 14 days');
    169182
    170183-- MAKES_CHANGE
    171184INSERT INTO makes_change (personal_ID, change_date_time, product_code) VALUES
    172 ('0010002', '2024-11-10 09:00:00', '00100001'),
    173 ('0020001', '2024-11-12 15:30:00', '00200001'),
    174 ('0010001', '2024-12-01 11:00:00', '00100002'),
    175 ('0020001', '2024-12-05 14:20:00', '00200002');
     185('0010002', '2025-11-10 09:00:00', '00100001'),
     186('0020001', '2025-11-12 15:30:00', '00200001'),
     187('0010001', '2025-12-01 11:00:00', '00100002'),
     188('0020001', '2025-12-05 14:20:00', '00200002');
    176189
    177190-- WORKS_IN_STORE
    … …  
    185198-- WORKED
    186199INSERT INTO worked (personal_ID, report_date, store_ID, wage, pay_method, total_hours, week) VALUES
    187 ('0010001', '2024-11-30 23:59:59', '001', 75, 'hourly', 48, '2024-11-24 - 2024-11-30'),
    188 ('0010002', '2024-11-30 23:59:59', '001', 75, 'hourly', 38, '2024-11-24 - 2024-11-30'),
    189 ('0020001', '2024-11-30 23:59:59', '002', 10000, 'monthly', 52, '2024-11-24 - 2024-11-30'),
    190 ('0030001', '2024-11-30 23:59:59', '003', 65, 'hourly', 42, '2024-11-24 - 2024-11-30'),
    191 ('0020002', '2024-11-30 23:59:59', '002', 60, 'hourly', 40, '2024-11-24 - 2024-11-30');
     200('0010001', '2025-11-30 23:59:59', '001', 75, 'hourly', 48, '2025-11-24 - 2025-11-30'),
     201('0010002', '2025-11-30 23:59:59', '001', 75, 'hourly', 38, '2025-11-24 - 2025-11-30'),
     202('0020001', '2025-11-30 23:59:59', '002', 10000, 'monthly', 52, '2025-11-24 - 2025-11-30'),
     203('0030001', '2025-11-30 23:59:59', '003', 65, 'hourly', 42, '2025-11-24 - 2025-11-30'),
     204('0020002', '2025-11-30 23:59:59', '002', 60, 'hourly', 40, '2025-11-24 - 2025-11-30');
    192205
    193206-- SELLS
    … …  
    197210('00200001', '002', 0.0),
    198211('00200002', '002', 0.5),
    199 ('00200002', '003', 0.3),
    200 ('00200003', '003', 0.0),
    201 ('00200001', '003', 0.2);
     212('00300002', '003', 0.3),
     213('00300003', '003', 0.0),
     214('00300001', '003', 0.2);
    202215
    203216-- INCLUDES
    … …  
    206219('002202500001', '00200002'),
    207220('002202500002', '00200001'),
    208 ('003202500001', '00200002'),
    209 ('003202500001', '00200003'),
     221('003202500001', '00300002'),
     222('003202500001', '00300003'),
    210223('001202500002', '00100001'),
    211 ('001202500002', '00200001');
     224('001202500002', '00100001');
    212225
    213226-- APPROVES
    214227INSERT INTO approves (boss_ID, report_date, store_ID, owner_signature) VALUES
    215 ('0010001', '2024-11-30 23:59:59', '001', 'M.Petrovski'),
    216 ('0020001', '2024-11-30 23:59:59', '002', 'S.Vaneva'),
    217 ('0010001', '2024-12-31 23:59:59', '001', 'M.Petrovski'),
    218 ('0020001', '2024-12-31 23:59:59', '002', 'S.Vaneva');
    219 
    220 -- EXCHANGES_DATA
    221 INSERT INTO exchanges_data (report_date, store_ID, monthly_profit, date, sales, damages) VALUES       
    222 ('2024-11-30 23:59:59', '001', 38750.00, '2024-11-30 20:00:00', 52, 750.00),
    223 ('2024-11-30 23:59:59', '002', 26150.00, '2024-11-30 20:00:00', 40, 0.00),
    224 ('2024-11-30 23:59:59', '003', 19500.00, '2024-11-30 20:00:00', 35, 250.00),
    225 ('2024-12-31 23:59:59', '001', 41250.00, '2025-12-31 20:00:00', 58, 1200.00),
    226 ('2024-12-31 23:59:59', '002', 28500.00, '2025-12-31 20:00:00', 45, 500.00);
    227 
    228 -- REFUND
    229 INSERT INTO refund (order_num, amount, reason, status) VALUES
    230 ('002202500003', 199.00, 'Customer changed mind before shipping', 'processed'),
    231 ('001202500001', 50.00, 'Partial refund for late delivery', 'approved'),
    232 ('003202500001', 89.99, 'One item damaged during shipping', 'pending'),
    233 ('001202500002', 75.00, 'Price adjustment after promotion', 'declined');
     228('0010001', '2025-11-30 23:59:59', '001', 'M.Petrovski'),
     229('0020001', '2025-11-30 23:59:59', '002', 'S.Vaneva'),
     230('0010001', '2025-12-31 23:59:59', '001', 'M.Petrovski'),
     231('0020001', '2025-12-31 23:59:59', '002', 'S.Vaneva');
     232
     233
    234234
    235235