1 / 18

Midterm Exam: Tuesday, September 28

Learn about the design and use of queries in SQL, including string and arithmetic expressions, and the WHERE clause for selecting rows. Review sessions available. Homework #6 due Oct 12.

williamdharris
Download Presentation

Midterm Exam: Tuesday, September 28

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. Midterm Exam: Tuesday, September 28 • Covers all course material through today’s lecture: • Homeworks 1 - 5; Problem 1 of Homework #6 • All lectures • All handouts • Readings from Stair and Reynolds and Rob and Coronel • Will be pencil-and-paper • No computers or calculators are necessary • Will include: • Relational database design (about 60%) • Short answer/True-False (about 30%/10%) • Review sessions will be held: • Wednesday 9/29, 5:30 PM - 7 PM, Room 255 Baker Hall • Saturday 10/2, 1 PM - 4 PM, Room 1003 HbH • Homework #6 (Queries) due Tuesday, October 12 90-728 MIS Lecture Notes

  2. Query Logic and Design Queries are sequences of commands used to extract and combine data. Using the department store example, what are typical questions that could be answered via queries? While tables store data, and relationships encode business rules that link data in multiple tables, only queries assist in the transformation of data into information 90-728 MIS Lecture Notes

  3. Design and Use of Queries • In Access, queries are designed and executed using three views: • Design view • Datasheet view • SQL view • Queries are used: • to perform ad-hoc data analysis • to populate recordsets used in forms • to populate controls such as combo boxes and text boxes • to organize data for reports • as part of macros and code for custom processing tasks 90-728 MIS Lecture Notes

  4. Structured Query Language Queries are executed using Structured Query Language (SQL). SQL has a number of advantages compared to sequential programs implemented in third-generation languages such as C or Pascal: • SQL is simple: there are only thirty or so commands • SQL is nonprocedural: data storage details are shielded from the user; • SQL is platform-independent: queries can be represented identically on different platforms; • SQL is GUI-compatible: queries can be written in Access and other languages with a GUI alone. You may eventually find that some queries are more easily expressed using SQL than via an interface 90-728 MIS Lecture Notes

  5. SQL SELECT SQL View  Many queries are based on the command which extracts a set of records from a table for further analysis: SELECT  Datasheet View  Design View 90-728 MIS Lecture Notes

  6. SQL SELECT (cont’d) The general SELECT command uses many clauses : SELECT <set of columns, string or arithmetic combination of column(s) or rows, joins on columns in different tables> [INTO <new table>] FROM <table name(s)> [WHERE <logical conditions hold>] [GROUP BY <column(s)>] The SELECT command can extract a set of columns from one table, or a set of columns from different tables for which pairs of tables have Foreign Key relationships. The INTO clause specifies a table which will store results of this query. The FROM clause lists the table(s) used in the query The WHERE clause lists logical conditions that must be satisfied. The GROUP BY clause lists the attributes by which rows will be aggregated. 90-728 MIS Lecture Notes

  7. SQL SELECT- Using String Expressions New columns in queries may be assigned values using a variety of string expressions: • [ColName1] & [ColName2] (concatenation): appends one string field to another. • Ex: [SName] & “ - “ & [Sex] applied to “Richards” & “ - “ “M” yields “Richards - M” • Left([ColName], N): extracts first N characters of column ColName. Similarly: Right([ColName], N) • Ex: Left([SName], 1) applied to “Richards” yields “R” • Mid([ColName], N, M): extracts M characters of column ColName starting at character #N • Ex: Mid([CrsNbr],4,1) applied to “ACC610” yields “6” • Len([ColName]): returns the length of column ColName • Ex: Len([SID]) applied to “218” yields 3. 90-728 MIS Lecture Notes

  8. SQL SELECT- Using Arithmetic Expressions New columns in queries may also be assigned values using a variety of arithmetic expressions: • [ColName1] + [ColName2] (addition): add the contents of two columns. Similarly for “-” (subtraction) • [ColName1] * [ColName2] (multiplication): subtracts the contents of one column from another. Similarly for “/” (division) • [ColName]^N (exponentiation): computes the value of a column to the power N (N can be any real number) • Abs([ColName]) (absolute value): computes the absolute value of a column • ex.: Abs([Temperature]) applied to “-32” gives “32” • Int([ColName]): returns the integer portion of a real number • ex.: Int([InterestRate]) applied to “8.325” gives “8” 90-728 MIS Lecture Notes

  9. SQL WHERE: Criteria for Selecting Rows Logical expressions can determine rows to appear in query output: • WHERE LogicalExpression([ColName]) • Ex: WHERE Left([Sname],1) < “S” chooses all rows where the first initial of the last name is lower than “S” • Ex: WHERE Major Is Null selects all rows where the value of the field “Major” is Null (empty) • Other operators: BETWEEN, LIKE, IN, EXISTS • WHERE LogicalExpression1([ColName1]) OR LogicalExpression2([ColName2]) (Logical OR) • Ex: (Left([SName],1)<"P") OR ([GPA])>2.5) chooses all rows where the first initial of the last name is lower than “P” OR the GPA exceeds 2.5 • WHERE LogicalExpression1([ColName1]) AND LogicalExpression2([ColName2]) (Logical AND) • Ex: (Left([SName],1)<"P") AND ([GPA])>2.5) chooses all rows where the first initial of the last name is lower than “P” AND the GPA exceeds 2.5 90-728 MIS Lecture Notes

  10. SQL GROUP BY: Aggregate Records The GROUP BY clause divides records into groups based on the value of a criterion. New fields may be created using summarized values of fields in each category. Example: • SELECT Major, Avg(GPA) AS AvgOfGPA FROM STUDENT GROUP BY STUDENT.Major Other summary calculations: Sum, Min, Max, Count, StDev, Var, First, Last 90-728 MIS Lecture Notes

  11. Access Variations on SELECT Queries • UPDATE: Calculates a new value for an existing column using arithmetic/string expressions for rows satisfying certain criteria. For example: • UPDATE FACULTY SET FACULTY.FName = "Kennedy-Jenkins" WHERE (([FName]="Kennedy")) 90-728 MIS Lecture Notes

  12. Access Variations on SELECT Queries (cont’d) • APPEND: Adds records from the current query to another table with identical format. For example: • INSERT INTO FACULTY4 SELECT FACULTY3.* FROM FACULTY3; 90-728 MIS Lecture Notes

  13. Access Variations on SELECT Queries (cont’d) • MAKE TABLE: Creates a new table based on results from the current query. For example: • SELECT FACULTY3.FID, FACULTY3.FName INTO FACULTY_NEW FROM FACULTY3 GROUP BY FACULTY3.FID, FACULTY3.FName ORDER BY FACULTY3.FID; 90-728 MIS Lecture Notes

  14. Access Variations on SELECT Queries (cont’d) • DELETE: deletes all records satisfying certain criteria. Example: • DELETE STUDENT2.*, STUDENT2.GPA FROM STUDENT2 WHERE (((STUDENT2.GPA)<=2.5)); 90-728 MIS Lecture Notes

  15. Access Variations on SELECT Queries (cont’d) • CROSSTAB: creates a two-dimensional table in which a value field is summarized according to row and column field(s). Example: • TRANSFORM Avg(STUDENT.GPA) AS AvgOfGPA SELECT STUDENT.Major FROM STUDENT GROUP BY STUDENT.Major PIVOT STUDENT.Sex; 90-728 MIS Lecture Notes

  16. Parameter Queries • Parameter Queries: Select records that match a user-defined criterion based on a particular field. Example: • SELECT COURSE.* FROM COURSE WHERE (((COURSE.CrsNbr)=[Select a course number:])); 90-728 MIS Lecture Notes

  17. Other Query Applications Views: Select a row driver, and add one or more tables, each pair of which is linked by a Foreign key relationship. Select columns of interest for output. Find Duplicate Records: Find the records in a table that have identical values for all columns Find Unmatched Records: Find the records in one table without records in another table according to a common field Archive: Select records according to given criteria (SELECT query); create a new table based on query results (MAKE TABLE query), delete selected records from original table (DELETE query) Select Top X Records: Select the top X (X%) records according to given criteria 90-728 MIS Lecture Notes

  18. Data Analysis Applications of Queries • Suppose you have a number of fields that could serve as indexes or foreign keys, but with inconsistent information: • “Degree attained” = “Bachelor’s”, “B.A.”, “A.B”, “MPM”, “M.S.” How could you derive a consistent set of values? • Now suppose you have a dataset describing customers with a number of candidate keys: • Last name, Phone number, Street address How could you determine a primary key (if one exists)? • Finally, you want to determine the distribution of family income by marital status and gender: • “Marital Status” = “Married”, “Separated”, “Divorced”, … How would you calculate this distribution? 90-728 MIS Lecture Notes

More Related