lab9x

tenderlaSoftware and s/w Development

Dec 13, 2013 (3 years and 9 months ago)

83 views

MBAC 611


We have been using MS Access to query and modify
our databases.



MS Access provides a GUI (Graphical User Interface)
that hides much of the SQL code that is typically
needed to build databases, query databases, etc.



MS Access is actually interpreting SQL but one can,
for the most part, avoid writing SQL to perform most
queries.



Sometimes you may need to work with a database
that provides only a text
-
based interface. We will
look at one such database SQLite.



SQLite should already be installed on your lab
computer (on the desktop).



Double
-
click on the SQLite icon and skip to the
NEXT slide.



If needed, copy of the SQLite zip file is in Moodle



It can also be downloaded from:
http
://
www.sqlite.org/download.html



Download the first “Precompiled Binary for
Windows” to your
c:
\
temp
folder.

You should now see the following SQLite command window. This is
where you will enter you SQL commands.


Notice that the second line states that we can enter “.help” for
instructions.


Enter
.help

into the command window and then hit the
Enter

key.

You can use the scroll
-
bar on the right
-
hand side of the
window to view the commands. Notice all commands
must start with a period. These commands are different
from SQL commands.



SQL commands are issued on a database whereas the
other commands are issued to the database management
software (SQLite) to perform tasks that the SQL language
doesn’t address


such as exiting the database and
requesting help.



So while the SQL language is a standard the other
commands for performing system
-
specific tasks are not.



SQL can be viewed as programming language
for databases. As such we can write programs
that the database will execute.



In order to avoid having to retype the SQL
commands every time we start the database
software we typically store the SQL commands in
a file. This file is referred to as a SQL script or
SQL program.



We can then instruct the database software to
execute the SQL script.


We will now look at a SQL script that
creates a three table database about
movies.



From Moodle save the
rating.sql

script
o
nto the desktop (or folder containing
the sqlite3 program).



In order to tell SQLite to run a
sql

script
we must issue the “.read” command.



Inside the SQLite command window
enter the following command (note the
period at the beginning of the
command):


.read
rating.sql



No output will result if the SQL script executed successfully.



However, a number of tables should have been created.



We can which tables have been created using the “.tables” command.



Type the following command into the SQLite command window and then
press the
Enter

key:


.tables



The result show should be a list of three tables: Movie, Reviewer, Rating


The script created these three tables.


Lets take a closer look at the script.



Start WordPad (not Notepad)
-

it can be
found under the Accessories Program
group. The script does not display well in
Notepad


that is why we are using
WordPad.



From within WordPad open the
rating.sql

file that is found in your
sqlite
-
shell

folder in the
c:
\
temp
folder.

The comments in the
script tell us what the
SQL code does. The
comments are written
with
/*

and
*/
characters.


The first three
statements utilize the
SQL
drop

command that
deletes the tables if they
previously existed. One
cannot create tables if
they already exist.


The SQL
create

command creates the
tables and the
insert into

command adds data to
the tables.


Lets view the records contained in the
Movie table.



Type the following SQL statement into the
SQLite command window and then press the
Enter

key (note the semicolon at the end of
the statement


this is needed to tell SQLite
that the statement is complete):


s
elect * from movie;



You should see the following output:

Lab Exercise 1:


Start MS Word and save the empty file to your private network directory
under the name
lab9
.


Write and execute the SQL code to display the records of the
Reviewer

table. Using the Snipping Tool take a screenshot of the result.


Paste the screenshot into
the
lab9

MS Word file under the heading
Exercise 1.

Below is a graphical view of the tables created by the
rating.sql

script.
This view copied from the Stanford DB class website:


http://www.db
-
class.org/course/resources/index?page=moviedata


SQLite doesn’t offer great editing
capabilities


there isn’t a built
-
in editor to
write and save scripts. This would allow us
to correct mistakes in our SQL code.



Therefore we will use
NotePad

to write our
scripts. You can also use WordPad but you
may be more familiar with Notepad.



Close WordPad and Start
NotePad
.

Below we will answer some of SQL assignment questions that are posted on
the Stanford DB course website:

http://
www.db
-
class.org/course/quiz/attempt?quiz_id=63

http://
www.db
-
class.org/course/quiz/attempt?quiz_id=106

Question 1:

Find the names of all reviewers who rated
the movie “Gone
with the
Wind”.



It looks like we will need all three tables for the query.


In
NotePad

enter (or paste) the following SQL code:


select
name

from
Movie, Reviewer, Rating

where
title="Gone with the Wind"

and
Movie.mID
=
Rating.mID

and
Reviewer.rID
=
Rating.rID
;


Save the file in your
sqlite
-
shell folder under the name
"
one.sql
"

(note that
the quotes ARE needed).


To run the code type the command
.read
one.sql

into the SQLite command
window and then press the
Enter

key.

The result should look like the following:

Note that “Sarah Martinez” is listed twice. This is not a mistake as the Rating
table does indeed list “Sarah Martinez” twice


the reviews were given on two
different days.

Exercise 2:
For any rating where the reviewer is the same as the director
of the movie, return the reviewer name, movie title, and number of stars.



Write the SQL query in
NotePad
. Run it in SQLite. Copy the SQL code to
your
lab9

file and ADD a screenshot of the output to the
lab9

file.


The screenshot should look similar to the following:


Below is a copy of the tables to help you write the query:

Exercise 3:

Find the titles of all movies not reviewed by Chris Jackson.

Note
that you will need to use the difference operator for this query. In the query
use the word “except” (without quotes) instead of the word “difference”.


Write the SQL query in
NotePad
. Run it in SQLite. Copy the SQL code to your
lab9

file and ADD a screenshot of the output to the
lab9

file.


The screenshot should look similar to the following:


Below is a copy of the tables to help you write the query:

Exercise 4:

For each
movie find the lowest rating
(fewest stars) currently in
the
database. Hint use an aggregate operator and “Group By”.


Write the SQL query in
NotePad
. Run it in SQLite. Copy the SQL code to your
lab9

file and ADD a screenshot of the output to the
lab9

file.


The screenshot should look similar to the following:



Below is a copy of the tables to help you write the query:

Exercise 5:

Some reviewers didn't provide a date with their rating. Find the
names of all reviewers who have ratings with a NULL value for the date.


Remember in a query one writes “is null” NOT “=null”.


Write the SQL query in
NotePad
. Run it in SQLite. Copy the SQL code to your
lab9

file and ADD a screenshot of the output to the
lab9

file.


The screenshot should look similar to the following:


Below is a copy of the tables to help you write the query:


Save your
lab9

Word file.



Submit the
lab9

file to the
lab9

Moodle
assignment.