Delete records from one table using join with another table? Delete records from one table using join with another table.
Ok, the mission is to delete all employee records if in the title of their department can be 
found word "sales". It can happen in the real life. Imagine that newlly elected CEO decides 
to close all sales operations.

this my Query:

DELETE *
FROM Employees
WHERE DeptNo IN
  (SELECT DeptNo
  FROM Departments
  WHERE LCase(DeptName) LIKE '*sales*') 


but i got error. SQL Syntax error.Please help me.
Remove the star! The syntax (see the DELETE page at MSDN), can be either:

DELETE
FROM Employees
...


or 

DELETE Employees
FROM Employees
...


or 

DELETE e
FROM Employees AS e
...


You probably don't need the stars in the '*sales*' string either. If you mean to find the rows with 'sales' string anywhere in that column, the wild card is % (see the LIKE page):

DELETE e
FROM dbo.Employees AS e
WHERE e.DeptNo IN
  (SELECT d.DeptNo
  FROM dbo.Departments AS d
  WHERE d.DeptName LIKE '%sales%') ;


I removed the LCase() as well. That is an Access function, not an SQL-Server one (thnx @Aaron). Add back (the equivalent LOWER()) if your database (or table) uses a case sensitive collation (the default is case insensitive so usually there is no need for such functions.)