IM Multi Tables

• List the employee name and the department name from the tables employee and department using cross join or cartesian product

  SELECT empname, deptname

  FROM employee, department;

  
• List the employee name and his corresponding department name

  SELECT empname, deptname

  FROM employee, department

  WHERE employee.deptid = department.depID;
• write an sql statement to prepare a list with customer ID, customer name, and the name of his corresponding salesman

  SELECT customer_id, cust_name, name

  FROM customer, salesman

  WHERE customer.salesman_id = salesman.salesman_id;

  
• write an SQL statement to make a list of customer name and his city with his order no, purchase amount for those orders with order amount between 500 and 2000. use alias for the tables

  SELECT cust_name, city, ord_no, purch_amt

  FROM customer AS C, orders AS O

  WHERE C.cust_id = O.cust_id AND O.purch_amt BETWEEN 500 AND 2000

  
• GENERAL RULE

  The number of tables minus one (n-1) is USUALLY the LEAST number of join predicates needed fro the query, to ensure that there are no un-linked tables.