Querying by SQL (Structured Query Language)

We start with some SQL examples related to the University Organization Problem.

Q1 Find the matriculation number (student id) of the student whose name is John.

Query Expression

SELECT  matNr FROM Student WHERE sName=’John’

From the above SQL query expression we note the following:

• The SELECT part is equivalent to a projection in relational algebra, not a selection.
• After SELECT, all attributes must be listed onto which a projection is executed.
• After FROM, all tables must be listed that are necessary for finding the query result.
• After WHERE, all constraints (formulas) for joins and selections (conditions) in the query must be listed.

Q2 Find the students who took the class on CSE 303.

Query Expression:

SELECT sName FROM Student, Takes WHERE Student.matNr=Takes.matNr AND classNr= ‘CSE 303’

Q3 Display the names and salaries of professors whose salary is > than 1 lac.

 Query Expression:

SELECT  * FROM  Professor WHERE psalary > 100000

Q4 Find the matNrs of all students younger than the oldest student named Philips.

Query Expression:

SELECT A.matNr FROM Student A, Student B WHERE B.sName = ‘Philips’ AND

A.age < B.age