/Famous-People

This project contains sample SQL commands that create different tables and insert values into them. It also includes various join queries, including INNER JOIN and LEFT OUTER JOIN, to get the results from multiple tables.

MIT LicenseMIT

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.