help with syntax (comming clean)

Question

Post reply

help with syntax (comming clean)

Ehsan Ashouri

SSC Veteran

Points: 241
More actions
April 27, 2007 at 2:04 pm

#173874

ill come clean, need to pass a basic SQL test before i can get an interview for a company but I am pretty rusty with my syntax and have no live server to test my answers on. I have done most of it but I have a feeling a lot of what i have done is incorrect and i badly need help with the last 3 questions. 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’
Answers
1.CREATE VIEW EMPCOUNT_MANID (MANAGER_ID, NUM_OF_EMP)
SELECT MANAGER_ID, COUNT(MANAGER_ID) FROM EMPLOYEES
2.CREATE VIEW EMPCOUNT_MANNAME(NAME, NUM_OF_EMP)
SELECT NAME, COUNT(MANAGER_ID)
FROM EMPLOYEES
GROUP BY NAME
3.CREATE VIEW EMPCOUNT_MANNAME (NAME, NUM_OF_EMP)
SELECT COUNT(MANAGER_ID)
FROM EMPLOYEES
GROUP BY NAME
4.CREATE VIEW EMPCOUNT_MANNAME (NAME, NUM_OF_EMP)
SELECT COUNT(MANAGER_ID)
FROM EMPLOYEES
GROUP BY NAME
WHERE NUM_OF_EMP > 2
5.CREATE VIEW EMP_SURNAME (SURNAME_B)
SELECT NAME
FROM EMPLOYEES
WHERE NAME LIKE 'B%'
6.CREATE VIEW EMP_HIGHEST (ID, SALARY_HIGHEST)
SELECT EMP_ID, SALARY
FROM CURRENT_SALARY
ORDER BY SALARY DESC
7.CREATE VIEW EMP_HIGHEST (ID, SALARY_HIGHEST)
SELECT EMP_ID, SALARY
FROM CURRENT_SALARY
WHERE CURRENT_SALARY >= 4000 AND <= 5000
8.SELECT EMPLOYEES.NAME
WHERE
MIN(START_DATE), END_DATE
WHERE
9.
10. SELECT CURRENT_SALARY

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

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Cross post

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)