MySQL/scripts - Revision history

Christoph at 05:23, 13 February 2013

2013-02-13T05:23:51Z

2013-02-07T05:29:18Z

2012-02-11T21:28:48Z

‎Amarok in MySQL

2007-09-23T06:18:31Z

2007-08-25T07:52:30Z

2007-08-25T07:52:04Z

2007-08-25T05:40:19Z

2007-07-23T23:35:38Z

2007-07-08T07:50:52Z

New page

*Find duplicate entries
SELECT accession,count(*) AS n
FROM mydb
WHERE kingdom!='Bacteria'
GROUP BY accession HAVING n>2;

@@ Line 12: / Line 12: @@
 *Show current timestamp:
   SELECT NOW();
 ==Relational algebra (RA)==

@@ Line 7: / Line 7: @@
   GROUP BY accession HAVING n>2;
-*Padding integers
+*Padding integers: 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;
 ==Relational algebra (RA)==

@@ Line 41: / Line 41: @@
   FLUSH PRIVILEGES;
-In Amarok, go to <code>settings> configure Amarok</code>, click on Collection, and select MySQL. Then, use the following settings:
+In Amarok, go to <code>settings -> configure Amarok</code>, click on Collection, and select MySQL. Then, use the following settings:
   Hostname: 127.0.0.1
   Database: amarok

← Older revision		Revision as of 06:18, 23 September 2007
Line 34:		Line 34:

	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).		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).
		+
		+	==Amarok in MySQL==
		+	mysql -u root -p
		+	CREATE DATABASE amarok; USE mysql;
		+	GRANT ALL ON amarok.* TO amarok@localhost IDENTIFIED BY 'PASSWORD_CHANGE_ME';
		+	FLUSH PRIVILEGES;
		+
		+	In Amarok, go to <code>settings> configure Amarok</code>, click on Collection, and select MySQL. Then, use the following settings:
		+	Hostname: 127.0.0.1
		+	Database: amarok
		+	Port: 3306
		+	Username: amarok
		+	Password: Your Password

@@ Line 1: / Line 1: @@
 *Find duplicate entries
   SELECT accession,count(*) AS n
@@ Line 9: / Line 11: @@
   SELECT RIGHT(1000 + id,3) AS id FROM mydb;
-==Relational algebra (RA)===
+==Relational algebra (RA)==
 ''Note: Taken from the MIT course in [http://www.aduni.org/courses/databases/index.php?view=cw Database Management Systems].''

← Older revision	Revision as of 07:52, 25 August 2007
(No difference)

← Older revision		Revision as of 05:40, 25 August 2007
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).

← Older revision		Revision as of 23:35, 23 July 2007
Line 4:		Line 4:
	WHERE kingdom!='Bacteria'		WHERE kingdom!='Bacteria'
	GROUP BY accession HAVING n>2;		GROUP BY accession HAVING n>2;
		+
		+	*Padding integers
		+	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;