SQLite

_README

See makefile for execution

billboard.csv

billboard2.csv

makefile

run:
	node server.js

setup-db:
	sqlite3 billboard.db -cmd '.mode csv' -cmd '.import --csv billboard.csv top100' --cmd '.import --csv billboard2.csv top100'

install:
	npm install express sqlite3

server.js

const sqlite3 = require('sqlite3').verbose();
const express = require("express");
const app = express();
const router = express.Router();
app.use(express.json());

const port = 3000;

// Start server
app.listen(port, () => {
    console.log("Server running on port %PORT%".replace("%PORT%",port))
});

// open the database
let db = new sqlite3.Database('./billboard.db', sqlite3.OPEN_READWRITE, (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the billboard top 100 database.');
});

/* GET Request /week/:num */
app.get("/week/:num", (req, res) => {
  // Access id via: req.params.id
  const sql = `SELECT * FROM top100 where week = ${req.params.num};`;
  db.all(sql, [], (err, row) => {
    if (err) {
      res.status(400).json({"error":err.message});
      return console.error(err.message);
    }
    res.status(200).json({row});
  })
});


// Default response for any other request
app.use(function(req, res){
    res.status(404);
});

statements.sql

-- (a) adding record to the table
INSERT INTO top100 VALUES ('Michael Jackson', 'Thriller');

-- (b) get a specific song, i.e. by artist
 -- * means return all attributes
SELECT * FROM top100 WHERE artist = 'Michael Jackson';

-- (c) get nth song
  -- rowid is a attribute assigned by sqlite
SELECT * FROM top100 WHERE rowid = 6;

-- get number of songs per artist
select artist, count(*) as Count from top100 GROUP BY artist ORDER BY Count DESC;

-- (d) change name of artist or other text 
  -- update for updating data; ALTER for updating table structure
UPDATE top100 SET Artist = 'MULTIPLE' WHERE Artist like '% Featuring%'; 
-- to replace substring
UPDATE top100 SET Artist = replace(Artist, 'Featuring', 'feat.');

-- (e) delete records matching condition
DELETE FROM top100 WHERE artist = 'Michael Jackson';

-- (i) create table

CREATE TABLE anothertable (column1 TEXT, column2 TEXT);

-- (ii) counting records in a table
  -- use aggregating function count(*) to count
SELECT count(*) FROM top100;

-- (iii) list everything in a table
SELECT * FROM top100;

-- (iv) change column names
ALTER TABLE top100 RENAME Song TO Title;

-- (v) add or remove columns
 -- adding column
ALTER TABLE top100 ADD COLUMN week;
 -- remove column with DROP keyword

-- update week column
UPDATE top100 SET week = 1 where rowid < 101;
UPDATE top100 SET week = 2 where rowid > 100;
-- setting to today's date UPDATE top100 SET week = date();


-- (vi) delete the table
DROP TABLE top100;