SQL - STRING MGMT FUNCTIONS - SUBSTR, INSTR, REPLACE, LOWER, UPPER AND MIN, MAX, AVG FUNC - Day15

@LearnWithRD0529 ------------------ scripts - ----------------- CREATE TABLE Account ( account_id INT PRIMARY KEY, account_number VARCHAR(20) NOT NULL, account_name VARCHAR(255), balance DECIMAL(10, 2), account_type VARCHAR(50) ); -- Inserting sample account data INSERT INTO Account (account_id, account_number, account_name, balance, account_type) VALUES(1, 'ACC123456', 'John Doe', 1000.00, 'Savings'); INSERT INTO Account (account_id, account_number, account_name, balance, account_type) VALUES(2, 'ACC789012', 'Jane Smith', 2500.50, 'Checking'); INSERT INTO Account (account_id, account_number, account_name, balance, account_type) VALUES(3, 'ACC345678', 'Bob Johnson', 500.75, 'Savings'); INSERT INTO Account (account_id, account_number, account_name, balance, account_type) VALUES(4, 'ACC901234', 'Alice Brown', 3500.25, 'Checking'); INSERT INTO Account (account_id, account_number, account_name, balance, account_type) VALUES(5, 'ACC567890', 'Eve Wilson', 200.00, 'Savings'); INSERT INTO Account (account_id, account_number, account_name, balance, account_type) VALUES(6, 'ACC123123', 'Charlie Davis', 1800.60, 'Checking'); INSERT INTO Account (account_id, account_number, account_name, balance, account_type) VALUES(7, 'ACC456456', 'Grace Lee', 3000.75, 'Savings'); INSERT INTO Account (account_id, account_number, account_name, balance, account_type) VALUES(8, 'ACC789789', 'David White', 150.25, 'Checking'); INSERT INTO Account (account_id, account_number, account_name, balance, account_type) VALUES(9, 'ACC987654', 'Sarah Johnson', 900.00, 'Savings'); INSERT INTO Account (account_id, account_number, account_name, balance, account_type) VALUES(10, 'ACC321321', 'Michael Smith', 4500.50, 'Checking'); ----------------------------------------------------- queries: ----------- SELECT MIN(BALANCE) MIN_BALANCE,MAX(BALANCE) MAX_BALANCE,AVG(BALANCE) AVG_BALANCE FROM ACCOUNT; SELECT ACCOUNT_TYPE,MIN(BALANCE),MAX(BALANCE),AVG(BALANCE) FROM ACCOUNT GROUP BY ACCOUNT_TYPE; SELECT ACCOUNT_NAME,LOWER(ACCOUNT_NAME) FROM ACCOUNT; SELECT ACCOUNT_NAME,UPPER(ACCOUNT_NAME) FROM ACCOUNT; SELECT ACCOUNT_NAME,ACCOUNT_NUMBER,LENGTH(ACCOUNT_NAME),LENGTH(ACCOUNT_NUMBER) AC_NUMBR_LEN FROM ACCOUNT; SELECT LENGTH('LEARN WITH RD') FROM DUAL; SELECT ACCOUNT_NAME,REPLACE(ACCOUNT_NAME,' ','_') FROM ACCOUNT; SELECT ACCOUNT_NAME,REPLACE(ACCOUNT_NAME,'J','S') FROM ACCOUNT WHERE ACCOUNT_NAME IN ('John Doe','Jane Smith'); SELECT ACCOUNT_NAME,SUBSTR(ACCOUNT_NAME,6,4) FROM ACCOUNT; SELECT ACCOUNT_NAME,SUBSTR(ACCOUNT_NAME,6) FROM ACCOUNT; SELECT ACCOUNT_NAME,INSTR(ACCOUNT_NAME,' ',1,1) FROM ACCOUNT; SELECT INSTR('LEARN WITH RD',' ',1,1) FROM DUAL; SELECT INSTR('LEARN WITH RD','R',1,2) FROM DUAL; --eXTRACT ONLY THE SURNAME FROM ACCOUNT_NAME SELECT * FROM ACCOUNT; SELECT ACCOUNT_NAME,SUBSTR(ACCOUNT_NAME,INSTR(ACCOUNT_NAME,' ',1,1)+1) FROM ACCOUNT;