CH 2- Structured Query

Question 1

Business intelligence systems (BI)

Answer 1

-information systems that assist managers
and other professionals:
–Assessment
–Analysis
–Planning
–Control

Question 2

Ad-Hoc Queries

Answer 2

-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

Question 3

SQL as a sublanguage

Answer 3

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.

Question 4

SQL Categories

Answer 4

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

Question 5

Data definition language (DDL)

Answer 5

Used for creating tables, relationships, and other structures

Question 6

Data manipulation language (DML)

Answer 6

Used for:
▪Queries – SQL SELECT statement
▪Inserting data – SQL INSERT statement
▪Modifying data – SQL UPDATE statement
▪Deleting data – SQL DELETE statemen

Question 7

SQL/Persistent Stored Modules

SQL/PSM

Answer 7

-Add procedural programming capabilities
▪Variables
▪Control-of-flow statements

Question 8

Transaction control language (TCL)

Answer 8

-Used to mark transaction boundaries and

control transaction behavior

Question 9

Data control language (DCL)

Answer 9

Used to grant (or revoke) database
permissions to (from) users and groups

Question 10

SQL SELECT Statement

Answer 10

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(;).

Question 11

SQL FROM Statement

Answer 11

–FROM {TableName(s)}

=on what tables are the columns we selected

Question 12

SQL WHERE Statement

Answer 12

–WHERE {Condition(s)}

=Specify the criteria needed on the columns

Question 13

SQL Asterisc (*)

Answer 13

-All the columns of the table

Question 14

SQL (DISTINCT) keyword

Answer 14

• 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

Question 15

SQL TOP {percentage} PERCENT statement

Answer 15

-Organizes all the data based on the established top percentage

Question 16

SQL ORDER BY Statement

Answer 16

• sorts table rows from lowest to highest (ascendant)
• can be specified to be Descendant

a) ASC (ascendant)
b) DESC (Descendent)

Question 17

Wildcards SQL

Answer 17

-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 apellido

Question 18

Null operator

Answer 18

-Determinar si hay valores ausentes o no los hay
( IS NULL)

*Si está vacio significa que no fue incluido en el catalogo

Question 19

SQL IN Operator

Answer 19

• Put after WHERE statement.

IN= is equal to one of a set of values

Question 20

Aggregate functions

Answer 20

Put after SELECT function and with parenthesis the title
   SELECT       SUM (   )              AS      New Table

11

Question 21

SQL Built-in Aggregate Functions

Answer 21

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.

Question 22

CONCAT (+)

Answer 22

-añadir un caracter o grupos de caracteres

Question 23

HAVING clause

Answer 23

-need to filter the result of an aggregate function.

*HAVING ES UN CRITERIO QUE SE LE APLICA AL
GRUPO