SQL
This presentation is the property of its rightful owner.
Sponsored Links
1 / 81

SQL PowerPoint PPT Presentation


  • 56 Views
  • Uploaded on
  • Presentation posted in: General

SQL. Chapter 9:. An Introduction to Structured Query Language (SQL). (With Considerable Modifications). SQL. Why SQL??.  “De facto standard in RDBMS”.  Can be implemented on all platforms.  Processes data on a SET Level.  Works with groups of data (Tables).

Download Presentation

SQL

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


Sql

SQL

Chapter 9:

An Introduction to Structured Query Language (SQL)

(With Considerable Modifications)


Sql

SQL

Why SQL??

“De facto standard in RDBMS”

Can be implemented on all platforms

Processes data on a SET Level

 Works with groups of data (Tables)

Relatively Few Statements

 Portability

 Ease of Learning

 Ease of use

Developed around concept of

 Data Definition Language (DDL)

 Data Manipulation Language (DML)

 Data Control Language (DCL)


Sql

PhysID

Name

Specialty

Street

City

State

ZipCode

SQL

Database Definition

Recall our Physician Table:

Physician Table Attributes/Fields

 We know the fields we need, and perhaps even what field names we will use, BUT what type of data will we use to store the information ???

What Types of data are there ???


Sql

SQL

Database Definition

Basic Data Types: (C Data Types used for illustration)

Character

char/signed

1-byte

-128 to + 127

(Maybe)

char/signed

(OR)

2-byte

-32,768 to 32,767

unsigned char

(Maybe)

1-byte

0 to + 255

(OR)

unsigned char

2-bytes

0 to 65,537

Integer

int/signed

2-bytes

-32,768 to 32,767

(Maybe)

(OR)

int/signed

4-bytes

-2,147,483,648 to 2,147,483,647

unsigned int

2-bytes

0 to 65,537

(Maybe)

(OR)

unsigned int

4-bytes

0 to 4,294,967,295

long/signed

4-bytes

-2,147,483,648 to 2,147,483,647

(OR)

long/signed

8-bytes

-9,223,372,036,854,780,000 to …….

unsigned long

4-bytes

0 to 4,294,967,295

(OR)

unsigned long

4-bytes

0 to 18,467,440,737,906,000,000

float

4-bytes

7 decs. precision

Real

double

8-bytes

10 decs. precision

long double

16-bytes

10 decs. precision


Sql

SQL

Database Definition

Of course, there are additional abstract data structures

Arrays

A fixed number of contiguous data elements all of the same type

Strings

An array of data type char

Structs

A combination of two or more data types

  • Structured data objects

  • Records

Others

  • Date/Time

  • Graphics

  • OLE (Object Linking and Embedding)

  • Hyperlinks


Sql

SQL

Database Definition

Oracle Allows for the following Basic data types to be entered into Fields (There are others)

Characters

CHAR

  • A string

  • Up to 255 characters are allowed

FieldnameCHAR(N)

  • Usage:

  • The length (N) MUST be specified

  • If less than N characters are entered, N spaces will STILL be allocated


Sql

SQL

Database Definition

Characters

VARCHAR

  • A string

  • Up to 255 characters are allowed

fieldnameVARCHAR(N)

  • Usage:

  • The length (N) MUST be specified

  • ONLY the numbercharacters are entered will be allocated space

VARCHAR2, LONG VARCHAR


Sql

SQL

Database Definition

Numeric Data Types

INTEGER

Typically, up to 10 - 11 digits are allowed

  • Range (Typically):

-2,147,483,648 through +2,147,483,647

  • Usage:

fieldnameINTEGER

SMALLINT

Typically, up to 4 - 5 digits are allowed

  • Range (Typically):

-32,768 through +32,767

  • Usage:

fieldnameSMALLINT


Sql

SQL

Database Definition

Numeric Data Types

DECIMAL(m,n)

A Real (Floating-Point) Number with m total digits (INCLUDING the sign and decimal point) and n decimals to the right of the decimal point

  • m may (Typically) be as large as 19

  • n may (Typically) be as large as 7 - 8

  • Usage:

fieldnameDECIMAL(10,4)

This allows for a number between

99999.9999 through -9999.999

(The sign counts)

But ONLY to 3 decimal point of precision


Sql

SQL

Database Definition

Date and Time Data Types

DATE

Date Value

  • (Typically) Displayed as mm/dd/yy

  • Usage:

fieldnameDATE

TIME

Time Value

  • (Typically) Displayed as hh:mm:ss

  • Useful in Time Stamping

  • Usage:

fieldnameTIME


Sql

PhysID

Name

Specialty

Street

City

State

ZipCode

SQL

Database Definition

NOW, Returning to our Physician Table:

Let’s assume the following Field Names and Data types:

Physician Table Attributes/Fields

physidCHAR(9)

physnameCHAR(30)

specialtyCHAR(15)

streetCHAR(20)

cityCHAR(20)

stateCHAR(2)

zipCHAR(5)


Sql

SQL

Database Definition

Before going on to SQL, Let’s see how other languages might create the record

In COBOL:

01PHYSICIAN

05 PHYSIDPIC X(9).

05 PHYSNAMEPIC X(30).

05 SPECIALTYPIC X(15).

05 STREETPIC X(20).

05 CITYPIC X(20).

05STATEPIC X(2).

05ZIPPIC X(5).


Sql

SQL

Database Definition

In Pascal:

In C:

struct physician

{ char physid[10];

char physname[31];

char specialty[16];

char street[21];

char city[21];

char state[3];

char zip[6];

};

type physician = record

physid: array[1..9] of char;

physname:array[1..30] of char;

specialty: array[1..15] of char;

street: array[1..20] of char;

city: array[1..20] of char;

state: array[1..2] of char;

zip: array[1..5] of char;

end;


Sql

PhysID

Name

Specialty

Street

City

State

ZipCode

SQL

Database Definition

The SQL Commands Needed to create the table are

Physician Table Attributes/Fields

CREATE TABLE physician

( physidCHAR(9),

physnameCHAR(30),

specialtyCHAR(15),

streetCHAR(20),

cityCHAR(20),

stateCHAR(2),

zipCHAR(5) );

Every SQL Command ends with a semicolon


Sql

SQL

Database Definition

Let’s Examine the command

  • We have CREATEd a new table (file) called physician

CREATE TABLE physician

( physidCHAR(9),

physnameCHAR(30),

specialtyCHAR(15),

streetCHAR(20),

cityCHAR(20),

stateCHAR(2),

zipCHAR(5) );

  • Each record we enter will contain 7 fields

  • Each record will require 9 + 30 + 15 + 20 + 20 + 2 + 5 = 101 Bytes of Storage

  • We encased our field declarations between parentheses ()

  • Each field (except for the last) was separated by a comma ( , )


Sql

SQL

Database Definition

But don’t we know more about the table??

Yes, We Know:

  • The Table physician has as its primary key the field physid

  • It wouldn’t make sense to leave the physname field blank (although theoretically, we could)

We Could also make some assumptions:

  • The physician lives in Texas (Code: TX)


Sql

PhysID

Name

Specialty

Street

City

State

ZipCode

SQL

Database Definition

Let’s rewrite the command:

Physician Table Attributes/Fields

CREATE TABLE physician

( physidCHAR(9) NOT NULL, physnameCHAR(30)NOT NULL,

Adding these Qualifiers will assure that the fields will NOT be left empty

This is Superfluous. WHY ???


Sql

PhysID

Name

Specialty

Street

City

State

ZipCode

SQL

Database Definition

Let’s rewrite the command:

Physician Table Attributes/Fields

CREATE TABLE physician

( physidCHAR(9) NOT NULL, physnameCHAR(30)NOT NULL,

specialtyCHAR(15),

streetCHAR(20),

cityCHAR(20),

stateCHAR(2),DEFAULT ‘TX’,

If nothing is entered into the state field, TX will be entered by default


Sql

PhysID

Name

Specialty

Street

City

State

ZipCode

SQL

Database Definition

Let’s rewrite the command:

Physician Table Attributes/Fields

CREATE TABLE physician

( physidCHAR(9) NOT NULL, physnameCHAR(30)NOT NULL,

specialtyCHAR(15),

streetCHAR(20),

cityCHAR(20),

stateCHAR(2),DEFAULT ‘TX’,

zipCHAR(5),

PRIMARY KEY(physid) );

physid has been identified as the primary key


Sql

SQL

Database Definition

We Can get also add additional constraints

  • Let’s assume that we can only have one (1) specialist in each area:

CREATE TABLE physician

( physidCHAR(9) NOT NULL, physnameCHAR(30)NOT NULL,

specialtyCHAR(15)NOT NULL,

streetCHAR(20),

cityCHAR(20),

stateCHAR(2),DEFAULT ‘TX’,

zipCHAR(5),

PRIMARY KEY(physid)

UNIQUE(specialty) );


Sql

SQL

Database Definition

We need to ‘Back-Track’

  • Remember that we previously discussed Schemas

 The Network Schema

 The conceptual Organization of the entire database

 The Network Subschema

 The conceptual Organization of the database as “seen” by the applications programs accessing it

We need to Develop our tables with this in mind


Sql

SQL

Database Definition

A Schema consists of

  • Tables (which we previously created)

  • Views (Or Virtual Table)

 A subset of rows and columns

 Not a physical entity (as are tables)

 Can be treated as tables

 Created by the DBMS each time they are referenced by the user as a query

  • User Authorization

 Usernames allowed (and how allowed) to access the tables


Sql

SQL

Database Definition

A more appropriate CREATE might have been:

CREATE SCHEMA

ATHORIZATION pkirs

CREATE TABLE physician

( physidCHAR(9), physnameCHAR(30)NOT NULL,

specialtyCHAR(15)NOT NULL,

streetCHAR(20),

cityCHAR(20),

stateCHAR(2),DEFAULT ‘TX’,

zipCHAR(5),

PRIMARY KEY(physid)

UNIQUE(specialty) );


Sql

SQL

Database Definition

Some Basic CREATE TABLE Guidelines

  • Choose Numeric data types ONLY if calculations are to be performed on the field

  • Make the lengths of the character columns long enough to accommodate future values (Remember the Y2K Problem)

  • Apply the same guidelines for DECIMAL data

  • Don’t automatically choose VARCHAR over CHAR

  • Use EXACTLY the same data types for columns which will be compared often or used in calculations together

  • Use NOT NULL when a column MUST contain a value

  • PRIMARY KEYSAREALWAYSNOT NULL


Sql

Patient

Treats

SQL

Database Definition

Let’s Consider the relationship between our Physician and Patient tables

Physician

Remember what this means:

  • A Patient MUST have one (and ONLY one) Physician

  • A Physician MAY have more than 1 (one) Patient


Sql

PatID

PhysID

Name

Name

Address

Specialty

Street

PhysID

City

State

ZipCode

SQL

Database Definition

We Previously defined our Patient fields:

And how Patient relates to our Physician table:

Patient Table Attributes/Fields

Physician Table Attributes/Fields

physid has in Patient is a foreign key


Sql

PatID

Name

……..

PhysID

SQL

Database Definition

We now need to define our Patient fields:

Patient Table Attributes/Fields

CREATE TABLE patient

( patidCHAR(9) NOT NULL, nameCHAR(30)NOT NULL,

physidCHAR(9) NOT NULL,

PRIMARY KEY(patid)

FOREIGN KEY(physid)

REFERENCES physician (physid));

Let’s Pretend that field Address doesn’t Exist

We have established our relationship between patient and physician


Sql

CREATE TABLE patient

( patidCHAR(9) NOT NULL, nameCHAR(30)NOT NULL,

physidCHAR(9) NOT NULL,

PRIMARY KEY(patid),

FOREIGN KEY(physid)

REFERENCES

physician (physid) );

SQL

Database Definition

Again, Let’s Examine the command

  • We have CREATEd a new table (file) called patient

  • Each record we enter will contain 3 fields

  • Each record will require 9 + 30 + 9 = 48 Bytes of Storage

  • We have linked two (2) tables together

  • We encased our field declarations between parentheses ()

  • Each field (except for the last) was separated by a comma ( , )


Sql

SQL

Database Definition

We are on the way to building a Script (Program)

  • A text file that contains SQL commands

So far, our Script would appear as:

CREATE SCHEMA

ATHORIZATION pkirs

CREATE TABLE physician

( physidCHAR(9) NOT NULL, physnameCHAR(30)NOT NULL,

specialtyCHAR(15)NOT NULL,

streetCHAR(20),

cityCHAR(20),

stateCHAR(2),DEFAULT ‘TX’,

zipCHAR(5),

PRIMARY KEY(physid)

UNIQUE(specialty) );

CREATE TABLE patient

( patidCHAR(9) NOT NULL, nameCHAR(30)NOT NULL,

physidCHAR(9) NOT NULL,

PRIMARY KEY(patid),

FOREIGN KEY(physid)

REFERENCES physician (physid) );


Sql

Suffer

Illness

IllCode

PatID

Name

Patient Table

Suffers Table

Illness Table

Date/Time

Name

1

PatID

1

M

Others

……..

IllCode

PhysID

M

Others

SQL

Database Definition

Finally, Let’s create two more tables

Patient

Remember, this is a Many-to-Many Relationship


Sql

IllCode

Name

Illness Table

. . . . . .

SQL

Database Definition

First, let’s create our illness table:

CREATE TABLE illness

( illcodeCHAR(10), nameCHAR(20)NOT NULL,

PRIMARY KEY(illcode)

UNIQUE(name));

Let’s Pretend that field Others doesn’t Exist


Sql

Suffers Table

Date/Time

PatID

IllCode

. . . . . . .

SQL

Database Definition

We now need to define our Suffers fields:

CREATE TABLE suffers

( patidCHAR(9) NOT NULL, illcodeCHAR(10)NOT NULL,

sdateDATE NOT NULL,

stimeTIMENOT NULL,

PRIMARY KEY(patid, illcode),

FOREIGN KEY patid

REFERENCES patient (patid),

FOREIGN KEY illcode

REFERENCES illness (illcode) );

Let’s Pretend that field Others doesn’t Exist


Sql

SQL

Database Definition

Our Script would appear as:

CREATE SCHEMA

ATHORIZATION pkirs

CREATE TABLE physician

( physidCHAR(9) NOT NULL, physnameCHAR(30)NOT NULL,

specialtyCHAR(15)NOT NULL,

streetCHAR(20),

cityCHAR(20),

stateCHAR(2),DEFAULT ‘TX’,

zipCHAR(5),

PRIMARY KEY(physid)

UNIQUE(specialty) );

CREATE TABLE illness

( illcodeCHAR(10) NOT NULL, nameCHAR(20)NOT NULL,

PRIMARY KEY(illcode)

UNIQUE(name));

CREATE TABLE suffers

( patidCHAR(9), illcodeCHAR(10)NOT NULL,

sdateDATE NOT NULL,

stimeTIMENOT NULL,

PRIMARY KEY(patid, illcode),

FOREIGN KEY (patid)

REFERENCES patient (patid),

FOREIGN KEY (illcode)

REFERENCES illness (illcode) );

CREATE TABLE patient

( patidCHAR(9) NOT NULL, nameCHAR(30)NOT NULL,

physidCHAR(9) NOT NULL,

PRIMARY KEY(patid),

FOREIGN KEY (physid)

REFERENCES physician (physid) );


Sql

SQL

Database Definition

Additional options in CREATE

 CHECK

  • Suppose ALL of our physicians could ONLY live in Arlington, Dallas, or Fort Worth

  • Our physician table could be rewritten as:

CREATE TABLE physician

( physidCHAR(9) , physnameCHAR(30)NOT NULL,

specialtyCHAR(15),

streetCHAR(20),

cityCHAR(20),

stateCHAR(2),DEFAULT ‘TX’,

zipCHAR(5),

PRIMARY KEY(physid),

CHECK(city in ‘Arlington’, ‘Dallas’, Ft. Worth’) );


Sql

SQL

Database Definition

Additional CHECK options

  • If we had an integer field called salary, and salary HAD to be greater than 50000:

CREATE SCHEMA

( ººººº

CHECK(salary > 50000) );

  • If we had an integer field called salary, and salary HAD to be greater than 50000 BUT less than 100000:

CREATE SCHEMA

( ººººº

CHECK(salary BETWEEN 50000 AND 100000) );


Sql

SQL

Database Definition

Security/Privilege Options

  • The owner (AUTHORIZATIONpkirs) has full access

  • If we wished to allow user clinton (which MUST be a registered username) full access to table physician:

CREATE SCHEMA

ATHORIZATION pkirs

CREATE TABLE physician

( ººººº )

GRANT ALL ON physician TO clinton;


Sql

SQL

Database Definition

  • If we wished to allow user clinton to insert new records into to table physician:

GRANT INSERT ON physicianTO clinton;

  • If we wished to allow user clinton ONLY to edit tables physician and patient:

GRANT UPDATE ON physician, patient TO clinton;

  • If we allow user clinton ONLY to view physician and patient data:

GRANT SELECT ON physician, patient TO clinton;

  • If we allow everyone to view and update table physician:

GRANT SELECT, UPDATE ON physician TO PUBLIC


Sql

SQL

Database Modification

Inserting records

INSERT INTO physician VALUES

(‘123456789’, ‘Smith, John’, ‘Surgery’, ‘123Main’,

‘El Paso’, ‘TX’, ‘76019’)

  • Note that this corresponds to our physician table structure:

( physidCHAR(9),

physnameCHAR(30),

specialtyCHAR(15),

streetCHAR(20),

cityCHAR(20),

stateCHAR(2),

zipCHAR(5) );


Sql

SQL

Database Modification

Saving records

  • Any new records added (or changes made to records) will not be permanent until we enter the command:

COMMIT;

Changing our minds

  • If we decide we shouldn’t have entered the record (or made the changes), we can enter the command:

ROLLBACK;

  • Which will restore the database back to how it was before the last COMMIT was entered


Sql

123456789

physid

Smith, John

physname

Surgery

specialty

123 Main

street

city

El Paso

TX

state

76019

zip

SQL

Database Modification

Updating Records

  • Right now, our physician table appears as:

  • If we wished to change John Smith’s specialty from surgery to OB/GYN we would enter the command:

UPDATE physician

SET specialty = ‘OB/GYN’

WHERE physid = ‘123456789’;

  • NOTE: The changes would be applied only AFTER we COMMIT


Sql

SQL

Database Modification

Updating Records

  • Notice also that the command

UPDATE physician

SET specialty = ‘OB/GYN’

WHERE physid = ‘123456789’;

  • Requires us to find for a specific record using the search condition:

WHERE physid = ‘123456789’;

(a TRUE/FALSE condition)


Sql

SQL

Database Modification

Deleting Records

  • To delete a record from a database, we must once again find it and issue the command:

DELETE FROM physician

WHERE physid = ‘123456789’;

  • Once Again, The changes would be applied only AFTER we COMMIT


Sql

SQL

Database Modification

Deleting Records

  • We could also delete multiple records:

DELETE FROM patient

WHERE physid NOT IN (‘123456789’, ‘234567890’);

  • This would delete all records who did not have these two physicians as their primary providers

(We can get much fancier here)


Sql

SQL

Database Modification

Deleting Tables

  • To delete a table from a database, we need only issue the command:

DROP TABLE physician;

  • A DROPDOES NOT require an explicit COMMIT statement (although a ROLLBACK will undo the command)


Sql

SQL

Database Structure Modification

Altering the database (FOR MOST RDBMS)

  • Unrestricted Alterations

  • Adding a new column to a table

  • Deleting a primary key

(Foreign Key references are automatically removed)

  • Deleting a foreign key

  • Restricted Alterations

  • Changing a columns data type, size, and default value is allowed ONLY if there are NO DATA in the column being modified


Sql

SQL

Database Structure Modification

Altering the database (FOR MOST RDBMS)

(Continued)

  • Restricted Alterations

  • Adding a Primary Key, BUT only if there are no duplicate values

  • Adding UNIQUE and CHECK condition ONLY if the field matches the added condition

  • Adding a foreign key allowed ONLY if the values are NOT NULL or exist in the referenced Table

  • Changing a column name is Not Allowed

  • Deleting a column is NOT allowed


Sql

SQL

Database Structure Modification

Altering the database

  • To add a field to a database

ALTER TABLE physician

ADD (malpracticefees DECIMAL(7,2));

  • We can also specify WHERE to add it and add constraints:

ALTER TABLE physician

ADD (malpracticefees DECIMAL(8,2))

BEFORE street

INIT = 10000.00;


Sql

SQL

Database Structure Modification

Altering the database

  • Suppose that we find out that we need more space for our specialty field, presently:

specialtyCHAR(15),

  • We might enter the command:

ALTER TABLE physician,

MODIFY (specialty CHAR(20));

  • which would accept up to 20 characters for a physician specialty


Sql

SQL

Database Structure Modification

Altering the database

  • If, for some reason, we decided to change physid from a character to a numeric field, we might enter the command:

ALTER TABLE physician

MODIFY (physid INTEGER);

  • NOTE:

This assumes that the field CAN be converted into a numeric Field

4B7876D

  • If physid contained the data:

It could not be converted


Sql

SQL

Database Structure Modification

Altering the database

  • To remove a primary or foreign key, we might issue the command:

DROP CONSTRAINT ……. ;

  • NOTE: If this sounds like a cop-out, it is because there are a number of variations


Sql

SQL

Database Structure Modification

Altering the database

  • IF we have designated a primary of foreign key (or we have removed them as above), we could now add them as:

ALTER TABLE physician

ADD PRIMARY KEY (physname);

  • AND

ALTER TABLE patient

ADD FOREIGN KEY (physname)

REFERENCES (physician);


Sql

SQL

Importing Data from another Database

NOTE:

  • Data imported from other tables MUST be of the same data type

  • IF we had a table physinfo which was IDENTICAL to our table physician, we could enter the command:

INSERT INTO physician

SELECT *

FROM physinfo;


Sql

SQL

Importing Data from another Database

  • IF the table physinfo had some fields which matched our table physician (and some which perhaps didn’t), we might enter the command:

INSERT INTO physician, (street, city, state, zip)

SELECT (streetname, cityname, statename, zipcode)

FROM physinfo

WHERE NOT (physid = idnum);

  • NOTE: Fields in table physician had which were NOT imported from table physinfo will remain empty.


Sql

SQL

Database Queries

Extracting information from a database

Performed using high-level Query Languages

Very similar across SQL platforms

(Although differences DO exist)

Use of standard English commands:

  • SELECT *

  • SELECT

  • FROM

  • WHERE

  • GROUP BY

  • HAVING

  • ORDER BY


Sql

SQL

Database Queries

The Command:

SELECT * FROM physinfo ;

Would result in the (approximate) display:

physid physname specialty street city state zip

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

123456789 Smith, John OB/GYN 123 Main El Paso TX 76019

The Command:

SELECT physid, physname, specialty FROM physician;

Would result in the (approximate) display:

physid physname specialty

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

123456789 Smith, John OB/GYN


Sql

SQL

Database Queries

In SQL, We also have the choice of listing ALL (using SELECT *,OR SELECT ALL) records or DISTINCT records:

SELECT DISTINCT specialty

FROM physician;

Might result in the (approximate) display:

specialty

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

Surgery

Dermatology

OB/GYN

Opthamology

ENT

Pediatrics


Sql

SQL

Database Queries

The Command:

SELECT physid, physname

FROM physician

WHERE (specialty = ‘Surgery’

OR specialty = ‘Dermatology’);

Might result in the (approximate) display:

physid physname

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

123456789 Smith, John

234567890 Jones, Mary

345678901 Houston, Sam

456789012 Bush, G.W.

567890123 Clinton, Hillary

NOTE: Because we are using the OR operator, ALL records meeting the condition will be shown


Sql

SQL

Database Queries

The Command:

SELECT physid, physname

FROM physician

WHERE (specialty = ‘Surgery’

AND specialty = ‘Dermatology’);

Might result in the (approximate) display:

physid physname

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

NOTE: With the AND operator, ONLY those records meeting BOTH conditions will be shown (in this case, None)


Sql

partnum

cost

price

onhand

105

103

100

102

101

104

2.56

1.76

12.83

7.16

0.89

4.88

7.22

12.99

3.89

2.99

17.99

1.45

28

122

53

82

45

8

SQL

Database Queries

SQL has the following built-in Operators:

Operator(s)Description

* /Multiplication Division

+ -Addition Subtraction

Suppose we had a table called inventory:


Sql

price

cost

partnum

cost

price

onhand

Margin

102

104

100

105

101

103

1.76

2.56

4.88

7.16

0.89

12.83

3.89

12.99

1.45

7.22

17.99

2.99

82

122

45

8

28

53

51.95

62.92

69.89

40.22

47.95

81.42

SQL

Database Queries

Suppose we wanted to find the items where our profit margin was 60% or more

In this case our profit is defined as:

- 1

* 100

For our table inventory, our profit margins are:


Sql

SQL

Database Queries

The Command:

SELECT partnum, price - cost

FROM inventory

WHERE ((price / cost - 1)* 100 => 0.60);

Would result in the (approximate) display:

partnum price - cost

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

101 1.33

102 1.23

105 5.83

Notice we didn’t do a very good job labeling our fields


Sql

SQL

Database Queries

The Command:

SELECT partnum AS ‘Part’, cost AS ‘Cost’, price AS ‘Price’,

price - cost AS ‘Profit’, (price / cost - 1)* 100 AS ‘Margin’

FROM inventory

WHERE ((price / cost - 1)* 100 => 0.60)

ORDER BY ((price / cost - 1)* 100) DESC;

Would result in the (approximate) display:

Part CostPriceProfit Margin

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

105 7.1912.995.83 81.42

102 1.762.991.23 69.89

101 0.89 1.45 1.33 62.95


Sql

SQL

Database Queries

SQL has a number of built-in numeric functions:

FunctionOutput

COUNTThe number of rows containing

the attribute

MINThe minimum attribute encountered

MAXThe maximum attribute encountered

SUMThe total of all values for a selected

attribute

AVGThe arithmetic mean for a selected

attribute


Sql

SQL

Database Queries

The Command:

SELECT COUNT (DISTINCT partnum)AS ‘No. Parts’,

MIN (price) AS ‘Cheapest’, MAX(price) AS ‘Costliest’,

SUM(cost * onhand) AS ‘Inv. Cost’,

SUM (price * onhand) AS ‘Inv. Potential’,

AVG ((price / cost - 1)* 100) AS ‘Ave. Margin’,

SUM((price * onhand)-(cost * onhand))AS ‘Pot. Profit’,

FROM inventory;

Would result in the (approximate) display:

No. PartsCheapestCostliestInv. CostInv. PotentialAve. MarginPot. Profit

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

6 1.4517.99986.861631.6859.06 644.82


Sql

SQL

Database Queries

SQL also has a number of built-in special functions:

FunctionOutput

BETWEENUsed to define Range Limits

IS NULLUsed to check if an attribute contains

NULL Values

LIKEUsed to check similar Character

Strings

INUsed to check if an attribute is

contained IN a set of Values

EXISTSUsed to check if an attribute value

exists (The opposite of IS NULL)


Sql

SQL

Database Queries

The Command:

SELECT partnum,cost, price

FROM inventory

WHERE

partnum BETWEEN 102 AND 104;

Would result in the (approximate) display:

partnum costprice

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

102 1.762.99

103 12.8317.99

104 4.887.22


Sql

SQL

Database Queries

The Command:

SELECT physid,physname

FROM physician

WHERE physname LIKE ‘Smi%’;

Where:% is a wildcard

Might result in the (approximate) display:

Physidphysname

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

123456789 Smithers

234567890 Smith

456789012 Smickman

678901234Smiley


Sql

SQL

Database Queries

The LIKE command can take on a few forms:

Search StringPattern Matched

Sm%Finds Any string starting with Sm

%Sm%Finds Any string containing Sm

%SmFinds Any string ending with Sm

S_mFinds any 3 letter string beginning with S and ending in m

%S_m%Finds any string containing the letter S followed by any character, followed by the character m

NOT LIKEThe opposite of LIKE


Sql

SQL

Database Queries

Often, it is necessary to JOIN tables together on common keys (E.g., a foreign key)

We Might enter the command:

SELECT physid,physname, patid, name

FROM physician, patient

WHERE physician.physid = patient.physid;

NOTE:If two tables have the same field names, we need to indicate which is which using DOT NOTATION

This might result in the (approximate) display:

Physidphysnamepatidname

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

123456789 Smith 987654321 Gore 123456789 Smith876543210Bradley

456789012 Jones765432109Richardson

678901234Bush654321098Sanders


Sql

Treatment

Illness

Patient

Treats

Prescription

SQL

Database Queries

We can also JOIN multiple tables together

Remember our expanded relationship:

Physician

Where we had a ternary relationship between patient, illness, and prescription


Sql

PatID

DrugCode

IllCode

Name

Others

Name

DrugCode

PatID

Date/Time

Address

Others

IllCode

Others

PhysID

SQL

Database Queries

In Tabular Form, this appeared as:

Patient Table

Treatment Table

Illness Table

Prescription Table


Sql

SQL

Database Queries

One command we might enter is:

SELECTphysname, patient.name, illness.name,

prescription.drugcode

FROM physician, patient, treatment, illness, prescription

WHERE physician.physid = patient.physid

AND patient.patid = treatment.patid

AND treatment.illcode = illness.illcode

AND treatment.drugcode = prescription.drugcode

ORDER BY physname;

(It CAN get involved)


Sql

SQL

Database Queries

This command might yield the outcome:

physnamepatient.nameillness.nameprescription.drugcode

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

BushImanBeautyS228C99

ClintonLewinskiSore throatL001H98

ClintonFlowersHumiliationJ897T11

GuilianniClintonHumiliationA345B23

JonesRichardsonDepressionJ897T11

JonesVonnegutWritersblkK980F66

Smith GoreDullnessA345B23

SmithBradleyTallnessS228C99

SmithBeethovenDeafnessA345B23

SmithAikmanSore ArmA345B23

SmithBradleyTallnessA345B23

Smith GoreEuphoriaS228C99

SmithAstaireBrk LegA345B23


Sql

SQL

Database Queries

NOW, remember our original Concern:

The head administrator wants a list of all of Dr. Smith’s patients, their illnesses, and what prescriptions were given to those people.

 Let’s assume that we had a field in our prescription table called drugname (for the name of the drug).

 Our command might be:

SELECTphysname, patient.name, illness.name,

prescription.drugname

FROM physician, patient, treatment, illness, prescription

WHERE physician.physid = patient.physid

AND patient.patid = treatment.patid

AND treatment.illcode = illness.illcode

AND treatment.drugcode = prescription.drugcode

AND physid = ‘123456789’;


Sql

SQL

Database Queries

This command might yield the outcome:

physnamepatient.nameillness.nameprescription.drugname

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

Smith GoreDullnessThorazine

SmithBradleyTallnessAspirin

SmithBeethovenDeafnessThorazine

SmithAikmanSore ArmThorazine

SmithBradleyTallnessThorazine

Smith GoreEuphoriaAspirin

SmithAstaireBrk LegThorazine

And Maybe the Administrator DOES have a valid concern


Sql

SQL

Database Queries

If we wanted to get fancier, we might issue the command:

SELECTphysname, patient.name, illness.name,

prescription.drugname

FROM physician, patient, treatment, illness, prescription

WHERE physician.physid = patient.physid

AND patient.patid = treatment.patid

AND treatment.illcode = illness.illcode

AND treatment.drugcode = prescription.drugcode

AND physid = ‘123456789’

ORDER BY COUNT(prescription.drugname,patient.name);


Sql

SQL

Database Queries

This command might yield the outcome:

physnamepatient.nameillness.nameprescription.drugname

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

SmithAikmanSore ArmThorazine

SmithAstaireBrk LegThorazine

SmithBeethovenDeafnessThorazine

SmithBradleyTallnessThorazine

Smith GoreDullnessThorazine

SmithBradleyTallnessAspirin

Smith GoreEuphoriaAspirin


Sql

SQL

Database Queries

Notice that our queries can become complex, and that reentering the command each time becomes tedious

We can create permanent VIEWS

  • A VIEW is a logical table (NOT physical) which contains a query

  • A VIEW holds the query commands and is run each time it is called

  • A VIEW must be CREATEd and selected when to be run


Sql

SQL

Database Queries

To create our view:

CREATE VIEW drugs_given AS

SELECTphysname, patient.name, illness.name,

prescription.drugcode

FROM physician, patient, treatment, illness, prescription

WHERE physician.physid = patient.physid

AND patient.patid = treatment.patid

AND treatment.illcode = illness.illcode

AND treatment.drugcode = prescription.drugcode

ORDER BY physname;


Sql

SQL

Database Queries

To run our view:

SELECT *

FROM drugs_given

WHERE physid = ‘123456789’

ORDER BY COUNT(prescription.drugname), patient.name;

This might again result in the (approximate) display:

physnamepatient.nameillness.nameprescription.drugname

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

SmithAikmanSore ArmThorazine

SmithAstaireBrk LegThorazine

SmithBeethovenDeafnessThorazine

SmithBradleyTallnessThorazine

Smith GoreDullnessThorazine

SmithBradleyTallnessAspirin

Smith GoreEuphoriaAspirin


Sql

SQL


  • Login