SQL Exercise using Teradata Student Network

1
SQL Exercise using Teradata Student Network

• Sean Eom
• MI375MI410

2
Before the start of this assignment

• You are expected to read the power point file
  (Intro to sql) from the class website at week 5
  row. I suggest to print and read many times.
• If you dont understand and have questions, post
  them on the forum. If you believe that you have a
  good understanding, proceed to the next slide.

3
Teradata Student Network?

• Teradata University Network (TUN) is, created by
  Teradata (a division of NCR Corporation), an
  online learning portal on state of the art
  technology designed to foster education of data
  warehousing, business intelligence (BI) and
  database administration. Southeast Missouri
  State University is now one of nearly 170
  universities from 27 countries representing in
  the network.

4

• "The resources Teradata has made available with
  the Teradata University Network make it possible
  to provide educational experiences to students
  that otherwise would be difficult or impossible
  to give, such as training on state- of-the-art
  technology," said Dr. Hugh Watson, Terry College
  of Business, University of Georgia. "What makes
  this site unique is that it is designed and
  managed by faculty to meet the specific needs of
  those who are teaching students how to use these
  decision-support tools."

5

• Since 2004, TUN has been used to help MI375
  students understand the structured query
  languages (SQL). Before the inclusion of SQL
  assignments using TUN software, students used
  only Microsoft Access to extract information from
  the database. Now MI375 students, online as well
  as face-to-face class, learn how to extract
  information from database using structured query
  languages.

6
Entering Teradata Student Network

• Click teradata student network on the course
  schedule page. The screen below appears.
• Enter password SPARTANS. All upper cases. Do
  not enter quotation .

7
This is the second screen of TUN
8
Register before the use of TUN

• The previous screen shows three red headlines
  under Welcome (orange color).
• Welcome to Teradata University Network
• Register Today
• Access TSN
• Click register (hyperlinked), the first line
  under the heading register today.

9
The screen after clicking register.
10
Register!

• Fill out the registration form now. After
  successful registration, you will see the
  following. Click Return to Teradata Student
  Network Homepage

11

• You are creating two sets of user_ID and
  password.
• You have just created the first one to enter the
  Teradata Student Network(TSN). Please jot down
  the user_ID and password to TSN.
• In a few moments, you will register again to
  enter Teradata SQL website. You will create
• SQL User_ID and SQL password
• Dont forget two write down these two.

12
TUN Home After Registration, Click/select
Software (blue-hyperlinked)
13
After software selection, this is the screen.
Now, select Teradata Database
14
After clicking Teradata Database, this is what
you see. You need to register again to enter
Teradata SQL assistant by clicking Register to
Use Teradata SQL Assistant/Web Edition in
a Course
15
This is registration form to fill out. You will
find our university and my name. The course
dropdown menu will list MI410, MI430, and MIS.
MIS is the course you will select. There is NO
course password.
16

• Enter university, Instructor, and course. There
  is no course password. Do not enter.
• Add the following
• SQL UserID (this is your user id, you decide it)
• SQL PW (you decide it)
• E-mail address (your e-mail address)
• Hint Phrase
• Hint Answer
• Click Register, Congrat! You are done!! Jot
  down your SQL User ID and SQL password.

17
We are back to Teradata Database Section, Click
Execute Teradata SQL Assistant/Web Edition
18

• In the previous slide, you see execute teradata
  assistant/web edition (bottom last row).
• Click it.
• On the next screen, enter
• your SQL user ID,
• SQL password, and
• default database (db_watson)
• Click OK.

19
Entering SQL Assistant/web edition
20
SQL Assistant First ScreenNotice that there are
three sign at the left hand side.
21
From the SQL assistant screen, click sign at
the left of db_watson database symbol. Then the
sign changes into - and you will see four
signs with Tables, Views, Macros, and
Procedures.
22
From the previous screen, click sign at the
left of table symbol under db_watson database.
23
Entering SQL Commands
24
Executing command and output

• After entering the command, click the execute
  button on the previous slide.
• The output of the query is to be displayed in
  the answer set window area (see next slide).

25
Executing the commands and output
26
Reading error messages

• Suppose you entered an SQL command with a syntax
  error, then of course you will not get correct
  SQL outputs. Instead, you will get an error
  message. Here is an example.
• You entered Selct from shr instead of
  Select from shr
• the very bottom of the next screen says3706
  Syntax error expected something between the
  beginning of the request and the word selct.

27
(No Transcript)
28
SQL Exercise

• The SQL exercise is based on the following single
  table database. The table name is SHR (Share).
• The SHR table has several attributes (fields).
• Shrcode (Firms code) - character
• Shrfirm (Firms name) - character
• Shrprice (Firms stock price) - number
• Shrqty (Quantity of stock owned) -number
• Shrdiv (Share Dividend) - number
• Shrpe (Price-to-earnings ratio) - number

29
Attributes in the SHR table

• As discussed in the previous slide, the Table
  consists of 6 fields (attributes). To see the
  list of attributes in SHR table, Do the
  following.
• From the list of DB_Watson Tables, find SHR
  tables. (see the next slide). Click , which
  means we will explore what is inside the share
  table.

30
(No Transcript)
31
After clicking of SHR table, you will see the
following, then click of Columns results in
the right figure showing all attributes (fields).
32
Share Table before building queries, make sure
that you understand the database contents below.
33
Now, I will show two examples of how to write a
query using structured query language (SQL) and
execute it to display output. As stated earlier,
please study Intro to SQL power point file on
the class schedule page.

• Select from shr
• Asterisk () means all
• Semicolon() is used at the end of every SQL
  command.
• SQL commands are case-insensitive
• SQL commands used in the teradata network work
  with or without semicolon ()
• Select shrcode, shrfirm from shr

34
Select shrcode, shrfirm from shr
35
What to do? Save and upload

• You will save 12 queries and corresponding 12
  outputs as described below. Upload your 24 text
  files to WEEK 5 dropbox.
• Save SQL Statements/Outputs
• On the entering sql command slide, you will
  seeSave Query button. Click it and choose
  save and save it on your disk (a, c, f, or
  g ) for submission to me for grading purpose.
• you need to save each of 12 queries and
  corresponding 12 outputs. Add your last name as
  prefix query number. E.g., the first query is
  saves as eomquery1.txt, the corresponding output
  is saved as eomanswerset1.txt). All the rest of
  the assignments must be saved with different
  numbers).

36
Assignments

• Q1. Get all firms with a price-earnings ratio
  less than 12. Q2. List the firms name, price,
  quantity, and dividend where the holding is at
  least 100,000.
• Q3. Find all firms where the PE is 12 or higher
  and the share holding is less than 10,000.
• Q4. Report firms with code AR.

37

• Q5. Report data on forms with codes of FC, AR or
  SLG (Hint Use IN)
• Q6. List all firms where PE is at least 10, and
  order the report in descending PE (Hint User
  ORDER BY).
• Q7. How many firms are there in the portfolio?
  (Hint Use Count ())
• Q8. How many firms have a holding greater than
  50,000?
• Q9. Find the average dividend.

38

• Q10. List all firms with a name starting with
  F. (Hint use LIKE)
• Q11. List all firms containing Ruby in their
  name. (Hint Use Like and )
• Q12. Find maximum share price.