1 / 81

SQL

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).

sharon-carney
Télécharger la présentation

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Chapter 9: An Introduction to Structured Query Language (SQL) (With Considerable Modifications)

  2. 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)

  3. 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 ???

  4. 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

  5. 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

  6. 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 Fieldname CHAR(N) • Usage: • The length (N) MUST be specified • If less than N characters are entered, N spaces will STILL be allocated

  7. SQL Database Definition Characters VARCHAR • A string • Up to 255 characters are allowed fieldname VARCHAR(N) • Usage: • The length (N) MUST be specified • ONLY the numbercharacters are entered will be allocated space VARCHAR2, LONG VARCHAR

  8. 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: fieldname INTEGER SMALLINT Typically, up to 4 - 5 digits are allowed • Range (Typically): -32,768 through +32,767 • Usage: fieldname SMALLINT

  9. 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: fieldname DECIMAL(10,4) This allows for a number between 99999.9999 through -9999.999 (The sign counts) But ONLY to 3 decimal point of precision

  10. SQL Database Definition Date and Time Data Types DATE Date Value • (Typically) Displayed as mm/dd/yy • Usage: fieldname DATE TIME Time Value • (Typically) Displayed as hh:mm:ss • Useful in Time Stamping • Usage: fieldname TIME

  11. 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 physid CHAR(9) physname CHAR(30) specialty CHAR(15) street CHAR(20) city CHAR(20) state CHAR(2) zip CHAR(5)

  12. SQL Database Definition Before going on to SQL, Let’s see how other languages might create the record In COBOL: 01PHYSICIAN 05 PHYSID PIC X(9). 05 PHYSNAME PIC X(30). 05 SPECIALTY PIC X(15). 05 STREET PIC X(20). 05 CITY PIC X(20). 05STATE PIC X(2). 05ZIP PIC X(5).

  13. 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;

  14. 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 ( physid CHAR(9), physname CHAR(30), specialty CHAR(15), street CHAR(20), city CHAR(20), state CHAR(2), zip CHAR(5) ); Every SQL Command ends with a semicolon

  15. SQL Database Definition Let’s Examine the command • We have CREATEd a new table (file) called physician CREATE TABLE physician ( physid CHAR(9), physname CHAR(30), specialty CHAR(15), street CHAR(20), city CHAR(20), state CHAR(2), zip CHAR(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 ( , )

  16. 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)

  17. PhysID Name Specialty Street City State ZipCode SQL Database Definition Let’s rewrite the command: Physician Table Attributes/Fields CREATE TABLE physician ( physid CHAR(9) NOT NULL, physname CHAR(30) NOT NULL, Adding these Qualifiers will assure that the fields will NOT be left empty This is Superfluous. WHY ???

  18. PhysID Name Specialty Street City State ZipCode SQL Database Definition Let’s rewrite the command: Physician Table Attributes/Fields CREATE TABLE physician ( physid CHAR(9) NOT NULL, physname CHAR(30) NOT NULL, specialty CHAR(15), street CHAR(20), city CHAR(20), state CHAR(2), DEFAULT ‘TX’, If nothing is entered into the state field, TX will be entered by default

  19. PhysID Name Specialty Street City State ZipCode SQL Database Definition Let’s rewrite the command: Physician Table Attributes/Fields CREATE TABLE physician ( physid CHAR(9) NOT NULL, physname CHAR(30) NOT NULL, specialty CHAR(15), street CHAR(20), city CHAR(20), state CHAR(2), DEFAULT ‘TX’, zip CHAR(5), PRIMARY KEY (physid) ); physid has been identified as the primary key

  20. 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 ( physid CHAR(9) NOT NULL, physname CHAR(30) NOT NULL, specialty CHAR(15) NOT NULL, street CHAR(20), city CHAR(20), state CHAR(2), DEFAULT ‘TX’, zip CHAR(5), PRIMARY KEY (physid) UNIQUE (specialty) );

  21. 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

  22. 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

  23. SQL Database Definition A more appropriate CREATE might have been: CREATE SCHEMA ATHORIZATION pkirs CREATE TABLE physician ( physid CHAR(9), physname CHAR(30) NOT NULL, specialty CHAR(15) NOT NULL, street CHAR(20), city CHAR(20), state CHAR(2), DEFAULT ‘TX’, zip CHAR(5), PRIMARY KEY (physid) UNIQUE (specialty) );

  24. 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

  25. 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

  26. 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

  27. PatID Name …….. PhysID SQL Database Definition We now need to define our Patient fields: Patient Table Attributes/Fields CREATE TABLE patient ( patid CHAR(9) NOT NULL, name CHAR(30) NOT NULL, physid CHAR(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

  28. CREATE TABLE patient ( patid CHAR(9) NOT NULL, name CHAR(30) NOT NULL, physid CHAR(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 ( , )

  29. 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 ( physid CHAR(9) NOT NULL, physname CHAR(30) NOT NULL, specialty CHAR(15) NOT NULL, street CHAR(20), city CHAR(20), state CHAR(2), DEFAULT ‘TX’, zip CHAR(5), PRIMARY KEY (physid) UNIQUE (specialty) ); CREATE TABLE patient ( patid CHAR(9) NOT NULL, name CHAR(30) NOT NULL, physid CHAR(9) NOT NULL, PRIMARY KEY (patid), FOREIGN KEY (physid) REFERENCES physician (physid) );

  30. 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

  31. IllCode Name Illness Table . . . . . . SQL Database Definition First, let’s create our illness table: CREATE TABLE illness ( illcode CHAR(10), name CHAR(20) NOT NULL, PRIMARY KEY(illcode) UNIQUE(name)); Let’s Pretend that field Others doesn’t Exist

  32. Suffers Table Date/Time PatID IllCode . . . . . . . SQL Database Definition We now need to define our Suffers fields: CREATE TABLE suffers ( patid CHAR(9) NOT NULL, illcode CHAR(10) NOT NULL, sdate DATE NOT NULL, stime TIME NOT 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

  33. SQL Database Definition Our Script would appear as: CREATE SCHEMA ATHORIZATION pkirs CREATE TABLE physician ( physid CHAR(9) NOT NULL, physname CHAR(30) NOT NULL, specialty CHAR(15) NOT NULL, street CHAR(20), city CHAR(20), state CHAR(2), DEFAULT ‘TX’, zip CHAR(5), PRIMARY KEY (physid) UNIQUE (specialty) ); CREATE TABLE illness ( illcode CHAR(10) NOT NULL, name CHAR(20) NOT NULL, PRIMARY KEY(illcode) UNIQUE(name)); CREATE TABLE suffers ( patid CHAR(9), illcode CHAR(10) NOT NULL, sdate DATE NOT NULL, stime TIME NOT NULL, PRIMARY KEY(patid, illcode), FOREIGN KEY (patid) REFERENCES patient (patid), FOREIGN KEY (illcode) REFERENCES illness (illcode) ); CREATE TABLE patient ( patid CHAR(9) NOT NULL, name CHAR(30) NOT NULL, physid CHAR(9) NOT NULL, PRIMARY KEY (patid), FOREIGN KEY (physid) REFERENCES physician (physid) );

  34. 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 ( physid CHAR(9) , physname CHAR(30) NOT NULL, specialty CHAR(15), street CHAR(20), city CHAR(20), state CHAR(2), DEFAULT ‘TX’, zip CHAR(5), PRIMARY KEY (physid), CHECK(city in ‘Arlington’, ‘Dallas’, Ft. Worth’) );

  35. 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) );

  36. 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;

  37. 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

  38. 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: ( physid CHAR(9), physname CHAR(30), specialty CHAR(15), street CHAR(20), city CHAR(20), state CHAR(2), zip CHAR(5) );

  39. 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

  40. 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

  41. 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)

  42. 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

  43. 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)

  44. 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)

  45. 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

  46. 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

  47. 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;

  48. SQL Database Structure Modification Altering the database • Suppose that we find out that we need more space for our specialty field, presently: specialty CHAR(15), • We might enter the command: ALTER TABLE physician, MODIFY (specialty CHAR(20)); • which would accept up to 20 characters for a physician specialty

  49. 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

  50. 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

More Related