Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL express version Expand / Collapse
Author
Message
Posted Saturday, September 14, 2013 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 14, 2013 2:16 PM
Points: 3, Visits: 7
Hi Guys,

I know I might be in the wrong topic group but I am new here and don't know how to use it.

I am currently doing sql at UNISA and I have an sql assignment question that I don't know how to complete.

The question reads as follows:

The HR department wants to get a list of underpaid employees, salary history of employees and the salary history of managers based on an industry salary survey.
You were requested to create a SQL statement (save it as A2Q10) to do the following:
Retrieve the details of the employee ID, hire date and manager ID of those employees whose mangers are 101, 102, 103 and 108 and who is in the IT department.
If the salary is less than a certain AMOUNT (request the user to dynamically insert this value),
o insert the details of employee ID and salary into the SPECIAL_SAL table.
o Insert the details of employee ID, hire date and salary into the SAL_HISTORY table.
o Insert the details of employee ID, manager ID and salary into the MGR_HISTORY table.

I know how to retrieve the data but I don't know how to insert the specific data into those tables.

I know how to insert new data in a table, but don't know how to combine it.

PLEASE HELP...... question counts like 15 marks.
Post #1494811
Posted Saturday, September 14, 2013 8:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
I'm not going to do your homework for you. You learn nothing, I don't need the practice and I'm sure your lecturer doesn't condone other people doing your work for you either (I'm also studying at UNISA, I'm pretty sure I know the person who teaches that course)

What have you got so far?
Where exactly are you stuck?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1494813
Posted Saturday, September 14, 2013 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 14, 2013 2:16 PM
Points: 3, Visits: 7
well what I have is the following:

the code for retrieving the data:

SELECT e.employee_id, hire_date, e.manager_id, e.salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'IT'
AND e.manager_id IN (101, 102, 103, 108)

now the part I am stuck at is this:
If the salary is less than a certain AMOUNT (request the user to dynamically insert this value),
o insert the details of employee ID and salary into the SPECIAL_SAL table.
o Insert the details of employee ID, hire date and salary into the SAL_HISTORY table.
o Insert the details of employee ID, manager ID and salary into the MGR_HISTORY table.

if I run the part that I have it all works fine, but I don't know how to combine that to insert data in a table if my salary is say like less than 6000
Post #1494816
Posted Saturday, September 14, 2013 10:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
First things first, take that query you already have and figure out how to have it only return rows for salary under a specific value (which from the reading I would assume would be a variable)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1494821
Posted Saturday, September 14, 2013 11:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 14, 2013 2:16 PM
Points: 3, Visits: 7
can you use a CASE statement with this question?

it looks like it can but at the other end it also looks like it cant
Post #1494828
Posted Sunday, September 15, 2013 5:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
You don't need a case statement here.

Take the query you posted, figure out how to have that query only return salary values that match the requirement.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1494876
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse