# 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

• Residential customers were engaged to understand the level of support for a Time of Use tariff. Western Power take its responsibility to deliver a safe and reliable electricity service to 1.1 million homes and businesses in Western Australia seriously.
• Attendance. Student Incentives. Grade Level Competition. One big celebration each semester. Monthly socials. RC Spirit Wear. Adult Practices: Student Academic Instructional Groups (SAIG)
• Chapter 11 The Formation of Stars Giant Molecular Clouds Large Low density Cold To Form Stars Small High density Hot 4 Factors that Resist Contraction Thermal Energy Interstellar Magnetic Field Rotation Turbulence Shockwaves Caused by: Supernova Explosions Ignition of very...
• Bubble sort. Insertion sort. First O(n log n) algorithm: Shell sort: Theoretical. Based on Insertion sort. Then came "practical" O(n log n) algorithm. Heap sort. Merge sort. Quick sort. These are "comparison based" sorts. For given additional information one can...
• Virtual RC Racing Nipunn Koorapati Erik Schmidt Jacob Olson Robert Liu Concept Think Mario Kart with real RC cars Virtual race environment projected onto the ground Speed of car varies with terrain (like Mario Kart) Perhaps interactive items (Thunderbolts?!)
• The PartnershipHubs: Cambridge, Peterborough, Kings Lynn and WisbechPartners: Axiom Housing, Broadland Housing, Cambridge University, Centre 33, CHS Group, Cross Keys Homes, Norfolk CAB, Papworth Trust, Rural Cambs CAB. Partnership and offer developed over a period of almost two years, building...
• Future scripts will have an ability to correlate events between system measurements/ routing log files Challenges in Testing Third-Party Mechanisms ManHunt license is IP/MAC specific Control of machine selection in DETER Administration software: some products for Windows XP only, e.g.,...
• Curriculum & Instruction Curriculum can be thought of as "doing the right thing." Instruction can be thought of as "doing the thing right." Curriculum Development Curriculum development is a planning process a way of assessing needs, identifying goals and objectives,...