SQL Server programming must know--(58-79-point summary)

------------------------Create an advanced junction-----------------------
--58. Using Table/Column aliases
--58.1. Using aliases on tables
SELECT Cust_name,cust_contact
From customers as c,orders as o,orderitems as Oi
WHERE c.cust_id =o.cust_id
and Oi.order_num = O.order_num
and prod_id = ' TNT2 '

--Results:
Cust_name Cust_contact
Coyote INC. Y Lee
Yosemite Place Y Sam

--58.2. Using Aliases on Columns
SELECT Cust_name as c_name,cust_contact as C_contact
From customers as c,orders as o,orderitems as Oi
WHERE c.cust_id =o.cust_id
and Oi.order_num = O.order_num
and prod_id = ' TNT2 '

--Results:
C_name C_contact
Coyote INC. Y Lee
Yosemite Place Y Sam

--58.3. A normal representation:
SELECT Cust_name,cust_contact
From Customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
and orderitems.order_num= Orders.order_num
and prod_id = ' TNT2 '

--59. Use different types of junctions

--59.1. Self-coupling: Find the supplier with the production ID of dtntr goods, and then find out the other items produced by this supplier; the self-coupling is sometimes faster than the subquery;
SELECT p1.prod_id,p1.prod_name,p1.vend_id
From products as P1, products as P2
WHERE p1.vend_id = p2.vend_id
and p2.prod_id = ' dtntr '

--59.2. Subquery: Locate the supplier with the production ID of the DTNTR item, and then find out what other items the supplier produces
SELECT prod_id, prod_name,vend_id
From Products
WHERE vend_id = (SELECT vend_id
From Products
WHERE prod_id = ' dtntr ')
--60. Natural coupling: Excludes multiple occurrences of the returned data, so that each column is returned only once; the same column in the standard self-junction may appear multiple times;
SELECT c.*, O.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price
From customers as C, orders as O, OrderItems as Oi
WHERE c.cust_id = o.cust_id
and Oi.order_num = O.order_num
and prod_id= ' FB '

--61. Outer joins: Associates rows in one table with rows in another table, returning rows that contain no associated rows;

-How many orders are counted for each customer, including those who have not yet placed an order
SELECT Customers.cust_name,customers.cust_id,orders.order_num
--right OUTER Join selects all rows from the table (Orders table) to the right of the FROM clause
--left OUTER Join selects all rows from the table (Customers table) to the left of the FROM clause
From customers left OUTER JOIN orders
On customers.cust_id = orders.cust_id

--Simplified use of previous versions:
SELECT Customers.cust_name,customers.cust_id,orders.order_num
From Customers, orders
WHERE customers.cust_id *= orders.cust_id

--full outer join, retrieving unrelated rows from each table (these rows have null values for the non-selective columns of the other table)
SELECT Customers.cust_name,customers.cust_id,orders.order_num
From customers full OUTER JOIN orders
On customers.cust_id = orders.cust_id

--Retrieving all customers and their orders
SELECT Customers.cust_name,customers.cust_id,orders.order_num
From Customers INNER JOIN orders
On customers.cust_id = orders.cust_id

--List all products and order quantities, including products that are not ordered by people

-Calculate average sales size, including customers who have not yet placed an order

--62. Use a junction with an aggregate function:

--Retrieve the number of orders placed by all customers and each customer
SELECT Customers.cust_name,
CUSTOMERS.CUST_ID,
COUNT (orders.order_num) as Num_ord
From Customers INNER JOIN orders
On customers.cust_id = orders.cust_id
GROUP by Customers.cust_name,
customers.cust_id

--Retrieve the number of orders placed by all customers and each customer, including those who have not placed any orders
SELECT Customers.cust_name,
CUSTOMERS.CUST_ID,
COUNT (orders.order_num) as Num_ord
From customers left OUTER JOIN orders
On customers.cust_id = orders.cust_id
GROUP by Customers.cust_name,
customers.cust_id

--63. Considerations for using Junctions

--internal coupling is generally used, but external coupling is also effective.
--Ensure that the correct join conditions are used, otherwise incorrect data will be returned.
--should always provide the coupling condition, otherwise the Cartesian product will be obtained.
--you can include more than one table in a junction, or even a different junction type for each junction.

------------------------Combination Query-----------------------

--64. There are two basic scenarios in which you need to use a combination query:
--1. Return a similar structure of data from a different table in a single query
--2. Execute multiple queries against a single table, returning data as a single query

--Find a list of all items with a price less than or equal to 5
SELECT vend_id,prod_id, Prod_price
From Products
WHERE Prod_price <= 5

--Find a list of all items with a price less than or equal to 5, and also want to include all items produced by suppliers 1001 and 1002.
SELECT vend_id, prod_id, Prod_price
From Products
WHERE Prod_price <=5
UNION
SELECT vend_id, Prod_id,prod_price
From Products
WHERE vend_id in (1001,1002)

--equivalent to
SELECT vend_id,prod_id, Prod_price
From Products
WHERE Prod_price <=5
OR vend_id in (1001,1002)

--65. Union Usage precautions
--must consist of two or more than two SELECT statements separated by a keyword union
--Each query in the Union must contain the same column, expression, or aggregate function, and each column must be listed in the same order
--column data types must be compatible: types do not have to be identical, but must be types that SQL Server can implicitly convert
--Union will automatically cancel the duplicate rows

--66. UNION all is used to return all matching rows, including duplicate rows
SELECT vend_id, prod_id, Prod_price
From Products
WHERE Prod_price <=5
UNION All
SELECT vend_id, Prod_id,prod_price
From Products
WHERE vend_id in (1001,1002)

--67. Sort the results of a combined query
SELECT vend_id, prod_id, Prod_price
From Products
WHERE Prod_price <=5
UNION
SELECT vend_id, Prod_id,prod_price
From Products
WHERE vend_id in (1001,1002)
ORDER by Vend_id,prod_price

------------------------Full-Text Search-----------------------

--Full text search, SQL Server does not need to view each row separately, and does not need to parse and process each word separately.
--SQL Server creates an index of the words in the specified column, and the search can be made for those words. In this way, SQL Server can quickly and efficiently think which words match (which rows contain them) and which words do not match;

--Set full-text search requirements:
--Full-text search support must be enabled for the corresponding database
--Must define a directory
--You must establish a full-text index on the tables and columns to be indexed

--68. Enable full-Text search support
EXEC sp_fulltext_database ' Enable '

--69. Create a Directory
CREATE Fulltext CATALOG Catalog_crashcourse

--70. Create a full-text index, indexed column Note_text column. A key that uniquely identifies each row, using key index to provide the table's primary key name Pk_productnotes,on clause to store the full-text data in the directory.
CREATE fulltext INDEX on Productnotes (Note_text)
KEY INDEX Pk_productnotes
On Catalog_crashcourse

--Do not use full-text indexing when importing data

--Managing catalogs and indexes

--71. Delete and rebuild the catalog index to effectively perform a full re-index.
ALTER Fulltext CATALOG Catalog_crashcourse REBUILD

--72. Use FREETEXT to search

--Full-text Search

--Fretext A simple search, matching by meaning;
--Contains the search for words or phrases, including approximate words and derived words;
--Fretext,contains can be used in the WHERE clause of the SELECT statement;

SELECT note_id, Note_text
From Productnotes
WHERE FREETEXT (Note_text, ' rabbit food ')

--The row in the query column that contains the phrase rabbit food has no result output because the phrase does not appear in any row.
SELECT note_id, Note_text
From Productnotes
WHERE note_text like '%rabbit food% '

--73. Search using contains to find the word handsaw in the column note_text;
SELECT note_id, Note_text
From Productnotes
WHERE CONTAINS (note_text, ' Handsaw ')

--74. Use contains to search, support wildcard characters, in the column Note_text find the word contains "anvil, after any match";
SELECT note_id, Note_text
From Productnotes
WHERE CONTAINS (Note_text, ' "anvil*")

--75. Search with contains, support Boolean operators and,or and not; match only rows that contain safe and handsaw
SELECT note_id, Note_text
From Productnotes
WHERE CONTAINS (Note_text, ' safe and Handsaw ')

--76. Search using contains, support for Boolean operators and,or and not; Matches only rows that contain words rabbit and do not contain word food
SELECT note_id, Note_text
From Productnotes
WHERE CONTAINS (Note_text, ' Rabbit and not food ')

--77. Search using contains to match only the lines containing the words detonate and quickly that are close to each other
SELECT note_id, Note_text
From Productnotes
WHERE CONTAINS (Note_text, ' detonate near quickly ')

--78. Use contains to search for words that have the same stemming as the word vary, such as varies
SELECT note_id, Note_text
From Productnotes
WHERE CONTAINS (Note_text, ' formsof (inflectional,vary) ')

--79. Results of Sort Search
--Using the FREETEXT type of search, use the freetexttable () function to provide a search pattern that instructs the full-text engine to match lines that contain words that mean rabbit and food
--FREETEXTTABLE () returns a table with an alias of F, which contains a column named key that matches the primary key of the table being indexed, and a column named Rank, which is the assigned rank value.
-The first row has a rank of 256, which indicates a good match, and the second row is 45, indicating a poor match.

SELECT F.rank, note_id, Note_text
From Productnotes,
Freetexttable (Productnotes,note_text, ' rabbit food ') f
WHERE productnotes.note_id = F.[key]
ORDER by RANK DESC

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1703603

SQL Server programming must know--(58-79-point summary)