Technical Test (Note: You may choose which task you want to do. Doing all tasks is not a mandatory)

Task-1.md

Create A Bookstore API with express

Guide

1. Fork project project berikut sebagai base pengerjaan test https://github.com/neos1803/mock-technical-test.git
2. Commit perubahan yang telah dikerjakan, bisa menggunakan cli: git commit -am 'Add some feature' atau menggunakan gui
3. Push ke fork yang telah dibuat: git push origin nama-pribadi
4. Silahkan open pull request.
5. Penilaian akan dilakukan pada pull request masing-masing
6. Untuk data buku yang akan digunakan, sudah terdapat di bagian models. Silahkan digunakan.

Tugas

Silahkan membuat beberapa endpoint dengan ketentuan sebagai berikut

HTTP Request	Endpoint	Function
GET	/books/	Menampilkan semua jenis buku
GET	/books/:id	Menampilkan buku berdasarkan id
GET	/books/jenis/:jenis	Menampilkan buku berdasarkan jenis bukunya
PUT	/books/:id	Mengubah nama buku
POST	/books/	Menambah buku baru
DELETE	/books/:id	Menampilkan semua buku kecuali buku yang telah dihapus berdasarkan id

Expected Response

Endpoint	Expected Response
GET /books/
GET /books/:id
GET /books/jenis/:jenis
PUT /books/:id
POST /books/
DELETE /books/:id

Task-2.md

SQL Challenge 1

Customers Table

Orders Table

The challenge is to find customers who don't make any order or don't have any order. Create a query that will return a result like the image below

Task-3.md

SQL Challenge 2

Table mahasiswa

Challenge: Buatlah query untuk menemukan NIM yang memiliki duplikasi kode_matakuliah seperti contoh di bawah

Vikhrama

SQL Challenge 1 Answers

SELECT `customers`.`name`
FROM `customers`
WHERE NOT EXISTS(
    SELECT 1 FROM `orders`
    WHERE `orders`.`customer_id` = `orders`.`id`
);

SQL Challenge 2 Answers

SELECT *, COUNT(*) AS Jumlah_duplikasi
FROM mahasiswa
GROUP BY nim, kode_matakuliah
HAVING Jumlah_duplikasi > 1;

tugas query SQL
Nama: Zulham Ari Nur Ridhwan

Task 2

SELECT id, name
FROM customers
LEFT JOIN orders ON customer_id = orders.customer_id
WHERE orders.customer_id IS NULL;

Task 3

SELECT nim, kode_matakuliah, COUNT(*) AS Jumlah_duplikasi
FROM mahasiswa
GROUP BY NIM, kode_matakuliah
HAVING COUNT(kode_matakuliah) > 1;

Nama: Irgy Rafliyanda

Task 2
SELECT a.name
FROM customers a
LEFT JOIN orders b
ON a.id = b.customer_id
WHERE a.id IS NULL;

Taks 3
SELECT nim, kode_matakuliah, COUNT(nim) jumlah_duplikasi
FROM mahasiswa
GROUP BY nim, kode_matakuliah
HAVING COUNT(kode_matakuliah) > 1;

Nama: indrawan

task 2
SELECT customers.name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.customer_id IS NULL;

task 3
SELECT nim, kode_matakuliah, COUNT(kode_matakuliah) AS duplikasi FROM mahasiswa
GROUP BY nim, kode_matakuliah
HAVING duplikasi > 1

nomer 1
/** OPTION 1 **/
SELECT C.NAME
FROM CUSTOMER C
LEFT JOIN ORDER_TABLE O ON C.ID = O.CUSTOMER_ID
WHERE O.ID IS NULL;

/** OPTION 2 **/
SELECT C.NAME
FROM CUSTOMER C
WHERE C.ID NOT IN (SELECT DISTINCT CUSTOMER_ID FROM ORDER_TABLE);

nomer 2

SELECT nim, kode_matakuliah, COUNT() AS jumlah_duplikasi
FROM mahasiswa
GROUP BY nim, kode_matakuliah
HAVING COUNT() > 1;

Nama: Ridwan arioman

Task 2
SELECT a.name
FROM customers a
LEFT JOIN orders b
ON a.id = b.customer_id
WHERE ISNULL(a.id);

Taks 3
SELECT nim, kode_matakuliah, COUNT(*) AS Jumlah_duplikasi
FROM mahasiswa
GROUP BY NIM, kode_matakuliah
HAVING COUNT(kode_matakuliah) > 1;

Nama : Muhammad Shidqi Rahidtyo

Task 2
SELECT name FROM Customers_Table a LEFT JOIN Orders_Table b ON a.id = b.id WHERE b.id IS NULL

Task 3
SELECT nim, kode_matakuliah, COUNT(nim) AS duplikasi FROM mahasiswa
GROUP BY nim, kode_matakuliah
HAVING COUNT(nim) > 1

Nama: Ahmad Aziz F

Task 2
SELECT c.name
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
WHERE ISNULL(c.id);

Taks 3
SELECT nim, kode_matakuliah, COUNT(*) AS Jumlah_duplikasi
FROM mahasiswa
GROUP BY NIM, kode_matakuliah
HAVING COUNT(kode_matakuliah) > 1;

=================
Nama : Rendi Widjaya

TAKS 1

https://github.com/renwidjaya/API-BOOKS-STORE

TAKS 2

SELECT C.NAME
FROM CUSTOMER C
WHERE C.ID NOT IN (SELECT DISTINCT CUSTOMER_ID FROM ORDER_TABLE);

TAKS 3

SELECT nim, kode_matakuliah, COUNT( ) AS jumlah_duplikasi
FROM mahasiswa
GROUP BY nim, kode_matakuliah
HAVING COUNT( ) > 1;

Nama: Widya Arum Amalia

TASK 1
https://gitlab.com/widyaarumamalia95/technical-test
TASK 2
SELECT a.name
FROM tbl_customer a
LEFT JOIN tbl_order as b
ON a.id = b.customer_id
WHERE b.customer_id IS NULL
TASK 3
SELECT nim AS NIM, kode_matakuliah, COUNT(*) AS Jumlah_duplikasi
FROM tbl_mahasiswa as a
GROUP BY nim , kode_matakuliah
HAVING COUNT(kode_matakuliah) > 1;