mis 385 mba 664 systems implementation with dbms database management
Download
Skip this Video
Download Presentation
MIS 385/MBA 664 Systems Implementation with DBMS/ Database Management

Loading in 2 Seconds...

play fullscreen
1 / 68

MIS 385 - PowerPoint PPT Presentation


  • 122 Views
  • Uploaded on

MIS 385/MBA 664 Systems Implementation with DBMS/ Database Management. Dave Salisbury [email protected] (email) http://www.davesalisbury.com/ (web site). Benefits of a Standardized Relational Language. Reduced training costs Productivity Application portability Application longevity

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'MIS 385' - matsu


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
mis 385 mba 664 systems implementation with dbms database management

MIS 385/MBA 664Systems Implementation with DBMS/Database Management

Dave Salisbury

[email protected] (email)

http://www.davesalisbury.com/ (web site)

benefits of a standardized relational language
Benefits of a Standardized Relational Language
  • Reduced training costs
  • Productivity
  • Application portability
  • Application longevity
  • Reduced dependence on a single vendor
  • Cross-system communication
disadvantages of a standardized relational language
Disadvantages of a standardized relational language
  • stifle creativity and innovation
  • never enough to meet all needs
  • offspring of compromises
  • difficult to change
  • using special features may result in loss of portability
terminology
Terminology
  • Data Definition Language (DDL):
    • Commands that define a database, including creating, altering, and dropping tables and establishing constraints.
  • Data Manipulation Language (DML)
    • Commands that maintain and query a database.
  • Data Control Language (DCL)
    • Commands that control a database, including administering privileges and committing data.
common sql commands
Common SQL Commands
  • Data Definition Language (DDL):

 Create  Drop

 Alter  Rename

  • Data Manipulation Language (DML):

 Select  Update

 Insert  Delete

  • Data Control Language (DCL):

 Grant  Revoke

querying a database with sql

SQL statementis entered

SQL> SELECT loc

2 FROM dept;

Database

Data is displayed

LOC

-------------

NEW YORK

DALLAS

CHICAGO

BOSTON

Querying a Database with SQL

Statement is sent to database

writing sql statements
Writing SQL Statements
  • SQL statements are not case sensitive(but criteria within quotation marks are)
  • SQL statements can be on one or more lines
  • Clauses are usually placed on separate lines
  • Keywords cannot be split across lines
  • Tabs and spaces are allowed to enhance readability
  • Each SQL statement (not line) ends with a semicolon (;)
data definition language ddl to create tables
Data Definition Language (DDL) to Create Tables
  • Identify appropriate datatypes
  • Identify columns that should accept null values
  • Identify columns that need to be unique
  • Identify all PK/FK mates
  • Determine any default values to be inserted
  • Identify columns which need a domain specification
  • Create the table
ddl example in sql
DDL example in SQL

CREATE TABLE ORDER

(Order_Id char not null,

Order_Date date default sysdate,

Customer_Id char not null,

Constraint Order_pk primary key (order_Id) ,

Constraint Order_fk foreign key (Customer_Id) references Customer(Customer_Id));

Cust_ID

Order_ID

Customer

Order

Order_Date

data manipulation language dml
Data Manipulation Language (DML)
  • This is the major focus of our coverage of SQL
  • Most useful for querying database based on specific criteria
  • Includes: insert, delete, update, and select
dml inserting
DML - Inserting

Useful for populating a database

Syntax is:

INSERT INTO Product(Product_ID, Product _Name, Unit_Price, On_Hand)

VALUES (1, ‘End Table’, 175, 8);

Or

INSERT INTO Ca_Customer

SELECT * FROM Customer

WHERE State=‘CA’;

dml deleting
DML - Deleting
  • DELETE FROM Customer;
  • Or
  • DELETE FROM Customer

WHERE State=‘HI’;

dml updating
DML - Updating

UPDATE Product

SET Unit_Price = 775

WHERE Product_ID = 7;

dml select
DML - SELECT

Statement Syntax:

SELECT [DISTINCT] column_list

FROM table_list

[WHERE conditional expression]

[GROUP BY column_list]

[HAVING conditional expression]

[ORDER BY column_list] ;

the basic select statement
The Basic SELECT Statement

SELECT column1, column2, column3,...

FROM table;

  • SELECT identifies what columns
  • FROM identifies which table
example select statement

SQL> SELECT deptno, loc

2 FROM dept;

DEPTNO LOC

--------- -------------

10 NEW YORK

20 DALLAS

30 CHICAGO

40 BOSTON

Example SELECT Statement
selecting all columns

SQL> SELECT *

2 FROM dept;

DEPTNO DNAME LOC

--------- -------------- ––––––––––––-

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Selecting All Columns
specifying output headings

SQL> SELECT loc AS location

2 FROM dept;

LOCATION

-------------

NEW YORK

DALLAS

CHICAGO

BOSTON

Note Upper-Case Heading

Specifying Output Headings
duplicate output

SQL> SELECT job

2 FROM emp;

JOB

---------

PRESIDENT

MANAGER

MANAGER

MANAGER

SALESMAN

SALESMAN

...

A total of 14 records display

Duplicate Output
suppressing duplicate output

SQL> SELECT DISTINCT job

2 FROM emp;

JOB

---------

ANALYST

CLERK

MANAGER

PRESIDENT

SALESMAN

Suppressing Duplicate Output

Each unique job is listed only once

limiting rows with where

SQL> SELECT ename, job, sal

2 FROM emp

3 WHERE job = ‘CLERK’;

Case sensitive;

single quotes

ENAME JOB SAL

---------- --------- ---------

JAMES CLERK 950

SMITH CLERK 800

ADAMS CLERK 1100

MILLER CLERK 1300

Limiting Rows with WHERE
where clause criteria
WHERE Clause Criteria
  • Text and dates (sometimes) are enclosed in single quotes
  • Numbers are not enclosed in quotes
  • Text values are case sensitive
  • Date values are format sensitive
  • Oracle’s default date format is DD-MON-YY, Access’ default is #MM/DD/YY#

SQL> SELECT ename, hiredate

2 FROM emp

3 WHERE hiredate >= ’01-Jan-82\';

between operator example

SQL> SELECT ename, sal

2 FROM emp

3 WHERE sal BETWEEN 1000 AND 1500;

ENAME SAL

---------- ---------

MARTIN 1250

TURNER 1500

WARD 1250

ADAMS 1100

MILLER 1300

BETWEEN Operator Example
in operator example

SQL> SELECT ename, job

2 FROM emp

3 WHERE job IN(‘PRESIDENT’,’MANAGER’);

ENAME JOB

---------- ---------

KING PRESIDENT

BLAKE MANAGER

CLARK MANAGER

JONES MANAGER

IN Operator Example
like operator example

SQL> SELECT ename

2 FROM emp

3 WHERE ename LIKE ‘J%’;

ENAME

----------

JONES

JAMES

The % is a wildcard character

that stands for zero to many

characters. The underscore

character (_) can be used to

stand for exactly one character.

[ LIKE is not the same as = ]

LIKE Operator Example
is null operator example

SQL> SELECT ename, mgr

2 FROM emp

3 WHERE mgr IS NULL;

ENAME MGR

---------- ---------

KING

IS NULL Operator Example
and operator example

SQL> SELECT ename, job, sal

2 FROM emp

3 WHERE job = ‘CLERK’

4 AND sal > 1000;

Both conditions

must be true

ENAME JOB SAL

---------- --------- ---------

ADAMS CLERK 1100

MILLER CLERK 1300

AND Operator Example
or operator example

SQL> SELECT ename

2 FROM emp

3 WHERE ename LIKE ‘J%’

4 OR ename LIKE ‘M%’;

At least one condition

must be true

ENAME

----------

JONES

MARTIN

JAMES

MILLER

OR Operator Example
not operator example

SQL> SELECT DISTINCT job

2 FROM emp

3 WHERE job NOT LIKE ‘C%’;

JOB

---------

ANALYST

MANAGER

PRESIDENT

SALESMAN

NOT Operator Example
arithmetic expression example

SQL> SELECT ename, sal, sal*12 AS annual

2 FROM emp

3 WHERE job = ‘CLERK’;

ENAME SAL ANNUAL

---------- --------- ---------

JAMES 950 11400

SMITH 800 9600

ADAMS 1100 13200

MILLER 1300 15600

Arithmetic Expression Example
sorting rows with order by

SQL> SELECT ename, job, sal

2 FROM emp

3 ORDER BY ename;

ENAME JOB SAL

---------- --------- ---------

ADAMS CLERK 1100

ALLEN SALESMAN 1600

BLAKE MANAGER 2850

CLARK MANAGER 2450

FORD ANALYST 3000

... ... ...

Sorting Rows with ORDER BY
sorting by multiple fields

SQL> SELECT ename, job, sal

2 FROM emp

3 ORDER BY job, ename;

The order of the

list determines

the precedence

of the sort order

ENAME JOB SAL

---------- --------- ---------

FORD ANALYST 3000

SCOTT ANALYST 3000

ADAMS CLERK 1100

JAMES CLERK 950

MILLER CLERK 1300

... ... ...

Sorting by Multiple Fields
sorting in descending order

SQL> SELECT ename, job, sal

2 FROM emp

3 ORDER BY sal DESC;

ENAME JOB SAL

---------- --------- ---------

KING PRESIDENT 5000

FORD ANALYST 3000

SCOTT ANALYST 3000

JONES MANAGER 2975

BLAKE MANAGER 2850

... ... ...

Sorting in Descending Order
aggregate function example

SQL> SELECT AVG(sal) AS “SALARY AVG”

2 FROM emp

3 WHERE job = ‘SALESMAN’;

SALARY AVG

----------

1400

Aggregate Function Example
aggregate function example1

SQL> SELECT COUNT(*)

2 FROM emp;

COUNT(*)

---------

14

Aggregate Function Example

Note: COUNT(*) returns the number of rows in a table

while COUNT(field)

returns the number

of rows that are

nonnull for the

field counted

subqueries
Subqueries
  • Subqueries are useful when a query is based on unknown values (e.g., “Who has a salary greater than Blake?” when Blake’s salary is unknown)
  • Subqueries involve placing an inner query (SELECT, FROM, WHERE) within a WHERE or HAVING clause of another (outer) query
  • The inner query is a complete query that could stand on its own and serves to provide values for the search condition of the outer query
subquery example

The inner query

is enclosed in

parentheses;

indenting is

optional

SQL> SELECT ename

2 FROM emp

3 WHERE sal >

4 (SELECT sal

5 FROM emp

6 WHERE empno = 7698);

ENAME

----------

KING

JONES

FORD

SCOTT

The inner query

determines the

salary of Blake

(empno = 7698)

and returns it to

the outer query

Subquery Example
aggregate function subquery

SQL> SELECTename, job, sal

2 FROM emp

3 WHERE sal =

4 (SELECT MIN(sal)

5 FROM emp);

ENAME JOB SAL

---------- --------- ---------

SMITH CLERK 800

The inner query

determines the

minimum salary

of all employees

and returns it to

the outer query

Aggregate Function Subquery
processing multiple tables
Processing Multiple Tables
  • When relationships exist between tables, the tables can be linked together in queries
  • Relationships between tables are established by setting up primary key to foreign key relationships between columns that are common to both tables
  • We link related tables together in SQL queries by using either joins or subqueries
joins
Joins
  • A join is defined as: “A relational operation that causes two tables with a common domain to be combined into a single table”
  • A join is specified in SQL by using a WHERE clause to match values for the common field between the two tables (if you were joining three tables, you would need two joining WHERE clauses)
  • Each row in the resultant table (i.e., the join product) contains data from rows in the input tables where values for the common field match
data from multiple tables

EMP

EMPNO ENAME ... DEPTNO------ ----- ... ------ 7839 KING ... 10

7698 BLAKE ... 30

7782 CLARK ... 10

7566 JONES ... 20

... ... ... ...

Data from Multiple Tables

DEPT

DEPTNO DNAME LOC

------ ---------- --------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

EMPNO DEPTNO LOC

----- ------- --------

7839 10 NEW YORK

7698 30 CHICAGO

7782 10 NEW YORK

7566 20 DALLAS

7654 30 CHICAGO

7499 30 CHICAGO

... ... ...

DEPTNO is the

common field

joining the

EMP and

DEPT tables

creating a join in sql
Write the join condition in the WHERE clause

Prefix the column name with the table name when the same column name appears in more than one table (to avoid ambiguity)

Creating a Join in SQL

SELECT table1.field, table2.field, ...

FROM table1, table2

WHERE table1.fieldX=table2.fieldX;

This is the

query that is

shown on the

previous slide

SELECT empno, emp.deptno, loc

FROM emp, dept

WHERE emp.deptno = dept.deptno;

3 table join example

SQL> SELECTord.orderdate, item.qty, product.descrip

2 FROM ord, item, product

3 WHERE ord.ordid = item.ordid

4 AND item.prodid = product.prodid;

ORDERDATE QTY DESCRIP

--------- --------- ------------------------------

07-JAN-87 1 ACE TENNIS NET

11-JAN-87 1 ACE TENNIS RACKET II

15-JAN-87 100 ACE TENNIS RACKET I

01-MAY-86 1 SB ENERGY BAR-6 PACK

05-JUN-86 20 ACE TENNIS BALLS-3 PACK

15-JUN-86 3 ACE TENNIS NET

... ... ...

3 Table Join Example

Note: Table name

prefixes are used

here for all

fields for clarity

join types
Join Types
  • Equi-join: “A join in which the joining condition is based on equality between values in the common columns. Common columns appear (redundantly) in the result table.”
  • Natural join: “Same as equi-join except one of the duplicate columns is eliminated in the result table.”
  • Outer join: “A join in which rows that do not have matching values in common columns are nevertheless included in the result table.”
  • Self join: A join that represents a recursive unary relationship of a table with itself.
equi join example

Note table name prefixes used to

specify names for common fields

Note joining

WHERE clause

SQL> SELECTename,emp.deptno,dept.deptno,dname

2 FROM emp, dept

3 WHERE emp.deptno = dept.deptno;

Note duplicate

columns

ENAME DEPTNO DEPTNO DNAME

---------- --------- --------- --------------

KING 10 10 ACCOUNTING

BLAKE 30 30 SALES

CLARK 10 10 ACCOUNTING

JONES 20 20 RESEARCH

MARTIN 30 30 SALES

ALLEN 30 30 SALES

... ... ... ...

Equi-join Example
natural join example

Note elimination

of duplicate field

SQL> SELECT ename, emp.deptno, dname

2 FROM emp, dept

3 WHERE emp.deptno = dept.deptno;

Note elimination

of duplicate column

ENAME DEPTNO DNAME

---------- --------- --------------

KING 10 ACCOUNTING

BLAKE 30 SALES

CLARK 10 ACCOUNTING

JONES 20 RESEARCH

MARTIN 30 SALES

ALLEN 30 SALES

... ... ...

Natural Join Example
outer join example

First, let’s run this

as a natural join

SQL> SELECTename, emp.deptno, dname

2 FROM emp, dept

3 WHERE emp.deptno = dept.deptno;

ENAME DEPTNO DNAME

---------- --------- --------------

KING 10 ACCOUNTING

BLAKE 30 SALES

CLARK 10 ACCOUNTING

... ... ...

MILLER 10 ACCOUNTING

A total of 14 rows

are returned

Outer Join Example
outer join example1

Now, we’ll run this

as an outer join;

note the (+) symbol

SQL> SELECTename, emp.deptno, dname

2 FROM emp, dept

3 WHERE emp.deptno(+)=dept.deptno;

ENAME DEPTNO DNAME

---------- --------- --------------

KING 10 ACCOUNTING

BLAKE 30 SALES

CLARK 10 ACCOUNTING

... ... ...

MILLER 10 ACCOUNTING

OPERATIONS

A total of 15 rows

are returned

Included is this

department

that has no

employees

Outer Join Example
using outer joins
Using Outer Joins
  • The outer join operator (+) can appear on only one side of the equal sign in the WHERE clause; it is placed on the side that is deficient in information
  • A condition involving an outer join cannot be linked to another condition by the OR operator
  • A condition involving an outer join cannot use the IN operator
group by having
Group by/Having
  • The “group by” function groups rows where the values in those rows are the same for one or more columns
  • The “having” function can only be used in conjunction with the “group by” function. It acts as a secondary where clause.
categorizing with group by

SQL> SELECT job, AVG(sal)

2 FROM emp

3 GROUP BY job;

JOB AVG(SAL)

--------- ---------

ANALYST 3000

CLERK 1037.5

MANAGER 2758.3333

PRESIDENT 5000

SALESMAN 1400

Categorizing with GROUP BY

Note: Any field or

expression in the

SELECT list that

is not an aggregate

function must be in

the GROUP BY clause

categorizing with group by1

SQL> SELECT deptno, COUNT(deptno)

2 FROM emp

3 GROUP BY deptno;

DEPTNO COUNT(DEPTNO)

--------- -------------

10 3

20 5

30 6

Categorizing with GROUP BY

Note: The GROUP BY

field does not

have to be in

the SELECT list

limiting group by with having

SQL> SELECT deptno, COUNT(deptno)

2 FROM emp

3 GROUP BY deptno

4 HAVING COUNT(deptno) >= 5;

DEPTNO COUNT(DEPTNO)

--------- -------------

20 5

30 6

Limiting GROUP BY with HAVING
self joins
Self Joins
  • Used to join a table to itself
  • Useful to query a table involved in a recursive unary relationship with itself
  • Aliases are used in order to be able to distinguish the side of the relationship the table being referenced is on
    • e.g., we could reference the emp table on the employee side of a “manages” unary relationship as “e” and the same table on the manager side of the relationship as “m”
preparation for self join

Identify the

common fields

SQL> SELECT empno, ename, mgr

2 FROM emp;

EMPNO ENAME MGR

--------- ---------- ---------

7839 KING

7698 BLAKE 7839

7782 CLARK 7839

7566 JONES 7839

7654 MARTIN 7698

7499 ALLEN 7698

... ... ...

Preparation for Self Join
self join example

Aliases

SQL> SELECT e.ename, m.ename AS manager

2 FROM emp e, emp m

3 WHERE e.mgr = m.empno (+);

ENAME MANAGER

---------- ----------

KING

BLAKE KING

CLARK KING

JONES KING

MARTIN BLAKE

... ...

Self Join Example

Note: Aliases can be

useful in other

situations besides

self joins. Any time

you wish to abbreviate

a table name you

can use them

correlated sub queries
Correlated sub-queries
  • A query (outer query) with a nested query (inner query) in which processing the inner query depends on data from the outer query.
  • The inner query is processed for each outer row.
exists not exists
Exists/Not exists
  • Existential qualifier: used in a where clause to test whether a table contains at least one row satisfying a specific condition. Returns a true or false.
  • List all courses which were taken by no students in the most recent semester

Select name from course

where not exists (Select * from Schedule

where course.course_ID=schedule.course_ID);

universal qualifier
Universal Qualifier
  • SQL does not provide direct support for a universal qualifier, but it can be accomplished…
  • List all the students who have taken every MIS class.

Select name from student

where not exists (Select * from course

where not exists (Select * from schedule

where student.student_ID=schedule.student_ID and course.course_ID=schedule.course_ID));

referencing a table by owner

SQL> SELECT ename, mgr

2 FROM demo.emp;

The User ID of the owner

of the emp table is demo

Referencing a Table by Owner
  • If you are not the owner of a table (i.e., if you did not create the table), you must append the table owner’s User ID to the table name when you reference the table
  • Example:
  • Synonyms can be created by the DBA toreduce the necessity of this referencing
a final example

SQL>SELECTename, emp.deptno, loc as city

2 FROM demo.emp, demo.dept

3 WHERE emp.deptno=dept.deptno

4 AND emp.deptno<>10

5 ORDER BY city, ename;

ENAME DEPTNO CITY

---------- ---------- -------------

ALLEN 30 CHICAGO

BLAKE 30 CHICAGO

... ... ...

ADAMS 20 DALLAS

... ... ...

SMITH 20 DALLAS

A Final Example

How many

different

skills are

shown here?

Do you

understand

the result of

this query?

(11 records)

ad