Part ACreate a database called AWESOME and initialize it with the following schema:Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real)The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included)Part B Write the following queries in SQL:Find the pnames of parts for which there is some supplier.Find the snames of suppliers who supply every part.Find the snames of suppliers who supply every red part.Find the pnames of parts supplied by Acme Widget Suppliers and no one else.Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part).For each part, ﬁnd the sname of the supplier who charges the most for that part.Find the sids of suppliers who supply only red parts.Find the sids of suppliers who supply a red part and a green part.Find the sids of suppliers who supply a red part or a green part.For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies.For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.

SQL: SQL stands for Structured Query Language. It is uses to access and manipulate the databases.…

Answered: Create a database called AWESOME and…

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter7: Introduction To Structured Query Language (sql)

Section: Chapter Questions

Problem 13P: Write the two SQL command sequences required to: a.Create a temporary table named TEMP_1 whose...

See similar textbooks

Part A

Create a database called AWESOME and initialize it with the following schema:

Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)

The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included)

Part B

Write the following queries in SQL:

Find the pnames of parts for which there is some supplier.
Find the snames of suppliers who supply every part.
Find the snames of suppliers who supply every red part.
Find the pnames of parts supplied by Acme Widget Suppliers and no one else.
Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part).
For each part, ﬁnd the sname of the supplier who charges the most for that part.
Find the sids of suppliers who supply only red parts.
Find the sids of suppliers who supply a red part and a green part.
Find the sids of suppliers who supply a red part or a green part.
For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies.
For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 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.

Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real)

Part A

Part B