Ecpi - Cis223 - Sql Database Week 1

Front 1

What is a database?

Back 1

–Data that is being collected for record-keeping & analysis

–Managed by a database management system (DBMS)

Front 2

What does DBMS stand for?

Back 2

Data Base Management System

Front 3

What are some common examples of a database?

Back 3

• MS Access
  
• MS SQL Server
  
• Oracle, Sybase
  

Front 4

Why are databases so important?

Back 4

–Amount of electronically available data is exploding

–Cost of storage is continuously dropping

NOTE: Moore’s law: every 18 months, speed of processor doubles, price goes down by half

Front 5

Who owns the largest databases?

Back 5

• Federal Express
• Wal-Mart
• UPS

NOTE:

• 1 Billion Rows
• Approaching 10s of TB of data

Front 6

What is DBMS?

Back 6

Database Management System

–Collection of programs that allows users to create a new database and specify structure

–Gives users the ability to query and modify the data efficiently

–Keeps the data secure from accidents or unauthorized use

–Controls the access to the data for many users at once

Front 7

Who created the modern DBMS and in what year?

Back 7

Ted Codd invented the relational data model and the concept of abstraction in the early 1970's

Front 8

Who invented SQL?

Back 8

A team from IBM

NOTE: This became the defacto standard for query languages based on the relational data model.

Front 9

What is SQL?

Back 9

Structured Query Language (SQL)

It is a computer language designed to get information from data that is stored a relational database.

Front 10

What does SQL describe?

Back 10

The information you want.

Front 11

What is the most successful declarative language?

Back 11

SQL

Front 12

What is a declarative language

Back 12

This is where the programmer specifies what needs to be done rather than how to do it.

Front 13

What is DDL?

Back 13

Data Definition Language

–A DDL Statement is used to create structures in a database

–Create table of employees

Front 14

What is DML?

Back 14

Data Manipulation Language

Command to retrieve actual data (aka queries)

Front 15

What is a host language?

Back 15

General-purpose programming language which lets users embed DML commands

EXAMPLE

• Java
• C
• C++
• Visual Basic

Front 16

How is information presented in a relational DB?

Back 16

Information is presented as sets of tables

Front 17

What is the format of Select Clause?

Back 17

SELECT columnnames

FROM tablenames

WHERE somecondition

ORDERBY somecolumn

Front 18

How do I select all items from a table?

Back 18

*

Front 19

How do I output only one of each item in a column?

Back 19

Select distinct ColumnName

The word distinct is the word that will sort out duplicates

Front 20

How do you rename a colum,or give it an alias?

Back 20

Select ColumnName as NewNameHere

as means to rename something

Front 21

What commands can be used in a where clause to define the output of the table?

Back 21

• =
• <
• >
• <=
• >=
• <>
• IN, NOT IN
• BETWEEN, NOT BETWEEN
• LIKE, NOT LIKE
• IS NULL, IS NOT NULL
• AND, OR, NOT

Front 22

In a where clause, what does the statement "and" mean?

Back 22

The statement A and B is true only when both A and B are True

Front 23

In a where clause, what does the statement "or" mean?

Back 23

The statement A or B is true when either A or B is true

Front 24

In a where clause, what does the statement "not" mean?

Back 24

The staement not A is true when A is false

Front 25

Can you use multiple where statements?

Back 25

Yes

EXAMPLE

SELECTemployee_idfrom l_employeesWHEREdept_code= ‘SHP’ AND credit_limit> 15.00

Front 26

What do commas do in SQL?

Back 26

Commas Separate items in of a list.

NOTE: A list cannot end in a comma

Front 27

Can you use commas , or dollar $ signs when entering numbers?

Back 27

No

Front 28

What are single quotes ' in SQL?

Back 28

These should be used to surround text strings and dates.

NOTE: Do Not use quotes around numbers

Front 29

What is a semicolon ; in SQL?

Back 29

This marks the end of an SQL statement

Front 30

What is a double dash -- in SQL?

Back 30

A comment line

NOTE: You can add information that will make it easier for others to understand your formula

Front 31

How do you write an apostrophy ' in SQL?

Back 31

'' place two apostrophy's next to each other

EXAMPLE

SELECT* FROM l_suppliersWHERE supplier_name= ‘JIM PARKER’’S SHOP’

Front 32

What is a period in SQL?

Back 32

A period is used between a schma name, table name, and column name

EXAMPLE

SELECT.l_employees.first_nameFROM l_employeesm

Front 33

What is a double bar in SQL ||

Back 33

This is a concatenation operator

Front 34

What is a concatenation operator?

Back 34

These can combine two or more characters or binary strings, column names into one expression

EXAMPLE

SELECTlast_name|| ‘,’ || first_name AS full_nameFROM l_employees

~ In this example we are combining the first and last name into one column for display

Front 35

What is a wildcard?

Back 35

This is represented by % or _

Front 36

What is the % in SQL?

Back 36

This is a wildcard used to represent any number of characters to all, helps define a search criteria

EXAMPLE

If I wanted to find all the words that had abc listed somewhere in them I would use the % sign

Front 37

What is the _ in SQL?

Back 37

This is a wildcard used to represent exactly one character.

EXAMPLE

I need to find all the states that begin with the letter A, I would use _ symbol