CS4421 Lab – Get started with MySQL database

Sprint 2012


For sever, database and account configuration

              Goto: orion.csl.mtu.edu                                 

For Mysql manual

              Goto: http://dev.mysql.com/doc/refman/5.1/en/


Exercises 1: Perform the following tasks using mysql utility.

1.      The client program mysql help


2.      Connect to mysql database server on orion.csl.mtu.edu

%sql –horion.csl.mtu.edu –uyourname –p

Press enter after the –p, don’t put your password on the command line. “ps command” will show it.

3.      Change your password

Mysql>set password=password(‘……’);


4.       Use the SHOW statement to find out the list of the databases that you are allowed to see:

mysql> show databases;


5.       Switch/set the default database

mysql> USE your_own_database

Database changed

mysql> select database();


6.       List mysql commands and pay attention to the commands that are interesting


mysql> help


              Pay attention to

              clear \c,


              edit (\e),

              delimiter (\d),




7.       Create the student table student(id, name, dept_name, total_credits);

Attribute id is the primary key

                   Create table ..


8.       Modify the student table to add a new column age;

Alter table …



9.       Check the properties of table

 mysql> describe student;

mysql> show create table student;  

10.  Insert 3 students into student

mysql> insert into student values (  ,  ,…);



11.  List all the data

mysql> select * from student;



12.  Try to insert a student with existing ID, and see what happen!!!

13.  How many students in the table

mysql> select count(*) from student;


14.  Insert into the student with duplicate name


     insert into students values(…);

     insert into students(id,name) values ( … , …);


15.  Find the duplicate names using self join. (use distinct to get rid of duplicates)


                       select distinct a.name from students a, students b where a.name=b.name and a.id != b.id;


16.  Find the duplicate names using grouping


                       select name from students group by name having count(*) >1



17.  Delete a students by its id=1


              mysql> delete from student where id = …


18.  Update  student ID=1’s age

Update student set age=21 where id=1;


19.  To create a data file

Type you input in a local file, say "testin.txt", like below, separated by tab

10             Alice           CS                80                 21

20             Joe               EE                40                 20


20.  To load the text file `student.data' into the table, use this command:

mysql> LOAD DATA LOCAL INFILE "/home/major/…/student.data " INTO TABLE student;


if there are any warnings, show the warnings:

mysql> show warnings;


21.  Run a batch job from command directory

Save your sql commands separated by “;” ( like the following) into a file, say myquery.sql,

                   select "____________";

                   describe students;

                   select "____________";

                   select count(*) from students;

                   select "____________";

                   select avg(credits) from students;


and invoke it with the command as below from the terminal

                   mysql –h … -u… -p [database_name] < myquery.sql > myquery.out