Business intelligence systems (BI)
-information systems that assist managers and other professionals: –Assessment –Analysis –Planning –Control
Ad-Hoc Queries
-Questions that can be answered using database data
Example: “How many customers in Portland, Oregon, bought our green baseball cap?”
–Created by the user as needed, instead of programmed into an application
–Common in business
SQL as a sublanguage
SQL is not a full-featured programming
language.
–C, C#
- SQL is a data sublanguage for creating and processing database data and metadata.
- SQL programming is a critical skill.
SQL Categories
1) Data definition language (DDL)
2) Data manipulation language (DML) statements
3)SQL/Persistent Stored Modules (SQL/PSM)
statements
4) Transaction control language (TCL) statements
5) Data control language (DCL) statements
Data definition language (DDL)
Used for creating tables, relationships, and other structures
Data manipulation language (DML)
Used for: ▪Queries – SQL SELECT statement ▪Inserting data – SQL INSERT statement ▪Modifying data – SQL UPDATE statement ▪Deleting data – SQL DELETE statemen
SQL/Persistent Stored Modules
SQL/PSM
-Add procedural programming capabilities
▪Variables
▪Control-of-flow statements
Transaction control language (TCL)
-Used to mark transaction boundaries and
control transaction behavior
Data control language (DCL)
Used to grant (or revoke) database permissions to (from) users and groups
SQL SELECT Statement
The fundamental framework for an SQL
query is the SQL SELECT statement.
=Selects the name of the column wanted
–SELECT {ColumnName(s)}
–FROM {TableName(s)}
–WHERE {Condition(s)}
•All SQL statements end with a semicolon(;).
SQL FROM Statement
–FROM {TableName(s)}
=on what tables are the columns we selected
SQL WHERE Statement
–WHERE {Condition(s)}
=Specify the criteria needed on the columns
SQL Asterisc (*)
-All the columns of the table
SQL (DISTINCT) keyword
- Added after SELECT and before the column name to erase all the extra copies associate to a term or name.
- ONLY one from each, no repetitions
SQL TOP {percentage} PERCENT statement
-Organizes all the data based on the established top percentage
SQL ORDER BY Statement
- sorts table rows from lowest to highest (ascendant)
- can be specified to be Descendant
a) ASC (ascendant)
b) DESC (Descendent)
Wildcards SQL
-Used after LIKE and NOT LIKE operator
types (caracteres, no numeros): 1) SQL underscore (-): Sustituir un solo caracter
represents a single, unspecified character in a specific position in the character string.
ej: Casa_ -> significa que puede buscar casa 1,2...2)SQL percent sign (%): Sustituir varios caracteres
represents any sequence of contiguous,
unspecified characters (including spaces) in a
specific position in the character string
ej: 'Pete%' -> puede ser Pete con cualquier apellidoNull operator
-Determinar si hay valores ausentes o no los hay
( IS NULL)
*Si está vacio significa que no fue incluido en el catalogo
SQL IN Operator
- Put after WHERE statement.
IN= is equal to one of a set of values
Aggregate functions
Put after SELECT function and with parenthesis the title SELECT SUM ( ) AS New Table
11
SQL Built-in Aggregate Functions
COUNT(*)= Count the number of rows in a table
COUNT ({Name}]= Count the number of rows in the table where column {Name} IS NOT NULL
SUM =Calculate the sum of all values (numeric columns only)
AVG =Calculate the average of all values (numeric columns only)
MIN =Calculate the minimum value of all values
MAX =Calculate the maximum value of all values
*aggregate functions operate
on columns.
CONCAT (+)
-añadir un caracter o grupos de caracteres
HAVING clause
-need to filter the result of an aggregate function.
*HAVING ES UN CRITERIO QUE SE LE APLICA AL
GRUPO
