API tools faq
paste
zed_com

Valik SQL

zed_com
Apr 24th, 2021 (edited)
163
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 23.73 KB | None | 0 0
raw download clone embed print report
  1. #MS SQL
  2.  
  3. CREATE TABLE departments
  4. (
  5.     id      BIGINT CHECK (id > 0) IDENTITY
  6.         PRIMARY KEY,
  7.     name    VARCHAR(255) NOT NULL,
  8.     address VARCHAR(MAX)         NOT NULL
  9. )
  10.    ;
  11.  
  12. CREATE TABLE expenses
  13. (
  14.     id                BIGINT CHECK (id > 0) IDENTITY
  15.         PRIMARY KEY,
  16.     department_id     BIGINT CHECK (department_id > 0) NOT NULL,
  17.     service           FLOAT          NOT NULL ,
  18.     lease             FLOAT          NOT NULL ,
  19.     salaries          FLOAT          NOT NULL ,
  20.     logistic_services FLOAT          NOT NULL ,
  21.     DATE              DATE            NOT NULL,
  22.     CONSTRAINT expenses_department_id_foreign
  23.         FOREIGN KEY (department_id) REFERENCES departments (id)
  24. )
  25.    ;
  26.  
  27. CREATE TABLE migrations
  28. (
  29.     id        INT CHECK (id > 0) IDENTITY
  30.         PRIMARY KEY,
  31.     migration VARCHAR(255) NOT NULL,
  32.     batch     INT          NOT NULL
  33. )
  34.    ;
  35.  
  36. CREATE TABLE workers
  37. (
  38.     id            BIGINT CHECK (id > 0) IDENTITY
  39.         PRIMARY KEY,
  40.     department_id BIGINT CHECK (department_id > 0) NOT NULL,
  41.     last_name     VARCHAR(255)    NOT NULL,
  42.     first_name    VARCHAR(255)    NOT NULL,
  43.     email         VARCHAR(255)    NOT NULL,
  44.     tel           VARCHAR(255)    NOT NULL,
  45.     bdate         DATE            NOT NULL,
  46.     accepted      DATE            NOT NULL ,
  47.     fired         DATE            NULL ,
  48.     CONSTRAINT workers_email_unique
  49.         UNIQUE (email),
  50.     CONSTRAINT workers_department_id_foreign
  51.         FOREIGN KEY (department_id) REFERENCES departments (id)
  52. )
  53.    ;
  54.  
  55. CREATE TABLE packages
  56. (
  57.     id                BIGINT CHECK (id > 0) IDENTITY
  58.         PRIMARY KEY,
  59.     name              VARCHAR(255)    NOT NULL ,
  60.     cost              FLOAT          NOT NULL ,
  61.     address           VARCHAR(MAX)            NOT NULL ,
  62.     city              VARCHAR(255)    NOT NULL ,
  63.     TYPE              INT             NOT NULL ,
  64.     department_id     BIGINT CHECK (department_id > 0) NOT NULL,
  65.     worker_id         BIGINT CHECK (worker_id > 0) NOT NULL,
  66.     time_of_creation  DATE            NOT NULL ,
  67.     time_of_departure DATE            NOT NULL ,
  68.     time_of_receipt   DATE            NOT NULL ,
  69.     CONSTRAINT packages_department_id_foreign
  70.         FOREIGN KEY (department_id) REFERENCES departments (id),
  71.     CONSTRAINT packages_worker_id_foreign
  72.         FOREIGN KEY (worker_id) REFERENCES workers (id)
  73. )
  74.    ;
  75.  
  76. #############################################################################
  77. #############################################################################
  78. INSERT INTO departments ([id], [name], [address]) VALUES
  79.     (1, 'Jamil Blick', '9953 Niko Branch Suite 795nLake Flaviemouth, DC 12086'),
  80.     (2, 'Orlo O'Reilly MD', '189 Schimmel Walk Suite 331nChristiansenfort, KY 84704'),
  81.     (3, 'Edna Feeney', '79022 Carter JunctionsnAudieberg, RI 10984-4165'),
  82.     (4, 'Hulda Kerluke', '27386 Alexandria Roads Suite 995nLake Rebecca, MI 90073-4891'),
  83.     (5, 'Dr. Otha Romaguera Jr.', '6350 Lila StreetsnSouth Anniefurt, MI 87732-8969'),
  84.     (6, 'Miss Yadira Schmitt I', '441 Flatley TurnpikenTorpmouth, NM 22688-3461'),
  85.     (7, 'Verlie Schmitt III', '343 Yasmeen Freeway Suite 945nStanfordbury, VT 43519'),
  86.     (8, 'Mikel Lemke', '8279 Waters MountainsnPort Marleneland, NE 78007'),
  87.     (9, 'Rosie Breitenberg', '6565 Clinton HarborsnSawaynbury, CT 50672'),
  88.     (10, 'Prof. Makenna Cole V', '8020 Tremayne Stream Suite 694nKarinefurt, OK 37312-7488'),
  89.     (11, 'Prof. Taylor Luettgen Sr.', '663 Howe Fort Suite 570nNorth Janessa, SC 66166'),
  90.     (12, 'Amya Wilderman', '872 Gregg AlleynBillytown, MD 10717'),
  91.     (13, 'Hardy Rogahn', '3338 Bria Square Suite 377nPort Viviannechester, LA 89147-0186'),
  92.     (14, 'Korey Greenfelder', '393 Oberbrunner SpringsnNew Audreanne, MT 66041-0031'),
  93.     (15, 'Rose Rohan', '57074 Abbott Villages Apt. 490nArielleport, CA 40438-0296'),
  94.     (16, 'Mrs. Anastasia Tromp DVM', '2509 Fritsch UNION Suite 660nSkilesbury, MN 13422'),
  95.     (17, 'Mathilde Schaden', '948 Pfeffer Radial Apt. 645nLizafort, OR 44686'),
  96.     (18, 'Joanie Anderson V', '95088 Zoila HavennTreutelfort, WY 76448-9311'),
  97.     (19, 'Miss Elna Nader', '626 Dibbert VianEleonoreside, MA 37753-6560'),
  98.     (20, 'Euna Block', '86453 Brown CenternNorth Darylside, LA 07858'),
  99.     (21, 'Brendon Kuphal II', '55452 Krajcik MountainsnHettingerview, MO 29868-5743'),
  100.     (22, 'Prof. Stefan Cronin Sr.', '29184 Audie BrooknEast Hershelfort, WV 77105'),
  101.     (23, 'Ahmad Wolf', '928 Tillman CurvenMarshallside, HI 27889-7727'),
  102.     (24, 'Alexane Koelpin', '213 Kiel FallnRueckerburgh, IL 34847'),
  103.     (25, 'Fabiola Miller', '758 Kilback StravenuenPort Alize, WI 97138'),
  104.     (26, 'Mr. Chesley Thiel III', '103 Jacynthe Dale Apt. 543nNorth Ardella, AL 04021-1227'),
  105.     (27, 'Hosea Hahn', '9367 Bosco WaynWest Princetown, CA 73020'),
  106.     (28, 'Florida Schoen', '5134 Natasha Mall Apt. 687nTyrellberg, NE 97536'),
  107.     (29, 'Peggie Grady', '54580 Carmelo Course Suite 226nEast Michaelastad, OH 42667-7317'),
  108.     (30, 'Margarete Bradtke', '9930 Hailie Falls Apt. 598nEast Jakob, MD 15407'),
  109.     (31, 'Shany Lindgren', '36223 Jessica StationnZoilabury, OK 06913'),
  110.     (32, 'Betsy Fisher', '19886 Bernhard Pine Suite 963nClaudiastad, ND 21249-5002'),
  111.     (33, 'Zetta Blanda V', '1045 Morar Spurs Apt. 727nNorth Felixhaven, TN 19757'),
  112.     (34, 'Alvis Kris V', '68024 Bell Terrace Apt. 884nEast Romaineport, IA 16172-0378'),
  113.     (35, 'Minnie Waelchi', '98956 Miguel HeightsnEast Ernest, PA 93144-6380'),
  114.     (36, 'Adolf West', '5116 Orlo Stravenue Apt. 233nBodehaven, WI 99323'),
  115.     (37, 'Payton Quigley PhD', '19498 Jeremie Ports Apt. 356nLilyanton, IL 87995-4900'),
  116.     (38, 'Mrs. Harmony Padberg', '165 Calista Summit Suite 635nNorth Victoriaport, MT 49151-7104'),
  117.     (39, 'Justyn Yost', '20710 Milo Spring Apt. 061nNorth Katheryn, TN 51083'),
  118.     (40, 'Prof. Al O'Reilly', '910 Kathlyn Ferry Suite 685nEast Arelyhaven, NE 60725-2400')dbo.,
  119.     (41, 'Bridie Little', '619 Metz Manors Suite 677nSerenaville, DC 44500-9216')dbo.,
  120.     (42, 'Brooklyn Rosenbaum', '15301 Elvera ForestnLake Maverick, ME 08964')dbo.,
  121.     (43, 'Carolanne Rippin', '670 Chesley LoafnHamillview, NH 15467-0822')dbo.,
  122.     (44, 'Barton Hickle PhD', '391 Kayleigh FlatsnEast Robynville, HI 15984-7199')dbo.,
  123.     (45, 'Barry Bode', '2541 Hettinger StreamnRamirofurt, GA 37523-6852')dbo.,
  124.     (46, 'Mr. Kennith Heaney', '618 Kihn Well Apt. 289nSouth Willa, ME 45855-7688')dbo.,
  125.     (47, 'Barton Harris DDS', '8128 Johnnie Motorway Apt. 717nEast Mckenna, SC 83550-5310')dbo.,
  126.     (48, 'Ladarius Mitchell', '132 Jaydon BurgsnEast Wilburn, MT 49531')dbo.,
  127.     (49, 'Kevon Wilkinson', '20223 Renner ShoresnPort Ahmed, NC 41694-7018')dbo.,
  128.     (50, 'Jayce Grant', '5971 Powlowski Tunnel Suite 188nHyattport, AL 95862-1707');
  129. /* SQLINES DEMO ***  `departments` ENABLE KEYS */;
  130.  
  131. -- SQLINES DEMO *** ых таблицы valiksql.expenses: ~50 rows (приблизительно)
  132. /* SQLINES DEMO ***  `expenses` DISABLE KEYS */;
  133. INSERT INTO expenses ([id], [department_id], [service], [lease], [salaries], [logistic_services], [DATE]) VALUES
  134.     (1, 42, 349.97, 196.79, 471.15, 976.33, '2021-02-27'),
  135.     (2, 16, 513.36, 510.06, 480.23, 331.76, '2020-04-27'),
  136.     (3, 34, 874.66, 157.18, 588.3, 834.41, '2020-01-20'),
  137.     (4, 35, 497.14, 388.91, 103.14, 678.4, '2021-04-05'),
  138.     (5, 34, 464.8, 327.39, 130.47, 292.27, '2020-09-09'),
  139.     (6, 35, 999.44, 213.76, 179.28, 153.98, '2019-12-22'),
  140.     (7, 1, 480.75, 253.05, 462.87, 435.89, '2019-06-07'),
  141.     (8, 30, 926.44, 787.4, 969.24, 838.76, '2021-05-01'),
  142.     (9, 4, 332.46, 277.98, 313.76, 627.29, '2020-09-21'),
  143.     (10, 41, 819.07, 937.15, 324.78, 493.08, '2021-04-12'),
  144.     (11, 28, 322.23, 338.08, 986.05, 360.56, '2020-04-25'),
  145.     (12, 36, 601.14, 309.98, 507.64, 235.19, '2019-10-15'),
  146.     (13, 32, 184.11, 488.72, 316.22, 914.36, '2021-02-13'),
  147.     (14, 26, 491.73, 366.04, 130.39, 119.8, '2020-08-13'),
  148.     (15, 33, 545.93, 130.63, 190.84, 100.79, '2020-09-10'),
  149.     (16, 1, 900.97, 431.58, 867.19, 900.74, '2020-01-19'),
  150.     (17, 24, 143.04, 555.45, 438.93, 360.93, '2019-10-06'),
  151.     (18, 34, 174.98, 664.36, 401.94, 152.91, '2021-02-06'),
  152.     (19, 23, 444.58, 132.98, 894.71, 873.8, '2021-02-05'),
  153.     (20, 7, 492.44, 263.62, 648.13, 408.09, '2020-04-17'),
  154.     (21, 9, 395.32, 847.19, 241.12, 244.6, '2020-05-29'),
  155.     (22, 17, 752.97, 373.41, 859.82, 976.37, '2020-11-25'),
  156.     (23, 7, 977.77, 479.32, 902.34, 739.32, '2020-01-16'),
  157.     (24, 14, 940.15, 872.74, 635.37, 906.34, '2020-02-05'),
  158.     (25, 35, 224.52, 304.75, 327.09, 545.91, '2020-01-13'),
  159.     (26, 24, 577, 620.82, 402.08, 931.78, '2019-08-29'),
  160.     (27, 16, 828.04, 106.02, 513.13, 593.69, '2020-07-23'),
  161.     (28, 39, 993.05, 500.64, 919.16, 683.91, '2020-10-24'),
  162.     (29, 14, 617.98, 198.22, 124.88, 905.8, '2019-11-14'),
  163.     (30, 38, 275.88, 645.22, 258.2, 332.6, '2020-12-26'),
  164.     (31, 2, 241.72, 963.26, 411.46, 234.98, '2020-08-16'),
  165.     (32, 16, 308.92, 960.2, 534.77, 684.62, '2021-02-11'),
  166.     (33, 13, 934.58, 905.83, 506.67, 321.79, '2020-09-27'),
  167.     (34, 35, 746.69, 990.15, 714.54, 485.06, '2021-03-27'),
  168.     (35, 17, 279.86, 824.59, 371.54, 383.79, '2020-07-26'),
  169.     (36, 37, 423.17, 579.28, 205.55, 424.49, '2021-04-30'),
  170.     (37, 44, 188.69, 313.01, 405.93, 941.1, '2021-01-24'),
  171.     (38, 33, 706.48, 200.16, 688.83, 277.48, '2019-07-18'),
  172.     (39, 6, 244.01, 928.81, 332.68, 270.01, '2021-03-14'),
  173.     (40, 6, 421.13, 397.7, 667.24, 614.69, '2020-07-07'),
  174.     (41, 8, 803.26, 524.97, 986.75, 882.97, '2020-11-06'),
  175.     (42, 37, 596.52, 640.27, 515.48, 630.05, '2020-02-18'),
  176.     (43, 44, 690.58, 894.07, 500.06, 111.09, '2020-04-28'),
  177.     (44, 20, 701.32, 385.94, 683.33, 469.66, '2020-10-12'),
  178.     (45, 16, 815.35, 498.53, 599.01, 743.13, '2020-09-28'),
  179.     (46, 48, 895.54, 180.11, 835.39, 941.63, '2019-08-02'),
  180.     (47, 26, 547.01, 878.31, 494.14, 640.37, '2021-02-24'),
  181.     (48, 35, 473.71, 108.1, 741.21, 302.07, '2021-01-11'),
  182.     (49, 22, 194.78, 131.75, 679.26, 890.64, '2020-11-14'),
  183.     (50, 10, 664.31, 816.02, 380.5, 214.36, '2020-11-10');
  184. /* SQLINES DEMO ***  `expenses` ENABLE KEYS */;
  185.  
  186. -- SQLINES DEMO *** ых таблицы valiksql.migrations: ~4 rows (приблизительно)
  187. /* SQLINES DEMO ***  `migrations` DISABLE KEYS */;
  188. INSERT INTO migrations ([id], [migration], [batch]) VALUES
  189.     (1, '2021_04_24_225156_create_departments_table', 1),
  190.     (2, '2021_04_24_225222_create_workers_table', 1),
  191.     (3, '2021_04_24_225645_create_packages_table', 1),
  192.     (4, '2021_04_24_232831_create_expenses_table', 1);
  193. /* SQLINES DEMO ***  `migrations` ENABLE KEYS */;
  194.  
  195. -- SQLINES DEMO *** ых таблицы valiksql.packages: ~50 rows (приблизительно)
  196. /* SQLINES DEMO ***  `packages` DISABLE KEYS */;
  197. INSERT INTO packages ([id], [name], [cost], [address], [city], [TYPE], [department_id], [worker_id], [time_of_creation], [time_of_departure], [time_of_receipt]) VALUES
  198.     (1, 'Miss Carlee Schowalter', 8309.76, '71315 Deja LandingnEast Violetville, NH 30995-9203', 'East Dena', 1, 42, 7, '2021-02-16', '2021-02-21', '2021-02-27'),
  199.     (2, 'Arlene Mante', 638.55, '224 Koch GardensnEast Sibylmouth, WY 08600-0242', 'Arnohaven', 1, 16, 1, '2020-04-13', '2020-04-23', '2020-04-27'),
  200.     (3, 'Roger Wintheiser', 1061.56, '876 Candace Harbors Apt. 918nPort Lawrenceville, KS 16283', 'Joellemouth', 2, 34, 4, '2020-01-02', '2020-01-12', '2020-01-20'),
  201.     (4, 'Prof. Jedediah Barton IV', 1088.92, '65979 Fatima Road Suite 661nLake Rubiestad, WY 82377', 'West Ryan', 1, 35, 6, '2021-03-26', '2021-03-30', '2021-04-05'),
  202.     (5, 'Royal Muller', 6551.56, '24641 Allen Turnpike Apt. 587nGenevieveport, ME 55247', 'Lake Ulises', 1, 34, 8, '2020-08-26', '2020-09-03', '2020-09-09'),
  203.     (6, 'Kassandra Upton', 5223, '13027 Marcelino Gardens Suite 296nSchinnerfort, VA 91283-2729', 'West Mosefort', 2, 35, 7, '2019-12-09', '2019-12-18', '2019-12-22'),
  204.     (7, 'Rogelio Nicolas MD', 1830.39, '4721 Laron StationnNorth Kristina, CA 88921-9476', 'Raymundoview', 2, 1, 7, '2019-06-01', '2019-06-04', '2019-06-07'),
  205.     (8, 'Rita Johns', 5066, '547 Jeanne Port Apt. 174nWest Jannie, ID 43672-8221', 'Lomaburgh', 2, 30, 9, '2021-04-23', '2021-04-27', '2021-05-01'),
  206.     (9, 'Nickolas Gorczany MD', 2733.36, '32849 Runte KnollnJaquelinshire, CO 53785', 'South Sarahchester', 1, 4, 7, '2020-09-14', '2020-09-17', '2020-09-21'),
  207.     (10, 'Aaron Lynch', 5779.94, '864 Harmony PortnWisokybury, CA 37331-6765', 'New Jamaalberg', 2, 41, 3, '2021-04-02', '2021-04-06', '2021-04-12'),
  208.     (11, 'Earlene Beer', 5709.92, '21392 Stroman FreewaynKobechester, IL 00331', 'West Colinfurt', 1, 28, 9, '2020-04-19', '2020-04-23', '2020-04-25'),
  209.     (12, 'Viola Mitchell', 801.33, '215 Kunze Crossroad Suite 695nBridgetshire, MA 95295-6514', 'Tracyshire', 1, 36, 7, '2019-09-29', '2019-10-08', '2019-10-15'),
  210.     (13, 'Jordan Gusikowski', 5329.62, '4343 Shayna Plaza Apt. 027nNew Sunny, WY 54292-6709', 'East Mozell', 2, 32, 4, '2021-02-07', '2021-02-09', '2021-02-13'),
  211.     (14, 'Maxwell Jakubowski', 5786.44, '837 Schultz Ville Suite 886nPort Mazie, OH 43585', 'West Zachery', 2, 26, 9, '2020-07-29', '2020-08-05', '2020-08-13'),
  212.     (15, 'Edwina Shields DDS', 3293.18, '22544 Leonie SpringsnDaphneeville, WY 69703', 'Hermanmouth', 2, 33, 9, '2020-08-27', '2020-08-31', '2020-09-10'),
  213.     (16, 'Adonis Goldner I', 7570.88, '962 Vicky Cove Suite 742nAngelahaven, DC 50480', 'Olsontown', 2, 1, 7, '2020-01-02', '2020-01-09', '2020-01-19'),
  214.     (17, 'Dennis Wilkinson', 9206.23, '578 Jaskolski Oval Suite 763nWest Audie, OK 97841', 'Cormierview', 1, 24, 2, '2019-09-25', '2019-10-01', '2019-10-06'),
  215.     (18, 'Kaycee Hilpert', 4735.2, '13169 Fritsch Alley Suite 536nSouth Garrett, NM 97629-8606', 'Mrazview', 2, 34, 3, '2021-01-26', '2021-02-04', '2021-02-06'),
  216.     (19, 'Mr. Ruben Pfannerstill MD', 3294.42, '40795 Carlos ForknPort Johnathanfort, AK 94537-1941', 'Santaport', 2, 23, 1, '2021-01-26', '2021-01-30', '2021-02-05'),
  217.     (20, 'Virgie Mertz DDS', 9754.3, '1804 Ernesto CovesnSouth Dolores, NJ 41650', 'Nikolausville', 2, 7, 5, '2020-03-31', '2020-04-09', '2020-04-17'),
  218.     (21, 'Deion Flatley', 4130.25, '6924 Legros Avenue Apt. 560nBlairfort, VA 10764-9825', 'North Vellaberg', 1, 9, 10, '2020-05-19', '2020-05-26', '2020-05-29'),
  219.     (22, 'Raven Boehm', 4741.92, '32290 Howe Mountains Apt. 673nDarrellhaven, ID 69710-8880', 'South Donnellfurt', 2, 17, 9, '2020-11-13', '2020-11-19', '2020-11-25'),
  220.     (23, 'Miss Kacie Grant I', 3622.16, '789 Rodriguez Crossing Apt. 999nNew Abel, DE 34039-1377', 'Quigleyton', 2, 7, 4, '2019-12-30', '2020-01-07', '2020-01-16'),
  221.     (24, 'Linnea Kunde III', 9797.71, '4598 Eldora Port Apt. 156nEast Dustin, DE 76448-6264', 'Torphyberg', 2, 14, 9, '2020-01-29', '2020-02-01', '2020-02-05'),
  222.     (25, 'Deangelo Hand', 8326.42, '716 Kaycee PikenWest Reyesport, TX 21068', 'Lake Estel', 2, 35, 8, '2019-12-30', '2020-01-07', '2020-01-13'),
  223.     (26, 'Kayley Crooks', 9524.72, '1337 Vance Track Suite 688nPort Gerardoside, KS 69464', 'Haroldland', 1, 24, 7, '2019-08-17', '2019-08-22', '2019-08-29'),
  224.     (27, 'Leilani Upton', 5045.04, '4634 Coralie Walks Apt. 698nEast Syble, NC 18722', 'Willmsfurt', 2, 16, 8, '2020-07-06', '2020-07-15', '2020-07-23'),
  225.     (28, 'Tiffany Kassulke', 6759.26, '7334 Kacey Mill Apt. 358nSouth Jade, DE 64764-2057', 'Blandachester', 1, 39, 1, '2020-10-11', '2020-10-18', '2020-10-24'),
  226.     (29, 'Abbie Hoppe', 6257.89, '6040 Wisozk Burg Apt. 271nAlexandriabury, WV 84370', 'Port Jerrold', 1, 14, 2, '2019-11-06', '2019-11-12', '2019-11-14'),
  227.     (30, 'Dr. Albert Schiller', 2926.36, '537 Jerrod Mountains Apt. 285nEast Nick, IL 43048-9359', 'West Monroeton', 1, 38, 4, '2020-12-15', '2020-12-23', '2020-12-26'),
  228.     (31, 'Bonnie Lemke', 2970.13, '285 Pacocha Ranch Suite 901nEast Sid, WY 63472-2548', 'Wizachester', 2, 2, 7, '2020-07-30', '2020-08-06', '2020-08-16'),
  229.     (32, 'Odell Kessler', 6915.67, '115 Leif MallnPort Ryley, MN 37718-3133', 'New Wymanview', 1, 16, 9, '2021-02-01', '2021-02-05', '2021-02-11'),
  230.     (33, 'Garett Sipes', 6299.57, '22485 Felipa AlleynDaneshire, OR 46776-4295', 'Monahanshire', 1, 13, 3, '2020-09-18', '2020-09-24', '2020-09-27'),
  231.     (34, 'Marshall Pollich', 5587.96, '719 Vicky Meadow Suite 295nCroninmouth, LA 96943-6546', 'Maeburgh', 1, 35, 7, '2021-03-18', '2021-03-24', '2021-03-27'),
  232.     (35, 'Marilie Bogisich', 9171.08, '58521 Jalen PrairienNorth Cornellbury, HI 67466-7607', 'Turcotteland', 2, 17, 7, '2020-07-17', '2020-07-19', '2020-07-26'),
  233.     (36, 'Sylvia Nader', 2037.79, '76231 Stroman RidgenNoeltown, WA 75587-7206', 'Lake Graycestad', 1, 37, 1, '2021-04-15', '2021-04-21', '2021-04-30'),
  234.     (37, 'Jena Trantow DDS', 6849.37, '454 Jeanie VianBradtkechester, AZ 46472-1713', 'South Marvinton', 2, 44, 2, '2021-01-07', '2021-01-14', '2021-01-24'),
  235.     (38, 'Maximus Funk', 919.27, '2893 Alysson LakenWest Eugene, TX 39201-3777', 'Amaliastad', 2, 33, 7, '2019-07-04', '2019-07-12', '2019-07-18'),
  236.     (39, 'Prof. Flossie Nicolas', 1402.98, '346 Brennan RunnCecilton, UT 11973', 'South Rodolfo', 1, 6, 2, '2021-03-02', '2021-03-10', '2021-03-14'),
  237.     (40, 'Bulah Hand', 7413.62, '910 Rogahn Corner Suite 056nKundeburgh, WI 36446-5994', 'West Carolina', 1, 6, 6, '2020-06-23', '2020-06-30', '2020-07-07'),
  238.     (41, 'Mitchel Leffler', 7032.12, '24674 Mills ManorsnMiracleview, NY 63381', 'East Westonport', 1, 8, 6, '2020-10-23', '2020-11-01', '2020-11-06'),
  239.     (42, 'Ms. Arielle Padberg I', 9914.17, '590 Jaskolski InletnMullerberg, UT 54905', 'New Augustineport', 2, 37, 10, '2020-02-03', '2020-02-12', '2020-02-18'),
  240.     (43, 'Eino Collier', 7944.1, '639 Raul Haven Apt. 528nDesireefurt, MA 68617-5867', 'East Kaliville', 2, 44, 5, '2020-04-16', '2020-04-20', '2020-04-28'),
  241.     (44, 'Miss Katlyn Monahan', 9869.71, '91859 Moen Stream Suite 477nPort Carlottaland, MS 62995-8642', 'Huelsbury', 2, 20, 8, '2020-10-04', '2020-10-08', '2020-10-12'),
  242.     (45, 'Ford Vandervort DDS', 4783.76, '754 Houston UnionnEast Molly, AR 92959', 'East Savion', 2, 16, 4, '2020-09-21', '2020-09-24', '2020-09-28'),
  243.     (46, 'Mafalda Rowe', 2915.93, '1787 Lolita Valleys Suite 631nMcDermottborough, MS 83689', 'Port Marielamouth', 2, 48, 10, '2019-07-23', '2019-07-29', '2019-08-02'),
  244.     (47, 'Melvin Lesch', 1762.2, '822 McLaughlin Shores Apt. 923nHerzogland, CO 81532', 'West Jeramieton', 1, 26, 8, '2021-02-07', '2021-02-17', '2021-02-24'),
  245.     (48, 'Treva O'Keefe II', 1334.22, '22479 Eric Stravenue Apt. 459nNew Ines, SD 07991-8093', 'Hollyhaven', 1, 35, 9, '2020-12-24', '2021-01-02', '2021-01-11'),
  246.     (49, 'Prof. Linwood Wyman', 2374.32, '877 Leora CommonnEast Madisonstad, WA 83059', 'West Khalilbury', 2, 22, 3, '2020-11-06', '2020-11-08', '2020-11-14'),
  247.     (50, 'Jennings D'Amore', 6733.17, '14020 Hoppe Turnpike Suite 669nNew Ebbafurt, CA 42452-6589', 'New Jadonhaven', 1, 10, 10, '2020-10-28', '2020-11-06', '2020-11-10');
  248. /* SQLINES DEMO ***  `packages` ENABLE KEYS */;
  249.  
  250. -- SQLINES DEMO *** ых таблицы valiksql.workers: ~10 rows (приблизительно)
  251. /* SQLINES DEMO ***  `workers` DISABLE KEYS */;
  252. INSERT INTO workers ([id], [department_id], [last_name], [first_name], [email], [tel], [bdate], [accepted], [fired]) VALUES
  253.     (1, 38, 'Stiedemann', 'Christophe', '[email protected]', '(585) 959-7087', '1998-04-20', '2017-01-20', '2021-02-21'),
  254.     (2, 25, 'Brekke', 'Alfonso', '[email protected]', '434.417.3681', '1991-05-12', '2018-02-26', '2021-02-22'),
  255.     (3, 35, 'Pollich', 'Mara', '[email protected]', '+1-208-514-9925', '1992-07-20', '2020-03-20', '2020-11-17'),
  256.     (4, 19, 'Upton', 'Raheem', '[email protected]', '+1.606.682.6460', '1993-09-08', '2018-03-23', NULL),
  257.     (5, 46, 'Vandervort', 'Yvonne', '[email protected]', '341-763-1356', '1992-05-29', '2017-08-05', NULL),
  258.     (6, 11, 'Nicolas', 'Luisa', '[email protected]', '810-864-2987', '1996-03-03', '2018-12-28', '2021-03-13'),
  259.     (7, 2, 'Gleason', 'Aliyah', '[email protected]', '(631) 306-9992', '1996-09-05', '2020-12-12', '2020-11-18'),
  260.     (8, 37, 'DuBuque', 'Heaven', '[email protected]', '+1 (540) 823-5488', '1994-10-12', '2017-09-10', '2021-02-18'),
  261.     (9, 37, 'Schaefer', 'Uriah', '[email protected]', '+1-618-272-8976', '1994-11-21', '2018-03-28', '2020-12-20'),
  262.     (10, 27, 'Russel', 'Dayna', '[email protected]', '+1-806-517-7058', '1992-06-04', '2019-10-22', '2020-06-27');
  263.  
  264.  
  265.  
  266. #############################################################################
  267. #############################################################################
  268.  
  269. #====================================================================
  270. #====================================================================
  271. #====================================================================
  272. #====================================================================
  273. #====================================================================
  274. #MYSQL
  275.  
  276. #1 Завдання
  277. SELECT d.name AS DEPART_NAME, p.* FROM packages AS p
  278. LEFT JOIN departments AS d ON p.department_id = d.id
  279. WHERE p.TYPE = 1 AND p.time_of_receipt BETWEEN "2019-01-01" AND "2020-01-01";
  280.  
  281. #2 Завдання
  282. SELECT d.name AS DEPART_NAME, p.* FROM packages AS p
  283. LEFT JOIN departments AS d ON p.department_id = d.id
  284. WHERE p.TYPE = 2 AND p.time_of_receipt BETWEEN "2019-01-01" AND "2020-01-01";
  285.  
  286. #3 Завдання
  287. SELECT SUM(service) + SUM(lease) + SUM(salaries) + SUM(logistic_services) AS DEPARTMENT_UBYTOK FROM expenses
  288. WHERE department_id = 23
  289.  
  290. #4 Завдання
  291. SELECT d.name, SUM(salaries) AS ZP FROM expenses AS e
  292. LEFT JOIN departments AS d ON d.id = e.department_id
  293. GROUP BY d.name
  294.  
  295. #5 Завдання
  296. SELECT d.name, SUM(e.service) FROM expenses AS e
  297. LEFT JOIN departments AS d ON d.id = e.department_id
  298. GROUP BY d.name
  299.  
  300. #6 Завдання
  301. SELECT d.name, SUM(p.cost)-e.logistic_services AS DOHID FROM packages AS p
  302. LEFT JOIN expenses AS e ON e.department_id = p.department_id
  303. LEFT JOIN departments AS d ON p.department_id = d.id
  304. GROUP BY d.name, e.logistic_services
  305.  
  306. #7 Завдання
  307. SELECT * FROM packages
  308. WHERE time_of_receipt BETWEEN "2020-07-22" AND "2020-07-29"
  309.  
  310. #8 Завдання
  311. SELECT d.name, SUM(p.cost)-e.service AS PROFIT FROM packages AS p
  312. LEFT JOIN expenses AS e ON e.department_id = p.department_id
  313. LEFT JOIN departments AS d ON p.department_id = d.id
  314. WHERE p.time_of_receipt BETWEEN "2020-07-22" AND "2020-07-29"
  315.         AND e.DATE BETWEEN "2020-07-22" AND "2020-07-29"
  316.         GROUP BY d.name, e.service
  317.  
  318. #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  319. #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  320. #MS SQL
  321. --1 Завдання
  322. -- SQLINES LICENSE FOR EVALUATION USE ONLY
  323. SELECT d.name AS DEPART_NAME, p.* FROM packages AS p
  324. LEFT JOIN departments AS d ON p.department_id = d.id
  325. WHERE p.TYPE = 1 AND p.time_of_receipt BETWEEN "2019-01-01" AND "2020-01-01";
  326.  
  327. --2 Завдання
  328. -- SQLINES LICENSE FOR EVALUATION USE ONLY
  329. SELECT d.name AS DEPART_NAME, p.* FROM packages AS p
  330. LEFT JOIN departments AS d ON p.department_id = d.id
  331. WHERE p.TYPE = 2 AND p.time_of_receipt BETWEEN "2019-01-01" AND "2020-01-01";
  332.  
  333. --3 Завдання
  334. -- SQLINES LICENSE FOR EVALUATION USE ONLY
  335. SELECT SUM(service) + SUM(lease) + SUM(salaries) + SUM(logistic_services) AS DEPARTMENT_UBYTOK FROM expenses
  336. WHERE department_id = 23
  337.  
  338. --4 Завдання
  339. -- SQLINES LICENSE FOR EVALUATION USE ONLY
  340. SELECT d.name, SUM(salaries) AS ZP FROM expenses AS e
  341. LEFT JOIN departments AS d ON d.id = e.department_id
  342. GROUP BY d.name
  343.  
  344. --5 Завдання
  345. -- SQLINES LICENSE FOR EVALUATION USE ONLY
  346. SELECT d.name, SUM(e.service) FROM expenses AS e
  347. LEFT JOIN departments AS d ON d.id = e.department_id
  348. GROUP BY d.name
  349.  
  350. --6 Завдання
  351. -- SQLINES LICENSE FOR EVALUATION USE ONLY
  352. SELECT d.name, SUM(p.cost)-e.logistic_services AS DOHID FROM packages AS p
  353. LEFT JOIN expenses AS e ON e.department_id = p.department_id
  354. LEFT JOIN departments AS d ON p.department_id = d.id
  355. GROUP BY d.name, e.logistic_services
  356.  
  357. --7 Завдання
  358. -- SQLINES LICENSE FOR EVALUATION USE ONLY
  359. SELECT * FROM packages
  360. WHERE time_of_receipt BETWEEN "2020-07-22" AND "2020-07-29"
  361.  
  362. --8 Завдання
  363. -- SQLINES LICENSE FOR EVALUATION USE ONLY
  364. SELECT d.name, SUM(p.cost)-e.service AS PROFIT FROM packages AS p
  365. LEFT JOIN expenses AS e ON e.department_id = p.department_id
  366. LEFT JOIN departments AS d ON p.department_id = d.id
  367. WHERE p.time_of_receipt BETWEEN "2020-07-22" AND "2020-07-29"
  368.         AND e.DATE BETWEEN "2020-07-22" AND "2020-07-29"
  369.         GROUP BY d.name, e.service
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!