Using the University Database: 1) Rank order all students by total credit (DESC) 2) Create a view tot_credits_dept (columns: year, dept_name, num_credits) with the total number of cred
Using the University Database:
1)
Rank order all students by total credit (DESC)
2)
Create a view tot_credits_dept
(columns: year, dept_name,
num_credits)
with the total
number of credits taken
by all students in each year for courses offered by each department
3)
Compute average
number of credits over three
preceding years
per department using the view you created
4)
Compute maximum
number of credits over for the window
of 2 years before and 2 years after the
current year per department
5)
Using the view you just created, get the average
total credits by each department over all prior years.
#drop table prereq; #drop table time_slot; #drop table advisor; #drop table takes; #drop table student; #drop table teaches; #drop table section; #drop table instructor; #drop table course; #drop table department; #drop table classroom; create table classroom (building varchar(15), room_number varchar(7), capacity numeric(4,0), primary key (building, room_number) ); create table department (dept_name varchar(20), building varchar(15), budget numeric(12,2) check (budget > 0), primary key (dept_name) ); create table course (course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0) check (credits > 0), primary key (course_id), foreign key (dept_name) references department (dept_name) on delete set null ); create table instructor (ID varchar(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2) check (salary > 29000), primary key (ID), foreign key (dept_name) references department (dept_name) on delete set null ); create table section (course_id varchar(8), sec_id varchar(8), semester varchar(6) check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), year numeric(4,0) check (year > 1701 and year < 2100), building varchar(15), room_number varchar(7), time_slot_id varchar(4), primary key (course_id, sec_id, semester, year), foreign key (course_id) references course (course_id) on delete cascade, foreign key (building, room_number) references classroom (building, room_number) on delete set null ); create table teaches (ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), primary key (ID, course_id, sec_id, semester, year), foreign key (course_id, sec_id, semester, year) references section (course_id, sec_id, semester, year) on delete cascade, foreign key (ID) references instructor (ID) on delete cascade ); create table student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0) check (tot_cred >= 0), primary key (ID), foreign key (dept_name) references department (dept_name) on delete set null ); create table takes (ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID, course_id, sec_id, semester, year), foreign key (course_id, sec_id, semester, year) references section (course_id, sec_id, semester, year) on delete cascade, foreign key (ID) references student (ID) on delete cascade ); create table advisor (s_ID varchar(5), i_ID varchar(5), primary key (s_ID), foreign key (i_ID) references instructor (ID) on delete set null, foreign key (s_ID) references student (ID) on delete cascade ); create table time_slot (time_slot_id varchar(4), day varchar(1), start_hr numeric(2) check (start_hr >= 0 and start_hr < 24), start_min numeric(2) check (start_min >= 0 and start_min < 60), end_hr numeric(2) check (end_hr >= 0 and end_hr < 24), end_min numeric(2) check (end_min >= 0 and end_min < 60), primary key (time_slot_id, day, start_hr, start_min) ); create table prereq (course_id varchar(8), prereq_id varchar(8), primary key (course_id, prereq_id), foreign key (course_id) references course (course_id) on delete cascade, foreign key (prereq_id) references course (course_id) on delete cascade );
,
| SET SQL_SAFE_UPDATES = 0; | |||||
| delete from prereq; | |||||
| delete from time_slot; | |||||
| delete from advisor; | |||||
| delete from takes; | |||||
| delete from student; | |||||
| delete from teaches; | |||||
| delete from section; | |||||
| delete from instructor; | |||||
| delete from course; | |||||
| delete from department; | |||||
| delete from classroom; | |||||
| insert into time_slot values ( 'A' | 'M' | 8 | 0 | 8 | 50); |
| insert into time_slot values ( 'A' | 'W' | 8 | 0 | 8 | 50); |
| insert into time_slot values ( 'A' | 'F' | 8 | 0 | 8 | 50); |
| insert into time_slot values ( 'B' | 'M' | 9 | 0 | 9 | 50); |
| insert into time_slot values ( 'B' | 'W' | 9 | 0 | 9 | 50); |
| insert into time_slot values ( 'B' | 'F' | 9 | 0 | 9 | 50); |
| insert into time_slot values ( 'C' | 'M' | 11 | 0 | 11 | 50); |
| insert into time_slot values ( 'C' | 'W' | 11 | 0 | 11 | 50); |
| insert into time_slot values ( 'C' | 'F' | 11 | 0 | 11 | 50); |
| insert into time_slot values ( 'D' | 'M' | 13 | 0 | 13 | 50); |
| insert into time_slot values ( 'D' | 'W' | 13 | 0 | 13 | 50); |
| insert into time_slot values ( 'D' | 'F' | 13 | 0 | 13 | 50); |
| insert into time_slot values ( 'E' | 'T' | 10 | 30 | 11 | 45); |
| insert into time_slot values ( 'E' | 'R' | 10 | 30 | 11 | 45); |
| insert into time_slot values ( 'F' | 'T' | 14 | 30 | 15 | 45); |
| insert into time_slot values ( 'F' | 'R' | 14 | 30 | 15 | 45); |
| insert into time_slot values ( 'G' | 'M' | 16 | 0 | 16 | 50); |
| insert into time_slot values ( 'G' | 'W' | 16 | 0 | 16 | 50); |
| insert into time_slot values ( 'G' | 'F' | 16 | 0 | 16 | 50); |
| insert into time_slot values ( 'H' | 'W' | 10 | 0 | 12 | 30); |
| insert into classroom values('Lamberton' | 134 | 10); | |||
| insert into classroom values('Chandler' | 375 | 10); | |||
| insert into classroom values('Fairchild' | 145 | 27); | |||
| insert into classroom values('Nassau' | 45 | 92); | |||
| insert into classroom values('Grace' | 40 | 34); | |||
| insert into classroom values('Whitman' | 134 | 120); | |||
| insert into classroom values('Lamberton' | 143 | 10); | |||
| insert into classroom values('Taylor' | 812 | 115); | |||
| insert into classroom values('Saucon' | 113 | 109); | |||
| insert into classroom values('Painter' | 86 | 97); | |||
| insert into classroom values('Alumni' | 547 | 26); | |||
| insert into classroom values('Alumni' | 143 | 47); | |||
| insert into classroom values('Drown' | 757 | 18); | |||
| insert into classroom values('Saucon' | 180 | 15); | |||
| insert into classroom values('Whitman' | 434 | 32); | |||
| insert into classroom values('Saucon' | 844 | 24); | |||
| insert into classroom values('Bronfman' | 700 | 12); | |||
| insert into classroom values('Polya' | 808 | 28); | |||
| insert into classroom values('Gates' | 707 | 65); | |||
| insert into classroom values('Gates' | 314 | 10); | |||
| insert into classroom values('Main' | 45 | 30); | |||
| insert into classroom values('Taylor' | 183 | 71); | |||
| insert into classroom values('Power' | 972 | 10); | |||
| insert into classroom values('Garfield' | 119 | 59); | |||
| insert into classroom values('Rathbone' | 261 | 60); | |||
| insert into classroom values('Stabler' | 105 | 113); | |||
| insert into classroom values('Power' | 717 | 12); | |||
| insert into classroom values('Main' | 425 | 22); | |||
| insert into classroom values('Lambeau' | 348 | 51); | |||
| insert into classroom values('Chandler' | 804 | 11); | |||
| insert into department values('Civil Eng.' | 'Chandler' | 255041.46); | |||
| insert into department values('Biology' | 'Candlestick' | 647610.55); | |||
| insert into department values('History' | 'Taylor' | 699140.86); | |||
| insert into department values('Physics' | 'Wrigley' | 942162.76); | |||
| insert into department values('Marketing' | 'Lambeau' | 210627.58); | |||
| insert into department values('Pol. Sci.' | 'Whitman' | 573745.09); | |||
| insert into department values('English' | 'Palmer' | 611042.66); | |||
| insert into department values('Accounting' | 'Saucon' | 441840.92); | |||
| insert into department values('Comp. Sci.' | 'Lamberton' | 106378.69); | |||
| insert into department values('Languages' | 'Linderman' | 601283.60); | |||
| insert into department values('Finance' | 'Candlestick' | 866831.75); | |||
| insert into department values('Geology' | 'Palmer' | 406557.93); | |||
| insert into department values('Cybernetics' | 'Mercer' | 794541.46); | |||
| insert into department values('Astronomy' | 'Taylor' | 617253.94); | |||
| insert into department values('Athletics' | 'Bronfman' | 734550.70); | |||
| insert into department values('Statistics' | 'Taylor' | 395051.74); | |||
| insert into department values('Psychology' | 'Thompson' | 848175.04); | |||
| insert into department values('Math' | 'Brodhead' | 777605.11); | |||
| insert into department values('Elec. Eng.' | 'Main' | 276527.61); | |||
| insert into department values('Mech. Eng.' | 'Rauch' | 520350.65); | |||
| insert into course values('787' | 'C Programming' | 'Mech. Eng.' | 4); | ||
| insert into course values('238' | 'The Music of Donovan' | 'Mech. Eng.' | 3); | ||
| insert into course values('608' | 'Electron Microscopy' | 'Mech. Eng.' | 3); | ||
| insert into course values('539' | 'International Finance' | 'Comp. Sci.' | 3); | ||
| insert into course values('278' | 'Greek Tragedy' | 'Statistics' | 4); | ||
| insert into course values('972' | 'Greek Tragedy' | 'Psychology' | 4); | ||
| insert into course values('391' | 'Virology' | 'Biology' | 3); | ||
| insert into course values('814' | 'Compiler Design' | 'Elec. Eng.' | 3); | ||
| insert into course values('272' | 'Geology' | 'Mech. Eng.' | 3); | ||
| insert into course values('612' | 'Mobile Computing' | 'Physics' | 3); | ||
| insert into course values('237' | 'Surfing' | 'Cybernetics' | 3); | ||
| insert into course values('313' | 'International Trade' | 'Marketing' | 3); | ||
| insert into course values('887' | 'Latin' | 'Mech. Eng.' | 3); | ||
| insert into course values('328' | 'Composition and Literature' | 'Cybernetics' | 3); | ||
| insert into course values('984' | 'Music of the 50s' | 'History' | 3); | ||
| insert into course values('241' | 'Biostatistics' | 'Geology' | 3); | ||
| insert into course values('338' | 'Graph Theory' | 'Psychology' | 3); | ||
| insert into course values('400' | 'Visual BASIC' | 'Psychology' | 4); | ||
| insert into course values('760' | 'How to Groom your Cat' | 'Accounting' | 3); | ||
| insert into course values('629' | 'Finite Element Analysis' | 'Cybernetics' | 3); | ||
| insert into course values('762' | 'The Monkeys' | 'History' | 4); | ||
| insert into course values('242' | 'Rock and Roll' | 'Marketing' | 3); | ||
| insert into course values('482' | 'FOCAL Programming' | 'Psychology' | 4); | ||
| insert into course values('581' | 'Calculus' | 'Pol. Sci.' | 4); | ||
| insert into course values('843' | 'Environmental Law' | 'Math' | 4); | ||
| insert into course values('679' | 'The Beatles' | 'Math' | 3); | ||
| insert into course values('704' | 'Marine Mammals' | 'Geology' | 4); | ||
| insert into course values('774' | 'Game Programming' | 'Cybernetics' | 4); | ||
| insert into course values('591' | 'Shakespeare' | 'Pol. Sci.' | 4); | ||
| insert into course values('319' | 'World History' | 'Finance' | 4); | ||
| insert into course values('960' | 'Tort Law' | 'Civil Eng.' | 3); | ||
| insert into course values('274' | 'Corporate Law' | 'Comp. Sci.' | 4); | ||
| insert into course values('426' | 'Video Gaming' | 'Finance' | 3); | ||
| insert into course values('852' | 'World History' | 'Athletics' | 4); | ||
| insert into course values('408' | 'Bankruptcy' | 'Accounting' | 3); | ||
| insert into course values('808' | 'Organic Chemistry' | 'English' | 4); | ||
| insert into course values('902' | 'Existentialism' | 'Finance' | 3); | ||
| insert into course values('730' | 'Quantum Mechanics' | 'Elec. Eng.' | 4); | ||
| insert into course values('362' | 'Embedded Systems' | 'Finance' | 4); | ||
| insert into course values('341' | 'Quantum Mechanics' | 'Cybernetics' | 3); | ||
| insert into course values('582' | 'Marine Mammals' | 'Cybernetics' | 3); | ||
| insert into course values('867' | 'The IBM 360 Architecture' | 'History' | 3); | ||
| insert into course values('169' | 'Marine Mammals' | 'Elec. Eng.' | 3); | ||
| insert into course values('680' | 'Electricity and Magnetism' | 'Civil Eng.' | 3); | ||
| insert into course values('227' | 'Elastic Structures' | 'Languages' | 4); | ||
| insert into course values('991' | 'Transaction Processing' | 'Psychology' | 3); | ||
| insert into course values('366' | 'Computational Biology' | 'English' | 3); | ||
| insert into course values('376' | 'Cost Accounting' | 'Physics' | 4); | ||
| insert into course values('489' | 'Journalism' | 'Astronomy' | 4); | ||
| insert into course values('663' | 'Geology' | 'Psychology' | 3); | ||
| insert into course values('461' | 'Physical Chemistry' | 'Math' | 3); | ||
| insert into course values('105' | 'Image Processing' | 'Astronomy' | 3); | ||
| insert into course values('407' | 'Industrial Organization' | 'Languages' | 4); | ||
| insert into course values('254' | 'Security' | 'Cybernetics' | 3); | ||
| insert into course values('998' | 'Immunology' | 'Civil Eng.' | 4); | ||
| insert into course values('457' | 'Systems Software' | 'History' | 3); | ||
| insert into course values('401' | 'Sanitary Engineering' | 'Athletics&
Collepals.com Plagiarism Free Papers Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers. Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS Why Hire Collepals.com writers to do your paper? Quality- We are experienced and have access to ample research materials. We write plagiarism Free Content Confidential- We never share or sell your personal information to third parties. Support-Chat with us today! We are always waiting to answer all your questions.  All Rights Reserved Terms and Conditions |