Database Report: Migration, Temporal Data, and SQL Statements

This report provides a comprehensive overview of database concepts, focusing on MySQL. It begins with an explanation of database migration from MS SQL Server to MySQL, detailing the process and tools like MySQL Workbench. The report then explores temporal data types in MySQL, differentiating between Date, Datetime, and Timespan, and their respective formats and ranges. Following this, it delves into data manipulation statements, including Call, Delete, Insert, Update, Do, Load Data, Load XML, Replace, Select, and Subquery statements, providing examples for each. Finally, the report concludes with a discussion of table joins in MySQL, covering Cross, Inner, Outer (Left and Right), and their applications, along with references to relevant sources.

Database Assignment
May 15
2019
Student ID:
Student Name:

Report
Contents
Report..........................................................................................................................................................2
Part 1: Database Migration from MS SQL Server to MySQL.....................................................................3
Part 2: Temporal Data Types in MySQL.....................................................................................................9
Part 3: Data Manipulation Statements in MySQL.....................................................................................10
Part 4: Table Joins in MySQL...................................................................................................................14
References.................................................................................................................................................18

Part 1: Database Migration from MS SQL Server to MySQL
There are different tools that provide database migration like MySQL workbench, Amazon
DMS etc. MySQL workbench provides very easy and quick database migration tool that
migrates the database from MS SQL Server or any other RDBMS to MySQL. Only accessible
database is possible to be migrate into mySQL e.g. if there is no access of database, then it is not
possible to migrate that into mySQL. Following procedure or steps will have to be followed for
migration of the MS SQL Server database to MySQL-
1. Click on Database Migrate Wizard and the screen will open-

2. Click on Start Migration and the screen will open-
Now select source RDBMS as Microsoft SQL Server from the dropdown list. Enter user
name and password and test connection. If connection succeeded then click on next button and
the screen will open -

Now select connection of target RDBMS from the dropdown list. Enter user name and
password and test connection. If connection succeeded then click on next button and the screen
will open-

Now click on next button and the screen will open-
Now select database to migrate and click on next button and the screen will open-

Now click on Next button-
Select objects to migrate and click on Next button and following window will appear-

Now click on Next button and the database has been migrated.

Part 2: Temporal Data Types in MySQL
There are different temporal data types in MySQL-
1. Date
2. Datetime
3. Timespan
The Date data type stores only the date part no time part. It stores the date in the format like
‘YYYY-MM-DD’. The range which is supported by Date data type is- ‘1000-01-01’ to ‘9999-
12-31’.
The Datetime data type stores both date part and time part. It stores the date in the format like
‘YYYY-MM-DD hh:mm:ss’. The range which is supported by Date data type is- ‘1000-01-01
00:00:00’ to ‘9999-12-31 23:59:59’.
The Timespan data type stores both date part and time part but its range is differ from
Datetime data type. The range which is supported by Timespan data type is- ‘1970-01-01
00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.
The major different between Datetime and Timespan data types is that the Timespan use the
UTC time zone to store the data and convert back the data to the current zone on retrieval while
the Datetime stores the data in the current zone only.
(Oracle. 2019)
(QD Ideas, LLC 2015)

Part 3: Data Manipulation Statements in MySQL
There are different data manipulation statements in MySQL as mentioned below-
1. Call statement
2. Delete statement
3. Insert statement
4. Update statement
5. Do statement
6. Load Data statement
7. Load XML statement
8. Replace statement
9. Select statement
10. Subquery statement
Call statement
Call storedProcedure_name(parameter [1..])
Call storedProcedure_name()
The call statement is used to invoke the stored procedure which is defined by create
procedure statement.
Delete statement
DELETE FROM table [WHERE condition]
Delete from tbl_student where StudentId=1;

The delete statement is used to delete number of records from the specified tables
according to the given where condition. If the where condition is not given, it will delete all
records. It returns the number of deleted records.
Insert statement
Insert into tableName values ()
Insert into tableName (col1, col2,..) values (val1, val2,..)
The insert statement is used to insert new records into the existing table. We can specify
the columns and if we not specify it inserts the values from starting into the columns.
Update statement
UPDATE table SET assign_list
[WHERE condition]
[ORDER BY field]
[LIMIT row_count]
The update statement is used to update values of specific columns in the existing tables
based on specific condition etc.
Do statement
Do expression [,expression]
The do statement is used to execute the expressions but does not return the result sets. It
is the short form of select statement but does not return the record sets.

Load Data statement
LOAD DATA INFILE ‘fileName.txt’ INTO TABLE tableName;
The load data statement is used to load data from the files into the tables. It works at very
high speed.
Load XML statement
LOAD DATA LOCAL INFILE ‘fileName.xml’ INTO TABLE tableName;
The load xml statement is used to load data from the xml file into the table. It works at
very high speed.
Replace statement
REPLACE [INTO] tableName VALUES (value_list)
The replace command is usd to insert data into existing table. it is just like insert
statement but the difference is that it used to delete the duplicate records first if the new record is
of same primary key or unique key.
This is useful in the tables only where primary or unique keys are defined. If there is no
primary or unique key defined, it will work just like insert statement.

Select statement
SELECT
[ALL | DISTINCT ]
[FROM tableName
[WHERE condition]
[GROUP BY colname]
[ASC | DESC]
[HAVING condition]
[ORDER BY colname]
[ASC | DESC]
The select statement is used to fetch data. There are so many options to fetch data like all
fields, specific fields, and records in ascending or descending order, records in groups by using
group by clause etc.
Subquery statement
Select * from table1 where col1=(select col from table2)
The sub query is a select statement within another select statement. There are so many
advantages of sub queries like below-
- It makes the query structured. It separates the parts of the query in a readable form.

- It replaces the complex joins etc. which are very tough to understand.
(Oracle. 2019)
Part 4: Table Joins in MySQL
The SQL join is used to combine rows from two or more tables on specific common
column between them. The tables which are connected with join are related to each other with
primary or foreign n keys.
There are different joins in MySQL-
- Cross join
- Inner join
- Outer Jon
- Left Join
- Right Join
Cross join
Cross join is the simplest join the SQL. It combines each row of first table with all rows
of another table. e.g.-
Select * from department cross join employee;
It will show all the records of department and employee tables like below-

Inner join
The inner join returns rows from both tables that satisfy the given condition e.g. the data
from Employee table and Work hour table. The inner join will be used to get employees and their
working hours. The employee who worked more than 20 hours can be displayed by inner join.
e.g.-
Select * from department inner join employee on department.departmentID=employee.
departmentID
It will show the records of department and employee tables that satisfy the condition like
below-

Outer join
The inner join returns rows from both tables that satisfy the given condition. It can return
null if no match found. There are two types of outer joins- left outer join and right outer join.
Left join
The left join will fetch the all records of left table and the matching values from another
table. In the cases of not getting matched value in the second table, the null value will be shown
in the second table fields. e.g.-
Select * from department left join employee on department.departmentID=employee.
departmentID
It will show all records of department and records from employee tables that satisfy the
condition like below-

Right join
The right join will fetch the all records of right table and the matching values from left
side table. In the cases of not getting matched value in the left side table, the null value will be
shown in the left side table fields. e.g.-
Select * from department right join employee on department.departmentID=employee.
departmentID
It will show all records of employee and records from department tables that satisfy the
condition like below-
(guru99 2019)