Hi, these are the MySQL questions! Have fun ;) (it's a basic course, so they shouldn't be that hard.)
Theory 1/ Give a way to solve ambiguous column names in a SELECT statement. 2/ How can you test if a value is undefined? 3/ Why is x IN (SELECT columnname FROM large_table) slow? 4/ How can you do inexact stringmatching? e.g. I want all names that have a 'en' in them. 5/ In which order are multiple order by's evaluated? 6/ Why can't you group aggregate functions? 7/ Why can't you use another attribute in the SELECT after the use of an aggregate function? 8/ Are NULL values 'used' with aggregate functions? 9/ What happens to NULL values in a GROUP BY? 10/ Why is ORDER BY useless/usefull in a subquery? 11/ Where can you use aggregate functions outside of the SELECT ... FROM ? 12/ Does the following query return any rows: DO expression(); 13/ Why do we use placeholders in a DBI query? 14/ With what can you replace the HAVING if you don't use aggregate functions in HAVING? 15/ What does NOT EXISTS check?
practice: 1/ Correct following query: SELECT sum(max(P.discount) * BD.price / 100) AS summed_price, P.bought, id FROM Buydetails, Books JOIN Promotions WITH BD.book_id = P.bought ORDER BY summed_price GROUP BY BD.book_id
2/ Correct following query SELECT title, SUM(price)*21/100 As PriceWithBTW FROM Buydetails, Books WHERE PriceWithBTW != price 3/ Add yourself to the table Members (provide at least a first and lastname). 4/ Change your id to '42'. 5/ Delete yourself again from the table Members. 6/ List all members (firstname, lastname) that live in Gent, Mariakerke and Belsele. Order them on their lastname. 7/ List all members (firstname, lastname) that have bought the book 'The Art of War' with VISA or Contant. 8/ List all members (firstname, lastname) that have bought the book 'The Art of War' three times. 9/ List all members (fristname, lastname) that have bought the book 'The Art of War'. Only list those members that have payed with VISA. Only list those members who payed more for 'The Art of War' then for any book sold in month of August. 10/ Write a perl DBI program that can print the title of all books a person bought. Don't panic! List the books the person with id 42 bought. -Kenny
Error: List all members (fristname, lastname) Kenny Billiau wrote:
Hi,
these are the MySQL questions!
Have fun ;)
(it's a basic course, so they shouldn't be that hard.)
Theory 1/ Give a way to solve ambiguous column names in a SELECT statement. 2/ How can you test if a value is undefined? 3/ Why is x IN (SELECT columnname FROM large_table) slow? 4/ How can you do inexact stringmatching? e.g. I want all names that have a 'en' in them. 5/ In which order are multiple order by's evaluated? 6/ Why can't you group aggregate functions? 7/ Why can't you use another attribute in the SELECT after the use of an aggregate function? 8/ Are NULL values 'used' with aggregate functions? 9/ What happens to NULL values in a GROUP BY? 10/ Why is ORDER BY useless/usefull in a subquery? 11/ Where can you use aggregate functions outside of the SELECT ... FROM ? 12/ Does the following query return any rows: DO expression(); 13/ Why do we use placeholders in a DBI query? 14/ With what can you replace the HAVING if you don't use aggregate functions in HAVING? 15/ What does NOT EXISTS check?
practice: 1/ Correct following query: SELECT sum(max(P.discount) * BD.price / 100) AS summed_price, P.bought, id FROM Buydetails, Books JOIN Promotions WITH BD.book_id = P.bought ORDER BY summed_price GROUP BY BD.book_id
2/ Correct following query SELECT title, SUM(price)*21/100 As PriceWithBTW FROM Buydetails, Books WHERE PriceWithBTW != price
3/ Add yourself to the table Members (provide at least a first and lastname).
4/ Change your id to '42'.
5/ Delete yourself again from the table Members.
6/ List all members (firstname, lastname) that live in Gent, Mariakerke and Belsele. Order them on their lastname.
7/ List all members (firstname, lastname) that have bought the book 'The Art of War' with VISA or Contant.
8/ List all members (firstname, lastname) that have bought the book 'The Art of War' three times.
9/ List all members (fristname, lastname) that have bought the book 'The Art of War'. Only list those members that have payed with VISA. Only list those members who payed more for 'The Art of War' then for any book sold in month of August.
10/ Write a perl DBI program that can print the title of all books a person bought. Don't panic! List the books the person with id 42 bought.
-Kenny _______________________________________________ Binari Intellectuals Need And Require Insanity https://maillist.psb.ugent.be/mailman/listinfo/binari
hmmm, when can we follow this course again :-/ L. Kenny Billiau wrote:
Hi,
these are the MySQL questions!
Have fun ;)
(it's a basic course, so they shouldn't be that hard.)
Theory 1/ Give a way to solve ambiguous column names in a SELECT statement. 2/ How can you test if a value is undefined? 3/ Why is x IN (SELECT columnname FROM large_table) slow? 4/ How can you do inexact stringmatching? e.g. I want all names that have a 'en' in them. 5/ In which order are multiple order by's evaluated? 6/ Why can't you group aggregate functions? 7/ Why can't you use another attribute in the SELECT after the use of an aggregate function? 8/ Are NULL values 'used' with aggregate functions? 9/ What happens to NULL values in a GROUP BY? 10/ Why is ORDER BY useless/usefull in a subquery? 11/ Where can you use aggregate functions outside of the SELECT ... FROM ? 12/ Does the following query return any rows: DO expression(); 13/ Why do we use placeholders in a DBI query? 14/ With what can you replace the HAVING if you don't use aggregate functions in HAVING? 15/ What does NOT EXISTS check?
practice: 1/ Correct following query: SELECT sum(max(P.discount) * BD.price / 100) AS summed_price, P.bought, id FROM Buydetails, Books JOIN Promotions WITH BD.book_id = P.bought ORDER BY summed_price GROUP BY BD.book_id
2/ Correct following query SELECT title, SUM(price)*21/100 As PriceWithBTW FROM Buydetails, Books WHERE PriceWithBTW != price
3/ Add yourself to the table Members (provide at least a first and lastname).
4/ Change your id to '42'.
5/ Delete yourself again from the table Members.
6/ List all members (firstname, lastname) that live in Gent, Mariakerke and Belsele. Order them on their lastname.
7/ List all members (firstname, lastname) that have bought the book 'The Art of War' with VISA or Contant.
8/ List all members (firstname, lastname) that have bought the book 'The Art of War' three times.
9/ List all members (fristname, lastname) that have bought the book 'The Art of War'. Only list those members that have payed with VISA. Only list those members who payed more for 'The Art of War' then for any book sold in month of August.
10/ Write a perl DBI program that can print the title of all books a person bought. Don't panic! List the books the person with id 42 bought.
-Kenny _______________________________________________ Binari Intellectuals Need And Require Insanity https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ============================================================== Lieven Sterck, PhD Tel:+32 (0)9 3313821 Fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, UGent Bioinformatics and Evolutionary Genomics Division Technologiepark 927, B-9052 Gent, Belgium Email: lieven.sterck@psb.ugent.be Website: http://bioinformatics.psb.ugent.be ============================================================== "Facts are meaningless. You could use facts to prove anything that's even remotely true!" H. Simpson
The slides and exercises are online: http://bioinformatics.psb.ugent.be/ggs/courses/mysql login: bioinformatics passw: YVdPeer Does anyone want the solutions or are there overconfident peepz willing to try the exam afterall? ;) -Kenny ps: live @ the exam .. I wonder when the first one will give me his/her results .. On Mon, 26 Jan 2009, lieven sterck wrote:
hmmm, when can we follow this course again :-/
L.
Kenny Billiau wrote:
Hi,
these are the MySQL questions!
Have fun ;)
(it's a basic course, so they shouldn't be that hard.)
Theory 1/ Give a way to solve ambiguous column names in a SELECT statement. 2/ How can you test if a value is undefined? 3/ Why is x IN (SELECT columnname FROM large_table) slow? 4/ How can you do inexact stringmatching? e.g. I want all names that have a 'en' in them. 5/ In which order are multiple order by's evaluated? 6/ Why can't you group aggregate functions? 7/ Why can't you use another attribute in the SELECT after the use of an aggregate function? 8/ Are NULL values 'used' with aggregate functions? 9/ What happens to NULL values in a GROUP BY? 10/ Why is ORDER BY useless/usefull in a subquery? 11/ Where can you use aggregate functions outside of the SELECT ... FROM ? 12/ Does the following query return any rows: DO expression(); 13/ Why do we use placeholders in a DBI query? 14/ With what can you replace the HAVING if you don't use aggregate functions in HAVING? 15/ What does NOT EXISTS check?
practice: 1/ Correct following query: SELECT sum(max(P.discount) * BD.price / 100) AS summed_price, P.bought, id FROM Buydetails, Books JOIN Promotions WITH BD.book_id = P.bought ORDER BY summed_price GROUP BY BD.book_id
2/ Correct following query SELECT title, SUM(price)*21/100 As PriceWithBTW FROM Buydetails, Books WHERE PriceWithBTW != price
3/ Add yourself to the table Members (provide at least a first and lastname).
4/ Change your id to '42'.
5/ Delete yourself again from the table Members.
6/ List all members (firstname, lastname) that live in Gent, Mariakerke and Belsele. Order them on their lastname.
7/ List all members (firstname, lastname) that have bought the book 'The Art of War' with VISA or Contant.
8/ List all members (firstname, lastname) that have bought the book 'The Art of War' three times.
9/ List all members (fristname, lastname) that have bought the book 'The Art of War'. Only list those members that have payed with VISA. Only list those members who payed more for 'The Art of War' then for any book sold in month of August.
10/ Write a perl DBI program that can print the title of all books a person bought. Don't panic! List the books the person with id 42 bought.
-Kenny _______________________________________________ Binari Intellectuals Need And Require Insanity https://maillist.psb.ugent.be/mailman/listinfo/binari
--
Kenny Billiau wrote:
The slides and exercises are online:
http://bioinformatics.psb.ugent.be/ggs/courses/mysql
login: bioinformatics passw: YVdPeer
Does anyone want the solutions or are there overconfident peepz willing to try the exam afterall? ;)
-Kenny ps: live @ the exam .. I wonder when the first one will give me his/her results .. 16:33 me!
On Mon, 26 Jan 2009, lieven sterck wrote:
hmmm, when can we follow this course again :-/
L.
Kenny Billiau wrote:
Hi,
these are the MySQL questions!
Have fun ;)
(it's a basic course, so they shouldn't be that hard.)
Theory 1/ Give a way to solve ambiguous column names in a SELECT statement. 2/ How can you test if a value is undefined? 3/ Why is x IN (SELECT columnname FROM large_table) slow? 4/ How can you do inexact stringmatching? e.g. I want all names that have a 'en' in them. 5/ In which order are multiple order by's evaluated? 6/ Why can't you group aggregate functions? 7/ Why can't you use another attribute in the SELECT after the use of an aggregate function? 8/ Are NULL values 'used' with aggregate functions? 9/ What happens to NULL values in a GROUP BY? 10/ Why is ORDER BY useless/usefull in a subquery? 11/ Where can you use aggregate functions outside of the SELECT ... FROM ? 12/ Does the following query return any rows: DO expression(); 13/ Why do we use placeholders in a DBI query? 14/ With what can you replace the HAVING if you don't use aggregate functions in HAVING? 15/ What does NOT EXISTS check?
practice: 1/ Correct following query: SELECT sum(max(P.discount) * BD.price / 100) AS summed_price, P.bought, id FROM Buydetails, Books JOIN Promotions WITH BD.book_id = P.bought ORDER BY summed_price GROUP BY BD.book_id
2/ Correct following query SELECT title, SUM(price)*21/100 As PriceWithBTW FROM Buydetails, Books WHERE PriceWithBTW != price
3/ Add yourself to the table Members (provide at least a first and lastname).
4/ Change your id to '42'.
5/ Delete yourself again from the table Members.
6/ List all members (firstname, lastname) that live in Gent, Mariakerke and Belsele. Order them on their lastname.
7/ List all members (firstname, lastname) that have bought the book 'The Art of War' with VISA or Contant.
8/ List all members (firstname, lastname) that have bought the book 'The Art of War' three times.
9/ List all members (fristname, lastname) that have bought the book 'The Art of War'. Only list those members that have payed with VISA. Only list those members who payed more for 'The Art of War' then for any book sold in month of August.
10/ Write a perl DBI program that can print the title of all books a person bought. Don't panic! List the books the person with id 42 bought.
-Kenny _______________________________________________ Binari Intellectuals Need And Require Insanity https://maillist.psb.ugent.be/mailman/listinfo/binari
participants (4)
-
Esther-Kristin Lather -
Kenny Billiau -
lieven sterck -
Thomas Abeel