Connecting to UNIX

1
Connecting to UNIX

• Use ssh client or putty freeware.
• Initially you will be in your home directory.
• Use vi or emacs editors to type text in Unix OR
• Type in notepad, transfer to your home directory.
• Dont forget to give permissions.

2
Access Rights in UNIX

• A tuple for every user.
• User(u) individual user (media56 etc).
• Group(g) a collection of similar users. (like
  media).
• Others(o) all other users of the machine
• Different access rights for yourself, your group
  and others.
• Rights are for
• Reading files (quite harmless unless the files
  are top secret)
• Writing files (dangerous as others can modify
  your files)
• Executing files (needed for binaries and
  executable files)
• In Unix read rights4, write rights2, execute
  rights1
• chmod 755 file.txt (7421(rwx), 541(rx),
  541(rx))

u
g
o
3
Connecting to SQL

• At your prompt
• mysql h wang5 u media33 p
• enter your password
• you can now access databases
• Batch mode Mysql processing
• Write your mysql commands in a file say file.txt
• At the prompt
• mysql h wang5 u media33 p
• Enter your password
• Database/tables will be changed as per your
  statements

4
Single table SQL overview

• Most used Data Types
• INT , FLOAT numeric types
• CHAR(9) , VARCHAR(20) strings
• Simple MySQL commands
• show databases
• use database1
• show tables
• describe table1
• Create table and enter values
• create table students (fname varchar(20), mname
  char(1), lname varchar(20), homework_id
  varchar(3), grade int)
• insert into students values (Clark, P,
  Gable, H1, 99)
• delete from students where lname Gable

5
Single table SQL overview

• select from where statements
• select from students
• select from students where grade90
• select fname, lname from students where grade90
• select concat(fname, ,lname) as name from
  students where grade90
• select lname, grade from students where grade90

6
Single table SQL overview

• Pattern matching statements
• Here denotes any string of any number of
  characters.
• _ denotes any one character.
• select fname, lname, grade from students where
  lname like B
• select fname, lname, grade from students where
  lname in (Smith, Porter, Gibson)
• Order by statements
• select fname, lname, grade from students order
  by grade
• select fname, lname, grade from students order
  by grade desc
• select fname, lname, grade from students order
  by lname

7
Single table SQL overview

• Group by clause
• select homework_id, MIN(grade) as minimum from
  students group by homework_id
• select homework_id, AVG(grade) as average from
  students group by homework_id
• Distinct clause
• select distinct lname from students order by
  lname

8
Multi table SQL

• Suppose there are two tables with schemas as
  follows
• students (name, ssn, major, nationality)
• offices (name, room, building)
• Multi-table queries Inner Join using where
• select students.name, ssn, concat(room, ,
  building) as address from students, offices where
  students.name offices.name
• select students.name, ssn, concat(room, ,
  building) as address from students, offices where
  (students.name offices.name)
• and (building Hammond)

9
Multi table SQL

• Multi-table queries Inner Join using join
  clause
• select students.name, ssn from students join
  offices
• on students.name offices.name order by ssn
• select students.name, ssn, concat(room, ,
  building) as address
• from students join offices on students.name
  offices.name
• where (building Hammond) or (building
  Henderson)

10
Multi table SQL

• Multi-table queries Outer Join using left/right
  join clause
• Three kinds of outer joins left, right and full.
• Table1 left/right join Table2
• Left (Right) Join returns all rows of Table1
  (Table2) regardless of whether they are matched
  or not. Full Join returns union of Left and Right
  joins.

11
Multi table SQL

• Our two tables with schemas are as follows
• students (name, ssn, major, nationality)
• offices (name, room, building)
• Consider the following
• select students.name from students left join
  offices
• on students.name offices.name
• where offices.name is NULL
• The above statement will return names of students
  who have not been assigned offices.

12
Multi table SQL - Sub queries

• Our two tables with schemas are as follows
• students (name, ssn, major, nationality)
• offices (name, room, building)
• A query forms the argument of the where clause of
  another.
• select name, ssn from students
• where name in
• (select name from offices
  where room 131
• and building Hammond)

13
Multi table SQL - Sub queries

• What does the following query do
• select name, ssn from students
• where name in
• (select from offices where
  room 131
• and building Hammond)
• ???????????