1 / 18

Tools of the trade TSQL

Tools of the trade TSQL. CIS 407. SQL Server Tools. Books on line! Don’t use sql server authentication Use windows authentication (safer) for developer version Don’t embed passwords in code (ways to avoid this ) Connection strings in appendix X (for accessing with code Query window

xiujuan
Download Presentation

Tools of the trade TSQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tools of the tradeTSQL CIS 407

  2. SQL Server Tools Books on line! • Don’t use sql server authentication • Use windows authentication (safer) for developer version • Don’t embed passwords in code (ways to avoid this ) • Connection strings in appendix X (for accessing with code • Query window • This is where we do almost everything using Tsql statements (chapter 3) • Not just queries – create update tables, system info, etc

  3. Query Window Example pg 33 • (follow along on your own machine) • Open query window • Select * from information-schema.tables • Information_schema is a special access path for displaying meta data about your system’s database • Hit execute button • Show execution plan • Lots of stuff going on to convert SQL to code (loops, ifthenelse, etc). • Why do you care?

  4. Transaction SQL • Basic • Select • Insert • Update • Delete

  5. Basic Select • Syntax on page 42 • Use northwinds • Select * from customers • Select all the rows and columns from customer relation • Using * is expensive – better to specify only those columns you want • Select companyName from customers • All rows but just the companyname column from customers

  6. Select Statement • Select companyName from northwinds.customers • Can use database name before relation name • Select companyName, contactNamefrom customerswhere customerId = 23 • Page 48 details where clause operators • =, =>, =<, <>, !=, !>, !< • and, or, not, between, like, in, all, any, some, exists

  7. ‘Where’ clause examples • Select *from customerswhere (city=‘NY’ or city=‘detroit’) and contactTitle = ‘boss’ or contactTitle > ‘flunky’ • select * from customerswhere companyName in (‘snufu’,’acme’,’fubar’)

  8. ‘Order by’ Clause • Relations are sets of tuples or rows • Sets are not ordered • Cannot assume results of query will be returned in any order !!! • If want particular order, then MUST use ‘order by’ clause • Expensive • Select * from customerswhere city = ‘ny’order by companyName DESC, address

  9. Aggregating data • Select avg(unitPrice) as ‘averagePrice’from product as pwhere p.categoryID = 10 • note alias – ‘as’ optional • Alias has other uses – more later • Select count(customerID)from customers cwhere c.city = ‘NY’ • Aggregates: avg, min, max, count,

  10. Aggregates and ‘group by’ • Aggregates for each value of specified col. • Select orderID, min(quantity)from [order details] (!!!!!)where orderID between 11000 and 12002group by orderID

  11. Constraining Groups with ‘Having’ • Select employeeID, count(customerID)from Orderswhere shipCity = ‘ny’having count <100 • List the number of customers with orders shipped to NY for each employee but only if the count is greater than 100. • “where” constrains what goes into group • ‘having” constrains what goes in result

  12. Distinct • Relations are sets and so should not have duplicates. • But removing duplicates in the results set requires either a complex datastructure or sorting (expensive) • So SQL server does not do this unless you ask • Select distinct city from customers • Select count(distinct customerId)from [order detail]

  13. Insert statement (pg 66) • Insert [into] <table> [(column_list)] values (data_values) • Insert shippers (shipperID, companyName, Phone) values (1234,’acme’,’(208)2822685’) • Listing columns optional: Insert shippers values (1234,’acme’, ’(208)2822685’)

  14. Insert Statement • Not all columns need be listed (except keys) • Insert shippers (shipperid) values (1234) • Nulls or defaults for the rest BUT much better • Insert shippers (shipperID, companyName, Phone) values (1234,default, null) • Page 67: recommend naming every column every time, even if have to use explicit null and default – code more readable.

  15. Nulls are BAD • What does Null mean? • Not supposed to be there? • Unknown • Exec sp_help <name> page 69

  16. Combine Insert & Select (pg71) • Use NorthwindDeclare @MyTable TABLE ( OrderID int, CustomerID char(5) )Insert into @mytable select orderid, customerid from Northwind.dbo.Orders where OrderID between 10240 and 10250select * from @mytable

  17. Update Statement • Update <table name> set <column> = <value>[,<column> = <value>]*[from <source table(s)>][where <restrictive condition>] • Update shippersset companyName = ‘fubar’where shipperID = 10

  18. Delete Statement • DELETE <table_Name> where <search condition>] • Delete customer where customerID = 10

More Related