SQL Exercise using Teradata Student Network - PowerPoint PPT Presentation

Loading...

PPT – SQL Exercise using Teradata Student Network PowerPoint presentation | free to view - id: 429e30-MWM0Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

SQL Exercise using Teradata Student Network

Description:

SQL Exercise using Teradata Student Network Sean Eom MI375 MI410 Before the start of this assignment You are expected to read the power point file (Intro to sql) from ... – PowerPoint PPT presentation

Number of Views:295
Avg rating:3.0/5.0
Slides: 39
Provided by: eom
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: SQL Exercise using Teradata Student Network


1
SQL Exercise using Teradata Student Network
  • Sean Eom
  • MI375 MI410

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 Screen Notice 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 says 3706
    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
    see Save 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.
About PowerShow.com