lec11.365

lec11.365 - . . Fall 2007 . CPE/CSC 365: Introduction to...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
. . Fall 2007 CPE/CSC 365: Introduction to Database Systems Alexander Dekhtyar . . SQL: Structured Query Language Nested Queries One of the most important features of SQL is that SQL SELECT statements can be nested within each other to produce complex queries. While some of the queries discussed below can be written without nesting, some queries require nested structure. Return Types In general, a SELECT statement returns a relational table as a result. However, there are situations, where the returned table can be cast to a more simple structure. In general, the following four cases are possible. We note, that in all four cases, it is always possible to treat the returned result as a relational table. Consider, for example, the following table: Univerersity(Id INT , NAME CHAR(60), City CHAR(15), State CHAR(2), NumStudents INT) The return type of a SELECT statement may be: 1. An atomic value . Returned when a single column of a relation is re- quested in the SELECT clause, and the information requested is unique in the table (e.g., a key value). For example, the following query SELECT Id FROM University WHERE Name = ’University of Montana’; returns a single number: the Id attribute for the University of Montana record. 2. A single tuple . Returned when the SELECT clause contains multiple attributes, but the information requested is unique in the table. For ex- ample, consider the following query: SELECT * FROM University WHERE Id = 10; This query returns a single tuple, whose value for the primary key attribute is 10. 1
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
3. A single column . Returned when a single attribute is listed in the SELECT clause, and when the result contains more than one entry. This can be viewed as a set of atomic values. For example, the query below, returns the list of IDs for all universities in California. SELECT Id FROM University WHERE State =’CA’; 4. A relational table . This is the most general return type, used when the SELECT clause contains multiple columns, and the result of the query is multiple tuples. This return type can be viewed as a set of tuples. The following query returns as its result the list of all California campuses and their corresponding enrollments: SELECT Name, NumStudents FROM University WHERE State = ’CA’; Note: For some queries, the return type can be established statically, by analyzing constraints on the relational table. For some other queries, the actual return type of the query run on some instances can be di±erent than the stati- cally predicted return type (e.g., the last query will return a single tuple if only one record about a California university exists in the table). Nested Queries in the FROM clause
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 7

lec11.365 - . . Fall 2007 . CPE/CSC 365: Introduction to...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online