title: mysql query in shellscript
body: <p>I need to create a database in Shell-Script and insert into database considering the following scenario.
  My question is "How to generate random numbers as marks for each registration numbers and insert to database" ?</p>
  
  <p>Scenario :
  1) Create a table called "students" (reg_id, subject, marks) Select the proper data types and lengths. Note: if there exist a students table drop it first.
  2) Generate and insert records to the table according to the following criteria.
        a. Student reg_id should be like "idx". "x" should be start from 1. Ex: id1, id2..... id138
        b. Every student should have marks only for 8 subjects. (Optional subjects  : Art, Computer, literature)
        c. The maximum number of records should be able to change using a parameter.
        d. The subject  marks can be vary from 0 to 100.</p>
  
  <p>Here is the code i wrote :</p>
  
  <pre><code>#!/bin/bash
  
  NoOfSubjects=8
  
  # create students table
  mysql -u root school &lt;&lt; EOF
  drop table students;
  create table students (reg_id varchar (10) not null, subject varchar(20), marks int);
  EOF
  
  echo "Enter number of students"
  read NoOfStudents
  
  # get list of subjects from teachers table
  results=`mysql -u root school -e "select subject from teachers;"` 
  #get results into array
  read -r -a arr &lt;&lt;&lt; ${results}
  
  # get marks from terminal
  studentCount=1
  subjectCount=1
  count=1
  while [ ${studentCount} -le ${NoOfStudents} ]
  do
      echo "Enter marks for student :" ${studentCount} " with spaces"
          #marks=$(( RANDOM % 100 ))
          #echo $marks
          #count=`expr ${count} + 1`
      read marks
      student_id="id"${studentCount}
  
      # enter to db
      for input in ${marks}
      do
          if [ ${input} -ge 0 ] &amp;&amp; [ ${input} -le 100 ]
          then
              `mysql -u root school -e "insert into students(reg_id, subject, marks) values ('${student_id}','${arr[$subjectCount]}',${input})"`
              subjectCount=`expr ${subjectCount} + 1`
          else
              echo "Invalid marks: " ${input}
          fi
      done
  
      studentCount=`expr ${studentCount} + 1`
      subjectCount=1
  done
  </code></pre>