a) drop and re-create
b) truncate
c) delete

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

 
Observe the scenario below:

CREATE TABLE `sales` (
  `site_key` int(11) default NULL,
  `sales_date` date default NULL,
  `product_key` int(11) default NULL,
  `sales_amount` decimal(10,2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- test data generation
create table gen(a1 bit(1));
insert into gen values (1),(1),(1),(0),(1),(1),(1),(1),(0),(1);
insert into sales select floor(rand()*10),
            adddate('2010-01-01',
	    interval rand()*360 day),
            rand()*5000,rand() * 1000
  from gen v1,gen v2,gen v3,gen v4;
--

 
Which index would support the following query the best?

select sales_date,sum(sales_amount)
  from sales where site_key=5 and
    sales_date between '2010-01-01' and '2010-01-31'
  group by sales_date;

a) create index ix1 on sales(site_key,sales_date);
b) create index ix1 on sales(sales_date,site_key);
c) create index ix1 on sales(sales_amount);
d) create index ix1 on sales(product_key);

 
[ Tables and Indexes (15%) - Indexes ]
 

CREATE TABLE `rftst` (
  `a` char(8) default NULL,
  `b` int(11) NOT NULL auto_increment,
  `c` decimal(10,2) default NULL,
  PRIMARY KEY  (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

a) Fixed
b) Dynamic
c) Compact
d) Redundant

 
[ Tables and Indexes (15%) - Table Properties ]
 

 
See the following szenario:

mysql> use tt2;

mysql> show create database tt2;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| tt2      | CREATE DATABASE `tt2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table testi (bla varchar(10));
Query OK, 0 rows affected (0.06 sec)

mysql> alter database tt2  charset latin1;
Query OK, 1 row affected (0.00 sec)

 
Which is now the default characterset of table testi?

a) latin1
b) utf8
c) sjis

 
[ Databases (5%) - Database Properties | Altering Databases | Obtaining Database Metadata ]
 

select concat(v1.pronoun,' ',v2.verb,' ',v3.object, ' :-)' ) as sentence
  from
    (select 1 as id,'She' as pronoun union select 2 ,
    'I' union select 3 ,'He' ) v1
  left outer join
    (select 1 as id,'like' as verb union select 2 ,
    'likes' union select 3 ,'hates' ) v2
      on (v1.id > v2.id)
  left outer join
    (select 1 as id,'chocolade' as object union select 2 ,
    'playing' union select 3 ,'sql' ) v3
      on (v1.id < v3.id)
  having sentence is not null ;

a) She likes chocolade :-)
b) He hates playing :-)
c) I like sql :-)

 
[ Just for fun ]
 

mysql> create table dfttst (a int null default null,
b int null default 0,c int not null default 0);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into dfttst values (1,1,1),(null,null,null),(0,0,0);
Query OK, 3 rows affected, 1 warning (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'c' cannot be null |
+---------+------+---------------------------+
1 row in set (0.05 sec)

 
What is the result of the query below?

select count(a),count(b),count(c) from dfttst;

a) 2,2,2
b) 3,3,3
c) 2,2,3

 
And still an extra question:
What would “select a + b + c from tt.dfttst;” return?
 
[ Data Types (15%) - Handling Missing or Invalid Data Values ]
 

 
Check out the following situation:

create table chartst1 (a char(2));
create table chartst2 (a varchar(2));
insert into chartst1 values ('xx'),('Xx'),('x1'),('x2'),('3x');
insert into chartst2 values ('xx'),('Xx'),('x1'),('x2'),('3x');

 
Which table will use less storage?

a) chartst1
b) chartst2
c) both tables use the same storage, as the data is the same

 
Extra question: how would you check?
 
[ Data Types (15%) - String Data Types ]
 

 
See the following table:

CREATE TABLE `bittst` (
  `a` bit(3) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 
What is the maximum value column a can store?

a) 3
b) 8
c) 7
d) 127

 
[ Data Types (15%) - Numeric Data Types ]
 

 
(Find all correct answers)

a) /e
b) \G
c) \g
d) \c

 
[The mysql Client Program (10%) - Client Commands and SQL Statements]
 

 
The script.sql file was created like below:
 
echo select * from tt.table1 > script.sql
 
How do you execute the script?
 
(Find all correct answers)

a) mysqlexe -p -u root -f script.sql
b) mysql -p -u root < script.sql
c) mysql -p -u root -e "source script.sql"

 
[ The mysql Client Program (10%) - Using Script Files with MySQL ]