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)
