Question

Examine the Employees table. Note that the data in the Title field contains redundant values and therefore is a candidate for a one-field lookup table. Use your Access Query Design View or SQL skills to create a lookup table named Titles with the unique values in the Title field of the Employees table. List the steps you performed to create the lookup table.

Answer 1

Following is the lookup table for the EMP table

#creating table according to the given constraints. Note that each field has to be set not null, which are mentioned in the question

CREATE TABLE Employee (EmpId int NOT NULL, LastName character varying NOT NULL,
FirstName character varying NOT NULL, HireDate character varying NOT NULL, 
Title character varying NOT NULL, Salary int varying NOT NULL );

CREATE TABLE links_type_lookup (EmpId int NOT NULL, LastName character varying NOT NULL,
FirstName character varying NOT NULL);


#alter table is used to change the table, here we are setting the primary key using alter table

ALTER TABLE ONLY links_type_lookup ADD CONSTRAINT links_type_lookup_pkey PRIMARY KEY (EmpId);