SQL statements
In this project, you will create a Node.js application that interacts with a MySQL database. You’ll practice executing various types of queries, such as INSERT, SELECT, UPDATE, and DELETE, to manage employee records in the database.
Instructions:
Setup:
Make sure you have Node.js and MySQL installed on your system. Create a new folder for your project and navigate to it using the command line.
Database Setup:
Create a new MySQL database named “employee_db” or a name of your choice. Create a table named “employees” with columns “id” (INT), “name” (VARCHAR), and “job_title” (VARCHAR).
Node.js Application:
Inside your project folder, create a new file named “app.js”.
Connect to the Database:
Require the mysql npm package and create a connection to the MySQL database using your database credentials.
INSERT Statement:
Write a function to insert a new employee record into the “employees” table. Use the connection.query() method to execute an INSERT query.
SELECT Statement:
Write a function to retrieve the names of all employees from the “employees” table. Use the connection.query() method to execute a SELECT query.
UPDATE Statement:
Write a function to update the job title of an employee. Use the connection.query() method to execute an UPDATE query.
DELETE Statement:
Write a function to delete an employee record from the “employees” table. Use the connection.query() method to execute a DELETE query.
Execution:
Run your Node.js application using the command node app.js. Check the console output for the results of each query.
Example Code Snippets:
Here’s an example of how your “app.js” file could be structured:
const mysql = require(‘mysql’);
const connection = mysql.createConnection({
host: “localhost”,
user: “yourusername”,
password: “yourpassword”,
database: “employee_db”
});
connection.connect(function(err) {
if (err) throw err;
console.log(“Connected to MySQL server!”);
// Write your INSERT, SELECT, UPDATE, and DELETE functions here
// Close the connection
connection.end(function(err) {
if (err) throw err;
console.log(“Connection closed”);
});
});
Note: Replace ‘yourusername’ and ‘yourpassword’ with your actual MySQL credentials.
Submission Instructions
1. Place all of your files for this project in a folder labeled “Lesson 10.” In the file names, be sure to replace MEID with your actual MEID.
CIS276DA_XXXXX_JAM2334209.sql
