online exsam
fadoo
module_9_part_2_-_databases.pdf
1 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering
CS1160 Intro to Programming
CS1150
Introduction to Computer Science
Module 9 Part 2 – Databases
Instructor: Kera Watkins 479 Joshi Research Center 937-775-5138 [email protected]
CS1150 Intro to Computer Science Sources: Jones & Bartlett Learning; Nell Dale and John Lewis – Computer Science Illuminated; Karen Myers – Wright State University; Vance Saunders – Wright State University
2 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering
CS1160 Intro to Programming
CS1150
Introduction to Computer Science
Goals
l Establish relationships among elements in a database
l Practice writing basic SQL statements
2
3 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering
CS1160 Intro to Programming
CS1150
Introduction to Computer Science
Database Table Relationships
l Clearwater and Northwood are available via SQL Developer
l Color code the attributes related to one another in different tables
l Quick! How many relationships between tables can you find?!!
4 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering
CS1160 Intro to Programming
CS1150
Introduction to Computer Science
Answer the following questions using the Clearwater db sheet. (need to identify table names, attribute names, key
relationships)
1. Which customer(s) live in Georgia(GA)? List by name. Table name is customer, check c_state attribute to locate matching state, returns c_id # 5 (Sheila Lewis)
2. How many customers placed an order on May 2006? a. What are their names?
3. How many 3-Season Tents are left in stock?
4. Which customer (s) ( first and last names) placed orders from the “Web Site” order source?
5. Which specific items of the women’s clothing category were sold between May 29th and May 31st inclusive?
4
5 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering
CS1160 Intro to Programming
CS1150
Introduction to Computer Science
Using Column Aliases
l When column names are not descriptive or calculations are made, it is helpful to alias the column
l After the column specification enter the keyword AS followed by the column alias, may embed in “ “ if it contains spaces
Example:
select os_id as “Order Source ID” from orders;
5
6 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering
CS1160 Intro to Programming
CS1150
Introduction to Computer Science
Group Functions – used to summarize or group records
l SUM
l COUNT
l AVERAGE
l MAX
l MIN
l etc.
eg. SELECT SUM(inv_qoh) AS “Total Inventory” FROM inventory;
6
7 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering
CS1160 Intro to Programming
CS1150
Introduction to Computer Science
Using Oracle SQL Developer
Run the application
Login by clicking on the + sign next to cseora11(below Connections on the left hand side of the screen)
Your username is S1(this is already entered)
Your password is: tiger9
7
8 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering
CS1160 Intro to Programming
CS1150
Introduction to Computer Science
Querying data that requires access to 2 or
more tables(Inner Join)
Syntax:
select column(s) from Table1, Table2 where table1.PK = table2.FK [and
column=search condition];
– [ ] brackets indicate optional clause
– Example: l List the first and last named of the students advised by faculty ID = 1,
(Teresea Marx)
SELECT s_last, s_first FROM student, faculty WHERE faculty.f_id = student.f_id AND student.f_id = 1;
8
9 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering
CS1160 Intro to Programming
CS1150
Introduction to Computer Science
Create the SQL Statements!
1. Which customer(s) live in Georgia(GA)? List by name. Table name is customer, check c_state attribute to locate matching state, returns c_id # 5 (Sheila Lewis)
2. How many customers placed an order on May 2006? a. What are their names?
3. How many 3-Season Tents are left in stock?
4. Which customer (s) ( first and last names) placed orders from the “Web Site” order source?
5. Which specific items of the women’s clothing category were sold between May 29th and May 31st inclusive?
9
10 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering
CS1160 Intro to Programming
CS1150
Introduction to Computer Science
Summary
l Saw how different tables relate to one another
l Practiced writing SQL statements using JOINS and group functions
10
