sql - part 2 - COP 4610L: Applications in the Enterprise...

Info iconThis preview shows pages 1–10. Sign up to view the full content.

View Full Document Right Arrow Icon
COP 4610L: SQL Part 2 Page 1 Mark Llewellyn © COP 4610L: Applications in the Enterprise Fall 2006 Introduction To SQL – Part 2 COP 4610L: Applications in the Enterprise Fall 2006 Introduction To SQL – Part 2 School of Electrical Engineering and Computer Science University of Central Florida Instructor : Mark Llewellyn markl@cs.ucf.edu CSB 242, 823-2790 http://www.cs.ucf.edu/courses/cop4610/fall2006
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COP 4610L: SQL Part 2 Page 2 Mark Llewellyn © An Example Database
Background image of page 2
COP 4610L: SQL Part 2 Page 3 Mark Llewellyn © Advanced SELECT Queries One of the most important advantages of SQL is its ability to produce complex free-form queries. The logical operators that were illustrated in the last set of notes work just as well in the query environment. In addition, SQL provides useful functions that count, find minimum and maximum values, calculate averages, and so on. Even better, SQL allows the user to limit queries to only those entries having no duplicates or entries whose duplicates may be grouped. We’ll illustrate several of these features over the next few pages.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COP 4610L: SQL Part 2 Page 4 Mark Llewellyn © Ordering A Listing The ORDER BY clause is especially useful if the listing order is important to you. T •T h e s y n t a x i s : If the ordering column contains nulls, they are either listed first or last depending on the RDBMS. The ORDER BY clause must always be listed last in the SELECT command sequence. Although you have the option of specifying the ordering type, either ascending or descending – the default order is ascending. SELECT columnlist FROM tablelist [ WHERE conditionlist ] [ORDER BY columnlist [ASC | DESC] ] ;
Background image of page 4
COP 4610L: SQL Part 2 Page 5 Mark Llewellyn © Ordering A Listing (cont.) The query shown below lists the contents of the PRODUCT table listed by P_PRICE in ascending order: SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE;
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COP 4610L: SQL Part 2 Page 6 Mark Llewellyn © Ordering A Listing (cont.) The query shown below lists the contents of the PRODUCT table listed by P_PRICE in descending order: SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE DESC;
Background image of page 6
COP 4610L: SQL Part 2 Page 7 Mark Llewellyn © Cascading Order Sequences Ordered listings are used frequently. For example, suppose you want to create a phone directory of employees. It would be helpful if you could produce an ordered sequence (last name, first name, middle initial) in three stages: 1. ORDER BY last name. 2. Within last names, ORDER BY first name. 3. Within the order created in Step 2, ORDER BY middle initial. A multi-level ordered sequence is called a cascading order sequence , and is easily created by listing several attributes, separated by commas, after the ORDER BY clause. This concept is illustrated in the next couple of slides.
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COP 4610L: SQL Part 2 Page 8 Mark Llewellyn © Cascading Order Sequences (cont.) Employee Table
Background image of page 8
COP 4610L: SQL Part 2 Page 9 Mark Llewellyn © Cascading Order Sequences (cont.) To create the phonebook type ordering from the EMPLOYEE table, we
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 10
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 70

sql - part 2 - COP 4610L: Applications in the Enterprise...

This preview shows document pages 1 - 10. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online