Movatterモバイル変換

2 December 2005Introduction to DatabasesStructured Query LanguageProf. Beat SignerDepartment of Computer ScienceVrije Universiteit Brusselhttp://www.beatsigner.com

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 2March 17, 2017Context of Today's LectureAccessMethodsSystemBuffersAuthorisationControlIntegrityCheckerCommandProcessorProgramObject CodeDDLCompilerFileManagerBufferManagerRecoveryManagerSchedulerQueryOptimiserTransactionManagerQueryCompilerQueriesCatalogueManagerDMLPreprocessorDatabaseSchemaApplicationProgramsDatabase andSystem CatalogueDatabaseManagerDataManagerDBMSProgrammers Users DB AdminsBased on 'Components of a DBMS', Database Systems,T. Connolly and C. Begg, Addison-Wesley 2010

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 3March 17, 2017Structured Query Language (SQL) Declarative query language to create database schemas,insert, update, delete and query information based on adata definition and data manipulation language Data definition language (DDL) definition of database structure (relation schemas) data access control Data manipulation language (DML) query language to create, read, update and delete tuples(CRUD operations)

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 4March 17, 2017Structured Query Language (SQL) ... The SQL language further deals with the following issues transaction control integrity constraints (DDL) auhorisation (DDL) views (DDL) embedded SQL and dynamic SQL

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 5March 17, 2017 SEQUEL (70's) structured english query language developed by Raymond F. Boyceand Donald D. Chamberlin access data stored in IBM'sSystem R relational database SQL-86 first ANSI standard version SQL-89 / SQL 1 SQL-92 / SQL 2 we will mainly discuss features of the SQL-92 standardHistory of SQLDonald D. Chamberlin Raymond F. Boyce

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 6March 17, 2017History of SQL ... SQL:1999 / SQL 3 recursive queries, triggers, object-oriented features, ... SQL:2003 window functions, XML-related features, ... SQL:2006 XML Query Language (XQuery) support, ... SQL:2008 SQL:2011 improved support for temporal databases

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 7March 17, 2017SQL "Standard" Each specific SQL implementation by a database vendoris called a dialect The vendors implement parts of the SQL standard(e.g. most implement SQL-92) but add their vendor-specific extensions Most relational database vendors conform to a set ofCore SQL features but portability might still be limiteddue to missing or additional features

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 8March 17, 2017Data Definition Language (DDL) The data definition language (DDL) is used to specify therelation schemas as well as other information about therelations relation schemas attribute domain types integrity constraints relation indexes access information physical storage structure of relations

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 9March 17, 2017Database Creation The concrete process of creating a new database mightdiffer for different relational database products According to the SQL standard, an SQL environmentcontains one or more catalogues Each catalogue manages various metadata set of schemas consisting of- relations/tables- views- assertions- indexes SET SCHEMA name can be used toset the current schema users and user groupsenvironmentcatalogue catalogueschemaschema schema

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 10March 17, 2017Database Creation ... The creation of catalogues is not covered by the SQLstandard and therefore implementation specific Schemas can be created and deleted via the CREATE andDROP statements The default parameter of the DROP SCHEMA statement isRESTRICT only empty schema can be deleted If CASCADE is specified, all objects associated with theschema will be droppedcreateSchema = "CREATE SCHEMA" , name , "AUTHORIZATION" , creator ,[ ddlStatements ];dropSchema = "DROP SCHEMA" , name , [ "RESTRICT" | "CASCADE" ];

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 11March 17, 2017Extended Backus-Naur Form (EBNF) Notation to describe computer program-ming languages (context-free grammars) developed by Niklaus WirthNotation Meaning= Definition, Sequence; Termination| Choice[...] Option{...} Repetition(...) Grouping"..." Terminal StringNiklaus Wirth We use the EBNFto describe differentSQL conceptshttp://en.wikipedia.org/wiki/Extended_Backus-Naur_Form

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 12March 17, 2017Relational Database ExamplecustomerID name street postcode city1 Max Frisch Bahnhofstrasse 7 8001 Zurich2 Eddy Merckx Pleinlaan 25 1050 Brussels5 Claude Debussy 12 Rue Louise 75008 Paris53 Albert Einstein Bergstrasse 18 8037 Zurich8 Max Frisch ETH Zentrum 8092 ZurichcdID name duration price year1 Falling into Place 2007 17.90 20072 Carcassonne 3156 15.50 19933 Chromatic 3012 16.50 1993customercd

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 13March 17, 2017Relational Database Example ...supplierID name postcode city5 Max Frisch 8037 Zurich2 Mario Botta 6901 LuganoorderID customerID cdID date amount status1 53 2 13.02.2010 2 open2 2 1 15.02.2010 1 deliveredordersupplierCustomer (customerID, name, street, postcode, city)CD (cdID, name, duration, price, year)Order (orderId, customerID, cdID, date, amount, status)Supplier (supplierID, name, postcode, city)relational database schema

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 14March 17, 2017Table Definition ExampleCREATE TABLE Customer (customerID INTEGER CHECK (customerID > 0) PRIMARY KEY,name VARCHAR(30) NOT NULL,street VARCHAR(30) NOT NULL,postcode SMALLINT CHECK (postcode > 0),city VARCHAR(20));CREATE TABLE CD (cdID INTEGER PRIMARY KEY,name VARCHAR(30) NOT NULL,duration TIME,price NUMERIC(6,2),year SMALLINT);

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 15March 17, 2017Table Definition Example ...CREATE TABLE Supplier (supplierID INTEGER PRIMARY KEY,name VARCHAR(30) NOT NULL,postcode SMALLINT CHECK (postcode > 0),city VARCHAR(20));CREATE TABLE Order (orderID INTEGER CHECK (orderID > 0) PRIMARY KEY,customerID INTEGER,cdID INTEGER ,date DATE,amount INTEGER,Status VARCHAR(20) NOT NULL DEFAULT 'open',UNIQUE (customerID, cdID, date),FOREIGN KEY (customerID) REFERENCES Customer(customerID)ON UPDATE CASCADE ON DELETE SET NULL,FOREIGN KEY (cdID) REFERENCES CD(cdID)ON UPDATE CASCADE);

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 16March 17, 2017Table Constraints We can have only one PRIMARY KEY constraint butmultiple UNIQUE constraints if no primary key is defined, duplicates are allowed (bag) Referential integrity a foreign key always has to have a matching value in thereferenced table (or it can be null) different referential actions can be defined for update (ON UPDATE)and delete (ON DELETE) operations on the referenced candidatekey- CASCADE: propagate operations to the foreign keys which might lead to furthercascaded operations- SET DEFAULT: set the foreign keys to their default value- SET NULL: set the foreign keys to NULL- NO ACTION: the operation on the candidate key will be rejected (default)

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 17March 17, 2017Table DefinitioncreateTable = "CREATE TABLE" , table , "(" ,( columnElement | tableConstraint ) ,{ "," , ( columnElement | tableConstraint ) } , ")";columnElement = column , datatype ,[ "DEFAULT" , ( value | "NULL" ) ] , { columnConstraint };columnConstraint = "NOT NULL" | "UNIQUE" | "PRIMARY KEY" |( "REFERENCES" , table , [ "(" , column , ")" ] ,{ referentialAction } ) |( "CHECK (" , searchCondition , ")" );tableConstraint = ( ( "UNIQUE" | "PRIMARY KEY ) , "(" , column ,{ "," , column } , ")" ) |( "FOREIGN KEY (" , column , { "," , column } , ")" ,"REFERENCES" , table , [ "(" , column , { "," , column } , ")" ] ,{ referentialAction } ) |( "CHECK (" , searchCondition , ")" );

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 18March 17, 2017Table Definition ...referentialAction = ( "ON UPDATE" | "ON DELETE" ) ,( "CASCADE" | "SET DEFAULT" | "SET NULL" | "NO ACTION" );

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 19March 17, 2017SQL Datatypes Character data fixed-length or variable-length sequence of characters optional multibyte character sets (e.g. for Japanese etc.) Large character data or binary data often a so-called locator is returned to access a large object inpieces instead of loading the entire object into memorychar = fixedChar | varyingChar [charSet];fixedChar = "CHAR" , [ "(" , length , ")" ];varyingChar = "VARCHAR" , [ "(" , maxLength , ")" ];charSet = "CHARACTER SET" charSetName;lob = clob | blob;clob = "CLOB" , [ "(" , size , ")" ];blob = "BLOB" , [ "(" , size , ")" ];

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 20March 17, 2017SQL Datatypes ... Numeric data The DECIMAL datatype is sometimes used as a synonymfor the NUMERIC datatypenumeric = decimal | int | smallInt | float | real | double;decimal = "DECIMAL" , [ "(" , precision , [ "," , scale ] , ")" ];int = "INTEGER";smallInt = "SMALLINT";float = "FLOAT" , [ "(" , precision , ")" ];real = "REAL";double = "DOUBLE PRECISION";

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 21March 17, 2017SQL Datatypes ... Datetime data Format of the datetime values date: YYYY-MM-DD time: hh:mm:ss.p ± hh:mm timestamp: YYYY-MM-DD hh:mm:ss.p ± hh:mmdatetime = date | time | timestamp;date = "DATE";time = "TIME" , [ "(" , precision , ")" ] ,[ "WITH TIME ZONE" , timezone ];timestamp = "TIMESTAMP" , [ "(" , precision , ")" ] ,[ "WITH TIME ZONE" , timezone ];

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 22March 17, 2017SQL Datatypes ... Boolean the domain of boolean values consist of the two truth values TRUEand FALSE a thrid UNKNOWN truth value is used to represent NULL values introduced in SQL:1999 Bit data fixed or varying sequence of binary digits (0 or 1)boolean = "BOOLEAN";bit = fixedBit | varyingBit;fixedBit = "BIT" , [ "(" , length , ")" ];varyingBit = "BIT VARYING" , [ "(" , maxLength , ")" ];

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 23March 17, 2017SQL Datatypes ... For further details about the presented datatypes as wellas information about vendor-specific datatypes one hasto consult the specific database manualsdatatype = char | lob | numeric | datetime | boolean | bit;

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 24March 17, 2017Data Manipulation After a table has been created, we can use the INSERTcommand to add tuples unspecified attribute values are set to the default value or NULL attribute order can be changed via optional column names "bulk loader" utilities to insert large amounts of tuples ExampleINSERT INTO Customer VALUES(8,'Max Frisch','ETH Zentrum', 8001, 'Zurich');insert = "INSERT INTO" , table ,[ "(" , column , { "," , column } , ")" ] ,( "VALUES (" , expr , { "," , expr } , ")" ) | ( "(" , query , ")" );

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 25March 17, 2017Expressionsexpr = exprElement { ( "+" | "-" | "*" | "/" ) , exprElement };exprElement = column | value |"COUNT" , "(" ( "*" | ( [ "ALL" | "DISTINCT" ] , column ) , ")" |( "MIN" | "MAX" ) , "(" , expr , ")" |( "SUM" | "AVG" ) , "(" , [ "DISTINCT" ] , expr , ")";

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 26March 17, 2017Data Manipulation ... The DELETE statement can be used to delete tuples Tuples can be updated via the UPDATE statement ExampleUPDATE Customer SET name = 'Walter Faber' WHERE customerID = 8;update = "UPDATE" , table , "SET" ,column , "=" , ( "NULL" | expr | "(" , query , ")" ) ,{ "," , column , "=" , ("NULL" | expr | "(" , query , ")" ) } ,[ "WHERE" , searchCondition ];delete = "DELETE FROM" , table [ "WHERE" , searchCondition ];

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 27March 17, 2017Data Manipulation ... The DROP TABLE statement can be used to delete arelation from the database A relation schema can be modified via the ALTER TABLEcommand existing tuples are assigned a NULL value for the new attribute ExamplealterTable = "ALTER TABLE" , table , "ADD" ,( columnElement | columnConstraint );ALTER TABLE Customer ADD birthdate DATE;dropTable = "DROP TABLE" , table;

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 28March 17, 2017Basic SQL Query Structure A basic SQL query consists of a SELECT, a FROM and aWHERE clause SELECT- specifies the columns to appear in the result (projection in relational algebra) FROM- specifies the relations to be used (cartesian product in relational algebra) WHERE- filters the tuples (selection in relational algebra)- join conditions are explicitly specified in the WHERE clause GROUP BY- groups rows with the same column values- the HAVING construct can be used to further filter the groups ORDER BY- defines the order of the resulting tuples

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 29March 17, 2017Basic SQL Query Structure ... In general, the SELECT FROM WHERE parts are evaluated asfollows1. generate a cartesian product of the relations listed in the FROMclause2. apply the predicates specified in the WHERE clause on the resultof the first step3. for each tuple in the result of the second step output the attri-butes (or results of expressions) specified in the SELECT clause The evaluation is normally optimised by a query optimiser

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 30March 17, 2017Basic SQL Query Structure ... The order of clauses in an SQL query cannot bechanged Note that the SELECT is equivalent to a relational algebraprojection In contrast to the relational algebra, SQL does noteliminate duplicates automatically the automatic elimination of duplicates would be time consuming user has to eliminate duplicates explicitly via DISTINCT keywordSELECT A1, A2,..., AnFROM r1, r2,..., rmWHERE PpA1,A2,...,An(sP(r1  r2  ...  rm)is equivalent to

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 31March 17, 2017SELECT Clause A '*' can be used in the SELECT clause as a shortcut toget all tuple attributesSELECT *FROM Customer;customerID name street postcode city1 Max Frisch Bahnhofstrasse 7 8001 Zurich2 Eddy Merckx Pleinlaan 25 1050 Brussels5 Claude Debussy 12 Rue Louise 75008 Paris53 Albert Einstein Bergstrasse 18 8037 Zurich8 Max Frisch ETH Zentrum 8092 Zurich

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 32March 17, 2017SELECT Clause ... Duplicate tuples resulting from a projection to specificattributes are not eliminated by defaultSELECT nameFROM Customer;nameMax FrischEddy MerckxClaude DebussyAlbert EinsteinMax Frisch

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 33March 17, 2017SELECT Clause ... The DISTINCT keyword can be used to eliminateduplicatesSELECT DISTINCT nameFROM Customer;nameMax FrischEddy MerckxClaude DebussyAlbert Einstein

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 34March 17, 2017Computed Attributes and Rename Computations can be performed in the SELECT clause multiple numeric attributes can be used in a computation The rename operation (AS) is used to rename relationsas well as attributes computed columns have no name by default also used when multiple relations have the same attribute namesSELECT name, price * 1.5 AS newPriceFROM CD;name newPriceFalling into Place 26.85Carcassonne 23.20Chromatic 24.75

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 35March 17, 2017WHERE Clause In the WHERE clause we can use five basic predicates(search conditions) comparison- compare two expressions range- check whether the value is within a specified range of values (BETWEEN) set membership- check whether the value is equal to a value of a given set (IN) pattern matching- test whether the expression matches a specifies string pattern (LIKE) check for NULL values- check whether the expression is a NULL value (IS NULL)

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 36March 17, 2017WHERE Clause ...SELECT name, postcodeFROM CustomerWHERE city = 'Zurich' AND postcode >= 8040;name postcodeMax Frisch 8092SELECT name, priceFROM CDWHERE price BETWEEN 15.0 AND 17.0;name priceCarcassonne 15.50Chromatic 16.50

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 37March 17, 2017WHERE Clause ... Check for set membership with the IN constructSELECT *FROM CustomerWHERE city IN ('Zurich', 'Brussels');customerID name street postcode city1 Max Frisch Bahnhofstrasse 7 8001 Zurich2 Eddy Merckx Pleinlaan 25 1050 Brussels53 Albert Einstein Bergstrasse 18 8037 Zurich8 Max Frisch ETH Zentrum 8092 Zurich

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 38March 17, 2017Pattern Matching Strings are enclosed in single quotes use a double single quote for escaping The LIKE operator is used for pattern matching the underscore (_) is a placeholder for a single character the percent sign (%) is a placeholder for any substring e.g. LIKE '_e%'nameAlbert EinsteinSELECT DISTINCT nameFROM CustomerWHERE name LIKE '%Ein%';

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 39March 17, 2017Null Values Missing (unknown) info is represented by NULL values result of any comparison involving a NULL value is Unknown three-valued logic (3VL) based on True, False and UnknownTrue False UnknownTrue True False UnknownFalse False False FalseUnknown Unknown False UnknownANDTrue False UnknownTrue True True TrueFalse True False UnknownUnknown True Unknown UnknownOR=True False UnknownTrue True False UnknownFalse False True UnknownUnknown Unknown Unknown UnknownNOTTrue False UnknownFalse True Unknown

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 40March 17, 2017Null Values ... The NULL keyword can also be used in predicates tocheck for null values Note that a check for NULL is not the same as a check forthe empty String ''SELECT *FROM CDWHERE price IS NOT NULL;cdID name duration price year1 Falling into Place 2007 17.90 20072 Carcassonne 3156 15.50 19933 Chromatic 3012 16.50 1993

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 41March 17, 2017FROM Clause The FROM clause creates a cartesian product of multiplerelations and can be used to specify join operations In a previous lecture we have seen the followingrelational algebra expression- "list the name and street of customers whose order is still open"- pname, street(sstatus="open"(order ⋈ customer))- the same can be achieved in SQL by explicitly specifying the matching attributesSELECT name, streetFROM Customer, OrderWHERE Order.customerID = Customer.customerID AND status = 'open';name streetAlbert Einstein Bergstrasse 18

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 42March 17, 2017Inner and Outer Joins Note that there exist SQL extensions to perform joinoperations between two relations R and S in the FROMclause Inner Joins Outer JoinsSELECT * FROM R NATURAL JOIN S;SELECT * FROM R CROSS JOIN S;SELECT * FROM R JOIN S ON R.A > S.B;SELECT * FROM R LEFT OUTER JOIN S ON R.A = S.B;SELECT * FROM R RIGHT OUTER JOIN S ON R.A = S.B;SELECT * FROM R FULL OUTER JOIN S ON R.A = S.B;

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 43March 17, 2017Correlation Variable A correlation variable can be used as an alias for a table Example "Find all pairs of CDs that were produced in the same year"SELECT c1.name AS name1, c2.name AS name2FROM CD c1, CD c2WHERE c1.year = c2.year AND c1.cdID < c2.cdID;name1 name2Carcassonne Chromatic

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 44March 17, 2017Sorting The ORDER BY clause can be used to arrange the resulttuples in acending (ASC) or descending (DESC) order multiple sort keys can be specified; highest priority first tuples with NULL values are either before or after non-NULL tuplesSELECT name, street, cityFROM CustomerORDER BY city ASC, name DESC;name street cityEddy Merckx Pleinlaan 25 BrusselsClaude Debussy 12 Rue Louise ParisMax Frisch ETH Zentrum ZurichMax Frisch Bahnhofstrasse 7 ZurichAlbert Einstein Bergstrasse 18 Zurich

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 45March 17, 2017Set Operations The UNION, INTERSECT and EXCEPT operations correspondto the , and - relational algebra operations the relations have to be compatible (same attributes) these operations remove duplicates by default- the ALL keyword has to be used to retain duplicates(SELECT nameFROM Customer)INTERSECT(SELECT nameFROM Supplier);nameMax Frisch

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 46March 17, 2017Aggregate Functions and Grouping In SQL there are five aggregate functions (MIN, MAX, AVG,SUM and COUNT) that take a set or multiset of values asinput and return a single value Example "Find the number of customers in each city" Aggregate functions (except COUNT(*)) ignore NULLvalues in the input set input set might be empty in which case NULL is returnedSELECT city, COUNT(customerID) AS numberFROM CustomerGROUP BY city;city numberZurich 3Brussels 1Paris 1

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 47March 17, 2017Subqueries A subquery is a SELECT FROM WHERE expression that isnested within another query e.g. via check for set membership (IN or NOT IN) Example "Find all the suppliers who are no customers"SELECT DISTINCT nameFROM SupplierWHERE name NOT IN (SELECT nameFROM Customer);nameMario Botta

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 48March 17, 2017Nested Subqueries ... Example "Find all CDs with a price smaller than average"SELECT *FROM CDWHERE price < (SELECT AVG(price)FROM CD);cdID name duration price year2 Carcassonne 3156 15.50 19933 Chromatic 3012 16.50 1993

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 49March 17, 2017Set Comparison For nested queries with conditions like "greater than atleast one" we can use these set comparison operators > SOME, >= SOME, < SOME, <= SOME, = SOME, <> SOME as well as thesame combination with ALL Example "Find the customers with a postcode greater than all supplier postcodes"SELECT name ,postcodeFROM CustomerWHERE postcode > ALL (SELECT postcodeFROM Supplier);name postcodeClaude Debussy 75008Max Frisch 8092

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 50March 17, 2017Existence Test The EXISTS operator can be used to check if a tupleexists in a subquery ExampleSELECT nameFROM CustomerWHERE EXISTS (SELECT *FROM SupplierWHERE Supplier.name = Customer.name);nameMax Frisch

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 51March 17, 2017Derived Relations A subquery expression can also be used in the FROMclause in this case, a name has to be given to the relation Example "Find the number of customers in the city with the mostcustomers"SELECT MAX(noCustomers) AS maxFROM (SELECT city, COUNT(customerID)FROM CustomerGROUP BY city) AS CityTotal(city, noCustomers);max3

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 52March 17, 2017Basic SQL Query Structure The query statement can be used to retrieve informationfrom one or multiple database tables can perform the relational algebra's selection, projection and joinoperation in a single SELECT FROM WHERE commandquery = select { ("UNION" | "INTERSECT" | "EXCEPT") , [ "ALL" ] , select};select = "SELECT" [ "ALL" | "DISTINCT" ] ,("*" | ( expr , [ "AS" , newName ] ,{ "," , expr , [ "AS" , newName ] } ) ,"FROM" , table , [ correlationVar ] ,{ "," , table , [ correlationVar ] } ,[ "WHERE" , searchCondition ] ,[ "GROUP BY" , column , { "," , column } ,[ "HAVING" , searchCondition ] ];orderedQuery = query , "ORDER BY" , column , [ "ASC" | "DESC" ] ,{ "," , column , [ "ASC" | "DESC" ] };

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 53March 17, 2017Basic SQL Query Structure ...searchCondition = [ "NOT" ] , search ,{ ( "AND" | "OR" ) , [ "NOT" ] , search };search = ( expr , [ "NOT" ] , "BETWEEN" , expr , "AND" , expr ) |( expr , [ "NOT" ] , "LIKE" , "'" , ( string | "_" | "%" ) ,{ string | "_" | "%" } , "'" ) |( column | ( "(" , expr , ")" ) , "IS" , [ "NOT" ] , "NULL" ) |( expr , ( "=" | "<>" | ">" | ">=" | "<" | "<=" ) , ( expr |( [ "SOME" | "ALL" ] , "(" , query , ")" ) ) ) |( expr , [ "NOT" ] , "IN (" ,( ( value , { "," , value } ) | query ) , ")" |( "EXISTS (" , query , ")";

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 54March 17, 2017WITH Clause The WITH clause can be used to improve the readabilityby introducing temporary new relations introduced only in SQL:1999 and not supported by all databases Example "Find all customers who bought one of the most expensive CDs"WITH Expensive(price) ASSELECT MAX(price)FROM CDSELECT Customer.nameFROM Customer, CD, OrderWHERE CD.price = Expensive.price AND CD.cdID = Order.cdID ANDOrder.customerID = Customer.customerID;nameAlbert Einstein

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 55March 17, 2017Views New virtual relations (views) can be defined on top of anexisting logical model simplify queries provide access to only parts of the logical model (security) computed by executing the query whenever the view is used Some DBMS allow views to be stored (materialisedviews) materialised views have to be updated when its relations change(view maintenance)createView = "CREATE VIEW" , table ,[ "(" , column , { "," , column } , ")" ] ,"AS" , query;

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 56March 17, 2017Views Example Note that a view can be used like any other relation Views are useful for queries but they present a seriousproblem for UPDATE, INSERT and DELETE operations modifications are difficult to be propagated to the actual relations modifications on views are therefore generally not permittedCREATE VIEW CustomerCD ASSELECT Customer.customerID, Customer.name, CD.cdID, CD.name AS cdNameFROM Customer, Order, CDWHERE Customer.customerID = Order.customerID ANDOrder.cdID = CD.cdID;

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 57March 17, 2017Transactions A transaction consists of a sequence of query and/orupdate statements atomic set of statements A transaction explicitly starts when an SQL statement isexecuted and is ended by a COMMIT statement a ROLLBACK statement In many SQL implementations each SQL statement is atransaction on its own (automatic commit) this default behaviour can be disabled SQL:1999 introduced BEGIN ATOMIC ... END blocks Transactions will be discussed in detail later

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 58March 17, 2017Homework Study the following chapters of theDatabase System Concepts book chapter 3- sections 3.1-3.10- Introduction to SQL chapter 4- sections 4.1-4.5 and section 4.7- Intermediate SQL

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 59March 17, 2017Exercise 5 Structured Query Language (SQL)

Beat Signer - Department of Computer Science - bsigner@vub.ac.be 60March 17, 2017References A. Silberschatz, H. Korth and S. Sudarshan,Database System Concepts (Sixth Edition),McGraw-Hill, 2010 Donald D. Chamberlin and Raymond F. Boyce,SEQUEL: A Structured English Query Language,Proceedings of the 1974 ACM SIGFIDET Workshop onData Description, Access and Control (SIGFIDET 1974),Michigan, USA, May 1974

2 December 2005Next LectureAdvanced SQL

Movatterモバイル変換

Change Language

Structured Query Language (SQL) - Lecture 5 - Introduction to Databases (1007156ANR)

Embed presentation

Recommended

More Related Content

What's hot

Viewers also liked

Similar to Structured Query Language (SQL) - Lecture 5 - Introduction to Databases (1007156ANR)

More from Beat Signer

Recently uploaded

Structured Query Language (SQL) - Lecture 5 - Introduction to Databases (1007156ANR)