5. Fundamental SQL Using FUNCTION and GROUP BY - anggita-dian/DQLab GitHub Wiki
1. Fungsi di SQL
Fungsi Scalar vs Fungsi Aggregate
Fungsi Skalar Matematika
Fungsi Skalar Matematika - ABS()
SELECT StudentID, FirstName, LastName, Semester1, Semester2, ABS(MarkGrowth) AS MarkGrowth
FROM students;
Fungsi Skalar Matematika - CEILING()
SELECT StudentID, FirstName, LastName, CEILING(Semester1) as Semester1, CEILING(Semester2) as Semester2, MarkGrowth
FROM students;
Fungsi Skalar Matematika - FLOOR()
SELECT StudentID, FirstName, LastName, FLOOR(Semester1) as Semester1, FLOOR(Semester2) as Semester2, MarkGrowth
FROM students;
Fungsi Skalar Matematika - ROUND()
SELECT StudentID, FirstName, LastName, ROUND(Semester1, 1) as Semester1, ROUND(Semester2, 0) as Semester2, MarkGrowth
FROM students;
Fungsi Skalar Matematika - SQRT( )
SELECT StudentID, FirstName, LastName, SQRT(Semester1) as Semester1, Semester2, MarkGrowth
FROM students;
Tugas Praktek
SELECT StudentID, FirstName, LastName, MOD(Semester1, 2) as Semester1, Semester2, EXP(MarkGrowth)
FROM students;
2. Fungsi Text di SQL
Fungsi Text
Fungsi Text - CONCAT( )
SELECT StudentID, CONCAT(FirstName, LastName) as Name, Semester1, Semester2, MarkGrowth
FROM students;
Fungsi Text - SUBSTRING_INDEX( )
SELECT StudentID, SUBSTRING_INDEX(Email,'@',1) as Name
FROM students;
Fungsi Text - SUBSTR( )
SELECT StudentID, SUBSTR(FirstName, 2, 3) as Initial
FROM students;
Fungsi Text - LENGTH( )
SELECT StudentID, FirstName, LENGTH(FirstName) as Total_char
FROM students;
Fungsi Text - REPLACE( )
SELECT StudentID, Email, REPLACE(Email, 'yahoo', 'gmail') as New_Email
FROM students;
Tugas Praktek
SELECT StudentID, UPPER(FirstName) as FirstName, LOWER(LastName) as LastName
FROM students;
3. Fungsi Aggregate dan Group By
Fungsi Aggregate
Fungsi Aggregate - SUM()
SELECT SUM(Semester1) as Total_1, SUM(Semester2) as Total_2
FROM students;
Fungsi Aggregate - COUNT()
SELECT COUNT(FirstName) as Total_Student
FROM students;
Fungsi Aggregate - AVG( )
SELECT AVG(Semester1) as AVG_1, AVG(Semester2) as AVG_2
FROM students;
Tugas Praktek
SELECT MIN(Semester1) as Min1, MAX(Semester1) as Max1, MIN(Semester2) as Min2, MAX(Semester2) as Max2
FROM students;