Querying Database - San Francisco State University

Querying Database - San Francisco State University

Querying Database ISYS 363 Basic Query Language Operations Selection Projection Join Aggregates: Max, Min, Sum, Avg, Count

Totals and SubTotals GroupBy Calculated fields Selection Selection operation retrieves records that satisfy users criteria. Criteria >, >=, <, <=, =, <> Range: BETWEEN 1/1/03 AND 12/31/03 Wildcard:

? match any one character K?NG * - Match any number of characters C* Projection Projection operation defines a vertical subset of a table and retrieves only the specified fields. Example: Customer table: CID, Cname, City, Rating Create a query to show CID, Cname, Rating Create a query to show CID, Cname, City

Join The two tables must have common attributes: Key and foreign key. Combines two tables to form a new table where records of the two tables are combined if the common attributes have the same value. Join Example Faculty File:

FID F1 F2 F5 Student File: SID S1 S2 S3 Fname Chao Smith

Boxer Sname Peter Paul Smith Faculty Join Student = FID F1 F2 F1

Join Example Student File: SID S1 S2 S3 Sname Peter Paul Smith StudentCourse File:

SID CID S1 ISYS263 S1 Acct101 S3 ISYS363 S2 ISYS263 S2 Fin350 S2

Acct101 FID F1 F2 F1 Course File: CID Cname ISYS263 IS Intro ISYS363 MIS Intro Acct101 accounting

Fin350 Finance Intro Units 3 3 3 3 (Student Join StudentCourse) Join Course Aggregate Functions Max, Min, Sum, Count, Avg QueryTools/Totals

Ex. Employee: EmpID,Ename, Sex, Race, Birthdate, Hiredate, Salary How many employees in this company? What is the overall average salary? Aggregates by Group Compute the average salary by race. Compute the average salary by sex. Compute the average salary by Race and Sex. How many employees in each race group? University Database

ERD Notations SID Sname GPA Major 1 Student

M M Advise Has Enroll SID 1

Balance Account M Grade 1 Faculty FID Fname

Course Phone CID Cname Units Query Examples Q1: Display students ID, name and account balance who owe university more than $2000.

Q2: Display students ID, name and total units. Q3: Find students taking at least 9 units and display their ID, Name and total units. Q4: Display CID, Cname, SID, Sname Q5: Display CID, Cname, number of students in each course. Q6: Display facultys name and phone if the faculty advises at least three students. Top n query: Example Top 3 GPA 1. Sort GPA in descending order 2. From Query/Design view, click Property Sheet and select Top Value

Order Processing Database Query Examples Find customers live in San Francisco. Produce a customer report that shows CID, Cname, and Rating. Number of customers in each city City, NumbeOfCustomers Produce a report that shows the number of orders for each customer: CID, Cname, TotalNumberOfOrders

Total sales by product Total amount for each order MIS report Criteria applied to subtotal Calculated Fields Rename a field: NewName:OldName Define a calculated field: Tax:salary*.15 Age:Year(Now()) Year(DOB)

IIF function Sorting One field sorting Two fields sorting Other Queries Update query: Query tools/Design/Query type/Update Delete query Parameter query CrossTab query

Crosstab row Access Tools for Import/Export Import from Excel: The first row of Excels list should contain field names External Data/Import/Excel Export to Excel: External Data/Export/Excel Open Database Connectivity (ODBC)

Provide a standard to retrieve data from a database. It manages one or more "database drivers that enables the communication between database and applications. To access a database, we use ODBC facilities to define a ODBC data source name for the database.

Recently Viewed Presentations

  • Executive Briefing [Agency]

    Executive Briefing [Agency]

    Executive Briefing[Agency] [Speaker] The National Response System. Information for Presenter: This presentation was developed by the National Response Team (NRT) for use by members in conducting Executive Level briefings.
  • New Beginnings - Portland State University

    New Beginnings - Portland State University

    Transform and Conquer. Divide and Conquer. Greedy Algorithms. Dynamic Programming. Other algorithms. Complexity. New Beginnings Topics - Third Quarter (Computer Science) Operating system services. File systems. Resource management. Synchronization.
  • Introduction to Financial Management

    Introduction to Financial Management

    If you can deposit money into an account that pays an APR of 5.5% based on daily compounding, how much would you need to deposit? 1095 N (3*365) .015068493 I/Y (5.5/365) 0 PMT 15,000 FV CPT PV = -12,718.56 =PV(0.00015,1095,0,15000)...
  • 2019 NWTLiving Wage - anotheralt.files.wordpress.com

    2019 NWTLiving Wage - anotheralt.files.wordpress.com

    Child support, Child care subsidies, GST credit, UCCB, AFETC, CCTB. Tax paid. Income tax, CPP, EI, etc. Employment income vs After-tax income. Step 1: Define household(s) and confirm budget items needed to reflect local living expenses.
  • Your Genworth Team AimcoR August 2014 2012 Genworth

    Your Genworth Team AimcoR August 2014 2012 Genworth

    * Sales & Marketing Distribution Anthony Vossenberg President, Distribution Joel Mier Product & Distribution Marketing Leader Kevin O'Sullivan Internal Wholesale Sales Leader Charlie Gipple National Director Index Products John Whitcomb SVP Marketing & Distribution Svcs Mark Dinora Life Internal Team...
  • Discovering Computers 2016 Tools, Apps, Devices, and the

    Discovering Computers 2016 Tools, Apps, Devices, and the

    Network Communications Standards and Protocols. Wi-Fiidentifies any network based on the 802.11 standard that specifies how two wireless devices communicate over the air with each other. LTE . is a network standard that defines how high-speed cellular transmissions use broadcast...
  • Chapter 12-13 THE CELL CYCLE/MEIOSIS and SEXUAL LIFE CYLES

    Chapter 12-13 THE CELL CYCLE/MEIOSIS and SEXUAL LIFE CYLES

    Define: genome, gametes, chromatin, chromosome, centromere, kinetochore, checkpoint, Cdk, MPF What is the longest part of the cell cycle? Why? If the diploid number is 46, the haploid number is? G1 Checkpoint Major Checkpoints G1 checkpoint (Most important!)
  • &quot;Additional&quot; DiVincenzo Criteria

    "Additional" DiVincenzo Criteria

    Verdana Arial Wingdings Calibri MS Pゴシック Times New Roman Eclipse 1_Eclipse Josephson Junction Qubits Classical Resistance (Review) Superconductors BSC Theory Cooper Pairs Two Notes on Modern Superconductors Josephson Junction Some Uses of Junctions Flux Qubit Manipulate State SQUID Measurement Qubit...