Built-In Functions
Subtopic: String Manipulation Magic
1. Which function returns the length of a string?
a) LEN() / LENGTH()
b) SIZE()
c) COUNT()
d) STRLEN()
Answer: a) LEN() / LENGTH()
2. Which function converts text to lowercase?
a) LCASE() / LOWER()
b) TOLOW()
c) CASELOW()
d) DOWNCASE()
Answer: a) LCASE() / LOWER()
3. Which function converts text to uppercase?
a) UPCASE()
b) UPPER() / UCASE()
c) CAPITAL()
d) TOUP()
Answer: b) UPPER() / UCASE()
4. SUBSTRING('Database', 2, 3) returns:
a) Dat
b) ata
c) tab
d) ase
Answer: c) tab
5. TRIM() is used to:
a) Remove spaces from both ends
b) Remove characters from middle
c) Cut string into pieces
d) Replace substring
Answer: a) Remove spaces from both ends
6. Which function joins two strings?
a) CONCAT()
b) ADD()
c) MERGE()
d) UNION()
Answer: a) CONCAT()
7. Function REPLACE('SQL','S','M') returns:
a) MQL
b) MSL
c) SML
d) SQL
Answer: a) MQL
8. Which function finds position of substring?
a) POSITION() / INSTR()
b) LOCATE()
c) FIND()
d) All of these (depends on DBMS)
Answer: d) All of these
9. Which function pads a string on the left?
a) LPAD()
b) RPAD()
c) LTRIM()
d) PREPEND()
Answer: a) LPAD()
10. RIGHT('Database', 4) returns:
a) Daba
b) base
c) data
d) tab
Answer: b) base
Subtopic: Unlocking Math Prowess
11. Which function returns absolute value?
a) ABS()
b) SQRT()
c) SIGN()
d) ROUND()
Answer: a) ABS()
12. POWER(2,3) returns:
a) 6
b) 8
c) 9
d) 12
Answer: b) 8
13. SQRT(25) returns:
a) 4
b) 5
c) 6
d) Error
Answer: b) 5
14. MOD(17,5) returns:
a) 2
b) 3
c) 4
d) 5
Answer: b) 2
15. ROUND(15.567,2) returns:
a) 15.56
b) 15.57
c) 15.5
d) 16
Answer: b) 15.57
16. CEIL(10.2) returns:
a) 10
b) 11
c) 9
d) 12
Answer: b) 11
17. FLOOR(10.9) returns:
a) 10
b) 11
c) 9
d) 12
Answer: a) 10
18. SIGN(-25) returns:
a) 0
b) -1
c) 1
d) NULL
Answer: b) -1
19. Which returns random value?
a) RAND()
b) RANDOM()
c) NEWID()
d) All of these (DBMS dependent)
Answer: d) All of these
20. LOG(100) (base e) returns approx:
a) 1
b) 2
c) 4.6
d) 10
Answer: c) 4.6
Subtopic: Mastering Dates and Times
21. Which function returns current date?
a) GETDATE()
b) CURRENT_DATE
c) SYSDATE
d) All of these (DBMS dependent)
Answer: d) All of these
22. Which function returns current timestamp?
a) NOW()
b) CURRENT_TIMESTAMP
c) LOCALTIMESTAMP
d) All of these
Answer: d) All of these
23. Function EXTRACT(YEAR FROM '2025-09-20') returns:
a) 25
b) 20
c) 2025
d) 09
Answer: c) 2025
24. DATEADD(YEAR, 2, '2020-01-01') returns:
a) 2022-01-01
b) 2020-03-01
c) 2020-01-03
d) 2021-01-01
Answer: a) 2022-01-01
25. DATEDIFF(DAY, '2025-01-01', '2025-01-10') returns:
a) 8
b) 9
c) 10
d) 11
Answer: b) 9
26. Which function extracts month name?
a) MONTHNAME()
b) TO_CHAR(date, 'Month')
c) DATENAME(month, date)
d) All of these
Answer: d) All of these
27. DAY('2025-09-20') returns:
a) 20
b) 09
c) 2025
d) 1
Answer: a) 20
28. Which function returns only time part?
a) TIME()
b) CAST(date AS TIME)
c) EXTRACT(HOUR FROM …)
d) All of these
Answer: d) All of these
29. Which operator adds interval to date?
a) + INTERVAL
b) ADD INTERVAL
c) DATEADD()
d) All of these (depends on DBMS)
Answer: d) All of these
30. Which function converts string to date?
a) STR_TO_DATE()
b) TO_DATE()
c) CAST()
d) All of these (depends on DBMS)
Answer: d) All of these
Subtopic: Crunching Numbers with SQL
31. Which function counts rows including NULL?
a) COUNT()
b) COUNT(col)
c) SUM()
d) MAX()
**Answer: a) COUNT()**
32. COUNT(col) ignores:
a) Zero
b) Negative values
c) NULLs
d) Duplicates
Answer: c) NULLs
33. SUM(col) returns:
a) Total sum of values
b) Average of values
c) Max value
d) Count of values
Answer: a) Total sum of values
34. AVG(col) ignores:
a) Negative numbers
b) NULLs
c) Decimal points
d) Duplicates
Answer: b) NULLs
35. MAX(col) returns:
a) Highest value
b) Lowest value
c) Median value
d) Average
Answer: a) Highest value
36. MIN(col) returns:
a) Highest value
b) Lowest value
c) First value
d) Null value
Answer: b) Lowest value
37. Which is true about aggregate functions?
a) They return single value
b) They ignore NULLs (except COUNT*)
c) Used with GROUP BY
d) All of these
Answer: d) All of these
38. Which operator is used with aggregates to filter groups?
a) WHERE
b) HAVING
c) GROUP BY
d) ORDER BY
Answer: b) HAVING
39. Which query finds 2nd highest salary?
a) MAX(salary) WHERE salary < MAX(salary)
b) TOP 2 salary ORDER BY salary DESC
c) Both a & b (DBMS dependent)
d) None
Answer: c) Both a & b (DBMS dependent)
40. Which function finds median directly?
a) MEDIAN() (Oracle, etc.)
b) PERCENTILE_CONT()
c) Not available in all DBMS
d) All of these
Answer: d) All of these
Subtopic: Discovering SQL Trends
41. Which keyword finds top values?
a) MAX()
b) TOP / LIMIT
c) ORDER BY + LIMIT
d) Both b & c
Answer: d) Both b & c
42. Which function calculates running total?
a) SUM() OVER(ORDER BY …)
b) RANK()
c) ROW_NUMBER()
d) AVG()
Answer: a) SUM() OVER(ORDER BY …)
43. Which function calculates moving average?
a) AVG() OVER(ORDER BY … ROWS …)
b) PERCENTILE_CONT()
c) NTILE()
d) None
Answer: a) AVG() OVER(ORDER BY … ROWS …)
44. Which keyword is used to rank data?
a) RANK()
b) DENSE_RANK()
c) ROW_NUMBER()
d) All of these
Answer: d) All of these
45. Which function finds percentage contribution?
a) (col/SUM(col))*100 with OVER()
b) NTILE()
c) COALESCE()
d) None
*Answer: a) (col/SUM(col))100 with OVER()
46. Which function finds trend across categories?
a) GROUP BY with aggregates
b) CUBE / ROLLUP
c) Both a & b
d) None
Answer: c) Both a & b
47. Which clause helps discover year-wise trend?
a) GROUP BY YEAR(date)
b) PARTITION BY YEAR(date)
c) Both a & b
d) None
Answer: a) GROUP BY YEAR(date)
48. Which aggregate helps find growth rate?
a) SUM()
b) LAG() + LEAD()
c) COUNT()
d) MAX()
Answer: b) LAG() + LEAD()
49. Which function finds previous row value?
a) LAG()
b) LEAD()
c) NTILE()
d) ROW_NUMBER()
Answer: a) LAG()
50. Which function finds next row value?
a) LAG()
b) LEAD()
c) RANK()
d) ROW_NUMBER()
Answer: b) LEAD()