This recipe consists of three scripts: one to create a database and table, one to insert student data, and one to read and display data from the table.

Create the database and table script:

#!/bin/bash 
#Filename: create_db.sh 
#Description: Create MySQL database and table 

USER="user" 
PASS="user" 

mysql -u $USER -p$PASS <<EOF 2> /dev/null 
CREATE DATABASE students; 
EOF 

[ $? -eq 0 ] && echo Created DB || echo DB already exist  
mysql -u $USER -p$PASS students <<EOF 2> /dev/null 
CREATE TABLE students( 
id int, 
name varchar(100), 
mark int, 
dept varchar(4) 
); 
EOF 

[ $? -eq 0 ] && echo Created table students || \
    echo Table students already exist  

mysql -u $USER -p$PASS students <<EOF 
DELETE FROM students; 
EOF 

This script inserts data in the table:

#!/bin/bash 
#Filename: write_to_db.sh 
#Description: Read from CSV and write to MySQLdb 

USER="user" 
PASS="user" 

if [ $# -ne 1 ]; 
then 
  echo $0 DATAFILE 
  echo 
  exit 2 
fi 

data=$1 

while read line; 
do 

  oldIFS=$IFS 
  IFS=, 
  values=($line) 
  values[1]="\"`echo ${values[1]} | tr ' ' '#' `\"" 
  values[3]="\"`echo ${values[3]}`\"" 


  query=`echo ${values[@]} | tr ' #' ', ' ` 
  IFS=$oldIFS 

  mysql -u $USER -p$PASS students <<EOF 
INSERT INTO students VALUES($query); 
EOF 

done< $data 
echo Wrote data into DB 

The last script queries the database and generates a report:

#!/bin/bash 
#Filename: read_db.sh 
#Description: Read from the database 

USER="user" 
PASS="user" 

depts=`mysql -u $USER -p$PASS students <<EOF | tail -n +2 
SELECT DISTINCT dept FROM students; 
EOF` 

for d in $depts; 
do 

echo Department : $d 

result="`mysql -u $USER -p$PASS students <<EOF 
SET @i:=0; 
SELECT @i:=@i+1 as rank,name,mark FROM students WHERE dept="$d" ORDER BY mark DESC; 
EOF`" 

echo "$result" 
echo 

done 

The data for the input CSV file (studentdata.csv) will resemble this:

1,Navin M,98,CS
2,Kavya N,70,CS
3,Nawaz O,80,CS
4,Hari S,80,EC
5,Alex M,50,EC
6,Neenu J,70,EC
7,Bob A,30,EC
8,Anu M,90,AE
9,Sruthi,89,AE
10,Andrew,89,AE

Execute the scripts in the following sequence:

$ ./create_db.sh 
Created DB
Created table students

$ ./write_to_db.sh studentdat.csv
Wrote data into DB

$ ./read_db.sh 
Department : CS
rank  name  mark
1  Navin M  98
2  Nawaz O  80
3  Kavya N  70

Department : EC
rank  name  mark
1  Hari S  80
2  Neenu J 70
3  Alex M  50
4  Bob A   30

Department : AE
rank  name  mark
1  Anu M    90
2  Sruthi   89
3  Andrew   89