Question

1 Approved Answer

Posted on Sep 22, 2024

Database Question The SQL statements that answer these 3 questions. please keep the answer simple will give thumbs up thank you :)! I will add

Database Question

The SQL statements that answer these 3 questions. please keep the answer simple will give thumbs up thank you :)!

I will add the sql data and table.sql below the IMAGE

image text in transcribed

LINK TO SQL DATA (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.sq:

-- 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;