CS669_Lab5HealthInstructions - MET CS 669 Database Design...

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

Page 1 of 8 Copyright 2013, 2014 Boston University. All Rights Reserved. MET CS 669 Database Design and Implementation for Business Health SQL Lab 5 Instructions: Subqueries OBJECTIVE The objective of this lab is to provide you with a practical exercise of using subqueries to practically address use cases. REQUIRED SOFTWARE The examples in this lab will execute in modern versions of Oracle and Microsoft SQL Server as is. Note that the first section in this lab has syntax specific to each DBMS, and you will need to complete the version for the DBMS you are using. If you are using a different RDBMS, you may need to modify the SQL for successful execution. PREREQUISITES Before attempting this lab, it is best to read the textbook and lecture material covering the objectives listed above. While this lab shows you how to create and use these constructs in SQL, the lab does not explain in full the theory behind the constructs, as does the lecture and textbook. SAVING YOUR DATA If you choose to perform portions of the assignment in different sittings, it is important to commit your data at the end of each session. This way, you will be sure to make permanent any data changes you have made in your curent session, so that you can resume working without issue in your next session. To do so, simply issue this command: COMMIT; Data changes in one session will only be visible only in that session, unless they are committed, at which time the changes are made permanent in the database. LAB COMPLETION Use the submission template provided in the assignment inbox to complete this lab.
Image of page 1

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

Page 2 of 8 SECTION ONE In this section we will learn to work with subqueries, which significantly extend the expressional power of queries. Through the use of subqueries, a single query can extract result sets that could not be extracted without subqueries. Subqueries enable the query creator to ask the database for many complex structures in a single query. The foundation for learning how subqueries work lies in the expressional nature of the relational model. We learned previously that the operations in the relational model, such as SELECT, PROJECT, and UNION, perform operations on relations and yield a new relation as the result. That is, when one operation operates on a relation, and yields a new relation, we can use a second operation to operate on the result of the first operation. We will take a look at these relational operations in a moment, but first let us look at a simple mathematical example. If we add two plus two to obtain a result of 4, 2 + 2 = 4 , we have applied the plus operation to two numbers. Interestingly, the result of the plus operation is another number. So we can say that the plus operator operates on two numbers, and results in a new number.
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.
  • Fall '15
  • Relational model, subqueries, medical procedure

{[ 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