\r\nLast Name<\/strong><\/td>\r\nFirst Name<\/strong><\/td>\r\nPhone Number<\/strong><\/td>\r\n<\/tr>\r\n\r\n| Hagans<\/td>\r\n | Jim<\/td>\r\n | 604-232-3232<\/span><\/td>\r\n<\/tr>\r\n\r\n| Wong<\/td>\r\n | Bruce<\/td>\r\n | 604-244-2322<\/span><\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\nTable 16.1. Employees table.<\/em>\r\n\r\nIn this next example, we will use a Publishers table (Table 16.2). (You will notice that Canada is mispelled in the Publisher Country<\/em> field for Example Publishing and ABC Publishing.\u00a0To correct mispelling, use the UPDATE statement to standardize the country field to Canada - see UPDATE statement later in this chapter.)<\/span>\r\n\r\n\r\n\r\nPublisher Name<\/strong><\/td>\r\nPublisher City<\/strong><\/td>\r\nPublisher Province<\/strong><\/td>\r\nPublisher Country<\/strong><\/td>\r\n<\/tr>\r\n\r\n| Acme Publishing<\/td>\r\n | Vancouver<\/td>\r\n | BC<\/td>\r\n | Canada<\/td>\r\n<\/tr>\r\n | \r\n| Example Publishing<\/td>\r\n | Edmonton<\/td>\r\n | AB<\/td>\r\n | Cnada<\/td>\r\n<\/tr>\r\n | \r\n| ABC Publishing<\/td>\r\n | Toronto<\/td>\r\n | ON<\/td>\r\n | Canda<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\nTable 16.2. Publishers table.<\/em>\r\n\r\nIf you add the publisher's name and city, you would use the SELECT statement followed by the fields name separated by a comma:\r\nSELECT PubName, city\r\nFROM Publishers<\/div>\r\nThis action will display the publisher's name and city from the Publishers table.\r\n\r\n If you just want the publisher's name under the display name city, you would use the SELECT statement with no comma<\/em> separating pub_name and city:<\/span>\r\nSELECT\u00a0 PubName city<\/span>\r\nFROM Publishers<\/div>\r\nPerforming this action will dis<\/span>play only the pub_name from the Publishers table with a \"city\" heading. \u00a0If you do not include the comma, SQL Server assumes you want a new column name for pub_name.\u00a0<\/strong>\r\nSELECT statement with WHERE criteria<\/h2>\r\nSometimes you might want to focus on a portion of the Publishers table, such as only publishers that are in Vancouver. In this situation, you would use the SELECT statement with the WHERE criterion, i.e., WHERE city = 'Vancouver'.<\/span>\r\n\r\nThese first two examples illustrate\u00a0how to limit record\u00a0selection\u00a0with the WHERE criterion\u00a0using BETWEEN. Each of these examples give the same results for store items with between 20 and 50 items in stock.<\/span>\r\n\r\nExample #1 uses the quantity, qty BETWEEN 20 and 50<\/em>.\r\nSELECT StorID, qty, TitleID\r\nFROM Sales\r\nWHERE qty BETWEEN 20 and 50\u00a0 (includes the 20 and 50)<\/i><\/div>\r\nExample #2, on the other hand, uses qty >=20 and qty <=50<\/em>\u00a0.\r\nSELECT StorID, qty, TitleID<\/span>\r\nFROM Sales\r\nWHERE qty >= 20 and qty\u00a0 <= 50<\/div>\r\nExample #3 illustrates how to limit record selection with the WHERE criterion using NOT BETWEEN.<\/span>\r\nSELECT StorID, qty, TitleID<\/span>\r\nFROM Sales\r\nWHERE qty NOT BETWEEN 20 and 50<\/div>\r\nThe next two examples show two different ways\u00a0to limit record\u00a0selection\u00a0with the WHERE criterion using IN, with each yielding the same results.\r\n\r\nExample #4 shows how to select records using\u00a0province= <\/em>as part of the WHERE statement.\r\nSELECT *\r\nFROM Publishers\r\nWHERE province = 'BC' OR province = 'AB' OR province = 'ON'<\/div>\r\nExample #5 select records using province IN<\/em> as part of the WHERE statement.\r\nSELECT *\r\nFROM Publishers\r\nWHERE province IN ('BC', 'AB', 'ON')<\/div>\r\nThe final two examples illustrate how NULL and NOT NULL can be used to select records. For these examples, a Books\u00a0table (not shown) would be used that contains fields called Title, Quantity, and Price (of book). Each publisher has a Book s<\/span>\u00a0table that lists all of its books.<\/span>\r\n\r\nExample #6 uses NULL.<\/span>\r\nSELECT price, title\r\nFROM Books\r\nWHERE price IS NULL<\/div>\r\nExample #7 uses NOT NULL.\r\n SELECT price, title\r\nFROM Books\r\nWHERE price IS NOT NULL<\/div>\r\n Using wildcards in the LIKE\u00a0clause<\/span><\/h2>\r\nThe LIKE keyword selects rows containing fields that match specified portions of character strings. LIKE is used with char, varchar, text, datetime and smalldatetime data. A wildcard\u00a0<\/em>allows the user to match fields that contain certain letters. For example, the wildcard province = 'N%' would give all provinces that start with the letter 'N'. Table 16.3 shows four ways to specify wildcards in the SELECT statement in regular express format.\r\n\r\n\r\n\r\n| \r\n %<\/p>\r\n<\/td>\r\n | Any string of zero or more characters<\/td>\r\n<\/tr>\r\n | \r\n| \r\n _<\/p>\r\n<\/td>\r\n | Any single character<\/td>\r\n<\/tr>\r\n | \r\n| \r\n [ ]<\/p>\r\n<\/td>\r\n | Any single character within the specified range (e.g., [a-f]) or set (e.g., [abcdef])<\/td>\r\n<\/tr>\r\n | \r\n| \r\n [^]<\/p>\r\n<\/td>\r\n | Any single character not within the specified range (e.g., [^a - f]) or set (e.g., [^abcdef])<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\nTable 16.3. How to specify wildcards in the SELECT statement.<\/em>\r\n\r\nIn example #1,\u00a0LIKE 'Mc%' searches for all last names that begin with the letters \"Mc\" (e.g., McBadden).\r\nSELECT LastName\r\nFROM Employees\r\nWHERE LastName<\/span>\u00a0LIKE 'Mc%'<\/div>\r\nFor example #2: LIKE '%inger' searches for all last names that end with the letters \"inger\" (e.g., Ringer, Stringer).\r\nSELECT\u00a0 LastName<\/span>\r\nFROM Employees\r\nWHERE LastName<\/span>\u00a0LIKE '%inger'<\/div>\r\nIn, example #3: LIKE '%en%' searches for all last names that have the letters \"en\" (e.g., Bennett, Green, McBadden).\r\nSELECT\u00a0 LastName<\/span>\r\nFROM Employees\r\nWHERE LastName<\/span> LIKE '%en%'<\/div>\r\nSELECT statement with ORDER BY\u00a0clause<\/h2>\r\nYou use the ORDER BY\u00a0clause to sort the records in the resulting list. Use\u00a0ASC\u00a0<\/em>to sort the\u00a0results\u00a0in ascending order and DESC\u00a0<\/em>to sort the results\u00a0in descending order.\r\n\r\nFor example, with ASC:\r\nSELECT *\r\nFROM Employees\r\nORDER BY HireDate ASC<\/div>\r\nAnd with DESC:\r\n SELECT *\r\nFROM Books\r\nORDER B Y type, pric<\/span>e DESC<\/div>\r\nSELECT\u00a0statement with GROUP BY\u00a0clause<\/h2>\r\nThe GROUP BY\u00a0<\/em>clause is used to create one output row per each group and produces summary values for the selected columns, as shown below.\r\nSELECT type\r\nFROM\u00a0Books\r\nGROUP BY type<\/div>\r\nHere is an example using the above statement.\r\n SELECT type AS 'Type', MIN(price) AS 'Minimum Price'\r\nFROM\u00a0Books\r\nWHERE royalty > 10\r\nGROUP BY type<\/div>\r\nIf the SELECT statement includes a WHERE criterion where price is not null<\/em>,\r\nSELECT type, price\r\nFROM\u00a0Books\r\nWHERE price is not null<\/div>\r\nthen a statement with the GROUP BY clause would look like this:\r\n SELECT type AS 'Type', MIN(price) AS 'Minimum Price'\r\nFROM\u00a0Books\r\nWHERE price is not null\r\nGROUP BY type<\/div>\r\n Using COUNT with GROUP BY<\/h3>\r\nWe can use COUNT to tally how many items are in a container. However, if we want to count different items into separate groups, such as marbles of varying colours, then we would use the COUNT function with the GROUP BY command.\r\n\r\nThe below SELECT statement illustrates how to count groups of data using the COUNT function with the GROUP BY clause.\r\nSELECT COUNT(*)\r\nFROM\u00a0Books\r\nGROUP BY type<\/div>\r\n Using AVG and SUM with GROUP BY<\/h3>\r\nWe can use the AVG function to give us the average of any group, and SUM to give the total.\r\n\r\nExample #1 uses the AVG FUNCTION with the GROUP BY type.\r\nSELECT AVG(qty)\r\nFROM\u00a0Books\r\nGROUP BY type<\/div>\r\nExample #2 uses the SUM function with the GROUP BY type.\r\n SELECT SUM(qty)\r\nFROM Books\r\nGROUP BY type<\/div>\r\nExample #3 uses both the AVG and SUM functions with the GROUP BY type in the SELECT statement.\r\n SELECT 'Total Sales' = SUM(qty), 'Average Sales' = AVG(qty), stor_id\r\nFROM Sales\r\nGROUP BY StorID ORDER BY\u00a0 'Total Sales'<\/div>\r\n Restricting rows with HAVING<\/h2>\r\nThe HAVING clause can be used to restrict rows. It is similar to the WHERE condition except HAVING\u00a0can include the aggregate function; the WHERE cannot do this.\r\n\r\nThe HAVING clause behaves like the WHERE clause, but is applicable to groups. In this example, we use the HAVING clause to exclude the groups with the province 'BC'.\r\nSELECT au_fname AS 'Author''s First Name', province as 'Province'\r\nFROM Authors\r\nGROUP BY au_fname, province\r\nHAVING province <> 'BC'<\/div>\r\n INSERT statement<\/h2>\r\nThe INSERT statement<\/em> adds rows to a table. In addition,\r\n\r\n \t- INSERT specifies the table or view that data will be inserted into.<\/li>\r\n \t
- Column_list lists columns that will be affected by the INSERT.<\/li>\r\n \t
- If a column is omitted, each value must be provided.<\/li>\r\n \t
- If you are including columns, they can be listed in any order.<\/li>\r\n \t
- VALUES specifies the data that you want to insert into the table.\u00a0VALUES is required.<\/li>\r\n \t
- Columns with the IDENTITY property should not be explicitly listed in the column_list or values_clause.<\/li>\r\n<\/ul>\r\nThe syntax for the INSERT statement is:\r\n
INSERT [INTO] Table_name | view name [column_list]\r\nDEFAULT VALUES | values_list | select statement<\/div>\r\nWhen inserting rows with the INSERT statement, these rules apply:\r\n \r\n \t- Inserting an empty string (' ') into a varchar or text column inserts a single space.<\/li>\r\n \t
- All char columns are\u00a0right-padded to the defined length.<\/li>\r\n \t
- All trailing spaces are removed\u00a0from data inserted into varchar columns, except in strings that contain\u00a0only spaces. These strings are truncated to a single space.<\/li>\r\n \t
- If an INSERT statement violates a constraint, default or rule, or if it is the wrong data type, the\u00a0statement fails and SQL Server displays an error message.<\/li>\r\n<\/ul>\r\nWhen you specify values for only some of the columns in the column_list, one of three things can happen to the columns that have no values:\r\n
\r\n \t- A default value is entered if\u00a0the column has a DEFAULT constraint, if a default is bound to the column, or if a default is bound to the underlying user-defined data type.<\/li>\r\n \t
- NULL is entered if the column allows NULLs and no default value exists for the column.<\/li>\r\n \t
- An error message is displayed\u00a0and the row is rejected if the column is defined as NOT NULL and no\u00a0default exists.<\/li>\r\n<\/ol>\r\nThis example uses INSERT to add a record to the publisher's Authors table.\r\n
INSERT INTO Authors\r\nVALUES(\u2018555-093-467\u2019, \u2018Martin\u2019, \u2018April\u2019, \u2018281 555-5673\u2019, \u2018816 Market St.,\u2019 , \u2018Vancouver\u2019, \u2018BC\u2019, \u2018V7G3P4\u2019, 0)<\/div>\r\nThis following example illustrates how to insert a partial row into the Publishers table with a column list. The country column had a default value of Canada so it does not require that you include it in your values.\r\n INSERT INTO Publishers\u00a0(PubID, PubName, city, province)\r\nVALUES (\u20189900\u2019, \u2018Acme Publishing\u2019, \u2018Vancouver\u2019, \u2018BC\u2019)<\/div>\r\nTo insert rows into a table with an IDENTITY column, follow the below example.\u00a0Do not supply the value for the IDENTITY nor the name of the column in the column list.\r\n INSERT INTO jobs\r\nVALUES (\u2018DBA\u2019, 100, 175)<\/div>\r\n Inserting specific values into an IDENTITY column<\/h3>\r\nBy default, data cannot be inserted directly into an IDENTITY column; however, if a row is accidentally deleted, or there are gaps in the IDENTITY column values, you can insert a row and specify the IDENTITY column value.\r\nIDENTITY_INSERT option<\/div>\r\nTo allow an insert with a specific identity value, the IDENTITY_INSERT option can be used as follows.\r\n SET IDENTITY_INSERT jobs ON\r\nINSERT INTO jobs \u00a0(job_id, job_desc, min_lvl, max_lvl)\r\nVALUES (19, \u2019DBA2\u2019, 100, 175)\r\nSET IDENTITY_INSERT jobs OFF<\/div>\r\n \u00a0Inserting rows with a SELECT\u00a0statement<\/h3>\r\nWe can sometimes create a small temporary table from a large table. For this, we can insert rows with a SELECT statement. When using this command, there is no validation for uniqueness. Consequently, there may be many rows with the same pub_id in the example below.\r\n\r\nThis example creates a smaller temporary Publishers table using the CREATE TABLE statement. Then the INSERT with a SELECT statement is used to add records to this temporary Publishers table from the publis table.\r\nCREATE TABLE dbo.tmpPublishers (\r\nPubID char (4) NOT NULL ,\r\nPubName varchar (40) NULL ,\r\ncity varchar (20) NULL ,\r\nprovince char (2) NULL ,\r\ncountry varchar (30) NULL\u00a0 DEFAULT (\u2018Canada\u2019)\r\n)\r\nINSERT\u00a0 tmpPublishers\r\nSELECT * FROM Publishers<\/div>\r\nIn this example, we're copying a subset of data.\r\n INSERT tmpPublishers (pub_id, pub_name)\r\nSELECT PubID, PubName\r\nFROM Publishers<\/div>\r\nIn this example, the publishers\u2019 data are copied to the tmpPublishers table and the country column is set to Canada.\r\n INSERT tmpPublishers ( PubID, PubName<\/span>, city, province, country)\r\nSELECT PubID, PubName<\/span>, city, province, \u2018Canada\u2019\r\nFROM Publishers<\/div>\r\nUPDATE statement<\/h2>\r\nThe UPDATE\u00a0statement<\/em> changes data in existing rows either by adding new data or modifying existing data.\r\n\r\nThis example uses the UPDATE statement to standardize the country field to be Canada for all records in the Publishers table.\r\nUPDATE Publishers\r\nSET country = \u2018Canada\u2019<\/div>\r\nThis example increases the royalty amount by 10% for those royalty amounts between 10 and 20.\r\n UPDATE roysched\r\nSET royalty = royalty + (royalty * .10)\r\nWHERE royalty BETWEEN 10 and 20<\/div>\r\n Including subqueries in an UPDATE statement<\/h3>\r\nThe employees from the Employees table who were hired by the publisher in 2010 are given a promotion to the highest job level for their job type. This is what the UPDATE statement would look like.\r\nUPDATE Employees\r\nSET job_lvl =\r\n(SELECT max_lvl FROM jobs\r\nWHERE employee.job_id = jobs.job_id)\r\nWHERE DATEPART(year, employee.hire_date) = 2010<\/div>\r\n DELETE\u00a0statement<\/h2>\r\nThe DELETE\u00a0statement<\/em> removes rows from a record set. DELETE names the table or view that holds the rows that will be deleted and only one table or row may be listed at a time. WHERE is\u00a0 a standard WHERE clause that limits the deletion to select records.\r\n\r\nThe DELETE syntax looks like this.\r\nDELETE [FROM] {table_name | view_name }\r\n[WHERE clause]<\/div>\r\nThe rules for the DELETE statement are:\r\n \r\n \t- If you omit a WHERE clause, all rows in the table are removed\u00a0(except for indexes, the table, constraints).<\/i><\/li>\r\n \t
- DELETE cannot be used with a view that has a FROM clause naming more than one table. (Delete can affect only one base table at a time.)<\/li>\r\n<\/ol>\r\nWhat follows are three different DELETE statements that can be used.\r\n\r\n1. Deleting all rows from a table.\r\n
DELETE\r\nFROM Discounts<\/div>\r\n2. Deleting selected rows:\r\n DELETE\r\nFROM Sales\r\nWHERE stor_id = \u20186380\u2019<\/div>\r\n3. Deleting rows based on a value in a subquery:\r\n DELETE FROM Sales\r\nWHERE title_id IN\r\n(SELECT title_id FROM Books\u00a0WHERE type = \u2018mod_cook\u2019)<\/div>\r\n Built-in Functions<\/h1>\r\nThere are many built-in functions in SQL Server such as:\r\n\r\n \t- Aggregate:<\/em>\u00a0returns summary values<\/li>\r\n \t
- Conversion:<\/em>\u00a0transforms one data type to another<\/li>\r\n \t
- Date:<\/em>\u00a0displays information about dates and times<\/li>\r\n \t
- Mathematical:<\/em>\u00a0performs operations on numeric data<\/li>\r\n \t
- String:<\/em>\u00a0performs operations on character strings, binary data or expressions<\/li>\r\n \t
- System:<\/em>\u00a0returns a special piece of information from the database<\/li>\r\n \t
- Text and image:<\/em>\u00a0performs operations on text and image data<\/li>\r\n<\/ol>\r\nBelow you will find detailed descriptions and examples for the first four functions.\r\n
Aggregate functions<\/h2>\r\nAggregate functions perform a calculation on a set of values and return a single, or summary, value. Table 16.4 lists these functions.\r\n\r\n\r\n\r\nFUNCTION<\/strong><\/td>\r\nDESCRIPTION<\/strong><\/td>\r\n<\/tr>\r\n\r\n| AVG<\/td>\r\n | Returns the average of all the values, or only the DISTINCT values, in the expression.<\/td>\r\n<\/tr>\r\n | \r\n| COUNT<\/td>\r\n | Returns the number of non-null values in the expression. When DISTINCT is specified, COUNT finds the number of unique non-null values.<\/td>\r\n<\/tr>\r\n | \r\n| COUNT(*)<\/td>\r\n | Returns the number of rows. COUNT(*) takes no parameters and cannot be used with DISTINCT.<\/td>\r\n<\/tr>\r\n | \r\n| MAX<\/td>\r\n | Returns the maximum value in the expression. MAX can be used with numeric, character and datetime columns, but not with bit columns. With character columns, MAX finds the highest value in the collating sequence. MAX ignores any null values.<\/td>\r\n<\/tr>\r\n | \r\n| MIN<\/td>\r\n | Returns the minimum value in the expression. MIN can be used with numeric, character and datetime columns, but not with bit columns. With character columns, MIN finds the value that is lowest in the sort sequence. MIN ignores any null values.<\/td>\r\n<\/tr>\r\n | \r\n| SUM<\/td>\r\n | Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only.<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\nTable 16.4\u00a0A list of aggregate functions and descriptions.<\/em>\r\n\r\nBelow are examples of each of the aggregate functions listed in Table 16.4.\r\n\r\nExample #1: \u00a0AVG<\/strong>\r\nSELECT AVG (price) AS 'Average Title Price'\r\nFROM\u00a0Books<\/div>\r\n Example #2: COUNT<\/strong>\r\nSELECT COUNT(PubID) AS 'Number of Publishers'\r\nFROM Publishers<\/div>\r\n Example #3: COUNT<\/strong>\r\nSELECT COUNT(province) AS 'Number of Publishers'\r\nFROM Publishers<\/div>\r\n Example #3: COUNT (*)<\/strong>\r\nSELECT COUNT(*)\r\nFROM Employees\r\nWHERE job_lvl = 35<\/div>\r\n Example #4: MAX<\/strong>\r\nSELECT MAX (HireDate)\r\nFROM Employees<\/div>\r\n Example #5: MIN<\/strong>\r\nSELECT MIN (price)\r\nFROM\u00a0Books<\/div>\r\n Example #6: SUM<\/strong>\r\nSELECT SUM(discount) AS 'Total Discounts'\r\nFROM Discounts<\/div>\r\n Conversion function<\/h2>\r\nThe conversion function\u00a0transforms one data type to another.\r\n\r\nIn the example below, a price that contains two 9s is converted into five characters. The syntax for this statement is\u00a0SELECT 'The date is ' + CONVERT(varchar(12), getdate()).\r\nSELECT CONVERT(int, 10.6496)\r\nSELECT title_id, price\r\nFROM\u00a0Books\r\nWHERE CONVERT(char(5), price) LIKE '%99%'<\/div>\r\nIn this second example, the conversion function changes data to a data type with a different size.\r\n SELECT title_id, CONVERT(char(4), ytd_sales) as 'Sales'\r\nFROM\u00a0Books\r\nWHERE type LIKE '%cook'<\/div>\r\n Date function<\/h2>\r\nThe date function produces a date by adding an interval to a specified date. The result is a datetime value equal to the date plus the number of date parts. If the date parameter is a smalldatetime value, the result is also a smalldatetime value.\r\n\r\nThe DATEADD function is used to add and increment date values. The\u00a0syntax\u00a0for this function is\u00a0DATEADD(datepart, number, date).\r\nSELECT DATEADD(day, 3, hire_date)\r\nFROM Employees<\/div>\r\nIn this example, the\u00a0function\u00a0DATEDIFF(datepart, date1, date2) \u00a0is used.\r\n\r\nThis command returns the number of datepart \"boundaries\" crossed between two specified dates. The method of counting crossed boundaries makes the result given by DATEDIFF consistent across all data types such as minutes, seconds, and milliseconds.\r\n SELECT DATEDIFF(day, HireDate, 'Nov 30 1995')\r\nFROM Employees<\/div>\r\nFor any particular date, we can examine any part of that date from the year to the millisecond.\r\n\r\nThe date parts (DATEPART) and abbreviations recognized by SQL Server, and the acceptable values are listed in Table 16.5.\r\n \r\n\r\n\r\nDATE PART<\/strong><\/td>\r\nABBREVIATION<\/strong><\/td>\r\nVALUES<\/strong><\/td>\r\n<\/tr>\r\n\r\n| Year<\/td>\r\n | yy<\/td>\r\n | 1753-9999<\/td>\r\n<\/tr>\r\n | \r\n| Quarter<\/td>\r\n | qq<\/td>\r\n | 1-4<\/td>\r\n<\/tr>\r\n | \r\n| Month<\/td>\r\n | mm<\/td>\r\n | 1-12<\/td>\r\n<\/tr>\r\n | \r\n| Day of year<\/td>\r\n | dy<\/td>\r\n | 1-366<\/td>\r\n<\/tr>\r\n | \r\n| Day<\/td>\r\n | dd<\/td>\r\n | 1-31<\/td>\r\n<\/tr>\r\n | \r\n| Week<\/td>\r\n | wk<\/td>\r\n | 1-53<\/td>\r\n<\/tr>\r\n | \r\n| Weekday<\/td>\r\n | dw<\/td>\r\n | 1-7 (Sun.-Sat.)<\/td>\r\n<\/tr>\r\n | \r\n| Hour<\/td>\r\n | hh<\/td>\r\n | 0-23<\/td>\r\n<\/tr>\r\n | \r\n| Minute<\/td>\r\n | mi<\/td>\r\n | 0-59<\/td>\r\n<\/tr>\r\n | \r\n| Second<\/td>\r\n | ss<\/td>\r\n | 0-59<\/td>\r\n<\/tr>\r\n | \r\n| Millisecond<\/td>\r\n | ms<\/td>\r\n | 0-999<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\nTable 16.5. Date part abbreviations and values.<\/em>\r\nMathematical functions\u00a0<\/b><\/h2>\r\nMathematical functions\u00a0perform operations on numeric data. The following example lists the current price for each book sold by the publisher\u00a0and what they would be if all prices increased by 10%.\r\nSELECT Price, (price * 1.1) AS 'New Price', title\r\nFROM\u00a0Books\r\nSELECT 'Square Root' = SQRT(81)\r\nSELECT 'Rounded\u2018 = ROUND(4567.9876,2)\r\nSELECT FLOOR (123.45)<\/div>\r\n Joining Tables<\/h1>\r\nJoining two or more tables is the process of comparing the data in specified columns and using the comparison results to form a new table from the rows that qualify. A join statement:\r\n\r\n \t- Specifies a column from each table<\/li>\r\n \t
- Compares the values in those columns row by row<\/li>\r\n \t
- Combines rows with qualifying values into a new row<\/li>\r\n<\/ul>\r\nAlthough the comparison is usually for equality \u2013 values that match exactly \u2013 other types of joins can also be specified. All the different joins such as inner, left (outer), right (outer), and cross join will be described below.<\/span>\r\n
Inner join<\/h2>\r\nAn inner join<\/em> connects two tables\u00a0on a column with the same data type. Only the rows where the column values match are returned; unmatched rows are discarded.\r\n\r\nExample #1<\/strong>\r\nSELECT jobs.job_id, job_desc\r\nFROM jobs\r\nINNER JOIN Employees ON employee.job_id = jobs.job_id\r\nWHERE jobs.job_id < 7<\/div>\r\n Example #2\r\n<\/b>\r\nSELECT authors.au_fname, authors.au_lname, books.royalty, title\r\nFROM authorsINNER JOIN titleauthor ON authors.au_id=titleauthor.au_id\r\nINNER JOIN books\u00a0ON titleauthor.title_id=books.title_id\r\nGROUP BY authors.au_lname, authors.au_fname, title, title.royalty\r\nORDER BY authors.au_lname<\/div>\r\n Left outer join<\/h2>\r\nA left outer join<\/em> specifies that all left outer rows be returned. All rows from the left table that did not meet the condition specified are included in the results set, and output columns from the other table are set to NULL.\r\n\r\nThis first example uses the new syntax for a left outer join.\r\nSELECT publishers.pub_name, books.title\r\nFROM Publishers\r\nLEFT OUTER JOIN Books\u00a0On publishers.pub_id = books.pub_id<\/div>\r\nThis is an example of a left outer join using the old syntax.\r\n SELECT publishers.pub_name, books.title\r\nFROM Publishers,\u00a0Books\r\nWHERE publishers.pub_id *= books.pub_id<\/div>\r\n Right outer join<\/h2>\r\nA right outer join<\/em> includes, in its result set, all rows from the right table that did not meet the condition specified. Output columns that correspond to the other table are set to NULL.\r\n\r\nBelow is an example using the new syntax for a right outer join.\r\nSELECT titleauthor.title_id, authors.au_lname, authors.au_fname\r\nFROM titleauthor\r\nRIGHT OUTER JOIN authors\u00a0ON titleauthor.au_id = authors.au_id\r\nORDERY BY au_lname<\/div>\r\nThis second example show the old syntax used for a right outer join.\r\n SELECT titleauthor.title_id, authors.au_lname, authors.au_fname\r\nFROM titleauthor, authors\r\nWHERE titleauthor.au_id =* authors.au_id\r\nORDERY BY au_lname<\/div>\r\n Full outer join<\/h2>\r\nA full outer join<\/em> specifies that if a row from either table does not match the selection criteria, the row is included in the result set, and its output columns that correspond to the other table are set to NULL.\r\n\r\nHere is an example of a full outer join.\r\nSELECT books.title, publishers.pub_name, publishers.province\r\nFROM Publishers\r\nFULL OUTER JOIN Books\u00a0ON books.pub_id = publishers.pub_id\r\nWHERE (publishers.province <> \u201cBC\u201d and publishers.province <> \u201cON\u201d)\r\nORDER BY books.title_id<\/div>\r\n \u00a0<\/b>Cross join<\/h2>\r\nA cross join<\/em> is a product combining two tables. This join returns the same rows as if no WHERE clause were specified. For example:\r\nSELECT au_lname, pub_name,\r\nFROM Authors CROSS JOIN Publishers<\/div>\r\n \r\n | | | | | | | | | | | | | | | | | |