New Mexico State University



SQL Fundamentals[1] – Overview

Prepared by Jennifer Kreie, Associate Professor

Accounting & Information Systems Department

College of Business, New Mexico State University

The purpose of this set of Power Point slides and videos is to introduce SQL fundamentals using the Oracle database software and Oracle’s SQL Developer as the interface for creating and running SQL commands. Most of the SQL statements presented in this series are generic and not vendor-dependent and, therefore, the SQL works with other DBMS software such as Microsoft SQL Server.

In addition to the SQL presented in this series, you can also learn several features of SQL Developer.

Databases used in this SQL series: Student -Teams (S-T)

Oracle sample database schema: Human Resources (HR)

Topics covered:

1 /15 SQL Fundamentals – Introduction.

2 /15 SQL Fundamentals – Create an account in Oracle XE.

3 /15 SQL Fundamentals – Create tables and populate with data (Student-Teams database).

4 /15 SQL Fundamentals – Overview of the oracle sample database: human resources.

5 /15 SQL Fundamentals – SELECT … FROM.

6 /15 SQL Fundamentals – SELECT … FROM … WHERE.

7 /15 SQL Fundamentals – Logical operators in the WHERE clause (AND, OR, NOT).

8 /15 SQL Fundamentals – Inexact matching and between a range of values.

9 /15 SQL Fundamentals – IN and NOT IN, NULL and NOT NULL.

10 /15 SQL Fundamentals – Sorting output, column aliases and calculated columns.

11 /15 SQL Fundamentals – Using two or more tables in a query: JOIN and WHERE.

12 /15 SQL Fundamentals – Aggregates.

12a /15 SQL Fundamentals – Aggregate COUNT – Examples of COUNT(*), COUNT(), and COUNT(DISTINCT )

13 /15 SQL Fundamentals – Aggregates with the HAVING clause

14 /15 SQL Fundamentals – Self-joins and table aliases.

15 /15 SQL Fundamentals – Traditional set operators.

SQL Fundamentals – Introduction.

← ANSI

← SQL

◦ DDL

◦ DML

← DDL

◦ CREATE TABLE

◦ Define columns

◦ Define constraints

← Databases used in this SQL series

SQL Fundamentals – Create tables and populate with data (Student-Teams database).

← How to create the Student-Teams database in your account.

← DDL

CREATE TABLE (Build tables and relationships)

← DML

INSERT INTO (Populate tables with data)

SQL Fundamentals – SELECT … FROM.

← SELECT … FROM …

← SELECT * FROM …

← SELECT Count(*) FROM …

← ..

SQL Fundamentals – SQL Server Management Studio Overview.

← Object Navigator pane

← Database Diagram

← Query Editor Window

← Context-sensitive menu

SQL Fundamentals – SELECT … FROM … WHERE.

← SELECT … FROM … WHERE

← Using the WHERE clause to filter output.

← Sometimes no output is the correct output.

← Combine the count(*) function with a filter in the WHERE clause.

SQL Fundamentals – Logical operators in the WHERE clause (AND, OR, NOT).

← Logical operators in the WHERE clause.

◦ AND

◦ OR - It is best to use parentheses around OR conditions.

◦ NOT

SQL Fundamentals – Inexact matching and between a range of values.

← Evaluating inexact matches in the WHERE clause using LIKE and %

← Evaluating a range of values with BETWEEN … AND.

SQL Fundamentals – IN and NOT IN, NULL and NOT NULL.

← More operators to use in the WHERE clause.

◦ IN ( … ) - an alternative to using OR.

◦ NOT IN

◦ IS NULL

◦ IS NOT NULL

SQL Fundamentals – Sorting output, column aliases and calculated columns.

← Sorting query output using ORDER BY.

← Using column aliases to make easy-to-read column headings,

← Using calculated columns.

◦ Concatenating text columns into a single display column.

◦ Calculating values and displaying them.

SQL Fundamentals – Using two or more tables in a query: JOIN and WHERE.

← How to use more than one table in a query.

← Joining tables

◦ WHERE clause

◦ JOIN operator in the FROM clause

SQL Fundamentals – Aggregates.

← How to group data using the GROUP BY clause.

← Some aggregates functions

◦ COUNT() and COUNT(distinct)

◦ SUM()

◦ AVG()

◦ MIN()

◦ MAX()

SQL Fundamentals – COUNT(*), COUNT(), COUNT(DISTINCT )

SQL Fundamentals – Aggregate data and the HAVING clause.

← HAVING clause for aggregate values

◦ Filter output based on aggregate value criteria.

← HAVING versus WHERE criteria

◦ Aggregate versus detail (row by row) criteria

SQL Fundamentals – Self-joins and tables aliases.

← Unary relationship

← Self-join in a query

← Table aliases

← Joining the same two tables twice in a query

SQL Fundamentals – Traditional set operators.

← Set Operators

o UNION

o INTERSECT

o EXCEPT

← UNION compatible rule

-----------------------

[1] You’re welcome to use this material with a reference to the source included.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download