LU03 Notes - DbWiki



IST459: Introduction to SQLTable of Contents TOC \o "1-3" \h \z \u Topic - Introduction to SQL PAGEREF _Toc251784382 \h 2Learning Objectives PAGEREF _Toc251784383 \h 2SQL Goals PAGEREF _Toc251784384 \h 2Part 1: SQL: What it is and what it’s not. PAGEREF _Toc251784385 \h 2SQL: Tow-may-tow, Tow-mah-tow? PAGEREF _Toc251784386 \h 3SQL: Not really a programming language per-se PAGEREF _Toc251784387 \h 3SQL: The Not-So-Standard, Standard PAGEREF _Toc251784388 \h 3ANSI SQL Standards PAGEREF _Toc251784389 \h 4Popular DBMS Software and Their SQL Implementations PAGEREF _Toc251784390 \h 4A word about SQL Implementation PAGEREF _Toc251784391 \h 4Part 2: SQL Language Internals PAGEREF _Toc251784392 \h 4Case Sensitivity PAGEREF _Toc251784393 \h 4Naming conventions PAGEREF _Toc251784394 \h 5Comments PAGEREF _Toc251784395 \h 5Data Definition Language (DDL) PAGEREF _Toc251784396 \h 6Data Manipulation Language (DML) PAGEREF _Toc251784397 \h 6Data Control Language (DCL) PAGEREF _Toc251784398 \h 6Data Transaction Language (DTL) PAGEREF _Toc251784399 \h 6Part 3: Syntax of SQL commands PAGEREF _Toc251784400 \h 6Table Creation PAGEREF _Toc251784401 \h 6SQL Data Types on Microsoft SQL Server PAGEREF _Toc251784402 \h 7Table Manipulation PAGEREF _Toc251784403 \h 7Table Removal PAGEREF _Toc251784404 \h 7Add data to a table PAGEREF _Toc251784405 \h 7Read data from a table PAGEREF _Toc251784406 \h 8Update data in a table PAGEREF _Toc251784407 \h 8Remove data from a table PAGEREF _Toc251784408 \h 8SQL Operators PAGEREF _Toc251784409 \h 8Appendix: Other important things to know PAGEREF _Toc251784410 \h 9dbo? Oh my! PAGEREF _Toc251784411 \h 9INFORMATION_SCHEMA PAGEREF _Toc251784412 \h 9Topic - Introduction to SQL HYPERLINK "" \l "Learning_Unit_AD_?€“_Introduction_to_SQ" \o "Link to this Section" ?Table of Contents [Hide/Show]Learning Unit 03 ?€“ Introduction to SQL???Learning Objectives??????SQL Goals???Part 1: SQL: What it is and what its not.??????SQL: Tow-may-tow, Tow-mah-tow???????SQL: Not really a ?€?programming language?€? per-se??????SQL: It?€?s the ?€?not-so-standard?€? standard.??????ANSI SQL Standards??????Popular DBMS Software and Their SQL Implementations??????A word about SQL Implementation???Part 2: SQL Language Internals??????Case Sensitivity??????Naming conventions??????Comments??????Data Definition Language (DDL)??????Data Manipulation Language (DML)??????Data Control Language (DCL)??????Data Transaction Language (DTL)???Syntax of SQL commands??????Table Creation??????SQL Data Types on Microsoft SQL Server??????Table Manipulation??????Table Removal??????Add data to a table??????Read data from a table??????Update data in a table??????Remove data from a table??????SQL Operators???Other important things to know??????dbo? Oh my!??????INFORMATION SCHEMALearning Objectives HYPERLINK "" \l "Learning_Objectives_25" \o "Link to this Section" ?In this learning unit we will explore SQL, the database language used to implement the relational model in popular DBMS products. Upon completion of this learning unit you should be able to:Describe and use basic SQL commandsExplain how and why SQL is used, and why its importantCompare and contrast DML and DDLUse SQL commands to create metadata structures and perform CRUD operations.SQL Goals HYPERLINK "" \l "SQL_Goals_0" \o "Link to this Section" ?Our SQL goals for this learning unit are to:Understand how to manipulate tables with the CREATE TABLE, ALTER TABLE, and DROP TABLE commandsUnderstand basic use of the “fantastic four” DML commands: INSERT, SELECT, UPDATE and DELETEPart 1: SQL: What it is and what it’s not. HYPERLINK "" \l "Part_B:_SQL:_What_it_is_and_what_its_no" \o "Link to this Section" ?SQL is a popular computer language for data and meta-data management in a relational DBMS. With SQL you can create database structures, such as tables, constraints, and indexes and then populate the structures with data programmatically. For the most part, SQL is a comprehensive language for controlling and manipulating all aspects of the DBMS. As part of the implementation model, SQL is used to create the internal and external models from a logical model. So if the conceptual model represents the ideas for a database, and the logical model represents the actual blueprint for it, then SQL is the tool used to build the actual database implementation of the SDLC (aka internal and external models). SQL: Tow-may-tow, Tow-mah-tow? HYPERLINK "" \l "SQL:_Tow-may-tow_Tow-mah-tow_1" \o "Link to this Section" ?First, is SQL pronounced “Ess-Queue-Elle” or “Sequel”? According to ANSI () the pronunciation it is the former, S-Q-L, but I’ve heard both used both ways in practice. I suggest just accepting there is no right or wrong way to pronounce it, and just move on. SQL: Not really a programming language per-seSQL as a programming language is much different from C, Java or Visual Basic. The latter are general purpose procedural programming languages and you can write almost anything in them. SQL is a set-based declarative computer language that is domain specific. Declarative means you focus on “what you need to do” rather than on “how you need to do it”. Domain specific implies the language solves a focused set of problems. SQL is notoriously easily to learn, yet difficult to master. This is most likely because the language is not well suited to solving more complicated problems. Accomplishing some tasks in SQL are so downright trivial you’ll wonder if you’re even “programming”, while others require you re-learn what you may already know and understand about computer programming in order to accomplish the task. Most often you need to re-think your logic to operate on sets of data versus the individual items of data. SQL: The Not-So-Standard, Standard HYPERLINK "" \l "SQL:_It?€?s_the_?€?not-so-standard?€?_s" \o "Link to this Section" ?SQL was developed by Donald D. Chamberlin and Raymond F. Boyce at IBM in the 1970’s. Their concept was based upon E. F. Codd’s paper on relational design, which we introduced earlier on. SQL was adopted as a standard by ANSI in 1986, however by that time several DBMS implementations were already available, notably ones from IBM and Oracle. Since that date SQL has always been driven by the market leaders as opposed to the actual standard. In addition, customers with production databases implemented in their DBMS of choice are often reluctant to change database design to conform to the standard, forcing vendors to maintain backwards compatibility. DBMS vendors are in direct competition with each other for your DBMS dollars, and they know once you select a product your odds of switching low. As such, standards adherence takes a back seat to adding more whiz-bang features, providing stability and good performance. The end result is a not-so-standard standard.ANSI SQL Standards HYPERLINK "" \l "ANSI_SQL_Standards_4" \o "Link to this Section" ?YearNameComments 1986SQL-86First Standard, also called SQL-87 1989SQL-89 (SQL1)Minor revision, the baseline of SQL. 1992SQL-92 (SQL2)New Join syntax added 1999SQL-99 (SQL3)Triggers, object-oriented features 2003SQL:2003XML support, Windows functions, cursors, identity columns 2006SQL:2006More XML support, XQuery support Popular DBMS Software and Their SQL Implementations HYPERLINK "" \l "Popular_DBMS_Software_and_Their_SQL_Imp" \o "Link to this Section" ?DBMS NameMaintainerLicense Model Oracle Database (10g)OracleProprietaryDB2IBMProprietarySQL Server 2008MicrosoftProprietary MySQL 5Sun / MySQL ABOpen-Source PostgreSQL PostgreSQL GDGOpen Source FirebirdFirebird FoundationOpen Source A word about SQL Implementation HYPERLINK "" \l "A_word_about_SQL_Implementation_6" \o "Link to this Section" ?In this course, we will use Microsoft SQL server as our primary DBMS for learning about SQL and the internal and external data models. It is important to understand that while we use MS SQL server for our examples, the focus of this course is not SQL server training, but database design and management principles in general. To get the most mileage out of our learning, I will make sure to differentiate between those items that are specific to Microsoft SQL server and those which are part of the SQL standard. In addition, for any proprietary information, we will discuss how it is implemented in other DBMS products.Part 2: SQL Language Internals HYPERLINK "" \l "Part_C:_SQL_Language_Internals_27" \o "Link to this Section" ?Case Sensitivity HYPERLINK "" \l "Case_Sensitivity_7" \o "Link to this Section" ?In SQL keywords are case insensitive, thus the SELECT, SelecT and select commands are all the same command. However, the data and the object definitions and data within the database can be set to either case-sensitive or case-insensitive based on the default collation on the database. In a case-sensitive collation, tables named Employee and employee are two separate tables! Yikes! It goes without saying that caution must be exercised, which is why you need:Naming conventions HYPERLINK "" \l "Naming_conventions_8" \o "Link to this Section" ?Naming conventions or “coding style” are a set of rules for naming programming objects. In SQL, we use naming conventions to identify tables, columns, constraints and other objects. While not actually a part of SQL itself, naming conventions become imperative as database designs get complex and there are multiple developers with hands in the pot. Many a battle has been waged in the corporate, open source and internet communities over naming conventions, especially over the use of Hungarian Notation. MY OPINION OF NAMING CONVENTIONS IS THAT ANY ONE IS GOOD AS LONG AS YOU ADOPT AND ADHERE TO IT. THE POINT OF NAMING CONVENTIONS IS STANDARDIZATION FOR CONSISTENCY AND READABILITY. That being said here are some naming conventions I use in SQL:WhatThe Fudge ConventionRationale for use All object namesUse lower case letters onlyDisambiguation e.g. Employee vs. employeeAll object namesUse underscore in place of SPACEAvoids the need to place brackets around identifiers. TablesPluralizeSince a table contains many 'things' it should be plural eg. Employees Tablesqualify with logical schemaWhen learning SQL, helps disambiguate different tables in the same database eg. fudgemart_customers vs. fudgeflix_customersColumn namesQualify with table nameWhen learning SQL, helps you define scope vendor_zipcode vs. employee_zipcodeconstraints pk=primary key, fk=foreign key u=unique, ck=check, i=indexDisambiguation of constraints ck_vendor_zipcode vs. fk_vendor_zipcodeComments HYPERLINK "" \l "Comments_9" \o "Link to this Section" ?The ability to document what we do is truly what separates us from the animals, so use it. To place a comment in-line, use double-dashes - -, to place a multi-line comment use /* */. SQL Comments are ignored by the SQL interpreter, but useful to people! You may even find them useful later on to yourself if you need to correct your own work or find an error.For example:-- This is a one line comment/* This commentspans several lines */Data Definition Language (DDL) HYPERLINK "" \l "Data_Definition_Language_DDL_10" \o "Link to this Section" ?DDL commands are used to create, modify and delete the meta-data structures of the DBMS, such as tables and indexes. There are three main DDL commands: CREATE - make a new objectALTER - manipulate an existing objectDROP - delete an existing object, such as a table.Data Manipulation Language (DML) HYPERLINK "" \l "Data_Manipulation_Language_DML_11" \o "Link to this Section" ?DML commands manage the data in the tables. There are 4 DML commands, corresponding to each of the 4 CRUD operations:INSERT INTO - creates or adds data into a table.SELECT - reads data from a tableUPDATE- updates data in a tableDELETE- removes data from a table.Data Control Language (DCL) HYPERLINK "" \l "Data_Control_Language_DCL_12" \o "Link to this Section" ?The DCL commands process the authorization aspects of the database (who has access to what). We will learn more about DCL in the database security learning unit.GRANT - Add rights.REVOKE - Remove rightsData Transaction Language (DTL) HYPERLINK "" \l "Data_Transaction_Language_DTL_13" \o "Link to this Section" ?The DTL commands allow the user to programmatically control and manage how data is manipulated in the database. We will learn more about DTL and transactions in general during the database transactions and concurrency control learning unit. Part 3: Syntax of SQL commands HYPERLINK "" \l "Syntax_of_SQL_commands_28" \o "Link to this Section" ?Table Creation HYPERLINK "" \l "Table_Creation_14" \o "Link to this Section" ?CREATE TABLE tablename ( colname datatype [NOT] NULL ,...n ,CONSTRAINT name PRIMARY KEY(pkcolname, ..n) ,CONSTRAINT name CHECK (expression), ,CONSTRAINT name UNIQUE(ucolname, ...n), ,CONSTRAINT name FOREIGN KEY(fkcolname, ...n) REFERENCES fktablename(pkcolHYPERLINK " %e2%80%a6n.ashx" \o ", ?€?n", ...n)])SQL Data Types on Microsoft SQL Server HYPERLINK "" \l "SQL_Data_Types_on_Microsoft_SQL_Server_" \o "Link to this Section" ?Data TypeSQL Std?Description char(length)YFixed-length character string of size length varchar(length)YVariable-length character string of size lengthtextYVariable-length character of up to 2GB, not indexablenumeric(precision,scale)YFixed-length character string of size lengthdecimal(precision,scale)YSame as numericsmallintY16-bit integer (2 bytes)intY32-bit integer (4 bytes)bigintY64-bit integer (8 bytes)floatYfloating-point decimalbitYOne bit. Used for true/false and yes/no valuesuniqueidentifierNA globally-unique 16-byte idenfierrowversionNA timestamp used for replicationmoneyNsynonym for decimal(18,4)For a complete list of data types available in Microsoft SQL Server including samples of their usage, visit the SQL Server books online: Manipulation HYPERLINK "" \l "Table_Manipulation_16" \o "Link to this Section" ?ALTER TABLE tablename {ADD colname datatype colsettings}| {ADD CONSTRAINT constraintinfo}| {ALTER COLUMN colname colsettings}| {DROP COLUMN|CONSTRAINT name}Table Removal HYPERLINK "" \l "Table_Removal_17" \o "Link to this Section" ?DROP TABLE tablenameAdd data to a table HYPERLINK "" \l "Add_data_to_a_table_18" \o "Link to this Section" ?INSERT INTO tablename (colname, ...n)VALUES (data , ...n)Read data from a table HYPERLINK "" \l "Read_data_from_a_table_19" \o "Link to this Section" ?SELECT {colname HYPERLINK ", ..n" \o ", ..n", ..n | * } FROM tablename WHERE conditionORDER BY colname [, ..n]Update data in a table HYPERLINK "" \l "Update_data_in_a_table_20" \o "Link to this Section" ?UPDATE tablename SET colname = data , ..nWHERE conditionRemove data from a table HYPERLINK "" \l "Remove_data_from_a_table_21" \o "Link to this Section" ?DELETE FROM tablename WHERE conditionSQL Operators HYPERLINK "" \l "SQL_Operators_22" \o "Link to this Section" ?These operators are used to test conditions for the WHERE clause and CHECK constraintsOperatorTypePurposeExample Usage +ArithmeticAddition, string concatenationa+b -ArithmeticSubtractiona-b*ArithmeticMultiplicationa*b/ArithmeticDivisiona/b >ComparativeGreater thana>b<ComparativeLess thana<b=ComparativeEqual toa=b<>ComparativeNot equal toa<>b>=ComparativeGreater than or equal toa>=b<=ComparativeLess than or equal toa<=bbetweenComparativeRange checkinga between c and dis nullNullCheck for nulla is nullis not nullNullCheck for not nulla is not nullANDBooleanLogical AND - both must be truea AND bORBooleanLogical OR - one must be truea OR b NOTBooleanNegationNOT aexistsSetcheck for existenceexists SQLinSetcheck for membershipa in (1,2,3)( )Misc.Order of operations(a > b) OR (c < d) For a complete list of SQL operators supported by Microsoft SQL Server, visit this section of the SQL Server books online: : Other important things to know HYPERLINK "" \l "Other_important_things_to_know_29" \o "Link to this Section" ?dbo? Oh my! HYPERLINK "" \l "dbo_Oh_my!_23" \o "Link to this Section" ?In Microsoft SQL Server, each DDL object can be prefixed with a name followed by a period. This represents the owner of the object, and by default is the owner dbo or database owner. You can actually have two object names in a database with different owners, such as mafudge450.employees and dbo.employees, which would be considered different objects! Oh my! INFORMATION_SCHEMA HYPERLINK "" \l "INFORMATION_SCHEMA_24" \o "Link to this Section" ?The question you might be asking yourself at this point is if there?€?s a way to list the tables in a database or list the columns in a table? Well, every DBMS implements the meta-data in its own internal structures of tables, known as system tables. Yes, this is the ultimate in dogfooding. You can query the system tables using the INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS system objects.Bottom of Form ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download