4. Cricket Team

Given two files

https://file.io/CY1ew0PoEZ8w

app.js

and a database file

cricketTeam.db

consisting a table

cricket_team

Write APIs to perform operations on the table

cricket_team

containing the following columns,

Columns	Type
player_id	INTEGER
player_name	TEXT
jersey_number	INTEGER
role	TEXT

API 1

Path:
/players/

Method:
GET

Description:

Returns a list of all players in the team

Response

[
  {
    playerId: 1,
    playerName: "Lakshman",
    jerseyNumber: 5,
    role: "All-rounder"
  },
  ...
]

API 2

Path:
/players/

Method:
POST

Description:

Creates a new player in the team (database).

player_id

is auto-incremented

Request

{
  "playerName": "Vishal",
  "jerseyNumber": 17,
  "role": "Bowler"
}

Response

Player Added to Team

API 3

Path:
/players/:playerId/

Method:
GET

Description:

Returns a player based on a player ID

Response

{
  playerId: 1,
  playerName: "Lakshman",
  jerseyNumber: 5,
  role: "All-rounder"
}

API 4

Path:
/players/:playerId/

Method:
PUT

Description:

Updates the details of a player in the team (database) based on the player ID

Request

{
  "playerName": "Maneesh",
  "jerseyNumber": 54,
  "role": "All-rounder"
}

Response

Player Details Updated

API 5

Path:
/players/:playerId/

Method:
DELETE

Description:

Deletes a player from the team (database) based on the player ID

Response

Player Removed

Use

npm install

to install the packages.

Export the express instance using the default export syntax.

Use Common JS module syntax.

const express = require("express");
const { open } = require("sqlite");
const sqlite3 = require("sqlite3");
const path = require("path");

const databasePath = path.join(__dirname, "cricketTeam.db");

const app = express();

app.use(express.json());

let database = null;

const initializeDbAndServer = async () => {
  try {
    database = await open({
      filename: databasePath,
      driver: sqlite3.Database,
    });
    app.listen(3000, () =>
      console.log("Server Running at http://localhost:3000/")
    );
  } catch (error) {
    console.log(`DB Error: ${error.message}`);
    process.exit(1);
  }
};

initializeDbAndServer();

const convertDbObjectToResponseObject = (dbObject) => {
  return {
    playerId: dbObject.player_id,
    playerName: dbObject.player_name,
    jerseyNumber: dbObject.jersey_number,
    role: dbObject.role,
  };
};

app.get("/players/", async (request, response) => {
  const getPlayersQuery = `
    SELECT
      *
    FROM
      cricket_team;`;
  const playersArray = await database.all(getPlayersQuery);
  response.send(
    playersArray.map((eachPlayer) =>
      convertDbObjectToResponseObject(eachPlayer)
    )
  );
});

app.get("/players/:playerId/", async (request, response) => {
  const { playerId } = request.params;
  const getPlayerQuery = `
    SELECT 
      * 
    FROM 
      cricket_team 
    WHERE 
      player_id = ${playerId};`;
  const player = await database.get(getPlayerQuery);
  response.send(convertDbObjectToResponseObject(player));
});

app.post("/players/", async (request, response) => {
  const { playerName, jerseyNumber, role } = request.body;
  const postPlayerQuery = `
  INSERT INTO
    cricket_team (player_name, jersey_number, role)
  VALUES
    ('${playerName}', ${jerseyNumber}, '${role}');`;
  const player = await database.run(postPlayerQuery);
  response.send("Player Added to Team");
});

app.put("/players/:playerId/", async (request, response) => {
  const { playerName, jerseyNumber, role } = request.body;
  const { playerId } = request.params;
  const updatePlayerQuery = `
  UPDATE
    cricket_team
  SET
    player_name = '${playerName}',
    jersey_number = ${jerseyNumber},
    role = '${role}'
  WHERE
    player_id = ${playerId};`;

  await database.run(updatePlayerQuery);
  response.send("Player Details Updated");
});

app.delete("/players/:playerId/", async (request, response) => {
  const { playerId } = request.params;
  const deletePlayerQuery = `
  DELETE FROM
    cricket_team
  WHERE
    player_id = ${playerId};`;
  await database.run(deletePlayerQuery);
  response.send("Player Removed");
});
module.exports = app;

5. Movies

Given two files

app.js

and a database file

moviesData.db

https://file.io/IXsnQsqnQh8y

consisting of two tables

movie

and

director

Write APIs to perform CRUD operations on the tables

movie

director

containing the following columns,

Movie Table

Columns	Type
movie_id	INTEGER
director_id	INTEGER
movie_name	TEXT
lead_actor	TEXT

Director Table

Columns	Type
director_id	INTEGER
director_name	TEXT

API 1

Path:
/movies/

Method:
GET

Description:

Returns a list of all movie names in the movie table

Response

[
  {
    movieName: "Captain America: The First Avenger",
  },
  ...
]

API 2

Path:
/movies/

Method:
POST

Description:

Creates a new movie in the movie table.

movie_id

is auto-incremented

Request

{
  "directorId": 6,
  "movieName": "Jurassic Park",
  "leadActor": "Jeff Goldblum"
}

Response

Movie Successfully Added

API 3

Path:
/movies/:movieId/

Method:
GET

Description:

Returns a movie based on the movie ID

Response

{
  movieId: 12,
  directorId: 3,
  movieName: "The Lord of the Rings",
  leadActor: "Elijah Wood",
}

API 4

Path:
/movies/:movieId/

Method:
PUT

Description:

Updates the details of a movie in the movie table based on the movie ID

Request

{
  "directorId": 24,
  "movieName": "Thor",
  "leadActor": "Christopher Hemsworth"
}

Response

Movie Details Updated

API 5

Path:
/movies/:movieId/

Method:
DELETE

Description:

Deletes a movie from the movie table based on the movie ID

Response

Movie Removed

API 6

Path:
/directors/

Method:
GET

Description:

Returns a list of all directors in the director table

Database

Response

[
  {
    directorId: 1,
    directorName: "Joe Johnston",
  },
  ...
]

API 7

Path:
/directors/:directorId/movies/

Method:
GET

Description:

Returns a list of all movie names directed by a specific director

Response

[
  {
    movieName: "Captain Marvel",
  },
  ...
]

const express = require("express");
const { open } = require("sqlite");
const sqlite3 = require("sqlite3");
const path = require("path");

// Second step is to define path for Database
const databasePath = path.join(__dirname, "moviesData.db");

// Third Step is to call express.
const app = express();
app.use(express.json()); //Here we defined that app should use JSON String Data.

// Fourth Step is Initialization of Database.
let database = null;

const initializationDatabaseAndServer = async () => {
  try {
    database = await open({
      filename: databasePath,
      driver: sqlite3.Database,
    });
    app.listen(3000, () =>
      console.log("Server is running at http://localhost:3000/")
    );
  } catch (error) {
    console.log(`Server Error ${error.message}`);
    process.exit(1);
  }
};

initializationDatabaseAndServer();

// Here we are Formatting Database to Response to showcase as per result.
const movieIdDBRes = (dbObject) => {
  return {
    movieId: dbObject.movie_id,
    directorId: dbObject.director_id,
    movieName: dbObject.movie_name,
    leadActor: dbObject.lead_actor,
  };
};

const databaseObjectToResponseObject = (dbObjectArgs) => {
  return {
    movieName: dbObjectArgs.movie_name,
  };
};

//API 1 - Here we are creating our first API.
app.get("/movies/", async (request, response) => {
  const queryToGetAllMovies = `
    SELECT movie_name
    FROM movie;`;
  const moviesTable = await database.all(queryToGetAllMovies);
  response.send(
    moviesTable.map((eachPlayer) => databaseObjectToResponseObject(eachPlayer))
  );
});

//API 2 Here we are creating a object in Movie Table.
app.post("/movies/", async (request, response) => {
  const { directorId, movieName, leadActor } = request.body;
  const queryToCreate = `
    INSERT INTO movie(director_id, movie_name, lead_actor)
    VALUES ('${directorId}', '${movieName}', '${leadActor}')`;
  await database.run(queryToCreate);
  response.send("Movie Successfully Added");
});

// API 3 Here We can get a particular movie object

app.get("/movies/:movieId/", async (request, response) => {
  const { movieId } = request.params;
  const queryToGetParticularMovie = `
    SELECT 
        *
    FROM 
        movie
    WHERE 
        movie_id = ${movieId};`;
  const getParticularMovie = await database.get(queryToGetParticularMovie);
  response.send(movieIdDBRes(getParticularMovie));
});

// API 4 Change in Movie SQLite Database particular ID
app.put("/movies/:movieId/", async (request, response) => {
  const { directorId, movieName, leadActor } = request.body;
  const { movieId } = request.params;
  const queryToUpdate = `
  UPDATE 
    movie
  SET
    director_id = ${directorId},
    movie_name = '${movieName}',
    lead_actor = '${leadActor}'
  WHERE movie_id = ${movieId};`;
  await database.run(queryToUpdate);
  response.send("Movie Details Updated");
});

// API 5 DELETE OPERATION
app.delete("/movies/:movieId/", async (request, response) => {
  const { movieId } = request.params;
  const deleteMovieIdQuery = `
    DELETE FROM movie
    WHERE movie_id = ${movieId}`;
  const deleteParticular = await database.run(deleteMovieIdQuery);
  response.send("Movie Removed");
});

//API 6 Director table getting ALL
const directorDBtoRes = (eachItem) => {
  return {
    directorId: eachItem.director_id,
    directorName: eachItem.director_name,
  };
};

app.get("/directors/", async (request, response) => {
  const directorAllQuery = `
    SELECT *
    FROM director`;
  const gettingAllDirector = await database.all(directorAllQuery);
  response.send(
    gettingAllDirector.map((eachItem) => directorDBtoRes(eachItem))
  );
});

//API 7 /directors/:directorId/movies/

app.get("/directors/:directorId/movies/", async (request, response) => {
  const { directorId } = request.params;
  const filterWithQuery = `
  SELECT movie_name
  FROM movie
  WHERE director_id = ${directorId}`;
  const gettingDataPerDirector = await database.all(filterWithQuery);
  response.send(
    gettingDataPerDirector.map((eachMovie) => ({
      movieName: eachMovie.movie_name,
    }))
  );
});

module.exports = app;

Use

npm install

to install the packages.

Export the express instance using the default export syntax.

Use Common JS module syntax.

6. Covid-19 India

Given two files

app.js

and a database file

covid19India.db

consisting of two tables

https://file.io/U5kYXdgyp3cK

state

and

district

Write APIs to perform CRUD operations on the tables

state

district

containing the following columns,

State Table

Columns	Type
state_id	INTEGER
state_name	TEXT
population	INTEGER

District Table

Columns	Type
district_id	INTEGER
district_name	TEXT
state_id	INTEGER
cases	INTEGER
cured	INTEGER
active	INTEGER
deaths	INTEGER

API 1

Path:
/states/

Method:
GET

Description:

Returns a list of all states in the state table

Response

[
  {
    stateId: 1,
    stateName: "Andaman and Nicobar Islands",
    population: 380581
  },
  ...
]

API 2

Path:
/states/:stateId/

Method:
GET

Description:

Returns a state based on the state ID

Response

{
  stateId: 8,
  stateName: "Delhi",
  population: 16787941
}

API 3

Path:
/districts/

Method:
POST

Description:

Create a district in the district table,

district_id

is auto-incremented

Request

{
  "districtName": "Bagalkot",
  "stateId": 3,
  "cases": 2323,
  "cured": 2000,
  "active": 315,
  "deaths": 8
}

Response

District Successfully Added

API 4

Path:
/districts/:districtId/

Method:
GET

Description:

Returns a district based on the district ID

Response

{
  districtId: 322,
  districtName: "Haveri",
  stateId: 36,
  cases: 2816,
  cured: 2424,
  active: 172,
  deaths: 220,
}

API 5

Path:
/districts/:districtId/

Method:
DELETE

Description:

Deletes a district from the district table based on the district ID

Response

District Removed

API 6

Path:
/districts/:districtId/

Method:
PUT

Description:

Updates the details of a specific district based on the district ID

Request

{
  "districtName": "Nadia",
  "stateId": 3,
  "cases": 9628,
  "cured": 6524,
  "active": 3000,
  "deaths": 104
}

Response

District Details Updated

API 7

Path:
/states/:stateId/stats/

Method:
GET

Description:

Returns the statistics of total cases, cured, active, deaths of a specific state based on state ID

Response

{
  totalCases: 724355,
  totalCured: 615324,
  totalActive: 99254,
  totalDeaths: 9777
}

API 8

Path:
/districts/:districtId/details/

Method:
GET

Description:

Returns an object containing the state name of a district based on the district ID

Response

{
  stateName: "Maharashtra"
}

Use

npm install

to install the packages.

Export the express instance using the default export syntax.

Use Common JS module syntax.

7. Player Match Scores

Given two files

https://file.io/Z4vOLsdCDnxs

app.js

and a database file

cricketMatchDetails.db

consisting of three tables

player_details

match_details

and

player_match_score

Write APIs to perform operations on the tables

player_details

match_details

and

player_match_score

containing the following columns,

Player Details Table

Column	Type
player_id	INTEGER
player_name	TEXT

Match Details Table

Column	Type
match_id	INTEGER
match	TEXT
year	INTEGER

Player Match Score Table

Column	Type
player_match_id	INTEGER
player_id	INTEGER
match_id	INTEGER
score	INTEGER
fours	INTEGER
sixes	INTEGER

API 1

Path:
/players/

Method:
GET

Description:

Returns a list of all the players in the player table

Response

[
  { 
    playerId: 1,
    playerName: "Ram"
  },
  ...
]

API 2

Path:
/players/:playerId/

Method:
GET

Description:

Returns a specific player based on the player ID

Response

{ 
  playerId: 2,
  playerName: "Joseph"
}

API 3

Path:
/players/:playerId/

Method:
PUT

Description:

Updates the details of a specific player based on the player ID

Request

{
  "playerName": "Raju"
}

Response

Player Details Updated

API 4

Path:
/matches/:matchId/

Method:
GET

Description:

Returns the match details of a specific match

Response

{ 
  matchId: 18,
  match: "RR vs SRH",
  year: 2011
}

API 5

Path:
/players/:playerId/matches

Method:
GET

Description:

Returns a list of all the matches of a player

Response

[
  { 
    matchId: 1,
    match: "SRH vs MI",
    year: 2016
  },
  ...
]

API 6

Path:
/matches/:matchId/players

Method:
GET

Description:

Returns a list of players of a specific match

Response

[
  { 
    playerId: 2,
    playerName: "Joseph"
  },
  ...
]

API 7

Path:
/players/:playerId/playerScores

Method:
GET

Description:

Returns the statistics of the total score, fours, sixes of a specific player based on the player ID

Response

{
  playerId: 1,
  playerName: "Ram"
  totalScore: 3453,
  totalFours: 342,
  totalSixes: 98
}

Use

npm install

to install the packages.

Export the express instance using the default export syntax.

Use Common JS module syntax.

0000000000000000000000

Coding Practice APIs

API 1

Path: /players/

Method: GET

Description:

Response

API 2

Path: /players/

Method: POST

Description:

Request

Response

API 3

Path: /players/:playerId/

Method: GET

Description:

Response

API 4

Path: /players/:playerId/

Method: PUT

Description:

Request

Response

API 5

Path: /players/:playerId/

Method: DELETE

Description:

Response

5. Movies

API 1

Path: /movies/

Method: GET

Description:

Response

API 2

Path: /movies/

Method: POST

Description:

Request

Response

API 3

Path: /movies/:movieId/

Method: GET

Description:

Response

API 4

Path: /movies/:movieId/

Method: PUT

Description:

Request

Response

API 5

Path: /movies/:movieId/

Method: DELETE

Description:

Response

API 6

Path: /directors/

Method: GET

Description:

Response

API 7

Path: /directors/:directorId/movies/

Method: GET

Description:

Response

6. Covid-19 India

API 1

Path: /states/

Method: GET

Description:

Response

API 2

Path: /states/:stateId/

Method: GET

Description:

Response

API 3

Path: /districts/

Method: POST

Path:
/players/

Method:
GET

Path:
/players/

Method:
POST

Path:
/players/:playerId/

Method:
GET

Path:
/players/:playerId/

Method:
PUT

Path:
/players/:playerId/

Method:
DELETE

Path:
/movies/

Method:
GET

Path:
/movies/

Method:
POST

Path:
/movies/:movieId/

Method:
GET

Path:
/movies/:movieId/

Method:
PUT

Path:
/movies/:movieId/

Method:
DELETE

Path:
/directors/

Method:
GET

Path:
/directors/:directorId/movies/

Method:
GET

Path:
/states/

Method:
GET

Path:
/states/:stateId/

Method:
GET

Path:
/districts/

Method:
POST

Path:
/districts/:districtId/

Method:
GET

Path:
/districts/:districtId/

Method:
DELETE

Path:
/districts/:districtId/

Method:
PUT

Path:
/states/:stateId/stats/

Method:
GET

Path:
/districts/:districtId/details/

Method:
GET

Path:
/players/

Method:
GET

Path:
/players/:playerId/

Method:
GET

Path:
/players/:playerId/

Method:
PUT

Path:
/matches/:matchId/

Method:
GET

Path:
/players/:playerId/matches

Method:
GET

Path:
/matches/:matchId/players

Method:
GET

Path:
/players/:playerId/playerScores

Method:
GET