My syntax:

CREATE TABLE coffee_shop ( shop_id INTEGER NOT NULL, shop_name varchar(50) NOT NULL, city varchar(50) NOT NULL, state varchar(2) NOT NULL, PRIMARY KEY (shop_id) ); INSERT INTO coffee_shop (shop_id, shop_name, city, state) VALUES(1, 'Super Coffee', 'San Francisco', 'CA'); INSERT INTO coffee_shop (shop_id, shop_name, city, state) VALUES(2, 'Good Coffee', 'Los Angeles', 'CA'); INSERT INTO coffee_shop (shop_id, shop_name, city, state) VALUES(3, 'Strong Coffee', 'San Jose', 'CA');

CREATE TABLE employee ( employee_id INTEGER NOT NULL, first_name varchar(30) NOT NULL, last_name varchar(30) NOT NULL, hire_date date NOT NULL, job_title varchar(30) NOT NULL, shop_id INTEGER NOT NULL, PRIMARY KEY (employee_id), FOREIGN KEY (shop_id) REFERENCES coffee_shop(shop_id ) );

INSERT INTO employee (employee_id, first_name, last_name, hire_date, job_title, shop_id) VALUES(1, 'Judy', 'Ludy', '2022-03-02', 'Worker', 1); INSERT INTO employee (employee_id, first_name, last_name, hire_date, job_title, shop_id) VALUES(2, 'Michael', 'Jackson', '2021-07-05', 'Worker', 2); INSERT INTO employee (employee_id, first_name, last_name, hire_date, job_title, shop_id) VALUES(3, 'William', 'Shakespare', '2020-05-06', 'Worker', 3) ;

CREATE TABLE supplier ( supplier_id INTEGER NOT NULL, company_name varchar(50) NOT NULL, country varchar(30) NOT NULL, sales_contact_name varchar(60) NOT NULL, email varchar(50) NOT NULL, PRIMARY KEY (supplier_id) ) INSERT INTO supplier (supplier_id, company_name, country, sales_contact_name, email) VALUES(1, 'Companyl', 'China', 'Cody', 'goods@mail.com'); INSERT INTO supplier (supplier_id, company_name, country, sales_contact_name, email) VALUES(2, 'Company2', 'America', 'Jax', 'yummy@mail.com'); INSERT INTO supplier (supplier_id, company_name, country, sales_contact_name, email) VALUES(3, 'Company3', 'Russia', 'Kenny', 'coffeeman@mail.com');

CREATE TABLE coffee ( coffee_id INTEGER NOT NULL, shop_id INTEGER NOT NULL, supplier_id INTEGER NOT NULL, coffee_name varchar(30) NOT NULL, price_per_pound NUMERIC(5,2) NOT NULL, PRIMARY KEY (coffee_id), FOREIGN KEY (shop_id) REFERENCES coffee_shop(shop_id ), FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ) ; INSERT INTO coffee (coffee_id, shop_id, supplier_id, coffee_name, price_per_pound) VALUES(1, 1, 1, 'Arabica', 5); INSERT INTO coffee (coffee_id, shop_id, supplier_id, coffee_name, price_per_pound) VALUES(2, 2, 2, 'Liberica', 4); INSERT INTO coffee (coffee_id, shop_id, supplier_id, coffee_name, price_per_pound) VALUES(3, 3, 3, 'Robusta', 5);

CREATE VIEW Enployee_view AS SELECT employee_id, CONCAT(first_name, ' ', last_name) AS enployee_full_name, hire_date, job_title, shop_id FROM employee;

CREATE INDEX index1 ON coffee (coffee_name);

-----------------------------------------------------------------------------------------------------

I provided this for #6 but it was not accepted by my advisor. How should I update this to have a table joins query?

SELECT * FROM coffee, coffee_shop, supplier WHERE coffee.shop_id = coffee_shop.shop_id AND coffee.supplier_id = supplier.supplier_id

B. Create a database using the attached "Jaunty Coffee Co. ERD" by doing the following: 1. Develop SQL code to create each table as specified in the attached "Jaunty Coffee Co. ERD" by doing the following: a. Provide the SQL code you wrote to create all the tables. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. 2. Develop SQL code to populate each table in the database design document by doing the following: Note: This data is not provided. You will be fabricating the data for this step. a. Provide the SQL code you wrote to populate the tables with at least three rows of data in each table. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. 3. Develop SQL code to create a view by doing the following: a. Provide the SQL code you wrote to create your view. The view should show all of the information from the "Employee" table but concatenate eachemployee's first and last name, formatted with a space between the first and last name, into a new attribute called employee_full_name. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. 4. Develop SQL code to create an index on the coffee_name field by doing the following: a. Provide the SQL code you wrote to create your index on the coffee_name field from the "Coffee" table. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. 5. Develop SQL code to create an SFW (SELECT-FROM-WHERE) query for any of your tables or views by doing the following: a. Provide the SQL code you wrote to create your SFW query. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. 6. Develop SQL code to create a query by doing the following: a. Provide the SQL code you wrote to create your table joins query. The query should join together three different tables and include attributes from allthree tables in its output. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. - B6a. SQL Code: Join Query Approaching Competence EVALUATOR COMMENTS: ATTEMPT 1 Approaching Competence The submission provides inaccurate or illogical SQL code written to create the table joins query. Or the table joins query does not join together 3 different tables and include attributes from all 3 tables in its output. - B6b. Join Query: Screenshot of SQL Code Results Approaching Competence EVALUATOR COMMENTS: ATTEMPT 1 Approaching Competence Not applicable