/*============================================================================
  File:     QueriesForCredit.sql

  Summary:  Sample queries to send through Database Tuning Advisor - against
			the Credit sample database.

  Date:     June 2005

  SQL Server Version: 9.00.1187.07 (June CTP)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE credit
go

UPDATE Member
SET LastName = 'Tripp'
WHERE Member_No = 6798
go

UPDATE Member
SET FirstName = 'Kimberly'
WHERE Member_No = 2896
go

UPDATE Member
SET FirstName = 'Kimberly',
	LastName = 'Tripp'
WHERE Member_No = 842
go

SET STATISTICS IO ON
go

SELECT LastName, FirstName, Region_No
FROM Member
WHERE FirstName = 'Kimberly'
OR LastName = 'Tripp'
go

SELECT LastName, FirstName, Region_No
FROM Member
WHERE FirstName LIKE 'Kimberly'
go
SELECT LastName, FirstName, Region_No
FROM Member
WHERE LastName LIKE 'Tripp'
go
SELECT LastName, FirstName, Region_No
FROM Member
WHERE FirstName LIKE 'Kimberly'
	OR LastName LIKE 'Tripp'
go

SELECT LastName, FirstName, Region_No
FROM Member
WHERE FirstName LIKE 'Kimberly'
UNION
SELECT LastName, FirstName, Region_No
FROM Member
WHERE LastName LIKE 'Tripp'
go

SELECT FirstName, Member_No
FROM Member
WHERE FirstName > ''
ORDER BY FirstName
go

SET STATISTICS IO ON
go
SELECT Member_No, FirstName, Region_No
FROM Member
WHERE FirstName LIKE 'K%' AND Region_No > 6 AND Member_No < 5000

SELECT Member_No, FirstName, Region_No
FROM Member
WHERE FirstName LIKE 'K%' 
	AND Region_No > 6 
	AND Member_No < 5000
go
SELECT LastName, FirstName, Region_No
FROM Member
WHERE FirstName = 'Kimberly'
OR LastName = 'Tripp'
go
SELECT LastName, FirstName, Region_No
FROM Member
WHERE FirstName LIKE 'Kim%'
go

SELECT c.* 
FROM provider p
	INNER JOIN charge c ON c.provider_no = p.provider_no
WHERE p.provider_no = 497
go

SELECT s.statement_no, c.charge_amt, p.provider_name, m.lastname
	FROM dbo.Charge AS c
		INNER JOIN dbo.provider AS p ON p.provider_no = c.provider_no
		INNER JOIN dbo.member AS m ON c.member_no = m.member_no
		INNER JOIN dbo.statement AS s ON c.statement_no = s.statement_no
		INNER JOIN dbo.region AS r ON r.region_no = m.region_no
WHERE r.region_name = 'Japan'
	AND c.charge_amt > 2500