1 / 27

MGT 329/637 – Spring 2010 Structured Query Language (SQL) Simple SELECT Statements

MGT 329/637 – Spring 2010 Structured Query Language (SQL) Simple SELECT Statements. Last Revised: 2/1/2010 5:37 PM. Setup for Exercises and Examples. Exercises and examples are all based on the Albuquerque Pharmaceutical Service database: Database build procedure – click here

karly-mendez
Download Presentation

MGT 329/637 – Spring 2010 Structured Query Language (SQL) Simple SELECT Statements

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. MGT 329/637 – Spring 2010 Structured Query Language (SQL) Simple SELECT Statements Last Revised: 2/1/2010 5:37 PM

  2. Setup for Exercises and Examples • Exercises and examples are all based on the Albuquerque Pharmaceutical Service database: • Database build procedure – click here • To prepare for the examples, complete the following steps: • Start Microsoft SQL Server Management Studio • Connect to your database • If you haven’t already done so: • Open a new query window • Copy in the script from the link above (Aps_MakeDatabase) • Execute the procedure • Open another query editing window – use that window to test examples and to work on exercises

  3. Tables – Quick Overview • Data within a relational database is organized into tables, also called relations • Each table organizes information about one “thing of interest” – for example, an entity such as a student, employee, or product • Tables can also organize information about relationships among things – for example, a relationship such as student enrollment in a course or one course being a prerequisite of another • Each row, or tuple, contains data describing one specific instance of an entity or relationship – for example, each row in the student data contains data about one student • Each column contains values of the same data item for all instances – for example, ID number, name, and address • We’ll return to entities and relationships in a few weeks when we discuss conceptual database models

  4. Quick Table Overview - Continued • Each table name must be unique within a database • Each column has: • A unique name within its table • A data type – for example, int, string, money • Restrictions such as: • Allow NULLs • Primary key (look for key icon to the left of the column name in design view)

  5. Overview of SELECT Queries • SELECT queries extract data from one or more tables • Query output is, in turn, input for: • Reports (with or without formatting) • Application programs (e.g., listing customer order history in a Web-based application) • Tools such as spreadsheets and data mining programs • Queries often span multiple tables and must “connect” related rows from those tables • The output of a query is a table: • The output table is usually temporary • Sometimes output table can be used as input to a statement that modifies database content (e.g., a bulk INSERT) • Queries can be nested inside other queries • Queries can be stored as a procedure or view

  6. SELECT Statement • Simple format: • SELECT <column(s)> FROM <table(s)> WHERE <condition> • The WHERE clause is optional • Examples: • SELECT GenericName FROM Drug • SELECT GenericName, BrandName FROM Drug • SELECT GenericName, BrandName FROM Drug WHERE Manufacturer='Pfizer’

  7. SELECT Statement - Columns • After the keyword SELECT is a comma-separated list of column names, for example: • SELECT BrandName FROM Drug • SELECT BrandName, GenericName FROM Drug • * is an synonym for “all columns”, for example: • SELECT * FROM Drug • Column names can be renamed with aliases in the query output, for example: • SELECT BrandName AS [B-Name] FROM Drug

  8. SELECT Statement FROM Clause • The FROM clause identifies the table(s) from which column values will be read and output • In rare cases, a query may be written that doesn’t contain a FROM clause, for example: • SELECT GETDATE() • SELECT DATEPART(year,GETDATE()) • SELECT Column1=12345 • SELECT 'XYZ’ • SELECT 54321 – 12345

  9. Exercise • Create SELECT statements for the following queries: • List all Units (all columns) • List the name of all patients • List drug generic name and brand name with the column headings G-Name and B-Name • List the current week of the current year with the column heading WeekNum

  10. WHERE Clause The WHERE clause is a filter on SELECT output It filters (removes) output rows that don’t satisfy a stated condition (for which the condition is not true)

  11. Boolean Conditions and SQL • Boolean conditions are based on branch of logic called Boolean logic, first described by the mathematician George Boole • All Boolean logic statements or conditions are said to “evaluate” or “resolve” to one of two possible results: • True • False • SQL, like most programming languages, incorporates Boolean conditions for various purposes including: • Determining what rows are output by a SELECT statement • Determining what rows are affected by an UPDATE or DELETE statement • Restricting added or modified values of a table column to those that satisfy a CHECK constraint

  12. Conditions – Operators, Columns, and Values • A simple example of a Boolean condition is: Salary > 50000 • A simple Boolean condition has three parts: • Left-side column name or value (e.g., Salary) • Relational operator (e.g., >) • Right-side column name or value (e.g., 50000) • For any row of the table, the above condition evaluates/resolves/is: • True for all values greater than 50000 • False for all values less than or equal to 50000 (including negative values)

  13. Relational Comparison Operators • A relational operator modifies or compares column contents and/or values and returns True or False • SQL relational operators used for comparison are: = (is equal to) < (is less than) > (is greater than) <= (is less than or equal to) >= (is greater than or equal to) IN (used to test equality with one of a set of possible values) ISNULL (used to test whether a column has no assigned value)

  14. Simple Condition Examples • Simple conditions examples include: • Salary > 50000 • Price <= 12 • Name = ‘Steve’ • Name IN (‘Steve’,’Bob’) • The left- and right-side columns/values must both be of the same data type • Some examples of incorrect conditions due to data type mismatches include: • Salary > ‘50000’ (assuming Salary is of type real, a numeric value with a decimal point) • QuantityOnHand < 20.5 (assuming QuantityOnHand is an integer) • Name = 12345 (assuming that Name is a string or other character data type)

  15. Exercise • Create SELECT statements for the following queries: • List all Units within the institution Shady Stay • List the name of all patients with numbers less than 5000000000 • List all drugs with more than 5 units on hand

  16. Complex/Compound Conditions • Some SQL relational operators combine simple conditions to form complex conditions: NOT (negate/reverse the result of a condition – True becomes False and vice-versa) AND (combine two conditions and resolve to True only if bothconditions are True OR (combine two conditions and resolve to True if eithercondition is True • For example, Salary = 5000 OR Salary > 5000 (same as Salary >= 5000) Price >= 10 AND Price <=100 (True for values between 10 and 100, inclusive) NOT Name = ‘Steve’ (True for any value except ‘Steve’)

  17. USE of Parentheses • Complex conditions can be: • Difficult to read • Ambiguous • For example, • NOT Price = 10 OR Price = 20 • Does NOT apply to everything that follows it or just to Price = 10 ??? • The ambiguity can be resolved by using parentheses, for example: • NOT ((Price = 10) OR (Price = 20)) • The condition is True for all Price values except 10 and 20 • Use parentheses whenever possible in conditions to improve readability and avoid ambiguity !!

  18. BETWEEN • SQL provides a shorthand for writing conditions to check for a value within a range using the relational operator BETWEEN: • BETWEEN is followed by a numeric constant or expression, the keyword AND, and another numeric constant or expression – for example, SELECT * FROM Drug WHERE QuantityOnHand BETWEEN 5 AND 10 • Which is equivalent to: SELECT * FROM Drug WHERE ((QuantityOnHand >= 5) AND (QuantityOnHand <= 10))

  19. Additional Complex Condition Examples • (Sport='Baseball' OR Sport='Football' OR Sport='Basketball’) • True if Sport contains one of the three listed values • False if Sport contains any other value or is NULL • ((Sport='Baseball‘) OR (Sport='Football‘) OR (Sport='Basketball‘) OR (Sport IS Null)) • True if Sport contains one of the three listed values or NULL • Note the use of SQL keyword IS – only way to test for NULL !! • ((QuantityOrdered IS NULL) OR (QuantityOrdered >=0)) • True if QuantityOrdered is NULL or contains a non-negative value (0, 1, 2, …) • ((AmQuantity > (-1)) AND (AmQuantity < 101)) • True if AmQuantity contains a value between zero and 100, inclusive • ((AccountBalance >= 1000) AND (NOT (AccountType IN (‘Basic’,’Economy’))) • True if both of the embedded conditions are True: • AccountType does not contain one of the two listed values, and • AccountBalance is greater than or equal to 1000

  20. Resolving Complex Conditions • When evaluating/resolving a complex condition: • Evaluate one embedded condition at a time • Work from the innermost to the outermost embedded conditions • For example, assume AccountBalance contains 300 and AccountType contains ‘Preferred’: • ((AccountBalance >= 1000) AND (NOT(AccountType IN (‘Basic’,’Economy’))) • ((AccountBalance >= 1000) AND (NOT(False)) • ((AccountBalance >= 1000) AND (True)) • ((False) AND (True)) • (False)

  21. Ordering Output Rows • ORDER BY is an optional clause of the SELECT statement which enables rows to be sorted: • SELECT * FROM Drug WHERE Manufacturer IN ('Pfizer', 'Schwartz','Merck') ORDER BY Manufacturer • SELECT * FROM Drug WHERE Manufacturer IN ('Pfizer', 'Schwartz','Merck') ORDER BY Manufacturer DESC • SELECT * FROM Drug WHERE Manufacturer IN ('Pfizer', 'Schwartz','Merck') ORDER BY Manufacturer, GenericName • SELECT * FROM Drug WHERE Manufacturer IN ('Pfizer', 'Schwartz','Merck') ORDER BY Manufacturer, GenericName DESC • SELECT Manufacturer, GenericName, UPC FROM Drug WHERE Manufacturer IN ('Pfizer', 'Schwartz','Merck') ORDER BY Manufacturer, GenericName DESC

  22. Building Complex Queries • Building a complex query can be frustrating and overwhelming • Follow these guidelines to keep your sanity • Examine the contents of the source table(s) and estimate the number of output rows that the query should produce • Start with an unrestricted query – no WHERE clause • Work in small steps – save working versions after each step successfully executes – keep earlier versions in separate files (or commented out in the same file) so you can return if needed • Save “formatting issues” such as ordering, column headings, and column width restrictions for last • First make it work, then make it pretty !!!

  23. Exercise • Create SELECT statements for the following queries: • List the patient name and room number only for patients in unit MESACN1 ordered by name • List the brand name, generic name, and quantity on hand of all drugs with more than 5 units in inventory ordered by descending quantity • List the ID number and name of all patients whose ID number starts with the digit 8 ordered by ascending ID number • List patient number (column heading PatientNum), name (column heading PatientName), and UnitAbbreviation (column heading Unit) for patients in all units except MESACN1 and MESACN2 whose first names begin with the letters A through J, inclusive. Order the output rows by ascending unit, then by descending name

  24. SQL Formatting • SQL ignores extra white space including blank characters, tabs, and empty lines • Use white space to improve readability for: • Yourself – “pretty” code is easier to read and debug! • The person who gets your job after you’re – choose one: • Promoted (and learn the Peter Principle is true) • Fired (for writing code that is neither readable nor correct) • Hit by a bus (during Ride Your Bike to Work Week)

  25. SQL Comments • SQL accepts comments in two forms • /* followed by */ • They can be on separate lines with any number of intervening lines • Don’t embed them!! • -- anything after -- to the end of the line is a comment • Comments appear in green in the query editor windows of SQL Server Management Studio and Visual Studio • Use comments for the reasons listed on the previous slide

  26. Stored Procedures • A stored procedure is one or more SQL commands stored within the database • To create a stored procedure: • Write a query in a query window • Add the following lines at the top: • CREATE PROCEDUREProcedureName, where ProcedureName is a name that doesn’t already exist in the database • AS • Add an extra line at the end with only the keyword: • RETURN

  27. Stored Procedure - Continued • To see/manipulate stored procedures with SQL Server Management Studio open the Programmability folder and then the Store Procedures folder • Right-click on a stored procedure to modify, execute, rename, or delete it • Once a procedure is stored: • The keyword CREATE is automatically changed to ALTER • A few extra SQL lines are added at the top

More Related