help with syntax
-
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
WHEREMIN(START_DATE), END_DATE
WHERE9.
10. SELECT CURRENT_SALARY
-
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) -
ethically now allowed.
-
if ur not willing to help dont post up replies
-
The syntax for creating the VIEWs does not look like SQL Server syntax.
Which DBMS are you using?
Oracle or DB2? VARCHAR2 is not available in SQL Server.
Maybe you should try a different forum more suited for your DBMS.
N 56°04'39.16"
E 12°55'05.25" -
Oracle is my guess. Also the spelling and grammar in this cross post has magically improved. And who says cross-posting is now ethically allowed (apart from Muhammad Abbars of course) ?
DB
The systems fine with no users loggged in. Can we keep it that way ?br> -
I think Muhammad meant "Ethically NOT allowed" in regards to asking for answers to interview questions.
-SQLBIll
-
I'd agree with that, and apologise.
DB
The systems fine with no users loggged in. Can we keep it that way ?br> -
While I agree with some of the other posts that nobody should take your test for you I'll assume you have a legitimate interested in learning the answers to these questions and are not simply going to plagiarize them. (If I'm wrong I doubt you would be able to get past the oral interview anyway). Based on the schema this looks like an Oracle DB, but since we are in a SQL Server forum I'll answer for a SQL Server DB.
Now having said that I hope I don't embarrass myself by making any really stupid mistakes here 🙂
--Q1. Count of employees by manager id
select manager_id, count(*)
from employees
group by manager_id
--Q2. Above with mangers name
select (select name from employees m where m.employee_id = e.manager_id), count(*)
from employees e
group by e.manager_id
--Q2. Same as above but with self join
--NOTE: Won't pickup any employees without a manager.
select m.name, count(*)
from employees e join employees m on (e.manager_id = m.employee_id)
group by m.name
--Q3. Above ordered by number of employees per manager (assuming highest to lowest)
select m.name, count(*)
from employees e join employees m on (e.employee_id = m.manager_id)
group by m.name
order by count(*) desc
--Q4. above for managers with more than 2 employees
select m.name, count(*)
from employees e join employees m on (e.employee_id = m.manager_id)
group by m.name
having count(*) > 2
order by count(*) desc
--Q5. List of employees with surnmaes beginning with B
--Assumption: SQL server is RDBMS (not sure if 'like' is ANSI standard), and all employee names are Last, First MI
select name
from employees
where name like 'B%'
--Q6. List of employees ordered by highest current salary
--Assumption: Current_Salary table is 1 to 1 with employee (not a good assumption)
select name, salary
from employees join current_salary
order by salary desc
--Q7. List of employees earning between 4000 and 5000
--Assumption: Salary is already in pounds not dollars
select name
from employees join current_salary
where salary between 4000 and 5000
--Q8. Name of longest serving employee
select name
from employees join salary_history
where start_date = (select min(start_date) from salary_history)
--Q9. Same as above but with rownum
--NOTE: I've only worked with oracle minimally so only have a passing familiarity with ROWNUM but my understanding
-- is physically part of each table and it increments upwards with each insert.
-- Therefore using that and the following assumption:
--Assumption: Records were inserted into employee table as they joined the company and have never been deleted/reorganized
--NOTE: I doubt this is what they are looking for, but from the question....
select min(rownum), name
from employees
--Q10. Current and historical salary for fred brown
--Assumption: To keep the query simply I'll assume the name field stores First Name Last Name now.
select name, salary
from employees join current_salary
where name = 'Fred Brown'
union
select name, salary
from employees join salary_history
where name = 'Fred Brown'
-
Nice gesture... but a really, really bad thing to do...
--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) -
Yes, and he hasn't even had the courtesy to say thank you. No doubt you've already added him to your mental list of users that you won't bother helping again... or am I the only one who has one of those?
John
-
Based on the dates I figured his interview had already passed, and based on his attempted answers he probably bombed badly and has moved on to another project. Though in fairness he did say he was not a SQL person, JUST and application developer
, though I have never worked on an application that did not involve a database (guess that comes with only working on business applications).James.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply