SHARE
TWEET

Untitled

a guest May 25th, 2019 55 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE `employees` (
  2.     `ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
  3.     `FirstName` VARCHAR(30) NOT NULL,
  4.     `LastName` VARCHAR(5) NOT NULL,
  5.     `PESEL` INT(11) NOT NULL
  6. );
  7.  
  8. CREATE TABLE `regions` (
  9.     `ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
  10.     `Name` VARCHAR(30)
  11.     );
  12.    
  13. CREATE TABLE `agency` (
  14.     `ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
  15.     `Name` VARCHAR(50) NOT NULL,
  16.     `RegionId` INT(11) NOT NULL,
  17.      Provision DECIMAL(3,2) NOT NULL,
  18.      FOREIGN KEY (RegionId) REFERENCES regions(ID)
  19. );
  20.  
  21. CREATE TABLE `agents` (
  22.     `ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
  23.     `EmployeeId` INT(11) NOT NULL,
  24.     `AgencyId` INT(11) NOT NULL,
  25.     `Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  26.     FOREIGN KEY (EmployeeId) REFERENCES employees(ID),
  27.     FOREIGN KEY (AgencyId) REFERENCES agency(ID)
  28.     );
  29.    
  30. CREATE TABLE `products` (
  31.     `ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  32.     `Name` VARCHAR(255) NOT NULL,
  33.     `Price` DECIMAL NOT NULL
  34. );
  35.  
  36. CREATE  TABLE `clients` (
  37.     `ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  38.     `Name` VARCHAR(50) NOT NULL
  39. );
  40.  
  41. CREATE TABLE `transactions` (
  42.     `ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  43.     `ProductId` INT(11) NOT NULL,
  44.     `ClientId` INT(11) NOT NULL,
  45.     `AgentId` INT(11) NOT NULL,
  46.     `Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  47.     FOREIGN KEY (ProductId) REFERENCES products(ID),
  48.     FOREIGN KEY (AgentId) REFERENCES agents(ID),
  49.     FOREIGN KEY (ClientId) REFERENCES clients(ID)
  50. );
  51.  
  52.  
  53.  
  54.  
  55. INSERT INTO `regions` (`Name`) VALUES
  56.     ('pomorskie'),
  57.     ('kujawsko-pomorskie'),
  58.     ('lubelskie'),
  59.     ('lubuskie'),
  60.     ('warmińsko-mazurskie');
  61.    
  62.    
  63. INSERT INTO `agency` (`Name`, `RegionId`, `Provision`) VALUES
  64.     ('Ubezpieka', 1, 20.00),
  65.     ('Bezpieczne ubezpieczenie', 3, 15.00),
  66.     ('NoRisk', 4, 30.00),
  67.     ('Ub3zpiecz0ny', 2, 25.50),
  68.     ('Ubyzpieczyciele', 5, 27.50);
  69.    
  70. INSERT INTO `employees` (`FirstName`, `LastName`, `PESEL`) VALUES
  71.     (`Marian`, `Pazdzioch`, `53121042957`),
  72.     (`Ferdynand`, `Kiepski`, `54111542957`),
  73.     (`Halina`, `Kiepska`, `581210420679),
  74.     (`Waldemar`, `Kiepski`, `78050691334`),
  75.     (`Arnold`, `Boczek`, `65072412354`);
  76.    
  77. INSERT INTO `agents` (`EmployeeId`, `AgencyId`) VALUES
  78.     (1, 3),
  79.     (2,5),
  80.     (3,1),
  81.     (4,2),
  82.     (5,4);
  83.    
  84. INSERT INTO `products` (`Name`, `Price`) VALUES
  85.     (`Ubezpieczenie OC`, 850.00),
  86.     (`Ubezpieczenie AC`, 1850.00),
  87.     (`Ubezpieczenie OC + AC`, 2500.00),
  88.     (`Ubezpieczenie NNW`, 100.00),
  89.     (`Ubezpieczenie ASS`, 250.00);
  90.    
  91. INSERT INTO `clients` (`Name`) VALUES
  92.     ('Mariola Testowa'),
  93.     ('Genowefa Pigwa'),
  94.     ('Teodor Motor'),
  95.     ('Mariusz Kajetanowicz'),
  96.     ('Oliwia Oliwska');
  97.    
  98. INSERT INTO `transactions` (`ProductId`, `ClientId`, `AgentId`) VALUES
  99.     (1, 3, 5),
  100.     (2, 1, 2),
  101.     (3, 2, 1),
  102.     (3, 1, 3),
  103.     (4, 4, 4),
  104.     (5, 4, 2),
  105.     (5, 2, 5),
  106.     (2, 3, 4);
  107.    
  108.    
  109.    
  110.    
  111. DROP TABLE IF EXISTS `regions`;
  112. DROP TABLE IF EXISTS `agency`;
  113. DROP TABLE IF EXISTS `employees`;
  114. DROP TABLE IF EXISTS `agents`;
  115. DROP TABLE IF EXISTS `clients`;
  116. DROP TABLE IF EXISTS `products`;
  117. DROP TABLE IF EXISTS `transactions`;
  118.  
  119.  
  120. SELECT * FROM `transactions` WHERE `Client` != 2;
  121. SELECT * FROM `products` WHERE `Price` > 800.00;
  122.  
  123. SELECT count(*) FROM `transactions` GROUP BY `AgentId`;
  124. SELECT SUM(*) FROM `transactions`;
  125.  
  126. SELECT * FROM `employees` ORDER BY `LastName` ASC;
  127. SELECT * FROM `transactions` ORDER BY `ID` DESC;
  128.  
  129. SELECT `transactions`.`ID` as idKlienta, `transactions`.`AgentId` as idAgenta, `products`.`Prize` as cena, `agency`.`Provision` as prowizja
  130.     FROM `transactions`
  131.     LEFT JOIN `products` ON `products`.`ID` = `transactions`.`ProductId`
  132.     LEFT JOIN `agents` ON `agents`.`ID` = `transactions`.`AgentId`
  133.     LEFT JOIN `agency` ON `agency`.`ID` = `agents`.`AgencyId`;
  134.  
  135. CREATE VIEW `widoczek` AS SELECT `transactions`.`ID` as idKlienta, `transactions`.`AgentId` as idAgenta, `products`.`Prize` as cena, `agency`.`Provision` as prowizja
  136.     FROM `transactions`
  137.     LEFT JOIN `products` ON `products`.`ID` = `transactions`.`ProductId`
  138.     LEFT JOIN `agents` ON `agents`.`ID` = `transactions`.`AgentId`
  139.     LEFT JOIN `agency` ON `agency`.`ID` = `agents`.`AgencyId`;
RAW Paste Data
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
 
Top