Branch Data Length 4 60 60 8 Keys Primary Ke Column Name Branch no Street Cit Po
ID: 3909630 • Letter: B
Question
Branch Data Length 4 60 60 8 Keys Primary Ke Column Name Branch no Street Cit Postcode Data Type In Varchar Varchar In Staff Data Type Integer Integer Varchar Varchar Varchar Char Date Double Data Length 4 Keys Column Name Staff no Branch no Fname Lname Position Pri Ke Date of Birth Sala Salary v alue given in table is monthly salary Figure 2: Staff Brach Relation a) Create a database named 'Staff Brach b) Create the tables/schema given in Figure 2, using 'Staff Branch' database (18 marks) c) Write query to display Staff no, Fname and Lname and calculate employee's annual (2 marks) (5 marks) d) Write query to display the salary and calculate the estimated total salary for the next 5 years for all the employees without any increment associated to the salary (5 marks) e) Write SQL insert statement to insert the value given below into the Branch table (5 marks) Branch no Street Ci Postcode an street 12/33 Cheras 63450 f) Update the Branch information that you have just insert in question (e) with the Information given below (5 marks) Street Sultanah street 12/33 Ampang 63450 Branch no Ci PostcodeExplanation / Answer
If you have any doubts, please give me comment...
--a)
CREATE DATABASE `Staff Branch`;
--b)
CREATE TABLE Branch(
Branch_no INT(4) NOT NULL PRIMARY KEY,
Street VARCHAR(60),
City VARCHAR(60),
Postcode INT(8)
);
CREATE TABLE Staff(
Staff_no INT(4) NOT NULL PRIMARY KEY,
Branch_no INT(4),
Fname VARCHAR(30),
Lname VARCHAR(30),
Position VARCHAR(30),
Gender CHAR(2),
Date_of_Birth DATE,
Salary DOUBLE(10,2)
);
--c)
SELECT Staff_no, Fname, Lname, 12*Salary AS 'Annual Salary'
FROM Staff;
--d)
SELECT SUM(5*12*Salary) AS 'Salary'
FROM Staff;
--e)
INSERT INTO Branch VALUES(1111, 'Layman Street 12/33', 'Cheras', 63450);
--f)
UPDATE Branch SET Steet = 'Sultanah street 12/33', City = 'Ampang' WHERE Branch_no = 1111 AND Postcode =63450;