MySQL Multiple Choice Question

1. Which of the following is not one of the top databases in use today?

(a) Amazon CloudIt

(b) Microsoft SQL Server

(c) Oracle

(d) MySQL

(e) MongoDB

Solution:

A (Amazon CloudIt)

Explanation : Microsoft SQL server, Oracle, and MySQL are relational databases. MongoDB is non relational databases. Amazon CloudIt is not a database.

2. MySQL is free to use as a back-end database for a website.
(a) True
(b) False

Solution:

 True

  Explanation : MySQL is free to use as a back-end database for a website.

3. MySQL uses which TCP/IP port by default?

Solution:

MySQL uses 3306 TCP/IP port by default.

4. MySQL is primarily a relational database based on SQL.
(a) True
(b) False

Solution:

True

Explanation : MySQL is primarily a relational database based on Structured Query Language (SQL).

5. Which of the following is correct about this command: mysql -u root -p?
(a) Installs MySQL for the first time
(b) Starts the MySQL Service in Linux
(c) Starts the MySQL Service in Windows
(d) Configures MySQL to like rootbeer
(e) Connects to MySQL on localhost

Solution:

E) Connects to MySQL on localhost

Explanation : 'mysql -u root -p' command is used to connect to MySQL on localhost. To connect to remote host we have to use -h option. 'mysql -h <remote-ip> -u <user> -p

6. The MySQL Configuration file for Windows is:
(a) my.cnf
(b) mysql.prop
(c) my.ini
(d) my.conf
(e) mysql.conf

Solution:

c (my.ini)

7. The MySQL Configuration file for Linux is:
(a) my.cnf
(b) mysql.prop
(c) my.ini
(d) my.conf
(e) mysql.conf

Solution:

a (my.cnf)

Explanation : we can define most of MySQL's configuration parameters in the my.cnf file which is located in /etc/mysql folder.

How do I store binary data in MySQL?

Q: How do I store binary data in MySQL?

Solution:

The basic answer is in a BLOB data type / attribute domain. BLOB is short for Binary Large Object and that column data type is specific for handling binary data.

you should be aware that there are other binary data formats:
TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB
VARBINARY
BINARY
Each has their use cases. If it is a known (short) length (e.g. packed data) often times BINARY or VARBINARY will work. They have the added benefit of being able ton index on them.

For a table like this:

CREATE TABLE binary_data (
    id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    description CHAR(50),
    bin_data LONGBLOB,
    filename CHAR(50),
    filesize CHAR(50),
    filetype CHAR(50)
);

Best practice for securing user accounts for a MySQL

Q:

Which of the following are considered best practice for securing user accounts for a MySQL installation? Select all that apply.
Hint: This question goes somewhat further than what was covered in the lecture so you will probably need to do some reading to correctly answer this question.
1.
When connecting to a database, never send the password on the command line or code it in plain text into the front end. You can use a properties file, and can hide it by moving it elsewhere and/or changing its name. Better yet, use the mysql_config_editor to store authentication details in an encrypted file.
2.
Any code used to set up users and/or update passwords from the front end needs to ensure that communications with the database are encrypted so that passwords are not sent across the network in plain text.
3.
A MySQL account should only have the privileges that it absolutely needs; different user profiles generally warrant the creation of different accounts. Only administrative accounts should have access to things like triggers and procedures, and they should be restricted to logging in from specific IP addresses (or localhost) where possible.
4.
When giving edit privileges to a user, it is best to have them log in through a front end that does initial checks for attacks such as SQL injection. This front end should communicate via the database through specific procedure calls to insert or update data, to limit the ways in which data is changed. Such procedures should also perform data integrity checks before allow data to be changed.
5.
None of the above.

 

Solution:

1>
When connecting to a database, never send the password on the command line or
code it in plain text into the front end. You can use a properties file,
and can hide it by moving it elsewhere and/or changing its name.
Better yet, use the mysql_config_editor to store authentication details in an encrypted file

I feel the proper method to get passwords for MYSQL servers is to , to have a sign on prompt and get client to enter
the passwords. or have a secured , protected file to save the password.
so coding password in a plain text is highly unadvisable or getting it from command line.
and saving it in a config file using mysql_config editor is advisable.
so option A in your question is one among the best options to secure accounts.

2>Any code used to set up users and/or update passwords from the front end needs to ensure that communications with the database are encrypted
so that passwords are not sent across the network in plain text.
   Communications with database should always be encrypted because, any insert or update statements
might log user passwords as it is. Here privileges comes into picture, only user with super privileges should be allowed to update passwords.
so when password is entered from front end , it should be shown as ******.
also hashing the password , before it travels from user end to database also using secure networks such as https instead of http is a plus.
storing passwords in encrypted way , there is also unique salts for each encrypted passwords, ensure double security.
so option B in your question is one among the best options to secure accounts.
3>yes this is right, we cannot give all privileges to all users in system.
we should have administrative accounts/admins at different levels.
so option C is also one among the best options to secure accounts.
4>so when we are granting edit privileges to user, initially we would have to login as super user or root user.
I feel when entered as super user, we are compromising a lot on security.
its not just the procedures to perform integrity checks, privileges at table level, columns level, proxy privil, along with procedure privileges should be taken care of.
so option 4 is all good, but not the best among 4 described here.
 

Performance of the database

Q: A shop stores information on Customers(cid, name, address, last_visit), Inventory(iid, name, cost, price, stock) and Purchases( cid, iid, when, price, quantity) FK cid REF Customers(cid) ON DELETE RESTRICT ON UPDATE CASCADE, FK iid REF Inventory(iid) ON DELETE RESTRICT ON UPDATE CASCADE.
Which of the following actions are more likely than not to be true regarding the performance of the database? Select all that apply.

 

1.	Using the CREATE INDEX statement to create an index to cid in Customers would make no difference at all.
2.	Creating indexes on Purchases may be harmful if we are expecting very frequent INSERT operations.
3.	Attribute iid of Inventory having an index is likely to assist with an INSERT operation on Customers.
4.	Creating an index on the stock attribute of Inventory would be useful if changes are often due to customers purchasing items (the integer stored in stock decreases by the quantity of every purchase for the product with the iid given in the Purchases tuple), but there are few INSERT operations on the table.

Solution:

NOTE : The INSERT and UPDATE statements take more time on tables having indexes, whereas the SELECT statements become fast on those tables. The reason is that while doing insert or update, a database needs to insert or update the index values as well.
1. False
Explanation : Indexing in Customers is likely to improve its performance because it is less likely to have new customers very often so INSERT (or UPDATE) operations will be less on Customers. On the other hand, INSERT operations on Purchases is more likely and that would require lookup of cid in Customers table due to the presence of foreign key. So, indexing will decrease the lookup time in Customers table and hence, will increase the performance.
2. True
Explanation : As INSERT operations are expected to be more frequent, the indexing may slow down the operations and hence, may degrade the performance.
3. False
Explanation : INSERT operation on Customers has nothing to do with attribute iid of Inventory.
4. False
Explanation : Changes in stock attribute of Inventory due to customers purchasing items would be UPDATE operations. And UPDATE operations also take more time on tables having indexes and hence, may degrade the performance.

 

Triggers in MySQL

Q: Which of the following is true of triggers in MySQL? Select all that apply.
Note: The way triggers works has changed slightly in recent versions. This question refers to the most recent version (after 6 or later).

Solution:

1.	A trigger can call a stored procedure.
2.	A trigger attached to table A that performs an operation on a table B, can cause the activation (triggering) of one or more triggers that are attached to table B.
3.	Specifying a trigger as FOR EACH ROW means the trigger is activated once for each row that is inserted, updated, or deleted.
4.	It is not possible to have multiple triggers with the same trigger event and action time (before/after).
5.	A trigger that attempts to alter the table that it is attached to will result in an error.

Solution:

Solution for 1: False, you can only execute stored procedures and Triggers in a stored procedure cannot be called directly.

Solution for 2: False, it does not create activation of several triggers at table B.
Solution for 3: True, trigger is activated when DML operations are triggered once for each row that is updated
Solution for 4: False, you can only create one trigger for an event per table as well as trigger event and action time
Solution for 5: False, unless the trigger and table posses the same name
 

Stored procedures and functions are available in the current database

Q: Which of the following commands is the best way to generate an overview of what stored procedures and functions are available in the current database and what each does? Assume that the developer(s) of the database have followed the good coding guidelines laid out in this unit.

1.	SHOW PROCEDURES;
2.	SHOW PROCEDURE STATUS WHERE db = DATABASE();
3.	SELECT * FROM INFORMATION_SCHEMA.ROUTINES;
4.	SELECT name, comment, type FROM mysql.proc WHERE db = DATABASE();

Solution:
 

2.SHOW PROCEDURE STATUS WHERE db = DATABASE();
This is the ideal statement to list the procedures stored in the database mentioned in the search condition.
Only SHOW PROCEDURES is not a valid statement as it also needs STATUS
SELECT * FROM INFORMATION_SCHEMA.ROUTINES; this statement selects all the rows from the table ROUTINES and displays the result.
SELECT name, comment, type FROM mysql.proc WHERE db = DATABASE(); This is the selective display of certain columns from the table mysql.proc from the database db.

How to export data from SQL Server 2005 to MySQL

Question: I've been banging my head against SQL Server 2005 trying to get a lot of data out. I've been given a database with nearly 300 tables in it and I need to turn this into a MySQL database. My first call was to use bcp but unfortunately it doesn't produce valid CSV - strings aren't encapsulated, so you can't deal with any row that has a string with a comma in it (or whatever you use as a delimiter) and I would still have to hand write all of the create table statements, as obviously CSV doesn't tell you anything about the data types.

What would be better is if there was some tool that could connect to both SQL Server and MySQL, then do a copy. You lose views, stored procedures, trigger, etc, but it isn't hard to copy a table that only uses base types from one DB to another... is it?
Does anybody know of such a tool? I don't mind how many assumptions it makes or what simplifications occur, as long as it supports integer, float, datetime and string. I have to do a lot of pruning, normalising, etc. anyway so I don't care about keeping keys, relationships or anything like that, but I need the initial set of data in fast!

Solution: Using MSSQL Management Studio i've transitioned tables with the MySQL OLE DB. Right click on your database and go to "Tasks->Export Data" from there you can specify a MsSQL OLE DB source, the MySQL OLE DB source and create the column mappings between the two data sources.

You'll most likely want to setup the database and tables in advance on the MySQL destination (the export will want to create the tables automatically, but this often results in failure). You can quickly create the tables in MySQL using the "Tasks->Generate Scripts" by right clicking on the database. Once your creation scripts are generated you'll need to step through and search/replace for keywords and types that exist in MSSQL to MYSQL.
Of course you could also backup the database like normal and find a utility which will restore the MSSQL backup on MYSQL. I'm not sure if one exists however.

Throw an error in a MySQL trigger

Question: If I have a trigger before the update on a table, how can I throw an error that prevents the update on that table?

Solution:

 As of MySQL 5.5, you can use the SIGNAL syntax to throw an exception:

signal sqlstate '45000' set message_text = 'My Error Message';

State 45000 is a generic state representing "unhandled user-defined exception".

Here is a more complete example of the approach:

delimiter //
use test//
create table trigger_test
(
    id int not null
)//
drop trigger if exists trg_trigger_test_ins //
create trigger trg_trigger_test_ins before insert on trigger_test
for each row
begin
    declare msg varchar(128);
    if new.id < 0 then
        set msg = concat('MyTriggerError: Trying to insert a negative value in trigger_test: ', cast(new.id as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
end
//

delimiter ;
-- run the following as seperate statements:
insert into trigger_test values (1), (-1), (2); -- everything fails as one row is bad
select * from trigger_test;
insert into trigger_test values (1); -- succeeds as expected
insert into trigger_test values (-1); -- fails as expected
select * from trigger_test;

Binary Data in MySQL

Question: How do I store binary data in MySQL?

Solution:  The basic answer is in a BLOB data type / attribute domain. BLOB is short for Binary Large Object and that column data type is specific for handling binary data.

Calculate age in SQL

Question: Given a DateTime representing a person's birthday, how do I calculate their age in years?

 Solution:

declare @dd smalldatetime = '1980-04-01'
declare @age int = YEAR(GETDATE())-YEAR(@dd)
if (@dd> DATEADD(YYYY, -@age, GETDATE())) set @age = @age -1

print @age