Observe the following scenario:
 

create table pktest1(id int);
create table pktest2 like pktest1;
insert into pktest1 values (1),(1),(2),(2),(3),(3),(1);
insert into pktest2 select * from pktest1;

alter table pktest1 add primary key(id);
alter ignore table pktest2 add primary key(id);

 
Which of the statements below are true?
 
(Find all correct answers)
 

a) The ALTER on pktest1 will fail.
b) The ALTER on pktest2 will fail.
c) The ALTER on pktest2 will remove duplicates.
d) The ALTER on pktest1 will remove duplicates.

 
[ Tables and Indexes (15%) - Altering Tables ]
 

create table
  monthly_sum (yearmonth char(6) primary key,msum decimal (10,2));
insert into monthly_sum
  values ('201001',100),('201002',200),('201003',400),('201004',500);

 
Which is the equivalent to the REPLACE statement below?
 

replace into monthly_sum values ('201001',1202.13);

a) insert into monthly_sum values ('201001',1202.13)
     ON DUPLICATE KEY UPDATE msum=1202.13;

b) update monthly_sum set msum=1202.13 where yearmonth='201001';

c) insert into monthly_sum select '201001',1202.13 from dual
     where not exists (select 1 from monthly_sum where yearmonth='201001');

d) delete from monthly_sum where yearmonth='201001';
   insert into monthly_sum values ('201001',1202.13);

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

create table grptst
  (s_dt date, s_shop int, sales decimal(10,2), key ix1(s_shop,s_dt));
insert into grptst
  values ('2010-01-01',1,1000.00),('2010-01-01',2,1000.00),
         ('2010-01-01',1,1000.00),('2010-01-01',2,null),
         ('2010-01-01',null,5000.50),('2010-01-01',null,5000.50);
 
select s_shop,s_dt,sum(sales)
  from grptst group by s_shop,s_dt order by 1,2;
 
a)
+--------+------------+------------+
| s_shop | s_dt       | sum(sales) |
+--------+------------+------------+
|      1 | 2010-01-01 |    2000.00 |
|      2 | 2010-01-01 |    1000.00 |
+--------+------------+------------+
2 rows in set (0.01 sec)
 
b)
+--------+------------+------------+
| s_shop | s_dt       | sum(sales) |
+--------+------------+------------+
|   NULL | 2010-01-01 |   10001.00 |
|      1 | 2010-01-01 |    2000.00 |
|      2 | 2010-01-01 |    1000.00 |
+--------+------------+------------+
3 rows in set (0.02 sec)
 
c)
+--------+------------+------------+
| s_shop | s_dt       | sum(sales) |
+--------+------------+------------+
|   NULL | 2010-01-01 |   10001.00 |
|      1 | 2010-01-01 |    2000.00 |
|      2 | 2010-01-01 |       NULL |
+--------+------------+------------+
3 rows in set (0.02 sec)
 
d)
+--------+------------+------------+
| s_shop | s_dt       | sum(sales) |
+--------+------------+------------+
|   NULL |       NULL |       NULL |
|      1 | 2010-01-01 |    2000.00 |
|   NULL |       NULL |       NULL |
+--------+------------+------------+
3 rows in set (0.02 sec)

 
[ Querying for Data (15%) - Aggregating Results, Grouping Results ]
 

 
(Find all correct answers)
 
See the scenario below:
 

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 idea is to delete the rows in the stock table for all product ids listed in the out-of-stock table.
 

a) delete from stock where prod_id
     in (select prod_id from out_of_stock);
 
b) delete stock v1 from stock v1
     inner join out_of_stock v2 on (v1.prod_id=v2.prod_id) ;
 
c) delete from stock using stock
     inner join out_of_stock v2 on (stock.prod_id=v2.prod_id) ;
 
d) delete stock.* from stock , out_of_stock
     where stock.prod_id=out_of_stock.prod_id ;

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

 
(Find all correct answers)
 
Both databases are inside the same mysql instance.
 

a) create table tt2.test like tt.test;
   insert into tt2.test select * from tt.test;
   drop table tt.test;
 
b) alter table tt.test rename to tt2.test;
 
c) alter table tt.test move to tt2;

 
[ Tables and Indexes (15%) - Altering Tables ]
 

a) alter table tt.sales add column bla1 varchar(200),
         add index bla_idx(bla),
         add column bla2 varchar(200) default ' ';
 
b)  alter table tt.sales add column bla1 varchar(200);
    alter table tt.sales add index bla_idx(bla);
    alter table tt.sales add column bla2 varchar(200) default ' ';
 
c) a and b do exactly the same: they will execute in the same time.

 
[ Tables and Indexes (15%) - Altering Tables ]
 

create table mintst (n int not null default 0);
 
select min(n),max(n) from mintst;
 
a) 0, 0
b) 0, 255
c) NULL, NULL
d) 0, NULL

 
[ SQL Expressions (15%) - NULL Values ]
 

 
(Find all correct answers)
 

a) show indexes from sales;
b) show columns from sales;
c) describe sales;
d) show create table sales\G
e) mysqldump -d -p -u root tt sales < sales.sql
f) select * from information_schema.columns
     where table_schema='TT' and table_name= 'sales'
     order by ordinal_position;

 
[ Tables and Indexes (15%) - Obtaining Table and Index Metadata ]
 

 
(Find all)
 

a) index on "gender"
b) index on "monthly_sum"
c) index on "sales_date"
d) index on "user_id"

 
[ Tables and Indexes (15%) - Indexes ]
 

 
(Find all correct answers)
 

a) is the amount of distinct values of the column relatively high?
b) is the column used in where clauses?
c) is the column used in order by clauses?
d) is the column used in joins?

 
[ Tables and Indexes (15%) - Indexes ]