-- Access SQL for chapter 13 Chapter 13 - Inner Joins select first_name, last_name, department_name from l_employees, l_departments; select first_name, last_name, department_name from l_employees, l_departments where l_employees.dept_code = l_departments.dept_code; select first_name, last_name, dept_code, department_name from l_employees, l_departments where l_employees.dept_code = l_departments.dept_code; select l_employees.first_name, l_employees.last_name, l_departments.dept_code, l_departments.department_name from l_employees, l_departments where l_employees.dept_code = l_departments.dept_code; select l_employees.first_name, l_employees.last_name, l_departments.dept_code, l_departments.department_name from l_employees, l_departments where l_employees.dept_code = l_departments.dept_code and l_employees.dept_code in ('Act', 'Exe', 'Mkt'); select l_departments.department_name, Count(l_employees.last_name) from l_employees, l_departments where l_employees.dept_code = l_departments.dept_code group by l_departments.department_name; select l_departments.department_name, Count(l_employees.last_name) from l_employees, l_departments where l_employees.dept_code = l_departments.dept_code group by l_departments.department_name having count(l_employees.last_name) > 1; select e.first_name, e.last_name, d.dept_code, d.department_name from l_employees e, l_departments d where e.dept_code = d.dept_code; -- 13-3 select a.fruit, a.f_num, b.c_num, b.color from sec1303_fruits a, sec1303_colors b where a.f_num = b.c_num order by a.fruit; -- 13-4 select a.fruit, a.f_num, b.c_num, b.color from sec1304_fruits a, sec1304_colors b where a.f_num = b.c_num order by a.fruit; -- 13-5 select a.fruit, a.f_num, b.c_num, b.color from sec1305_fruits a, sec1305_colors b where a.f_num = b.c_num order by a.fruit; -- 13-6 select a.fruit, a.f_num, b.c_num, b.color from sec1306_fruits a, sec1306_colors b where a.f_num = b.c_num order by a.fruit; -- 13-7 select a.fruit, a.f_num, b.c_num, b.color from sec1307_fruits a, sec1307_colors b where a.f_num = b.c_num order by a.fruit; -- 13-8 select a.fruit, a.f_num, b.c_num, b.color from sec1308_fruits a, sec1308_colors b where a.f_num = b.c_num order by a.fruit; -- 13-9 Variation 1 — This is the best solution select a.fruit, a.f_num, b.c_num, b.color from sec1309_fruits a, sec1309_colors b where a.f_num = b.c_num order by a.fruit; -- 13-9 Variation 2 select a.*, b.* from sec1309_fruits a, sec1309_colors b where a.f_num = b.c_num order by a.fruit; -- 13-9 Variation 3 select fru.fruit, fru.f_num, col.c_num, col.color from sec1309_fruits fru, sec1309_colors col where fru.f_num = col.c_num order by fru.fruit; -- 13-9 Variation 4 select sec1309_fruits.fruit, sec1309_fruits.f_num, sec1309_colors.c_num, sec1309_colors.color from sec1309_fruits, sec1309_colors where sec1309_fruits.f_num = sec1309_colors.c_num order by sec1309_fruits.fruit; -- 13-9 Variation 5, Fine for computers -- confusing for people, Not recommended select fruit, f_num, c_num, color from sec1309_fruits, sec1309_colors where f_num = c_num order by fruit; -- 13-10 select a.f_num_1, a.f_num_2, a.fruit, b.c_num_1, b.c_num_2, b.color from sec1310_fruits a, sec1310_colors b where a.f_num_1 = b.c_num_1 and a.f_num_2 = b.c_num_2 order by a.fruit; -- 13-11 select a.student_name, a.test_score, b.letter_grade from sec1311_student_scores a, sec1311_grade_ranges b where a.test_score between b.beginning_score and b.ending_score order by a.student_name; -- 13-12 select a.*, b.* from sec1312_bigger_numbers a, sec1312_smaller_numbers b where a.larger_number > b.smaller_number order by a.larger_number, b.smaller_number; -- 13-13 select a.fruit, a.f_num, b.c_num, b.color from sec1313_fruits a, sec1313_colors b where a.f_num + b.c_num = 6 order by a.fruit; -- 13-14 Older syntax select '13-14 Older syntax' as Section from dual; select a.fruit, a.f_num, b.c_num, b.color from sec1314_fruits a, sec1314_colors b where a.f_num = b.c_num order by a.fruit; -- 13-14 Newer syntax select a.fruit, a.f_num, b.c_num, b.color from sec1314_fruits a inner join sec1314_colors b on a.f_num = b.c_num order by a.fruit; -- 13-15 select a.employee_id, a.first_name, a.last_name, a.dept_code, b.department_name from l_employees a, l_departments b where a.dept_code = b.dept_code order by a.employee_id; -- 13-16 select a.employee_id, a.first_name, a.last_name, a.dept_code, b.department_name from l_employees a, l_departments b where a.dept_code = b.dept_code and a.employee_id < 206 order by a.employee_id; -- 13-16 Step 1 — Create the inner join of the tables select a.*, b.dept_code as dept_code2, b.department_name from l_employees a, l_departments b where a.dept_code = b.dept_code; -- 13-16 step 1, see the view select * from sec1316_view; -- 13-16 Step 2 — Select part of the data from the sec1316 table select employee_id, first_name, last_name, dept_code, department_name from sec1316_view where employee_id < 206 order by employee_id; -- 13-17 select a.employee_id, a.first_name, a.last_name, count(*) as number_of_lunches from l_employees a, l_lunches b where a.employee_id = b.employee_id and not (a.employee_id = 208) group by a.employee_id, a.first_name, a.last_name having count(*) > 1 order by a.employee_id; -- 13-19 select '13-19' as Section from dual; select a.employee_id, a.first_name, a.last_name, b.lunch_date, d.description, c.quantity from l_employees a, l_lunches b, l_lunch_items c, l_foods d where a.employee_id = b.employee_id and b.lunch_id = c.lunch_id and c.supplier_id = d.supplier_id and c.product_code = d.product_code and a.dept_code = 'SHP' order by a.employee_id, b.lunch_date;