Looking for an SQL statement that pertains the to the table below that answers t
ID: 3847159 • Letter: L
Question
Looking for an SQL statement that pertains the to the table below that answers this question: List each teacherno, techerName, Salary and the extent to which this teachers salary differs from the avarage salary of all teachers. (make the difference positive if the teacher’s salary is higher than the average and negative if it is lower than the average..
I am using ORACLE.
Teachers
TeachNo
TeachName
CourseNo
DeptNo
StartDate
Salary
101
Kluger
101
10
20-Oct-2016
65000
102
Confucious
102
11
11-Jan-2017
73000
103
Crazer
101
10
15-Aug-2000
71000
104
Nutting
103
10
16-Aug-2007
52000
105
Brawler
104
11
20-Aug-2005
91000
106
Lazerk
107
12
20-Sep-2002
64000
107
Alienous
105
13
15-Aug-2001
79000
108
Asylant
106
13
15-Aug-2007
61000
109
fanatikal
102
11
20-Sep-2000
62000
110
Babyest
101
10
20-Aug-2003
67000
Teachers
TeachNo
TeachName
CourseNo
DeptNo
StartDate
Salary
101
Kluger
101
10
20-Oct-2016
65000
102
Confucious
102
11
11-Jan-2017
73000
103
Crazer
101
10
15-Aug-2000
71000
104
Nutting
103
10
16-Aug-2007
52000
105
Brawler
104
11
20-Aug-2005
91000
106
Lazerk
107
12
20-Sep-2002
64000
107
Alienous
105
13
15-Aug-2001
79000
108
Asylant
106
13
15-Aug-2007
61000
109
fanatikal
102
11
20-Sep-2000
62000
110
Babyest
101
10
20-Aug-2003
67000
Explanation / Answer
To calculate average the sql statement syntax is : SELECT AVG(column_name) FROM table_name;
so in our case it is : SELECT AVG(Salary) FROM Teachers;
To get data from a database statement syntax is: SELECT column1,column2,.... FROM table_name;
so in our case it is : SELECT TeachNo,TeachName,Salary FROM Teachers;
Now to calculate a difference of salary and average salary statement will be: Salary-(select avg(salary) from Teachers);
To add a column to an existing table for marking difference as positive and negative the statement is : ALTER TABLE table-name ADD column _name DATATYPE;
so for our it will be : ALTER TABLE Teachers ADD Pos/Neg char(1) ;
to add values to a new column statement syntax is: INSERT INTO Table_name (column) VALUES ( your values);
so our finall statement will be :-
SELECT TechNo, TeachName, Salary, Salary-(SELECT AVG(Salary) FROM Teachers) AS Difference FROM Teachers;
this statement will create a new column with name Difference which will have salary minus average salary as value in it. Then make a new column with name Pos/Neg with ALTER cmd to mark difference as positive or negative.
so the statements will be:
ALTER TABLE Teachers ADD Pos/Neg char(1);
INSERT INTO Teachers (Pos/Neg) VALUES ('P' WHERE Difference >= (SELECT AVG(Salary) FROM Teachers));
INSERT INTO Teachers (Pos/Neg) VALUES ('N' WHERE Difference < (SELECT AVG(Salary) FROM Teachers));
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.