Optimizing SQL queries

Question

I have created some MSSQL queries, all of them work well, but I think it could be done in a faster way. Can you help me to optimize them?

That's the database:

Create table Teachers
    (TNO char(3) Primary key,
     TNAME char(20),
     TITLE char(6) check (TITLE in('Prof','PhD','MSc')),
     CITY char(12),
     SUPNO char(3) REFERENCES Teachers);

Create table Students
    (SNO char(3) Primary key,
     SNAME char(20),
     SYEAR int,
     CITY char(20));

Create table Courses
    (CNO char(3) Primary key,
     CNAME char(20),
     STUDYEAR int);

Create table TSC
    (TNO char(3) REFERENCES Teachers,
     SNO char(3) REFERENCES Students,
     CNO char(3) REFERENCES Courses,
     HOURS int,
     GRADE float,
     PRIMARY KEY(TNO,SNO,CNO));

1: On which study year there are most courses?

Problem: it looks like the result is being sorted while I only need the max element.

select
  top 1 STUDYEAR
from
  Courses
group by
  STUDYEAR
order by COUNT(*) DESC

2: Show the TNOs of those teachers who do NOT have courses with the 1st studyear

Problem: I'm using a subquery only to negate a select query

select
  TNO
from
  Teachers
where
  TNO not in (
    select distinct
      tno
    from
      Courses, TSC
    where tsc.CNO=Courses.CNO and STUDYEAR = 1)

Answer 1

1. Some ordering needs to be done to find the max or min value; maybe using ranking functions instead of a group by would be better but I frankly expect the query analyzer to be smart enough to find a good query plan for this specific query.

2. The subquery is performing well as long as it isn't using columns from the outer query (which may cause it to be performed for every row in many cases). However, I'd leave away the distinct, as it has no benefit. Also, I'd always use the explicit join syntax, but that's mostly a matter of personal preference (for inner joins - outer joins should always be done with the explicit syntax).

So all in all I think that these queries are simple and clear enough to be handled well in the query analyzer, thereby yielding good performance. Do you have a specific performance issue for asking this question? If yes, give us more info (query plan etc.), if no, just leave them - don't to premature optimization.