The topic of the ORACLE database please god help me to write a thank you-CodePudding

Please help me to look at the topic in SQL or deposit write thank you especially the second topic

CodePudding user response:

Let's build some data yourself out

CodePudding user response:

Homework yourself

CodePudding user response:

refer to the second floor chengccy response:

homework yourself

CodePudding user response:

SQL implementation is as follows:

 
- create employee basic information table 
The create table Staffer as 
Select 1 id, 'Jack' name, age 25, 'IT' dept from dual union all 
Select id, 2 'Tom' name, age, 'IT' dept from dual union all 
Select 3 id, 'Jessie' name, the age 27, 'Sale' dept from dual union all 
Select 4 id, 'Rose' name, age, 'Sale' dept from dual union all 
Select 5 id, 'Steven' name, age, 'Market' dept from dual union all 
Select 6 id, 'John' name, age, and 'Market' dept from dual 

- create timesheets, 
The create table Worktime as 
Select 1 id, ' 'at 8:45 intime,' that is' outtime, 1 daynumber from dual union all 
Select 1 id, '9' intime, 'number' outtime, 2 daynumber from dual 

- 1: be late, leave early, absenteeism employee names and dates, associated with the union all query 
- 1. Query late and leave early employee name and date 
The select a.n ame, b.d aynumber 
The from Staffer a, Worktime b 
Where Anderson, d=b.i d 
And ((case when Anderson ntime & gt; '9' then one else 0 end) + (case when a.o uttime & lt; Then one else 0 end) 18:00) & gt; 0 
Union all 
-- 2. Query the absence of the employee's name and date 
Select a t1. The name, t1. Daynumber 
The from (select Anderson d, a.n ame, b.d aynumber 
The from Staffer a, (select 1 + daynumber rownum - 1 the from dual connect by rownum & lt; B=30) 
) t1 
Left the join (select distinct id, daynumber from Worktime) t2 on t2. Id=t1. Id 
Where t2. Id is null; 

- 2: each department employees daily work time: 
The select a. d. ept, round (sum (b.h ours)/30/count (*), 2) avg_work 
The from Staffer a 
Left the join (select id, round (to_date (outtime, 'yyyymmdd hh24: mi: ss') - to_date (intime, 'yyyymmdd hh24: mi: ss'), 2) pump 
The from (select id, to_char (trunc (sysdate), 'yyyymmdd') | | "| | intime as intime, to_char (trunc (sysdate), 'yyyymmdd') | |" | | outtime as outtime 
The from Worktime 
) 
Group by a. d. ept;

CodePudding user response:

I ask, 4/f, I run the second question on the database and found a few braces I quote the parentheses after the lack of keyword I see what is missing for a while didn't find can help me see thanks

CodePudding user response:

And the b.h b there is no definition of ours

CodePudding user response:

You try this:

 
The select a. d. ept, round (sum (b.h ours)/30/count (*), 2) avg_work 
The from Staffer a 
Left the join (select id, round (to_date (outtime, 'yyyymmdd hh24: mi: ss') - to_date (intime, 'yyyymmdd hh24: mi: ss'), 2) pump 
The from (select id, to_char (trunc (sysdate), 'yyyymmdd') | | "| | intime as intime, to_char (trunc (sysdate), 'yyyymmdd') | |" | | outtime as outtime 
The from Worktime 
B) 
On Anderson, d=b.i d 
Group by a. d. ept;

CodePudding user response:

 
- Create table 
The create table T_STAFFER 
(
Id is an INTEGER, 
Name VARCHAR2 (50), 
Age is an INTEGER, 
Dept VARCHAR2 (20) 
); 


- Create table 
The create table T_WORKTIME 
(
Id is an INTEGER, 
Intime VARCHAR2 (20), 
Outtime VARCHAR2 (20), 
Daynumber INTEGER 
);

 
Prompt Importing table t_staffer... 
The set feedback off 
The set define off 
Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7369, 'SMITH', 29, '20'); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7499, 'Mr. ALLEN, 26,' 30 '); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7521, 'WARD, 25,' 30 '); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7566, 'JONES, 25,' 20 '); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7654, 'MARTIN, 34,' 30 '); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7698, 'BLAKE, 33,' 30 '); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7782, 'CLARK, 38,' 10 '); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7788, 'SCOTT, 32,' 20 '); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7839, 'KING, 29,' 10 '); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7844, 'TURNER, 21,' 30 '); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7876, ADAMS, 38, '20'); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7900, 'JAMES', 21, '30'); 

Insert into t_staffer (ID, NAME, AGE, DEPT) 
Values (7902, 'FORD, 36,' 20 '); 

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull