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

Info icon This 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
Image of page 1

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

View Full Document Right 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 different 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).
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern