Myanimeanalysis

use ngampus;

select * from kuliah
select * from matakuliah
select * from peserta
select * from mahasiswa
select * from dosen

/* 1 inner join */
SELECT DSN.nip, DSN.nama, KLH.semester
FROM dosen DSN
INNER JOIN kuliah KLH
ON DSN.nip = KLH.nip;

/* 2 outer join */
SELECT DSN.nip, DSN.nama, KLH.semester
FROM dosen DSN, kuliah KLH
WHERE DSN.nip = KLH.nip;

/*3. left outer join */
SELECT DSN.nip, DSN.nama, KLH.semester
FROM dosen DSN
LEFT OUTER JOIN kuliah KLH
ON DSN.nip = KLH.nip;

/*4. cross join */
select DSN.nip, DSN.nama, KLH.semester
from dosen DSN, kuliah KLH;

/*5. SELECT UNIQUE */
select distinct thnakademik from kuliah;


/*6. ROUND FUNCTUION */

create table student_rating(
studentid int primary key,
first_name char(20),
rating float
)

insert into student_rating values(1,'Jenny',85.235)
insert into student_rating values(2,'Bob',95.25)
insert into student_rating values(3,'Alice',3.9)
insert into student_rating values(4,'James',120.1)

SELECT first_Name, ROUND (rating, 1) rounded_score FROM student_rating;

SELECT First_Name, ROUND(Rating, -1) Rounded_Score FROM Student_Rating;


alter table student_rating add score float;
update student_rating set score=85.235 where studentid=1;
update student_rating set score=95.25 where studentid=2;
update student_rating set score=3.9 where studentid=3;
update student_rating set score=120.1 where studentid=4;

/* 7.CAST Function */
SELECT First_Name, CAST(Score AS Integer) Int_Score FROM Student_Rating;
SELECT First_Name, CAST(Score AS float) Char_Score FROM Student_Rating;

/* 8.CONVERT Function */
SELECT First_Name, CONVERT(integer, score) Int_Score FROM Student_Rating;

/* 9.CONVERT Function */
SELECT {fn CONCAT(kd_kl, kd_nik)} as Kodekode  FROM kuliah;

/* 10.SUBSTRING */
SELECT SUBSTRING (nama, 6, 7) FROM dosen WHERE nama = 'Firdaus';

/* 11.INSTR Function */
SELECT CHARINDEX(nama,'r') FROM dosen WHERE nama = 'Firdaus';

/* 12.TRIM */
SELECT TRIM(' Sample ');

/* 13.Length Function */
SELECT DataLength (nama) FROM dosen WHERE nama = 'Fauzan';

/* 14.Replace Function */
SELECT REPLACE (nama, 'ziz', 'anjas') nama FROM mahasiswa;