Basic Database SQL Code.

-create table
	
	CREATE TABLE tablename
	(
		studentid	char(11),
		studentname	varchar(50),
		address		varchar(50),
	)

-table definition

 PRIMARY KEY
	CREATE TABLE tablename1
	(
		studentid	char(11),
		studentname	varchar(50),

		CONSTRAINT PKstudentid PRIMARY KEY (studentid)	
	)

 FOREIGN KEY
	CREATE TABLE tablename2
	(
		studentname	varchar(50),
		grade		char(1),

		CONSTRAINT FKstudentname FOREIGN KEY (studentname)
		REFERENCE tablename
	)

-update data

	UPDATE tablename
	SET collumnname = 'value'
	WHERE rowname = 'value'

 example

	UPDATE student
	SET address = 'pangkalpinang'
	WHERE studentid = '001201200001'

-delete data

	DELETE tablename
	WHERE condition

 example

	DELETE student
	WHERE studentid = '001201200001'  /(we don't know the id number, but want to delete andree who live in pangkalpinang, and don't want the other andree be deleted)/

	DELETE student
	WHERE Address = 'PangkalPinang' AND studentname = 'Andree'

-insert data

	INSERT INTO tablename
	Values ('......','.......','......')

 example

	INSERT INTO student
	Values ('001201200004', 'Andree', 'PangkalPinang')

-show data

	SELECT *
	FROM tablename				  /show all the data/

	SELECT Collumnname1, Collumnname2
	FROM Tablename                    

	SELECT Collumnname1, Collumnname2
	FROM Tablename
	WHERE condition
	
	SELECT *
	FROM tablename
	WHERE columnname in ('condition','condition','...')

	SELECT *
	FROM tablename1 , tablename2
	WHERE tablename1.collumnname = tablename2.collumnname   /to avoid invalid data/ 

	SELECT tablename1.collumnname1, collumnname, collumnname
	FROM tablename1, tablename2
	WHERE tablename1.collumnname = tablename2.collumnname 

 example

	SELECT studentid, studentname
	FROM student
	WHERE address = 'PangkalPinang' AND studentname = 'Novi'

	SELECT *
	FROM student
	WHERE address = 'PangkalPinang'

	SELECT *
	FROM student, grade
	WHERE student.stdid = grade.stdid

	SELECT student.studentid, studentname, subjectid, grade
	FROM student, grade
	WHERE student.studentid = grade.studentid

	SELECT student.studentid, studentname, subjectname, grade
	FROM student, grade, subject
	WHERE student.studentid = grade.studentid AND subject.subjectid = grade.subjectid

	SELECT student.studentid, studentname, subjectname, grade
	FROM student, grade, subject
	WHERE student.studentid = grade.studentid AND subject.subjectid = grade.subjectid AND studentname = 'Andre'
	
	SELECT student.studentid, studentname, subjectname, grade
	FROM student, grade, subject
	WHERE student.studentid = grade.studentid AND subject.subjectid = grade.subjectid AND studentname = 'Andre' AND subjectname = 'Database System'

	SELECT studentid AS NomorIndukMhs, studentname AS NamaMahasiswa, Address AS Alamat
	FROM student

	SELECT studentid AS [Nomor Induk Mahasiswa],
	       studentname AS [Nama Mahasiswa],
	FROM student
	
	SELECT collumnname
	FROM Tablename
	WHERE Condition
	GROUP BY Collumname
		

	SELECT studentid
	FROM student
	GROUP BY address, studentid, studentname
	GROUP BY = group the table by collumname
	
AGGREGATE FUNCTION
"if we have aggregate function, then in the SELECT, we cannot have other collumnname, except the collumnname appears in GROUP BY"

-COUNT (collumnname)
 Counting how many rows in a table.
	
	SELECT COUNT(student)
	FROM student

	SELECT COUNT(student) AS [Jumlah Mahasiswa]
	FROM student
	
	SELECT address, COUNT(address) AS [Jumlah Mahasiswa]
	FROM student
	GROUP BY address

	SELECT COUNT(studentid) AS [Jumlah Mahasiswa]
	FROM grade
	WHERE subjectid = 'DB'		
	
	SELECT COUNT(studentid)	AS [Jumlah Mahasiswa]
	FROM grade, subject
	WHERE subjectName = 'Database System' and grade.subjectid = subject.subjectid


-SUM (collumnname)
 will be sum up all the number in the collumn

	SELECT studentname, SUM(credits) AS [Jumlah Kredit]
	FROM grade, student, subject
	WHERE studentname = 'Andre' and subject.subjectid = grade.subjectid and student.studentid = grade.studentid
	GROUP BY studentname
	
	
-MAX (collumnname)
 find the maximum number in the collumn

	SELECT subjectname, max(credits) as [Kredit Tertinggi]
	FROM subject
	GROUP BY subjectname, credits
	HAVING credits = MAX(credits)	
		
-MIN (collumnname)
 find the minimum number in the collumn
	
-AVERAGE (collumnname)