Question
SQL query help: In figure 3.5, a travel agency shema is shown where employees are salespersons and managers (hence employee_type attribute). A manager can make
SQL query help:
In figure 3.5, a travel agency shema 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 retrieve info asked for as if they had access to data from a database):
1. Find the salaries of the top 3 salesmen across all branches
2. List all the sales that have been made after the most current employee was hired
3. Find the name of customers who have visited the travel agency but have not made any purchases
4. Find the top 5 salesmen with the maximum number of sales
5. Find the list of customers who have made more than one purchases but from different branches
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.5Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started