Question

Task 1: Use the HR schema to implement the following scenario.

The megacorp company want to expand its operation to New Zealand (NZ) by establishing a new department “Research and Development” department ID “280” located in Auckland city. The new department has the location ID “3300”, address “AUT City Campus, WT Building” and postal code “1010”. You and 4 of your friends are hired to work in the new department as “IT_PROG”. This position comes with a salary package of 5000 and does NOT include any commission percentage. All employees in the new department are hired on 1 st of March, 2016 and will report to the manager “Steven King (Emp ID: 100)”. The new 5 employees will take the Employee ID values of (207 – 211) and must get email IDs according to the format used in the employees table. Implement the scenario above by inserting the necessary data into (employees, departments, locations, countries) tables.

Task 2: Use the HR schema to implement the following scenario. You did an excellent work during the past few weeks and your manager decided to give you a commission of 0.05%. Update your record in the employees table to give yourself the 0.05% commission.

Answer 1

Answer: Task 1) CREATE TABLE Department ( Did INT Lid INT Dname VARCHAR (20) PRIMARY KEY (Did) FOREIGN KEY (Lid) REFERENCES Project (pid) NOT NULL, NOT NULL, NOT NULL, INSERT INTO Department (Did, Lid, Dname) VALUES (280,3300, 'Research and developement) CREATE TABLE Location ( Lİd INT Lname VARCHAR (20) Address VARCHAR (20) Country VARCHAR (20) Postal code INT PRIMARY KEY (Lid) NOT NULL, NOT NULL, NOT NULL NOT NULL NOT NULL

INSERT INTO Location (Lid, Lname, Address, Country, Postal_code) VALUES (3300 'Auckland city ', 'AUT City Campus, WT Building', 'New Zealand,1010); CREATE TABLE Employee( Empid INT Ename VARCHAR (20) Did INT Email VARCHAR (20) Hired on DATE NOT NULL, Salary INT Commision INT PRIMARY KEY (Empid) NOT NULL NOT NULL NOT NULL NOT NULL, NOT NULL, NOT NULL FOREIGN KEY (Did) REFERENCES Project (Pid) INSERT INTO Employee (Empid, Ename, Did, Email,Hired on, Salary, Commision) VALUES (207, 'sharmi', 280, [email protected], 1/03/2016',5000, 0)

Sample output: sh-4.3 sqlite3 database.sdb < main.sql 2801 3300| Research and developement 3300 Auckland city AUT City Campus, WT Buildingl New Zealand | 1010 207 sharmi|280 [email protected] 1/03/2016 5000le 288l keerthil 280 [email protected] 1/03/20161 50001e 209l ilamathi | 280l [email protected] 1/03/20165e00le 2101 Anul2801 [email protected]] 1/03/20165e00le 211 Anushyal 280 [email protected] 1/03/2016| 500e1e Task 2) table set commission as 5 for particular Empid: UPDATE Employee SET Commision 5 WHERE Empid-209: select * from Emp loyee:

sample output: 207 sharmi 280 [email protected] 1/03/2016 50ee e 208l keerthil280| [email protected]| 1/03/2016| 500ele 209 l ilamathiļ2801 [email protected] 1 1/e3/2016150ee15 210l Anu 280 [email protected] 1/03/2016|50eele 211 | Anushyal 288| [email protected] 1 1/θ3/20161500010