Query Problems

[rhuffman8]

I am fairly new to MySQL and having some problems figuring out how to format a couple queries using the following database:
branch (branch_name, branch_city, assets)
customer (customer_name, customer_street, customer_city)
account (account_number, branch_name, balance)
loan (loan_number, branch_name, account)
depositor (customer_name, account_number)
borrower (customer_name, loan_number)

I need to find all customers with accounts at a branch where a depositor names "Hayes" has an account (that would be in the depositor table, not borrower)
and the name and average balance of all customers who live in Harrison and have at least 2 accounts.
I don't know if you can help given the information I have posted here but if more is needed please let me know.
Hopefully this will be a big help in my understanding of MySQL

[abelLazm]

Please tell what are the foreign keys you are using is that ids or names. Because your given seem to have names as foreign keys if it is so then.

1-SELECT CUSTOMER_NAME, ACCOUNT_NUMBER FROM DEPOSITOR WHERE DEPOSITOR_NAME LIKE '%Hayes%'
2-your other query is very lengthy and tricky it will take time to get sorted out still trying that... which language is you using at the front end? cann't you perform these checks at front end?

[rhuffman8]

Please tell what are the foreign keys you are using is that ids or names. Because your given seem to have names as foreign keys if it is so then.

1-SELECT CUSTOMER_NAME, ACCOUNT_NUMBER FROM DEPOSITOR WHERE DEPOSITOR_NAME LIKE '%Hayes%'
2-your other query is very lengthy and tricky it will take time to get sorted out still trying that... which language is you using at the front end? cann't you perform these checks at front end?

Yes, the names are generally the foreign keys.

and I'm sorry, I'm not entirely sure I understood the last part of that. This is mostly an exercise for me learning to use MySQL. we are just writing the queries in a file using the SQL language and then running the using a

source queries.sql

command (for example) in the Linux terminal window.

[abelLazm]

Check this code it may help you here all the entities with @ are user defined variables

DECLARE  @Cutomer_ID varchar(50),@Customer_first_name varchar(50), @Customer_last_name  varchar(50); 
DECLARE @avgBlncs1 varchar(50), @countAccount1  varchar(50);
 DECLARE @avgBlncs bigint;
DECLARE @countAccount bigint;

set @avgBlncs=0
set  @countAccount=0
DECLARE contact_cursor CURSOR FOR
--SELECT customer_id, account_ID, balance FROM account
SELECT customer_id, customer_first_name, customer_last_name FROM customer

OPEN contact_cursor;

-- Perform the first fetch and store the values in variables.


FETCH NEXT FROM contact_cursor
INTO @Cutomer_ID,@Customer_first_name, @Customer_last_name;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   
set @countAccount=(select count(account_id) from Account where customer_id=@Cutomer_ID )
set @avgBlncs= (select avg(balance)as Balance  from Accounts where Customer_id=@Customer_ID 
and (select count(account_id) from Account where Customer_id=@Customer_ID  )>=2)
 set @avgBlncs1=@avgBlncs
set  @countAccount1=@countItem
-- Concatenate and display the current values in the variables.
   --PRINT 'Count: '+@Cutomer_ID +'------'+  @Account+ ' ' +  @Balance
PRINT =@Customer_ID+ ' '+@Customer_first_name+' '+@Customer_last_name;  +' Has '+@avgBlncs1+ ' in ' +  @countAccount1+' Accounts'
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM contact_cursor
 INTO @Cutomer_ID,@Customer_first_name, @Customer_last_name;
END

CLOSE contact_cursor;
DEALLOCATE contact_cursor;