Java SQL Queries I have a working JavaFX program that acts as an SQL Client that

Question

Java SQL Queries

I have a working JavaFX program that acts as an SQL Client that opens MS Access databases and can run queries and output to tableview. I need to add some functionality and I need help with what the SQL queries should be. I don't need a full program, just the relevant lines. Please include code to output data to ObservableList so I can submit to tableview. I have a link to the Access DB file here http://onlyabout.me/Northwind.mdb

If you just want to see the tables here are a bunch of screenshots: OrderDetailsExtended: http://prntscr.com/a7e06d Orders: http://prntscr.com/a7e0ob Customers: http://prntscr.com/a7e16i

Employees: http://prntscr.com/a7e1pp

My data is stored like :

private ObservableList data;

...

data = FXCollections.observableArrayList();

SQL Queries:

Print order total (not including the freight cost) for a given order number

The program will ask the user for an order number, and then print out the total for all products in the order, taking into account quantities and discounts. (All computations should be done within the SQL query). ... So I think you would take the info from the "Order Details Extended", find each line with that ordernumber and return ExtendedPrice to my ObservableList. I just need help with the code to calculate and return the data.

Print all order details for a given order number

The program will ask the user for an order number, and then print the order date, freight charge, and all products and their quantity, unit price, and discount for the order...

This would be taking data from two tables; OrderDetailsExtended for products, quantity, unit price, and discount. Taking data from Orders for date and frieght charge. Keep in mind multiple items can be on one order number, being multiple rows.

Print the names and cities of all customers in a given country

The program will ask the user for a country, and then print out the names and cities of all customers in this country in order by city...... This would take data from Customers table.

Print the names of all employees who have a birthday in a given year

The program will ask the user for a year, and then print out the first and last names (in alphabetical order by last name) of all employees who were born during that year....

Use the Employees table.

I very much appreciate it!

Explanation / Answer

can help you with the last one

SELECT t1.EmpNo,
t1.LastName,
t1.BirthDate,
t2.EmpNo,
t2.LastName,
t2.Birthdate
FROM Employee t1, Employee t2
WHERE t1.BirthDate = t2.Birthdate
AND t1.EmpNo <> t2.EmpNo

Related Questions

Navigate

• Browse (All)
• Browse J
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.