← SQL roadmap

Topic 40 of 52 · Advanced

Functions Reference: System, Info & Security

Session and metadata functions, IDs, hashing/encryption helpers, locks and stored routines.

Concept

Information functions tell you about the session and the last statement: the current user, database, version, inserted id and affected rows.

Hashing (MD5, SHA2) is one-way and used for checksums; AES_ENCRYPT is reversible encryption with a key. Never store passwords with MD5 — hash them with bcrypt/argon2 in the application.

You can also write your own functions and procedures with CREATE FUNCTION / CREATE PROCEDURE and call them like built-ins.

Cheat sheet

Function / commandWhat it doesExample → result
DATABASE()Current databaseDATABASE() → 'campus'
USER() / CURRENT_USER()Login user / user used for privilege checksCURRENT_USER() → 'app@%'
VERSION()Server versionVERSION() → '8.0.36'
CONNECTION_ID()Session idCONNECTION_ID() → 42
LAST_INSERT_ID()Last AUTO_INCREMENT value generated in this sessionLAST_INSERT_ID() → 101
ROW_COUNT()Rows changed by the previous statementROW_COUNT() → 3
FOUND_ROWS()Rows found by the previous SELECT (deprecated)FOUND_ROWS()
UUID() / UUID_TO_BIN / BIN_TO_UUIDUnique id / compact 16-byte storageUUID() → '3f06af63-...'
MD5(s) / SHA1(s) / SHA2(s, 256)One-way hashesSHA2('abc',256) → 'ba7816bf...'
AES_ENCRYPT(s, key) / AES_DECRYPTReversible encryptionAES_DECRYPT(AES_ENCRYPT('x','k'),'k') → 'x'
TO_BASE64 / FROM_BASE64Base64 encode / decodeTO_BASE64('sql') → 'c3Fs'
RANDOM_BYTES(n)Cryptographically random bytesHEX(RANDOM_BYTES(4))
SLEEP(n)Pause n seconds (testing)SLEEP(1) → 0
GET_LOCK / RELEASE_LOCKNamed application lockGET_LOCK('payroll', 10) → 1
BENCHMARK(n, expr)Times an expressionBENCHMARK(1000000, MD5('a'))
SHOW TABLES / DESCRIBE t / SHOW CREATE TABLE tInspect schemaDESCRIBE students;
EXPLAIN / EXPLAIN ANALYZEQuery plan / actual timingEXPLAIN SELECT * FROM students WHERE id = 1;
information_schema.COLUMNSMetadata about every columnSELECT column_name FROM information_schema.columns WHERE table_name='students';
CREATE FUNCTIONUser-defined scalar functionCREATE FUNCTION grade(m INT) RETURNS CHAR(1) DETERMINISTIC RETURN IF(m>=60,'P','F');
CREATE PROCEDURE / CALLStored procedureCALL monthly_report(2026, 11);

Code (SQL · MySQL 8)

-- Insert and read back the new id
INSERT INTO students (name, dept_id) VALUES ('Kiran', 1);
SELECT LAST_INSERT_ID() AS new_id, ROW_COUNT() AS rows_added;

-- Session info
SELECT DATABASE(), CURRENT_USER(), VERSION(), CONNECTION_ID();

-- A user-defined function used like a built-in
DELIMITER //
CREATE FUNCTION grade_of(m DECIMAL(5,2)) RETURNS VARCHAR(12) DETERMINISTIC
BEGIN
  RETURN CASE WHEN m >= 75 THEN 'Distinction'
              WHEN m >= 60 THEN 'First'
              WHEN m >= 35 THEN 'Pass'
              ELSE 'Fail' END;
END //
DELIMITER ;

SELECT name, grade_of(marks) AS grade, SHA2(email, 256) AS email_hash
FROM students;

Remember

  • LAST_INSERT_ID() is per connection — safe under concurrency.
  • Hashes are one-way; encryption is reversible with the key.
  • Use EXPLAIN to check if your query uses an index.
  • Mark functions DETERMINISTIC only when the same input always gives the same output.

Interview questions

Is LAST_INSERT_ID() affected by other users' inserts?

No. It returns the last auto-generated id from the current connection only.

Function vs stored procedure?

A function returns a single value and can be used inside SELECT; a procedure is invoked with CALL, can return result sets and OUT parameters, and can run transactions.

Why not store passwords with MD5 or SHA2 in SQL?

They are fast, unsalted hashes that are easy to brute force; use slow salted algorithms like bcrypt or argon2 in the application layer.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Functions Reference: System, Info & Security” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.