LINK TO SQL DATA HERE ( had to add a space in between so it does not get blocked)

https://drive.go ogle.com/file/d/1qHCkrods5K2V1syyS5IG6-Eb_3QQmud7/view?usp=sharing

TABLE SQL DATA BELOW

-- MySQL Script generated by MySQL Workbench -- Sun Feb 21 00:00:31 2021 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- ----------------------------------------------------- -- Schema mydb -- -----------------------------------------------------

-- ----------------------------------------------------- -- Table state -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS state ( state_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(45) NOT NULL, abb VARCHAR(2) NOT NULL, PRIMARY KEY (state_id), UNIQUE INDEX abb_UNIQUE (abb ASC)) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table address -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS address ( address_id INT NOT NULL AUTO_INCREMENT, street1 VARCHAR(45) NOT NULL, street2 VARCHAR(45) NULL, city VARCHAR(45) NOT NULL, zip VARCHAR(5) NOT NULL, state_id INT NOT NULL, PRIMARY KEY (address_id), INDEX FK_ADDRESS_STATE_IDX (state_id ASC), CONSTRAINT FK_ADDRESS_STATE FOREIGN KEY (state_id) REFERENCES state (state_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table person -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS person ( person_id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, sex VARCHAR(1) NOT NULL, dob DATETIME, address_id INT NULL, PRIMARY KEY (person_id), INDEX FK_PERSON_ADDRESS_IDX (address_id ASC), CONSTRAINT FK_PERSON_ADDRESS FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table branch -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS branch ( branch_no VARCHAR(5) NOT NULL, address_id INT NOT NULL, PRIMARY KEY (branch_no), INDEX FK_BRANCH_ADDRESS_IDX (address_id ASC) COMMENT ' ', UNIQUE INDEX ADDRESS_ID_UNIQUE (address_id ASC), CONSTRAINT FK_BRANCH_ADDRESS FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table staff -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS staff ( staff_no VARCHAR(5) NOT NULL, person_id INT NOT NULL, position VARCHAR(45) NULL, salary INT NULL, branch_no VARCHAR(5) NOT NULL, PRIMARY KEY (staff_no), INDEX FK_STAFF_PERSON_IDX (person_id ASC), UNIQUE INDEX PERSON_ID_UNIQUE (person_id ASC), INDEX fk_staff_branch1_idx (branch_no ASC), CONSTRAINT FK_STAFF_PERSON FOREIGN KEY (person_id) REFERENCES person (person_id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_staff_branch1 FOREIGN KEY (branch_no) REFERENCES branch (branch_no) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Preamble The database relational schema for this assignment is shown in Figure 1. Study the tables and their relations carefully before attempting to solve the questions below. The source code for most of the tables is provided in the file A1_Tables.sql. Test data is provided in the file A1_Data.sql. To get started, create the test database as follows: 1. Login into MariaDB/MySQL using an authorized account (e.g. root). 2. Create a new database (e.g. cis495). If the database already exists, drop it first. 3. Copy the contents of the file A1_Tables.sql and paste them into your SQL command line window. 4. Copy the contents of the file Al_Data.sql- and paste them into your SQL command line window. Note: I had an issue with direct copy-paste of the contents of A1_Data.sql. Alternatively, I used the following SQL commands: source source Change the correct path on your OS The test data for the last two tables is commented out. Once you have completed Q1 below, uncomment these lines and paste them into your SQL command line window. Questions: Q1) (10pts) Code conforms to the SQL standards and has comments before each query to indicate it's puropose. Q2) (20pts) Write the SQL statements to create the tables client and property_for_rent. Once done, you can load the last portion of the test data in A1_Data.Sql Q3) (20pts) Show all persons who live in New York or New Jersesy. Order by first name (descending) then by state name (ascending). Write the query using three different approaches as follows: I. (7pts) Use the OR operator for the state predicates. II. (7pts) Use IN and make sure you do not use OR or AND. III. (6pts) Use UNION and make sure you do not use WHILE. | first_name | last_name sex dob | streeti street2 | city | name | Zack Thanh | Randy | Kelsie | Kasi | Jasper | Essie | Emory | Delorse | Skoglund IM | Soderstrom | M | Marlow | M | Valenzula | Hickey | Wardle | M | Vankirk | Vivanco IM | Bryan | 1966-09-16 00:00:00 | 590 Broadway Av | 1979-01-02 00:00:00 | 590 Broadway Av | | 1986-03-10 00:00:00 | 590 Broadway AV I | 1932-02-11 00:00:00 | 590 Broadway Av | | 1914-10-28 00:00:00 | 590 Broadway Av | 1905-10-20 00:00:00 | 135 Broadway Ln | | 1982-01-18 00:00:00 | 590 Broadway Av I | 1966-01-07 00:00:00 | 590 Broadway Av | | 1998-11-04 00:00:00 | 135 Broadway in | | City10 | New York I city10 | New York | City10 | New York | city10 | New York | City10 | New York | City17 | New Jersey | | City10 | New York | City10 | New York | City17 | New Jersey 9 rows in set (0.001 sec) Assignment 1-CIS 495 - BMCC 1 Q4) (15pts) Show clients that have 3 or more properties for rent. Order by the number of properties for rent in descending order. first name last_name client_properties 4 I Heidi | Nicolas Evelia Deon Elmer | Reggie | Coffin Hetherington | Patty | Bridgett | Hodak | Sartor 31 31 31 31 6 rows in set 05) (20pts) Show the properties for rent if that property's rent is (1) larger than the average rent for all other properties combined. (2) Limit the result to properties that have a client number (i.e. client_no cannot be null). Your query should also show by how much the rent is higher for each property. Order by the rent difference property_no prop_type staff_no | client_no rooms state difference PR512 | PR232 | PR875 | PR127 1PR655 | PR131 | PR810 PR343 | PR431 Flat | Condo | Condo | Flat House | Flat Flat | Flat House II SG530 | SL557 | SQ537 | NULL | SG508 | NULL | SQ574 | NULL | SQ551 IT | CR228 | CR191 | CR147 | CR148 | CR195 | CR244 | CR170 | CR222 | CR183 | Oklahoma | Georgia | North Carolina 15 | Michigan | 2 | Alaska 18 | Florida | Minnesota 13 | Maryland 11 | Michigan III 1.2600 19.26001 59.2600 85.26001 150.2600 173.2600 228.26001 255.2600 390.26001 1 41 rows in set 06) (15pts) Show properties for sale, the property's state, the property's branch, and the state of that branch. Order by the rent from highest to lowest. Note that the branch and property states may be different addresses. To distinguish between the addresses, the query must use two independent copies of the address and state tables. The following query uses myTable twice as two separate copies. SELECT * FROM myTable t1, myTable t2.... WHERE ... Bonus Question: (5pts) if you wrote Q6 using JOIN, rewrite using WHERE. If you used WHERE, rewrite using JOIN. property_no prop_type | rooms 7 | 2 I PR200 | PR901 PR173 | PR838 | PR797 | PR169 IPR575 | PR720 | PR656 | PR254 PR854 | PR859 L Flat | Flat | House | Condo | Condo House | Flat | Flat | Flat | Flat | Condo | House i rent Property State | staff_no | branch_no | Branch State 24 | Nevada | SQ523 B623 | Pennsylvania 31 | Vermont ISL569 B647 | Florida 59 | Wyoming | SG570 B911 | Colorado 72 | Minnesota | S0541 | B745 | New Mexico 79 | Wyoming | SQ536 B623 | Pennsylvania 88 | Wyoming IS0581 B714 | North Dakota 155 | Massachusetts | SG535 B632 | Louisiana 161 | West Virginia | S0574 B647 | Florida 174 | North Dakota | SL584 B424 | Kentucky 236 | Iowa | SQ504 | B960 | Rhode Island 265 | Arizona | SG516 | B190 | Vermont 296 | Rhode Island | SG527 B647 | Florida i 1 I i I 1 L 114 rows Assignment 1-CIS 495 - BMCC 2 Figures: staff staff_no VARCHAR(5) person_id INT position VARCHAR(45) 10- salary INT branch_no VARCHAR(5) Indexes PRIMARY FK_STAFF_PERSON_IDX PERSON_ID_UNIQUE fk_staff_branch1_idx branch branch_no VARCHAR(5) address_id INT - Indexes PRIMARY FK_BRANCH_ADDRESS_IDX ADDRESS_ID_UNIQUE property_for_rent property_no VARCHAR(5) address_id INT prop_type VARCHAR(45) rooms VARCHAR(45) rent INT staff_no VARCHAR(5) client_no VARCHAR(5) branch_no VARCHAR(5) Indexes PRIMARY HFK_PROPERTY_FOR_RENT_ADDRESS_IDX FK_PROPERTY_FOR_RENT_STAFF_IDX FK_PROPERTY_FOR_RENT_CLIENT_IDX fk_property_for_rent_branch1_idx address address_id INT #street1 VARCHAR(45) street2 VARCHAR(45) city VARCHAR(45) -H zip VARCHAR(5) state_id INT I OH Indexes PRIMARY FK_ADDRESS_STATE_IDX state state_id INT name VARCHAR(45) abb VARCHAR(2) Indexes PRIMARY person abb_UNIQUE person_id INT first_name VARCHAR(45) last name VARCHAR(45) + Sex VARCHAR(1) dob DATETIME address_id INT +- Indexes PRIMARY FK_PERSON_ADDRESS_IDX client client_no VARCHAR(5) person_id INT telephone VARCHAR(13) email VARCHAR(45) pref_type VARCHAR(16) max_rent INT Indexes PRIMARY FK_CLIENT_PERSON_IDX Figure 1: Database Schema Preamble The database relational schema for this assignment is shown in Figure 1. Study the tables and their relations carefully before attempting to solve the questions below. The source code for most of the tables is provided in the file A1_Tables.sql. Test data is provided in the file A1_Data.sql. To get started, create the test database as follows: 1. Login into MariaDB/MySQL using an authorized account (e.g. root). 2. Create a new database (e.g. cis495). If the database already exists, drop it first. 3. Copy the contents of the file A1_Tables.sql and paste them into your SQL command line window. 4. Copy the contents of the file Al_Data.sql- and paste them into your SQL command line window. Note: I had an issue with direct copy-paste of the contents of A1_Data.sql. Alternatively, I used the following SQL commands: source source Change the correct path on your OS The test data for the last two tables is commented out. Once you have completed Q1 below, uncomment these lines and paste them into your SQL command line window. Questions: Q1) (10pts) Code conforms to the SQL standards and has comments before each query to indicate it's puropose. Q2) (20pts) Write the SQL statements to create the tables client and property_for_rent. Once done, you can load the last portion of the test data in A1_Data.Sql Q3) (20pts) Show all persons who live in New York or New Jersesy. Order by first name (descending) then by state name (ascending). Write the query using three different approaches as follows: I. (7pts) Use the OR operator for the state predicates. II. (7pts) Use IN and make sure you do not use OR or AND. III. (6pts) Use UNION and make sure you do not use WHILE. | first_name | last_name sex dob | streeti street2 | city | name | Zack Thanh | Randy | Kelsie | Kasi | Jasper | Essie | Emory | Delorse | Skoglund IM | Soderstrom | M | Marlow | M | Valenzula | Hickey | Wardle | M | Vankirk | Vivanco IM | Bryan | 1966-09-16 00:00:00 | 590 Broadway Av | 1979-01-02 00:00:00 | 590 Broadway Av | | 1986-03-10 00:00:00 | 590 Broadway AV I | 1932-02-11 00:00:00 | 590 Broadway Av | | 1914-10-28 00:00:00 | 590 Broadway Av | 1905-10-20 00:00:00 | 135 Broadway Ln | | 1982-01-18 00:00:00 | 590 Broadway Av I | 1966-01-07 00:00:00 | 590 Broadway Av | | 1998-11-04 00:00:00 | 135 Broadway in | | City10 | New York I city10 | New York | City10 | New York | city10 | New York | City10 | New York | City17 | New Jersey | | City10 | New York | City10 | New York | City17 | New Jersey 9 rows in set (0.001 sec) Assignment 1-CIS 495 - BMCC 1 Q4) (15pts) Show clients that have 3 or more properties for rent. Order by the number of properties for rent in descending order. first name last_name client_properties 4 I Heidi | Nicolas Evelia Deon Elmer | Reggie | Coffin Hetherington | Patty | Bridgett | Hodak | Sartor 31 31 31 31 6 rows in set 05) (20pts) Show the properties for rent if that property's rent is (1) larger than the average rent for all other properties combined. (2) Limit the result to properties that have a client number (i.e. client_no cannot be null). Your query should also show by how much the rent is higher for each property. Order by the rent difference property_no prop_type staff_no | client_no rooms state difference PR512 | PR232 | PR875 | PR127 1PR655 | PR131 | PR810 PR343 | PR431 Flat | Condo | Condo | Flat House | Flat Flat | Flat House II SG530 | SL557 | SQ537 | NULL | SG508 | NULL | SQ574 | NULL | SQ551 IT | CR228 | CR191 | CR147 | CR148 | CR195 | CR244 | CR170 | CR222 | CR183 | Oklahoma | Georgia | North Carolina 15 | Michigan | 2 | Alaska 18 | Florida | Minnesota 13 | Maryland 11 | Michigan III 1.2600 19.26001 59.2600 85.26001 150.2600 173.2600 228.26001 255.2600 390.26001 1 41 rows in set 06) (15pts) Show properties for sale, the property's state, the property's branch, and the state of that branch. Order by the rent from highest to lowest. Note that the branch and property states may be different addresses. To distinguish between the addresses, the query must use two independent copies of the address and state tables. The following query uses myTable twice as two separate copies. SELECT * FROM myTable t1, myTable t2.... WHERE ... Bonus Question: (5pts) if you wrote Q6 using JOIN, rewrite using WHERE. If you used WHERE, rewrite using JOIN. property_no prop_type | rooms 7 | 2 I PR200 | PR901 PR173 | PR838 | PR797 | PR169 IPR575 | PR720 | PR656 | PR254 PR854 | PR859 L Flat | Flat | House | Condo | Condo House | Flat | Flat | Flat | Flat | Condo | House i rent Property State | staff_no | branch_no | Branch State 24 | Nevada | SQ523 B623 | Pennsylvania 31 | Vermont ISL569 B647 | Florida 59 | Wyoming | SG570 B911 | Colorado 72 | Minnesota | S0541 | B745 | New Mexico 79 | Wyoming | SQ536 B623 | Pennsylvania 88 | Wyoming IS0581 B714 | North Dakota 155 | Massachusetts | SG535 B632 | Louisiana 161 | West Virginia | S0574 B647 | Florida 174 | North Dakota | SL584 B424 | Kentucky 236 | Iowa | SQ504 | B960 | Rhode Island 265 | Arizona | SG516 | B190 | Vermont 296 | Rhode Island | SG527 B647 | Florida i 1 I i I 1 L 114 rows Assignment 1-CIS 495 - BMCC 2 Figures: staff staff_no VARCHAR(5) person_id INT position VARCHAR(45) 10- salary INT branch_no VARCHAR(5) Indexes PRIMARY FK_STAFF_PERSON_IDX PERSON_ID_UNIQUE fk_staff_branch1_idx branch branch_no VARCHAR(5) address_id INT - Indexes PRIMARY FK_BRANCH_ADDRESS_IDX ADDRESS_ID_UNIQUE property_for_rent property_no VARCHAR(5) address_id INT prop_type VARCHAR(45) rooms VARCHAR(45) rent INT staff_no VARCHAR(5) client_no VARCHAR(5) branch_no VARCHAR(5) Indexes PRIMARY HFK_PROPERTY_FOR_RENT_ADDRESS_IDX FK_PROPERTY_FOR_RENT_STAFF_IDX FK_PROPERTY_FOR_RENT_CLIENT_IDX fk_property_for_rent_branch1_idx address address_id INT #street1 VARCHAR(45) street2 VARCHAR(45) city VARCHAR(45) -H zip VARCHAR(5) state_id INT I OH Indexes PRIMARY FK_ADDRESS_STATE_IDX state state_id INT name VARCHAR(45) abb VARCHAR(2) Indexes PRIMARY person abb_UNIQUE person_id INT first_name VARCHAR(45) last name VARCHAR(45) + Sex VARCHAR(1) dob DATETIME address_id INT +- Indexes PRIMARY FK_PERSON_ADDRESS_IDX client client_no VARCHAR(5) person_id INT telephone VARCHAR(13) email VARCHAR(45) pref_type VARCHAR(16) max_rent INT Indexes PRIMARY FK_CLIENT_PERSON_IDX Figure 1: Database Schema