You will be using a SQL script for Oracle or PostGres that will build a database model and populate the database with data. You will be querying the data to show your expertise at joining tables. Download the scripts "SQL  Scripts" for the appropriate DBMS - there are different scripts for Oracle and for PostGres because they have slightly different SQL syntax. Run the attached scripts. 

 

Section 1:  a simple select query with a where clause that would allow you to list all faculty members with a middle initial "T". What ID number(s) are returned from your query? Include your query and the ID number in your response.

 

Section 2: a grouping query that joins the diploma and degree tables to count the number of alumni that graduated from each department (not the number of degrees). How many people graduated from the "Information Systems" Department? Include your query and the number of people who graduated from the "Information Systems" Department in your response.

 

Section 3:  a grouping query that joins the gift and donor_prospect tables together. Provide a list of the first and last name of any individuals who gave to any of the "Hackman Scholars Fund" scholarship fund, the "Geography Graduate Research" program fund, or the "IS Undergraduate Research" program fund. In your output list the fund they gave to (in a single query). You may find the "||" operator to be useful. 

 

Section 4: a new grouping query that joins the same 2 tables - gift and donor_prospect. Now, your query should make a list of the same 3 funds: "Hackman Scholars Fund", "Geography Graduate Research", and "IS Undergraduate Research" and list in alphabetical order the last names of any individuals who gave to those funds on the same line. You may find the "LISTAGG" operation to be useful. 

 

Section 5: The Department Chair of the Management Department wants to invite the best 3 prospective donors to dinner with her. She is considering inviting: Yani Fifer (DPRID: 825926593) Chuck Barakat (DPRID: 459980560) Ameerah Alday (DPRID: 224148484) Give your logic whether this is a good decision and use a query to support your position. If you would invite alternative guests, list the 3 people you would invite instead.

Please zoom in the attached sql queries

Transcribed Image Text:

CREATE TABLE ZIPCODE ( ……………●●●●●● varchar(50), varchar (20), varchar (2), int, Zip_add_zip CONSTRAINT pk Zip PRIMARY KEY (Zip_add_street, Zip_add_city, Zip_add_state) ); Zip_add_street Zip_add_city Zip_add_state CREATE TABLE SEMESTER ( int Sem_id varchar (6), Sem_sem int, Sem_year ●●●●●● CONSTRAINT ck_Sem_sem CHECK (Sem_sem IN('Fall', 'Spring', 'Summer')) ); CREATE TABLE ADDRESS ( Add_id int, Add_street varchar (50), Add_city varchar (20), ●●●●●●●●●●● Add_state varchar (2), ********** CONSTRAINT pk_Add PRIMARY KEY (Add_id), CONSTRAINT fk_Add_zip FOREIGN KEY (Add_street, Add_city, Add_state) REFERENCES ZIPCODE (Zip_add_street, Zip_add_city, Zip_add_state) ……….……●●●●● ); CREATE TABLE COLLEGE ( Col_name Col_budget Col_enrollment Col_numofprofs CREATE TABLE DEPARTMENT ( Dep_name Dep_budget Dep enrollment ............... CREATE TABLE FACULTY ( Fac_univid ............ Fac_first_nm ●●●●●●●●●● Fac id nm ….......... Fac_last_nm ............. Fac_type Dep_numofprofs .............. Dep_col_name CONSTRAINT fk_Dep_col FOREIGN KEY (Dep_col_name) REFERENCES COLLEGE ( Col_name) ); ); CREATE TABLE PROGRAM ( Prg_name Prg_desc Prg_fundingdep_name Prq admindep_name varchar (40) int, int, int varchar(40) ●●●●●●●●●●● int, int, int, ....... CREATE TABLE DEGREE varchar (40), ●●●●●●●●●●● int varchar (20) var (20) varchar (20) ●●●●●●2009 char(1) varchar (40), Fac_dep_name CONSTRAINT fk_Fac_dep FOREIGN KEY (Fac_dep_name) REFERENCES DEPARTMENT (Dep_name), CONSTRAINT ck_Fac_type CHECK (Fac_type IN ('T', 'A')) CONSTRAINT pk_sem PRIMARY KEY, varchar (40) varchar (80) varchar (40), CONSTRAINT pk_Col PRIMARY KEY, varchar (40) ●●●●●●●●● CONSTRAINT pk_Dep PRIMARY KEY, CONSTRAINT pk_fac PRIMARY KEY, CONSTRAINT nn_fac_first_nm not null, CONSTRAINT nn_fac_last_nm not null, CONSTRAINT nn_fac_type not null, CONSTRAINT nn_prg_admin not null, 000000000000 CONSTRAINT fk_Prg_depfund FOREIGN KEY (Prg_fundingdep_name) REFERENCES DEPARTMENT (Dep_name), CONSTRAINT fk_Prg_depadmin FOREIGN KEY (Prg_admindep_name) REFERENCES DEPARTMENT (Dep_name) CONSTRAINT pk_Prg PRIMARY KEY, CONSTRAINT nn_prg_desc not null, ............ CREATE TABLE ZIPCODE ( ……………●●●●●● varchar(50), varchar (20), varchar (2), int, Zip_add_zip CONSTRAINT pk Zip PRIMARY KEY (Zip_add_street, Zip_add_city, Zip_add_state) ); Zip_add_street Zip_add_city Zip_add_state CREATE TABLE SEMESTER ( int Sem_id varchar (6), Sem_sem int, Sem_year ●●●●●● CONSTRAINT ck_Sem_sem CHECK (Sem_sem IN('Fall', 'Spring', 'Summer')) ); CREATE TABLE ADDRESS ( Add_id int, Add_street varchar (50), Add_city varchar (20), ●●●●●●●●●●● Add_state varchar (2), ********** CONSTRAINT pk_Add PRIMARY KEY (Add_id), CONSTRAINT fk_Add_zip FOREIGN KEY (Add_street, Add_city, Add_state) REFERENCES ZIPCODE (Zip_add_street, Zip_add_city, Zip_add_state) ……….……●●●●● ); CREATE TABLE COLLEGE ( Col_name Col_budget Col_enrollment Col_numofprofs CREATE TABLE DEPARTMENT ( Dep_name Dep_budget Dep enrollment ............... CREATE TABLE FACULTY ( Fac_univid ............ Fac_first_nm ●●●●●●●●●● Fac id nm ….......... Fac_last_nm ............. Fac_type Dep_numofprofs .............. Dep_col_name CONSTRAINT fk_Dep_col FOREIGN KEY (Dep_col_name) REFERENCES COLLEGE ( Col_name) ); ); CREATE TABLE PROGRAM ( Prg_name Prg_desc Prg_fundingdep_name Prq admindep_name varchar (40) int, int, int varchar(40) ●●●●●●●●●●● int, int, int, ....... CREATE TABLE DEGREE varchar (40), ●●●●●●●●●●● int varchar (20) var (20) varchar (20) ●●●●●●2009 char(1) varchar (40), Fac_dep_name CONSTRAINT fk_Fac_dep FOREIGN KEY (Fac_dep_name) REFERENCES DEPARTMENT (Dep_name), CONSTRAINT ck_Fac_type CHECK (Fac_type IN ('T', 'A')) CONSTRAINT pk_sem PRIMARY KEY, varchar (40) varchar (80) varchar (40), CONSTRAINT pk_Col PRIMARY KEY, varchar (40) ●●●●●●●●● CONSTRAINT pk_Dep PRIMARY KEY, CONSTRAINT pk_fac PRIMARY KEY, CONSTRAINT nn_fac_first_nm not null, CONSTRAINT nn_fac_last_nm not null, CONSTRAINT nn_fac_type not null, CONSTRAINT nn_prg_admin not null, 000000000000 CONSTRAINT fk_Prg_depfund FOREIGN KEY (Prg_fundingdep_name) REFERENCES DEPARTMENT (Dep_name), CONSTRAINT fk_Prg_depadmin FOREIGN KEY (Prg_admindep_name) REFERENCES DEPARTMENT (Dep_name) CONSTRAINT pk_Prg PRIMARY KEY, CONSTRAINT nn_prg_desc not null, ............