{"id":374,"date":"2022-12-01T20:32:51","date_gmt":"2022-12-01T20:32:51","guid":{"rendered":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/chapter\/using-subqueries\/"},"modified":"2022-12-01T20:32:51","modified_gmt":"2022-12-01T20:32:51","slug":"using-subqueries","status":"publish","type":"chapter","link":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/chapter\/using-subqueries\/","title":{"raw":"Using Subqueries","rendered":"Using Subqueries"},"content":{"raw":"\n<div class=\"textbox learning-objectives\">\n<h3>Learning Outcomes<\/h3>\n<ul>\n \t<li>Use subqueries<\/li>\n<\/ul>\n<\/div>\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\n\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>&nbsp;(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&nbsp;<em>SEQUEL<\/em>&nbsp;(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.\n<div class=\"textbox tryit\">\n<h3>PRactice Question<\/h3>\nhttps:\/\/assess.lumenlearning.com\/practice\/4d9c5176-3201-4f36-a964-2eb91ee00324\n\n<\/div>\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:\n<pre>USE SW\nCREATE TABLE PROJECT\n(\nProjectID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Int&nbsp; NOT NULL IDENTITY (1000,100),\nProjectName&nbsp;&nbsp;&nbsp;&nbsp; Char(50) NOT NULL,\nDepartment&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Char(35) NOT NULL,\nMaxHours&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Numeric(8,2)&nbsp; NOT NULL DEFAULT 100,\nStartDate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DateTime NULL,\nEndDate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DateTime NULL,\nCONSTRAINT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ASSIGNMENT_PK&nbsp; PRIMARY KEY(ProjectID)\n);<\/pre>\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):\n\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\">\n<h2>Contribute!<\/h2><div style=\"margin-bottom: 8px;\">Did you have an idea for improving this content? We\u2019d love your input.<\/div><a href=\"https:\/\/docs.google.com\/document\/d\/1LO9US6YKVfpA3SNLT-smyDOW0JXeOJpM1YX897ODHMI\" target=\"_blank\" style=\"font-size: 10pt; font-weight: 600; color: #077fab; text-decoration: none; border: 2px solid #077fab; border-radius: 7px; padding: 5px 25px; text-align: center; cursor: pointer; line-height: 1.5em;\">Improve this page<\/a><a style=\"margin-left: 16px;\" target=\"_blank\" href=\"https:\/\/docs.google.com\/document\/d\/1vy-T6DtTF-BbMfpVEI7VP_R7w2A4anzYZLXR8Pk4Fu4\">Learn More<\/a>\n","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>&nbsp;(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&nbsp;<em>SEQUEL<\/em>&nbsp;(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\nCREATE TABLE PROJECT\n(\nProjectID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Int&nbsp; NOT NULL IDENTITY (1000,100),\nProjectName&nbsp;&nbsp;&nbsp;&nbsp; Char(50) NOT NULL,\nDepartment&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Char(35) NOT NULL,\nMaxHours&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Numeric(8,2)&nbsp; NOT NULL DEFAULT 100,\nStartDate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DateTime NULL,\nEndDate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DateTime NULL,\nCONSTRAINT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ASSIGNMENT_PK&nbsp; PRIMARY KEY(ProjectID)\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<h2>Contribute!<\/h2>\n<div style=\"margin-bottom: 8px;\">Did you have an idea for improving this content? We\u2019d love your input.<\/div>\n<p><a href=\"https:\/\/docs.google.com\/document\/d\/1LO9US6YKVfpA3SNLT-smyDOW0JXeOJpM1YX897ODHMI\" target=\"_blank\" style=\"font-size: 10pt; font-weight: 600; color: #077fab; text-decoration: none; border: 2px solid #077fab; border-radius: 7px; padding: 5px 25px; text-align: center; cursor: pointer; line-height: 1.5em;\">Improve this page<\/a><a style=\"margin-left: 16px;\" target=\"_blank\" href=\"https:\/\/docs.google.com\/document\/d\/1vy-T6DtTF-BbMfpVEI7VP_R7w2A4anzYZLXR8Pk4Fu4\">Learn More<\/a><\/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-374\">\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":503070,"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-374","chapter","type-chapter","status-publish","hentry"],"part":356,"_links":{"self":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/chapters\/374","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/wp\/v2\/users\/503070"}],"version-history":[{"count":0,"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/chapters\/374\/revisions"}],"part":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/parts\/356"}],"metadata":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/chapters\/374\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/wp\/v2\/media?parent=374"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/chapter-type?post=374"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/wp\/v2\/contributor?post=374"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/wp\/v2\/license?post=374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}