MBA - MIS Concentration Student Page

This page provides answers for the SQL Problem sets.

SQL Exercise Set #1
1. Display each employee’s name and date of hire.		SELECT Emp.Ename, Emp.HireDate FROM Emp;
2. Display date of hire first.		SELECT Emp.HireDate, Emp.Ename FROM Emp;
3. Display employees earning more than $2000. Label the column name "Employee."		SELECT Emp.Ename AS Employee FROM Emp WHERE Emp.Sal > 2000;
4. Display employees who are not clerks or salespersons. Order the list by employee name.		SELECT Emp.* FROM Emp WHERE Not (Emp.Job="clerk") And Not (Emp.Job="salesman") ORDER BY Emp.Ename;
5. Display the employee name of those whose names contain the letter "S."		SELECT Emp.Ename FROM Emp WHERE Emp.Ename Like "S";
6. Display employee names and salaries for those having salaries less than their commission. Label the employee column "Name" and the salary column "Salary."		SELECT Emp.Ename AS Name, Emp.Sal AS Salary FROM Emp WHERE Emp.Sal < [emp!Comm];
7. Display the employees hired in 1981.		SELECT Emp.* FROM Emp WHERE DatePart("yyyy",[HireDate])=1981);
8. Display the names and jobs of all employees in departments 10 and 20.		SELECT Emp.Ename, Emp.Job FROM Emp WHERE Emp.DeptNo=10 OR Emp.DeptNo=20 ORDER BY Emp.Ename;