1SQL: The Query LanguagePart 3R &G - Chapter 5It is not every questionthat deserves an answer.Publius Syrus. 42 B. C.Sorting the Results of a Query• ORDER BY column [ ASC | DESC] [, ...]• Can order by any column in SELECT list,including expressions or aggs:SELECT S.rating, S.sname, S.ageFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sidAND R.bid=B.bid AND B.color=‘red’ORDER BY S.rating, S.sname;SELECT S.sid, COUNT (*) AS redrescntFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sidAND R.bid=B.bid AND B.color=‘red’GROUP BY S.sidORDER BY redrescnt DESC;Views (repeat from last class)CREATE VIEW view_nameAS select_statementMakes development simplerOften used for securityNot instantiated - makes updates trickyCREATE VIEW RedsAS SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ GROUP BY B.bidSELECT bname, scount FROM Reds R, Boats B WHERE R.bid=B.bidAND scount < 10b.bid scount102 1RedsCREATE VIEW RedsAS SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ GROUP BY B.bidViews Instead of Relations in QueriesDiscretionary Access ControlGRANT privileges ON object TO users[WITH GRANT OPTION]• Object can be a Table or a View• Privileges can be:• Select• Insert• Delete• References (cols) – allow to create a foreignkey that references the specified column(s)• All• Can later be REVOKEd• Users can be single users or groups• See Chapter 17 for more details.Two more important topics• Constraints• SQL embedded in other languages2Integrity Constraints (Review)• An IC describes conditions that every legal instanceof a relation must satisfy.– Inserts/deletes/updates that violate IC’s are disallowed.– Can be used to ensure application semantics (e.g., sid isa key), or prevent inconsistencies (e.g., sname has to bea string, age must be < 200)•Types of IC’s: Domain constraints, primary keyconstraints, foreign key constraints, generalconstraints.–Domain constraints: Field values must be of right type.Always enforced.–Primary key and foreign key constraints: you know them.General Constraints• Useful whenmore general ICsthan keys areinvolved.• Can use queriesto expressconstraint.• Checked on insertor update.• Constraints canbe named.CREATE TABLE Sailors( sid INTEGER,sname CHAR(10),rating INTEGER,age REAL,PRIMARY KEY (sid),CHECK ( rating >= 1 AND rating <= 10 )) CREATE TABLE Reserves( sname CHAR(10),bid INTEGER,day DATE,PRIMARY KEY (bid,day),CONSTRAINT noInterlakeResCHECK (`Interlake’ <>( SELECT B.bnameFROM Boats BWHERE B.bid=bid)))Constraints Over Multiple RelationsCREATE TABLE Sailors( sid INTEGER,sname CHAR(10),rating INTEGER,age REAL,PRIMARY KEY (sid),CHECK ( (SELECT COUNT (S.sid) FROM Sailors S)+ (SELECT COUNT (B.bid) FROM Boats B) < 100 ) • Awkward andwrong!• Only checks sailors!• Only required tohold if theassociated table isnon-empty.• ASSERTION is theright solution; notassociated witheither table.• Unfortunately, notsupported in manyDBMS.•Triggers areanother solution.CREATE ASSERTION smallClubCHECK ( (SELECT COUNT (S.sid) FROM Sailors S)+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )Number of boatsplus number of sailors is < 100 Writing Applications with SQL• SQL is not a general purpose programminglanguage.+ Tailored for data retrieval and manipulation+ Relatively easy to optimize and parallelize- Can’t write entire apps in SQL aloneOptions:Make the query language “turing complete”Avoids the “impedance mismatch”but, loses advantages of relational lang simplicityAllow SQL to be embedded in regular programminglanguages.Q: What needs to be solved to make the latterapproach work?Embedded SQL• DBMS vendors usually provide “host language bindings”– E.g. for C or COBOL– Allow SQL statements to be called from within a program– Typically you preprocess your programs– Preprocessor generates calls to a proprietary DB connectivitylibrary• General pattern– One call to connect to the right database (login, etc.)– SQL statements can refer to host variables from the language• Typically vendor-specific– We won’t look at any in detail, we’ll look at standard stuff• Problem– SQL relations are (multi-)sets, no a priori bound on thenumber of records. No such data structure in C.– SQL supports a mechanism called a cursor to handle this.Just to give you a flavorEXEC SQL SELECT S.sname, S.ageINTO :c_sname,:c_ageFROM Sailors SWHERE S.sid = :c_sid3Cursors• Can declare a cursor on a relation or query• Can open a cursor• Can repeatedly fetch a tuple (moving the cursor)• Special return value when all tuples have been retrieved.• ORDER BY allows control over the order in which tuples arereturned.• Fields in ORDER BY clause must also appear in SELECT clause.• Can also modify/delete tuple pointed to by a cursor– A “non-relational” way to get a handle to a particular tuple• There’s an Embedded SQL syntax for cursors– DECLARE <cursorname> CURSOR FOR <select stmt>– FETCH FROM <cursorname> INTO <variable names>– But we’ll use JDBC insteadDatabase APIs: alternative toembedding• Rather than modify compiler, add a librarywith database calls (API)– special procedures/objects– passes SQL strings from language, presents resultsets in a language-friendly way–ODBC a C/C++ standard started on Windows–JDBC a Java equivalent– Most scripting languages have similar things• E.g. For Perl there is DBI, “oraPerl”, other packages• Mostly DBMS-neutral– at least try to hide distinctions across differentDBMSsArchitecture• A lookup service maps “data source names” (“DSNs”) to drivers– Typically handled by OS• Based on the DSN used, a “driver” is linked into the app at runtime• The driver traps calls, translates them into DBMS-specific code• Database can be across a network• ODBC is standard, so the same program can be used (in theory) toaccess multiple database systems• Data source may not even be an SQL database!ApplicationODBC driverData SourceODBC/JDBC• Various vendors provide drivers– MS bundles a bunch into Windows– Vendors like DataDirect and OpenLink sell drivers formultiple OSes• Drivers for various data sources– Relational DBMSs (Oracle, DB2, SQL Server, Informix, etc.)– “Desktop” DBMSs (Access, Dbase, Paradox, FoxPro, etc.)– Spreadsheets (MS Excel,
View Full Document
Unlocking...