SQL Query help: Each branch has many employees but only one branch manager. Each

Question

SQL Query help:

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:

1. Find the most visited destination

2. For each salesperson type, list the number of employees that belong to that type

3. Find the year with the maximum sale

4. Find the most common transport type taken by customers

5. Find the name of the branch that has maximum number of sales.

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

Explanation / Answer

--1. Find the most visited destination
SELECT Destination FROM Sales
GROUP BY Bookin_ID HAVING COUNT(Destination)=
(SELECT COUNT(Destination) FROM Sales
GROUP BY Bookin_ID
ORDER BY COUNT(Destination) limit 1);
--2. For each salesperson type,
-- list the number of employees that belong to that type
SELECT SalesPerson_Type,COUNT(Employee_ID) FROM Employee
GROUP BY SalesPerson_Type;
--3. Find the year with the maximum sale
SELECT TRANSACTION_Date, MAX(total_amount) FROM
(SELECT TRANSACTION_Date, SUM(Transaction_amount) AS total_amount FROM Transaction
GROUP BY YEAR(TRANSACTION_Date)) AS A

--4. Find the most common transport type taken by customers
SELECT transport_type
(SELECT TOP 1 s.transport_type,COUNT(c.customer_ID) FROM Sales s INNER JOIN Customer c
ON c.branch_id=s.branch_id
GROUP BY s.transport_type
ORDER BY COUNT(c.customer_ID) DESC) AS a

--5. Find the name of the branch that has maximum number of sales.
SELECT Branch_id,branch_name FROM Branch
WHERE count(brach_id)=(
SELECT TOP 1 COUNT(brach_id) FROM Sales GROUP BY Booking_ID ORDER BY COUNT(brach_id) DESC)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.