Please Help to write SQL queries for the following relational database schemas!! Thanks in advance!!
What have you tried?
What didn't work?
Hi Bitsmed, i made this queries, what do you think about?
A) SELECT COUNT(customer.id) AS customers_with_min_one_communication
FROM customer
INNER JOIN communication_channel
ON customer.id=communication_channel.customer_id
**************if there is customer without communication_id in COMMUNICATION_CHANNEL table
SELECT COUNT(DISTINCT customer_id)
FROM communication_channel
WHERE communication_id IS NOT NULL;
B) SELECT COUNT(customer_id) AS
FROM communication_channel
GROUP BY customer_id
HAVING COUNT(communication_id)=3;
C)SELECT customer.name, customer.addresses, COUNT(bank_account.account_type) AS total_accounts
FROM customer
INNER JOIN bank_account
ON customer.id=bank_account.customer_id
GROUP BY customer.name
HAVING COUNT(bank_account.account_type)=2
D)SELECT customer.id
FROM customer
INNER JOIN bank_account
ON customer.id=bank_account.cutomer_id
WHERE bank_account.account_type LIKE 'N'
E)SELECT id FROM communication WHERE name LIKE 'SMS_ALERT';
INSERT INTO communication_channel (customer_id, communication_id)
VALUES(47, id of SMS_ALERT);
A) Perfect. You should use "count(distinct customer.id)" on the first query to avoid counting same customer more than once.
B) As I read the question, you should show each communication name and the number of assigned customers. Not customers who are assigned to all communication forms. You should join to the communication table and then group by communication.[name]. In your select section and having section you should use "count(distinct customer_id)" to avoid counting same customer more than once.
C) You should use "count(distinct back_account.account_type)" in the having section to ensure you're counting only different account types. You are referencing field addresses which is not present in customer table (at least not in the picture you posted), so you should reference the correct fields. Also, when using group by, you should list all fields the you are NOT performing aggregation on (like count or sum).
D) In your where section, you filter on false field. You should be using bank_account.active instead of bank_account.account_type. Also if you filter on the correct field, you'll just get inactive bank accounts (what about active ones?). You could remove the join section and replace the where section with "where not exists (select 1 from bank_account where bank_account.customer_id=customer.id and bank_account.active='Y')" - this way you'd get all customers how doesn't have an account thats active (not exists).
E) This would work, but you'd probably surpossed to do it in one go:
insert into communication_channel (customer_id,communication_id)
select 47
,id
from communication
where [name]='SMS_ALERTS'
;
All in all, it looks like you're getting it - good job.
1 Like
Thank you very much for your help!