Make Query which,...
1. Shows all data from all fields in borrower-table.
2. Shows firstnames and lastnames in borrower table in alphabetical order based to lastname.
3. Shows all postalcodes, but same code should be mentioned only once.
4. Shows first 5 rows in borrower-table (use Google to find the answer).
5. Shows rows 6-10 in borrower table.
6. Shows all fields from borrower if id_borrower is 9.
7. Shows all fields from borrower if id_borrower is 9 or 11.
8. Shows all fields from borrower if id_borrower is 9 or 11 or 13.
9. Shows all fields from borrower if id_borrower is 5-10.
10. Shows all fields from borrower if id_borrower is not 5-10.
11. Shows all fields from borrower if id_borrower is 1-12 and postalcode is 90101.
12. Shows borrower's lastname and firstname if firstname is Carla.
13. Shows borrower's lastname and firstname if firstname is Carla or Ralph.
14. Shows borrower's lastname and firstname if streetaddress is Uusikatu 4 or Isokatu 66.
15. Shows borrower's lastname and firstname if the person doesn't have a phone.
16. Shows borrower's lastname and firstname and phone numbers if the person has phone.
17. MySQL has several functions which will return current date. Use Google to find out them. You can test them by writing Query: "SELECT function-name;"
18. Shows all fields from Borrow-table if the book is late.
19. Shows all fields from book if the book name starts with 'A'
20. Shows all fields from book if the book name includes word Management
21. Shows all fields from book if the book name includes letter e
22. Shows all fields from book if the book name includes letter e and a
23. Shows firstnames and lastnames of borrower's who lives on Isokatu
24. Shows firstnames and lastnames and phonenumber of borrower, which phonenumber starts with 040

Write Queries which,
1. Show author and the amount of books of that author
2. Show author and the names of the books made by that author the result should be like this

     +----------+----------------------------------------------------+
   | author   | books                                              |
   +----------+----------------------------------------------------+
   | Albrecht | Accounting Concepts                                |
   | Manz     | Leadership Wisdom of Jesus                         |
   | Marakas  | Management Info Systems,Management Info Systems    |
   | Miller   | Business Law Today                                 |
   | Speight  | Shaping Space,Art Since 1940                       |
   | Stice    | Intermediate Accounting                            |
   | Stokstad | Make it in Clay,Art History Vol II & ala carte lab |
   | Upton    | Everything You Ever Wanted to Know                 |
   | Vilppu   | Photography                                        |
   | Williams | Management                                         |
   | Zelanshi | Drawing Manual Vilppu,TBA                          |
   +----------+----------------------------------------------------+
     

3. Which will show the postalcodes and amount of borrowers on that postalcode area (borrower table). Results should be like this

   +------------+---------------------+
   | postalcode | amount of borrowers |
   +------------+---------------------+
   | 90100      |                   4 |
   | 90101      |                   4 |
   | 90102      |                   4 |
   | 90103      |                   4 |
   | 90104      |                   4 |
   +------------+---------------------+
       

Make Query which,...
1. Show the names of borrower and which book they have borrowed. Result should be like this

   +-----------+----------+------------------------------------+
   | firstname | lastname | name                               |
   +-----------+----------+------------------------------------+
   | Kay       | Naquin   | TBA                                |
   | Leonard   | Rowden   | Art Since 1940                     |
   | Leonard   | Rowden   | Art History Vol II & ala carte lab |
   | Leonard   | Rowden   | Drawing Manual Vilppu              |
   | Ralph     | Shane    | Everything You Ever Wanted to Know |
   | Marvin    | Heflin   | Intermediate Accounting            |
   | Marvin    | Heflin   | Management                         |
   | Marvin    | Heflin   | Business Law Today                 |
   | Marvin    | Heflin   | Management Info Systems            |
   +-----------+----------+------------------------------------+
   

2. The same as below but use concat and change the headins, so the the results are like this

   +----------------+------------------------------------+
   | borrower name  | book name                          |
   +----------------+------------------------------------+
   | Kay Naquin     | TBA                                |
   | Leonard Rowden | Art Since 1940                     |
   | Leonard Rowden | Art History Vol II & ala carte lab |
   | Leonard Rowden | Drawing Manual Vilppu              |
   | Ralph Shane    | Everything You Ever Wanted to Know |
   | Marvin Heflin  | Intermediate Accounting            |
   | Marvin Heflin  | Management                         |
   | Marvin Heflin  | Business Law Today                 |
   | Marvin Heflin  | Management Info Systems            |
   +----------------+------------------------------------+
   

3. The same as below but use group_concat, so that the results are like this

   +----------------+--------------------------------------------------------------------------------+
   | borrower name  | books                                                                          |
   +----------------+--------------------------------------------------------------------------------+
   | Kay Naquin     | TBA                                                                            |
   | Leonard Rowden | Art Since 1940,Art History Vol II & ala carte lab,Drawing Manual Vilppu        |
   | Ralph Shane    | Everything You Ever Wanted to Know                                             |
   | Marvin Heflin  | Intermediate Accounting ,Management,Business Law Today,Management Info Systems |
   +----------------+--------------------------------------------------------------------------------+
   

Make Query which,...
1. Names of the borrowers which has borrowed books
2. Names of the borrowers which has books late

1. Print this table

   +----------+-----------------+
   | author   | amount of books |
   +----------+-----------------+
   | Upton    |               1 |
   | Vilppu   |               1 |
   | Zelanshi |               2 |
   | Speight  |               2 |
   | Stokstad |               2 |
   | Albrecht |               1 |
   | Stice    |               1 |
   | Marakas  |               2 |
   | Williams |               1 |
   | Manz     |               1 |
   | Miller   |               1 |
   | TOTAL    |              15 |
   +----------+-----------------+
   

Make Query which will
1. Add below book to book table in library database

   name=SQL tutorial
   author=Jim Jones
   isbn=12345678x
   

2. Update the name of above book to "New SQL tutorial"
3. Check the new values of the book
4. Delete above book

Read the examples about procedures and transactions from here

Make a procedure named borrowBook, which work this way

1. The procedure takes in the id_book and id_borrower
2. It will check does the the book exist and is it borrowed or not
3. If the book exists and it is not borrowed at this moment it will be added to the borrow table
4. Call your procedure and check that it works