SQL202.3 Accelerated Introduction to SQL Using SQL Server Module 3

SQL/200 SQL Programming Workshop 3 – Modifying Data, Managing the Database Bookstore SQL200 Module 3 Based on SQL Clearly Explained by Jan Harrington

Note on SQL200 Slides These slides were originally designed to support the single SQL200 course which was used for any of MS Access, Oracle and SQL Server. As such you may see here slides developed in any one of the above products. We are in the process of migrating the Oracle slides and the MS Access slides out into their own slide sets. These SQL200 slides (used in SQL202 as well as SQL200) will focus on Microsoft SQL Server. Bookstore SQL200 Module 3

Warning! Below are some table name changes to be aware of in doing queries. We have created synonyms so either name should work. Bookstore2 SQL200 Module 2 New Name Old Name Orders Order_filled Order_Lines Orderlines

SQL200 Resources Bookstore database scripts found on box.net at http://tinyurl.com/SQLScripts Slides can be viewed on SlideShare… http://www.slideshare.net/OCDatabases Follow up questions? [email_address] Bookstore SQL212 Module 1

SQL200 Module 3 Part 1 – Modifying Data Part 2 – Managing Database Structures Part 3 – Creating Views and Indexes Part 4 -- Security Bookstore SQL200 Module 3

SQL/200 SQL Programming Part 1 – Modifying Data Bookstore SQL200 Module 3

Bookstore SQL200 Module 3 Relational Database with constraints (from text)

Data Modification Statements Insert Update Delete Bookstore SQL200 Module 3

Data Modification Statements End-user rarely sees these statements Application developer prepares these statements “behind the scenes” based on forms filled out by user Bookstore SQL200 Module 3

Insert Adds new rows to an existing table Two forms: Single Row Multi-Row Bookstore SQL200 Module 3

Single Row Insert Bookstore SQL200 Module 3 Basic Syntax: Insert [into] <table-name> Values (<value-list>)

Single Row Insert Bookstore SQL200 Module 3 Basic Example: insert into sources(source_numb, source_name, source_street) values (22,'Specialty Books', 'Canal Street')

Insert Statement Bookstore SQL200 Module 3

Sources table after Insert Bookstore SQL200 Module 3

Multi-row Insert Bookstore SQL200 Module 3 Basic Syntax: Insert [into] <table-name> Select <select-statement> We will do this after creating a new table later in this module

Update Updates fields in an existing row Bookstore SQL200 Module 3 Basic Syntax: Update <table-name> Set <field1> = new value, <field2> = new value,… Where <selection-criteria>

Update Increase Ingram prices by 10% Bookstore SQL200 Module 3 Example: Update books Set retail_price = retail_price *1.10 Where source_numb = 1

Ingram Book Prices before Update Bookstore SQL200 Module 3

Ingram Book Prices after Update Bookstore SQL200 Module 3

Bookstore SQL200 Module 3 After update in MS Access

Delete Deletes one or more rows Bookstore SQL200 Module 3 Basic Syntax: Delete from <table-name> Where <selection-criteria>

Delete Bookstore SQL200 Module 3 Example: Delete from sources Where source_numb = 22

Delete Bookstore SQL200 Module 3

Sources table after Delete Bookstore SQL200 Module 3

Delete and Referential Integrity Can affect referential integrity when deleting a “parent” row Can do following with child… Cascade: delete the child row Set null: set the child’s foreign key null Set default: as above but to default value No action: don’t allow delete of parent row Referential integrity can be established when creating or modifying table structures which we will look at later in the class Bookstore SQL200 Module 3

SQL/200 SQL Programming Part 2– Managing Database Structures Bookstore SQL200 Module 3

DDL Create Alter Drop Bookstore SQL200 Module 3

Schemas Logical view of a database; sort of a “sub-database” – we will not cover these in this module or… Catalogs Clusters Domains (somewhat like a user defined datatype) These topics are highly dependent upon the vendor DBMS and installation practices Bookstore SQL200 Module 3

Tables Base tables Temporary tables Local (or module scope) Global (session scope) Bookstore SQL200 Module 3

Creating Tables Use create statement Specify: Columns with data types and column constraints Table constraints Foreign key references Primary key designation Bookstore SQL200 Module 3

Data Types Int – integers or whole numbers Ex: how_many int Char – fixed length fields Ex: state char(2) Varchar/Varchar2 – variable length fields Ex: address varchar(35) Money – money field; same as MS Access currency Date/Datetime – date and time And many others – see documentation or Help Bookstore SQL200 Module 3

Create Table Bookstore SQL200 Module 3 Basic syntax: Create table <table-name> <column1> <datatype> <constraints> ,.. <column1> <datatype> <constraints> … <table constraints> Note: often preceded by a drop

Temporary Tables Bookstore SQL200 Module 3 Basic syntax (SQL standard): Create [global] temporary table <table-name> <rest of statement as for normal create> Note: SQL Server uses a different syntax. Just put a #in front of the table name as in #mytable.

Column Constraints Primary key Not NULL CHECK clause Default Unique Bookstore SQL200 Module 3

Table Constraints Primary Key Foreign Key Compare fields against each other. I.e. ship_date >= order_date Bookstore SQL200 Module 3

But first – the Drop Statement Deletes a database “object” Drop table <table-name> Drop view <view-name> Drop index <index-name> Drop domain <domain-name> Bookstore SQL200 Module 3

Create Table Bookstore SQL200 Module 3 Example 1: Create a summary table Create table summary( isbn varchar(20) primary key , How_many int check (how_many >= 0), Constraint isbn_fk Foreign key (isbn) references books(isbn) )

Create Summary Table Bookstore SQL200 Module 3

Constraints on Summary Table Bookstore SQL200 Module 3

Multi-row Insert Bookstore SQL200 Module 3 Basic Syntax: Insert [into] <table-name> [(<column list>)] Select <select-statement>

Multi-row Insert Bookstore SQL200 Module 3 Basic Example: (store # times each book ordered) Insert into summary Select isbn, count(*) From orderlines Group by isbn;

Multi-row Insert Bookstore SQL200 Module 3

Bookstore SQL200 Module 3 After multi-row insert in MS Access

SQL/200 SQL Programming Part 3 – Creating Views and Indexes, Modifying Structures Bookstore SQL200 Module 3

Views Think of a view as a named query wherein the definition is stored in the database Can be read like a table Some are updateable Bookstore SQL200 Module 3

Views Bookstore SQL200 Module 3 Basic syntax: Create view <view-name> (<column-list>) As <select statement>

Creating a View Bookstore SQL200 Module 3

Using Views Can be used like a table subject to various limitations Cannot insert into grouped queries, etc. Etc. Sample syntax: select column-list from employee_view Bookstore SQL200 Module 3

Using a View Bookstore SQL200 Module 3

Indexes Used to speed searches, joins, etc. Placed on: primary and foreign keys Secondary keys In commercial practice often managed by DBA’s for large databases Bookstore SQL200 Module 3

Indexes Bookstore SQL200 Module 3 Basic syntax: Create [unique] index <index-name> On <table-name> (field-name> [desc]) Note: can place index on a composite key; ex: state and city

Indexes Bookstore SQL200 Module 3 Basic example: create index state_inx on customers(customer_state)

Customers table with index Bookstore SQL200 Module 3

Dropping an index Basic Syntax: Drop index <table-name.index-name>; Bookstore SQL200 Module 3

Modifying a Table Design Applies to tables Use ALTER statement Add columns Delete columns Rename columns Add column constraints Add table constraints Bookstore SQL200 Module 3

Modifying a Table Design Bookstore SQL200 Module 3 Basic syntax: Alter <table-name> Add <field-name>, Add <table-constraint>, Modify <field-name> Etc.

Modify a Table Design Bookstore SQL200 Module 3 Example: add a phone number field alter table publishers add phone char(12);

Bookstore SQL200 Module 3 After alter publishers table

SQL/200 SQL Programming Part 4 – Security Bookstore SQL200 Module 3

Security Important DBA function Beyond scope of this course Typically controlled through Enterprise Manager or Studio GUI’s In commercial practice application security frequently controlled via own login and a “users” table or similar Bookstore SQL200 Module 3

Security Specifics can vary by product Access: workgroup administrator SQL Server: users, roles Oracle: users, roles Bookstore SQL200 Module 3

SQL Security Statements Grant Revoke Deny Bookstore SQL200 Module 3

Grant Bookstore SQL200 Module 3 Syntax: Grant <access-right> [with grant option] On <object> to <user> Note: by default only tables owners and admins can access a table. Others must be granted the relevant rights.

Access Rights Select Update Insert Delete References All privileges Bookstore SQL200 Module 3

Grant Bookstore SQL200 Module 3 Example: Grant update On employees to ddurso

Revoke Revokes the rights Syntax similar to grant Bookstore SQL200 Module 3 [end module]

Notes Bookstore SQL200 Module 3

SQL202.3 Accelerated Introduction to SQL Using SQL Server Module 3

More Related Content

What's hot

Viewers also liked

Similar to SQL202.3 Accelerated Introduction to SQL Using SQL Server Module 3

More from Dan D'Urso

Recently uploaded

SQL202.3 Accelerated Introduction to SQL Using SQL Server Module 3