JOINS COMMAND
1. INNER JOIN
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
AND a.state = p.state
ORDER BY a.au_lname ASC, a.au_fname ASC
2. Outer Join
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
OR,
Outer Join
SELECT employee.job_id, employee.emp_id,
employee.fname, employee.minit, jobs.job_desc
FROM employee LEFT OUTER JOIN jobs ON
employee.job_id = jobs.job_id
3.
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
OR,
USE pubs
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t
ON s.title_id = t.title_id
AND s.qty > 50
ORDER BY s.stor_id ASC
4.
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
SELECT COMMAND:-
A. Use SELECT to retrieve rows and columns
USE pubsSELECT *FROM authorsORDER BY au_lname ASC, au_fname ASC
B. Use SELECT with column headings and calculations
USE pubsSELECT ytd_sales AS Sales, authors.au_fname + ' '+ authors.au_lname AS Author, ToAuthor = (ytd_sales * royalty) / 100, ToPublisher = ytd_sales - (ytd_sales * royalty) / 100FROM titles INNER JOIN titleauthor ON titles.title_id = titleauthor.title_id INNER JOIN authors ON titleauthor.au_id = authors.au_id
ORDER BY Sales DESC, Author ASC
C. Use DISTINCT with SELECT
This example uses DISTINCT to prevent the retrieval of duplicate author ID numbers.USE pubsSELECT DISTINCT au_idFROM authorsORDER BY au_id
D. Create tables with SELECT INTO
This first example creates a temporary table named #coffeetabletitles in tempdb. To use this table, always refer to it with the exact name shown, including the number sign (#). USE pubsDROP TABLE #coffeetabletitlesGOSET NOCOUNT ONSELECT * INTO #coffeetabletitlesFROM titlesWHERE price < $20SET NOCOUNT OFFSELECT name FROM tempdb..sysobjects WHERE name LIKE '#c%' This second example creates a permanent table named ‘newtitles’USE pubsIF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'newtitles') DROP TABLE newtitlesGOEXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'USE pubsSELECT * INTO newtitlesFROM titlesWHERE price > $25 OR price < $20SELECT name FROM sysobjects WHERE name LIKE 'new%'USE masterEXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false' SUBQUERY COMMAND
Use correlated subqueries
USE pubsSELECT DISTINCT pub_nameFROM publishersWHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'business')
OR,
USE pubsSELECT t1.typeFROM titles t1GROUP BY t1.typeHAVING MAX(t1.advance) >= ALL (SELECT 2 * AVG(t2.advance) FROM titles t2 WHERE t1.type = t2.type) Use DELETE based on a subquery
/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')
Use GROUPBY COMMAND
This example finds the total year-to-date sales of each publisher in the database.USE pubsSELECT pub_id, SUM(ytd_sales) AS totalFROM titlesGROUP BY pub_idORDER BY pub_id
Use GROUP BY with multiple groups
This example finds the average price and the sum of year-to-date sales, grouped by type and publisher ID.USE pubsSELECT type, pub_id, AVG(price) AS 'avg', sum(ytd_sales) AS 'sum'FROM titlesGROUP BY type, pub_idORDER BY type, pub_id
H. Use GROUP BY and WHERE
This example puts the results into groups after retrieving only the rows with advances greater than $5,000.USE pubsSELECT type, AVG(price)FROM titlesWHERE advance > $5000GROUP BY typeORDER BY type
Use GROUP BY with an expression
This example groups by an expression. You can group by an expression if the expression does not include aggregate functions.USE pubsSELECT AVG(ytd_sales), ytd_sales * royaltyFROM titlesGROUP BY ytd_sales * royaltyORDER BY ytd_sales * royalty
Use GROUP BY with ORDER BY
This example finds the average price of each type of book and orders the results by average price.USE pubsSELECT type, AVG(price)FROM titlesGROUP BY typeORDER BY AVG(price)
Use the HAVING clause
The first example shows a HAVING clause with an aggregate function. It groups the rows in the titles table by type and eliminates the groups that include only one book. The second example shows a HAVING clause without aggregate functions. It groups the rows in the titles table by type and eliminates those types that do not start with the letter p. USE pubsSELECT typeFROM titlesGROUP BY typeHAVING COUNT(*) > 1ORDER BY type
This query uses the LIKE clause in the HAVING clause.USE pubsSELECT typeFROM titlesGROUP BY typeHAVING type LIKE 'p%'ORDER BY type
Use HAVING with SUM and AVG
This example groups the titles table by publisher and includes only those groups of publishers who have paid more than $25,000 in total advances and whose books average more than $15 in price.USE pubsSELECT pub_id, SUM(advance), AVG(price)FROM titlesGROUP BY pub_idHAVING SUM(advance) > $25000 AND AVG(price) > $15
USE pubsSELECT pub_id, total = SUM(ytd_sales)FROM titlesGROUP BY pub_idHAVING SUM(ytd_sales) > 40000
Use SELECT with GROUP BY, COMPUTE, and ORDER BY clauses
This example returns only those rows with current year-to-date sales, and then computes the average book cost and total advances in descending order by type. Four columns of data are returned, including a truncated title. All computed columns appear within the select list. USE pubsSELECT CAST(title AS char(20)) AS title, type, price, advanceFROM titlesWHERE ytd_sales IS NOT NULLORDER BY type DESCCOMPUTE AVG(price), SUM(advance) BY typeCOMPUTE SUM(price), SUM(advance)
Use the ROLLUP operator
This example shows two code examples. This first example retrieves the product name, customer name, and the sum of orders placed and uses the ROLLUP operator.USE pubsSELECT product_name, customer_name, SUM(number_of_orders) AS 'Sum orders'FROM cube_examplesGROUP BY product_name, customer_nameWITH ROLLUP
UPDATE Command
UPDATE authors SET authors.au_fname = 'Annie' WHERE au_fname = 'Anne'
OR,
UPDATE titles SET ytd_sales = t.ytd_sales + s.qty FROM titles t, sales s WHERE t.title_id = s.title_id AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
Use the UPDATE statement using information from another table
This example modifies the ytd_sales column in the titles table to reflect the most recent sales recorded in the sales table. UPDATE titles SET ytd_sales = titles.ytd_sales + sales.qty FROM titles, sales WHERE titles.title_id = sales.title_id AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
INSERT COMMAND
A. Use a simple INSERT
This example creates the table T1 and inserts one row.IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1GOCREATE TABLE T1 ( column_1 int, column_2 varchar(30))INSERT T1 VALUES (1, 'Row #1')
B. Insert data that is not in the same order as the columns
This example uses column_list and VALUES list to explicitly specify the values that are inserted into each column.IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1GOCREATE TABLE T1 ( column_1 int, column_2 varchar(30))INSERT T1 (column_2, column_1) VALUES ('Row #1',1)
C. Insert data with fewer values than columns
This example creates a table that has four columns. The INSERT statements insert rows that contain values for some of the columns, but not all of them.IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1GOCREATE TABLE T1 ( column_1 int identity, column_2 varchar(30) CONSTRAINT default_name DEFAULT ('column default'), column_3 int NULL, column_4 varchar(40))INSERT INTO T1 (column_4) VALUES ('Explicit value')INSERT INTO T1 (column_2,column_4) VALUES ('Explicit value', 'Explicit value')INSERT INTO T1 (column_2,column_3,column_4) VALUES ('Explicit value',-44,'Explicit value')SELECT * FROM T1
DELETE COMMAND
A. Use DELETE with no parameters
This example deletes all rows from the authors table.USE pubsDELETE authors
B. Use DELETE on a set of rows
Because au_lname may not be unique, this example deletes all rows in which au_lname is McBadden.USE pubsDELETE FROM authorsWHERE au_lname = 'McBadden'
C. Use DELETE on the current row of a cursor
This example shows a delete made against a cursor named complex_join_cursor. It affects only the single row currently fetched from the cursor.USE pubsDELETE FROM authorsWHERE CURRENT OF complex_join_cursor
CREATE A VIEW
Examples
A. Use a simple CREATE VIEW
This example creates a view with a simple SELECT statement. A simple view is helpful when a combination of columns is queried frequently.USE pubsIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'titles_view') DROP VIEW titles_viewGOCREATE VIEW titles_viewAS SELECT title, type, price, pubdateFROM titlesGO
B. Use WITH ENCRYPTION
This example uses the WITH ENCRYPTION option and shows computed columns, renamed columns, and multiple columns.USE pubsIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'accounts') DROP VIEW accountsGOCREATE VIEW accounts (title, advance, amt_due)WITH ENCRYPTIONAS SELECT title, advance, price * royalty * ytd_salesFROM titlesWHERE price > $5GO
Here is the query to retrieve the identification number and text of the encrypted stored procedure:USE pubsGOSELECT c.id, c.text FROM syscomments c, sysobjects oWHERE c.id = o.id and o.name = 'accounts'GO
C. Use WITH CHECK OPTION
This example shows a view named CAonly that allows data modifications to apply only to authors within the state of California.USE pubsIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'CAonly') DROP VIEW CAonlyGOCREATE VIEW CAonlyAS SELECT au_lname, au_fname, city, stateFROM authorsWHERE state = 'CA'WITH CHECK OPTIONGO
D. Use @@ROWCOUNT function in a view
This example uses the @@ROWCOUNT function as part of the view definition.USE pubsIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'myview') DROP VIEW myviewGOCREATE VIEW myviewAS SELECT au_lname, au_fname, @@ROWCOUNT AS bar FROM authors WHERE state = 'UT'GOSELECT * FROM myview
No comments:
Post a Comment