MBA - MIS Concentration Student Page

This page provides answers for the SQL Problem sets.

SQL Exercise Set #2
1. Create a duplicate EMP table. Name it EMPTEST.		SELECT Emp.* INTO EmpTest FROM Emp;
2. Add a new column named SEX to the EMPTEST table with a datatype of character and a length of 1.		alter table emptest add column sex char(1)
3. The users have changed their minds. Instead of storing SEX as F or M, they want to use MALE or FEMALE. Increase the size of the SEX field accordingly.		alter table emptest alter column sex char(7)
4. Create a query named EMP_NO_MONEY with all columns from the EMP table except the SAL and COMM columns.		Double-clicked "Create query in Design View." Created the following SQL query: SELECT Emp.EmpNo, Emp.Ename, Emp.Job, Emp.Mgr, Emp.HireDate, Emp.DeptNo FROM Emp; Clicked the disk icon to save query, naming it "EMP_NO_MONEY"
5. Go to the menu Tools \| Options and select the View tab. Check the "System Objects" box. Select all columns from the system table MSysQueries..		Clicked Tools -> Options -> View. Checked "System Objects." Double-clicked "Create query in Design View." Created the following SQL query: MSysQueries.* FROM MSysQueries;
6. Using your query EMP_NO_MONEY, create a new query to display all employee names and hire dates.		Double-clicked "Create query in Design View." On "Show Table" dialog, clicked the "Queries" tab. Double-clicked the EMP_NO_MONEY list option. Clicked "Close." Created the following query: SELECT Emp_No_Money.Ename, Emp_No_Money.HireDate FROM Emp_No_Money;
7. What happens when you look for salary in EMP_NO_MONEY?		Since the salary column was not selected in the EMP_NO_MONEY query definition, it is not available for subsequent queries created with EMP_NO_MONEY as their source.
8. Create a query named EMP_DEPTNO_TEN that includes only the employee name, department number, and employee number from the EMP table. Construct the query so that only employees in department 10 are visible.		Double-clicked "Create query in Design View." Created the following SQL query: SELECT Emp.Ename, Emp.DeptNo, Emp.EmpNo FROM Emp where Emp.DeptNo=10; Clicked the disk icon to save query, naming it "EMP_DEPTNO_TEN"
9. Enter any query using EMP_DEPTNO_TEN.		Double-clicked "Create query in Design View." Created the following SQL query: Select Emp.Ename from EMP_DEPTNO_TEN order by emp.empno asc; Clicked the disk icon to save query, naming it "EMP_DEPTNO_TEN_NEW"
10. Delete the query.		drop table EMP_DEPTNO_TEN_NEW

SQL Problem Sets