1 / 17

SQL DML

SQL DML. Quick recap of the SQL & the GUI way in Management Studio The Adwentureworks database from the book A script to create tables & insert data for the Amazon example INSERT UPDATE DELETE SELECT (simple) SELECT ... ORDER BY SELECT ... GROUP BY SELECT - aggregates

nassor
Download Presentation

SQL DML

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. SQL DML • Quick recap of the SQL & the GUI way in Management Studio • The Adwentureworks database from the book • A script to create tables & insert data for the Amazon example • INSERT • UPDATE • DELETE • SELECT (simple) • SELECT ... ORDER BY • SELECT ... GROUP BY • SELECT - aggregates • DISTINCT & ALL predicates • Steen Jensen, autumn 2013

  2. Quick recap the SQL way & the visual way in SQL Server Management Studio New database ’Demo’ created and expanded You can either type in SQL commands in the Query window Or you can do it visually by right-clicking A small live demo!

  3. The Adwentureworks database from the book • The Adwentureworks database can be downloaded, so all examples from the book can be tried out • Unfortunately there seems to be a problem with Windows 8 – it works fine on my old Windows 7 machine, but doesn’t work on Windows 8 • The database can be unzipped from Adwentureworks database • Some good advice: • Unpack the zip file into the C drive (avoid long/deep paths) • After you have unpacked the database, you can attach it in SQL Server Studio by following the screenshots shown in the next slide

  4. Attaching the Adwentureworks database

  5. Attaching the Adwentureworks database – try it Try to install the Adwentureworks database on your machine Use max. 15 minutes – if it doesn’t work, then skip it!

  6. A script to create and insert content into Amazon tables To help you get up an running quickly with your Amazon example, you can execute a script, which will create the necessary tables for you and put content into them (if you prefer to make it yourself, this is perfectly fine!) Before you can execute the script, you must make a database and call it Amazon Open a new query window and copy paste the content from the file booksCreateInsert.txt into the query window Press on the execute button, and the tables will now be created with content If you expand the Amazon database, you should be able to see the new tables under Tables

  7. SQL DML - INSERT • The following slides will be based upon the examples, which can be unzipped from the file 102282 Beginning SQL - Final.zip – all examples will be from the file Chap03.sql • Is used to insert new tuples/rows into a table • In two flavours: • Without column names • With column names • You can also insert more than one tuple/row at a time (multirowinsert)

  8. SQL DML - UPDATE Is used to change/update one or more attributes/columns in a table Also possible with expressions

  9. SQL DML - DELETE Is used to delete one or more tuples/rows in a table NB!!! SQL Server may refuse to delete specific rows due to referential integrity violation

  10. SQL DML - SELECT Is used to select/show one or more attribute(s) / column(s) from a table This is called a simple select – also possible with two or more tables  join (covered next time) SELECT * FROM ...: the asterisk (*) means select allattributes/columns A select will automatically select all tuples/rows, unless you add a WHEREclause

  11. SELECT – WHERE clause operators 1

  12. SELECT – WHERE clause operators 2

  13. SELECT – ORDER BY clause • The ORDER BY clause is used to order the shown tuples/rows in a specific order • You can add the keyword ASC or DESC after ORDER BY to specify the order : • ASC: ascending order – default • DESC: descending order

  14. SELECT – GROUP BY clause Alias The GROUP BY clause is used to aggregate info

  15. SELECT – aggregates • Aggregates are functions, which work on groups of data • Apart from GROUP BY other functions exist: • AVG – computing averages • MIN and MAxselects the smallest/biggest value within a group • COUNT counts the number of occurrences within a group • HAVING can be used to specify limitations/rules for a group

  16. DISTINCT & ALL predicates The DISTINCT predicate can be used to avoid showing duplicate tuples/rows The ALL predicate works just the oppsite – not very common!

  17. Exercise in SQL DML • Experiment trying out the different SQL DML commands with your Amazon example (and/or The AdwentureWorks database, if it works!) • Try the following commands: • INSERT • UPDATE • DELETE (you can e.g. delete one of the new rows, you insert) • SELECT • Select with all attributes/columns • Select with chosen attributes/columns • Select with WHERE clause • Select with ORDER BY • Select with aggregates (GROUP BY … see slide 15) • Select with DISTINCT

More Related