XSHPn77g

1-- TEST TWO NOTES. 
2-- HYBRID ONE
3Question 1
4How many rows does the country table contain?	
5239
6 
7Question 2
8The SQL statement that displays code, name and continent from the country table is?	
9SELECT code, name, continent
10FROM country;
11
12Question 3
13Which SQL statement displays code, name and continent in alphabetical order?
14SELECT code, name, continent
15FROM country ORDER BY name;
16
17Question 4
18Which SQL statement displays name and surface area from country table, with the country with largest surface area first?
19The country with the smallest area is displayed last.	
20SELECT Name, SurfaceArea 
21FROM country ORDER BY SurfaceArea DESC;
22
23Question 5
24Which SQL statement displays countries with name ending in the letter ‘m’?	
25 SELECT * 
26FROM country 
27WHERE name LIKE '%m';
28
29Question 6
30Which country has the highest LifeExpectancy?	
31Andorra
32
33Question 7
34What is the average LifeExpectancy of the countries listed in the country table? Round your answer to two decimal places.
3566.49
36
37Question 8
38Which city has the second largest population?
39Seoul
40
41Question 9
42How many Canadian cities are listed? (before you inserted data for Victoria BC)	
4349
44
45Question 10
46What is the command to add data to a table?	
47INSERT
48
49-- HYBRID 2
50
51Question 1
52A join in which rows that do not have matching 
53values in common columns are still included in the result table
54OUTER JOIN
55
56Question 2
57A join operation is performed on two tables.
58The common field that is used for the join operation has a 
59few NULL values in each of the two tables.
60The join operation will:
61NOT MATCH NULL RECORDS FROM ANY OF THE TWO TABLES
62
63Question 3
64An operation to join a table to itself is called a:
65SELF JOIN
66
67Question 4
68Which query will combine each row from the 
69first table with each row from the second table?
70CROSS JOIN 
71
72Question 5
73SELECT Country.Code, Country.Name
74FROM Country
75WHERE Country.Code NOT IN(SELECT CountryCode FROM CountryLanguage);
76In the above query
77the statement
78SELECT CountryCode FROM CountryLanguage
79is a:
80SUB QUERY
81
82Question 6
83Which of the following queries will list Country
84Code, Country Name and City Name for each city in the city table. 
85
86SELECT Country.Code, Country.Name, City.Name
87FROM Country
88INNER JOIN City
89ON Country.Code = City.CountryCode;
90
91Question 7
92A JOIN operation combines two tables into one on the 
93basis of common values in a common column.
94TRUE
95
96Question 8
97A join operation:
98CAUSES TWO TABLES W COMMON ATTRIBUTE TO BE COMBINED INTO A SINGLE TABLE,
99
100Question 9
101An INNER JOIN operation is performed on two tables
102The common eld that is used for the join operation has a 
103few NULL values in each of the two tables.
104THE JOIN:
105WILL NOT MATCH NULL RECORDS FROM ANY OF THE TWO TABLES.
106
107Question 10
108Identify the query that will list 
109countries that have no cities listed in the city table	
110		
111SELECT Country.Code, Country.Name
112FROM Country
113LEFT JOIN City
114ON Country.Code = City.CountryCode
115WHERE City.CountryCode IS NULL
116
117Question 11
118Identify the SQL operator that satisfies the following conditions:
1191. Combines the result sets of two or more SELECT statements
1202. Each SELECT statement must have the same number of columns
1213. All columns must have the same data type
1224. All columns must be in the same order
1235. The result set will display DISTINCT values by default, the ALL keyword is used to display duplicate values.
124UNION
125
126Question 12
127Which query will list countries that do not have a language 
128listed in the CountryLanguage Table
129
130SELECT Country.Code, Country.Name, Language
131FROM CountryLanguage
132RIGHT JOIN Country ON Country.Code = CountryLanguage.CountryCode
133WHERE CountryLanguage.CountryCode IS NULL;
134 
135-- QUIZ THREE
136
137QUESTION 1
138Data combined from several measurements or data items is called an aggregate
139True   
140
141QUESTION 2
142Identify the keyword that is used to give an ALIAS to a column or table.
143AS
144   
145QUESTION 3
146An operation that changes the state off a database is a
147transaction
148
149QUESTION 4
150What will the following statement do?
151SELECT * 
152FROM Part_T
153WHERE Cost 
154BETWEEN 1.00 AND 2.00;
155 
156The statement will list all parts that costs 1.00 and more than 1.00 or 2.00 and less than 2.00
157 
158QUESTION 5
159What does the keyword CONCAT do?
160Joins two or more strings in to one string
161  
162QUESTION 6
163Study the following SQL statement and answer the question related to the keyword IN
164SELECT Prod_Description, Prod_QOH, Prod_Price
165FROM Product_T
166WHERE Prod_QOH IN( 60, 70, 80, 90 )
167ORDER BY Prod_QOH;
168
169It replaces repeating the OR logical operator
170 
171QUESTION 7
172SELECT Prod_Description, Prod_QOH, Prod_Price, Prod_Discount
173FROM Product_T
174WHERE Prod_Discount IS NULL;
175Study the above SQL statement and answer the question:
176
177WHERE Prod_Discount IS NULL; 
178can be replaced by 
179WHERE Prod_Discount = 0;  ??
180
181False
182  
183QUESTION 8
184In an SQL statement the qualifier DISTINCT will
185not display rows with the same data item in the column
186   
187QUESTION 9
188A NULL value can be used
189when a value of a data item is not known
190   
191QUESTION 10
192Identify the clause that will sort the table, ascending or descending
193ORDER BY
194  
195QUESTION 11
196A single value returned from a SQL query is called
197scalar
198  
199QUESTION 12
200The two DDL statements shown below are examples of
201CREATE TYPE Address FROM VARCHAR(45);
202CREATE TYPE City FROM VARCHAR(25) NOT NULL;
203User Defined Datatype (UDT)
204
205  
206QUESTION 13
207Study the following line of code. And answer the question below.
208SELECT Prod_Code,
209Prod_Description,
210Prod_QOH,
211Prod_Price,
212Prod_QOH * Prod_Price AS "Inventory_Cost"
213FROM Product_T
214WHERE Prod_QOH > 50
215ORDER BY Prod_Description;
216 
217The line Prod_QOH * Prod_Price AS "Inventory_Cost"
218will only display the product of Prod_QOH and Prod_Price , it will not alter the table Product_T.
219
220QUESTION 14
221Which SQL statement displays countries with name beginning in the letter P?
222SELECT * FROM country WHERE name LIKE 'P%';
223   
224QUESTION 15
225In an SQL statement, which one of the following clauses determines the tables from which to retrieve data? 
226FROM
227   
228QUESTION 16
229In an SQL statement, which one of the following clauses states the conditions for row selection? 
230WHERE
231 
232QUESTION 17
233In an SQL statement, which one of the following clauses will list columns or expressions? 
234SELECT
235  
236QUESTION 18
237An index does not occupy any additional hard disk space.
238False
239   
240QUESTION 19
241A data structure used to determine the location of records in the actual table that satisfies a condition 
242index
243   
244QUESTION 20
245Using an index reduces search and retrieval times, but increases time taken for updates.
246True
247   
248QUESTION 21
249The primary purpose of using indexes is to improve
250database performance during search and retrieval
251  
252QUESTION 22
253Indexing on many columns
254has a tradeoff because each INSERT statement in the table needs to update the index as well
255  
256QUESTION 23
257Indexes are useful for large tables, they may not have much benefit for tables with few rows.
258True
259   
260QUESTION 24
261At the time of data entry of an invoice, the application automatically inserts the current date in the data entry form. The intention is to minimize data entry and reduce human errors. What is the term used to define this feature.
262default value
263
264WEEK EIGHT.
265--JOINS (INNER, OUTER, CROSS AND SELF)
266JOIN 
267CLAUSE COMBINES ROWS FROM TWO++ TABLES, BASED ON A RELATED COLUMN BETWEEN THEM. 
268ORDER ID HAS CUSTOMER 
269AND CUSTOMERS TABLE HAS CUSTOMER ID TOO 
270WE WRITE A STATEMENT THAT CONAINS AN INNER JIN AND SELECTS RECORDS 
271THAT MATCH THE VALUES IN BOTH TABLES 
272THEN WE GET ORDER ID AND CUSTOMER NAME IN ONE TABLE
273* INNER JOIN - RETURNS RECORDS THAT HAVE MATCHING VALUES IN BOTH TABLES 
274* LEFT OUTER RETURNS ALL RECORDS FROM THE LEFT TABLE AND THE MATCHED ONES ON THE RIGHT
275* RIGHT OUTER JOIN DOES THE SAME AS LEFT BUT ALL RECORDS FROM RIGHT AND THE MATCHED ONES ON THE LEFT
276* FULL OUTER JOIN TURNS ALL RECORDS WHENERE HERE IS A MATCH IN THEIR RIGHT OR LEFT TABLE
277
278INNER JOIN 
279SELECTS ALL ROWS FROM BOTH TABLES AS LONG AS THERE IS A MATCH
280
281SELECT Orders.OrderID, Customers.CustomerName
282FROM Orders
283INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
284
285LEFT JOIN
286TURNS ALL RECORDS FROM LEFT (CUSTOMERS) 
287EVEN IF THERE IS NO MACHES IN THE RIGHT (ORDERS)
288
289SELECT Customers.CustomerName, Orders.OrderID
290FROM Customers
291LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
292ORDER BY Customers.CustomerName;
293
294RIGHT JOIN 
295RETURNS ALL RECORDS FRM THE RIGHT TABLE (EMPLOYEES) 
296EVEN IF THERE ARE NO MATCH IN THE RIGHT, (ORDERS)
297
298SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
299FROM Orders
300RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
301ORDER BY Orders.OrderID;
302
303FULL JOIN
304RETURNS ALL ROWS FROM LEFT (CUSTOMERS) AND RIGHT (ORDERS)
305IF THERE ARENT MATCHES ON EITHER SIDE, THE ROWS WILL STILL BE LISTED. 
306
307SELECT Customers.CustomerName, Orders.OrderID
308FROM Customers
309FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
310ORDER BY Customers.CustomerName;
311
312SELF JOIN
313REGULAR JOIN BUT THE TABLE IS JOINED W ITSELF.
314SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
315FROM Customers A, Customers B
316WHERE A.CustomerID <> B.CustomerID
317AND A.City = B.City 
318ORDER BY A.City;
319
320CROSS JOIN
321RESULT SET X NUMBER OF ROWS IN TABLE ONE MULTIPLIED 
322BY THE NUMBER OF ROWS IN THE SECOND TABLE,
323IF THERE IS NO WHERE CLAUSE. 
324IF THERE IS A WHERE CLAUSE IT IS AN INNER JOIN.
325
326SELECT * 
327FROM table 1 
328CROSS JOIN table2;
329
330SELECT foods.item_name,foods.item_unit,
331company.company_name,company.company_city 
332FROM foods 
333CROSS JOIN company;
334
335--UNIONS, EXCEPT, INTERSECT 
336
337UNION 
338COMBINE THE RESULT SET OF TWO OR MORE SELECT STATEMENTS.
339
340SELECT column_name(s) FROM table1
341UNION
342SELECT column_name(s) FROM table2;
343
344UNION ALL TO ALLOW DUPLICATE VALUES.
345SELECT column_name(s) FROM table1
346UNION ALL
347SELECT column_name(s) FROM table2;
348
349COLUMN NAMES ARE USUALLY EQUAL TO THE 
350COLUMN NAME IN THE FIRST SELECT STATEMENT IN THE UNION
351
352UNION WITH WHERE 
353SELECTS DIFFERENT CITIES FROM TWO TABLES. 
354SELECT City, Country FROM Customers
355WHERE Country='Germany'
356UNION
357SELECT City, Country FROM Suppliers
358WHERE Country='Germany'
359ORDER BY City;
360
361UNION ALL W WHERE
362SELECTS ALL CITIES FROM TABLES.
363SELECT City, Country FROM Customers
364WHERE Country='Germany'
365UNION ALL
366SELECT City, Country FROM Suppliers
367WHERE Country='Germany'
368ORDER BY City;
369
370EXCEPT
371
372USED TO RETURN ALL ROWS IN THE FIRST SELECT STATEMENT THAT 
373ARE NOT RETURNED BY THE SECOND SELECT STATEMENT. 
374EACH SELECT STATEMENT WILL DEFINE A DATASET. 
375SELECT product_id
376FROM products
377EXCEPT
378SELECT product_id
379FROM inventory;
380This EXCEPT operator example returns all product_id values 
381that are in the products table and not in the inventory table. 
382What this means is that if a product_id value existed in the products 
383table and also existed in the inventory table, the product_id value would not appear in the EXCEPT query results.
384
385INTERSECT
386
387WILL RETURN THE RECORDS IN THE SHARED AREA BETWEEN TWO TABLES. 
388SELECT supplier_id
389FROM suppliers
390INTERSECT
391SELECT supplier_id
392FROM orders;
393In this SQL INTERSECT example, if a supplier_id appeared in both the suppliers 
394and orders table, it would appear in your result set.
395
396--AGGREGATED FUNCTIONS - SUM, MIN, MAX AND AVERAGE.
397MIN	returns the smallest value in a given column
398MAX	returns the largest value in a given column
399SUM	returns the sum of the numeric values in a given column
400AVG	returns the average value of a given column
401COUNT	returns the total number of values in a given column
402COUNT(*)	returns the number of rows in a table
403
404SELECT AVG(salary)
405FROM employee;
406
407This statement will return a single result which 
408contains the average value of everything returned in the salary 
409column from the employee table.
410
411Another example:
412SELECT AVG(salary)
413FROM employee
414WHERE title = 'Programmer';
415This statement will return the
416average salary for all employee whose
417Title is equal to 'Programmer'
418
419Example:
420SELECT Count(*)
421
422FROM employee;
423
424This particular statement is 
425slightly different from the other aggregate 
426functions since there isnt a column supplied
427To the count function. This statement will 
428return the number of rows in the employees table.
429
430
431--GROUP BY AND HAVING.
432
433GROUP BY clause
434
435The GROUP BY clause will gather all of the 
436rows together that contain data in the specified column(s) 
437and will allow aggregate functions to be performed on the one or more 
438columns. This can best be explained by an example:
439
440GROUP BY
441syntax:
442 
443SELECT column1, 
444SUM(column2)
445 
446FROM "list-of-tables"
447 
448GROUP BY "column-list";
449Lets say you would like to retrieve a list of the highest paid salaries 
450in each dept:
451 
452SELECT max(salary), dept
453FROM employee 
454GROUP BY dept;
455This statement will select the maximum salary 
456for the people in each unique department. Basically, the salary for the person 
457who makes the most in each department will be displayed. Their, salary and their 
458department will be returned.
459
460Multiple Grouping Columns - What if I wanted to display their lastname too?
461
462Use these tables for the exercises
463items_ordered
464customers
465
466For example, take a look at the items_ordered table. Lets say you want to 
467group everything of quantity 1 together, everything of quantity 2 together, 
468everything of quantity 3 together, etc. If you would like to determine what
469 the largest cost item is for each grouped quantity (all quantity 1s, all quantity 2s, 
470 all quantity 3s, etc.), you would enter:
471
472 
473SELECT quantity, max(price)
474FROM items_ordered
475GROUP BY quantity;
476Enter the statement in above, and take a look at the results to see if it 
477returned what you were expecting. Verify that the maximum price in each 
478Quantity Group is really the maximum price.
479
480HAVING 
481HAVING = WHERE, WHERE COULD NOT BE USED WITH AGGREGATED FUNCTIONS.
482
483SELECT column_name(s)
484FROM table_name
485WHERE condition
486GROUP BY column_name(s)
487HAVING condition
488ORDER BY column_name(s)
489
490SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
491FROM (Orders
492INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
493GROUP BY LastName
494HAVING COUNT(Orders.OrderID) > 10;
495
496WEEK NINE.
497
498-VIEWS AND INDEXES
499
500VIEW
501A VIRTUAL TABLE, SELECTIVE PORTION OF DATA FROM ONE OR MORE TABLE.
502DONT CONTAIN DATA OF THEIR OWN, RESTRICT ACCESS TO THE DATA BASE/
503HIDE DATA COMPLEXITY.
504VIEW IS STORED AS A SELECT STATEMENT.
505DML OPERATIONS ARE
506INSERT, UPDATE AND DELETE.
507CREATE VIEW view_product 
508AS 
509SELECT product_id, product_name 
510FROM product; 
511
512INDEX
513CREATS INDEXES IN THE TABLE, TO RETRIEVE DATA FAST. 
514USED TO SPEED UP SEARCHES
515
516CREATE INDEX index_name
517ON table_name (column1, column2, ...);
518UNIQUE INDEXES ARE SO DUPLICATES ARENT ALLOWED.
519VARIES FOR DIFFERENT DATABASES.
520
521-- INSERTS, UPDATES, DELETES AND SELECT QUERIES, 
522-INSERT
523INSERT NEW RECORD INTO A TABLE
524CAN BE WRITTEN IN TWO WAYS.
525INSERT INTO Customers (CustomerName, ContactName, Address, City, 
526PostalCode, Country)
527VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 
528'Stavanger', '4006', 'Norway');
529-UPDATE
530MODIFIES EXISITNG RECORDS IN A TABLE 
531UPDATE table_name
532SET column1 = value1, column2 = value2, ...
533WHERE condition;
534
535UPDATE Customers
536SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
537WHERE CustomerID = 1;
538MAKES ALFREAD THE FIRST CONTACT ID
539
540-DELETE
541deletes existing records in a table
542DELETE FROM table_name
543WHERE condition;
544DELETE FROM AND DELETED * FROM ARE THE SAME.
545
546-- LABS
547--- Lab 6 Practice
548DROP TABLE IF EXISTS Employee_SelfJoin;
549CREATE TABLE Employee_SelfJoin(
550    EmployeeID 	CHAR(  2 ) NOT NULL, 
551    EmpName 	CHAR( 20 ),
552    ManagerID 	CHAR(  2 ),
553    CONSTRAINT EmployeeID_PK PRIMARY KEY( EmployeeID )
554   ----CONSTRAINT ManagerID_FK FOREIGN KEY( ManagerID ) REFERENCES Employee_SelfJoin( EmployeeID )
555);
556
557--- adding constraint outside after table creation. 
558ALTER TABLE Employee_SelfJoin ADD CONSTRAINT ManagerID_FK FOREIGN KEY( ManagerID ) REFERENCES Employee_SelfJoin(EmployeeID);
559
560---eof: SelfJoinDDL.sql
561--REMEMBER that foriegn key can be entered only after primary key has been entered in parent table
562-- A manager has to be entered as an employee, before adding an employee with that person as manager.
563--To avoid any ordering problem, we can break down the insert as 
564-- insert only employee id and name, manager can be NULL
565--then update with manager id, in any order, since all employees have already been entered!
566
567INSERT INTO Employee_SelfJoin( EmployeeID, EmpName ) VALUES( 'g1', 'Guoh' );
568INSERT INTO Employee_SelfJoin( EmployeeID, EmpName ) VALUES( 't1', 'Teef' );
569INSERT INTO Employee_SelfJoin( EmployeeID, EmpName ) VALUES( 'h1', 'Heot' );
570INSERT INTO Employee_SelfJoin( EmployeeID, EmpName ) VALUES( 'm1', 'Meit' );
571INSERT INTO Employee_SelfJoin( EmployeeID, EmpName ) VALUES( 'h2', 'Hoij' );
572INSERT INTO Employee_SelfJoin( EmployeeID, EmpName ) VALUES( 'm2', 'Mooq' );
573
574UPDATE Employee_SelfJoin SET ManagerID='t1' WHERE EmployeeID='g1';
575UPDATE Employee_SelfJoin SET ManagerID='m2' WHERE EmployeeID='t1';
576UPDATE Employee_SelfJoin SET ManagerID='t1' WHERE EmployeeID='h1';
577UPDATE Employee_SelfJoin SET ManagerID='g1' WHERE EmployeeID='m1';
578UPDATE Employee_SelfJoin SET ManagerID='g1' WHERE EmployeeID='h2';
579
580
581--- By ensuring that manager records are entered first and then employee records
582--- INSERT INTO Employee VALUES( 'm2', 'Mooq', NULL );
583--- INSERT INTO Employee VALUES( 't1', 'Teef', 'm2' );
584--- INSERT INTO Employee VALUES( 'g1', 'Guoh', 't1' );
585--- INSERT INTO Employee VALUES( 'h1', 'Heot', 't1' );
586--- INSERT INTO Employee VALUES( 'm1', 'Meit', 'g1' );
587--- INSERT INTO Employee VALUES( 'h2', 'Hoij', 'g1' );
588
589
590--- Rows that do not have employees defined as manager 
591--- cannot be inserted. The following order does not work
592---INSERT INTO Employee VALUES( 'g1', 'Guoh', 't1' );
593---INSERT INTO Employee VALUES( 't1', 'Teef', 'm2' );
594---INSERT INTO Employee VALUES( 'h1', 'Heot', 't1' );
595---INSERT INTO Employee VALUES( 'm1', 'Meit', 'g1' );
596---INSERT INTO Employee VALUES( 'h2', 'Hoij', 'g1' );
597---INSERT INTO Employee VALUES( 'm2', 'Mooq', NULL );
598--- eof: SelfJoinDML.sql
599
600SELECT * FROM Employee_SelfJoin;
601
602SELECT 
603    e1.EmployeeID,
604    e1.EmpName AS Employee,
605    e2.EmpName AS Manager
606FROM
607    Employee_SelfJoin e1,
608    Employee_SelfJoin e2
609WHERE
610    e2.EmployeeID = e1.ManagerID;
611    
612-LAB 8
613
614SELECT * From park_t Where parkname LIKE 'B%';
615SELECT province FROM province_t WHERE abbreviation LIKE (SELECT abbreviation FROM park_t WHERE parkname LIKE 'B%'); 
616SELECT * FROM park_t JOIN province_t; NATURAL JOIN province_t;
617SELECT * From park_t JOIN province_t ON park_t.abbreviation = province_t.abbreviation;
618WHERE parkname LIKE 'B%';
619
620--OUTER JOIN 
621
622SELECT * FROM park_t RIGHT OUTER JOIN province_t ON park_t.abreviation = proviince_t.abbreviation WHERE park_t.parkname IS NULL;
623
624-- CROSS JOIN, NOT USED COMMONLY 
625SELECT * FROM park_t CROSS JOIN province_t;
626
627LAB 8
628
629SELECT
630customers
631FROM customers 
632join countries ON countries.id=customers.country_id  
633where countries.name = 'Canada'
634
635
636SELECT customers.name, customers.city, orders.id, order_date
637FROM orders join customers
638ON orders.customer_id=customers.id
639WHERE customers.id BETWEEN 4563 AND 5678
640ORDER BY orders.order_date
641
642SELECT customers.name, customers.city, orders.id, order_date
643FROM orders join customers
644ON orders.customer_id=customers.id
645WHERE customers.id BETWEEN 4563 AND 5678 AND customers.country_id = '5'
646ORDER BY orders.order_date
647
648SELECT customers.name, customers.city, orders.id, order_date, state_provinces.name as "state"
649FROM orders join customers
650ON orders.customer_id=customers.id
651join state_provinces on customers.state_province_id=state_provinces.id
652WHERE customers.id BETWEEN 4563 AND 5678 AND customers.country_id = '5'
653ORDER BY orders.order_date
654
655SELECT product_version_id,
656sum(price)/sum(quantity) as "Average Price"
657FROM order_lines
658group by product_version_id
659HAVING product_version_id = 198
660
661SELECT product_version_id, MAX(price) as "Maximum Price"
662FROM order_lines
663group by product_version_id
664HAVING product_version_id = 198
665
666SELECT order_id,order_date, count(*) as "# of order lines",order_date,
667sum(extended_price) as "extended price"
668FROM order_lines join orders
669ON order_lines.order_id=orders.id
670group by order_id, orders.order_date
671HAVING order_id = 34567
672
673SELECT product_id, sum(msrp)/count(*) as "Average price of Groove"
674FROM product_versions
675group by product_id
676HAVING product_id = 45
677
678SELECT * FROM(
679SELECT product_id, sum(msrp)/count(*) as "Average price of Groove"
680FROM product_versions
681group by product_id
682HAVING product_id = 45
683)As subquerytest;
684
685SELECT AVG(total), MIN(total), Max(total) FROM(
686SELECT count(*) as "total"
687FROM order_lines
688Group by order_id
689)dtable;
690
691/*SELECT state_provinces.name as "state", order_id
692FROM state_provinces
693join customers on customers.state_province_id=state_provinces.id
694join orders on customers.id
695WHERE state_province.name = 'ontario'*/
696
697--13
698select avg(total_orders) as average FROM 
699(SELECT avg.id as customer_id, count(orders.id) as total_orders
700from customers avg join orders orders on avg.id=orders.customer_id
701join state_provinces province on avg.state_province_id = province.id
702where province.name = 'Ontario'
703group by avg.id) as orders
704
705SELECT email from customers 
706UNION 
707Select email from tradeshow_leads
708SELECT email from customers 
709INTERSECT
710Select email from tradeshow_leads
711SELECT email from customers 
712EXCEPT
713Select email from tradeshow_leads
714
715
716SELECT order_id,sum(quantity), sum(extended_price)
717FROM order_lines
718GROUP BY order_id