help with syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with syntax

Author

Topic

fastshadow
Starting Member

2 Posts

Posted - 2007-04-27 : 16:11:05

hi i 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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-27 : 16:16:57

Duplicate:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82793

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources