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 ### Database Table Creation ScriptsThe following SQL scripts are used to create three different tables in a relational database: `t_customer`, `employee`, and `supplier`. Each table is designed with specific fields and constraints to store relevant data efficiently.---#### `t_customer` TableThis table is designed to store customer information. The columns include:- `id`: An integer that auto-increments to provide a unique identifier for each customer. It serves as the primary key.- `fname`: A `VARCHAR(30)` field for the customer's first name. This field is mandatory (NOT NULL).- `mname`: A `VARCHAR(30)` field for the customer's middle name.- `lname`: A `VARCHAR(30)` field for the customer's last name. This field is mandatory (NOT NULL).- `street`: A `VARCHAR(50)` field for the street address of the customer.- `city`: A `VARCHAR(50)` field for the city of the customer.- `st`: A `VARCHAR(10)` field for the state of the customer.- `email`: A `VARCHAR(50)` field for the customer's email address.- `phone`: A `VARCHAR(20)` field for the customer's phone number.The table uses the `InnoDB` storage engine.---#### `employee` TableThis table is designed to store employee information. The columns are:- `id`: An auto-incrementing integer serving as the primary key.- `fname`: A `VARCHAR(30)` field for the employee's first name. This field is mandatory (NOT NULL).- `mname`: A `VARCHAR(30)` field for the employee's middle name.- `lname`: A `VARCHAR(30)` field for the employee's last name. This field is mandatory (NOT NULL).- `street`: A `VARCHAR(50)` field for the street address of the employee.- `city`: A `VARCHAR(50)` field for the city of the employee.- `st`: A `VARCHAR(10)` field for the state of the employee.- `email`: A `VARCHAR(50)` field for the employee's email address.- `phone`: A `VARCHAR(20)` field for the employee's phone number.The table uses the `InnoDB` storage engine.---#### `supplier` TableThis table is tailored to store supplier information with the following fields:- `id`: An auto The image contains SQL code for creating three tables in a database, each designed to manage different aspects of a tech and customer management system.### Table 1: `tech_parts`- **Description:** Stores information about tech parts.- **Columns:** - `id`: INT, auto-increment, primary key - `part_number`: INT, not null - `name`: VARCHAR(30), not null - `brand`: VARCHAR(30) - `price`: INT - `bought_date`: DATE - `cus_id`: INT, foreign key referencing `t_customer(id)` - `sup_id`: INT, foreign key referencing `supplier(id)`### Table 2: `product`- **Description:** Stores information about products.- **Columns:** - `id`: INT, auto-increment, primary key - `brand`: VARCHAR(30) - `devicetype`: VARCHAR(30) - `cus_id`: INT, not null, foreign key referencing `t_customer(id)` - `emp_id`: INT, not null, foreign key referencing `employee(id)` - `part_id`: INT, foreign key referencing `tech_parts(id)`### Table 3: `refurbished_item`- **Description:** Stores information about refurbished items.- **Columns:** - `id`: INT, auto-increment, primary key - `item_source`: VARCHAR(30), not null - `brand`: VARCHAR(30) - `device_type`: VARCHAR(30) - `price`: INT - `t_customer_id`: INT, not null, foreign key referencing `t_customer(id)` - `employee_id`: INT, not null, foreign key referencing `employee(id)`### Additional Information:- All tables use `InnoDB` as their storage engine.- Foreign keys ensure referential integrity between tables, linking customers and suppliers with their respective tech parts, products, and employees.

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

### Database Table Creation Scripts

The following SQL scripts are used to create three different tables in a relational database: `t_customer`, `employee`, and `supplier`. Each table is designed with specific fields and constraints to store relevant data efficiently.

---

#### `t_customer` Table

This table is designed to store customer information. The columns include:

- `id`: An integer that auto-increments to provide a unique identifier for each customer. It serves as the primary key.
- `fname`: A `VARCHAR(30)` field for the customer's first name. This field is mandatory (NOT NULL).
- `mname`: A `VARCHAR(30)` field for the customer's middle name.
- `lname`: A `VARCHAR(30)` field for the customer's last name. This field is mandatory (NOT NULL).
- `street`: A `VARCHAR(50)` field for the street address of the customer.
- `city`: A `VARCHAR(50)` field for the city of the customer.
- `st`: A `VARCHAR(10)` field for the state of the customer.
- `email`: A `VARCHAR(50)` field for the customer's email address.
- `phone`: A `VARCHAR(20)` field for the customer's phone number.

The table uses the `InnoDB` storage engine.

---

#### `employee` Table

This table is designed to store employee information. The columns are:

- `id`: An auto-incrementing integer serving as the primary key.
- `fname`: A `VARCHAR(30)` field for the employee's first name. This field is mandatory (NOT NULL).
- `mname`: A `VARCHAR(30)` field for the employee's middle name.
- `lname`: A `VARCHAR(30)` field for the employee's last name. This field is mandatory (NOT NULL).
- `street`: A `VARCHAR(50)` field for the street address of the employee.
- `city`: A `VARCHAR(50)` field for the city of the employee.
- `st`: A `VARCHAR(10)` field for the state of the employee.
- `email`: A `VARCHAR(50)` field for the employee's email address.
- `phone`: A `VARCHAR(20)` field for the employee's phone number.

The table uses the `InnoDB` storage engine.

---

#### `supplier` Table

This table is tailored to store supplier information with the following fields:

- `id`: An auto

Transcribed Image Text:The image contains SQL code for creating three tables in a database, each designed to manage different aspects of a tech and customer management system. ### Table 1: `tech_parts` - **Description:** Stores information about tech parts. - **Columns:** - `id`: INT, auto-increment, primary key - `part_number`: INT, not null - `name`: VARCHAR(30), not null - `brand`: VARCHAR(30) - `price`: INT - `bought_date`: DATE - `cus_id`: INT, foreign key referencing `t_customer(id)` - `sup_id`: INT, foreign key referencing `supplier(id)` ### Table 2: `product` - **Description:** Stores information about products. - **Columns:** - `id`: INT, auto-increment, primary key - `brand`: VARCHAR(30) - `devicetype`: VARCHAR(30) - `cus_id`: INT, not null, foreign key referencing `t_customer(id)` - `emp_id`: INT, not null, foreign key referencing `employee(id)` - `part_id`: INT, foreign key referencing `tech_parts(id)` ### Table 3: `refurbished_item` - **Description:** Stores information about refurbished items. - **Columns:** - `id`: INT, auto-increment, primary key - `item_source`: VARCHAR(30), not null - `brand`: VARCHAR(30) - `device_type`: VARCHAR(30) - `price`: INT - `t_customer_id`: INT, not null, foreign key referencing `t_customer(id)` - `employee_id`: INT, not null, foreign key referencing `employee(id)` ### Additional Information: - All tables use `InnoDB` as their storage engine. - Foreign keys ensure referential integrity between tables, linking customers and suppliers with their respective tech parts, products, and employees.

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.