The Big Jumbo Post of SQL Exercises
So I just had the most intense day if learning in my life. in 8 very short hours, I went from a relative SQL novice to a real pro, able to do joins as well as I walk with subqueries and all that good stuff. We literally spent all day doing nothing but generating SQL queries using Oracle’s 10g database with their SQL developer on some data sets. I enjoyed the day so much, I thought it would be a great resource for people looking to get quickly up to speed on common SQL queries and tasks, such as join, equijoins, etc. Now there were some attached slides to go with these, and like I said it requires some databases and some setup, but if you are interested in SQL I’m sure you can adapt the SQl to run on SQL Server or MYSQL. I’ve put together a package that has everything you need to have to install and run the solutions I’ll post below, just so you know I’m not making this stuff up. LINK
First off, the instructions for what to do to set these all up (N.B. I didn’t write these questions nor the instructions for how to set this all up. Our professor, Lakshmi Iyer, did that, so if something doesn’t work for you, blame her. Although, we did get 25 people up to speed successfully so…) Set-up steps:
- Install Oracle Express Edition like any other software. Run the home page when it installs.
- Log in to the database with your web browser using the username “system” and the password you set during the install.
- Unlock the HR user account by using this SQL command in the terminal: “ALTER USER HT ACCOUNT UNLOCK IDENTIFIED BY hr;” Change it’s password as well.
- Create two users: Student and PVC (set the passwords the same as the usernames). Make sure the accounts are unlocked and has the roles “CONNECT” and “RESOURCES” with the Sytem Provledges “CREATE TABLE”, “CREATE VIEW”, “CREATE SEQUENCE”, and “CREATE TRIGGER”
- Run the SQL Developer
- Connect to the HR Database by clicking the green + symbol. Set the connection name as “hr”, username “hr”, password whatever you set the password to. Unless you installed the database on another computer, use the default connection settings.
- The hr database should be pre-populated, But go ahead and set up the same connections for the Student and PVC databases, using the credentials for each.
- Open the SQL file createStudent, select all the text, and run it against the Student database to load the data.
- Open the create database file in the BigOracle10e folder and run it. Then run the load 1 and load 2 files. MAKE SURE YOU RUN THEM IN ORDER!
- And now you’re ready to play around with the databases.
Now for the questions. They slowly increase in difficulty. I’ll post some possible solutions afterwards. Keep in mind there are many possible solutions you could do. These are only the ones we saved from the class in our little wiki thing we were using.
SQL Activity #1 – some basic SQL – use the HR database- Some simple queries to start of the session
1. List all employees in the organization.
2. List all employees who last name begins with a G
3. Display first name, last name and department ID of each employee.
4. Change last query so column heading reads First, Last and Dept
5. Modify Query 3 to list only employees in Department 90
6. Modify query 3 to list employees in depts. 100, 110 and 120.
7. List first name, last name and jobID of all managers from Employee table. Before you do this look at the jobs table to see how the data is coded for mgrs. The JOB_ID of a manager ends with either ‘_MGR’ or ‘_MAN’, depending on the department. Therefore, the search pattern must be a regular expression, and you must use the REGEXP_LIKE function, In the regular expression (_m[an|gr]), the metacharacter | indicates the OR condition. The third function parameter, ‘i’, specifies that the match is case-insensitive.
8. List employees first name, last name, hire date and sort by last name.
9. Modify query 4 to show dept name 10. Display LAST_NAME, SALARY (monthly pay), and annual pay for each employee in department 90, in descending order of SALARY.
11. Use numeric function ROUND to display the daily pay of each employee in department 100, rounded to the nearest cent.
12. When an employee changes jobs, the START_DATE and END_DATE of his or her previous job are recorded in the JOB_HISTORY table. Employees who have changed jobs more than once have multiple rows in the JOB_HISTORY table. Display info from Job History listed by Employee ID.
13. Display the Number of Months Between Dates and list by months worked. 14. Count the Number of people reporting to a manager. (all employees that have a manager)
14. Count the number of employees each manager supervises.
15. Display the first name, last name, and job title of that employee who does not report to a manager.
16. Display for each job_id, the minimum, average, median, maximum and standard deviation of salary and list by Job_id
SQL Activity # 2 based on Student database: Basic to intermediate SQL Refer to the student ERD handout for attributes and relationships
1. Retrieve data from the COURSE table for courses that cost 1195, and whose descriptions start with ‘Intro’, sorted by their prerequisites.
2. List data from the STUDENT table for students whose last names begin with ‘A’,’B’, or ‘C’, and who work for ‘Competrol Real Estate’, sorted by their last names.
3. List all the descriptions from the GRADE_TYPE table, for rows that were modified by the user MCAFFREY. Date/Time Functions
4. Display all the sections where classes start at 10:30 AM.
5. Write the query to accomplish the following result. The output shows you all the days of the week where sections 82, 144 and 107 start. Note the order of the days.
DAY SECTION_ID
— ———-
MON 107
SAT 82
SUN 144
3 rows selected.
6. Select the distinct course costs of all the courses. If the course cost is unknown, substitute a zero. Format the output with a leading $ sign, and separate the thousands with a comma. Display two digits after the decimal point. The query’s output should look like the following result:
COST
$0.00
$1,095.00
$1,195.00
$1,595.00
4 rows selected.
7. For the students enrolled on January 30, 2003, display the columns STUDENT_ID and ENROLL_DATE. Resulting output:
STUDENT_ID ENROLL_DA
———- ———
102 30-JAN-03
102 30-JAN-03
103 30-JAN-03
104 30-JAN-03
105 30-JAN-03
106 30-JAN-03
106 30-JAN-03
107 30-JAN-03
108 30-JAN-03
109 30-JAN-03
109 30-JAN-03
11 rows selected.
8. Execute the following SQL statements. Explain the individual statements.
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM dual;
– get current time zone
ALTER SESSION SET TIME_ZONE = ‘-8:00’;
– set current time zone to Pacific time
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM dual;
– get current time zone
ALTER SESSION SET TIME_ZONE = ‘-5:00’;
– set current time zone to Eastern time
Aggregate Functions
9. Display a count of all the different course costs in the COURSE table.
10. Determine the number of students living in zip code 10025.
11. Show all the different companies for which students work. Display only companies where more than four students are employed.
12. List how many sections each instructor teaches.
13. Formulate the question for the following statement:
SELECT COUNT(*), start_date_time, location
FROM section
GROUP BY start_date_time, location
HAVING COUNT(*) > 1
14. Determine the highest grade achieved for the midterm within each section.
Equijoins
1. Select the course description, section number, and location for sections meeting in location L211.
2. Show the course description, section number, starting date and time of the courses Joseph German is taking.
3. List the instructor ID, last name of the instructor, and section ID of sections where class participation contributes to 25% of the total grade. Order the result by the instructor’s last name.
4. Display the first and last names of students who received 99 or more points on their class project.
5. Select the grades for quizzes of students living in zip code 10956.
6. List the course number, section number, and instructor first and last names of classes with course number 350 as a prerequisite.
7. Write the questions for the following two SELECT statements. Explain the difference between the two results. Lists the students and instructors that share zip codes Lists the students and instructors that share zip codes and are enrolled in the same section
Subqueries
1. Using a subquery construct, determine which sections the student Thomas Thomas is enrolled in.
2. Write the question for the following SELECT statement.
SELECT zip FROM zipcode z WHERE NOT EXISTS (SELECT '*' FROM student WHERE z.zip = zip) AND NOT EXISTS (SELECT '*' FROM instructor WHERE z.zip = zip)
3. Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.
4. If you have a choice to write either a correlated subquery or a simple subquery, which one would you choose and why?
5. Determine the top three zip codes where most of the students live.
SQL Activity for BIGPVC – Intermediate to Advanced (NOTE: No solutions for these yet. Waiting for them to be posted)
1. Display order number, customer number, order date and items ordered for order #1.
2. Display each item ordered for order #1, its standard price, and total price for each item ordered.
3. Total the cost of the order for order #1.
4. Find customers who have not placed any orders.
5. Produce a list of all the products (show product description) and the number of times each product has been ordered.
6. Calculate the total raw material cost (label TotCost) for each product compared to its standard product price and display product ID, product description, standard price, and the total cost in the result.
7. For every order that has been received, display the order ID, the total dollar amount owed on that order (you’ll have to calculate this total from attributes in one or more tables; label this result Total_Due), and the amount received in payments on that order (assume there is only one payment made on each order). To make this query a little simpler, you don’t have to include those orders for which no payment has yet been received. List the results in decreasing order by the difference between total due and amount paid.
8. List the order number and order quantity for all customer orders for which the order quantity is greater than the average order quantity of that product (Hint: This involves a correlated subquery.).
9. List the names and number of employees supervised (label this value HeadCount) for all the supervisors who supervise more than two employees.
10. List each salesperson who has sold computer desks and the number of units sold by each salesperson.
11. List the salesperson who has sold the most computer desks.
12. List in alphabetical order the names of all employees (managers) who are now managing people with skill ID BS12. List each such manager’s name only once, even if that manager manages several people with this skill.
13. Display the salesperson name, product finish, and total quantity sold (label as Tot_Sales) for each finish by each salesperson.
14. Display the customer ID, name, and order ID for all customer orders. For those customers who do not have any orders, include them in the display once by showing an order ID of 0.
15. Write a query to list the number of products produced in each work center (label this result ‘TotalProducts’). If a work center does not produce any products, display the result with a total of 0.
16. The production manager at PVFC is concerned about support for purchased parts in products owned by customers. A simple analysis he wants done is to determine for each customer how many vendors are in the same state as that customer. Develop a list of all the PVFC customers by name with the number of vendors in the same state as that customer (label this computed result NumVendors).
17. Display the Employee ID and Employee Name for those employees who do not possess the skill Router. Display the results in order by EmployeeName.
18. Show the customer ID and name for all the customers who have ordered both products with Ids 3 and 4 on the same order.
19. The head of marketing is interested in some opportunities for cross-selling of products. She thinks that the way to identify cross-selling opportunities is to know for each product how many other products are sold to the same customer on the same order. So, for example, a product that is bought by a customer in the same order with lots of other products is a better candidate for cross-selling than a product bought by itself.
a. To help the marketing manager, first list the IDs for all the products that have sold in total more than 20 units across all orders (these are popular products, which are the only products she wants to consider as triggers for potential cross-selling).
b. Next, write a new query that lists all the IDs for the orders that include products that satisfy this first query along with the number of products on those orders (see result below for an example). Only orders with three or more products on them are of interest to the marketing manager. Write this query as general as possible to cover any answer to the first query, which might change over time. To clarify, if product X is one of the products that is in the answer set from part a., then in part b., we want to see the desired order information for orders that include product X.
c. Finally, the marketing manager needs to know what are the (other) products sold on the orders that are in the result for part b. (again, write this query for the general, not specific result, to the query in part b.). These are products that are sold, for example, with product X from part a., and these are the ones that if people buy that product, we would want to try to cross-sell them product X because history says they are likely to buy it along with whatever else they are buying. Write a query to identify these other products by ID and description. It is okay to include product X in your result (i.e., you don’t need to exclude the products that were in the result of part a.).
20. For each product display in ascending order by product ID the product ID and description along with the customer ID and name for the customer who has bought the most of that product; also show the total quantity ordered by that customer (who has bought the most of that product). Use a correlated subquery.
21. Display in product ID order the product ID and total amount ordered of that product by the customer who has bought the most of that product; use a derived table in a FROM clause to answer this query. Note that result of this query is a subset (first and last columns) of the prior query result.
The Solutions:
Part 1:
1. select * from EMPLOYEES;
2. select * from EMPLOYEES where LAST_NAME Like ‘G%’;
3. select FIRST_NAME, LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
4. select FIRST_NAME as First, LAST_NAME as Last, DEPARTMENT_ID as Dept from EMPLOYEE;
5. SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 90;
6. SELECT first_name “First”, last_name “Last”, department_id “Dept” FROM employees
WHERE department_id IN (100, 110, 120);
7.SELECT First_Name, Last_Name, Job_ID FROM Employees
WHERE REGEXP_LIKE( Job_ID, ‘_MGR$’) OR REGEXP_LIKE( Job_ID, ‘_MAN$’);
SELECT first_name, last_name, job_id FROM employees
WHERE job_id LIKE ‘%_MGR’ OR job_id LIKE ‘%_MAN’;
SELECT FIRST_NAME, LAST_NAME, JOB_ID FROM EMPLOYEES WHERE REGEXP_LIKE(JOB_ID, ‘_M[AN|GR]’);
SELECT FIRST_NAME, LAST_NAME, JOB_ID FROM EMPLOYEES WHERE REGEXP_LIKE(JOB_ID, ‘_M[AN|GR]’ ‘i’); //case insensitive version, note the i!
8. SELECT FIRST_NAME, LAST_NAME, HIRE_DATE FROM EMPLOYEES ORDER BY LAST_NAME;
9. Select Employees.FIRST_NAME,EMPLOYEES.LAST_NAME,EMPLOYEES.DEPARTMENT_ID,DEPARTMENTS.DEPARTMENT_NAME
From EMPLOYEES, DEPARTMENTS
Where DEPARTMENTS.DEPARTMENT_ID=EMPLOYEES.DEPARTMENT_ID
select E.FIRST_NAME as First, E.LAST_NAME as Last, E.DEPARTMENT_ID as Dept, D.DEPARTMENT_NAME
from EMPLOYEES E, DEPARTMENTS D
where D.DEPARTMENT_ID = E.DEPARTMENT_ID;
SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_NAME FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;
10. SELECT last_name “Last Name”, salary “Monthly Pay”, salary*12 AnnualSalary from employees
WHERE department_id = 90
ORDER BY salary DESC
11. SELECT last_name “Last Name”,Round( salary*12/365,2) “Daily Pay”, salary “Salary” from employees
WHERE department_id = 100
ORDER BY salary DESC
12. SELECT * FROM JOB_HISTORY ORDER BY EMPLOYEE_ID;
13. Select EMPLOYEE_ID, Round((END_DATE – START_DATE)/30) as “Months Worked” from job_history;
ALSO
SELECT ROUND(MONTHS_BETWEEN(END_DATE, START_DATE)) AS “Months Worked” FROM JOB_HISTORY
ORDER BY “Months Worked”;
select EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID, Round(MONTHS_BETWEEN(END_DATE, START_DATE))
as Months from JOB_HISTORY order by Months desc
14. Select count(manager_id) from employees;
14b. SELECT MANAGER_ID “Manager”,
COUNT(*) “Number of Reports”
FROM EMPLOYEES
GROUP BY MANAGER_ID;
15. Select employees.first_name, employees.last_name, jobs.job_title
from employees, jobs
where employees.job_id = jobs.job_id and
employees.manager_id is null;
16.SELECT JOB_ID, MIN(SALARY) AS “Min”, AVG(SALARY) AS “Average”, MEDIAN(SALARY) AS “Median”, MAX(SALARY) AS “Max”, ROUND(StdDev(SALARY),2) AS “Standard Deviation”
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;
Part 2:
1. SELECT * FROM course
WHERE cost = 1195
AND description LIKE ‘Intro%’
ORDER BY prerequisite;
2. Select *
from student
where (last_name like ‘A%’
or last_name like ‘B%’
or last_name like ‘C%’)
and employer=’Competrol Real Estate’
order by last_name
3. SELECT description FROM grade_type
WHERE modified_by = ‘MCAFFREY’;
4. SELECT * FROM SECTION WHERE TO_CHAR(START_DATE_TIME,’HH24MI’) = ‘1030’;
5. select to_char(S.START_DATE_TIME,’DY’) as Day, S.SECTION_ID from SECTION S
where S.SECTION_ID in (107, 82, 144)
order by Day
6. SELECT DISTINCT TO_CHAR(NVL(COST,’0′), ‘$9,990.99′)”COST” FROM COURSE ORDER BY “COST”;
7. select STUDENT_ID, ENROLL_DATE from ENROLLMENT where ENROLL_DATE >= ’30-JAN-03′ and
ENROLL_DATE < ’31-JAN-03′
select student_id, enroll_date
from Enrollment
where to_char(enroll_date,’YYYY-MM-DD’) <= ‘2003-01-30’;
8. inline
9. SELECT COUNT(DISTINCT cost) FROM course
SELECT COST, COUNT(COST) FROM COURSE GROUP BY COST;
10. SELECT COUNT(STUDENT_ID) AS STUDENTS FROM STUDENT
WHERE ZIP = 10025;
11. SELECT EMPLOYER, COUNT(EMPLOYER) FROM STUDENT HAVING COUNT(EMPLOYER) > ‘4’ GROUP BY EMPLOYER;
12. SELECT LAST_NAME, COUNT(SECTION_ID)
FROM SECTION, INSTRUCTOR
WHERE SECTION.INSTRUCTOR_ID=INSTRUCTOR.INSTRUCTOR_ID
GROUP BY LAST_NAME;
13. “Find the sections sharing the same start date and location and the number of rows which share these dates and locations.”
14. SELECT section_id, MAX(NUMERIC_GRADE) FROM GRADE WHERE GRADE_TYPE_CODE = ‘MT’ group by section_id order by section_id;
Equijoins section:
1. SELECT DESCRIPTION, SECTION_NO, LOCATION FROM SECTION, COURSE WHERE SECTION.COURSE_NO = COURSE.COURSE_NO AND LOCATION=’L211′;
2. select c.description, s.section_no, s.start_date_time from course c, section s, enrollment e, student st
where st.last_name = ‘German’ and st.first_name = ‘Joseph’ and S.SECTION_ID = E.SECTION_ID and ST.STUDENT_ID = E.STUDENT_ID and s.course_no = c.course_no
SELECT STUDENT.STUDENT_ID, FIRST_NAME, LAST_NAME, DESCRIPTION, SECTION_NO, TO_CHAR(START_DATE_TIME,’MM-DD-YY HH12:MM AM’)”START DATE AND TIME”
FROM SECTION, COURSE, ENROLLMENT, STUDENT
WHERE SECTION.COURSE_NO = COURSE.COURSE_NO
AND STUDENT.STUDENT_ID=ENROLLMENT.STUDENT_ID
AND ENROLLMENT.SECTION_ID=SECTION.SECTION_ID
AND FIRST_NAME = ‘Joseph’
AND LAST_NAME = ‘German’;
3. SELECT INSTRUCTOR.INSTRUCTOR_ID, LAST_NAME, SECTION.SECTION_ID, PERCENT_OF_FINAL_GRADE, GRADE_TYPE_CODE
FROM INSTRUCTOR, SECTION, GRADE_TYPE_WEIGHT
WHERE INSTRUCTOR.INSTRUCTOR_ID = SECTION.INSTRUCTOR_ID AND SECTION.SECTION_ID = GRADE_TYPE_WEIGHT.SECTION_ID AND PERCENT_OF_FINAL_GRADE = 25 AND GRADE_TYPE_CODE=’PA’
ORDER BY INSTRUCTOR.INSTRUCTOR_ID;
Another way:
SELECT instructor_id, section_id, last_name, percent_of_final_grade
FROM instructor JOIN section
USING (instructor_id)
JOIN grade_type_weight
USING (section_id)
WHERE percent_of_final_grade = 25
AND grade_type_code = ‘PA’
ORDER BY last_name;
4. select first_name, last_name, numeric_grade
from Student, grade
where Student.student_id = Grade.student_id and numeric_grade >= 99 and grade_type_code = ‘PJ’
order by first_name, last_name;
5. SELECT g.numeric_grade
FROM grade g
INNER JOIN student s ON g.student_id = s.student_id
WHERE s.zip = 10956
AND g.grade_type_code like ‘QZ’
ORDER BY g.numeric_grade DESC
6. SELECT c.course_no, s.section_no, i.first_name, i.last_name
FROM course c
INNER JOIN section s ON c.course_no = s.course_no
INNER JOIN instructor i ON s.instructor_id = i.instructor_id
WHERE c.prerequisite = 350
7. SELECT stud.student_id, i.instructor_id,
stud.zip, i.zip
FROM student stud, instructor i
WHERE stud.zip = i.zip
Problem “Lists the students and instructors that share zip codes”:SELECT stud.student_id, i.instructor_id,
stud.zip, i.zip
FROM student stud, enrollment e, section sec,
instructor i
WHERE stud.student_id = e.student_id
AND e.section_id = sec.section_id
AND sec.instructor_id = i.instructor_id
AND stud.zip = i.zip
Subqueries:
1. Select section_id
from enrollment
where student_id =
(Select student_id from student where first_name = ‘Thomas’ and last_name = ‘Thomas’);
3. SELECT course_no, description FROM course c
WHERE NOT EXISTS
(SELECT NULL FROM section s
WHERE c.course_no = s.course_no)
OR course_no IN
(SELECT course_no FROM section s2
WHERE NOT EXISTS
(SELECT NULL FROM enrollment e
WHERE s2.section_id = e.section_id));
SELECT * FROM course
LEFT JOIN section ON course.course_no = section.course_no
WHERE section.section_id NOT IN
(SELECT section_id FROM enrollment GROUP BY section_id)
OR course.course_no NOT IN
(SELECT course_no FROM section GROUP BY course_no)
4. The correlated subquery using the NOT EXISTS operator tests for NULL values which the NOT IN operator does not. Another consideration is the number of records returned by the outer query and the inner query. If the outer query returns a large number of records, the correlated subquery must test for each of these outer rows, which is very time-consuming. If the inner query returns only a very few records, the simple subquery is typically best. To determine the most efficient statement, test against realistic data volumes and properly indexed tables.
5. SELECT *
FROM
(SELECT STUDENT.ZIP, COUNT(STUDENT.ZIP) FROM STUDENT GROUP BY STUDENT.ZIP ORDER BY COUNT(STUDENT.ZIP) DESC) WHERE ROWNUM <=3;
Select s.*, rownum ranking
from
(SELECT zip, COUNT(*)
FROM student
GROUP BY zip
ORDER BY 2 DESC) s
where rownum <= 3;
Problem with above – Row 4 also has number as Row 3!
Part 3:
1. SELECT ORDER_t.Order_ID, ORDER_t.Customer_ID, ORDER_t.Order_Date,
Order_line_t.Product_ID, PRODUCT_T.Product_Description, Order_line_t.Ordered_Quantity
FROM Order_line_t, ORDER_t, PRODUCT_t
WHERE ORDER_t.Order_ID=Order_line_t.Order_ID AND
ORDER_line_t.Product_ID=PRODUCT_t.Product_ID AND
ORDER_t.Order_ID = 1;
2. SELECT Order_line_t.Product_ID, Product_t.Standard_Price,
Sum(Order_line_t.Ordered_quantity)*Product_t.Standard_Price
AS Total_Price
FROM Product_t,order_line_t
WHERE Product_t.Product_ID = Order_line_t.Product_ID
GROUP BY Order_line_t.Product_ID, Product_t.Standard_Price, Order_line_t.Order_ID
HAVING Order_line_t.Order_ID=1;
3. Save query2 or save results as view named order_1:
Select sum(order_1.total_price) as Total_Cost from order_1;
4. SELECT customer_t.Customer_Id
FROM customer_t
WHERE customer_t.Customer_Id Not In (select customer_id from order_t);
5. This query requires an outer join because some products may not have been ordered. Because many SQL systems do not have an outer join operator, often this type of query must use the UNION command. The following answer uses this second approach because it will work with almost any system. Also, note that the question wants the number of times a product has been ordered, not the total quantity ordered:
SELECT Product_t.Product_ID, Product_Description,
COUNT(*) as TimesOrdered
FROM Product_t,order_line_t
WHERE Product_t.Product_ID =
Order_line_t.Product_ID
GROUP BY Product_t.Product_ID, Product_Description
UNION
SELECT Product_ID, Product_Description, 0
FROM Product_t
WHERE NOT EXISTS
(SELECT * FROM Order_line_t
WHERE Order_line_t.Product_ID =
Product_t.Product_ID);
6. SELECT Product_t.Product_Id, Product_Description, Product_t.Standard_Price, TotCost
FROM Product_t, (SELECT Product_Id, SUM(Standard_Price*Goes_into_Quantity) as TotCost
FROM Uses, Raw_Material_t
WHERE Uses.Material_Id = Raw_Material_t.Material_id
GROUP BY Product_Id) as Cost_t
WHERE Product_t.Product_Id = Cost_t.Product_id;
Still waiting on the rest!