Answered: THIS IS A REVIEW QUESTION The SQL…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Database Question

THIS IS A REVIEW QUESTION

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

INSERT STATEMENT LINK below

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

Table.SQL 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;

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:
(7pts) Use the OR operator for the state predicates.
II.
I.
(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
| Skoglund
| City10 | New York
| City10 | New York
| City10 | New York
Zack
| 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 |
| 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 |
| M
Thanh
| Soderstrom| M
Randy
| Kelsie
| Marlow
| Valenzula
| Hickey
| Wardle
| city10 | New York
| City10 | New York
| city17 | New Jersey |
| F
| Kasi
| F
Jasper
| Essie
| M
| City10 | New York
| City10 | New York
| city17 | New Jersey |
| Vankirk
| F
|
| vivanco
| Bryan
Emory
| M
| 1966-01-07 00:00:00| 590 Broadway Av |
|
Delorse
| F
| 1998-11-04 00:00:00 | 135 Broadway Ln ||

Expert Solution

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions