I'm a newbie in SQL and I need some basic help please.

[ImperialSardaukar]

I have two tables - books and subjects, they are connected using subject_code, and I need to check which subject does not have any book, how can I do that, can anyone help ?

 

[kevinclark]

SELECT Subjects.SubjectName, Books.BookName
FROM Subjects LEFT OUTER JOIN Books ON Subjects.SubjectCode = Books.SubjectCode
WHERE Books.BookName Is Null

 

[ImperialSardaukar]

Thanks, works great, although I got another solution with SELECT DISTINCT subject
FROM subjects
WHERE subject_code Not In ( SELECT subject_code FROM books );

Yours is alot more efficient.

I got another question - In the Books table there is a price column, I need to check out the average (I know that is the Avg() function) and then name all books that their prices are above the average, and I can't figure how to do it, everything I've done failed... can ne1 help ?

 

[kevinclark]

You could try

Select BookName From Books where Price > (SELECT Avg(Books.Price) AS AvgOfPrice
FROM Books)

 

[ImperialSardaukar]

Works like a charm.

One last thing... I have the Books table and the column price and boolean column IsForFinalExams
and I need to make a query that will decrease 20% of the book Price if IsForFinalExams = True otherwise decreases 10% , any idea?

Thanks.

 

[kevinclark]

You can try inserting an If statement in the Query.
IF(Logical Test, Do this if True, Do This if False)

NOTE: I can not remember whether the Statement is If or IIf
So if one does not work try the other.


Sample SQL:

SELECT SubjectCode, BookName, IsForFinalExams, If(IsForFinalExams=True,Price-(Price*0.2),Price-(Price*0.1)) AS AdjustedPrice
FROM Books

 

[ImperialSardaukar]

It was IIF and it worked, thanks for everything!

 

[kevinclark]

Glad to help