daily pastebin goal
4%
SHARE
TWEET

DBPROJECT_IS1610_Yevsyukov_and_Kogai

a guest Dec 7th, 2017 50 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DATABASE DBPROJECT;
  2. COMMENT ON DATABASE "DBPROJECT"
  3.     IS 'IS 1610
  4. Yevsyukov Ilya + Kogai Marina';
  5.  
  6. CREATE TABLE Tovar (
  7.     codtovara INT NOT NULL,
  8.     ekzemplar CHAR(30) NOT NULL,
  9.     CONSTRAINT pk_tovar PRIMARY KEY (codtovara)
  10. );
  11.    
  12.  
  13. CREATE TABLE Postavsiki (
  14.     codpostavsika INT NOT NULL,
  15.     firma CHAR(20) NOT NULL,
  16.     CONSTRAINT pk_postavsiki PRIMARY KEY (codpostavsika)
  17. );
  18.  
  19.  
  20. CREATE TABLE Manageri_postavok (
  21.     cod_meng_postav INT NOT NULL,
  22.     surname CHAR(20) NOT NULL,
  23.     yname CHAR(10) NOT NULL,
  24.     lastname CHAR(20) NOT NULL,
  25.     phone INT NOT NULL,
  26.     CONSTRAINT pk_manageripostavok PRIMARY KEY (cod_meng_postav)
  27. );
  28.  
  29.  
  30. CREATE TABLE Postavka_tovara (
  31.     codpostavki INT NOT NULL,
  32.     codtovara INT NOT NULL,
  33.     codpostavsika INT NOT NULL,
  34.     colich_post_tov INT NOT NULL,
  35.     cena_post_tov INT NOT NULL,
  36.     cod_meng_postav INT NOT NULL,
  37.     CONSTRAINT pk_postavkatovara PRIMARY KEY (codpostavki),
  38.     CONSTRAINT fk_postavkatovara1 FOREIGN KEY (codtovara) REFERENCES Tovar (codtovara),
  39.     CONSTRAINT fk_postavkatovara2 FOREIGN KEY (codpostavsika) REFERENCES Postavsiki (codpostavsika),
  40.     CONSTRAINT fk_postavkatovara3 FOREIGN KEY (cod_meng_postav) REFERENCES Manageri_postavok (cod_meng_postav)
  41. );    
  42.  
  43.  
  44. CREATE TABLE Prodavci (
  45.      codprodavca INT NOT NULL,
  46.      surname CHAR(20) NOT NULL,
  47.      yname CHAR(10) NOT NULL,
  48.      lastname CHAR(20) NOT NULL,
  49.      phone INT NOT NULL,
  50.     CONSTRAINT pk_prodavci PRIMARY KEY (codprodavca)
  51. );
  52.  
  53.  
  54. CREATE TABLE Prodaza_tovara (
  55.      cod_vibitogo_cheka INT NOT NULL,
  56.      codtovara INT NOT NULL,
  57.      CodFirmaClient INT NOT NULL,
  58.      colich_prod_tov INT NOT NULL,
  59.      cena_prod_tov INT NOT NULL,
  60.      codprodavca INT NOT NULL,
  61.     CONSTRAINT pk_prdztovara PRIMARY KEY (cod_vibitogo_cheka),
  62.     CONSTRAINT fk_prdztovara1 FOREIGN KEY (codtovara) REFERENCES Tovar (codtovara),
  63.     CONSTRAINT fk_prdztovara2 FOREIGN KEY (codprodavca) REFERENCES Prodavci (codprodavca)
  64. );
  65.  
  66.  /****INSERT INTO TABLES*****/
  67.  
  68.  
  69. INSERT INTO Tovar(codtovara,ekzemplar) VALUES
  70.     (1, 'Сыр копченый'),
  71.     (2, 'Молоко'),
  72.     (3, 'Творог'),
  73.     (4, 'Сметана'),
  74.     (5, 'Йогурт');
  75.  
  76.  
  77. INSERT INTO Postavsiki(codpostavsika,firma) VALUES
  78.     (11, 'Молоко'),
  79.     (22, 'МолПрод'),
  80.     (33, 'Снежинка'),
  81.     (44, 'Меркурий'),
  82.     (55, 'Молочный дом');
  83.  
  84.  
  85. INSERT INTO Manageri_postavok(cod_meng_postav,surname,yname,lastname,phone) VALUES
  86.     (111, 'Евсюков', 'Илья', 'Викторович', 87775653223),
  87.     (222, 'Ким', 'Александра', 'Николаевна', 87089853222),
  88.     (333, 'Когай', 'Марина', 'Александровна', 87054893175),
  89.     (444, 'Махпиров', 'Илья', 'Иванович', 87012587532),
  90.     (555, 'Коваленко', 'Илья', 'Сергеевич', 87775553565);
  91.  
  92.  
  93. INSERT INTO Postavka_tovara(codpostavki,codtovara,codpostavsika,colich_post_tov,cena_post_tov,cod_meng_postav) VALUES
  94.     (21152, 1, 22, 230, 190, 111),
  95.     (21180, 2, 11, 230, 25, 111),
  96.     (21153, 3, 44, 230, 80, 333),
  97.     (21149, 4, 55, 230, 40, 222),
  98.     (21176, 5, 33, 230, 30, 333);
  99.  
  100.  
  101. INSERT INTO Prodavci(codprodavca,surname,yname,lastname,phone) VALUES
  102.     (12, 'Анисимов', 'Максим', 'Викторович', 87775655473),
  103.     (13, 'Никитин', 'Александр', 'Николаевич', 87089853242),
  104.     (14, 'Пономарёва', 'Екатерина', 'Александровна', 87054898505),
  105.     (15, 'Самойлова', 'Оксана', 'Геннадьевна', 87012583462),
  106.     (16, 'Муравьёв', 'Илья', 'Сергеевич', 87773653565);
  107.  
  108.  
  109. INSERT INTO Prodaza_tovara(cod_vibitogo_cheka,codtovara,CodFirmaClient,colich_prod_tov,cena_prod_tov,codprodavca) VALUES
  110.     (234, 1, 177, 230, 200, 13),
  111.     (123, 2, 265, 30, 35, 14),
  112.     (565, 3, 146, 128, 90, 16),
  113.     (90, 4, 180, 160, 50, 12),
  114.     (77, 5, 119, 41, 40, 15);  
  115.  
  116. SELECT * FROM Tovar;
  117. SELECT * FROM Postavsiki;
  118. SELECT * FROM Manageri_postavok;
  119. SELECT * FROM Postavka_tovara;
  120. SELECT * FROM Prodavci;
  121. SELECT * FROM Prodaza_tovara;
RAW Paste Data
Top