Database Design Sql Coding **finish

Front 1

SQL Function Categories

Back 1

- Data Definition Language

- Data Manipulation Language

- Database Control

Front 2

Creating tables

Back 2

CREATE TABLE tableName (

field1_name data_type [constraint],

field2_name data_type [constraint],

);

Front 3

Primary/Foreign Keys

Back 3

...

PRIMARY KEY (field1_name),

FOREIGN KEY (field2_name),

REFERENCES (tableName2)

);

Front 4

SQL Constraints

Back 4

NOT NULL = duh

DEFAULT = Assigns a value to the attribute when row is created.

CHECK = Validates data when a value is entered into attribute

Front 5

Inserting Values

Back 5

INSERT INTO TableName VALUES (field1_value, field2_value);

Front 6

Literal

Back 6

A constant used in SQL statements. Char's are enclosed in ' ' while numbers are not.

Front 7

Select Statement

Back 7

SELECT

field_name1,

field_name2

FROM

TableName

;

Front 8

DISTINCT

Back 8

Eliminates duplicates. Placed after SELECT statement. (SELECT DISTINCT fieldName1)

Front 9

Pattern Matching

Back 9

Enclose the term to find in % symbols. eg: WHERE

Address

LIKE

'%Sydney%'

;

Front 10

Update records

Back 10

UPDATE statement.

UPDATE TableName

SET

columnName1 = dataValue1

;

Front 11

Delete records

Back 11

DELETE FROM TableName;

Does not delete table, only records.

Front 12

Multi-Table Queries

Back 12

Must use a cartesian product to join the tables. WHERE A = B and

B = C and

...

;

Front 13

Aggregate Functions

Back 13

Includes: COUNT, SUM, AVG, MIN, MAX

Select list must either be in an aggregate function or in a GROUP BY clause.

Front 14

View

Back 14

Dynamic result of one or more relations operating on the original relations to produce a new relation (table). Is a logical or virtual table.

CREATE VIEW ViewName AS (regular select commands etc) ;

Front 15

Sub Queries

Back 15

A query inside a query. Typically the result of the inner query (eg. SELECT) is used as the criteria for a WHERE or HAVING clause.

Front 16

You can use ORDER BY in a sub query

Back 16

False. Only in the outer SELECT.

Front 17

You can name multiple columns in a SELECT sub query

Back 17

False. Can only name one column.

Front 18

Sub query in a Comparison can be used on both sides.

Back 18

False. Can only be on the right hand side.

Front 19

SOME and ALL

Back 19

SOME - Returns true if at least one value satisfies criteria.

ALL - Returns true if all values satisfy critera.