I need to write some queries for a project. I would need SQL code for these func
ID: 3572292 • Letter: I
Question
I need to write some queries for a project. I would need SQL code for these functions:
Search
New/Add
Remove
Edit
Reports
Example of what I need:
Function 1: List all the customer information
Query: Select * From Customer;
Here is some more information about the project:
Below is the home screen which is shown after the user login, giving the user a number of options to choice from which include: Add/New, Edit, Remove, Report and Search. Each screen will generate a different page which serve its own function to the database.
-The Search page which gives the user the option to search the database for a specific keyword, also having the option to narrow the search with a dropdown box, restricting the search fields.
-The Remove page, giving the user the power to delete a piece of data from the database, before which, a pop-up will appear as a confirmation to delete the data
-The Report page which gives the user the ability to generate a report on the data within the database and further export that data to an Excel spreadsheet to perform further analysis on.
Computer Inventory Main Page Main Page Search New/Add Remove Edit Reports Database Management ght 2016Explanation / Answer
SQL CODES:
Search:
This will be based on your drop-down box selection.
String selection = //the database column equivalent to your selection from drop down box
SELECT selection FROM CUSTOMER;
void search(String selection) {
// After all your connections into the database
stmt = conn.createStatement(); //stmt is the statement object and conn is the connectionobject
String sql = "SELECT " + selection + " FROM Customer ";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("customer_id");
String name = rs.getInt("customer_name");
String addr = rs.getString("customer_address");
double price = rs.getString("price");
}
rs.close();
}
New/Add:
I believe this is for adding a new row.
Get all the details from the user in a text box and then use the following Query, for example
INSERT INTO Customer VALUES (customer_id,customer_name, customer_address, price,..,..);
all the column values should be provided.
void insert(){ // you have arguments here which can send you in the values
// After all your connections into the database
stmt = conn.createStatement(); //stmt is the statement object and conn is the connectionobject
String sql = "INSERT INTO Customer " +
"VALUES (100, 'Zara', 'Arizona', 300)";
stmt.executeUpdate(sql);
}
Remove:
Deleting a row from the table. The table must have a unique id, get the unique id of the row which needs to be deleted and then use that id to delete the row.
DELETE FROM Customer WHERE customer_id = 123;
Only the intended record must be removed, not others. hence go with id.
void delete(int id) {
// After all your connections into the database
stmt = conn.createStatement(); //stmt is the statement object and conn is the connectionobject
String sql = "DELETE FROM Customer " +
"WHERE customer_id =" + id;
stmt.executeUpdate(sql);
}
Edit:
Use SELECT query to show the details to the user and then get to know what they want to edit.
After that use the UPDATE query.
UPDATE Customer SET customer_name='Helen' WHERE customer_id=122;
Again, its better to use the id.
void delete(String name, int id) {
// After all your connections into the database
stmt = conn.createStatement(); //stmt is the statement object and conn is the connectionobject
String sql = "UPDATE Customer " +
"SET customer_name = " + name +" WHERE customer_id = " + id;
stmt.executeUpdate(sql);
}
Report Page:
Use SELECT * FROM CUSTOMER; to get all the details and then display them using ResultSet.
void report(String item) {
// After all your connections into the database
stmt = conn.createStatement(); //stmt is the statement object and conn is the connectionobject
String sql = "SELECT * FROM Customer ";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("customer_id");
String name = rs.getInt("customer_name");
String addr = rs.getString("customer_address");
double price = rs.getString("price");
//do something with each record
}
rs.close();
}