Transcript COP 3540 – Introduction to Database Structures CET

COP 2700 – Data Structures - SQL
Lecture 2 – May 20, 2015
Single Table Select Statements
Announcements

May 25 is Memorial Day
–
–
No Classes
Come if you want, but you’ll be sitting outside the
classroom wondering where the heck everyone
else is!!
Tonight





SQLServer Installation and Set-Up
SQLServer basics
Attaching and Detaching Databases
The Little Web Site I did because of Management
Studio “Issues”
Single Table Select Statements
–
–
Read Chapter 4 in the Book
Chapter 1 has a complete definition of the Henry and
Premiere databases.
SQL Server 2008 Installations

https://www.microsoft.com/enus/download/details.aspx?id=30438
–
–
–
Link to Express 2008 R2 Download Page
If you have never downloaded from Microsoft in
the past, you may need to create a account. Use
your Palm Beach State email address as your
user-id
Once you have signed on, select the

SQL Server 2008 Express With Tools (32 or 64 bit
depending upon operating system)
SQL Server 2008 Installation


You will need at least 1 gig of free space to
download and install the software.
I’ve posted a separate document with the
steps to follow once the file has been
downloaded and you start-up the installation.
–
Let’s Go Through It
Some Nomenclature Review

Entities = Nouns
–

Relationships = Verbs
–
–


Entities are Composed of Attributes
How two or more Entities are directly related with each other.
Also may have Attributes that further define the Relationship
Both Entities and Relationships are represented in a Relational
Database System by a Relation.
Relation = Table (Similar to an Excel Spreadsheet)
–
One specific instance of a Relation is a Row in the Table

–
Sometimes called a Tuple
Attributes of a Relation are called Columns or Fields

Attributes in an RDMS additionally have a data type and a range of
acceptable values
So What The Heck are we going to call
these “things”





We’ll call the collection of related data a Database.
The Database will be comprised of Tables that
represent the Entities and Relationships of the data.
The Attributes of each Table will be called Columns
One instance of a Table will be called a Row
Databases then will be comprised of one or more
Tables. Each table is defined by its Columns and
contains zero or more Rows of information.
SQL Server Database Files

SQL Server stores database tables into specially
named files:
–
–
–
DatabaseName.MDF holds the actual data for the
database.
DatabaseName_log.LDF holds log records of all changes to
the database.
These two files by default are stored in:

–
–
C:\Program Files\Microsoft SQL
Server\MSSQL10_5.InstanceName\MSSQL\DATA
But you can tell SQLServer to put the files wherever you
would like when the database is created.
You can also move them someplace else by detaching,
moving and re-attaching
SQL Server Database Files

SQL Server also has several of its own databases
–
–
Called Meta-Databases
Basically databases of your databases



Defines the database, tables, columns, field types, views and
everything else in the database.
If you don’t know what a database file name is DON’T TOUCH
IT. You’ll probably break SQLServer.
This includes
–
Master.MDF, MSDBData.MDF, tempdb.MDF and model.mdf and
their associated LDF files.
Attaching and Detaching Databases




I’ve created the databases we will be using in class and saved
them out on our little web site.
Download them, unzip them to your thumb drive. Then you can
attach the MDF files to the SQLServer instance on the
workstation in class, or at the lab or at your home.
If you want to screw up the database, yank the thumb drive out
of the PC before detaching the database and asking to remove
the drive!! (You can always go back to the originals anyway!!)
If the attach command gives you an error, you may need to
change the Security of the files so that SQLServer is allowed to
access them.
–
I’ll do one now to show you.
Let’s Play Around with SQL Server
Management Studio a little bit.



Signing in to a connection
Displaying the databases, tables, columns,
views, etc.
Selecting a database to query and starting a
new query
Little SQLTool Web Page



Because I was having some “issues” with getting
SQLServer set up correctly in class, I wrote a quick
and dirty web page that can run Select queries.
Right now tables and views for Henry and
Registration have been set up and the Premier
database will be set up shortly.
The web page can be found at
–


www.wodwhere.com/cop2700/sqltool.aspx
Let’s Take a Look
Finally, let’s start doing something!!
Basic SELECT Statement


Is one of the commands available in an
RDMS Data Manipulation Language (DML)
Answers a Question
–

Who, What, How Many, Where
Returns a Table that is called a Result Set.
(All other DML statements return a success
or failure and/or the number of records
modified.)
Let’s Query one of our Databases
(Basic Select Statement)
SELECT {List of Fields or Expressions or * for
all fields}
FROM {Tables or Views}
JOIN {Links between Tables and Views}
WHERE {Conditional Expressions}
GROUP BY {List of Fields}
HAVING {Another Conditional Expression}
ORDER BY {Another List of Fields}
Let’s Play with the Registration
Database



We have three databases available to play with
(Registration, Henry and Premiere). What we will be
doing is using two of them in class, and then have
our homework assignment based on the third. For
tonight we will be using Registration in class, and
Henry for homework.
Attach it, request a New Query and make sure you
are pointing to the Registration database.
Let’s Review the Tables and Columns for the
database.
Registration Database
And Let’s Ask Some Questions

List all the attributes for the Course entity:
–

SELECT * FROM Course
List only the Last Name and City from the
Student Table
–
SELECT Last_Name, City FROM Student
What the heck is an Expression



An Expression is a series of constants,
function calls and/or columns that are
evaluated and then displayed as a single
column in a Select clause’s Result Set.
Different rules are applied based on the type
of column/constants used in the expression.
(The right side of an algebraic formula?)
Simple Expression

Without any database columns
–
Select 3+4*5



Notice, no Table name in this case. Only constants.
How is this evaluated?
Is it the same as
–

Select (3+4) * 5
With database columns
–
SELECT Course_Name, Credit_Hours * 16
FROM Course
Expressions

The constants and columns used in an Expression must all
have the same basic data type.
–
SQLServer has many different data types, but they can be boiled
down into four basic types




–
For more information check out:

–
Numeric (precise and approximate)
Character/String (value surrounded by single quotes)
DateTime (value in a specific format for expressions)
Binary (probably won’t use in a expression. Used to store pictures,
programs, anything that can be represented as a binary file.)
http://www.teratrax.com/sql-server-data-types-ranges/
We will go into more detail on these types when we play with
defining and creating databases.
Expressions

One thing we may want to do, is to concatenate two
or more strings together.
–
–
In SQLServer the concatenation symbol is the “+” sign
List the Name and city of all Instructors using the format
“LastName, FirstName” for the name.

–
SELECT Last_Name + ‘, ‘ + First_Name, City FROM Instructor
What happens if we try to combine data types?

SELECT Last_Name + Birth_Date, City FROM Student
–
It goes Kaboom. Will be going over how to “Cast” one data type
as another next week.
Column Aliases

SELECT Course_Name, Credit_Hours * 16
FROM Course
–
–

Notice what happens when we run this in
Management Studio – the calculated column has
a weird column name.
Let’s use a Column Alias to make it more
understable.
SELECT Course_Name, Credit_Hours * 16
as Lecture_Hours FROM Course
Some Practice



List the Semester, Course_ID, Section and
Course Time from the Schedule table.
List the Last_Name and First Name
combined into one column named “Name”,
Birth_Date, City and Student_Id from the
Student Table.
List all information in the Instructor table.
What happens when a werewolf
“bytes” old St Nicholas??
You Get a Where Clause
Where Clause



Series of conditional statements that evaluate to True or False
Can be connected with AND/OR
Can be logically defined with parenthesis
WHERE City = ‘Miami’
WHERE City = ‘Miami’ OR City = ‘Ft Lauderdale’
WHERE City = ‘Miami’ OR City = ‘Ft Lauderdale’
AND Gender = ‘M’ (what does this one do?)
WHERE (City = ‘Miami’ OR City = ‘Ft Lauderdale’)
AND Gender = ‘M’ (Is this the same?)
WHERE City = ‘Miami’ OR City != ‘Ft Lauderdale’
WHERE City = ‘Miami’ OR City <> ‘Ft Lauderdale’
WHERE City = ‘Miami’ AND City = ‘Ft Lauderdale’
(What does this one do??)

As an FYI. The default in SQLServer is that capitalization of data does not matter, so
unless you specifically install SQLServer requiring conditional statements to differentiate
between capitalization, ‘Miami’, ‘MIAMI’ or ‘miami’ will all match.
Our Special “Everything” Table
(OK – It’s really a View )
Registration View – What courses were taken by which students
and who taught them. A “View” is a fake table created for security
or ease of use reasons that is a “compiled” select statement.


















FIRST_NAME
LAST_NAME
GENDER
CITY
BIRTH_DATE
SEMESTER
COURSE_ID
SECTION
COURSE_NAME
CREDIT_HOURS
Course_City
CLASS_TIME
ROOM
INSTRUCTOR_ID
Instructor_First_Name
Instructor_Last_Name
Instructor_City
OFFICE
String
String
String
String
Date
String
String
Number
String
Number
String
String
String
String
String
String
String
String
50
50
1
50
10
10
10
18
50
18
50
20
10
10
50
50
50
10
Let’s use it to answer these questions


















FIRST_NAME
LAST_NAME
GENDER
CITY
BIRTH_DATE
SEMESTER
COURSE_ID
SECTION
COURSE_NAME
CREDIT_HOURS
Course_City
CLASS_TIME
ROOM
INSTRUCTOR_ID
Instructor_First_Name
Instructor_Last_Name
Instructor_City
OFFICE
String
String
String
String
Date
String
String
Number
String
Number
String
String
String
String
String
String
String
String
50
50
1
50
10
10
10
18
50
18
50
20
10
10
50
50
50
10




Display all Instructor_IDs that live in
‘Miami’.
Display all Instructor_IDs that live in
‘Miami’ and ever taught a class in ‘Boca
Raton’.
Display all Instructor IDs, First_Names
and Last_Names that live in ‘Miami” and
ever taught a class in ‘Boca Raton’.
Display all Instructor IDs, First_Names
and Last_Names, Course_ID and
Semester where the Instructor lives in
‘Miami” and the class was taught in
‘Boca Raton’.
IN

Allows a user to list a set of possible values in a WHERE
clause.
–
List of Courses taught by Bradley or Tondo using OR
SELECT Course_ID, Instructor_ID, Semester
FROM Schedule
Where Instructor_ID = 'Bradley‘ or Instructor_ID = ,'Tondo‘
–
Same Query using IN
SELECT Course_ID, Instructor_ID, Semester
FROM Schedule
WHERE Instructor_ID IN ('Bradley','Tondo')
Between

Allows one to specify a from and to boundary
(inclusive) for a WHERE clause
List all courses with a Schedule_ID between
“20150901” and “20150903'
SELECT Course_ID FROM Schedule
WHERE Schedule_ID BETWEEN '20150901' AND
'20150903'
Distinct

SELECT instructor_id FROM schedule
–

Instructors IDs will be repeated
SELECT DISTINCT Instructor_Id FROM
Schedule
Aggregates

Sum, Max, Min, Count(*), Avg Functions
Others also available for statistics and analysis
How Many Courses are offered this semester:
–
SELECT Count(*) FROM Schedule WHERE Semester = 'SPRING2014'
What is the highest credit hours for any course:
SELECT MAX(Credit_Hours) FROM Course
Group By

Uses the Aggregates to get Subtotals of partitioned
data
How many courses is each instructor teaching this semester?:
SELECT Instructor_ID, Count(*) FROM Schedule
WHERE Semester = 'FALL2014'
GROUP BY Instructor_ID
The Group By must include ALL fields in the result
set that are not aggregated.
Now You

How many courses are offered by Semester
and Course_City.

How many credit hours has each student
taken or is taking? List the Student_ID, Last
Name, First Name and number of hours.
Use the Registration view.
Scalar Selects

Selects that return a single value and can be used in a where clause with an
equal or not equal
List all courses with the highest number of credit hours offered:
First, let’s get the highest number of credit hours:
SELECT MAX(Credit_Hours) FROM Course
That’s the Scalar (Returns One Column and One Row!!)
Now use that as the right side of a condition in a WHERE clause
SELECT Distinct Course_ID, Section, Semester
FROM Registration
WHERE Credit_Hours =
(SELECT MAX(Credit_Hours) FROM Course)
Any idea why the parenthesis?
Order By


Is done AFTER the query is complete.
Orders the resulting table by the fields
specified
Count (Distinct Expression)


Used to count the aggregate of an expression without
duplicates:
How many courses has each student taken?
SELECT Student_ID, Count(*)
FROM Registration
Group By Student_ID
Order By Student_ID
SELECT Student_ID, Count(Distinct Course_Id)
FROM Registration
Group By Student_ID
Order By Student_Id
Dates

Jan. 1, 1753 B.C. through Dec. 31, 9999 A.D.
–


GetDate() = Current system date and time
Date Arithmetic uses Functions to return a result
–
–

Newer data types support Jan 1, 0001
DateAdd(interval, increment, date or column)
DateDiff (interval, starting, ending)
To use in a WHERE clause, always format the date as ‘DDMMM-YYYY’ to ensure the correct date is selected
–
SQLServer also has functions that can be used when specifying a
date or time that use specific formats. We’ll check out Cast and
Convert later.
Dates

List the student last names, gender and age
for all students:
–

SELECT Last_Name, Gender,
Datediff(YEAR,Birth_Date, GetDate()) FROM
Student
List all Student Last Names, First Names that
were born after January 1, 1980
Like – Wildcards for Strings

% - Matches any number of characters
–

_ - Matches one character
–

SELECT Instructor_Id, Semester FROM Schedule WHERE Semester Like
'F___2015'
Escape Character (so you can look for % and _)
–
–

SELECT Instructor_Id, Semester FROM Schedule WHERE Semester Like
'%2015'
Select Instructor_Id, Semester FROM Schedule WHERE Semester Like
'Fall!_2013' Escape '!'
Would find all Instructor Ids and Semesters where the Semester was like
“Fall_2013‘
Note…I use the “%” sign ALL THE TIME, but I think I may have used
the underscore once, and I have never done the Escape thing. So,
remember the percent sign. Remember you saw these other things
when you’re next boss asks about them.
Nulls


Nulls are NOT spaces and is NOT blank. Nulls, the column
never had anything inserted into it. Blank, the column was used
in an insert with a value of ‘’.
Nulls many times make it difficult to find data if you are not
aware that they exist.
–
Select * from Transcript Where Grade != 'A'
Will not return any transcript records where the Grade is null. If
you want them as well, must specify
–

Select * from Transcript Where Grade != 'A' OR Grade is NULL
Notice the special syntax for NULL conditionals
Concatenation and String Functions

Substring(Expression,Start,Length)
–

CharIndex(Compare, Expression)
–

returns an integer of the first occurrence of Compare in the
string Expression
+ (Concatenation) (We did this already)
–

returns a string starting at character Start for
“Add” two or more strings together. There is also a Concat
Function that is in the SQL Standards.
For more String functions see
–
https://msdn.microsoft.com/en-us/library/ms181984.aspx
Assignment 1



Worth 10 points with a 1 point bonus.
Will be due June 1 by 11:59 PM.
Follow the instructions on the Assignment
document.
Assignment 1 – The Henry View
BOOK_CODE
TITLE
TYPE
PRICE
PAPERBACK
PUBLISHER_CODE
PUBLISHER_NAME
BRANCH_NUM
BRANCH_NAME
BRANCH_LOCATION
AUTHOR_NUM
AUTHOR_LAST
AUTHOR_FIRST
ON_HAND
String
String
String
Numeric
String
String
String
Numeric
String
String
Numeric
String
String
Numeric
4
40
3
4
1
3
25
2
50
50
2
12
10
2

To be able to do more complicated
Single Table Select statements, I have
created a view that links all the tables
together called Henry.