4 Replies - 3328 Views - Last Post: 26 April 2014 - 10:58 PM

#1 Asus93  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 22-July 13

Question on writing SQL Statement

Posted 26 April 2014 - 09:25 PM

From the tables that I attached,

a. Assume Job relation is created. Write the SQL statement that will create the EMP relation (use the structure shown in Table 2)

Following is my answer:

Create Table EMP
(empnum NUMBER(3),
lname VARCHAR2(15) CONSTRAINT emp_lname_nn Not Null,
fname VARCHAR2(25),
mi CHAR(1),
hiredate DATE CONSTRAINT emp_hiredate_nn Not Null,
enddate DATE,
Jobcode CHAR(3),
Salary Number(10,2)
CONSTRAINT emp_empnum_pk Primary Key (empnum),
CONSTRAINT emp_jobcode_fk Foreign Key (jobcode) REFERENCES Job(job_code);


b. Write the SQL statement that will enter the first data row into the EMP relation

My answer:
Insert into EMP values (101, 'O''Connel', 'John', 'mi', '11-Aug-2000', NULL, '502', 3600)


For both of this questions, can someone checks with me whether am I doing it correctly? Please point out my mistakes if any, I appreciate your guides.Thank you!

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: Question on writing SQL Statement

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 5061
  • View blog
  • Posts: 13,657
  • Joined: 18-April 07

Re: Question on writing SQL Statement

Posted 26 April 2014 - 10:15 PM

Well first of all you just need to do...

empnum NUMBER(3) PRIMARY KEY



... to specify that empnum is a primary key, no need for the pk constraint at the end. Also remember that you started with "(" at the beginning, so you will need a ")" for the end to wrap it all.

Then as for the insert remember to insert 'G' not 'mi'. 'mi' is the column, you are entering the data which is 'G' for John O'Connell.

That should get you pretty close to being bang on. :)
Was This Post Helpful? 1
  • +
  • -

#3 Asus93  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 22-July 13

Re: Question on writing SQL Statement

Posted 26 April 2014 - 10:34 PM

View PostMartyr2, on 26 April 2014 - 10:15 PM, said:

Well first of all you just need to do...

empnum NUMBER(3) PRIMARY KEY



... to specify that empnum is a primary key, no need for the pk constraint at the end. Also remember that you started with "(" at the beginning, so you will need a ")" for the end to wrap it all.

Then as for the insert remember to insert 'G' not 'mi'. 'mi' is the column, you are entering the data which is 'G' for John O'Connell.

That should get you pretty close to being bang on. :)/>/>


Hi,

Thanks for checking the code with me, I appreciate your help.
How about for this question:

Write a SQL statement to list the numbers of employee for each position,sort by the job description in descending order.

It should produce the result as attached.

May I know that how should I do work with it? Should I apply the JOIN statement onto it? If yes, how should it works? Thank you.

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#4 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 5061
  • View blog
  • Posts: 13,657
  • Joined: 18-April 07

Re: Question on writing SQL Statement

Posted 26 April 2014 - 10:40 PM

Yes you will need a join and also you will need to look into the "GROUP BY" clause and COUNT aggregate function. As for how it should work, that will be up to you. We are not here to do your homework. ;)
Was This Post Helpful? 1
  • +
  • -

#5 Asus93  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 22-July 13

Re: Question on writing SQL Statement

Posted 26 April 2014 - 10:58 PM

View PostMartyr2, on 26 April 2014 - 10:40 PM, said:

Yes you will need a join and also you will need to look into the "GROUP BY" clause and COUNT aggregate function. As for how it should work, that will be up to you. We are not here to do your homework. ;)/>


Hi,

Here's the statement I'm trying to write:

select job_desc, count(jobcode)
from emp, job
where emp.jobcode = job.jobcode
group by jobcode
and order by job_desc  desc


does it makes sense? Thanks for your guides!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1