Advertisement
didito33

Tutorial Database

Mar 16th, 2022
132
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE employee (
  2.   emp_id INT PRIMARY KEY,
  3.   first_name VARCHAR(40),
  4.   last_name VARCHAR(40),
  5.   birth_day DATE,
  6.   sex VARCHAR(1),
  7.   salary INT,
  8.   super_id INT,
  9.   branch_id INT
  10. );
  11.  
  12. CREATE TABLE branch (
  13.   branch_id INT PRIMARY KEY,
  14.   branch_name VARCHAR(40),
  15.   mgr_id INT,
  16.   mgr_start_date DATE,
  17.   FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
  18. );
  19.  
  20. ALTER TABLE employee
  21. ADD FOREIGN KEY(branch_id)
  22. REFERENCES branch(branch_id)
  23. ON DELETE SET NULL;
  24.  
  25. ALTER TABLE employee
  26. ADD FOREIGN KEY(super_id)
  27. REFERENCES employee(emp_id)
  28. ON DELETE SET NULL;
  29.  
  30. CREATE TABLE client (
  31.   client_id INT PRIMARY KEY,
  32.   client_name VARCHAR(40),
  33.   branch_id INT,
  34.   FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
  35. );
  36.  
  37. CREATE TABLE works_with (
  38.   emp_id INT,
  39.   client_id INT,
  40.   total_sales INT,
  41.   PRIMARY KEY(emp_id, client_id),
  42.   FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
  43.   FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
  44. );
  45.  
  46. CREATE TABLE branch_supplier (
  47.   branch_id INT,
  48.   supplier_name VARCHAR(40),
  49.   supply_type VARCHAR(40),
  50.   PRIMARY KEY(branch_id, supplier_name),
  51.   FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
  52. );
  53. -- Corporate
  54. INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL);
  55.  
  56. INSERT INTO branch VALUES(1, 'Corporate', 100, '2006-02-09');
  57.  
  58. UPDATE employee
  59. SET branch_id = 1
  60. WHERE emp_id = 100;
  61.  
  62. INSERT INTO employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', 110000, 100, 1);
  63.  
  64. -- Scranton
  65. INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);
  66.  
  67. INSERT INTO branch VALUES(2, 'Scranton', 102, '1992-04-06');
  68.  
  69. UPDATE employee
  70. SET branch_id = 2
  71. WHERE emp_id = 102;
  72.  
  73. INSERT INTO employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, 2);
  74. INSERT INTO employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, 2);
  75. INSERT INTO employee VALUES(105, 'Stanley', 'Hudson', '1958-02-19', 'M', 69000, 102, 2);
  76.  
  77. -- Stamford
  78. INSERT INTO employee VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);
  79.  
  80. INSERT INTO branch VALUES(3, 'Stamford', 106, '1998-02-13');
  81.  
  82. UPDATE employee
  83. SET branch_id = 3
  84. WHERE emp_id = 106;
  85.  
  86. INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 106, 3);
  87. INSERT INTO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, 3);
  88.  
  89.  
  90. -- BRANCH SUPPLIER
  91. INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper');
  92. INSERT INTO branch_supplier VALUES(2, 'Uni-ball', 'Writing Utensils');
  93. INSERT INTO branch_supplier VALUES(3, 'Patriot Paper', 'Paper');
  94. INSERT INTO branch_supplier VALUES(2, 'J.T. Forms & Labels', 'Custom Forms');
  95. INSERT INTO branch_supplier VALUES(3, 'Uni-ball', 'Writing Utensils');
  96. INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper');
  97. INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom Forms');
  98.  
  99. -- CLIENT
  100. INSERT INTO client VALUES(400, 'Dunmore Highschool', 2);
  101. INSERT INTO client VALUES(401, 'Lackawana Country', 2);
  102. INSERT INTO client VALUES(402, 'FedEx', 3);
  103. INSERT INTO client VALUES(403, 'John Daly Law, LLC', 3);
  104. INSERT INTO client VALUES(404, 'Scranton Whitepages', 2);
  105. INSERT INTO client VALUES(405, 'Times Newspaper', 3);
  106. INSERT INTO client VALUES(406, 'FedEx', 2);
  107.  
  108. -- WORKS_WITH
  109. INSERT INTO works_with VALUES(105, 400, 55000);
  110. INSERT INTO works_with VALUES(102, 401, 267000);
  111. INSERT INTO works_with VALUES(108, 402, 22500);
  112. INSERT INTO works_with VALUES(107, 403, 5000);
  113. INSERT INTO works_with VALUES(108, 403, 12000);
  114. INSERT INTO works_with VALUES(105, 404, 33000);
  115. INSERT INTO works_with VALUES(107, 405, 26000);
  116. INSERT INTO works_with VALUES(102, 406, 15000);
  117. INSERT INTO works_with VALUES(105, 406, 130000);
Advertisement
RAW Paste Data Copied
Advertisement