DOC PREVIEW
SJSU CMPE 226 - CmpE226-DB-L03n-SQL

This preview shows page 1-2-19-20 out of 20 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 20 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 20 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 20 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 20 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 20 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Database DesignPowerPoint PresentationSlide 3What Is SQL?History of SQL (1)History of SQL (2)Syntax Conventions: Backus-Naur FormBackus-Naur Form (1)Backus-Naur Form (2)Backus-Naur Form (3)Backus-Naur Form (4)Examples of SQL Commands (1)Examples of SQL Commands (2)Examples of SQL Commands (3)Examples of SQL Commands (4)Examples of SQL Commands (5)Examples of SQL Commands (6)Examples of SQL Commands (7)The World is Full of FilesDiscussion Questions2003SJSU -- CmpE L3-S1 SQLDatabase Design Dr. M.E. Fayad, ProfessorComputer Engineering Department, Room #283I College of EngineeringSan José State UniversityOne Washington SquareSan José, CA 95192-0180 http://www.engr.sjsu.edu/~fayad2003SJSU – CmpE --- M.E. Fayad L3-S2 SQL2Lesson 3 – II:Structured Query Language (SQL)2003SJSU – CmpE --- M.E. Fayad L3-S3 SQL Lesson ObjectivesObjectives3 Explore the history of SQL Learn how to write queries using SQL2003SJSU – CmpE --- M.E. Fayad L3-S4 SQLStructured Query Language–A widely used standard set of commands and syntax for doing things with RDBMSs–Used especially for query and retrieval–Includes commands for defining RDBs, conducting transactions, storing data, etc.–Implemented in all major RDBMSs–SQL does not handle all the practical details involved in using a RDB. Hence, every RDBMS has additional features (some of which may form part of an extension of SQL for that particular RDBMS).4What Is SQL?2003SJSU – CmpE --- M.E. Fayad L3-S5 SQLA prototype DB query and retrieval language was developed by IBM in the early 1970s as "Sequel"Other RDB developers and users recognized the advantages of having a standard language for manipulating RDBsThis led to ANSI adoption of SQL as an enhanced RDB language based on Sequel5History of SQL (1)2003SJSU – CmpE --- M.E. Fayad L3-S6 SQLStandardized Versions of SQL–Work toward an ANSI standard version started in 1983–Further drafts in 1986 and 1988 led to SQL-1 in 1989–SQL-2, adopted as an ANSI and ISO standard in 1992, is the version generally used at present–The latest ANSI-ISO standard, 1998-1999, SQL-3, has not yet been widely implemented. •SQL-3 contains new features that help in the construction of "object-relational" databases, i.e., RDBs that can handle objects in the technical sense: viz., sets of data together with program code that operates on the data.6History of SQL (2)2003SJSU – CmpE --- M.E. Fayad L3-S7 SQLSQL (like other computer languages) is formally defined in a notation called "Backus-Naur Form"History of Backus-Naur Form (BNF)–In 1959 John Backus of IBM devised Backus Normal Form as a concise notation for describing components of a programming language (Algol 58, the first high-level programming language)–In 1960 Peter Naur, a Danish programmer, refined Backus's notation–Result is Backus-Naur Form (the preferred name for for the notation in current use, though this is still usually called Backus Normal Form)Use of BNF–Backus-Naur Form makes it possible to define SQL (and other programming languages) concisely7Syntax Conventions: Backus-Naur Form2003SJSU – CmpE --- M.E. Fayad L3-S8 SQLIngredients of BNF–When parentheses "()" are provided in the definition of a command, this tells you that you must use the parentheses, as shown, when you write out the command.–| (vertical bar) means an "exclusive or"; that is, "either but not both" or "any one but not all"–"," (comma) means a "nonexclusive or"; that is, "none, or one, or more than one"–"..." (ellipses) mean that you may repeat the previous item as many times as you wish 8Backus-Naur Form (1)2003SJSU – CmpE --- M.E. Fayad L3-S9 SQLIngredients of BNF (cont'd)– "<>" (angle brackets) are used to contain a generic name for a type of item; they mean that you are to replace them and their contents by the actual name of the item named generically between the "<>"•Example: "SELECT <tablename>" means that if, say, your RDB contains a table named "students" and you want to perform a select operation on this table, you should write SELECT students–Note: When confusion is unlikely to occur, angle brackets are often omitted in definitions of SQL commands; i.e., you will often see statements like "SELECT tablename"9Backus-Naur Form (2)2003SJSU – CmpE --- M.E. Fayad L3-S10 SQLIngredients of BNF (cont'd)–{} (braces) mean you must choose at least one of the enclosed choices, which may be separated by "|" or "," (i.e., by an "exclusive or" or a "non-exclusive or")–Examples •{a|b|c} means you must choose exactly one of the choices•{a,b,c} means you must choose one or more of the choices10Backus-Naur Form (3)2003SJSU – CmpE --- M.E. Fayad L3-S11 SQLIngredients of BNF (cont'd)–[] (brackets) mean that you may choose one or more of the enclosed choices, which may be separated by "|" or ","–Examples•[a|b|c] means that you may choose at most one of the choices•[a,b,c] means that you may choose none, one, or several of the choices11Backus-Naur Form (4)2003SJSU – CmpE --- M.E. Fayad L3-S12 SQLCREATE DATABASE database_name–Example•CREATE DATABASE library_catalog•CREATE DATABASE employeesCREATE INDEX index_name ON table_name (column_name [, column_name]...)–Examples•CREATE INDEX publisher_ndx ON monographs (pub_name)•CREATE INDEX name_ndx ON employee_names (emp_lname, emp_fname)–Note: This second example shows the creation of a composite index.12Examples of SQL Commands (1)2003SJSU – CmpE --- M.E. Fayad L3-S13 SQLCREATE TABLE table_name (column_name datatype [NULL | NOT NULL] [, column_name datatype [NULL | NOT NULL] ]...) –Examples•CREATE TABLE employee_names (SSN CHAR(11) NOT NULL, emp_lname CHAR(20), emp_fname CHAR(15), emp_bdate DATE(mm"/"dd"/"yy))•CREATE TABLE cataloging_staff (catlgr_fname CHAR(15), catlgr_lname CHAR(20), workstation_num CHAR(3)) 13Examples of SQL Commands (2)2003SJSU – CmpE --- M.E. Fayad L3-S14 SQLDELETE FROM table_name WHERE select_statement–Examples•DELETE FROM employee_names WHERE SSN = "123-45-6789"•DELETE FROM monographs WHERE copyright_date LESS THAN 1910 AND WHERE language = "Dutch"14Examples of SQL Commands (3)2003SJSU – CmpE --- M.E. Fayad L3-S15 SQLINSERT INTO table_name [(column_list)] VALUES (value1, value2,


View Full Document

SJSU CMPE 226 - CmpE226-DB-L03n-SQL

Documents in this Course
SQL-99

SQL-99

71 pages

XML

XML

52 pages

XML

XML

14 pages

Chapter 9

Chapter 9

45 pages

Load more
Download CmpE226-DB-L03n-SQL
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 CmpE226-DB-L03n-SQL 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 CmpE226-DB-L03n-SQL 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?