Q1: Assume the EMP and DEPT tables as described in Oralce 11g:
EMP (Eno, sal, Dno);
DEPT (Dno, Mgr, Tot_Emp_sal);
Write a combination of Triggers and stored procedures that make sure of the integrity of this
database when employees are added, deleted and their salaries are updated. Note: the salary of an
employee is calculated in the Tot_Emp_sal of the dept table.
Q2: Assume the following two tables in SQL Server 2008:
EMP (Eno INTEGER, sal DECIMAL(6,2), Dno INTEGER);
DEPT (Dno INTEGER, tot_emp_sal DECIMAL(8,2));
(A) Write a stored procedure that when given a Dno and a salary amount as input it adds the
salary amount to total salary amount of DEPT table.
(B) Write a trigger that is fired when employee’s salaries are updated in EMP. When this
happens, the trigger uses the stored procedure in (A) to adjust the tot_emp_sal in the
department where the employees work in. Note; this requires working with cursors in
SQL Server 2012
Q3: Briefly explain the problems you see with the following design:
CREATE TABLESPACE MyTableSpace
DATAFILE ‘File-1.dat’ SIZE 2 M REUSE,
‘File-2.dat’ SIZE 2 M REUSE,
‘File-3.dat’ SIZE 1 M REUSE
DEFAULT STORAGE (INITIAL 3M MINEXTENTS 2 PCTINCREASE 15);
Q4: Assume database seis733 has two file groups, Primary and MyFG. Currently, the filegroup
MyFG is set to default. MyFG has file1 and file2 in it. Tables T1 and T2 have been created on
MyFG filegroup. Both file1 and file2 have some unused space. To make space available, we need
to remove file2 from MyFG. Write the scripts that achieve this.
Q5: The following user has been created by the DBA and the specific rights have been granted.
SQL> create user TWS1
2 identified by password
3 account unlock;
User created.
SQL> grant connect, resource to TWS1;
Grant succeeded.
Write the scripts to show:
1/ Which tablespace this user is assigned to:
2/ That table T(a varchar2(10)) that this user creates is NOT located in the default tablespace that
this user is assigned to.