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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- introduction claros data management hach
- the oracle database commonly referred to as oracle rdbms
- new mexico state university
- first steps towards oracle 10g
- oracle secure enterprise search implementing search
- simple and advanced queries
- solutions chapter 1 sql and data test your thinking
- sqlaccess advisor
- sql interview questions for software testers
Related searches
- gadsden new mexico school district
- anthony new mexico school district
- new mexico blm maps online
- gadsden new mexico isd jobs
- affordable new mexico elk hunts
- new mexico professional engineer lookup
- new mexico cna endorsement form
- new mexico legalization 2020
- new mexico engineering license lookup
- new mexico marijuana legalization 2019
- new mexico state auditor
- new mexico marijuana law 2019