Modern daily life is almost inconceivable without databases. Online transactions, course catalogs, search engines, etc all use databases. We depend on databases for many aspects of our lives. It is important that these databases are designed and implemented correctly. With the increase of the Internet of Things and growth of online transactions database design, implementation, and management has many employment opportunities. This project walks through the process of design, development, and implementation of a relational database.

Scenario

Design a database system using ER diagrams and normalization. Create the database in a Relational Database Management System using SQL and create queries to retrieve data from the database.

Part I: Tables Created: Screenshot of tables below:

select * from customer;

select * from invoice;

select * from line;

select * from product;

select * from vendor;

PART II: Single table queries screenshots

Problem 1: Write the SQL code required to list the product code, description, and price from the product table. Take a screenshot of your results

Problem 2: Modify the query above to only show those products that cost more than 20. Take a screenshot of your results.

Problem 3: Modify the query above to show the results with price descending. Take a screenshot of your results.

Problem 4: Modify the query above to show products that cost more than 20 and less than 100. Take a screenshot of your results.

Problem 5: Write the SQL code required to list the first name and last name of all customers whose middle initial is G. Take a screenshot of your results (hint you should get two names)

Problem 6: Write the SQL code required to list the code, first name, last name, and balance of all customers whose last names begin with O. Take a screenshot of your results (hint: you should get 3 names)

Problem 7: Write the SQL code to list only distinct p_codes from the line table