help with ddl please!!!!!

Question

help with ddl please!!!!!

Ehsan Ashouri

SSC Veteran

Points: 241
More actions
April 27, 2007 at 9:07 am

#177540
Im having problems not only with finding the correct syntax but trying to understand what they want the resulting output to look like...any help is GREATLY appreciated!
DDL for Schema:
CREATE TABLE DBTEST.JOBS
(
JOB_ID NUMBER(38) NOT NULL,
COMPANY_ID NUMBER(38) NOT NULL,
START_DATE DATE NOT NULL,
JOB_TYPE VARCHAR2(100) NOT NULL,
STATUS VARCHAR2(20) NOT NULL
)
CREATE TABLE DBTEST.SALARY_HISTORY
(
SALARY_HISTORY_ID NUMBER(38) NOT NULL,
SALARY NUMBER(7,2) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE NOT NULL,
EMPLOYEE_ID NUMBER(38) NOT NULL,
CONSTRAINT SYS_C0036283
FOREIGN KEY (EMPLOYEE_ID)
REFERENCES DBTEST.EMPLOYEES (EMPLOYEE_ID)
ENABLE
)
CREATE TABLE DBTEST.EMPLOYEES
(
EMPLOYEE_ID NUMBER(38) NOT NULL,
NAME VARCHAR2(40) NOT NULL,
MANAGER_ID NUMBER(38) NOT NULL,
JOB_ID NUMBER NULL,
CONSTRAINT FK_EMPLOYEE_JOB
FOREIGN KEY (JOB_ID)
REFERENCES DBTEST.JOBS (JOB_ID)
ENABLE
)
CREATE TABLE DBTEST.CURRENT_SALARY
(
CURRENT_SALARY_ID NUMBER(38) NOT NULL,
SALARY NUMBER(7,2) NOT NULL,
START_DATE DATE NOT NULL,
EMPLOYEE_ID NUMBER(38) NOT NULL,
CONSTRAINT SYS_C0036280
FOREIGN KEY (EMPLOYEE_ID)
REFERENCES DBTEST.EMPLOYEES (EMPLOYEE_ID)
ENABLE
)
Questions:
1. Provide a count of employees by manager id
2. Repeat the previous question but provide the managers name instead of the id
3. Repeat the previous query and order the output by the number of employees per manager
4. Repeat the previous query for managers with more than 2 employees
5. Provide a list of employees whose surname begins with ‘B’
6. Provide a list of employees ordered by highest current salary
7. Return a list of employees earning between £4000 and £5000
8. Get the Employee name of the longest serving employee. Using the min function. Assume all long serving employees will have a salary history.
9. Do the same query but use rownum instead of min.
10. Get a full (current and historical) salary history for the employee ‘Fred Brown’

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Only the person who requested this information can tell you what output is expected. While someone on this forum can probably help you with syntax, you should post what you've tried and ask for specific help.

You should also disclose if this is a school assignment. Most posters don't mind helping, but don't want to do your homework for you.

Greg

Ehsan Ashouri SSC Veteran Points: 241 More actions · Answer 2

its non assessed work that was given during lectures, im having real trouble getting to grips with the syntax and was hoping to get some more real world examples before my exam on monday.