And answer update emp ceca eaf docxset emp where emp

ITECH 2004 DATA MODELLING

Answer:
(1) Deleting primary records
(2) Updating of primary records
(3) Inserting of dependent records

4. What is a sequence? Write its syntax.

Its purpose is to automate related, repetitious tasks with a database and to ensure that data is consistent across the database wherever it is stored.

An example of it is:
John is a new employee at Super Cyber Store (SCS). Like most companies, SCS has a few different applications that will need access to John's information, like Human Resources, Payroll, and Benefits.

For example:

Exercises

7. The ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure and contents of the ConstructCo database are shown in Figure E8.7.

a. Download the following file from moodle: Week 8: Lab Files / 08_ConstructCo_MySQL.txt

CRICOS Provider No. 00103D 9e29ceca9673f2468fa2e43854d96eaf67c1e8e5.docx Page 3 of 8

Answer:
Query:
CREATE TABLE EMP_1(
EMP_NUM CHAR (3) PRIMARY KEY,
EMP_LNAME VARCHAR (15) NOT NULL,
EMP_FNAME VARCHAR (15) NOT NULL,
EMP_INITIAL CHAR (1)
EMP_HIREDATE DATETIME,
JOB_CODE CHAR (3),
FOREIGN KEY(JOB_CODE) REFERENCE JOB);

d. Having created the table structure in Exercise 7 (b), write the SQL code to enter the first two rows for the table shown in Figure E8.7d. Each row should be inserted individually, without using a subquery. Insert the rows in the order that they are listed in the figure.

Answer:
CREATE TABLE EMP_1
( EMP_NUM char(3) NOT NULL,
EMP_LNAME varchar(15),
EMP_FNAME varchar(15) ,
EMP_INITIAL char(1),
EMP_HIREDATE date,
JOB_CODE char(3)
);
Inserting Rows:
INSERT INTO EMP_1
(EMP_NUM,EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_HIREDATE,JOB_CODE) VALUES
(101,'News','John','O',08-NOV-00,502);

INSERT INTO EMP_1

(102,'Senior','David','M',12-JULY-89,501);

e. Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from the EMPLOYEE table into the EMP_1 table. Remember, your sub- query should only retrieve the columns needed for the EMP_1 table and only the employees shown in the figure.

g. Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107.

j. Using the EMP_2 table, write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to EMP_2. The EMP_PCT is the bonus percentage to be paid to each employee.

The new attribute characteristics are:

ADD COLUMN EMP_PCT NUMBER(4,2),

ADD COLUMN PROJ_NUM CHAR(3);