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

SQL200 SQL Programming Based on SQL Clearly Explained by Jan Harrington Module 1 – Relational Database Background, Basic Single Table Retrieval Operations Bookstore SQL200 Module 1

Note on SQL200 Slides These slides were originally designed to support the single SQL200 course which was used for any of MS Access, MySQL, 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. The SQL200 slides will cover MySQL and SQL Server which are virtually identical for purposes of this course. Bookstore2 SQL200 Module 2

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

SQL Programming Course focus is SQL language Widely used for: Database administration Enterprise application development Data driven web sites A foundation skill for eBusiness and almost all major business applications that use relational databases Bookstore SQL200 Module 1

SQL Programming A basic knowledge of query systems, perhaps via MS Access, or some programming knowledge, is desirable We will use GUI tools or SQL Plus almost exclusively Bookstore SQL200 Module 1

Relational Database Evolution Based on Codd’s paper Early commercial efforts focused on Unix First mainframe implementation by IBM - precursor to today’s DB2 First PC implementation in early 80’s by Oracle Bookstore SQL200 Module 1

Relational Database Basics Storage Databases Tables Rows Columns Indexes Views Cursors Application interfaces Bookstore SQL200 Module 1

Bookstore SQL200 Module 1 Relational Database Table

Constraints Database Domain Uniqueness Relationship Cardinality 1 to 1 1 to N Other Business Rule Triggers Stored Procedures Bookstore SQL200 Module 1

Bookstore SQL200 Module 1 Relational Database with constraints

Database Management Systems Bookstore SQL200 Module 1 Positioning Chart VLDB Enterprise Workgroup Single user Spreadsheet # Users Cost

System Architecture Bookstore SQL200 Module 1 Access MDB File Server Architecture Access

System Architecture Bookstore SQL200 Module 1 Oracle DB Visual Basic App Client/Server Architecture Access  SQL 

System Architecture Bookstore SQL200 Module 1 Oracle DB Browser Web Architecture Web Server  SQL 

Approaching SQL Relatively simple Two main environments Interactive (This course) Embedded Static (Compiled) Dynamic Bookstore SQL200 Module 1

SQL Standardization ANSI standardization First standard in 1986 SQL 89 SQL 92 SQL 99 Various vendor extensions Microsoft/Sybase: T-SQL Oracle: PL/SQL Bookstore SQL200 Module 1

SQL Conformance Entry Intermediate Advanced Most are at least entry level Bookstore SQL200 Module 1

SQL Statements Data Manipulation Language (DML) Data Control Language (DCL) Data Definition Language (DDL) Note: SQL 99 changes these to seven types Bookstore SQL200 Module 1

SQL DDL Data definition language (DDL) Create, alter, drop, etc. Frequently implemented via various CASE tools: Visio, Embarcadero, ERWin, etc. But very useful for database administration Bookstore SQL200 Module 1

SQL DCL Data Control Language (DDL) Grant Revoke Deny Constraints Bookstore SQL200 Module 1

SQL DML Data Manipulation Language (DML) Select Insert Update Delete Bookstore SQL200 Module 1

SQL Statement Processing Bookstore SQL200 Module 1 Parse Validate Optimize Access Plan Execute

Bookstore Sample Database Before we continue (note: instructor may have already done this)… Load the sample database if you haven’t already Use Access import table feature, or Run SQL script, or Use Access upsizing wizard Bookstore SQL200 Module 1

Text Conventions In Access character strings are normally surrounded by double quotes “ Jones” In an enterprise database such as Oracle or SQL Sever enclose text strings in single quotes ‘ Jones’ Bookstore SQL200 Module 1

Date Conventions In an enterprise database such as Oracle or SQL Sever, enclose dates in single quotes ‘ 2004-12-23’ MySQL ’ 12-23-2004’ SQL Server ’ 23-DEC-04’ Oracle Bookstore SQL200 Module 1

SELECT Bookstore SQL200 Module 1 Basic Syntax ( Projection ): Select <column-list> or <*> From <table-list>

SELECT Bookstore SQL200 Module 1 Basic Example ( Projection ): select customer_last_name, customer_street from customers

MS Access SQL Query Bookstore SQL200 Module 1

SQL Server Query Bookstore SQL200 Module 1

SELECT with Where Clause Bookstore SQL200 Module 1 Example ( Restriction plus Projection ): Select <column-list> From <table-list> Where <selection-criteria>;

Comparison Operators < less than > greater than <= less than or equal to >= greater than or equal to <> or != two forms for not equal Bookstore SQL200 Module 1

SELECT with Where Bookstore SQL200 Module 1 Basic Example ( Restriction plus Projection ): select customer_last_name, customer_street from customers where customer_last_name = ‘Jones’

Select with Where Bookstore SQL200 Module 1

On Your Own Find books written by Mark Twain Show title, publisher, year Bookstore SQL200 Module 1

Complex Predicates Bookstore SQL200 Module 1 Follow normal boolean logic Select customer_last_name, customer_street From customers Where (customer_last_name = ‘Jones’ or customer_last_name = ‘Smith’)and customer_state=‘NY’

Select with Complex Where Bookstore SQL200 Module 1

Complex Where Result Bookstore SQL200 Module 1

Special Operators Can be used in where clause LIKE IN BETWEEN IS NULL Bookstore SQL200 Module 1

Like (“Wild Card Matches”) ANSI Where customer_last_name like ‘Jo%’ Like ‘Jo_’ Access Where customer_last_name like “Jo*” Like “Jo?” Bookstore SQL200 Module 1

IN Bookstore SQL200 Module 1 Select * From customers Where customer_last_name in (‘Rizzo’, ‘Jones’, ‘Garcia’) The list in parentheses can be replaced by a subquery. We will study this later.

SQL Where Clause with IN Bookstore SQL200 Module 1

IS NULL Bookstore SQL200 Module 1 Select * From customers Where customer_street IS NULL SQL uses three valued logic. Must use IS NULL to test for unknowns. A null is NOT the same as blank or empty.

On Your Own Find all customers with an address not equal to 4592 Maple Lane Was Peter Johnson selected? Why or why not? Bookstore SQL200 Module 1

BETWEEN Bookstore SQL200 Module 1 Select * From orders Where order_date BETWEEN ‘1-Jan-99’ and ’31-Dec-99’ Note: date formats vary from product to product.

Where with Between Bookstore SQL200 Module 1

Removing Duplicates Bookstore SQL200 Module 1 Select DISTINCT customer_city From customers List once each city in which there are customers Removes duplicate rows from result set

Removing Duplicates Bookstore SQL200 Module 1

Sorting – ORDER BY Bookstore SQL200 Module 1 DESC will sort in descending order Basic syntax : Select <column list> From <table list> Where <selection criteria> Order by <column list> [DESC]

Sorting – ORDER BY Bookstore SQL200 Module 1 Select * From customers Order by customer_state, customer_city Example: List all records sorted by state, city

Sorting Results with Order By Bookstore SQL200 Module 1

Selecting Top Records Bookstore SQL200 Module 1 Select Top 5 (or top 25 percent) Customer_last_name , contact_zip From customers Order by customer_zip desc ; List largest 5 zips or top 25 % of them…

SQL Exercises List all books whose publisher name begins with “H” or “T”; sort by title [hint: use LIKE] List all customers whose last name ends with “S”; sort by state, city, last name Find the order numbers of orders with order dates in 1999; sort by order #. [Hint: use BETWEEN] Find the order numbers and order dates of all orders with a “2” in column 2 of the credit card #; sort by order date descending Bookstore SQL200 Module 1 [end module]

Notes Bookstore SQL200 Module 1

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

More Related Content

What's hot

Viewers also liked

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

More from Dan D'Urso

Recently uploaded

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