On Sep 21, 10:11 am, stevenleong...@gmail.com wrote:
> I am new to SQL query. Can someone help me to solve this question?
>
> 1. Given the following schema of an employees table:
>
> *employees (*
>
> * empid integer primary key, -- employee id number*
>
> * dept string, -- the employee's department number*
>
> * salary float, -- salary of the employee*
>
> * mgrid integer -- employee id number of employee's
> manager*
>
> *)*
>
> Write the query (or queries if necessary) needed to count the number
> of employees in each employee's department who are paid more than
> their manager.
> All employees (including managers) appear in this table.
> Each employee has a manager (except the CEO), and the mgrid in the
> employee's row is the empid of the employee's manager.

On Sep 21, 4:20 pm, amish <shahami...@gmail.com> wrote:
> On Sep 21, 10:11 am, stevenleong...@gmail.com wrote:
>
>
>
>
>
> > I am new to SQL query. Can someone help me to solve this question?
>
> > 1. Given the following schema of an employees table:
>
> > *employees (*
>
> > * empid integer primary key, -- employee id number*
>
> > * dept string, -- the employee's department number*
>
> > * salary float, -- salary of the employee*
>
> > * mgrid integer -- employee id number of employee's
> > manager*
>
> > *)*
>
> > Write the query (or queries if necessary) needed to count the number
> > of employees in each employee's department who are paid more than
> > their manager.
> > All employees (including managers) appear in this table.
> > Each employee has a manager (except the CEO), and the mgrid in the
> > employee's row is the empid of the employee's manager.
>
> create table employees (
> empid integer primary key, -- employee id number*
> dept varchar(100), -- the employee's department
> number*
> salary float, -- salary of the employee*
> mgrid integer -- employee id number of employee's
> )
>
> insert into employees values (1,'CEO',10000,null)
> insert into employees values (2,'dep1',5000,1)
> insert into employees values (3,'dep1',5000,1)
> insert into employees values (4,'dep2',6000,2)
> insert into employees values (5,'dep2',4000,2)
> insert into employees values (6,'dep2',7000,3)
> insert into employees values (7,'dep2',4000,3)
>
> select e.* from employees e inner join employees e1 on e.mgrid =
> e1.mgrid and e.salary > e1.salary- Hide quoted text -
>
> - Show quoted text -

On Sep 21, 4:22 am, amish <shahami...@gmail.com> wrote:
> On Sep 21, 4:20 pm, amish <shahami...@gmail.com> wrote:
>
>
>
>
>
> > On Sep 21, 10:11 am, stevenleong...@gmail.com wrote:
>
> > > I am new to SQL query. Can someone help me to solve this question?
>
> > > 1. Given the following schema of an employees table:
>
> > > *employees (*
>
> > > * empid integer primary key, -- employee id number*
>
> > > * dept string, -- the employee's department number*
>
> > > * salary float, -- salary of the employee*
>
> > > * mgrid integer -- employee id number of employee's
> > > manager*
>
> > > *)*
>
> > > Write the query (or queries if necessary) needed to count the number
> > > of employees in each employee's department who are paid more than
> > > their manager.
> > > All employees (including managers) appear in this table.
> > > Each employee has a manager (except the CEO), and the mgrid in the
> > > employee's row is the empid of the employee's manager.
>
> > create table employees (
> > empid integer primary key, -- employee id number*
> > dept varchar(100), -- the employee's department
> > number*
> > salary float, -- salary of the employee*
> > mgrid integer -- employee id number of employee's
> > )
>
> > insert into employees values (1,'CEO',10000,null)
> > insert into employees values (2,'dep1',5000,1)
> > insert into employees values (3,'dep1',5000,1)
> > insert into employees values (4,'dep2',6000,2)
> > insert into employees values (5,'dep2',4000,2)
> > insert into employees values (6,'dep2',7000,3)
> > insert into employees values (7,'dep2',4000,3)
>
> > select e.* from employees e inner join employees e1 on e.mgrid =
> > e1.mgrid and e.salary > e1.salary- Hide quoted text -
>
> > - Show quoted text -
>
> minor change in the query
>
> select e.* from employees e inner join employees e1 on e.mgrid =
> e1.empid and e.salary > e1.salary- Hide quoted text -
>
> - Show quoted text -

>> I am new to SQL query. Can someone help me to solve this question? <<

>> All employees (including managers) appear in this table [only once or in many roles and salaries? ].

>> Write the query (or queries if necessary) needed to count the number of employees in each employee's department who are paid more than their manager. <<

On Sep 21, 6:16 pm, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> I am new to SQL query. Can someone help me to solve this question? <<
>
> I seem to remember this question from intro Database textbook that
> used the Scott/Tiger sample DB. If this is a homework assignment, we
> will track you down and get you expelled.
>
> You did not post DDL but some weird personal narrative and it was
> wrong in so many ways. Invalid data types; non-existent data types;
> data element names that violate ISO-11179 rules; etc.
>
> CREATE TABLE Personnel -- set of entities
> (emp_id INTEGER NOT NULL PRIMARY KEY,
> dept_name CHAR(8) NOT NULL,
> salary_amt DECIMAL(8,2) NOT NULL, -- never use FLOAT for money!!
> /* the following is a redundant design and stinks, but you wrote it
> mgr_emp_id INTEGER
> REFERENCES Personnel(emp_id),
> CHECK (emp_id <> mgr_emp_id), --assumption */
> );
>
> If you had a relational design, then there would be another table:
>
> CREATE TABLE OrgChart -- a relationship!
> (emp_id INTEGER NOT NULL -- might be PK, might not
> REFERENCES Personnel (emp_id),
> ..);
>
> There are several models for the Organizational chart;I like the
> Nested Sets model, but you will also find Path Enumeration and
> Adjacency List models. You were trying to shove an adjacency list
> into an entity table -- a common design error.
>
> >> All employees (including managers) appear in this table [only once or in many roles and salaries? ].
>
> Each employee has a manager (except the CEO), and the mgr_id in the
> employee's row is the emp_id of the employee's manager. <<
>
> >> Write the query (or queries if necessary) needed to count the number of employees in each employee's department who are paid more than their manager. <<
>
> How many levels of management are involved? Two? Hundreds? Assume
> only two levels:
>
> SELECT P1.dept_name, COUNT (P1.emp_id)
> FROM Personnel AS P1, Personnel AS P2
> WHERE P1.dept_name = P2.dept_name
> AND P1.emp_id = P2.mgr_emp_id
> AND P1.salary_amt > P2.salary_amt ;