Imagine, that in your environment there is the policy, that the reporting tool doesn’t use your tables directly,
but that all access is done via views. So you create views for all your tables like this:
 

create view rep.sales as select * from appdb.sales;

 
Which of the following statements are true?
 
a) Using views like this will decrease performance a lot because of the select *
b) Performance will not be noticeably impacted, because mysql can use the merge algorithm for views like that
c) Columns added after the view-creation will be visible in the view immediately because of the select *
d) After the view creation, changes to the base table are not reaching the view
e) Views like these are actually insertable and updatable, as there is a 1:1 relation to the rows in the base table
f) As their name implies, views can’t be written to
 
[ Views (15%) - Creating Views ]
 

1. execute test;
2. deallocate prepare test;
3. prepare test from @a;
4. set @a:=concat('show create database ',ifnull(database(),'mysql'));

 
Which is the correct order?
 

a) 1,2,3,4
b) 4,3,2,1
c) 3,1,2,4
d) 4,3,1,2

 
Extra question: What does the prepared statement in the example actually do?
 
[ Prepared Statements (5%) - Using Prepared Statements from the mysql Client ]
 

1. mysqldump -p -u root --databases tt > tt.sql
 
2. mysqldump -p -u root tt > tt.sql

 
(Find all correct answers)
 

a) There is no difference
b) The syntax of 1 is incorrect
c) The syntax of 2 is incorrect
d) 1 will include the create database statement
e) 2 will include the create database statement

 
[ Importing and Exporting Data (5%) - Importing and Exporting Data from the Command Line ]
 

create table work1 (
         site_id int,
	 sale_dt date,
	 amount decimal(10,2),
	 primary key (site_id,sale_dt));
create table work2 (
         site_id int,
	 sale_dt date,
	 amount decimal(10,2),
	 primary key (site_id,sale_dt));
 
-- test data
create table wgen(a1 bit(1));
insert into wgen
  values (1),(1),(1),(0),(1),(1),(1),(1),(0),(1);
 
insert ignore into work1
  select rand()*100,
         adddate('2010-01-01',
	 interval rand()*360 day),rand()*100000
  from wgen v1,wgen v2,wgen v3,wgen v4;
 
insert ignore into work2
  select rand()*100,adddate('2010-01-01',
         interval rand()*360 day),rand()*100000
  from wgen v1,wgen v2,wgen v3,wgen v4;

 
You would like to rewrite the query below in different ways.
 

select count(*)
  from work1 v1
  where not exists (
    select * from work2
    where site_id=v1.site_id and sale_dt=v1.sale_dt);

 
(Find all correct answers)
 

a) select count(*)
     from work1 v1 left join work2 v2
          on (v1.site_id=v2.site_id and v1.sale_dt=v2.sale_dt)
     where v2.site_id is null and v2.sale_dt is null;
 
b) select count(*)
     from work1 v1, work2 v2
     where (v1.site_id<>v2.site_id and v1.sale_dt<>v2.sale_dt);
 
c) select count(*)
     from work1 v1
     where (v1.site_id,v1.sale_dt)
       not in (select site_id,sale_dt from work2);
 
d) select count(*)
     from (select * from work1 minus select * from work2) v1;

 
Extra question: How does the test-data generation work, and how many rows will it create?
 
[ Subqueries (10%) - Converting Subqueries to Joins ]
 

create table j1 (i int primary key);
create table j2 like j1;
 
insert into j1 values (1),(2),(3);
insert into j2 values (1),(2),(3);

 
Queries:
 

a) select * from j1,j2;
b) select * from j1,j2 where j1.i=j2.i;
c) select * from j1,j2 where j1.i<>j2.i;
d) select * from j1,j2 where j1.i>j2.i;

 
Results:
 

1)
+---+---+
| i | i |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
3 rows in set (0.00 sec)
 
2)
+---+---+
| i | i |
+---+---+
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 3 | 2 |
| 1 | 3 |
| 2 | 3 |
+---+---+
6 rows in set (0.00 sec)
 
3)
+---+---+
| i | i |
+---+---+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
+---+---+
9 rows in set (0.00 sec)
 
4)
+---+---+
| i | i |
+---+---+
| 2 | 1 |
| 3 | 1 |
| 3 | 2 |
+---+---+
3 rows in set (0.00 sec)

 
[ Joins (10%) - Overview ]
 

create table jj1 (i int primary key);
create table jj2 like jj1;
 
insert into jj1 values (1),(2);
insert into jj2 values (1),(3);

 
Queries:
 

a) select * from jj1 left join jj2 on (jj1.i=jj2.i);
b) select * from jj1 right join jj2 on (jj1.i=jj2.i);
c) select * from jj1 inner join jj2 (jj1.i=jj2.i);

 
 
Results:
 

1)
+------+---+
| i    | i |
+------+---+
|    1 | 1 |
| NULL | 3 |
+------+---+
2 rows in set (0.02 sec)
 
2)
+---+---+
| i | i |
+---+---+
| 1 | 1 |
+---+---+
1 row in set (0.00 sec)
 
3)
+---+------+
| i | i    |
+---+------+
| 1 |    1 |
| 2 | NULL |
+---+------+
2 rows in set (0.00 sec)

 
[ Joins (10%) - Overview ]
 

create table shop(
	       shop_id int primary key,
	       shop_name varchar(32));
insert into shop values
	       (1,'shop one'),
	       (2,'shop two'),
	       (3,'shop three');
create table product(
               product_id int primary key,
	       product_name varchar(32),
	       product_type int);
insert into product values
               (1,'product one',1),
	       (2,'product two',1),
	       (3,'product three',2);
create table sale(
               shop_id int,
               sale_dt date,
	       product_id int,
	       type int,
	       amount decimal(10,2),
	       key ix1(shop_id, sale_dt));
insert into sale values
	       (1,'2010-01-01',1,0,100.50),(1,'2010-01-01',2,0,100.50),
	       (3,'2010-02-01',3,1,100.50),(2,'2010-02-01',3,1,100.50);
 
select v2.shop_name,v1.sale_dt,v3.product_type,sum(v1.amount)
  from sale v1, shop v2, product v3
  where v1.shop_id=v2.shop_id and v1.product_id=v3.product_id
  group by v1.shop_id,v1.sale_dt,v3.product_type
  order by 1,2,3;

 
Possible results:
 

a)
+------------+------------+--------------+----------------+
| shop_name  | sale_dt    | product_type | sum(v1.amount) |
+------------+------------+--------------+----------------+
| shop one   | 2010-01-01 |            1 |         201.00 |
| shop two   | 2010-02-01 |            2 |         100.50 |
| shop three | 2010-02-01 |            2 |         100.50 |
+------------+------------+--------------+----------------+
3 rows in set (0.00 sec)
 
b)
+------------+------------+--------------+----------------+
| shop_name  | sale_dt    | product_type | sum(v1.amount) |
+------------+------------+--------------+----------------+
| shop one   | 2010-01-01 |            1 |         201.00 |
| shop three | 2010-02-01 |            2 |         100.50 |
| shop two   | 2010-02-01 |            2 |         100.50 |
+------------+------------+--------------+----------------+
3 rows in set (0.00 sec)
 
c)
+------------+------------+--------------+----------------+
| shop_name  | sale_dt    | product_type | sum(v1.amount) |
+------------+------------+--------------+----------------+
| shop one   | 2010-01-01 |            1 |         201.00 |
| shop three | 2010-02-01 |            1 |         100.50 |
| shop two   | 2010-02-01 |            2 |         100.50 |
+------------+------------+--------------+----------------+
3 rows in set (0.02 sec)

 
[ Querying for Data (15%) - Using SELECT to Retrieve Data ]
 

 
(Find all correct answers)
 
Assume that the sql_mode is default (empty).
 

a) create table table (from int,column int, order int, by int);
b) create table date (time int,timestamp timestamp);
c) create table `table` (`from` int,`column` int, `order` int, `by` int);
d) create table "table" ("from" int,"column" int, "order" int, "by" int);

 
[ Identifiers (5%) - Reserved Words as Identifiers ]
 

 
(Find all correct answers)
 
See the following scenario:
 

create table stock (prod_id int primary key, amount decimal(10,2));
create table out_of_stock (prod_id int key);
 
insert into stock values (1,100),(2,200),(3,10),(4,10);
insert into out_of_stock values (1),(3);

 
The goal is to set the amount in stock to zero for all product ids listed in out_of_stock.
Which of of the following UPDATE-statements are valid?
 

a) update stock set amount=0
     where prod_id in (select prod_id from out_of_stock);
 
b) update stock set amount=0
     where exists (select 1 from out_of_stock where prod_id=stock.prod_id);
 
c) update stock v1
     inner join out_of_stock v2 on (v1.prod_id=v2.prod_id) set v1.amount=0;
 
d) update stock v1 , out_of_stock v2
     set v1.amount=0 where v1.prod_id=v2.prod_id ;
 
e) update stock
     set amount=ifnull((select 0 from out_of_stock
                where prod_id=stock.prod_id),amount);

 
[ Updating Data (10%) - The UPDATE Statement ]
 

create table utst1(a int);
insert into utst1 values (1),(1),(2),(2),(3),(3);
create table utst2(b int);
 
select * from utst1 union select * from utst2 order by 1;

 
Possible results:
 

a)
+------+
| a    |
+------+
|    1 |
|    1 |
|    2 |
|    2 |
|    3 |
|    3 |
+------+
6 rows in set (0.00 sec)
 
b)
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

 
[ Querying for Data (15%) - Using UNION ]