I need to write a SELECT query in SQL using these instructions. Attached is the code that created the tables. SHOW name-of-tech part which are less than $300 and The Name of the Supplier. This will show which supplier has what parts under $300.FOR [Supplier]QUERY WILL USE: JOIN yes SUBQUERY no AGGREGATION no CREATE TABLE t_customer (id INT AUTO_INCREMENT,fname VARCHAR(30) NOT NULL,mname VARCHAR (30),lname VARCHAR(30) NOT NULL,street VARCHAR(50),city VARCHAR(50),st VARCHAR(10),email VARCHAR (50),phone VARCHAR (20),PRIMARY KEY (id)> ENGINE = innodb;CREATE TABLE employee(id INT AUTO_INCREMENT,fname VARCHAR(30) NOT NULL,mname VARCHAR (30),lname VARCHAR(30) NOT NULL,street VARCHAR (50),city VARCHAR(50),st VARCHAR(10),email VARCHAR (50),phone VARCHAR (20),PRIMARY KEY (id)) ENGINE = innodb;CREATE TABLE supplier (id INT AUTO_INCREMENT,name VARCHAR(50) NOT NULL,phone VARCHAR (20),PRIMARY KEY (id)) ENGINE = innodb; CREATE TABLE tech_parts(id INT AUTO_INCREMENT,part number INT NOT NULL,name VARCHAR(30) NOT NULL,brand VARCHAR(30),price INT,bought_date DATE,cus_id INT,sup_id INT,FOREIGN KEY (cus_id) REFERENCES t_customer(id),FOREIGN KEY (sup_id) REFERENCES supplier(id),PRIMARY KEY (id)) ENGINE = innodb;CREATE TABLE product (id INT AUTO_INCREMENT,brand VARCHAR(30),devicetype VARCHAR(30),cus_id INT NOT NULL,emp_id INT NOT NULL,part_id INT,FOREIGN KEY (cus_id) REFERENCES t_customer(id),FOREIGN KEY (emp_id) REFERENCES employee (id),FOREIGN KEY (part_id) REFERENCES tech_parts(id),PRIMARY KEY (id)) ENGINE = innodb;CREATE TABLE refurbished_item (id INT AUTO_INCREMENT,item_source VARCHAR(30) NOT NULL,brand VARCHAR (30),device_type VARCHAR(30),price INT,t_customer_id INT NOT NULL,employee_id INT NOT NULL,FOREIGN KEY (t_customer_id) REFERENCES t_customer(id),FOREIGN KEY (employee_id) REFERENCES employee (id),PRIMARY KEY (id)) ENGINE = innodb;

Please refer to the following step for the complete solution to the problem above.

Answered: I need to write a SELECT query in 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

I need to write a SELECT query in SQL using these instructions. Attached is the code that created the tables.

SHOW name-of-tech part which are less than $300 and The Name of the Supplier. This will show which supplier has what parts under $300.

FOR [Supplier]

QUERY WILL USE: JOIN yes SUBQUERY no AGGREGATION no

CREATE TABLE t_customer (
id INT AUTO_INCREMENT,
fname VARCHAR(30) NOT NULL,
mname VARCHAR (30),
lname VARCHAR(30) NOT NULL,
street VARCHAR(50),
city VARCHAR(50),
st VARCHAR(10),
email VARCHAR (50),
phone VARCHAR (20),
PRIMARY KEY (id)
> ENGINE = innodb;
CREATE TABLE employee(
id INT AUTO_INCREMENT,
fname VARCHAR(30) NOT NULL,
mname VARCHAR (30),
lname VARCHAR(30) NOT NULL,
street VARCHAR (50),
city VARCHAR(50),
st VARCHAR(10),
email VARCHAR (50),
phone VARCHAR (20),
PRIMARY KEY (id)
) ENGINE = innodb;
CREATE TABLE supplier (
id INT AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
phone VARCHAR (20),
PRIMARY KEY (id)
) ENGINE = innodb;

Transcribed Image Text:CREATE TABLE tech_parts( id INT AUTO_INCREMENT, part number INT NOT NULL, name VARCHAR(30) NOT NULL, brand VARCHAR(30), price INT, bought_date DATE, cus_id INT, sup_id INT, FOREIGN KEY (cus_id) REFERENCES t_customer(id), FOREIGN KEY (sup_id) REFERENCES supplier(id), PRIMARY KEY (id) ) ENGINE = innodb; CREATE TABLE product ( id INT AUTO_INCREMENT, brand VARCHAR(30), devicetype VARCHAR(30), cus_id INT NOT NULL, emp_id INT NOT NULL, part_id INT, FOREIGN KEY (cus_id) REFERENCES t_customer(id), FOREIGN KEY (emp_id) REFERENCES employee (id), FOREIGN KEY (part_id) REFERENCES tech_parts(id), PRIMARY KEY (id) ) ENGINE = innodb; CREATE TABLE refurbished_item ( id INT AUTO_INCREMENT, item_source VARCHAR(30) NOT NULL, brand VARCHAR (30), device_type VARCHAR(30), price INT, t_customer_id INT NOT NULL, employee_id INT NOT NULL, FOREIGN KEY (t_customer_id) REFERENCES t_customer(id), FOREIGN KEY (employee_id) REFERENCES employee (id), PRIMARY KEY (id) ) ENGINE = innodb;

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