Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE IF EXISTS employees;
- DROP TABLE IF EXISTS departments;
- DROP TABLE IF EXISTS colleges;
- CREATE TABLE colleges (
- id INTEGER PRIMARY KEY,
- name VARCHAR
- );
- CREATE TABLE departments (
- id INTEGER PRIMARY KEY,
- name VARCHAR,
- central_office VARCHAR,
- college_id INTEGER REFERENCES colleges(id),
- head_id INTEGER REFERENCES employees(id)
- );
- CREATE TABLE employees (
- id INTEGER PRIMARY KEY,
- name VARCHAR,
- hire_date DATE,
- POSITION VARCHAR,
- department_id INTEGER REFERENCES departments(id)
- );
- INSERT INTO colleges (name) VALUES ("College of Agriculture and Life Sciences");
- INSERT INTO colleges (name) VALUES ("School of Architecture");
- INSERT INTO colleges (name) VALUES ("College of Arts and Sciences");
- INSERT INTO colleges (name) VALUES ("Mays Business School");
- INSERT INTO colleges (name) VALUES ("Bush School of Government and Public Service");
- INSERT INTO colleges (name) VALUES ("School of Dentistry");
- INSERT INTO colleges (name) VALUES ("School of Education and Human Development");
- INSERT INTO colleges (name) VALUES ("College of Engineering");
- INSERT INTO colleges (name) VALUES ("School of Engineering Medicine");
- INSERT INTO colleges (name) VALUES ("School of Law");
- INSERT INTO colleges (name) VALUES ("School of Medicine");
- INSERT INTO colleges (name) VALUES ("School of Nursing");
- INSERT INTO colleges (name) VALUES ("School of Performance, Visualization & Fine Arts");
- INSERT INTO colleges (name) VALUES ("School of Pharmacy");
- INSERT INTO colleges (name) VALUES ("School of Public Health");
- INSERT INTO colleges (name) VALUES ("School of Veterinary Medicine and Biomedical Sciences");
- INSERT INTO departments (name, college_id) VALUES ("Anthropology", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Atmospheric Sciences", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Biology", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Chemistry", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Communication and Journalism", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Economics", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("English", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Geography", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Geology and Geophysics", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Global Languages and Cultures", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("History", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Mathematics", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Oceanography", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Philosophy and Humanities", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Psychological and Brain Sciences", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Physics and Astronomy", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Sociology", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Statistics", (SELECT id FROM colleges WHERE name = "College of Arts and Sciences"));
- INSERT INTO departments (name, college_id) VALUES ("Accounting", (SELECT id FROM colleges WHERE name = "Mays Business School"));
- INSERT INTO departments (name, college_id) VALUES ("Finance", (SELECT id FROM colleges WHERE name = "Mays Business School"));
- INSERT INTO departments (name, college_id) VALUES ("Information and Operations Management", (SELECT id FROM colleges WHERE name = "Mays Business School"));
- INSERT INTO departments (name, college_id) VALUES ("Marketing", (SELECT id FROM colleges WHERE name = "Mays Business School"));
- 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"));
- INSERT INTO departments (name, college_id) VALUES ("Educational Psychology", (SELECT id FROM colleges WHERE name = "School of Education and Human Development"));
- INSERT INTO departments (name, college_id) VALUES ("Kinesiology and Sport Management", (SELECT id FROM colleges WHERE name = "School of Education and Human Development"));
- INSERT INTO departments (name, college_id) VALUES ("Teaching, Learning, and Culture", (SELECT id FROM colleges WHERE name = "School of Education and Human Development"));
- INSERT INTO departments (name, college_id) VALUES ("Aerospace Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Biological and Agricultural Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Biomedical Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Chemical Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Civil and Environmental Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Computer Science and Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Electrical and Computer Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Engineering Technology and Industrial Distribution", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Industrial and Systems Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Materials Science and Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Mechanical Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Multidisciplinary Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Nuclear Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Ocean Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO departments (name, college_id) VALUES ("Petroleum Engineering", (SELECT id FROM colleges WHERE name = "College of Engineering"));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Ivett Leyva", "Department Head", (SELECT id FROM departments WHERE name = 'Aerospace Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Patricia Smith", "Department Head", (SELECT id FROM departments WHERE name = 'Aerospace Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Mike McShane", "Department Head", (SELECT id FROM departments WHERE name = 'Biomedical Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Faisal Khan", "Interim Department Head", (SELECT id FROM departments WHERE name = 'Chemical Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Zachary Grasley", "Department Head", (SELECT id FROM departments WHERE name = 'Civil and Environmental Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Scott Schaefer", "Department Head", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Ivett Leyva') WHERE name = 'Aerospace Engineering';
- UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Patricia Smith') WHERE name = 'Biological and Agricultural Engineering';
- UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Mike McShane') WHERE name = 'Biomedical Engineering';
- UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Faisal Khan') WHERE name = 'Chemical Engineering';
- UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Zachary Grasley') WHERE name = 'Civil and Environmental Engineering';
- UPDATE departments SET head_id = (SELECT id FROM employees WHERE name = 'Scott Schaefer') WHERE name = 'Computer Science and Engineering';
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Calvin Beideman", "Instructional Assitant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Riccardo Bettati", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Marcus Botacin", "Visiting Assistant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Zoran Budimlic", "Instructional Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Martin Carlisle", "Professor of Practice", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("James Caverlee", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Jianer Chen", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Yoonsuck Choe", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Victoria Crawford", "Assisant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Dilma Da Silva", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Tim Davis", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Paula deWitte", "Professor of Practice", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Alpaslan Duysak", "Instructional Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Juan Garay", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Guofei Gu", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Ricardo Gutierrez-Osuna", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Unal Goktas", "Instructional Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Drew Hamilton", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Tracy Hammond", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("David Houngninou", "Instructional Assistant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Ruihong Huang", "Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Jeff Huang", "Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Thomas Ioerger", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Shuiwang Ji", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Anxiao Jiang", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Daniel Jimenez", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Nima Kalantari", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("John Keyser", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Jeeeun Kim", "Assistant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Eun Jung Kim", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Andreas Klappenecker", "Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Shu Kong", "Assistant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Alan Kuhnle", "Assistant Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Sandeep Kumar", "Associate Professor of Practice", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Hyunyoung Lee", "Senior Lecturer", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Teresa Leyk", "Instructional Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Robert Lightfoot", "Associate Professor of Practice", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Philip Ritchey", "Instructional Associate Professor", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Kathy Waskom", "Administrator I", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Stephanie Vilas", "Administrative Coordinator I", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Sarah Wall", "Business Administrator I", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Jennifer Runnels", "Academic Advisor IV", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Sarah Morgan", "Administrative Coordinator II", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Leia Leveridge", "Program Specialist I", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- INSERT INTO employees (name, POSITION, department_id) VALUES ("Dave Cote", "Senior IT Professional I", (SELECT id FROM departments WHERE name = 'Computer Science and Engineering'));
- SELECT name, POSITION
- FROM employees
- WHERE department_id = (SELECT id FROM departments WHERE name = 'Computer Science and Engineering');
- SELECT name, POSITION
- FROM employees
- WHERE department_id = (SELECT id FROM departments WHERE name = 'Computer Science and Engineering')
- AND POSITION LIKE "%Associate%";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement