Transcript BACS 287 - University of Northern Colorado

BACS 287
Structured
Query
Language 1
BACS 287
Visual Basic Table Access

Visual Basic provides 2 mechanisms to
access data in tables:
–
Record-at-a-time access

–
Set-at-a-time access


MoveFirst, MoveLast,MoveNext, MovePrevious
Select, Insert, Update, Delete
Both methods can accomplish the same
thing, but set-at-a-time is usually more
efficient.
BACS 287
Visual Basic Table Access



Set-at-a-time access means that you do not
have to explicitly manipulate the record
pointer or perform a loop.
This creates a non-procedural environment
were you describe what the solution looks
like, not how to do it.
The most popular set-at-a-time language is
called Structured Query Language.
BACS 287
Structured Query Language



Structured Query Language (SQL) is a 4th
generation language designed to work with
relational sets.
Commands exist to create and load tables,
select subsets of tables, and modify existing
tables.
SQL is not a full programming language. It is
intended to be embedded in another, more
traditional, language.
BACS 287
Structured Query Language

SQL commands are divided into 3 primary
groups:
–
–
–

Data Definition Language commands
Data Manipulation Language commands
Data Control Language commands
Visual Basic supports the first 2 categories
very well. It does not have complete support
for the 3rd group.
BACS 287
Data Manipulation Language

Visual Basic DML commands include:
–
SELECT
Retrieve data from a database, create copies of tables,
specify tuples for updating
–
INSERT
Add data to tables
–
UPDATE
Change existing data in tables
–
DELETE
Remove data from tables
BACS 287
Generic Select Statement

By far, the most commonly used DML statement is
the SELECT. It combines a range of functionality
into one complex command. This is the generic
format.
SELECT {field-list | * | DISTINCTROW field}
FROM table-list
WHERE expression
GROUP BY group-fields
HAVING group-expression
ORDER BY field-list;
BACS 287
Select Clauses

SELECT – The list of “columns” to select from the table. Also
allows you to select all (with *), or to remove duplicate rows
(with DISTINCTROW).

FROM - A required clause that lists the tables that the select
works on. You can define "alias" names with this clause to
speed up query input and to allow recursive "self-joins".

WHERE - An optional clause that selects rows that meet the
stated condition. A "sub-select" can appear as the expression
of a where clause. This is called a "nested select".
BACS 287
Select Clauses

GROUP BY - An optional clause that groups rows according to
the values in one or more columns and sorts the results in
ascending order (unless otherwise specified). The duplicate
rows are not eliminated, rather they are consolidated into one
row. This is similar to a control break in traditional
programming.

HAVING - An optional clause that is used with GROUP BY. It
selects from the rows that result from applying the GROUP BY
clause. This works the same as the WHERE clause, except
that it only applies to the output of GROUP BY.
BACS 287
Select Clauses

ORDER BY - An optional clause that sorts the final result of the
SELECT into either ascending or descending order on one or
more named columns.

There can be complex interaction between the WHERE,
GROUP BY, and HAVING clauses. When all three are present
the WHERE is done first, the GROUP BY is done second, and
the HAVING is done last.
BACS 287
Single Table Select Examples
Example 1: Retrieve all information about students
SELECT *
FROM STUDENT;
Example 2: Find the last name, Bear ID, and credits of all students
SELECT LAST_NAME, BEAR_NUM, CREDITS
FROM STUDENT;
BACS 287
Select Examples
Example 3: Select all employees from the 'ACCT' department.
SELECT *
FROM EMPLOYEES
WHERE EMP-DEPT = 'ACCT';
Example 4: Show what salary would be if each employee received a 10%
raise.
SELECT LAST_NAME, SALARY AS CURRENT, SALARY * 1.1 AS PROPOSED
FROM EMPLOYEES;
BACS 287
Single Table Select Examples
Example 5: Find all information about students who are math majors
SELECT *
FROM STUDENT
WHERE MAJOR = 'Math';
Example 6: Find the Bear ID of all History majors
SELECT BEAR_NUM
FROM STUDENT
WHERE MAJOR = 'History';
BACS 287
Enhanced Where Clauses
The WHERE clause can be enhanced to be more selective.
Operators that can appear in WHERE conditions include:
=, <> ,< ,> ,>= ,<=
IN
BETWEEN...AND...
LIKE
IS NULL
AND, OR, NOT
BACS 287
Single Table Select Examples
Example 1: Find the student ID of all math majors with more than 30
credit hours.
SELECT BEAR_NUM
FROM STUDENT
WHERE MAJOR = 'Math' AND CREDITS > 30;
Example 2: Find the student ID and last name of students with between
30 and 60 hours (inclusive).
SELECT BEAR_NUM, LAST_NAME
FROM STUDENT
WHERE CREDITS BETWEEN 30 AND 60;
BACS 287
Single Table Select Examples
Example 3: Retrieve the ID of all students who are either a math or an art
major.
SELECT BEAR_NUM
FROM STUDENT
WHERE MAJOR IN ('Math','Art');
Example 4: Retrieve the ID and course number of all students without a
grade in a class.
SELECT BEAR_NUM, COURSENUM
FROM ENROLL
WHERE GRADE IS NULL;
BACS 287
Single Table Select Examples
Example 5: List the course number and faculty ID for all math courses.
SELECT COURSENUM, FACULTY_ID
FROM CLASS
WHERE COURSENUM LIKE 'MTH*';
BACS 287
Aggregate Function Select

SQL also allows several aggregate functions to appear in the SELECT
line of the SELECT statement. These include:
Max, Min, Avg, Sum, Count, StdDev, StdDevP, Var, VarP.
Example 1: How many students are there?
SELECT COUNT(*)
FROM STUDENT;
BACS 287
Aggregate Function Select
Example 2: Find the number of departments that have faculty in them.
SELECT COUNT(DISTINCTROW DEPT)
FROM FACULTY;
Example 3: Find the average number of credits for students who major in
math.
SELECT AVG(CREDITS)
FROM STUDENT
WHERE MAJOR = 'Math';
BACS 287
Ordering the Select Result
Example 1: List the names and IDs of all faculty members arranged in
alphabetical order.
SELECT FACULTY_ID, FACULTY_NAME
FROM FACULTY
ORDER BY FACULTY_NAME;
Example 2: List names and IDs of faculty members.
SELECT FACULTY_ID, FACULTY_NAME
FROM FACULTY
ORDER BY FACULTY_NAME, FACULTY_ID DESC;
BACS 287
SQL Join Operation

A JOIN operation is performed when more than one
table is specified in the FROM clause. You join two
tables if you need information from both.

You must specify the JOIN condition explicitly in
SQL. This includes naming the columns the two
tables have in common and the comparison
operator.
BACS 287
SQL Join Examples
Example 1: Find the name and courses that each faculty member
teaches.
SELECT FACULTY.FACNAME, COURSENUM
FROM FACULTY, CLASS
WHERE FACULTY.FACID = CLASS.FACID;

Note how the table name is appended to the FACNAME field of
the SELECT clause. This is called qualification. It is required if
the same name is used in 2 tables.
BACS 287
SQL Join Examples
Example 2: Find the course number and the major of all students taught
by the faculty member with ID number 'F110'. (3 table JOIN)
SELECT ENROLL.COURSENUM, LAST_NAME, MAJOR
FROM CLASS , ENROLL, STUDENT
WHERE FACULTY_ID = 'F110'
AND CLASS.COURSENUM = ENROLL.COURSENUM
AND ENROLL.BEAR_NUM = STUDENT.BEAR_NUM;
BACS 287
Views



Views are a way to save your select queries
so that you do not have to build them each
time you need them.
The view saves the procedure (not the result)
for he query.
Views are a “free” form of security
BACS 287
Views



Views are used to simplify queries and to
provide security.
They are often called "virtual tables" because
the table is not stored in the database.
Instead, the procedure to derive the view is
stored.
The view is generated whenever it is
requested, thus it is always up-to-date and
does not take up any disk space.
BACS 287
Views



You build views by first creating a valid select
and then adding one line of code before the
select.
Any valid select can fill in the select portion.
In all cases (except for update) views can be
used in the same was as select statements.
BACS 287
Views
Example: Build a view called CLASS_LIST that
contains the student
IDs and last name for all
students in the class 'ART103A'.
CREATE VIEW CLASS_LIST AS
SELECT BEAR_NUM, LAST_NAME
FROM ENROLL, STUDENT
WHERE COURSENUM = 'ART103A'
AND ENROLL.BEAR_NUM = STUDENT.BEAR_NUM;
BACS 287