Teachers TeachNo TeachName CourseNo DeptNo StartDate Salary 101 Kluger 101 10 20
ID: 3847158 • Letter: T
Question
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
Looking for some SQL statements that pertain to the table above. I am using ORACLE.
1. How many teachers teach each course? (COUNT function but we do not want the count of all teachers but for each course, hence we need to use GROUP BY courseno)
2. List deptno, and the total salary for each department (GROUP BY deptno)
3. List the name of the teacher with the most experience (based on the start date)
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
The GROUP BY articulation is regularly utilized with total capacities COUNT, MAX, MIN, SUM, AVG to aggregate the outcome set by at least one sections.
1.SELECT COUNT(TeachName), CourseNo
FROM Teachers
GROUP BY CourseNo;
2.
SELECT DeptNo, SUM(Salary) AS total_salaries
FROM Teachers
GROUP BY DeptNo;
In this case, weve utilized the Entirety capacity to include the majority of the compensations for each dept_id and weve associated the aftereffects of the Aggregate capacity as total_salaries.Since the dept_id is not epitomized in the Whole capacity,it must be recorded in the Gathering BY statement
3.SELECT MAX(StartDate) FROM Teachers;
SQL MAX capacity is utilized to discover the record with greatest incentive among a record set.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.