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;