1. (1.5+1.5+1.5+2.5 pts) Assume the EMPLOYEE table has the following rows ID 100
ID: 3595059 • Letter: 1
Question
1. (1.5+1.5+1.5+2.5 pts) Assume the EMPLOYEE table has the following rows ID 100 SALES 101 RD 102 HR 103 SALES 104 RD DEPT SALARY 40000 38000 60000 58000 70000 NAME Smith Terry David Ellie Judy The DEPARTMENT table has the following rows: DEPTNAME RD SALES HR PHONE 8596001234 8596005555 B596009876 Write SQL statements to perform the following tasks: a) Find the employees whose salaries will reach between 40000 and 60000 if the salaries are increased by 20%. The query result should look as follows: ID 100 101 SALARY 40000 38000 b) Find the phone of the departments that Judy works in. c) Find the number of employees for every department. The query result should look as follows: DE PT HR RD SALES EMPNUM d) The company only has three departments, i.e., SALES, HR and RD. Assume the EMPLOYEE table was created as a partitioned table such that employees in different departments would be placed into different partitions. The table has the following constraints: The ID is the primary key Any value that appears in the DEPT column should also exist in the DEPARTMENT table .The value for the NAME column can never be nul What should be the statement used to create the EMPLOYEE table?Explanation / Answer
a)SELECT ID FROM EMPLOYEE WHERE 1.2*SALARY BETWEEN 40000 AND 60000
here we are using the BETWEEN clause to check between the two values after increasing salary to 20%(multiplying by 1.2).
b)SELECT PHONE FROM EMPLOYEE NATURAL JOIN DEPARTMENT WHERE EMPLOYEE.DEPT = DEPARTMENT.DEPTNAME AND NAME='Judy'
here we are using the natural join to join two tables on the basis of dept. and checking name as Judy
c)SELECT DEPT, COUNT(ID) FROM EMPLOYEE GROUP BY DEPT
here we are using the groupby clause to group all employee on the basis of department .
d)CREATE TABLE EMPLOYEE(
ID int PRIMARY KEY,
DEPT char(20),
NAME char(20) NOT NULL,
FOREIGN KEY(DEPT),
REFERENCES DEPARTMENT(DEPT)
);
here we are using the create table command to create our table where all constraints are defined and foreign key is used so that any value that appear in DEPT also appear in DEPARTMENT table
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.