Famous-People

Introduction

This project is a simple demonstration of how to create, manipulate and query a PostgreSQL database using SQL. The database contains 3 tables; persons, hobbies and friends and a second database with 2 tables; customers and orders.

This project sought to test the understanding of all the JOINS available in SQL as well as altering and updating a table. The JOINS used in this project include:

• self join
• implicit join
• explicit join
• cross join
• left outer join
• inner join
• full join

Schema

Persons

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
full_name	VARCHAR(20)	NOT NULL
age	INTEGER	NOT NULL

Hobbies

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
person_id	INTEGER	NOT NULL
game	VARCHAR(20)	NOT NULL

Friends

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
person1_id	INTEGER	NOT NULL
person2_id	INTEGER	NOT NULL

Customers

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
name	VARCHAR(20)	NOT NULL
email	TEXT	NOT NULL

Orders

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
customer_id	INTEGER	NOT NULL
item	TEXT	NOT NULL
price	REAL	NOT NULL

Requirements

To run this project, you need to have the following installed on your machine:

• PostgreSQL database
• An SQL client or PostgreSQL command line interface (CLI)

Usage

The following SQL queries can be used to query the tables:

• Quering all the data from the persons, hobbies, friends, customers, and orders tables respectively.
• Quering data from both persons and hobbies tables, joining them on the person_id field to show each person's name and their hobbies.
• Quering data for the name and hobbies of a specific person with age 12.
• Quering data for each friend's name with their corresponding friend's name.
• Quering data for all customers and their orders. Use a left outer join to include customers that have made no orders.
• Quering the name, email, and total amount spent by each customer in the orders table. The results are sorted in descending order according to the total amount spent.

Project Recommendation

This project is a great opportunity for anyone looking to learn about database design and SQL. Whether you're a beginner or an experienced database administrator, you will find this project both challenging and rewarding. With a comprehensive design and well-documented code, this project is easy to understand and customize to your needs.