{"id":3165,"date":"2019-12-23T16:46:19","date_gmt":"2019-12-23T16:46:19","guid":{"rendered":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/?post_type=chapter&#038;p=3165"},"modified":"2024-05-17T01:53:54","modified_gmt":"2024-05-17T01:53:54","slug":"using-subqueries","status":"publish","type":"chapter","link":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/chapter\/using-subqueries\/","title":{"raw":"Using Subqueries","rendered":"Using Subqueries"},"content":{"raw":"<div class=\"textbox learning-objectives\">\r\n<h3>Learning Outcomes<\/h3>\r\n<ul>\r\n \t<li>Use subqueries<\/li>\r\n<\/ul>\r\n<\/div>\r\nA subquery is a query that is contained or nested within another query. The idea is to use the outcome of one query within the execution of another query. It is analogous to asking a question of the results you have after answering another question, i.e., a multi-part question. A plain English example of a subquery would be \u201cshow me all employees who have been with the company more than ten years and then show me those who live in the state of Michigan.\u201d\r\n\r\nThe actual use of a subquery requires a basic knowledge of SQL (Structured Query Language). In essence, SQL is programming code and quite technical. <em>Structured Query Language<\/em>\u00a0(SQL) is a database language designed for managing data held in a relational database management system. SQL was initially developed by IBM in the early 1970s (Date 1986). The initial version, called\u00a0<em>SEQUEL<\/em>\u00a0(Structured English Query Language), was designed to manipulate and retrieve data stored in IBM\u2019s quasi-relational database management system, System R. Then in the late 1970s, Relational Software Inc., which is now Oracle Corporation, introduced the first commercially available implementation of SQL, Oracle V2 for VAX computers.\r\n<div class=\"textbox tryit\">\r\n<h3>PRactice Question<\/h3>\r\nhttps:\/\/assess.lumenlearning.com\/practice\/4d9c5176-3201-4f36-a964-2eb91ee00324\r\n\r\n<\/div>\r\nMany of the currently available relational DBMSs, such as Oracle Database, Microsoft SQL Server, MySQL, IBM DB2, IBM Informix and Microsoft Access, use SQL. Here is an example of the SQL language being used to create a database table:\r\n<pre>USE SW\r\nCREATE TABLE PROJECT\r\n(\r\nProjectID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Int\u00a0 NOT NULL IDENTITY (1000,100),\r\nProjectName\u00a0\u00a0\u00a0\u00a0 Char(50) NOT NULL,\r\nDepartment\u00a0\u00a0\u00a0\u00a0\u00a0 Char(35) NOT NULL,\r\nMaxHours\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Numeric(8,2)\u00a0 NOT NULL DEFAULT 100,\r\nStartDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DateTime NULL,\r\nEndDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DateTime NULL,\r\nCONSTRAINT\u00a0\u00a0\u00a0\u00a0\u00a0 ASSIGNMENT_PK\u00a0 PRIMARY KEY(ProjectID)\r\n);<\/pre>\r\nHere's an example of a subquery In Access using the Query Wizard in Design View (the subquery is written in SQL at the bottom of the third column):\r\n\r\n<img class=\"alignnone wp-image-3881 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2019\/12\/23011903\/Subquery.jpeg\" alt=\"Subquery for Dim_Transactions. The fields Customer ID from Dim_Transactions will be Grouped. The fields LineTotal from Dim_Transactions will be Summed. The fields CustomerId from Dim_Transactions will be totaled Where.\" width=\"527\" height=\"292\" \/>","rendered":"<div class=\"textbox learning-objectives\">\n<h3>Learning Outcomes<\/h3>\n<ul>\n<li>Use subqueries<\/li>\n<\/ul>\n<\/div>\n<p>A subquery is a query that is contained or nested within another query. The idea is to use the outcome of one query within the execution of another query. It is analogous to asking a question of the results you have after answering another question, i.e., a multi-part question. A plain English example of a subquery would be \u201cshow me all employees who have been with the company more than ten years and then show me those who live in the state of Michigan.\u201d<\/p>\n<p>The actual use of a subquery requires a basic knowledge of SQL (Structured Query Language). In essence, SQL is programming code and quite technical. <em>Structured Query Language<\/em>\u00a0(SQL) is a database language designed for managing data held in a relational database management system. SQL was initially developed by IBM in the early 1970s (Date 1986). The initial version, called\u00a0<em>SEQUEL<\/em>\u00a0(Structured English Query Language), was designed to manipulate and retrieve data stored in IBM\u2019s quasi-relational database management system, System R. Then in the late 1970s, Relational Software Inc., which is now Oracle Corporation, introduced the first commercially available implementation of SQL, Oracle V2 for VAX computers.<\/p>\n<div class=\"textbox tryit\">\n<h3>PRactice Question<\/h3>\n<p>\t<iframe id=\"assessment_practice_4d9c5176-3201-4f36-a964-2eb91ee00324\" class=\"resizable\" src=\"https:\/\/assess.lumenlearning.com\/practice\/4d9c5176-3201-4f36-a964-2eb91ee00324?iframe_resize_id=assessment_practice_id_4d9c5176-3201-4f36-a964-2eb91ee00324\" frameborder=\"0\" style=\"border:none;width:100%;height:100%;min-height:300px;\"><br \/>\n\t<\/iframe><\/p>\n<\/div>\n<p>Many of the currently available relational DBMSs, such as Oracle Database, Microsoft SQL Server, MySQL, IBM DB2, IBM Informix and Microsoft Access, use SQL. Here is an example of the SQL language being used to create a database table:<\/p>\n<pre>USE SW\r\nCREATE TABLE PROJECT\r\n(\r\nProjectID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Int\u00a0 NOT NULL IDENTITY (1000,100),\r\nProjectName\u00a0\u00a0\u00a0\u00a0 Char(50) NOT NULL,\r\nDepartment\u00a0\u00a0\u00a0\u00a0\u00a0 Char(35) NOT NULL,\r\nMaxHours\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Numeric(8,2)\u00a0 NOT NULL DEFAULT 100,\r\nStartDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DateTime NULL,\r\nEndDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DateTime NULL,\r\nCONSTRAINT\u00a0\u00a0\u00a0\u00a0\u00a0 ASSIGNMENT_PK\u00a0 PRIMARY KEY(ProjectID)\r\n);<\/pre>\n<p>Here&#8217;s an example of a subquery In Access using the Query Wizard in Design View (the subquery is written in SQL at the bottom of the third column):<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3881 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2019\/12\/23011903\/Subquery.jpeg\" alt=\"Subquery for Dim_Transactions. The fields Customer ID from Dim_Transactions will be Grouped. The fields LineTotal from Dim_Transactions will be Summed. The fields CustomerId from Dim_Transactions will be totaled Where.\" width=\"527\" height=\"292\" \/><\/p>\n\n\t\t\t <section class=\"citations-section\" role=\"contentinfo\">\n\t\t\t <h3>Candela Citations<\/h3>\n\t\t\t\t\t <div>\n\t\t\t\t\t\t <div id=\"citation-list-3165\">\n\t\t\t\t\t\t\t <div class=\"licensing\"><div class=\"license-attribution-dropdown-subheading\">CC licensed content, Original<\/div><ul class=\"citation-list\"><li>Practice Question. <strong>Authored by<\/strong>: Robert Danielson. <strong>Provided by<\/strong>: Lumen Learning. <strong>License<\/strong>: <em><a target=\"_blank\" rel=\"license\" href=\"https:\/\/creativecommons.org\/licenses\/by\/4.0\/\">CC BY: Attribution<\/a><\/em><\/li><\/ul><div class=\"license-attribution-dropdown-subheading\">CC licensed content, Shared previously<\/div><ul class=\"citation-list\"><li>SQL Structured Query Language. <strong>Authored by<\/strong>: Adrienne Watt and Nelson Eng. <strong>Provided by<\/strong>: BCcampus. <strong>Located at<\/strong>: <a target=\"_blank\" href=\"https:\/\/opentextbc.ca\/dbdesign01\/chapter\/sql-structured-query-language\/\">https:\/\/opentextbc.ca\/dbdesign01\/chapter\/sql-structured-query-language\/<\/a>. <strong>License<\/strong>: <em><a target=\"_blank\" rel=\"license\" href=\"https:\/\/creativecommons.org\/licenses\/by\/4.0\/\">CC BY: Attribution<\/a><\/em><\/li><\/ul><\/div>\n\t\t\t\t\t\t <\/div>\n\t\t\t\t\t <\/div>\n\t\t\t <\/section>","protected":false},"author":17,"menu_order":18,"template":"","meta":{"_candela_citation":"[{\"type\":\"cc\",\"description\":\"SQL Structured Query Language\",\"author\":\"Adrienne Watt and Nelson Eng\",\"organization\":\"BCcampus\",\"url\":\"https:\/\/opentextbc.ca\/dbdesign01\/chapter\/sql-structured-query-language\/\",\"project\":\"\",\"license\":\"cc-by\",\"license_terms\":\"\"},{\"type\":\"original\",\"description\":\"Practice Question\",\"author\":\"Robert Danielson\",\"organization\":\"Lumen Learning\",\"url\":\"\",\"project\":\"\",\"license\":\"cc-by\",\"license_terms\":\"\"}]","CANDELA_OUTCOMES_GUID":"f92d00e2-b587-46e4-bff2-3cc40f9de00d, 58f1aebc-6309-4d45-9562-193b5ca0b2a6","pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-3165","chapter","type-chapter","status-publish","hentry"],"part":2775,"_links":{"self":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapters\/3165","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/wp\/v2\/users\/17"}],"version-history":[{"count":13,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapters\/3165\/revisions"}],"predecessor-version":[{"id":5990,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapters\/3165\/revisions\/5990"}],"part":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/parts\/2775"}],"metadata":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapters\/3165\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/wp\/v2\/media?parent=3165"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapter-type?post=3165"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/wp\/v2\/contributor?post=3165"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/wp\/v2\/license?post=3165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}