drsBjvPX

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