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 SQLStructured 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 SQLA 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 RDBsThis led to ANSI adoption of SQL as an enhanced RDB language based on Sequel5History of SQL (1)2003SJSU – CmpE --- M.E. Fayad L3-S6 SQLStandardized 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 SQLSQL (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 SQLIngredients 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 SQLIngredients 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 SQLIngredients 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 SQLIngredients 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 SQLCREATE DATABASE database_name–Example•CREATE DATABASE library_catalog•CREATE DATABASE employeesCREATE 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 SQLCREATE 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 SQLDELETE 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 SQLINSERT INTO table_name [(column_list)] VALUES (value1, value2,
View Full Document