IS6030: Week 2

SQL Home Page

Housekeeping

1. Questions from Week 1
2. Assessment
3. Answers to Homework 1

Lecture Part 1: The SQL Query

1. Intro
• The relational model

Participation: what is a relational database?

• What is a query?

A query instructs SQL precisely what results to display, almost always from a table or view.

• What does a SQL table look like?

Data are organized as records with fields. There are no row names, and fields are represented as columns.

2. SELECT
• The SELECT statement

It can be used by itself (rare) or with a table/view (most cases). It is required for all queries.

• SELECT *

The asterisk * is used in SQL to refer to all records. SELECT * returns all records.

• SELECT TOP 10

You can specify a certain number of records to be returned with the TOP keyword.

• Point-and-Click: "Select Top 1000 Rows"

Break

Lecture Part 2: The SQL Query, Continued

1. FROM
• The FROM clause

Directs SQL to the table to use for the query.

• Referring to a table
1. A table is contained with a schema, a schema is contained within a database, and a database is contained on the server. Sometimes you must refer to all these things in the FROM clause, and other times you do not. Best practice is to refer to database name, schema name, and then table.
2. We use the period to navigate down the hierarchy: database.schema.table
3. Brackets are required for special characters: [database name].schema.[table name]
2. Clauses
• What are clauses?

They qualify the query.

• The fantastic four:
1. WHERE filters results for individual records
2. GROUP BY allows aggregate functions across fields.
3. HAVING filters grouped results contained in the GROUP BY
4. ORDER BY sorts results

In-Class Assignment

Click here for SQL Script. Once completed, submit via Blackboard. Due 11/09/2018 by 1:00PM.

Homework

Click here for SQL Script. Once completed, submit via Blackboard. Due 11/09/2018 by 1:00PM.

For Next Class

1. Review this week's material
2. Read Itzik Ben-Gan:
• Review Week 1 reading on Predicates and operators, CASE expressions, and NULLS: pgs. 49-58
• Working with character data, pgs. 60-65
• Working with date and time data, pgs. 73-78
3. As you know, Week 3 will begin with an assessment that covers material from this week.
4. The midterm will occur during Week 4 (November 17-November 24). Because many are out of town, I plan to have you take the midterm on your own time. It will be timed such that when you open it, you will have an hour or so to complete it (still tentative on amount of time to allow)