For this project we will be using SQL JOINS. Quite often you will find the need to combine data from multiple tables. It is better to put the data in two tables rather than repeating something over and over again. You will see this quite often in Shopping Carts. This will take place both before and after the purchase takes place. You will have to do some research on this in your Discussion Board also. There will be one SELECT statement for each of the steps where you have to do something. So you will have a SELECT statement for steps A, E, F, G, H, I and J below.
A. For this first part we need to see everything from the vacation package table and only the notes pulled from the categories table. This should all be in one SQL statement. Save this in one sql file starting with your two initials and then -web250project3.sql (mine would be sc-web250project3.sql)
B. Open this file in NotePad: createtables.txt. Save it as createtables.sql and then import it into your database. This will create the tables for the rest of the project.
C. Open this file in NotePad: insertintotables.txt. Save it as insertintotables.sql and then import it into your database. This will populate the tables for the rest of the project.
D. Get familiar with the different tables and the data stored in them.
E. For this section, I want to see the first and last name for each employee and their position. (Save this and the future work for this section in the file you created earlier)
F. Now I want to see the first name, last name, position and salary for each employee that makes more than $25000.
G. For this one I want the same as before, but sorted by last name and then first name.
H. On this part I want to know the first name and last name of the customer who had the order id of: 56A901.
I. On this part I want to know the first name and last name of the customer who had the order id of: 56A901 and also show the description of the product.
J. For this last part I need to see first and last names for all of the customers that bought the PLASTIC PUMPKIN 18 INCH.
Good Luck and Have Fun!! Next week it will be time for PHP and MySQL together via SQL. :)