Relational database Write the SQL query for each of the following tasks 1. Find
ID: 3698642 • Letter: R
Question
Relational databaseWrite the SQL query for each of the following tasks 1. Find all clients who are located in the city Austin. Include the Client Number, Client Name, and City in the query result 2. Find all clients located in North Carolina(NC) with an amount paid grearter than $3,000.00. Include the Client Number, Client Name, and Amount Paid fields in the query result. Find all clients whose names begin with the letter W. Include the Client Number, Client Name, and City fields in the query result. 4. List all cities in descending order. Each city should appear only once. 5. Display the Client Number, Client Name, Service Rep Number, First Name and Last Name for all clients. Sort the result in ascending order by service rep number and then by client number 6. List the average of Balance Due amount grouped by Service Rep Number. Name the average of Balance Due as Average Balance. Sort the result in ascending order by service rep number. Find the Client Number, Client Name, and City for every pair of clients who are located in the same city. No repeating pairs are allowed Find the Client Number, Client Name, and Service Rep Number for all clients that have service requests. Use the alias S for the Service Requests table and C for the Client table. Each client should appear only once in the result 7. 8. 9. Use a subquery to find all service reps whose clients are located in Georgetown. 10. Find the average balance due amount for service rep 52. Include the Service Rep Number, First Name, and Last Name in the query result
Explanation / Answer
Q1) SELECT Client_Number, Client_Name, City from Client where City = 'Austin';
Q2) SELECT Client_Number, Client_Name, Amount_Paid from Client where State = 'North Carolina(NC)' AND Amount_Paid > 3000.00;
Q3) SELECT Client_Number, Client_Name, City from Client where Client_Name LIKE 'W%';
Q4) SELECT DISTINCT City FROM Client Order by City DESC;
Q5) SELECT client_Name, Client_Number, Service_Rep_Number, First_Name, Last_Name
from Client JOIN Service_Rep
ON Client.Service_Rep_Number = Service_Rep.Service_Rep_Number
ORDER BY Service_Rep_Number, Client_Number ASC;
Q6) SELECT Service_Rep_Number, AVG(Balance_Due) AS 'Average Balance' from Client GROUP BY Service_Rep_Number ORDER BY Service_Rep_Number ASC;
Q7) Select C1.Client_Number, C1.Client_Name, C1.City, C2.Client_Number, C2.Client_Name, C2.City from Client C1, Client C2 where C1.City = C2.City AND C1.Client_Number <> C2.Client_Number;
Q8) SELECT DISTINCT C.Client_Number, C.Client_Name, C.Service_Rep_Number from Client C, Service_Request S WHERE C.Client_Number = S.Client_Number;
Please let me know in case of any clarifications required. Thanks!