DOC PREVIEW
Penn CIT 597 - Structured Query Language

This preview shows page 1-2-14-15-29-30 out of 30 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 30 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 30 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 30 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 30 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 30 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 30 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 30 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

SQLSlide 2Java and SQLDatabasesExample tablePrimary KeysIntegrityDDL and DMLCREATE TABLECommon data typesExample table creationConstraintsALTER TABLEDROP TABLESELECTHow SELECT worksNames and stringsConditionsOperatorsINSERT INTOUPDATEDELETEJoins I: INNER JOINJoins II: LEFT JOINJoins III: RIGHT JOINMySQLJDBCJDBC example IJDBC example IIThe EndJan 14, 2019SQLSQLSQL is Structured Query LanguageSome people pronounce SQL as “sequel”Other people insist that only “ess-cue-ell” is the only correct pronunciationSQL is a language for accessing and updating databasesSQL is an ANSI (American National Standards Institute) standardJust about every relational database supports SQLMost also extend it in various incompatible waysJava and SQLAlthough SQL is a language in its own right, it can be used from within JavaHere’s the general outline:Get and install a database program that supports SQLMySQL is the most commonly used open source databaseOracle is the most commonly used commercial databaseInstall a driver that lets Java talk to your databaseFor example, MySQL Connector/Jimport javax.sql.*; to make the JDBC API availableJDBC used to stand for “Java DataBase Connectivity,” but no longer stands for anythingUse the JDBC API to talk to your databaseDatabasesA database contains one or more tablesEach table has a nameA table consists of rows and columnsA row is a record: it contains information about a single entity (such as a person)Columns have names that tell what kind of information is stored in that column (for example, “Address”)The information in a cell may be of various types: string, integer, floating point number, date, blank, etc.A value of null means the data for that cell is missingTwo null values are not considered to be equalPeopleFirst_NameLast_NameGender Age PhoneJohn Smith M 27 2-4315Sally Jones F 27 3-1542John White M 32 2-4315Mary Smith F 42 5-4321Example tablePeople is the name of the tableEach row is a recordEach cell in a column contains the same kind of informationIn this example, no single column contains unique information (there are two “John”s, etc.)Primary KeysWe will want to look things up in a tableTo do that, we need a way of choosing a particular rowA primary key is a column, or group of columns, whose values uniquely identify each rowExample: In the previous table, no single column could be used as a primary keyMultiple people had the same first name, same last name, same gender, same age, and same telephone numberNo two people had the same first name and last nameFirst_name and Last_name could be used as a primary keyIt’s a lot more convenient to have a single column as a primary keyIntegrityTables must follow certain integrity rules:No two rows may be completely identicalAny column that is a primary key, or part of a primary key, cannot contain null valuesThere are some other rules about arrays and repeating groups that need not concern us hereDDL and DMLSQL contains two kinds of “languages” (statement types)DDL is the Data Definition Language; it defines the structure of tablesCREATE TABLE -- creates a new database tableALTER TABLE -- alters (changes) a database tableDROP TABLE -- deletes a database tableDML is the Data Manipulation Language; it defines and manipulates the content of tablesINSERT -- puts new data into the databaseSELECT -- gets data from the databaseUPDATE -- updates (changes) data in the databaseDELETE -- removes data from the databaseCREATE TABLESyntax:CREATE TABLE table_name ( column_name data_type constraint, … , column_name data_type constraint );Names, such as the table_name and the column_names, are not quotedThe data_types will be described shortlyThe constraints are optionalNotice where there are commas (and where there aren’t)Common data typeschar(size)Fixed-length character string (maximum of 255 characters)varchar(size)Variable-length character string (maximum of size characters)number(size)Integer value (max size digits)number(size, d)Decimal number value; maximum of size digits total, with not more than d digits to the right of the decimaldateA calendar dateExample table creationCREATE TABLE People ( First_Name VARCHAR(12), Last_Name VARCHAR(25), Gender CHAR(1), Age NUMBER(3), Phone CHAR(6) );PeopleFirst_NameLast_NameGenderAge PhoneJohn Smith M 27 2-4315Sally Jones F 27 3-1542John White M 32 2-4315Mary Smith F 42 5-4321ConstraintsWhen a table is created, constraints can be put on the columnsunique -- no repeated values in this columnprimary key -- unique and used to choose rowsnot null -- must have a valueALTER TABLEALTER TABLE table_name ADD column_name datatypeAdds a column to the tableALTER TABLE table_name DROP COLUMN column_nameRemoves a column (and all its data) from the tableDROP COLUMN is not available on all SQL platformsDROP TABLESyntax:DROP TABLE table_name;Just deleting all the rows from a table leaves a “blank” table with column names and typesThe DROP TABLE command removes the table from the database completelySELECTSyntax:SELECT columns FROM table WHERE condition ;columns is: a comma-separated list of column names, or * to indicate “all columns” table is the name of the tablecondition is an optional condition to be satisfiedExamples:SELECT First_Name, Last_Name FROM People;SELECT * FROM People WHERE age < 40;How SELECT works SELECT First_Name, Last_Name FROM People WHERE Age > 30;PeopleFirst_NameLast_NameGenderAge PhoneJohn Smith M 27 2-4315Sally Jones F 27 3-1542John White M 32 2-4315Mary Smith F 42 5-4321John WhiteMary SmithResult:Names and stringsSQL keywords (such as SELECT) are case insensitive, but are traditionally written in all uppercase lettersTable names and column names may or may not be case sensitiveData values presumably are case sensitiveString data must be enclosed in single quotesConditions< Less than<= Less than or equal= Equal<> Not equal to ( != works on some databases)>= Greater than or equal> Greater thanLIKE String equality; % may be used as a wildcard… WHERE First_Name LIKE 'Jo%';matches Joe, John, Joanna, etc.AND, OR and NOT can be used with conditionsOperatorsBasic arithmetic operators are defined in SQL: + add - subtract


View Full Document

Penn CIT 597 - Structured Query Language

Documents in this Course
DOM

DOM

21 pages

More DOM

More DOM

11 pages

Rails

Rails

33 pages

DOM

DOM

21 pages

RELAX NG

RELAX NG

31 pages

RELAX NG

RELAX NG

31 pages

RELAX NG

RELAX NG

31 pages

RELAX NG

RELAX NG

31 pages

Rake

Rake

12 pages

Ruby

Ruby

58 pages

DOM

DOM

21 pages

Tomcat

Tomcat

16 pages

DOM

DOM

21 pages

Servlets

Servlets

29 pages

Logging

Logging

17 pages

Html

Html

27 pages

DOM

DOM

22 pages

RELAX NG

RELAX NG

30 pages

Servlets

Servlets

28 pages

XHTML

XHTML

13 pages

DOM

DOM

21 pages

DOM

DOM

21 pages

Servlets

Servlets

26 pages

More CSS

More CSS

18 pages

Servlets

Servlets

29 pages

Logging

Logging

17 pages

Load more
Download Structured Query Language
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Structured Query Language and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Structured Query Language 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?