{"id":233,"date":"2018-07-13T05:52:27","date_gmt":"2018-07-13T05:52:27","guid":{"rendered":"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/?post_type=chapter&#038;p=233"},"modified":"2018-08-10T11:22:26","modified_gmt":"2018-08-10T11:22:26","slug":"sql-iii-in-mysql","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/chapter\/sql-iii-in-mysql\/","title":{"rendered":"SQL \u2013 III in MySQL"},"content":{"raw":"<div>\r\n\r\n&nbsp;\r\n\r\n<strong>Objectives<\/strong>\r\n<ul>\r\n \t<li><strong>Aggregate Function<\/strong><\/li>\r\n \t<li><strong>Group by and having clause<\/strong><\/li>\r\n \t<li><strong>Subquery<\/strong><\/li>\r\n<\/ul>\r\n<strong>\u00a0 \u00a0 \u00a0Aggregate function:<\/strong>\r\n<ul>\r\n \t<li style=\"text-align: justify\">The function which operates on single value and returns a single value is known as scalar function.<\/li>\r\n \t<li style=\"text-align: justify\">The function which operates on set of values and returns a single summary value is known as aggregate function. Aggregate function is also known as column functions.<\/li>\r\n \t<li style=\"text-align: justify\">A select statement having one or more aggregate function is called as a summary query.<\/li>\r\n \t<li style=\"text-align: justify\">The most common aggregate functions are:<\/li>\r\n<\/ul>\r\n<ol>\r\n \t<li>Avg<\/li>\r\n \t<li>Sum<\/li>\r\n \t<li>Min<\/li>\r\n \t<li>Max<\/li>\r\n \t<li>Count<\/li>\r\n<\/ol>\r\nThe syntax of aggregate functions is shown in following table.\r\n\r\n&nbsp;\r\n<table class=\"aligncenter\" border=\"1\">\r\n<tbody>\r\n<tr>\r\n<td><strong>Function Syntax<\/strong><\/td>\r\n<td><strong>Description<\/strong><\/td>\r\n<\/tr>\r\n<tr>\r\n<td>avg ( [All | Distinct ] expression )<\/td>\r\n<td>Returns the average of the non-null values in the expression<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>sum ( [All | Distinct ] expression )<\/td>\r\n<td>Returns the total of the non-null values in the expression<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>min ( [All | Distinct ] expression )<\/td>\r\n<td>Returns the minimum\u00a0 non-null value in the expression<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>max ( [All | Distinct ] expression )<\/td>\r\n<td>Returns the maximum non-null value in the expression<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>count ( [All | Distinct ] expression )<\/td>\r\n<td>Returns the number of the non-null values in the expression<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>count ( * )<\/td>\r\n<td>Returns the number of rows selected by the query<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<ul>\r\n \t<li style=\"text-align: justify\">The expression specified for the avg and sum functions must result in a numeric value.<\/li>\r\n \t<li style=\"text-align: justify\">The expression specified for the min , max and count function can result in a numeric, string or date value.<\/li>\r\n \t<li style=\"text-align: justify\">By default, all values are included in the calculation regardless of whether they are duplicated.<\/li>\r\n \t<li style=\"text-align: justify\">To omit duplicate values in the calculation, code distinct keyword. The distinct keyword is generally used with the count function.<\/li>\r\n \t<li style=\"text-align: justify\">Count (*) included null values. While all other aggregate functions not include null values in\u00a0 calculation.<\/li>\r\n \t<li style=\"text-align: justify\">For example, following query counts unpaid invoices and calculate the total due.<\/li>\r\n<\/ul>\r\nselect count(*) as\u00a0 \"Number of Invoices\",\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">\u00a0 sum(itotal - paymenttotal - credittotal) as \"Total Due\"<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">\u00a0from invoices\u00a0\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">\u00a0where (itotal - paymenttotal - credittotal ) &gt;= 0<\/span>\r\n\r\n<\/div>\r\n<div>\r\n<ul>\r\n \t<li>Count(*) is used to count all the selected rows. Count(&lt;columnName&gt;) can be coded .<\/li>\r\n \t<li>To count only the rows with unique values in a specified column, code count (distinct &lt;columnName&gt;).<\/li>\r\n<\/ul>\r\n\/* count(*) , AVG and sum function *\/\r\n\r\n&nbsp;\r\n\r\nselect 'After 15\/04\/2011' as selection_date,\r\n\r\ncount(*) as \"Number of Invoices\",\r\n\r\nround(avg(itotal),2) as \"Average Invoice Amount\",\r\n\r\nsum(itotal) as 'Total Invoice Amount'\r\n\r\nfrom invoices\r\n\r\nwhere idate &gt; '2011-04-15'; \/* Min and Max example *\/\r\n\r\n<\/div>\r\n<span style=\"text-align: initial;font-size: 1em\">\u00a0 \u00a0 select 'After 13\/04\/2011' as selection_date,<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">count(*) as 'Number of invoices', max(itotal) as 'Highest Invoice Total',<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">min(itotal) as 'Lowest Invoice Total' from invoices<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">where idate &gt; '2011-04-13';<\/span>\r\n<div>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">\/* Min and Max on non-numeric value *\/ select min(vname) as 'First Vendor', max(vname) as 'Last Vendor', count(vname) as 'Number of Vendors' from vendors;<\/p>\r\n&nbsp;\r\n\r\n\/* a summary query which uses Distinct keyword *\/\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify;padding-left: 30px\">select count(distinct vid) as 'Number of Vendors',<\/p>\r\n<p style=\"text-align: justify;padding-left: 30px\">count(vid) as 'number of invoices',<\/p>\r\n<p style=\"text-align: justify;padding-left: 30px\">round(avg(itotal),2) as 'Avg Income Amt',<\/p>\r\n<p style=\"text-align: justify;padding-left: 30px\">sum(itotal) as 'Total invoice Amount'<\/p>\r\n<p style=\"padding-left: 30px\">from invoices<\/p>\r\n<p style=\"padding-left: 30px\">where idate &gt; '2011-04-01';<\/p>\r\n&nbsp;\r\n\r\nThe syntax of select statement with group by and having clause\r\n\r\n&nbsp;\r\n<p style=\"padding-left: 30px\">Select &lt;column-list separated by ,&gt;<\/p>\r\n<p style=\"padding-left: 30px\">From &lt;tableName&gt;<\/p>\r\n<p style=\"padding-left: 30px\"><span style=\"text-align: initial;font-size: 1em\">[where searchCondition]<\/span><\/p>\r\n<p style=\"padding-left: 30px\"><span style=\"text-align: initial;font-size: 1em\">[group by &lt;group by list]<\/span><\/p>\r\n<p style=\"padding-left: 30px\"><span style=\"text-align: initial;font-size: 1em\">[having &lt;searchcondition&gt;]<\/span><\/p>\r\n<p style=\"padding-left: 30px\"><span style=\"text-align: initial;font-size: 1em\">[Order by &lt;order by list&gt;]<\/span><\/p>\r\n<p style=\"padding-left: 30px\"><span style=\"text-align: initial;font-size: 1em\">[limit\u00a0 &lt;row limit&gt;]<\/span><\/p>\r\n\r\n<\/div>\r\n<div>\r\n<ul>\r\n \t<li style=\"text-align: justify\">The group by clause is used to group the rows of a result set based on one or more columns or expressions. Comma (,) is used to include two or more columns or expressions.<\/li>\r\n \t<li style=\"text-align: justify\">Aggregate function is included in the select statement, then the aggregate is calculated for each group specified by the group by clause.<\/li>\r\n \t<li style=\"text-align: justify\">\u00a0If two or more columns or expression are included in the group by clause, they form a hierarchy where each column or expression is subordinate to the previous one.<\/li>\r\n \t<li style=\"text-align: justify\">The having clause specifies a search condition for a group or an aggregate. MySQL applies this condition after it groups the rows.<\/li>\r\n \t<li style=\"text-align: justify\">When a select statement includes a group by clause, the select clause can include the columns used for grouping, aggregate functions and expressions that result in a constant value.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n\r\n\/* A summary query that counts the number of invoices by vendor *\/\r\n\r\n&nbsp;\r\n\r\nselect vid, count(*) as invoice_qty\r\n\r\nfrom invoices\r\n\r\ngroup by vid;\r\n\r\n&nbsp;\r\n\r\n\/* A summary query that calculates the average invoice amount by vendor\u00a0\u00a0\u00a0\u00a0 *\/\r\n\r\n&nbsp;\r\n\r\nselect vid, round(avg(itotal),2) as \"Average Invoice Amount\"\r\n\r\nfrom invoices\r\n\r\ngroup by vid\r\n\r\nhaving avg(itotal) &gt; 100\r\n\r\norder by \"Average Invoice Amount\" desc;\r\n<ul>\r\n \t<li style=\"text-align: justify\">By default, group by clause sorts the columns in ascending order. To sort the same in descending order code desc keyword after the column name in the group by clause.<\/li>\r\n<\/ul>\r\n\/* A summary query that counts the number of invoices by vendor *\/\r\n\r\n&nbsp;\r\n\r\nselect vid, count(*) as invoice_qty\r\n\r\nfrom invoices\r\n\r\ngroup by vid;\r\n\r\n<\/div>\r\n<div>\r\n\r\n&nbsp;\r\n\r\n\/* A summary query that calculates the number of invoices and the average invoice amount\r\n\r\n&nbsp;\r\n\r\nfor the vendors in each state and city *\/\r\n\r\n&nbsp;\r\n\r\nselect vstate, vcity, count(*) as invoice_qty,\r\n\r\nround(avg(itotal),2) as invoice_avg\r\n\r\nfrom invoices , vendors\r\n\r\nwhere invoices.vid = vendors.vid\r\n\r\ngroup by vstate, vcity;\r\n\r\n&nbsp;\r\n\r\n\/* A summary query that limits the groups to those\r\n\r\n&nbsp;\r\n\r\nwith two or more invoices *\/\r\n\r\n&nbsp;\r\n\r\nselect vstate, vcity, count(*) as invoice_qty,\r\n\r\nround(avg(itotal),2) as invoice_avg\r\n\r\nfrom invoices i, vendors v\r\n\r\nwhere i.vid = v.vid\r\n\r\ngroup by vstate,vcity\r\n\r\nhaving count(*) &gt;= 2;\r\n\r\n&nbsp;\r\n\r\n<strong>Compare between where and having<\/strong>\r\n<ul>\r\n \t<li style=\"text-align: justify\">Where clause is used in select statement that uses grouping and aggregates, MySQL apply search criteria before it groups the rows and calculates the aggregates.<\/li>\r\n \t<li style=\"text-align: justify\">Having clause is used in select statement that uses grouping and aggregates, MySQL apply search criteria after it groups the rows and calculates the aggregates.<\/li>\r\n \t<li style=\"text-align: justify\">A where clause can refer to any column in the base table.<\/li>\r\n \t<li style=\"text-align: justify\">A having clause can only refer to a column included in the select clause.<\/li>\r\n \t<li style=\"text-align: justify\">A where clause cannot contain aggregate functions.<\/li>\r\n \t<li style=\"text-align: justify\">A having clause can contain aggregate functions.<\/li>\r\n<\/ul>\r\n\/* A summary query with search condition in the having clause *\/\r\n\r\n&nbsp;\r\n\r\nselect vname,count(*) as invoice_qty,\r\n\r\nround(avg(itotal),2) as invoice_avg\r\n\r\n<span style=\"font-size: 1em;text-align: initial\">from vendors v, invoices i<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">where v.vid = i.vid<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">group by vname<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">having avg(itotal) &gt; 200<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">order by invoice_qty desc;<\/span>\r\n\r\n<\/div>\r\n<div>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0 \/* A summary query with a search condition in the where clause *\/ select vname, count(*)as invoice_qty, round(avg(itotal),2) as invoice_avg<\/p>\r\n&nbsp;\r\n\r\nfrom vendors v, invoices i\r\n\r\nwhere v.vid = i.vid\r\n\r\nand itotal &gt; 200\r\n\r\ngroup by vname\r\n\r\norder by invoice_qty desc;\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">\/* A summary query with a search condition in the where clause *\/ select vname, count(*) as invoice_qty, round(avg(itotal),2) as invoice_avg<\/p>\r\n&nbsp;\r\n\r\nfrom vendors v,invoices i\r\n\r\nwhere v.vid = i.vid and\r\n\r\nitotal &gt; 200\r\n\r\ngroup by vname\r\n\r\norder by invoice_qty desc;\r\n\r\n&nbsp;\r\n\r\n\/* A summary query with a search condition in the having clause *\/ select vname, count(*) as invoice_qty,\r\n\r\n<\/div>\r\n<span style=\"text-align: initial;font-size: 1em\">\u00a0 \u00a0 round(avg(itotal),2) as invoice_avg<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">from vendors v, invoices i<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">where v.vid = i.vid<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">group by vname<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">having avg(itotal) &gt; 200<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">order by invoice_qty desc;<\/span>\r\n<ul>\r\n \t<li><span style=\"text-align: initial;font-size: 1em\">Compound search condition can be coded in having clause using the AND and OR operators.<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: justify;font-size: 1em\">If a search condition includes an aggregate function, it must be coded in the having clause. Otherwise, it can be coded in either the having clause or the where clause.<\/span><\/li>\r\n<\/ul>\r\n<div>\r\n\r\n\u00a0 \u00a0\/* summary query with a compound condition in having clause *\/\r\n\r\n&nbsp;\r\n\r\nselect idate, count(*) as invoice_qty, sum(itotal) as invoice_sum\r\n\r\nfrom invoices\r\n\r\ngroup by idate\r\n\r\nhaving idate between '2011-04-15' AND '2011-05-31'\r\n\r\nand count(*) &gt; 1\r\n\r\nand sum(itotal)&gt; 200\r\n\r\norder by idate desc;\r\n\r\n&nbsp;\r\n\r\n\/* summary query with a compound condition in having clause using where clause *\/\r\n\r\nselect idate, count(*) as invoice_qty, sum(itotal) as invoice_sum\r\n\r\nfrom invoices\r\n\r\nwhere idate between '2011-04-15' AND '2011-05-31'\r\n\r\ngroup by idate\r\n\r\nhaving count(*) &gt; 1\r\n\r\nand sum(itotal) &gt; 200\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">order by idate desc;<\/span>\r\n\r\n<\/div>\r\n<div>\r\n\r\n<strong>\u00a0 \u00a0 \u00a0Subquery<\/strong>\r\n<ul>\r\n \t<li>A subquery is a query (select statement) that is coded within another SQL statement.<\/li>\r\n \t<li>Subquery must enclose in parentheses.<\/li>\r\n \t<li>A subquery can return a single value, a result set that has a single column or a result set that has multiple columns.<\/li>\r\n \t<li>A subquery can be coded anywhere a single value, a result set that has a single column or a result set that has multiple columns.<\/li>\r\n \t<li>Subquery has the same syntax as the standard select statement.<\/li>\r\n \t<li>Subquery cannot include an order by clause.<\/li>\r\n \t<li>Subqueries can be nested within other subqueries.<\/li>\r\n \t<li>Subquery can be coded in select statement in<\/li>\r\n \t<li>A where clause as a search condition o A having clause as a search condition o The from clause as a table specification<\/li>\r\n \t<li>The select clause as a column specification<\/li>\r\n<\/ul>\r\n\/* Subquey in where clause *\/\r\n\r\nselect ino, idate, itotal\r\n\r\nfrom invoices\r\n\r\nwhere itotal &gt;\r\n\r\n(select avg(itotal)\r\n\r\nfrom invoices)\r\n\r\norder by itotal;\r\n<ul>\r\n \t<li>Subquery can be used to code queries which work with two or more tables.<\/li>\r\n \t<li>Most subquery can be coded as joins and most joins can be coded as subqueries.<\/li>\r\n \t<li>For example<\/li>\r\n<\/ul>\r\n\/* A query that uses a join *\/\r\n\r\n&nbsp;\r\n\r\nselect ino, idate, itotal\r\n\r\nfrom invoices i, vendors v\r\n\r\nwhere i.vid = v.vid\r\n\r\nand vstate = 'GJ'\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">order by idate;<\/span>\r\n\r\n<\/div>\r\n<div>\r\n\r\n\u00a0 \u00a0\/* The same query that uses a subquery *\/\r\n\r\n&nbsp;\r\n\r\nselect ino,idate,itotal\r\n\r\nfrom invoices\r\n\r\nwhere vid in\r\n\r\n(select vid\r\n\r\nfrom vendors\r\n\r\nwhere vstate = 'GJ')\r\n\r\norder by idate;\r\n\r\n&nbsp;\r\n<ul>\r\n \t<li style=\"text-align: justify\">Subquery can be written with IN operator to provide the list of values which are tested again the expression.<\/li>\r\n \t<li>Subquery written using not in can be recoded using outer join.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n\r\n\/* Get vendor without invoices *\/ select vid, vname, vcity\r\n\r\n&nbsp;\r\n\r\nfrom vendors where vid not in (select vid from invoices);\r\n<ul>\r\n \t<li style=\"text-align: justify\">A query which uses NOT IN operator with subquery can be recoded using an outer join.<\/li>\r\n<\/ul>\r\nselect v.vid, vname, vstate\r\n\r\nfrom vendors v left join invoices i on v.vid = i.vid\r\n\r\nwhere i.vid is null order by v.vid;\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n<strong>Comparison operators in subquery<\/strong>\r\n<ul>\r\n \t<li style=\"text-align: justify\">Comparison operator can be used in where clause to compare an expression with the result of a subquery.<\/li>\r\n \t<li style=\"text-align: justify\">The syntax for where clause that uses a comparison operator is: Where expression &lt;comparison operator&gt; [some|any|all] {subquery}<\/li>\r\n<\/ul>\r\n<\/div>\r\n<div>\r\n\r\n&nbsp;\r\n\r\nselect ino, idate, itotal-paymenttotal-credittotal as balance_due from invoices\r\n<p style=\"text-align: justify\">where itotal-paymenttotal-credittotal &gt;= 0 and itotal-paymenttotal-credittotal &lt;= (select avg(itotal-paymenttotal-credittotal) from invoices<\/p>\r\n&nbsp;\r\n\r\nwhere itotal-paymenttotal-credittotal &gt;= 0) order by itotal desc;\r\n<ul>\r\n \t<li style=\"text-align: justify\">A search condition without the some, any , all keywords , the subquery must return a single value.<\/li>\r\n \t<li style=\"text-align: justify\">\u00a0A search condition with the some, any , all keyword , the subquery can return a list of\u00a0 values.<\/li>\r\n \t<li style=\"text-align: justify\">All keyword is used to test that comparison condition is true for all of the values returned by a subquery.<\/li>\r\n \t<li style=\"text-align: justify\">If no rows are returned by the subquery, a comparison that uses the All keyword is always true.<\/li>\r\n \t<li style=\"text-align: justify\">\u00a0If all rows are returned by the subquery contains a null value, a comparison that uses the All keyword is always false.<\/li>\r\n \t<li>All keyword working<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<table class=\"aligncenter\" style=\"height: 462px\" border=\"1\">\r\n<tbody>\r\n<tr style=\"height: 0px\">\r\n<td style=\"height: 102px;width: 77.0625px\" rowspan=\"2\"><strong>Condition<\/strong>\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 102px;width: 75.0625px\" rowspan=\"2\"><strong>Equivalent<\/strong>\r\n\r\n<strong>expression<\/strong><\/td>\r\n<td style=\"height: 102px;width: 490.063px\" rowspan=\"2\"><strong>Description<\/strong>\r\n\r\n&nbsp;<\/td>\r\n<\/tr>\r\n<tr style=\"height: 0px\">\r\n<td style=\"height: 72px;width: 77.0625px\" rowspan=\"3\">X &gt; all (1,2)\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 72px;width: 75.0625px\" rowspan=\"3\">X &gt; 2\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 72px;width: 490.063px\" rowspan=\"3\">X must be greater than all the values returned by the subquery , which\r\n\r\nmeans that it must be greater than the maximum value. If X is greater\r\n\r\nthan the maximum value returned by subquery, it is evaluated as true.<\/td>\r\n<\/tr>\r\n<tr style=\"height: 0px\">\r\n<td style=\"height: 87px;width: 77.0625px\" rowspan=\"4\">X &lt; all (1,2)\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 87px;width: 75.0625px\" rowspan=\"4\">X &lt; 1\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 87px;width: 490.063px\" rowspan=\"4\">X must be less than all the values returned by the subquery, which means\r\n\r\nthat it must be less than the minimum value. If X is less than the\r\n\r\nminimum value returned by subquery, it is evaluated as true.\r\n\r\n&nbsp;<\/td>\r\n<\/tr>\r\n<tr style=\"height: 0px\">\r\n<td style=\"height: 117px;width: 77.0625px\" rowspan=\"5\">X = all (1,2)\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 117px;width: 75.0625px\" rowspan=\"5\">(X = 1)\r\n\r\nAND\r\n\r\n(X = 2)\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 117px;width: 490.063px\" rowspan=\"5\">This condition evaluates to TRUE only if the subquery returns a single\r\n\r\nvalue which is equal to X or if the subquery returns multiple values that\r\n\r\nare the same and these\u00a0 values are all equal to X. Otherwise it evaluates\r\n\r\nto FALSE.\r\n\r\n&nbsp;<\/td>\r\n<\/tr>\r\n<tr style=\"height: 28px\">\r\n<td style=\"height: 28px;width: 77.0625px\" rowspan=\"3\">X &lt; &gt; all(1,2)\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 84px;width: 75.0625px\" rowspan=\"3\">X NOT IN\r\n\r\n(1,2)\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 84px;width: 490.063px\" rowspan=\"3\">This condition evaluates to TRUE only if x is not one of the values\r\n\r\nreturned by the subquery. Otherwise it evaluates to FALSE.\r\n\r\n&nbsp;<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n&nbsp;\r\n<p style=\"text-align: justify\">For example, following query get invoices smaller than the largest invoices for vendorid 3<\/p>\r\n&nbsp;\r\n\r\nselect vname, ino, itotal\r\n\r\nfrom invoices i, vendors v\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">where i.vid = v.vid<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">and itotal &lt; any<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">(<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">select itotal<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">from invoices iv<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">where vid = 3<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">)<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">order by vname;<\/span>\r\n\r\n<\/div>\r\n<div>\r\n<ul>\r\n \t<li style=\"text-align: justify\">Any keyword is used to test that comparison condition is true for one or more of the values returned by a subquery.<\/li>\r\n \t<li style=\"text-align: justify\">If no rows are returned by the subquery or it returns null values, a comparison that uses the Any keyword is always false.<\/li>\r\n \t<li style=\"text-align: justify\">The Some keyword works the same as the ANY keyword.<\/li>\r\n \t<li style=\"text-align: justify\">Any keyword working<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<table class=\"aligncenter\" border=\"1\">\r\n<tbody>\r\n<tr>\r\n<td style=\"width: 86.0625px\" rowspan=\"2\"><strong>Condition<\/strong>\r\n\r\n&nbsp;<\/td>\r\n<td style=\"width: 75.0625px\" rowspan=\"2\"><strong>Equivalent<\/strong>\r\n\r\n<strong>expression<\/strong><\/td>\r\n<td style=\"width: 491.063px\" rowspan=\"2\"><strong>Description<\/strong>\r\n\r\n&nbsp;<\/td>\r\n<\/tr>\r\n<tr>\r\n<td style=\"width: 86.0625px\" rowspan=\"4\">X &gt; any (1,2)\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"width: 75.0625px\" rowspan=\"4\">X &gt; 1\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"width: 491.063px\" rowspan=\"4\">X must be greater than at least one of the values returned by the\r\n\r\nsubquery list, which means that it must be greater than the minimum\r\n\r\nvalue returned by the subquery.\u00a0 If X is greater than the minimum value\r\n\r\nreturned by subquery, it is evaluated as true.<\/td>\r\n<\/tr>\r\n<tr>\r\n<td style=\"width: 86.0625px\" rowspan=\"4\">X &lt; any (1,2)\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"width: 75.0625px\" rowspan=\"4\">X &lt; 2\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"width: 491.063px\" rowspan=\"4\">X must be less than at least one of the values returned by the subquery\r\n\r\nlist, which means that it must be less than the maximum value returned\r\n\r\nby the subquery. If X is less than the maximum value returned by\r\n\r\nsubquery, it is evaluated as true.<\/td>\r\n<\/tr>\r\n<tr>\r\n<td style=\"width: 86.0625px\" rowspan=\"3\">X = any (1,2)\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"width: 75.0625px\" rowspan=\"3\">(X = 1) OR\r\n\r\n(X = 2)\r\n\r\n&nbsp;<\/td>\r\n<td style=\"width: 491.063px\" rowspan=\"3\">This condition can evaluate to TRUE only if X is equal to any of the\r\n\r\nvalues returned by the subquery.\u00a0 Otherwise it evaluates to FALSE.\r\n\r\n&nbsp;<\/td>\r\n<\/tr>\r\n<tr>\r\n<td style=\"width: 86.0625px\" rowspan=\"3\">X &lt; &gt; any(1,2)\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<td style=\"width: 75.0625px\" rowspan=\"3\">( X &lt; &gt; 1 )\r\n\r\nOR\r\n\r\n(X &lt; &gt; 2)<\/td>\r\n<td style=\"width: 491.063px\" rowspan=\"3\">This consdition is equivalent to X NOT IN(1, 2)\r\n\r\n&nbsp;\r\n\r\n&nbsp;<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n&nbsp;\r\n\r\nFor example, following query get invoices smaller than the largest invoices for vendorid 7\r\n\r\n&nbsp;\r\n\r\nselect vname, ino, itotal\r\n\r\nfrom invoices i, vendors v\r\n\r\nwhere i.vid = v.vid\r\n\r\nand itotal &lt; any\r\n\r\n(\r\n\r\n&nbsp;\r\n\r\nselect itotal\r\n\r\nfrom invoices iv\r\n\r\nwhere vid = 7\r\n\r\n)\r\n\r\norder by vname;\r\n\r\n<\/div>\r\n&nbsp;\r\n<div>\r\n\r\n<strong>Correlated Subquery:<\/strong>\r\n<ul>\r\n \t<li style=\"text-align: justify\">A correlated subquery is a subquery which is executed once for each row of the main query.<\/li>\r\n \t<li style=\"text-align: justify\">A correlated subquery refers to a value that is provided by a column in the main query. For each different value that is returned by main query for that column, the subquery returns a different result.<\/li>\r\n \t<li style=\"text-align: justify\">A subquery which is executed only once for the entire query is known an uncorrelated subquery.<\/li>\r\n<\/ul>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0For example, following query get each invoice amount that is higher than the vendor\u2019s average invoice amount.<\/p>\r\n&nbsp;\r\n\r\nselect vid,ino,itotal\r\n\r\nfrom invoices i\r\n\r\nwhere itotal &gt;\r\n\r\n(\r\n\r\n&nbsp;\r\n\r\nselect avg(itotal)\r\n\r\nfrom invoices i\r\n\r\nwhere vid = i.vid\r\n\r\n)\r\n\r\norder by vid, itotal;\r\n\r\n&nbsp;\r\n\r\n<strong>Exists Operator:<\/strong>\r\n<ul>\r\n \t<li style=\"text-align: justify\">Exists operator is used to test that one or more rows are returned by the subquery.<\/li>\r\n \t<li style=\"text-align: justify\">Not exists operator is used to test that no rows are returned by the subquery.<\/li>\r\n \t<li style=\"text-align: justify\">When exists or not exists operator is used with a subquery, it does not matter what columns are specified in the select clause. As a result, generally an * (asterisk) is coded within subquery.<\/li>\r\n \t<li style=\"text-align: justify\">The syntax of a subquery which is used the exists operator<\/li>\r\n \t<li style=\"text-align: justify\">Where [not] exists (subquery)<\/li>\r\n<\/ul>\r\nselect vid,vname,vstate\r\n\r\nfrom vendors v where not exists\r\n\r\n(\r\n\r\nselect *\r\n\r\nfrom invoices i where i. vid = v.vid\r\n\r\n)\r\n\r\n&nbsp;\r\n\r\n<strong>Subquery used with other clauses<\/strong>\r\n\r\n<\/div>\r\n<ul>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: justify;font-size: 1em\">Subquery can be coded in select statement in o A where clause as a search condition o A having clause as a search condition o The from clause as a table specification<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">The select clause as a column specification<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">The use of subquery with where clause is shown by example earlier.<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">When a subquery is used in the select clause, it is generally a correlated subquery.<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">When a subquery is coded in the select clause, the subquery must return a single value.<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: justify;font-size: 1em\">A query which include subquery in its select clause can be coded using a join instead of the subquery. Join is generally faster and easier to read, subqueries are rarely coded in the select clause.<\/span><\/li>\r\n<\/ul>\r\n<div>\r\n\r\n&nbsp;\r\n\r\nFor example, to list the most recent invoice date for each vendor , the query is\r\n\r\n&nbsp;\r\n\r\nselect vname,\r\n\r\n(\r\n\r\nselect max(idate)\r\n\r\nfrom invoices\r\n\r\nwhere vid = vendors.vid ) as latest_inv\r\n\r\nfrom vendors\r\n\r\norder by latest_inv desc;\r\n\r\n&nbsp;\r\n\r\n<strong>The above query can be coded using join as below:<\/strong>\r\n\r\n&nbsp;\r\n\r\nselect vname, max(idate) as latest_inv\r\n\r\nfrom vendors v left join invoices i\r\n\r\non v.vid = i.vid\r\n\r\ngroup by v.vname\r\n\r\norder by latest_inv desc;\r\n<ul>\r\n \t<li>A subquery which is coded in the from clause returns a result set which is also known as an inline view.<\/li>\r\n \t<li style=\"text-align: justify\">When a subquery is coded in the form clause, an alias must be assign to the subquery. The alias can be used as the same way any table name alias.<\/li>\r\n \t<li style=\"text-align: justify\">When a subquery is coded in the form clause, an alias should be used for any columns in the subquery that perform calculations. Then, the inline view can use these aliases as the column name of a table.<\/li>\r\n<\/ul>\r\n<\/div>\r\nFor example, to get the largest invoice total for the top vendor in each state\r\n\r\n&nbsp;\r\n\r\nselect vstate, max(sum_of_invoices) as \" max sum of invoices\"\r\n\r\nfrom\r\n\r\n(\r\n\r\nselect vstate, vname, sum(itotal) as sum_of_invoices\r\n\r\nfrom vendors v, invoices i\r\n\r\nwhere v.vid = i.vid\r\n\r\ngroup by vstate, vname\r\n\r\n) t\r\n\r\ngroup by vstate;\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n<strong>References:<\/strong>\r\n\r\n&nbsp;\r\n\r\n1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,\r\n\r\n2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress\r\n\r\n3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,\r\n\r\n4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O'Reilly Media\r\n\r\n5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox\r\n\r\n6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible\r\n\r\n7. Joel Murach, Ray Harris: Murach\u2019s PHP and MySQL, Shroff\/Murach\r\n\r\n8. Ivan Bayross, Web Enabled Commercial Application Development UsingHTML\/Javascript\/DHTML\/PHP , BPB Publications\r\n\r\n9. Joel Murach, \u201cMurach\u2019s MySQL\u201d, Shroff\/Murach\r\n\r\n10. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams\r\n\r\n11. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education\r\n\r\n12. http:\/\/www.php.net\/\r\n\r\n13. http:\/\/www.w3schools.com\/\r\n\r\n14. http:\/\/www.tutorialspoint.com\/","rendered":"<div>\n<p>&nbsp;<\/p>\n<p><strong>Objectives<\/strong><\/p>\n<ul>\n<li><strong>Aggregate Function<\/strong><\/li>\n<li><strong>Group by and having clause<\/strong><\/li>\n<li><strong>Subquery<\/strong><\/li>\n<\/ul>\n<p><strong>\u00a0 \u00a0 \u00a0Aggregate function:<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">The function which operates on single value and returns a single value is known as scalar function.<\/li>\n<li style=\"text-align: justify\">The function which operates on set of values and returns a single summary value is known as aggregate function. Aggregate function is also known as column functions.<\/li>\n<li style=\"text-align: justify\">A select statement having one or more aggregate function is called as a summary query.<\/li>\n<li style=\"text-align: justify\">The most common aggregate functions are:<\/li>\n<\/ul>\n<ol>\n<li>Avg<\/li>\n<li>Sum<\/li>\n<li>Min<\/li>\n<li>Max<\/li>\n<li>Count<\/li>\n<\/ol>\n<p>The syntax of aggregate functions is shown in following table.<\/p>\n<p>&nbsp;<\/p>\n<table class=\"aligncenter\">\n<tbody>\n<tr>\n<td><strong>Function Syntax<\/strong><\/td>\n<td><strong>Description<\/strong><\/td>\n<\/tr>\n<tr>\n<td>avg ( [All | Distinct ] expression )<\/td>\n<td>Returns the average of the non-null values in the expression<\/td>\n<\/tr>\n<tr>\n<td>sum ( [All | Distinct ] expression )<\/td>\n<td>Returns the total of the non-null values in the expression<\/td>\n<\/tr>\n<tr>\n<td>min ( [All | Distinct ] expression )<\/td>\n<td>Returns the minimum\u00a0 non-null value in the expression<\/td>\n<\/tr>\n<tr>\n<td>max ( [All | Distinct ] expression )<\/td>\n<td>Returns the maximum non-null value in the expression<\/td>\n<\/tr>\n<tr>\n<td>count ( [All | Distinct ] expression )<\/td>\n<td>Returns the number of the non-null values in the expression<\/td>\n<\/tr>\n<tr>\n<td>count ( * )<\/td>\n<td>Returns the number of rows selected by the query<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<ul>\n<li style=\"text-align: justify\">The expression specified for the avg and sum functions must result in a numeric value.<\/li>\n<li style=\"text-align: justify\">The expression specified for the min , max and count function can result in a numeric, string or date value.<\/li>\n<li style=\"text-align: justify\">By default, all values are included in the calculation regardless of whether they are duplicated.<\/li>\n<li style=\"text-align: justify\">To omit duplicate values in the calculation, code distinct keyword. The distinct keyword is generally used with the count function.<\/li>\n<li style=\"text-align: justify\">Count (*) included null values. While all other aggregate functions not include null values in\u00a0 calculation.<\/li>\n<li style=\"text-align: justify\">For example, following query counts unpaid invoices and calculate the total due.<\/li>\n<\/ul>\n<p>select count(*) as\u00a0 &#8220;Number of Invoices&#8221;,<\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">\u00a0 sum(itotal &#8211; paymenttotal &#8211; credittotal) as &#8220;Total Due&#8221;<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">\u00a0from invoices\u00a0\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">\u00a0where (itotal &#8211; paymenttotal &#8211; credittotal ) &gt;= 0<\/span><\/p>\n<\/div>\n<div>\n<ul>\n<li>Count(*) is used to count all the selected rows. Count(&lt;columnName&gt;) can be coded .<\/li>\n<li>To count only the rows with unique values in a specified column, code count (distinct &lt;columnName&gt;).<\/li>\n<\/ul>\n<p>\/* count(*) , AVG and sum function *\/<\/p>\n<p>&nbsp;<\/p>\n<p>select &#8216;After 15\/04\/2011&#8217; as selection_date,<\/p>\n<p>count(*) as &#8220;Number of Invoices&#8221;,<\/p>\n<p>round(avg(itotal),2) as &#8220;Average Invoice Amount&#8221;,<\/p>\n<p>sum(itotal) as &#8216;Total Invoice Amount&#8217;<\/p>\n<p>from invoices<\/p>\n<p>where idate &gt; &#8216;2011-04-15&#8217;; \/* Min and Max example *\/<\/p>\n<\/div>\n<p><span style=\"text-align: initial;font-size: 1em\">\u00a0 \u00a0 select &#8216;After 13\/04\/2011&#8217; as selection_date,<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">count(*) as &#8216;Number of invoices&#8217;, max(itotal) as &#8216;Highest Invoice Total&#8217;,<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">min(itotal) as &#8216;Lowest Invoice Total&#8217; from invoices<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">where idate &gt; &#8216;2011-04-13&#8217;;<\/span><\/p>\n<div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">\/* Min and Max on non-numeric value *\/ select min(vname) as &#8216;First Vendor&#8217;, max(vname) as &#8216;Last Vendor&#8217;, count(vname) as &#8216;Number of Vendors&#8217; from vendors;<\/p>\n<p>&nbsp;<\/p>\n<p>\/* a summary query which uses Distinct keyword *\/<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;padding-left: 30px\">select count(distinct vid) as &#8216;Number of Vendors&#8217;,<\/p>\n<p style=\"text-align: justify;padding-left: 30px\">count(vid) as &#8216;number of invoices&#8217;,<\/p>\n<p style=\"text-align: justify;padding-left: 30px\">round(avg(itotal),2) as &#8216;Avg Income Amt&#8217;,<\/p>\n<p style=\"text-align: justify;padding-left: 30px\">sum(itotal) as &#8216;Total invoice Amount&#8217;<\/p>\n<p style=\"padding-left: 30px\">from invoices<\/p>\n<p style=\"padding-left: 30px\">where idate &gt; &#8216;2011-04-01&#8217;;<\/p>\n<p>&nbsp;<\/p>\n<p>The syntax of select statement with group by and having clause<\/p>\n<p>&nbsp;<\/p>\n<p style=\"padding-left: 30px\">Select &lt;column-list separated by ,&gt;<\/p>\n<p style=\"padding-left: 30px\">From &lt;tableName&gt;<\/p>\n<p style=\"padding-left: 30px\"><span style=\"text-align: initial;font-size: 1em\">[where searchCondition]<\/span><\/p>\n<p style=\"padding-left: 30px\"><span style=\"text-align: initial;font-size: 1em\">[group by &lt;group by list]<\/span><\/p>\n<p style=\"padding-left: 30px\"><span style=\"text-align: initial;font-size: 1em\">[having &lt;searchcondition&gt;]<\/span><\/p>\n<p style=\"padding-left: 30px\"><span style=\"text-align: initial;font-size: 1em\">[Order by &lt;order by list&gt;]<\/span><\/p>\n<p style=\"padding-left: 30px\"><span style=\"text-align: initial;font-size: 1em\">[limit\u00a0 &lt;row limit&gt;]<\/span><\/p>\n<\/div>\n<div>\n<ul>\n<li style=\"text-align: justify\">The group by clause is used to group the rows of a result set based on one or more columns or expressions. Comma (,) is used to include two or more columns or expressions.<\/li>\n<li style=\"text-align: justify\">Aggregate function is included in the select statement, then the aggregate is calculated for each group specified by the group by clause.<\/li>\n<li style=\"text-align: justify\">\u00a0If two or more columns or expression are included in the group by clause, they form a hierarchy where each column or expression is subordinate to the previous one.<\/li>\n<li style=\"text-align: justify\">The having clause specifies a search condition for a group or an aggregate. MySQL applies this condition after it groups the rows.<\/li>\n<li style=\"text-align: justify\">When a select statement includes a group by clause, the select clause can include the columns used for grouping, aggregate functions and expressions that result in a constant value.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>\/* A summary query that counts the number of invoices by vendor *\/<\/p>\n<p>&nbsp;<\/p>\n<p>select vid, count(*) as invoice_qty<\/p>\n<p>from invoices<\/p>\n<p>group by vid;<\/p>\n<p>&nbsp;<\/p>\n<p>\/* A summary query that calculates the average invoice amount by vendor\u00a0\u00a0\u00a0\u00a0 *\/<\/p>\n<p>&nbsp;<\/p>\n<p>select vid, round(avg(itotal),2) as &#8220;Average Invoice Amount&#8221;<\/p>\n<p>from invoices<\/p>\n<p>group by vid<\/p>\n<p>having avg(itotal) &gt; 100<\/p>\n<p>order by &#8220;Average Invoice Amount&#8221; desc;<\/p>\n<ul>\n<li style=\"text-align: justify\">By default, group by clause sorts the columns in ascending order. To sort the same in descending order code desc keyword after the column name in the group by clause.<\/li>\n<\/ul>\n<p>\/* A summary query that counts the number of invoices by vendor *\/<\/p>\n<p>&nbsp;<\/p>\n<p>select vid, count(*) as invoice_qty<\/p>\n<p>from invoices<\/p>\n<p>group by vid;<\/p>\n<\/div>\n<div>\n<p>&nbsp;<\/p>\n<p>\/* A summary query that calculates the number of invoices and the average invoice amount<\/p>\n<p>&nbsp;<\/p>\n<p>for the vendors in each state and city *\/<\/p>\n<p>&nbsp;<\/p>\n<p>select vstate, vcity, count(*) as invoice_qty,<\/p>\n<p>round(avg(itotal),2) as invoice_avg<\/p>\n<p>from invoices , vendors<\/p>\n<p>where invoices.vid = vendors.vid<\/p>\n<p>group by vstate, vcity;<\/p>\n<p>&nbsp;<\/p>\n<p>\/* A summary query that limits the groups to those<\/p>\n<p>&nbsp;<\/p>\n<p>with two or more invoices *\/<\/p>\n<p>&nbsp;<\/p>\n<p>select vstate, vcity, count(*) as invoice_qty,<\/p>\n<p>round(avg(itotal),2) as invoice_avg<\/p>\n<p>from invoices i, vendors v<\/p>\n<p>where i.vid = v.vid<\/p>\n<p>group by vstate,vcity<\/p>\n<p>having count(*) &gt;= 2;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Compare between where and having<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">Where clause is used in select statement that uses grouping and aggregates, MySQL apply search criteria before it groups the rows and calculates the aggregates.<\/li>\n<li style=\"text-align: justify\">Having clause is used in select statement that uses grouping and aggregates, MySQL apply search criteria after it groups the rows and calculates the aggregates.<\/li>\n<li style=\"text-align: justify\">A where clause can refer to any column in the base table.<\/li>\n<li style=\"text-align: justify\">A having clause can only refer to a column included in the select clause.<\/li>\n<li style=\"text-align: justify\">A where clause cannot contain aggregate functions.<\/li>\n<li style=\"text-align: justify\">A having clause can contain aggregate functions.<\/li>\n<\/ul>\n<p>\/* A summary query with search condition in the having clause *\/<\/p>\n<p>&nbsp;<\/p>\n<p>select vname,count(*) as invoice_qty,<\/p>\n<p>round(avg(itotal),2) as invoice_avg<\/p>\n<p><span style=\"font-size: 1em;text-align: initial\">from vendors v, invoices i<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">where v.vid = i.vid<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">group by vname<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">having avg(itotal) &gt; 200<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">order by invoice_qty desc;<\/span><\/p>\n<\/div>\n<div>\n<p style=\"text-align: justify\">\u00a0 \u00a0 \/* A summary query with a search condition in the where clause *\/ select vname, count(*)as invoice_qty, round(avg(itotal),2) as invoice_avg<\/p>\n<p>&nbsp;<\/p>\n<p>from vendors v, invoices i<\/p>\n<p>where v.vid = i.vid<\/p>\n<p>and itotal &gt; 200<\/p>\n<p>group by vname<\/p>\n<p>order by invoice_qty desc;<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">\/* A summary query with a search condition in the where clause *\/ select vname, count(*) as invoice_qty, round(avg(itotal),2) as invoice_avg<\/p>\n<p>&nbsp;<\/p>\n<p>from vendors v,invoices i<\/p>\n<p>where v.vid = i.vid and<\/p>\n<p>itotal &gt; 200<\/p>\n<p>group by vname<\/p>\n<p>order by invoice_qty desc;<\/p>\n<p>&nbsp;<\/p>\n<p>\/* A summary query with a search condition in the having clause *\/ select vname, count(*) as invoice_qty,<\/p>\n<\/div>\n<p><span style=\"text-align: initial;font-size: 1em\">\u00a0 \u00a0 round(avg(itotal),2) as invoice_avg<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">from vendors v, invoices i<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">where v.vid = i.vid<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">group by vname<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">having avg(itotal) &gt; 200<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">order by invoice_qty desc;<\/span><\/p>\n<ul>\n<li><span style=\"text-align: initial;font-size: 1em\">Compound search condition can be coded in having clause using the AND and OR operators.<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"text-align: justify;font-size: 1em\">If a search condition includes an aggregate function, it must be coded in the having clause. Otherwise, it can be coded in either the having clause or the where clause.<\/span><\/li>\n<\/ul>\n<div>\n<p>\u00a0 \u00a0\/* summary query with a compound condition in having clause *\/<\/p>\n<p>&nbsp;<\/p>\n<p>select idate, count(*) as invoice_qty, sum(itotal) as invoice_sum<\/p>\n<p>from invoices<\/p>\n<p>group by idate<\/p>\n<p>having idate between &#8216;2011-04-15&#8217; AND &#8216;2011-05-31&#8217;<\/p>\n<p>and count(*) &gt; 1<\/p>\n<p>and sum(itotal)&gt; 200<\/p>\n<p>order by idate desc;<\/p>\n<p>&nbsp;<\/p>\n<p>\/* summary query with a compound condition in having clause using where clause *\/<\/p>\n<p>select idate, count(*) as invoice_qty, sum(itotal) as invoice_sum<\/p>\n<p>from invoices<\/p>\n<p>where idate between &#8216;2011-04-15&#8217; AND &#8216;2011-05-31&#8217;<\/p>\n<p>group by idate<\/p>\n<p>having count(*) &gt; 1<\/p>\n<p>and sum(itotal) &gt; 200<\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">order by idate desc;<\/span><\/p>\n<\/div>\n<div>\n<p><strong>\u00a0 \u00a0 \u00a0Subquery<\/strong><\/p>\n<ul>\n<li>A subquery is a query (select statement) that is coded within another SQL statement.<\/li>\n<li>Subquery must enclose in parentheses.<\/li>\n<li>A subquery can return a single value, a result set that has a single column or a result set that has multiple columns.<\/li>\n<li>A subquery can be coded anywhere a single value, a result set that has a single column or a result set that has multiple columns.<\/li>\n<li>Subquery has the same syntax as the standard select statement.<\/li>\n<li>Subquery cannot include an order by clause.<\/li>\n<li>Subqueries can be nested within other subqueries.<\/li>\n<li>Subquery can be coded in select statement in<\/li>\n<li>A where clause as a search condition o A having clause as a search condition o The from clause as a table specification<\/li>\n<li>The select clause as a column specification<\/li>\n<\/ul>\n<p>\/* Subquey in where clause *\/<\/p>\n<p>select ino, idate, itotal<\/p>\n<p>from invoices<\/p>\n<p>where itotal &gt;<\/p>\n<p>(select avg(itotal)<\/p>\n<p>from invoices)<\/p>\n<p>order by itotal;<\/p>\n<ul>\n<li>Subquery can be used to code queries which work with two or more tables.<\/li>\n<li>Most subquery can be coded as joins and most joins can be coded as subqueries.<\/li>\n<li>For example<\/li>\n<\/ul>\n<p>\/* A query that uses a join *\/<\/p>\n<p>&nbsp;<\/p>\n<p>select ino, idate, itotal<\/p>\n<p>from invoices i, vendors v<\/p>\n<p>where i.vid = v.vid<\/p>\n<p>and vstate = &#8216;GJ&#8217;<\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">order by idate;<\/span><\/p>\n<\/div>\n<div>\n<p>\u00a0 \u00a0\/* The same query that uses a subquery *\/<\/p>\n<p>&nbsp;<\/p>\n<p>select ino,idate,itotal<\/p>\n<p>from invoices<\/p>\n<p>where vid in<\/p>\n<p>(select vid<\/p>\n<p>from vendors<\/p>\n<p>where vstate = &#8216;GJ&#8217;)<\/p>\n<p>order by idate;<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li style=\"text-align: justify\">Subquery can be written with IN operator to provide the list of values which are tested again the expression.<\/li>\n<li>Subquery written using not in can be recoded using outer join.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>\/* Get vendor without invoices *\/ select vid, vname, vcity<\/p>\n<p>&nbsp;<\/p>\n<p>from vendors where vid not in (select vid from invoices);<\/p>\n<ul>\n<li style=\"text-align: justify\">A query which uses NOT IN operator with subquery can be recoded using an outer join.<\/li>\n<\/ul>\n<p>select v.vid, vname, vstate<\/p>\n<p>from vendors v left join invoices i on v.vid = i.vid<\/p>\n<p>where i.vid is null order by v.vid;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Comparison operators in subquery<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">Comparison operator can be used in where clause to compare an expression with the result of a subquery.<\/li>\n<li style=\"text-align: justify\">The syntax for where clause that uses a comparison operator is: Where expression &lt;comparison operator&gt; [some|any|all] {subquery}<\/li>\n<\/ul>\n<\/div>\n<div>\n<p>&nbsp;<\/p>\n<p>select ino, idate, itotal-paymenttotal-credittotal as balance_due from invoices<\/p>\n<p style=\"text-align: justify\">where itotal-paymenttotal-credittotal &gt;= 0 and itotal-paymenttotal-credittotal &lt;= (select avg(itotal-paymenttotal-credittotal) from invoices<\/p>\n<p>&nbsp;<\/p>\n<p>where itotal-paymenttotal-credittotal &gt;= 0) order by itotal desc;<\/p>\n<ul>\n<li style=\"text-align: justify\">A search condition without the some, any , all keywords , the subquery must return a single value.<\/li>\n<li style=\"text-align: justify\">\u00a0A search condition with the some, any , all keyword , the subquery can return a list of\u00a0 values.<\/li>\n<li style=\"text-align: justify\">All keyword is used to test that comparison condition is true for all of the values returned by a subquery.<\/li>\n<li style=\"text-align: justify\">If no rows are returned by the subquery, a comparison that uses the All keyword is always true.<\/li>\n<li style=\"text-align: justify\">\u00a0If all rows are returned by the subquery contains a null value, a comparison that uses the All keyword is always false.<\/li>\n<li>All keyword working<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<table class=\"aligncenter\" style=\"height: 462px\">\n<tbody>\n<tr style=\"height: 0px\">\n<td style=\"height: 102px;width: 77.0625px\" rowspan=\"2\"><strong>Condition<\/strong><\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 102px;width: 75.0625px\" rowspan=\"2\"><strong>Equivalent<\/strong><\/p>\n<p><strong>expression<\/strong><\/td>\n<td style=\"height: 102px;width: 490.063px\" rowspan=\"2\"><strong>Description<\/strong><\/p>\n<p>&nbsp;<\/td>\n<\/tr>\n<tr style=\"height: 0px\">\n<td style=\"height: 72px;width: 77.0625px\" rowspan=\"3\">X &gt; all (1,2)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 72px;width: 75.0625px\" rowspan=\"3\">X &gt; 2<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 72px;width: 490.063px\" rowspan=\"3\">X must be greater than all the values returned by the subquery , which<\/p>\n<p>means that it must be greater than the maximum value. If X is greater<\/p>\n<p>than the maximum value returned by subquery, it is evaluated as true.<\/td>\n<\/tr>\n<tr style=\"height: 0px\">\n<td style=\"height: 87px;width: 77.0625px\" rowspan=\"4\">X &lt; all (1,2)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 87px;width: 75.0625px\" rowspan=\"4\">X &lt; 1<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 87px;width: 490.063px\" rowspan=\"4\">X must be less than all the values returned by the subquery, which means<\/p>\n<p>that it must be less than the minimum value. If X is less than the<\/p>\n<p>minimum value returned by subquery, it is evaluated as true.<\/p>\n<p>&nbsp;<\/td>\n<\/tr>\n<tr style=\"height: 0px\">\n<td style=\"height: 117px;width: 77.0625px\" rowspan=\"5\">X = all (1,2)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 117px;width: 75.0625px\" rowspan=\"5\">(X = 1)<\/p>\n<p>AND<\/p>\n<p>(X = 2)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 117px;width: 490.063px\" rowspan=\"5\">This condition evaluates to TRUE only if the subquery returns a single<\/p>\n<p>value which is equal to X or if the subquery returns multiple values that<\/p>\n<p>are the same and these\u00a0 values are all equal to X. Otherwise it evaluates<\/p>\n<p>to FALSE.<\/p>\n<p>&nbsp;<\/td>\n<\/tr>\n<tr style=\"height: 28px\">\n<td style=\"height: 28px;width: 77.0625px\" rowspan=\"3\">X &lt; &gt; all(1,2)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 84px;width: 75.0625px\" rowspan=\"3\">X NOT IN<\/p>\n<p>(1,2)<\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 84px;width: 490.063px\" rowspan=\"3\">This condition evaluates to TRUE only if x is not one of the values<\/p>\n<p>returned by the subquery. Otherwise it evaluates to FALSE.<\/p>\n<p>&nbsp;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">For example, following query get invoices smaller than the largest invoices for vendorid 3<\/p>\n<p>&nbsp;<\/p>\n<p>select vname, ino, itotal<\/p>\n<p>from invoices i, vendors v<\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">where i.vid = v.vid<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">and itotal &lt; any<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">(<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">select itotal<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">from invoices iv<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">where vid = 3<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">)<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">order by vname;<\/span><\/p>\n<\/div>\n<div>\n<ul>\n<li style=\"text-align: justify\">Any keyword is used to test that comparison condition is true for one or more of the values returned by a subquery.<\/li>\n<li style=\"text-align: justify\">If no rows are returned by the subquery or it returns null values, a comparison that uses the Any keyword is always false.<\/li>\n<li style=\"text-align: justify\">The Some keyword works the same as the ANY keyword.<\/li>\n<li style=\"text-align: justify\">Any keyword working<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<table class=\"aligncenter\">\n<tbody>\n<tr>\n<td style=\"width: 86.0625px\" rowspan=\"2\"><strong>Condition<\/strong><\/p>\n<p>&nbsp;<\/td>\n<td style=\"width: 75.0625px\" rowspan=\"2\"><strong>Equivalent<\/strong><\/p>\n<p><strong>expression<\/strong><\/td>\n<td style=\"width: 491.063px\" rowspan=\"2\"><strong>Description<\/strong><\/p>\n<p>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 86.0625px\" rowspan=\"4\">X &gt; any (1,2)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"width: 75.0625px\" rowspan=\"4\">X &gt; 1<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"width: 491.063px\" rowspan=\"4\">X must be greater than at least one of the values returned by the<\/p>\n<p>subquery list, which means that it must be greater than the minimum<\/p>\n<p>value returned by the subquery.\u00a0 If X is greater than the minimum value<\/p>\n<p>returned by subquery, it is evaluated as true.<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 86.0625px\" rowspan=\"4\">X &lt; any (1,2)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"width: 75.0625px\" rowspan=\"4\">X &lt; 2<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"width: 491.063px\" rowspan=\"4\">X must be less than at least one of the values returned by the subquery<\/p>\n<p>list, which means that it must be less than the maximum value returned<\/p>\n<p>by the subquery. If X is less than the maximum value returned by<\/p>\n<p>subquery, it is evaluated as true.<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 86.0625px\" rowspan=\"3\">X = any (1,2)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"width: 75.0625px\" rowspan=\"3\">(X = 1) OR<\/p>\n<p>(X = 2)<\/p>\n<p>&nbsp;<\/td>\n<td style=\"width: 491.063px\" rowspan=\"3\">This condition can evaluate to TRUE only if X is equal to any of the<\/p>\n<p>values returned by the subquery.\u00a0 Otherwise it evaluates to FALSE.<\/p>\n<p>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 86.0625px\" rowspan=\"3\">X &lt; &gt; any(1,2)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td style=\"width: 75.0625px\" rowspan=\"3\">( X &lt; &gt; 1 )<\/p>\n<p>OR<\/p>\n<p>(X &lt; &gt; 2)<\/td>\n<td style=\"width: 491.063px\" rowspan=\"3\">This consdition is equivalent to X NOT IN(1, 2)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>For example, following query get invoices smaller than the largest invoices for vendorid 7<\/p>\n<p>&nbsp;<\/p>\n<p>select vname, ino, itotal<\/p>\n<p>from invoices i, vendors v<\/p>\n<p>where i.vid = v.vid<\/p>\n<p>and itotal &lt; any<\/p>\n<p>(<\/p>\n<p>&nbsp;<\/p>\n<p>select itotal<\/p>\n<p>from invoices iv<\/p>\n<p>where vid = 7<\/p>\n<p>)<\/p>\n<p>order by vname;<\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<div>\n<p><strong>Correlated Subquery:<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">A correlated subquery is a subquery which is executed once for each row of the main query.<\/li>\n<li style=\"text-align: justify\">A correlated subquery refers to a value that is provided by a column in the main query. For each different value that is returned by main query for that column, the subquery returns a different result.<\/li>\n<li style=\"text-align: justify\">A subquery which is executed only once for the entire query is known an uncorrelated subquery.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">\u00a0 \u00a0For example, following query get each invoice amount that is higher than the vendor\u2019s average invoice amount.<\/p>\n<p>&nbsp;<\/p>\n<p>select vid,ino,itotal<\/p>\n<p>from invoices i<\/p>\n<p>where itotal &gt;<\/p>\n<p>(<\/p>\n<p>&nbsp;<\/p>\n<p>select avg(itotal)<\/p>\n<p>from invoices i<\/p>\n<p>where vid = i.vid<\/p>\n<p>)<\/p>\n<p>order by vid, itotal;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Exists Operator:<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">Exists operator is used to test that one or more rows are returned by the subquery.<\/li>\n<li style=\"text-align: justify\">Not exists operator is used to test that no rows are returned by the subquery.<\/li>\n<li style=\"text-align: justify\">When exists or not exists operator is used with a subquery, it does not matter what columns are specified in the select clause. As a result, generally an * (asterisk) is coded within subquery.<\/li>\n<li style=\"text-align: justify\">The syntax of a subquery which is used the exists operator<\/li>\n<li style=\"text-align: justify\">Where [not] exists (subquery)<\/li>\n<\/ul>\n<p>select vid,vname,vstate<\/p>\n<p>from vendors v where not exists<\/p>\n<p>(<\/p>\n<p>select *<\/p>\n<p>from invoices i where i. vid = v.vid<\/p>\n<p>)<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Subquery used with other clauses<\/strong><\/p>\n<\/div>\n<ul>\n<li style=\"text-align: justify\"><span style=\"text-align: justify;font-size: 1em\">Subquery can be coded in select statement in o A where clause as a search condition o A having clause as a search condition o The from clause as a table specification<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">The select clause as a column specification<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">The use of subquery with where clause is shown by example earlier.<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">When a subquery is used in the select clause, it is generally a correlated subquery.<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">When a subquery is coded in the select clause, the subquery must return a single value.<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"text-align: justify;font-size: 1em\">A query which include subquery in its select clause can be coded using a join instead of the subquery. Join is generally faster and easier to read, subqueries are rarely coded in the select clause.<\/span><\/li>\n<\/ul>\n<div>\n<p>&nbsp;<\/p>\n<p>For example, to list the most recent invoice date for each vendor , the query is<\/p>\n<p>&nbsp;<\/p>\n<p>select vname,<\/p>\n<p>(<\/p>\n<p>select max(idate)<\/p>\n<p>from invoices<\/p>\n<p>where vid = vendors.vid ) as latest_inv<\/p>\n<p>from vendors<\/p>\n<p>order by latest_inv desc;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>The above query can be coded using join as below:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>select vname, max(idate) as latest_inv<\/p>\n<p>from vendors v left join invoices i<\/p>\n<p>on v.vid = i.vid<\/p>\n<p>group by v.vname<\/p>\n<p>order by latest_inv desc;<\/p>\n<ul>\n<li>A subquery which is coded in the from clause returns a result set which is also known as an inline view.<\/li>\n<li style=\"text-align: justify\">When a subquery is coded in the form clause, an alias must be assign to the subquery. The alias can be used as the same way any table name alias.<\/li>\n<li style=\"text-align: justify\">When a subquery is coded in the form clause, an alias should be used for any columns in the subquery that perform calculations. Then, the inline view can use these aliases as the column name of a table.<\/li>\n<\/ul>\n<\/div>\n<p>For example, to get the largest invoice total for the top vendor in each state<\/p>\n<p>&nbsp;<\/p>\n<p>select vstate, max(sum_of_invoices) as &#8221; max sum of invoices&#8221;<\/p>\n<p>from<\/p>\n<p>(<\/p>\n<p>select vstate, vname, sum(itotal) as sum_of_invoices<\/p>\n<p>from vendors v, invoices i<\/p>\n<p>where v.vid = i.vid<\/p>\n<p>group by vstate, vname<\/p>\n<p>) t<\/p>\n<p>group by vstate;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>References:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,<\/p>\n<p>2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress<\/p>\n<p>3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,<\/p>\n<p>4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O&#8217;Reilly Media<\/p>\n<p>5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox<\/p>\n<p>6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible<\/p>\n<p>7. Joel Murach, Ray Harris: Murach\u2019s PHP and MySQL, Shroff\/Murach<\/p>\n<p>8. Ivan Bayross, Web Enabled Commercial Application Development UsingHTML\/Javascript\/DHTML\/PHP , BPB Publications<\/p>\n<p>9. Joel Murach, \u201cMurach\u2019s MySQL\u201d, Shroff\/Murach<\/p>\n<p>10. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams<\/p>\n<p>11. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education<\/p>\n<p>12. http:\/\/www.php.net\/<\/p>\n<p>13. http:\/\/www.w3schools.com\/<\/p>\n<p>14. http:\/\/www.tutorialspoint.com\/<\/p>\n","protected":false},"author":3,"menu_order":24,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["dr-hiren-joshi"],"pb_section_license":""},"chapter-type":[],"contributor":[59],"license":[],"class_list":["post-233","chapter","type-chapter","status-publish","hentry","contributor-dr-hiren-joshi"],"part":3,"_links":{"self":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/233","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/users\/3"}],"version-history":[{"count":4,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/233\/revisions"}],"predecessor-version":[{"id":421,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/233\/revisions\/421"}],"part":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/233\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/media?parent=233"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapter-type?post=233"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/contributor?post=233"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/license?post=233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}