CRUD Pada PostgreSQL

 

Berikut adalah CRUD  pada PostgreSQL :

1. Insert

Format :

INSERT INTO TABLE_NAME   

(column1,   column2, column3, ……columnN)    VALUES (value1, value2, value3, ….. valueN);

1.1. Insert Single

Format :

INSERT INTO table  (column1, column2, ... )  DEFAULT VALUES;

Contoh :

INSERT INTO myschema."Student"(  

"St_id", "St_Name", "St_age", "St_address", "St_blood_group")  

1.2. Insert Multi Record

Format :

INSERT INTO table_name  (column1, column2, ... )  

SELECT expression1, expression2, ...  FROM source_table  [WHERE conditions];

Contoh :

INSERT INTO myschema."Student"(  

"St_id", "St_Name", "St_age", "St_address", "St_blood_group")  

VALUES(101, 'John', 24, 'New York', 'A+')  

(102, 'Mike', 22, 'Chicago', 'B-'),  

(103, 'Emily', 24, 'Boston', 'A-'),  

(104, 'James', 20, 'Philadelphia', 'O+'),  

(105, 'Sophia', 21, 'New York', 'B+'); 

2. Select

Format : SELECT select_list  FROM table_name;   

Contoh :

SELECT column1, column2,  ……columnN  FROM table_name;

3. Update

Format :

UPDATE table_name    SET column1 = value1, column2 = value2....,   

columnN = valueN   WHERE condition;

Contoh :

UPDATE department  SET last_update = DEFAULT  

WHERE  last_update IS NULL;

4. Delete

Format : 

DELETE FROM table_name   WHERE [condition];

DELETE FROM table USING another_table  WHERE table.id = another_table.id AND

DELETE FROM table  WHERE table.id = (SELECT id FROM another_table);

Contoh :

DELETE FROM department  WHERE dept_id = 6; 

CRUD MongoDB Pada Node.js

 

1. Insert 

Format : (Insert Single record)

1. Insert Single Record

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var myobj = { name: "Kumar", age: "28", address: "India" };  

db.collection("employees").insertOne(myobj, function(err, res) {  

if (err) throw err;  

console.log("1 record inserted");  

db.close();  

});  

});

2. Insert Multi Record

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var myobj = [     

{ name: "Mahesh Sharma", age: "23", address: "Ghaziabad"},  

{ name: "Tom Moody", age: "43", address: "CA"},  

{ name: "Zahira Abdul", age: "18", address: "Islamabad"},  

{ name: "John Ross", age: "33", address: "London"}  

];  

db.collection("customers").insert(myobj, function(err, res) {  

if (err) throw err;  

console.log("Number of records inserted: " + res.insertedCount);  

db.close();  

});  

});

2. Select

2.1. Select Single Record

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/MongoDb";  

MongoClient.connect(url, function(err, db) {  

  if (err) throw err;  

  db.collection("employees").findOne({}, function(err, result) {  

    if (err) throw err;  

    console.log(result.name);  

    db.close();  

  });  

});

2.2. Select Multi Record

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/MongoDb";  

MongoClient.connect(url, function(err, db) {  

  if (err) throw err;  

  db.collection("employees").find({}).toArray(function(err, result) {  

    if (err) throw err;  

    console.log(result);  

    db.close();  

  });  

});

2.3. Select Condition dengan sama dengan ( = )

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var query = { address: "Delhi" };  

db.collection("employees").find(query).toArray(function(err, result) {  

if (err) throw err;  

console.log(result);  

db.close();  

});  

});

2.4. Select Condition dengan Start / Like

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var query = { address: /^L/ };  

db.collection("employees").find(query).toArray(function(err, result) {  

if (err) throw err;  

console.log(result);  

db.close();  

});  

});

2.5. Sorting /Filter

2.5.1. Sort Ascending

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var mysort = { name: 1 };  

db.collection("employees").find().sort(mysort).toArray(function(err, result) {  

if (err) throw err;  

console.log(result);  

db.close();  

});  

});

2.5.2. Sort Descending

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var mysort = { name: -1 };  

db.collection("employees").find().sort(mysort).toArray(function(err, result) {  

if (err) throw err;  

console.log(result);  

db.close();  

});  

});

3. Remove

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var myquery = { address: 'London' };  

db.collection("employees").remove(myquery, function(err, obj) {  

if (err) throw err;  

console.log(obj.result.n + " record(s) deleted");  

db.close();  

});  

});

Koneksi Databasse MongoDB Pada Node.js

 

1. Create Connection

1.1. Download MongoDB : apt-get install mongodb

1.2. Install MongoDB :  npm install mongodb --save

1.3. start MongoDb services : service mongodb start 

2. Create Database

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

console.log("Database created!");  

db.close();  

});

3. Create Collection

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

db.createCollection("employees", function(err, res) {  

if (err) throw err;  

console.log("Collection is created!");  

db.close();  

});  

});

CRUD MySQL Pada Node.js

 

1. Insert Record

1.1. Insert Single

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql" 

});  

con.connect(function(err) {  

if (err) throw err;  

console.log("Connected!");  

var sql = "Insert Inyo employees (id, name, age, city) Values ('1', 'Ajeet Kumar', '27', 'Allahabad')";  

con.query(sql, function (err, result) {  

if (err) throw err;  

console.log("1 record inserted");  

});

1.2. Insert Multi Record

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

console.log("Connected!");  

var sql = "INSERT INTO employees (id, name, age, city) VALUES ?";  

var values = [  

['2', 'Bharat Kumar', '45', 'Mumbai'],  

['3', 'John  Tan', '25', ?Las Vegas'],  

['4', 'David Mur', '17', ?CA']  

];  

con.query(sql, [values], function (err, result) {  

if (err) throw err;  

console.log("Number of records inserted: " + result.affectedRows);  

});  

});

2. Delete Record

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

var sql = "DELETE FROM employees WHERE city = 'Malyasia'";  

con.query(sql, function (err, result) {  

if (err) throw err;  

console.log("Number of records deleted: " + result.affectedRows);  

});  

});

3. Select Record

3.1.  Select all data

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

con.query("SELECT * FROM employees", function (err, result) {  

if (err) throw err;  

console.log(result);  

});  

});

3.2  Select  Condition Unique

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

con.query("SELECT * FROM employees WHERE id = '1'", function (err, result) {  

if (err) throw err;  

console.log(result);  

});  

});

3.3. Select  Condition Wildcard

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

con.query("SELECT * FROM employees WHERE city LIKE 'M%'", function (err, result) {  

if (err) throw err;  

console.log(result);  

});  

});

4. Drop Table

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

var sql = "DROP TABLE employee2";  

con.query(sql, function (err, result) {  

if (err) throw err;  

console.log("Table deleted");  

});  

});

Koneksi Databasse MySQL Pada Node.js

 

1. MySQL Create Connection

1.1. Install MySQL Driver : npm install mysql 

1.2. Create Connection : connection.js

var mysql = require('mysql');  

var con = mysql.createConnection({  

  host: "localhost",  

  user: "root",  

  password: "abcd"  

});  

con.connect(function(err) {  

  if (err) throw err;  

  console.log("Connected!");  

});

2. MySQL Create Database

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd"  

});  

con.connect(function(err) {  

if (err) throw err;  

console.log("Connected!");  

con.query("CREATE DATABASE dbMysql", function (err, result) {  

if (err) throw err;  

console.log("Database created");  

});  

});

3. MySQL Create Table

3.1. Membuat Table

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

console.log("Connected!");  

var sql = "CREATE TABLE employees (id INT, name VARCHAR(255), age INT(3), city VARCHAR(255))";  

con.query(sql, function (err, result) {  

if (err) throw err;  

console.log("Table created");  

});  

}); 

3.2. Membuat Table Primary Key

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "12345",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

console.log("Connected!");  

var sql = "CREATE TABLE employee2 (id INT PRIMARY KEY, name VARCHAR(255), age INT(3), city VARCHAR(255))";  

con.query(sql, function (err, result) {  

if (err) throw err;  

console.log("Table created");  

});  

});

Line Charts Pada Programming-R

 

Grafik garis memiliki garis yang menghubungkan semua titik dalam diagram.

Untuk membuat garis, gunakan fungsi plot() dan tambahkan parameter tipe dengan nilai "l":

Contoh :

plot(1:10, type="l")

plot(1:10, type="l", col="blue")

plot(1:10, type="l", lwd=2)

Note : lwd adalah ketebalan

parameter :

• 1 is default, while 0.5 means 50% smaller
• 2 means 100% larger

plot(1:10, type="l", lwd=5, lty=3)

note : lty merupakan styles

parameter : 

• 0 removes the line
• 1 displays a solid line
• 2 displays a dashed line
• 3 displays a dotted line
• 4 displays a "dot dashed" line
• 5 displays a "long dashed" line
• 6 displays a "two dashed" line

Multiline

line1 <- c(1,2,3,4,5,10)
line2 <- c(2,5,7,8,9,10)

plot(line1, type = "l", col = "blue")
lines(line2, type="l", col = "red")

Scatterplot Charts Pada Programming-R

 

Scatterplot Charts digunakan untuk menampilkan hubungan antara dua variabel numerik, dan memplot satu titik untuk setiap pengamatan. Dibutuhkan dua vektor dengan panjang yang sama, satu untuk sumbu x (horizontal) dan satu untuk sumbu y (vertikal)

Contoh :

x <- c(5,7,8,7,2,2,9,4,11,12,9,6)
y <- c(99,86,87,88,111,103,87,94,78,77,85,86)

plot(x, y)

x <- c(5,7,8,7,2,2,9,4,11,12,9,6)
y <- c(99,86,87,88,111,103,87,94,78,77,85,86)

plot(x, y, main="Observation", xlab="age", ylab="speed")

# day one, the age and speed of 12 cars:
x1 <- c(5,7,8,7,2,2,9,4,11,12,9,6)
y1 <- c(99,86,87,88,111,103,87,94,78,77,85,86)

# day two, the age and speed of 15 cars:
x2 <- c(2,2,8,1,15,8,12,9,7,3,11,4,7,14,12)
y2 <- c(100,105,84,105,90,99,90,95,94,100,79,112,91,80,85)

plot(x1, y1, main="Observation", xlab="age", ylab="speed", col="red", cex=2)
points(x2, y2, col="blue", cex=2)