SQL query help:

In figure 3.5, a travel agency schema is shown where employees are salespersons and managers (hence employee_type attribute). A manager can make sales. Salespersons are further classified into either a branch salesperson or internet salesperson (hence salesperson_type attribute). Each employee can only work in one branch. A customer can be either be a company or individual and can buy online or in-person. A customer can visit more than one branch.

Each branch has many employees but only one branch manager.

Each payment transaction has a unique ID, transaction amount, transaction method (cash, debit, or credit), and a transaction date. A customer can pay using more than one payment for the same transaction.

Note: Salesperson_ID is equal to Employee_ID if that employee is a salesperson.

Queries to do (there is no explicit data but queries should point to info asked for when data is gathered from a database):

1. Find the branch name that made sale with the highest amount

2. Find the names of customers and city they live in who have spent over $10,000

3. Find salesman who have not made any sale or have no sale in the past 5 months

4. Find the details of customers who are senior citizens (age > 65)

5. Find the customer details who have paid using credit.

Employee Employee ID First Middle Last StreetCity PIN Date_joined Date_of birth Salary Branch ID Employee type Salesperson _type Employee_phonenumber ID Phone number Customer ustomer I Customer t Date Email Street PIN Customer_phonenumber Customer l Phone number Customer Individual_Info Customer ID First Middle Last Date of Birth Customer_Company_Info Customer ID Company_name Sales Booking ID Transport_type Branch ID Destination Hotel name Departure Arrival No_of individuals Branch Branch ID Street City PIN Branch Name Email Phone_number Branch Employee_ID Branch ID Employee ID Branch_Manager Branch ID Manager ID Transaction Transaction ID Transaction date Salesperson ID Customer ID Transaction amount Payment method Figure 3.5 Employee Employee ID First Middle Last StreetCity PIN Date_joined Date_of birth Salary Branch ID Employee type Salesperson _type Employee_phonenumber ID Phone number Customer ustomer I Customer t Date Email Street PIN Customer_phonenumber Customer l Phone number Customer Individual_Info Customer ID First Middle Last Date of Birth Customer_Company_Info Customer ID Company_name Sales Booking ID Transport_type Branch ID Destination Hotel name Departure Arrival No_of individuals Branch Branch ID Street City PIN Branch Name Email Phone_number Branch Employee_ID Branch ID Employee ID Branch_Manager Branch ID Manager ID Transaction Transaction ID Transaction date Salesperson ID Customer ID Transaction amount Payment method Figure 3.5