Oracle exercises with answers

Question: known company employee table EMP (EID, ENAME, BDATE, SEX, CITY ),

Department table DEPT (DID, DNAME, DCITY ),

Worksheet WORK (EID, DID, STARTDATE, SALARY ). The fields are described as follows:

EID-employee ID, which can contain a maximum of 6 characters. For example, A00001 (primary key)

ENAME-employee name, up to 10 characters. For example, SMITH

BDATE -- Date of birth, date type

SEX-gender of the employee, a single character. F or M

CITY: the CITY in which employees reside. It can contain up to 20 characters. Example: Shanghai

DID-department ID, up to 3 characters. For example, A01 (primary key)

DNAME -- department name, up to 20 characters. Example: R & D department

DCITY: the city where the Department is located. It can contain up to 20 characters. Example: Shanghai

STARTDATE: the date on which employees go to work in the Department. It is of the date type.

SALARY: the employee's SALARY. Integer.

Use Oracle SQL * plus to complete the following operations

1. Create tables EMP, DEPT, and WORK, and define the primary key and foreign key of the table.

2. insert appropriate data into each table. For example, insert the data of three departments and insert two employee data for each department.

3. query the basic information of all employees in the R & D department

4. query the basic information of the Department with the most employees (only the information of one department is required). If multiple departments have the same number of employees, then retrieve the basic information of the Department with the smallest number.

5. The number, name, and number of people in each department with more than 5 persons are displayed.

6. display the highest salary and minimum wage of each department with more than 5 employees

7. List the basic information of all employees whose employee numbers start with P to S.

8. Delete employees over 60 years old

9. Increase the salary of 10% for employees who have worked for more than 10 years

The answer is as follows:

Create table emp1 (
Eid number (6 ),
Ename varchar2 (20 ),
Bdate date,
Sex char (2 ),
City varchar2 (20)
);

Create table dept1 (
Did number (6 ),
Dname varchar2 (20 ),
Dcity varchar2 (20)
);

Create table work1 (
Eid number (6 ),
Did number (6 ),
Startdate date,
Salary integer
);

1.
Alter table emp1
Add constraint emp1_eid_pk primary key (eid );

Alter table dept1
Add constraint dept1_did_pk primary key (did );

Alter table work1
Add constraint work1_eid_fk foreign key (eid) references emp1 (eid );

Alter table work1
Add constraint work1_did_fk foreign key (did) references dept1 (did );

2.
Add data to emp1
Insert into emp1 (eid, ename, bdate)
Select empno, ename, hiredate
From emp;

Update emp1
Set (sex, city) = ('male', 'beijing)

Update emp1
Set sex = 'male', city = 'beijing ';

Add data to the dept1 table

Insert into dept1 (did, dname)
Select deptno, dname
From dept;

Update dept1
Set dcity = 'beijing ';

/Change one of the departments to development for subsequent exercises
Update dept1
Set dname = 'developer'
Where did = 10;

Add data to work1 table

Insert into work1 (eid, did, startdate, salary)
Values (2500, 10 );

Insert into work1 (eid, did, startdate, salary)
Values (2501, 10 );

Insert into work1 (eid, did, startdate, salary)
Values (2500, 10 );

3.
Select emp1.eid, emp1.ename, dept1.dname, work1.salary
From emp1, dept1, work1
Where emp1.eid = work1.eid
And dept1.did = work1.did;

4.

Select max (e. renshu), d. did
From (select did, count (eid) renshu
From work1
Group by did) e, dept1 d
Where d. did = e. did
Group by d. did;

5.

Select e. *, d. did, d. dname, d. dcity
From (select did, count (eid)
From work1
Group by did
Having count (eid)> 5) e, dept1 d
Where d. did = e. did;

6.

Select max (salary), min (salary)
From work1
Having count (eid)> 2
Group by did;

7.

Select ename

From emp1

Where ename like's %'

Or ename like 'P % ';

 

8.

Delete from emp1

Where months_between (sysdate, bdate)/12> 60;

9.

Update work1

Set salary = salary * 1.1

Where months_between (sysdate, startdate)/12> 10;