human
Schema:
Accounts (account_id, customer_id, account_name, other_account_details)
Customers (customer_id, customer_first_name, customer_last_name, customer_address, customer_phone, customer_email, other_customer_details)
Customers_Cards (card_id, customer_id, card_type_code, card_number, date_valid_from, date_valid_to, other_card_details)
Financial_Transactions (transaction_id, previous_transaction_id, account_id, card_id, transaction_type, transaction_date, transaction_amount, transaction_comment, other_transaction_details)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the customer id with most number of cards, and how many does he have?
assistant
SELECT customer_id , count(*) FROM Customers_cards GROUP BY customer_id ORDER BY count(*) DESC LIMIT 1
human
Show all card type codes.
assistant
SELECT DISTINCT card_type_code FROM Customers_Cards
human
Show card type codes with at least 5 cards.
assistant
SELECT card_type_code FROM Customers_cards GROUP BY card_type_code HAVING count(*) >= 5
human
Show the number of card types.
assistant
SELECT count(DISTINCT card_type_code) FROM Customers_Cards
human
What is the card type code with most number of cards?
assistant
SELECT card_type_code FROM Customers_cards GROUP BY card_type_code ORDER BY count(*) DESC LIMIT 1
human
What are the different card types, and how many cards are there of each?
assistant
SELECT card_type_code , COUNT( * ) FROM Customers_cards GROUP BY card_type_code ;