There are three relations in the following schema for the company: 1. EMPLOYEE (National_ID, Employee_Name, Salary, Service_Unit), 2. EMPLOYER (National_ID, Employer_Name, Salary, Service_Unit), 3. UNIT (Service_Unit, Contact_Phone_Number). Primary key has been underlined. The data stored in the schema has been shown as follows:
Write SQL commands for the following instructions one-by-one (24%, 4 points each):
1. Please add a new record of employee with the National ID: P129799311, Name: Nohara Hiroshi, Service unit: Kasukabe Defence Force, Salary: $367,000.
2. We would like to raise the salary of Isaac Netero to $65,000.
3. Please list all the information of employee with the salary over $75,000 or between $30,000 and $40,000.
4. Please calculate the average salary and the total number of employees in the company.
5. If the company decides to raise the salary of the employers by 5%, please list the employers’ name and the new salary.
6. Please list all the employees’ name and order with the salary.
1) insert into employee values(‘P129799311′,’Nohara Hiroshi’,367000,’Kasukabe Defence Force’);
2) update employer set salary=65000 where employer_name=’Isaac Netero’;
3) select * from employee where salary>75000 or salary between 30000 and 40000;
4) select avg(salary),count(national_id) from employee;
5) select employer_name,salary*0.05 from employer;
6) select * from employee order by salary;
CREATE TABLES BY YOUR OWN