SQL: Overview and highlights - Princeton University
COS597D: Principlesof DatabaseandInforma;onSystems
SQL: Overviewandhighlights
Based on slides for Database Management Systems by R. Ramakrishnan and J. Gehrke
TheSQLQueryLanguage
? StructuredQueryLanguage ? DevelopedbyIBM(systemR)inthe1970s ? Needforastandardsinceitisusedbymanyvendors
? ANSI(AmericanNa;onalStandardsIns;tute) ? ISO(Interna;onalOrganiza;onforStandardiza;on) ? Standards: ? SQL-86 ? SQL-92(majorrevision) ? SQL-99(majorextensions) ? SQL2003(XMLSQL) ? SQL2008 ? SQL2011 ? con;nueenhancements
Crea;ngRela;onsinSQL
Observe: ?type (domain) of each attribute specified
?type enforced by DBMS whenever tuples are added or modified.
? CREATETABLEMovie ( nameCHAR(30), producerCHAR(30), rel_dateCHAR(8), ra;ngCHAR, PRIMARYKEY(name,producer,rel_date))
? CREATETABLEEmployee( SS#CHAR(9), nameCHAR(30), addrCHAR(50),
startYrINT, PRIMARYKEY(SS#))
? CREATETABLEAssignment( posi;onCHAR(20), SS#CHAR(9), managerSS#CHAR(9), PRIMARYKEY(posi;on),
FOREIGNKEY(SS#REFERENCESEmployee), FOREIGNKEY(managerSS#REFERENCESEmployee))
Referen;alIntegrityinSQL
? SQL-92onsupportall4op;onsondeletesandupdates. ? DefaultisNOACTION(delete/update is rejected)
? CASCADE(alsodeletealltuplesthatrefertodeletedtuple)
? SETNULL/SETDEFAULT(setsforeignkeyvalueof referencingtuple)
CREATE TABLE Acct (bname CHAR(20) DEFAULT `main', acctn CHAR(20), bal REAL, PRIMARY KEY ( acctn), FOREIGN KEY (bname) REFERENCES Branch ON DELETE SET DEFAULT )
BUT individual implementations may NOT support
PrimaryandCandidateKeysinSQL
? Possiblymanycandidate keys (specifiedusing UNIQUE),oneofwhichischosenastheprimary key.
? at most one book with a given title and edition ? date, publisher and isbn are determined
? Used carelessly, can prevent the storage of database instances that arise in practice! Title and ed suffice?
UNIQUE (title, ed, pub)?
CREATE TABLE Book (isbn CHAR(10) title CHAR(100), ed INTEGER, pub CHAR(30), date INTEGER, PRIMARY KEY (isbn), UNIQUE (title, ed ))
SELECT [DISTINCT] select-list
BasicSQLQuery FROM from-list
WHERE qualification
? from-listAlistofrela;onnames(possiblywitharange- variableahereachname).
? select-listAlistofaiributesofrela;onsinfrom-list ? qualifica;onComparisons(AiropconstorAir1op
Air2,whereopisoneof,=,,,)combined usingAND,ORandNOT. ? DISTINCTisanop;onalkeywordindica;ngthatthe answershouldnotcontainduplicates.Defaultisthat
duplicatesarenoteliminated!
ConceptualEvalua;onStrategy
? Seman;csofanSQLquerydefinedinterms ofthefollowingconceptualevalua;on strategy:
? Computethecross-productoffrom-list. ? Discardresul;ngtuplesiftheyfailqualifica;ons. ? Deleteaiributesthatarenotinselect-list. ? IfDISTINCTisspecified,eliminateduplicaterows.
? Thisstrategyisprobablytheleastefficient waytocomputeaquery!Anop;mizerwill findmoreefficientstrategiestocomputethe same answers.
ExampleInstances
instance of bname bcity assets
Branch
pu
Pton 10
? Wewillusethese
nyu nyc 20
instancesoftheAcct
andBranchrela;ons
time sq nyc 30
inourexamples.
instance of bname acctn bal
Acct pu
33
356
nyu
45
500
ExampleofConceptualEvalua;on
SELECT acctn FROM Branch, Acct WHERE Branch.bname=Acct.bname AND assets, ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- sql cheat sheet websitesetup
- 242 31 sql set operators so handy venn you need them sas support
- lecture 02 modern sql carnegie mellon university
- practice exam databricks
- advanced sql subqueries and complex joins mit opencourseware
- sql overview and highlights princeton university
- csc 443 data base management systems adelphi university
- sql basic concepts the university of edinburgh
- get sas r sy with proc sql mwsug
- create table students virginia tech
Related searches
- princeton community hospital princeton wv
- princeton university admissions staff
- princeton university hospital princeton nj
- sql date and time formats
- university medical center princeton nj
- princeton hospital princeton nj
- university of scranton princeton review
- princeton medical center princeton nj
- bible books overview and summary
- princeton review university rankings
- starbucks industry overview and trends
- princeton university acceptance rate