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

SQL200 SQL Programming Workshop 2 – Joins, Subqueries, Unions, Calculations and Grouping Bookstore SQL200 Module 2 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, 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. Bookstore 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. Bookstore 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 SQL200 Module 2

SQL200 SQL Programming Part 1 – Joins Bookstore SQL200 Module 2

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

More conventions Names can be surrounded with “ “ or [ ] as in [order details]. Some of the PowerPoint slides may have this convention. Better practice is to use an underscore as in order_details. Bookstore SQL200 Module 2

Joins Inner Outer Left Right Full Cross Self Theta We will cover the most important; others as time and interest permit Bookstore SQL200 Module 2

Inner Join Pairs each row from first table with corresponding row from second table over the “join column” The result set only contains rows where there is a match over the join column in both tables Equi-join is the common inner join Bookstore SQL200 Module 2

Inner Join Bookstore SQL200 Module 2 Older Syntax: Select <column-list> From <tablelist> Where <predicate> Still very commonly used

Inner Join Bookstore SQL200 Module 2 Example using older syntax: SELECT customer_first_name, customer_street, order_numb, order_date from customers, orders Where customers.customer_numb = orders.customer_numb

Inner Join with Result Bookstore SQL200 Module 2

Inner Join (New Syntax) Bookstore SQL200 Module 2 Basic SQL 92 Syntax: Select <column-list> From <table1> Inner join <table2> On <join condition>

Inner Join Bookstore SQL200 Module 2 Basic Example: SELECT customer_first_name, customer_street, order_numb, order_date from customers inner join orders on customers.customer_numb = orders.customer_numb

Inner Join over Multiple columns Note that that the join condition can apply to multiple columns if desired Used with composite keys Bookstore SQL200 Module 2

Bookstore SQL200 Module 2 Inner Join Result in MS Access

Inner Join In the last example… What was the cardinality of the relationship between customers and orders? Which table was the parent? What was it’s primary key? In which table did we employ a foreign key and what was it? Bookstore SQL200 Module 2

Cross Join What happens when you omit a join expression? Get the cartesian product of the tables – all possible combinations of the two tables For large tables this will run a long time! Bookstore SQL200 Module 2

Bookstore SQL200 Module 2 Cross Join Result Set in MS Access

Additional SQL92 Syntax Table1 natural join table3 – automatically uses columns with same name Table 1 natural join table2 using(<column-list> Not yet widely available in commercial implementations Bookstore SQL200 Module 2

Joining More than Two Tables Can join several tables in one select Try to limit to three or four Join order can be important for performance (although optimizers will usually handle this for you) Use parentheses to force order of evaluation (also vendor extensions, often called “hints”) Bookstore SQL200 Module 2

Joining More than Two Tables Add orderlines detail to previous queries Bookstore SQL200 Module 2 SELECT customer_first_name, customer_street, orders.order_numb, orders.order_date, orderlines.isbn, orderlines.quantity FROM customers INNER JOIN orders ON customers.customer_numb=orders.customer_numb INNER JOIN orderlines on orders.order_numb = orderlines.order_numb

Multi-table Join with Results Bookstore SQL200 Module 2

On Your Own Add the book title to the previous query results Hint: add another join to books table Bookstore SQL200 Module 2

Sample Database Before we continue (Access classes only)… Create a new employees table Bookstore SQL200 Module 2

Correlation Names (Table Aliases) Can abbreviate references to tables For example: Select e .name , j .payrange From employees as e Inner join job_information as j On e .jobcode = j .jobcode; Bookstore SQL200 Module 2

Self Joins Implements a recursive relationship Important in various applications Parts lists/assemblies HR Etc. Table joined to itself using correlation names Bookstore SQL200 Module 2

Self Joins Bookstore SQL200 Module 2 SELECT e.*, m.name FROM employees AS e, employees AS m WHERE e.managerid = m.employeeid;

Outer Joins Left – selects all rows from the left or first table, even if no match exists in the other table Widely used in commercial practice Especially useful for reporting Can be slower and interfere with optimizer Right – same idea but all rows from right table Full – all rows form both tables Bookstore SQL200 Module 2

Left Outer Join Bookstore SQL200 Module 2 Basic SQL 92 Syntax: Select <column-list> From <table1> Left join <table2> On <join condition>

Left-Join Bookstore SQL200 Module 2 Basic Example: SELECT customer_first_name, customer_street, order_numb, order_date from customers as c left join orders as o on c.customer_numb = o.customer_numb

Left Join with Results Bookstore SQL200 Module 2

SQL200 SQL Programming Part 2– Subqueries, Unions Bookstore SQL200 Module 2

Subqueries One select statement embedded in another Can be nested multiple levels deep Can be used in select, from and where clauses Two types: Uncorrelated – executes inner query then outer Correlated – executes inner query once for each outer query row Bookstore SQL200 Module 2

Uncorrelated Subquery Bookstore SQL200 Module 2 select isbn, quantity from orderlines where order_numb in (select order_numb from orders where order_date between ‘1/1/99’ and ‘12/31/99’);

Uncorrelated Subquery with Results Bookstore SQL200 Module 2

Negative Subquery A type of subquery that matches “not found” conditions Bookstore SQL200 Module 2

Negative Subquery Bookstore SQL200 Module 2 select isbn, quantity from orderlines where order_numb not in (select order_numb from orders where order_date between ‘1/1/99’ and ‘12/31/99’);

Negative Subquery with Results Bookstore SQL200 Module 2

Correlated Subquery with Exists Inner subquery executed once for each outer row Exists will return true or false depending on whether the result will have any rows or not Can be a quick way to test for existence of records (parent records, say) as used in application enforcement of referential integrity Bookstore SQL200 Module 2

Correlated subquery with Exists Bookstore SQL200 Module 2 SELECT isbn, quantity FROM orderlines AS ol WHERE exists (select * from orders o where ol.order_numb = o.order_numb and o.order_date between ‘1/1/99’ and ‘12/31/99’); This type of query covered in intermediate SQL class

Unions Combines two or more tables Tables must be union compatible Bookstore SQL200 Module 2

Unions Bookstore SQL200 Module 2 Select <column-list> from <table1> Union [ALL] Select <same-columns> from <table2>

Unions Bookstore SQL200 Module 2 select * from employees union all select * from employees_copy

Bookstore SQL200 Module 2 Results of Union query

SQL200 SQL Programming Part 3 – Calculations, Aggregates Bookstore SQL200 Module 2

Calculated Fields Can add a column calculated from others Bookstore SQL200 Module 2 SELECT order_numb, quantity, cost_each, quantity*cost_each as extension FROM orderlines

Calculated field in the Result Bookstore SQL200 Module 2

String Manipulation Concatenation Trim Substring Upper, Lower Etc. (various vendor extensions) Bookstore SQL200 Module 2

Concatenation Used for concatenated keys Useful to format reports Bookstore SQL200 Module 2 Basic syntax: (Access) Field1 & Field2 (Oracle, std) Field1 || Field2 (Sql Server) Field1 + Field2

Concatenation Bookstore SQL200 Module 2 select customer_first_name + ‘ ‘ + trim(customer_last_name) as Name from customers

Date Functions Numerous date functions Often vendor specific Often used: year month DateAdd, DateDiff, DatePart getdate() Ex: where year(order_date) = 1999 Bookstore SQL200 Module 2

Aggregate Functions Count Sum Min Max Avg Often used in conjunction with grouping Bookstore SQL200 Module 2

Aggregate Functions Bookstore SQL200 Module 2 Basic syntax: Select <function>(<column>) From <table> Group by <column-list> Having <predicate> Group by all columns to left of one(s) you want to aggregate

Aggregate Functions Bookstore SQL200 Module 2 SELECT order_numb, Count(*) AS [Number of Order Lines] , Sum(quantity) AS [Total Quantity], Sum(quantity * cost_each) AS [Total Amount] FROM order_lines GROUP BY order_numb having count(*) > 1;

Having vs. Where Having and Where clauses are similar but not the same Having removes groups after they are formed Where removes rows before groups are formed Bookstore SQL200 Module 2

Exercise List all customers and their orders Name nicely formatted With orders in the year of 1999 (do not use between, etc.) Show total order quantities and amounts Only include orders with more than three order lines Bookstore SQL200 Module 2

Exercise Result Bookstore SQL200 Module 2 [end module]

Notes Bookstore SQL200 Module 2

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

More Related Content

What's hot

Viewers also liked

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

More from Dan D'Urso

Recently uploaded

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