MySQL - Please include screenshot

/******************************************************** * This script creates the database named my_guitar_shop *********************************************************/

DROP DATABASE IF EXISTS cs431_music_store; CREATE DATABASE cs431_music_store; USE cs431_music_store;

-- create the tables for the database CREATE TABLE categories ( category_id INT PRIMARY KEY AUTO_INCREMENT, category_name VARCHAR(255) NOT NULL UNIQUE );

CREATE TABLE musicians ( musician_id INT PRIMARY KEY AUTO_INCREMENT, email_address VARCHAR(255) NOT NULL UNIQUE, password VARCHAR(60) NOT NULL, first_name VARCHAR(60) NOT NULL, last_name VARCHAR(60) NOT NULL, shipping_address_id INT DEFAULT NULL, billing_address_id INT DEFAULT NULL );

CREATE TABLE addresses ( address_id INT PRIMARY KEY AUTO_INCREMENT, musician_id INT NOT NULL, line1 VARCHAR(60) NOT NULL, line2 VARCHAR(60) DEFAULT NULL, city VARCHAR(40) NOT NULL, state VARCHAR(2) NOT NULL, zip_code VARCHAR(10) NOT NULL, phone VARCHAR(12) NOT NULL, disabled TINYINT(1) NOT NULL DEFAULT 0, CONSTRAINT addresses_fk_musicians FOREIGN KEY (musician_id) REFERENCES musicians (musician_id)

INSERT INTO musicians (musician_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id) VALUES (1, 'allan.sherwood@yahoo.com', '650215acec746f0e32bdfff387439eefc1358737', 'Allan', 'Sherwood', 1, 2), (2, 'barryz@gmai.com', '3f563468d42a448cb1e56924529f6e7bbe529cc7', 'Barry', 'Zimmer', 3, 3), (3, 'christineb@solarone.com', 'ed19f5c0833094026a2f1e9e6f08a35d26037066', 'Christine', 'Brown', 4, 4), (4, 'david.goldstein@hotmail.com', 'b444ac06613fc8d63795be9ad0beaf55011936ac', 'David', 'Goldstein', 5, 6), (5, 'erinv@gmai.com', '109f4b3c50d7b0df729d299bc6f8e9ef9066971f', 'Erin', 'Valentino', 7, 7), (6, 'frankwilson@sbcglobal.net', '3ebfa301dc59196f18593c45e519287a23297589', 'Frank Lee', 'Wilson', 8, 8), (7, 'gary_hernandez@yahoo.com', '1ff2b3704aede04eecb51e50ca698efd50a1379b', 'Gary', 'Hernandez', 9, 10), (8, 'heatheresway@mac.com', '911ddc3b8f9a13b5499b6bc4638a2b4f3f68bf23', 'Heather', 'Esway', 11, 12);

INSERT INTO addresses (address_id, musician_id, line1, line2, city, state, zip_code, phone, disabled) VALUES (1, 1, '100 East Ridgewood Ave.', '', 'Paramus', 'NJ', '07652', '201-653-4472', 0), (2, 1, '21 Rosewood Rd.', '', 'Woodcliff Lake', 'NJ', '07677', '201-653-4472', 0), (3, 2, '16285 Wendell St.', '', 'Omaha', 'NE', '68135', '402-896-2576', 0), (4, 3, '19270 NW Cornell Rd.', '', 'Beaverton', 'OR', '97006', '503-654-1291', 0), (5, 4, '186 Vermont St.', 'Apt. 2', 'San Francisco', 'CA', '94110', '415-292-6651', 0), (6, 4, '1374 46th Ave.', '', 'San Francisco', 'CA', '94129', '415-292-6651', 0), (7, 5, '6982 Palm Ave.', '', 'Fresno', 'CA', '93711', '559-431-2398', 0), (8, 6, '23 Mountain View St.', '', 'Denver', 'CO', '80208', '303-912-3852', 0), (9, 7, '7361 N. 41st St.', 'Apt. B', 'New York', 'NY', '10012', '212-335-2093', 0), (10, 7, '3829 Broadway Ave.', 'Suite 2', 'New York', 'NY', '10012', '212-239-1208', 0), (11, 8, '2381 Buena Vista St.', '', 'Los Angeles', 'CA', '90023', '213-772-5033', 0), (12, 8, '291 W. Hollywood Blvd.', '', 'Los Angeles', 'CA', '90024', '213-391-2938', 0);

5. Write a SELECT statement that joins the Musicians table to the Addresses table and returns these columns: first_name, last_name, linel, city, state, zip_code. Return one row for each musician, but only return addresses that are the billing address for a musician. Table 1 1 first_name last_name Line1 city State zip_code 2 Allan Sherwood 21 Rosewood Rd. Woodcliff Lake NJ 07677 3 Barry Zimmer 16285 Wendell St. Omaha NE 68135 4 Christine Brown Beaverton OR 97006 19270 NW Cornell Rd. 5 David Goldstein 1374 46th Ave. San Francisco CA 94129 6 Erin Valentino 6982 Palm Ave. Fresno CA 93711 7 Frank Lee Wilson Denver CO 80208 23 Mountain View St. 8 Gary Hernandez New York NY 10012 3829 Broadway Ave. 9 Heather Esway Los Angeles CA 90024 291 W. Hollywood Blvd. 10 11