Question

A. Project 1 case: DDL’s and Business Rules - The SQL queries for this project are based on the business rules from PROJECT 1. Thus, the SQL queries will run against the tables from Project 1 (see section B below). The following is a list of the business rules derived from the Project 1 assignment:

- A university is identified by its name and located in a particular city.

- A university has many researchers, each of whom can only be associated with only one university.

- A researcher is identified by an identification number, and has a name, phone, and email address.

- A researcher can attend many conferences.

- A conference is identified by an identification number; it also has a name, a location, and a date.

- For each conference, a researcher may or may not present a scientific research paper.

- For each conference, one university is in-charge of its logistic, while another university is in-charge of its marketing.

- A university can be involved in either role with any conference.

B. Run the following DDL’s and add your own data rows to the tables

CREATE TABLE `university` (

`UNIVERSITY_ID` int(11) NOT NULL,

`UNIVERSITY_NAME` varchar(45) DEFAULT NULL,

`UNIVERSITY_CITY` varchar(45) DEFAULT NULL,

`UNIVERSITY_STATE` varchar(2) DEFAULT NULL,

PRIMARY KEY (`UNIVERSITY_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `conference` (

`CONFERENCE_ID` int(11) NOT NULL,

`CONFERENCE_NAME` varchar(45) DEFAULT NULL,

`CONFERENCE_CITY` varchar(45) DEFAULT NULL,

`CONFERENCE_DATE` date DEFAULT NULL,

`UNIVERSITY_ID_LOGISTICS` int(11) NOT NULL,

`UNIVERSITY_ID_MARKETING` int(11) NOT NULL,

`CONFERENCE_STATE` varchar(2) DEFAULT NULL,

PRIMARY KEY (`CONFERENCE_ID`,`UNIVERSITY_ID_LOGISTICS`,`UNIVERSITY_ID_MARKETING`),

KEY `fk_CONFERENCE_UNIVERSITY1_idx` (`UNIVERSITY_ID_LOGISTICS`),

KEY `fk_CONFERENCE_UNIVERSITY2_idx` (`UNIVERSITY_ID_MARKETING`),

CONSTRAINT `fk_CONFERENCE_UNIVERSITY1` FOREIGN KEY (`UNIVERSITY_ID_LOGISTICS`) REFERENCES `university` (`UNIVERSITY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_CONFERENCE_UNIVERSITY2` FOREIGN KEY (`UNIVERSITY_ID_MARKETING`) REFERENCES `university` (`UNIVERSITY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `researcher` (

`researcher_id` int(11) NOT NULL,

`res_lname` varchar(45) DEFAULT NULL,

`res_fname` varchar(45) DEFAULT NULL,

`res_title` varchar(45) DEFAULT NULL,

`university_UNIVERSITY_ID` int(11) NOT NULL,

PRIMARY KEY (`researcher_id`,`university_UNIVERSITY_ID`),

KEY `fk_researcher_university1_idx` (`university_UNIVERSITY_ID`),

CONSTRAINT `fk_researcher_university1` FOREIGN KEY (`university_UNIVERSITY_ID`) REFERENCES `university` (`UNIVERSITY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `conference_has_researcher` (

`CONFERENCE_CONFERENCE_ID` int(11) NOT NULL,

`RESEARCHER_RESEARCHER_ID` int(11) NOT NULL,

PRIMARY KEY (`CONFERENCE_CONFERENCE_ID`,`RESEARCHER_RESEARCHER_ID`),

KEY `fk_CONFERENCE_has_RESEARCHER_RESEARCHER1_idx` (`RESEARCHER_RESEARCHER_ID`),

KEY `fk_CONFERENCE_has_RESEARCHER_CONFERENCE_idx` (`CONFERENCE_CONFERENCE_ID`),

CONSTRAINT `fk_CONFERENCE_has_RESEARCHER_CONFERENCE` FOREIGN KEY (`CONFERENCE_CONFERENCE_ID`) REFERENCES `conference` (`CONFERENCE_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_CONFERENCE_has_RESEARCHER_RESEARCHER1` FOREIGN KEY (`RESEARCHER_RESEARCHER_ID`) REFERENCES `researcher` (`researcher_id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

C. Data Queries   “Good questions to ask of your data” – The business rules from section A above were used to derive the following 10 query descriptions below. Read each description and write the equivalent SQL query.

1. List the universities sorted by city.

2. List the Researchers sorted by last name.

3. List the researchers and their corresponding universities sorted by the university city. You must use an INNER JOIN with RESEARCHER and UNIVERSITY tables.

4. List the university and “count” of researchers at each university sorted by the university name.

You CANNOT use an inner join. You must use a subquery with RESEARCHER.

5. List the university and “count” of researchers at each university where there is more than 1 researcher. Also, sort this query by the university name. You must user an INNER JOIN (NOT A SUBQUERY)

6. List all conferences in the state of georgia. Include the city and state with the query.

7. List the conference and researcher count for conferences in georgia. You CANNOT use an inner join. You must use “two” subqueries that are nested.

8. List the researcher, university and conferences where the conference is in California. You must user an INNER JOIN (NOT A SUBQUERY).

9. List the university and count of researchers that are planning to present at a conference in 2021. You CANNOT use an inner join. You must use a subquery.

10. List the Universities in the database table. If the university appears in a conference that is in charge of its marketing. Each university can only be listed once. You must user an OUTER JOIN (NO INNER JOIN OR SUBQUERY).

I have part A completed. I am stuck on part C mostly, but help on part B would be greatly appreciated.

Answer 1

1. List the universities sorted by city :

select UNIVERSITY_NAME from university order by UNIVERSITY_CITY;

• This SQL query is using order by clause to order the records

******************************************

2. List the Researchers sorted by last name :

select concat(res_fname,' ',res_lname) as 'researcher name' from researcher
order by res_lname;

• This SQL query is using concat() function along with order by clause

*******************************************

3. List the researchers and their corresponding universities sorted by the university city. You must use an INNER JOIN with RESEARCHER and UNIVERSITY tables.

select concat(res_fname,' ',res_lname) as 'researcher name',UNIVERSITY_NAME
from researcher inner join university
on researcher.university_UNIVERSITY_ID=university.UNIVERSITY_ID
order by UNIVERSITY_CITY;

• This SQL query join two tables researcher and university based on UNIVERSITY_ID and will return the result.

***********************************

4. List the university and “count” of researchers at each university sorted by the university name.

select UNIVERSITY_NAME ,
(select count(researcher_id) from researcher order by university_UNIVERSITY_ID)
as 'Number of researcher' from university
order by UNIVERSITY_NAME;

• This SQL query is using sub query to get the result.

********************************************

5. List the university and “count” of researchers at each university where there is more than 1 researcher.

select UNIVERSITY_NAME , count(researcher_id) as 'Number of researcher'
from university inner join researcher
on university.UNIVERSITY_ID=researcher.university_UNIVERSITY_ID
group by UNIVERSITY_NAME
having count(researcher_id) >1;

• This SQL query is grouping the records based on UNIVERSITY_NAME
• This SQL query is also using having clause

*****************************************

6. List all conferences in the state of georgia:

select CONFERENCE_NAME,CONFERENCE_CITY,CONFERENCE_STATE from conference
where CONFERENCE_STATE like 'georgia';

• This SQL query using like clause to get CONFERENCE details

*********************************

7. List the conference and researcher count for conferences in georgia.

select CONFERENCE_NAME,
(select count(RESEARCHER_RESEARCHER_ID) from conference_has_researcher
where CONFERENCE_CONFERENCE_ID in (select CONFERENCE_ID from conference
where CONFERENCE_STATE like 'georgia')) as 'Number of researchers' from conference;

• This SQL query is using subquery to get the result.

************************************