Competencies - E-Commerce - Lecture Slides, Slides of Fundamentals of E-Commerce

Download Competencies - E-Commerce - Lecture Slides and more Slides Fundamentals of E-Commerce in PDF only on Docsity!

Databases

… Competencies

Working with SELECT Queries

Field Computation Queries

Summary Queries

Use of Groups in Summary Queries

2. Update Queries3. Delete Queries4. Append Queries5. Make Table Queries6. Cross Tab Queries

…Computed Fields

Example^ – FACULTY

table having details about faculty

members

• A query needs to show

WorkingYears

for each

faculty member

• Not available in the table– Can be calculated for each record as

WorkingYears: 2001 –[ YR_JOINED] Query Result

5. SELECT Query: Summary

Queries

• When?
  • Some calculation is needed on
    • All table records• Group of table records
      • How to make?
        • Such queries do not show all fields– Only show some calculated results for all

records\ each record group

…Summary Queries

•^

Example 2^ – Average of Quiz

scores for

student groups in

different majors

Summary Query to show the Quiz Average

for each Major

Query Result

Label used(Avg

Of

Quiz

) Docsity.com

Update Queries

•^

Allow you to quickly update all\ selectedrecords

-^

Creation Process^ – Write a SELECT Query for

proper record selection

• Change the Query to

UPDATE type

• Specify the field name(s)

to be updated

• Provide New value(s)

for the mentioned fields

DELETE Queries

• Allow you to quickly delete all\ selected

records

• Creation Process
  • Write a SELECT Query for

proper record

selection

• Change the Query to

DELTE type

…..DELETE Queries

Example^ – Employee table^ – All employees having

ID#>

will be

deleted

Delete Criteria

Query Result

…Append Queries

•^

Example:^ – Entering new record

in ‘Locations’ table.

Make Table Queries

• Used to create new tables in the

database

• Table name needs to be entered• The data can be entered either:
  • as literals (data type will automatically be

set)

OR

• As rows from another source table (data

type will exactly match with the field typesof the source table)

Cross Tab Queries

• Used to generate summary information

in a more readable way

• Generate displays close to Pivot tables.
  • Rows contain all possible values of some

filed

• Columns contain all possible values of

some other filed

• At cross-section of each some summary

information is presented.

…Cross Tab Queries

•^

Example:^ – Counting Employees on the basis of Titles &

Locations