Difference between revisions of "MySQL/scripts"
From Christoph's Personal Wiki
| Line 8: | Line 8: | ||
Say you have a series of integers for your <code>id</code> column in a table and you wish to pad them with zeros if they are less than 1000 (i.e., "<code>1</code>" -> "<code>001</code>"), then: | Say you have a series of integers for your <code>id</code> column in a table and you wish to pad them with zeros if they are less than 1000 (i.e., "<code>1</code>" -> "<code>001</code>"), then: | ||
SELECT RIGHT(1000 + id,3) AS id FROM mydb; | SELECT RIGHT(1000 + id,3) AS id FROM mydb; | ||
| + | |||
| + | ==Relational algebra (RA)=== | ||
| + | ''Note: Taken from the MIT course in [http://www.aduni.org/courses/databases/index.php?view=cw Database Management Systems].'' | ||
| + | |||
| + | Consider the following relations and query: | ||
| + | student(_sname_,major,year) | ||
| + | class(_cnum_,building) | ||
| + | enrolled(_sname_,_cnum_,_time_) | ||
| + | |||
| + | QUESTION: "The majors of students who take at least one class in the physics building" | ||
| + | |||
| + | which could be written in SQL as: | ||
| + | SELECT DISTINCT major | ||
| + | FROM student S,class C,enrolled E | ||
| + | WHERE S.sname = E.sname | ||
| + | AND E.cnum = C.cnum | ||
| + | AND C.building = 'physics' | ||
| + | |||
| + | The RA could be written as: | ||
| + | PI_{major}( SIGMA_{building='physics'}((student JOIN | ||
| + | enrolled) JOIN class) | ||
| + | (where _{x} means subscripted x) | ||
| + | |||
| + | HW: Write three relational algebra trees showing different ways of evaluating this query (you do not need to annotate your diagram with choices of implementation for each operation, i.e. just draw the trees, not full evaluation plans). | ||
Revision as of 05:40, 25 August 2007
- Find duplicate entries
SELECT accession,count(*) AS n FROM mydb WHERE kingdom!='Bacteria' GROUP BY accession HAVING n>2;
- Padding integers
Say you have a series of integers for your id column in a table and you wish to pad them with zeros if they are less than 1000 (i.e., "1" -> "001"), then:
SELECT RIGHT(1000 + id,3) AS id FROM mydb;
Relational algebra (RA)=
Note: Taken from the MIT course in Database Management Systems.
Consider the following relations and query:
student(_sname_,major,year) class(_cnum_,building) enrolled(_sname_,_cnum_,_time_)
QUESTION: "The majors of students who take at least one class in the physics building"
which could be written in SQL as:
SELECT DISTINCT major FROM student S,class C,enrolled E WHERE S.sname = E.sname AND E.cnum = C.cnum AND C.building = 'physics'
The RA could be written as:
PI_{major}( SIGMA_{building='physics'}((student JOIN
enrolled) JOIN class)
(where _{x} means subscripted x)
HW: Write three relational algebra trees showing different ways of evaluating this query (you do not need to annotate your diagram with choices of implementation for each operation, i.e. just draw the trees, not full evaluation plans).
