| SQL Exercise Set #4 |
| 1. Insert yourself as a new employee into the EMP
table. |
|
insert into emp(empno, ename, job, mgr, hiredate,
sal, comm, deptno)
values(8120, "JONES", "Goddess", 8120, #01/01/2003#, 500000, 5000, 10) |
| 3. How can you insert other rows into the EMP
table prompting the operator for the employee number, department number, hire
date, and employee name. |
|
insert into emp(empno, ename, job, mgr, hiredate,
sal, comm, deptno)
values([Employee Number: ], [Employee Name: ], "Goddess", 8120, [HireDate: ],
500000, 5000, [Department Number: ]) |
| 4. Add a new department into the DEPT table with
a department number of 99, location of Maui, and a department name of
Education. |
|
insert into DEPT(DeptNo,Dname,Loc)
values(99,"Maui","Education") |
| 5. Update your own employee data by giving
yourself a $1000 per month raise. |
|
update emp set sal=[sal]+1000
where ename="JONES" and empno=8120 |
| 6. Delete yourself from the EMP table. |
|
delete from emp where ename="JONES" and
empno=8120 |
| 7. Re-create the query named EMP_DEPTNO_TEN.
|
|
-
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"
|
| 8. Save the query in the database. |
|
SEE ANSW 7 (Clicked the disk icon to save query,
naming it "EMP_DEPTNO_TEN") |
| 9. Update KING’s department number from 10 to 20
using this query. |
|
-
clicked the datasheet view.
-
Found the record with employee name "KING"
-
Typed 20 in the Dept Number field.
|
| 10. Use a query to see if KING is still in your
view or query. |
|
-
Created a new query in design view
- Ran the following sql statement:
SELECT Emp_deptno_ten.Ename, Emp_deptno_ten.DeptNo, Emp_deptno_ten.EmpNo
FROM Emp_deptno_ten
WHERE (((Emp_deptno_ten.Ename)="KING"));
-
The query contained no records, so King was no longer contained in
Emp_Deptno_Ten.
|
| 11. Delete (or at least don't save) the query. |
|
Closed the query window, answering "no" when
prompted to save changes. |
| 12. Change KING’s department number back to 10. |
|
update emp set deptno = 10 where ename="KING" |
| 13. How can you force EMP_DEPTNO_TEN to validate
that a Department exists before updating new changes. |
|
- Set the "DeptNo" field in the Dept table to be
a primary key.
- Open Tools->Relationships
- Drag the cursor from the Dept table DeptNo
field to the Emp table deptNo field. Click on the enforce referential
integrity option. Save and close the relationships window.
- After this, when you open the emp table and try to add a department that doesn't exist in the dept table, the following message is received.
 |
| 14. What happens when you update KING’s
department from 10 to 20?
|
|
The record saves without incident since department 20 exists in the dept table. |
| 15. Try to insert a duplicate employee into the
EMP table to test the index created in section 3. |
|
Received the following error message:
 |
| 16. Insert several new departments and see what
new sequence numbers are created. How can you make deptno go up in a series of
10s.
|
|
Because it is not an autonumber field, no sequence numbers were automatically created for the deptno field. Instead, the value of 0 was placed by default into the field. However, you can add another autonumber field in a Data Definition query to accomplish something like this. The sql would be as follows:
alter table dept
add column deptnumber counter (100,10)
constraint pkDept PRIMARY KEY
The result of this would be that all new numbers in the deptnumber field would be added in increments of ten, starting at 100 (since we added dept 99 in a previous question). |
| 17. Enter a query to observe your newly inserted
departments. |
|
select deptno as Department from dept where deptno < 100
UNION
select deptnumber as Department from dept where deptnumber > =100;
The result is:
|