API tools faq
paste
Advertisement
philip_ritchey

CSCE 331 SQL Demo

philip_ritchey
Feb 6th, 2024 (edited)
788
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 17.67 KB | Source Code | 0 0
raw download clone embed print report
  1. DROP TABLE IF EXISTS employees;
  2. DROP TABLE IF EXISTS departments;
  3. DROP TABLE IF EXISTS colleges;
  4.  
  5. CREATE TABLE colleges (
  6.     id INTEGER PRIMARY KEY,
  7.     name VARCHAR
  8. );
  9.  
  10. CREATE TABLE departments (
  11.     id INTEGER PRIMARY KEY,
  12.     name VARCHAR,
  13.     central_office VARCHAR,
  14.     college_id INTEGER REFERENCES colleges(id),
  15.     head_id INTEGER REFERENCES employees(id)
  16. );
  17.  
  18. CREATE TABLE employees (
  19.     id INTEGER PRIMARY KEY,
  20.     name VARCHAR,
  21.     hire_date DATE,
  22.     POSITION VARCHAR,
  23.     department_id INTEGER REFERENCES departments(id)
  24. );
  25.  
  26. INSERT INTO colleges (name) VALUES ("College of Agriculture and Life Sciences");
  27. INSERT INTO colleges (name) VALUES ("School of Architecture");
  28. INSERT INTO colleges (name) VALUES ("College of Arts and Sciences");
  29. INSERT INTO colleges (name) VALUES ("Mays Business School");
  30. INSERT INTO colleges (name) VALUES ("Bush School of Government and Public Service");
  31. INSERT INTO colleges (name) VALUES ("School of Dentistry");
  32. INSERT INTO colleges (name) VALUES ("School of Education and Human Development");
  33. INSERT INTO colleges (name) VALUES ("College of Engineering");
  34. INSERT INTO colleges (name) VALUES ("School of Engineering Medicine");
  35. INSERT INTO colleges (name) VALUES ("School of Law");
  36. INSERT INTO colleges (name) VALUES ("School of Medicine");
  37. INSERT INTO colleges (name) VALUES ("School of Nursing");
  38. INSERT INTO colleges (name) VALUES ("School of Performance, Visualization & Fine Arts");
  39. INSERT INTO colleges (name) VALUES ("School of Pharmacy");
  40. INSERT INTO colleges (name) VALUES ("School of Public Health");
  41. INSERT INTO colleges (name) VALUES ("School of Veterinary Medicine and Biomedical Sciences");
  42.  
  43. INSERT INTO departments (name, college_id) VALUES ("Anthropology", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  44. INSERT INTO departments (name, college_id) VALUES ("Atmospheric Sciences", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  45. INSERT INTO departments (name, college_id) VALUES ("Biology", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  46. INSERT INTO departments (name, college_id) VALUES ("Chemistry", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  47. INSERT INTO departments (name, college_id) VALUES ("Communication and Journalism", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  48. INSERT INTO departments (name, college_id) VALUES ("Economics", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  49. INSERT INTO departments (name, college_id) VALUES ("English", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  50. INSERT INTO departments (name, college_id) VALUES ("Geography", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  51. INSERT INTO departments (name, college_id) VALUES ("Geology and Geophysics", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  52. INSERT INTO departments (name, college_id) VALUES ("Global Languages and Cultures", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  53. INSERT INTO departments (name, college_id) VALUES ("History", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  54. INSERT INTO departments (name, college_id) VALUES ("Mathematics", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  55. INSERT INTO departments (name, college_id) VALUES ("Oceanography", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  56. INSERT INTO departments (name, college_id) VALUES ("Philosophy and Humanities", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  57. INSERT INTO departments (name, college_id) VALUES ("Psychological and Brain Sciences", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  58. INSERT INTO departments (name, college_id) VALUES ("Physics and Astronomy", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  59. INSERT INTO departments (name, college_id) VALUES ("Sociology", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  60. INSERT INTO departments (name, college_id) VALUES ("Statistics", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
  61.  
  62. INSERT INTO departments (name, college_id) VALUES ("Accounting", (SELECT id FROM colleges WHERE name = "Mays Business School"));
  63. INSERT INTO departments (name, college_id) VALUES ("Finance", (SELECT id FROM colleges WHERE name = "Mays Business School"));
  64. INSERT INTO departments (name, college_id) VALUES ("Information and Operations Management", (SELECT id FROM colleges WHERE name = "Mays Business School"));
  65. INSERT INTO departments (name, college_id) VALUES ("Marketing", (SELECT id FROM colleges WHERE name = "Mays Business School"));
  66.  
  67. INSERT INTO departments (name, college_id) VALUES ("Educational Administration and Human Resource Development", (SELECT id FROM colleges WHERE name = "School of Education and Human Development"));
  68. INSERT INTO departments (name, college_id) VALUES ("Educational Psychology", (SELECT id FROM colleges WHERE name = "School of Education and Human Development"));
  69. INSERT INTO departments (name, college_id) VALUES ("Kinesiology and Sport Management", (SELECT id FROM colleges WHERE name = "School of Education and Human Development"));
  70. INSERT INTO departments (name, college_id) VALUES ("Teaching, Learning, and Culture", (SELECT id FROM colleges WHERE name = "School of Education and Human Development"));
  71.  
  72. INSERT INTO departments (name, college_id) VALUES ("Aerospace Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  73. INSERT INTO departments (name, college_id) VALUES ("Biological and Agricultural Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  74. INSERT INTO departments (name, college_id) VALUES ("Biomedical Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  75. INSERT INTO departments (name, college_id) VALUES ("Chemical Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  76. INSERT INTO departments (name, college_id) VALUES ("Civil and Environmental Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  77. INSERT INTO departments (name, college_id) VALUES ("Computer Science and Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  78. INSERT INTO departments (name, college_id) VALUES ("Electrical and Computer Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  79. INSERT INTO departments (name, college_id) VALUES ("Engineering Technology and Industrial Distribution", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  80. INSERT INTO departments (name, college_id) VALUES ("Industrial and Systems Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  81. INSERT INTO departments (name, college_id) VALUES ("Materials Science and Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  82. INSERT INTO departments (name, college_id) VALUES ("Mechanical Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  83. INSERT INTO departments (name, college_id) VALUES ("Multidisciplinary Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  84. INSERT INTO departments (name, college_id) VALUES ("Nuclear Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  85. INSERT INTO departments (name, college_id) VALUES ("Ocean Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  86. INSERT INTO departments (name, college_id) VALUES ("Petroleum Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
  87.  
  88. INSERT INTO employees (name, POSITION, department_id) VALUES ("Ivett Leyva", "Department Head", (SELECT id FROM departments WHERE name = 'Aerospace Engineering'));
  89. INSERT INTO employees (name, POSITION, department_id) VALUES ("Patricia Smith", "Department Head", (SELECT id FROM departments WHERE name = 'Aerospace Engineering'));
  90. INSERT INTO employees (name, POSITION, department_id) VALUES ("Mike McShane", "Department Head", (SELECT id FROM departments WHERE name = 'Biomedical Engineering'));
  91. INSERT INTO employees (name, POSITION, department_id) VALUES ("Faisal Khan", "Interim Department Head", (SELECT id FROM departments WHERE name = 'Chemical Engineering'));
  92. INSERT INTO employees (name, POSITION, department_id) VALUES ("Zachary Grasley", "Department Head", (SELECT id FROM departments WHERE name = 'Civil and Environmental Engineering'));
  93. INSERT INTO employees (name, POSITION, department_id) VALUES ("Scott Schaefer", "Department Head", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  94.  
  95. UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Ivett Leyva') WHERE name = 'Aerospace Engineering';
  96. UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Patricia Smith') WHERE name = 'Biological and Agricultural Engineering';
  97. UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Mike McShane') WHERE name = 'Biomedical Engineering';
  98. UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Faisal Khan') WHERE name = 'Chemical Engineering';
  99. UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Zachary Grasley') WHERE name = 'Civil and Environmental Engineering';
  100. UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Scott Schaefer') WHERE name = 'Computer Science and Engineering';
  101.  
  102. INSERT INTO employees (name, POSITION, department_id) VALUES ("Calvin Beideman", "Instructional Assitant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  103. INSERT INTO employees (name, POSITION, department_id) VALUES ("Riccardo Bettati", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  104. INSERT INTO employees (name, POSITION, department_id) VALUES ("Marcus Botacin", "Visiting Assistant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  105. INSERT INTO employees (name, POSITION, department_id) VALUES ("Zoran Budimlic", "Instructional Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  106. INSERT INTO employees (name, POSITION, department_id) VALUES ("Martin Carlisle", "Professor of Practice", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  107. INSERT INTO employees (name, POSITION, department_id) VALUES ("James Caverlee", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  108. INSERT INTO employees (name, POSITION, department_id) VALUES ("Jianer Chen", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  109. INSERT INTO employees (name, POSITION, department_id) VALUES ("Yoonsuck Choe", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  110. INSERT INTO employees (name, POSITION, department_id) VALUES ("Victoria Crawford", "Assisant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  111. INSERT INTO employees (name, POSITION, department_id) VALUES ("Dilma Da Silva", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  112. INSERT INTO employees (name, POSITION, department_id) VALUES ("Tim Davis", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  113. INSERT INTO employees (name, POSITION, department_id) VALUES ("Paula deWitte", "Professor of Practice", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  114. INSERT INTO employees (name, POSITION, department_id) VALUES ("Alpaslan Duysak", "Instructional Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  115. INSERT INTO employees (name, POSITION, department_id) VALUES ("Juan Garay", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  116. INSERT INTO employees (name, POSITION, department_id) VALUES ("Guofei Gu", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  117. INSERT INTO employees (name, POSITION, department_id) VALUES ("Ricardo Gutierrez-Osuna", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  118. INSERT INTO employees (name, POSITION, department_id) VALUES ("Unal Goktas", "Instructional Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  119. INSERT INTO employees (name, POSITION, department_id) VALUES ("Drew Hamilton", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  120. INSERT INTO employees (name, POSITION, department_id) VALUES ("Tracy Hammond", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  121. INSERT INTO employees (name, POSITION, department_id) VALUES ("David Houngninou", "Instructional Assistant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  122. INSERT INTO employees (name, POSITION, department_id) VALUES ("Ruihong Huang", "Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  123. INSERT INTO employees (name, POSITION, department_id) VALUES ("Jeff Huang", "Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  124. INSERT INTO employees (name, POSITION, department_id) VALUES ("Thomas Ioerger", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  125. INSERT INTO employees (name, POSITION, department_id) VALUES ("Shuiwang Ji", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  126. INSERT INTO employees (name, POSITION, department_id) VALUES ("Anxiao Jiang", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  127. INSERT INTO employees (name, POSITION, department_id) VALUES ("Daniel Jimenez", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  128. INSERT INTO employees (name, POSITION, department_id) VALUES ("Nima Kalantari", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  129. INSERT INTO employees (name, POSITION, department_id) VALUES ("John Keyser", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  130. INSERT INTO employees (name, POSITION, department_id) VALUES ("Jeeeun Kim", "Assistant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  131. INSERT INTO employees (name, POSITION, department_id) VALUES ("Eun Jung Kim", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  132. INSERT INTO employees (name, POSITION, department_id) VALUES ("Andreas Klappenecker", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  133. INSERT INTO employees (name, POSITION, department_id) VALUES ("Shu Kong", "Assistant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  134. INSERT INTO employees (name, POSITION, department_id) VALUES ("Alan Kuhnle", "Assistant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  135. INSERT INTO employees (name, POSITION, department_id) VALUES ("Sandeep Kumar", "Associate Professor of Practice", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  136. INSERT INTO employees (name, POSITION, department_id) VALUES ("Hyunyoung Lee", "Senior Lecturer", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  137. INSERT INTO employees (name, POSITION, department_id) VALUES ("Teresa Leyk", "Instructional Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  138. INSERT INTO employees (name, POSITION, department_id) VALUES ("Robert Lightfoot", "Associate Professor of Practice", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  139. INSERT INTO employees (name, POSITION, department_id) VALUES ("Philip Ritchey", "Instructional Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  140. INSERT INTO employees (name, POSITION, department_id) VALUES ("Kathy Waskom", "Administrator I", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  141. INSERT INTO employees (name, POSITION, department_id) VALUES ("Stephanie Vilas", "Administrative Coordinator I", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  142. INSERT INTO employees (name, POSITION, department_id) VALUES ("Sarah Wall", "Business Administrator I", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  143. INSERT INTO employees (name, POSITION, department_id) VALUES ("Jennifer Runnels", "Academic Advisor IV", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  144. INSERT INTO employees (name, POSITION, department_id) VALUES ("Sarah Morgan", "Administrative Coordinator II", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  145. INSERT INTO employees (name, POSITION, department_id) VALUES ("Leia Leveridge", "Program Specialist I", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  146. INSERT INTO employees (name, POSITION, department_id) VALUES ("Dave Cote", "Senior IT Professional I", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
  147.  
  148. SELECT name, POSITION
  149. FROM employees
  150. WHERE department_id = (SELECT id FROM departments WHERE name = 'Computer Science and Engineering');
  151.  
  152. SELECT name, POSITION
  153. FROM employees
  154. WHERE department_id = (SELECT id FROM departments WHERE name = 'Computer Science and Engineering')
  155. AND POSITION LIKE "%Associate%";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!