question 4 SQL

___ deletes rows from a table.

DELETE FROM

DROP TABLE

DELETES A TABLE

Descending

DESC stands for ...

|

Default headsep character in SQL plus

DATE_FORMAT()

Displays date/time data in different formats

The UPDATE command cannot be used on a view created with the WITH CHECK OPTION. T/F

FALSE

DISTINCT

Prevents retrieval of duplicate items

INTERSECT

Query pulls values that are common to both tables

Relational Database Management System

RDBMS stands for ...

What is RDBMS and what are some examples?

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

RDBMS

RELATIONAL DATABASE SYSTEM

DELETE query

Removes one or more rows from a table or view

Full Outer Join

Retrieves all rows from 2 tables and associates any related records

Right Outer Join

Retrieves all rows from the right table and any related rows from the right table

Subset Table

Filtered and/or combined table(s)

##

First character of global temp table

#

First character of local temp table

Data type: char(size)

Fixed-length character string. Size specified in parentheses. Max 255 bytes.

char(size)

Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.

FORMAT()

Formats how a field is to be displayed

ABS(x)

Returns the absolute value of x

NOW()

Returns the current date and time

NewID()

Function used to automatically generate a new GUID value

Semicolon ;

Goes at the end of a SQL statement so it will execute

GROUP clause

Groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions

Data Warehouse, Database, Tables, Views

How data is organized in SQL Server

____ adds a new row to a table

INSERT INTO add row to table

SIGN(x)

Returns the sign of input x as -1,0,1

___ is a condition in SQL that returns true when the value is NULL and false otherwise.

IS NULL

WHERE

IS USED TO FILTER RECORDS

ORDER BY

IS USED TO SORT DATA IN ASCENDING ORDER

*

Identifier used to indicate that all columns should be returned in the result set

Mathematical Functions

Includes: +, -, *, /, %

Data Type: Integer

Integer numerical (no decimal). Precision p int

Common data type

Integer, a positive or negative whole number Text, Date, Real (decimal points)

OR

Join two or more conditions in the WHERE clause, at least condition must be true

AND

Joins two or more conditions in the WHERE clause; both conditions must be true

ROUND()

Rounds a numeric field to the number of decimals specified

Where

Keyword used to filter the results returned in an SQL statement

Between

Keyword used to find values that fall inside a designated range

Group By

Keyword used to indicate a column or set of columns in which to bundle values together (and must be used when combining aggregate and non-aggregate values in a select clause)

In

Keyword used to indicate a specified set of values to be compared against

Like

Keyword used to perform a "fuzzy" search and gives us the ability to use wildcards in our search criteria

As

Keyword used to rename a column in a result set

Distinct

Keyword used to return only unique values in a result set

Top

Keyword used to return the first set number or percentage of rows in a result set

Except

Keyword used to return values from the first table that are not present in the second table

Intersect

Keyword used to return values that are common in both (or all) tables

Set

Keyword used with an update statement to indicate which column(s) to update to which value(s)

SELECT table1.column1, table2.column2 FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name

LEFT joint syntax

Record

Row of information

In Oracle11g, a(n) ____ allows a series of DML actions to occur.

MERGE STATEMENTS

SELECT

Main T-SQL statement used to query data

MOD(x,y)

Modulo- returns the integer remainder of x divided by y (same as x%y)

Ex. for INNER Join - FROM customers, items

Most common join "equijoin" or where two tables data are being pulled

Constraint (SQL)

Rule associated with a column that the data entered into that column must follow.

When sorting the results in ascending order, which of the following values will be presented last in the output?

NULL

____ queries data from a table

SELECT

Null Values

NULL values represent missing unknown data. By default, a table column can hold NULL values. If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value. SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL

When sorting the results in ascending order, which of the following values will be presented first in the output?

NUMERIC

TABLE JOIN

SELECT "list-of-columns" FROM table1,table2 WHERE "search-condition(s)"

How to run a query to see the result?

SELECT * FROM celebs;d

SELECT statement (full)

SELECT [ALL | DISTINCT] column1[,column2] FROM table1[,table2] [WHERE "conditions"] [GROUP BY "column-list"] [HAVING "conditions] [ORDER BY "column-list" [ASC | DESC] ]

CREATE TABLE/Database "table name" (col1 constraint1, col2 constraint2, etc)

SQL Statement to Create a new table/database

Case SenSiTive

SQL is NoT Case Sensitive

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.

SQL Server Management Studio

SSMS stands for ...

HAVING

Search condition for a group

Semicolon

Statement terminator

UPDATE

Statement used to update records

DATE_SUB()

Subtracts a specified time interval from a date

UPDATE Statement

The UPDATE statement is used to update existing records in a table. UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value; Notice the WHERE clause in the SQL UPDATE statement! The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

HAVING clause

The WHERE clause for GROUP BY functionality

A multiple-row subquery can be nested in a HAVING clause.

TRUE

A subtype can have a relationship not shared by the supertype. True or False?

TRUE

The IN logical operator is similar to using OR.

TRUE

The most common type of join is an equijoin, which joins two or more tables together based upon the tables having equivalent data values in a common column. T/F

TRUE

When functions are nested, the inner function is solved first. T/F

TRUE

AND

TRUE IF BOTH EXPRESSIONS ARE TRUE

Commenting Character

- -

Data Integrity

The correctness, consistency and uniqueness of data stored in a table.

One-to-Many

The most common relationship type where one record in a table relates to many records in another table

Operators

=, <>, >, <, >=, <=

SQL

A general language used to interact with relational databases

JSON

A technology that uses JavaScript object syntax to store data in name : value pairs in a flat (text) file.

XML

A technology to store information using a tag based syntax (similar to HTML) in a flat (text) file.

Primary Key

A unique identifier for a row

Table Valued Function

A user defined function that returns a result set

Scalar Function

A user defined function that returns a single value

Views

A view is a virtual table.

Alias/Column Aliasing

Allows a column to be renamed for the query output only

Null

An unknown value

Address+

Character allowed as a space for when combining phrases together

NOT BETWEEN

To display the keyword outside the range

Primary Keys

Uniquely identifies each row in a table

Data type: varchar(size)

Variable-length character string. Max size is specified in parenthesis.

float(M,D)

a floating point number that cannot be unsigned, length and number of decimals

Columns

a.k.a. Fields

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

auto increment

A ___ is a set of data values of a particular type. Here id, name, and age are each ---

column

WHERE

create a condition from a table

A correlated subquery is one in which the inner query is executed first, and then the outer query is executed. t/f?

false

Group functions return a group of results per row processed. t/f?

false

LCASE()

function that Converts a field to lower case

FIRST() or TOP 1 + ASC/DESC

function that returns the first value

parameter ex: (column_1 data_type, column_2 data_type, column_3 data_type)

is a list of columns, data types, or values that are passed to a clause as an argument

purchases.customer_info

nomenclature used for naming tables when joins for multiple tables is present

int

normal sized integer that can be signed or unsigned

REFERENCES

points to a different column

DESCRIBE

provides context about the columns in a SQL table

AVG

returns the average value in column

COUNT

returns the count of values in a column

FLOOR(x)

returns the largest integer value that is less than or equal to x

COUNT(*)

returns the number of rows in a table

ASC

sort by ascending (smallest on top)

DESC

sort by descending (largest on top)

UPDATE

statement allows us to alter data in the table

DELETE, where

statement to remove rows from a table

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

syntax for between

SELECT column_list FROM table_name WHERE condition;

syntax for where

how many types of data types are there?

three numeric,data/time,string

SUM, AVG, and COUNT are all considered group functions. t/f?

true

+

used for addition

/

used for division

alter table

used to add, delete, or modify columns in an existing table.

Drop column

used to delete column

DELETE statement

used to delete records or rows from the table delete from "tablename" where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; [ ] = optional

SELECT Statement (def, partial)

used to query the database and retrieve select "column1" [,"column2",etc] from "tablename" [where "condition"]; [] = optional

UPDATE statement (def, statement)

used to update or change records that match a specified criteria update "tablename" set "columnname" = "newvalue" [,"nextcolumn" = "newvalue2"...] where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; [] = optional

A Matrix Diagram will help you with all of the following except:

verifying relationships between entities

_

wildcard used to substitute for any single character, multiple can be combined together

NOT IN

will exclude keywords from a list

Which SQL statement can be executed based upon the view created from the following command, assuming no constraints exist on the underlying table? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;

INSERT INTO prices (title, cost, retail) VALUES ('A NEW BOOK', 49.99, 69.99);

Sum Function

The MIN() function returns the smallest value of the selected column. SELECT MIN(column_name) FROM table_name;

Trigger

A stored procedure that executes automatically

User Defined Function

A stored query that can be called as part of another query which can return a scalar variable or a result set and can take parameters

Subquery

A subquery is a SQL query nested inside a larger query. SELECT a.studentid, a.name, b.total_marks FROM student a, marks b WHERE a.studentid = b.studentid AND b.total_marks > (SELECT total_marks FROM marks WHERE studentid = 'V002');

When you add the concept of time to your data model, you are:

ADDING COMPLEXITY TO YOUR MODEL

If the DISTINCT keyword is not included in the AVG function, the ____ keyword will be assumed.

ALL

_____ changes an existing table

ALTER TABLE

To instruct Oracle11g to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.

ASC

Ascending

ASC stands for ...

INSERT INTO

Add Columns or data to a table INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;

Add a new column 'twitter_handle' to the table in TEXT format

ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) ALTER TABLE Persons ADD UNIQUE (P_Id)

Add constraint

HAVING clause

Allows you to specify conditions on the rows for each group- in other words, which rows should be selected will be based on the conditions you specify needs to follow GROUP BY clause SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list" HAVING "condition";

Synonym

Alternative name for a database object

Lookup Table

An ID and single value

Concatenation

Brings multiple columns together

Avg()

Built-in aggregate function used to calculate the average column value in a result set

Min()

Built-in aggregate function used to find the smallest column value in a result set

Count()

Built-in aggregate function used to return the total number of values in a column (will ignore null values)

Max()

Built-in function used to find the largest column value in a result set

VIEW allows us to

- Structure data in a way that users or classes of users find natural or intuitive. - Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more. - Summarize data from various tables and use it to generate reports

Commenting Character

/* */

Stored Procedures

A collection of lines of code that performs a complex action

Derived Column

A column that is created as a result of an arithmetic calculation or the use of a function

Primary Key

A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly

Table

A container in a database made up of columns and rows to hold data

Foreign Key

A field in a table that references the primary key in another table

Combination Key

A key made up of two or more non-unique fields that when combined are a unique identifier for that row

Data Definition Language

A part of SQL that is used to create and modify objects of a database such as tables, views, functions and stored procedures

Data Control Language

A part of SQL that is used to manage permissions to objects in a database

Data Manipulation Language

A part of SQL that is used to query, insert, update and remove data from a database

Clause

A portion of an SQL command that begins with a keyword

Subquery

A query that is nested inside another query (that could be run independently of the outer query)

Trigger

A special kind of stored procedure that automatically executes when a DML, DDL or logon event occures

TABLE

COLUMNS ROWS UNIQUE IDENTIFIERS AND FIELDS

UNION

COMBINES DATASETS AND REMOVES DUPLICATES

UNION ALL

COMBINES MULTIPLE DATA SETS INTO ONE DATA SET KEEPS DUPLICATES

A(n) ____ is used to indicate how data should relate to a given search condition.

COMPARISON OPERATOR

Combining the contents of two or more columns is known as ____.

CONCATENATION

The ____ function can be used to determine the number of rows containing a specified value.

COUNT

____ creates a new table.

CREATE TABLE creates a new table.

Exclusionary Left Outer Join

Can produce set of records that exist only in Table A ( and not Table B) using a where clause.

Constraint

Can require a range, unique values and/or a maximum number of digits

LIKE

Causes the WHERE condition to match a specified strong pattern

Data Type: Character

Character string. Fixed-length n char(32)

FROM

Choose from a table

WHERE

Clause that filters SELECT * FROM movies WHERE imdb_rating > 8; clause that indicates you want to filter the result set to include only rows where the following condition is true.

FROM clause

Clause used to specify which tables, views, joined tables to be used in the query

UPDATE, set age= age+1 where firstname="Mary"

Clause used with UPDATE where you make an update to a column and then list a condition

___ perform specific tasks in SQL. By convention, __ are written in capital letters. ___ can also be referred to as commands. ex) CREATE TABLE

Clauses

Column set

Combines all sparse columns of a table into a structured output

UNION

Combines results of 2 or more queries into a single result set

CONCAT()

Combines text together

ALTER TABLE

Command used to change a table's structure

CREATE TABLE

Command used to create a table

Not Null

Constraint that specifics a column cannot be empty

TO_DATE ( String, [Format], [Optional Setting] )

Converts a Date to a string

Count(*)

Count(*)

COUNT

Counts the number of occurrences of values returned by a query

SELECT * INTO newtable FROM table1 WHERE 1=0;

Create a new empty table

INTO

Creates a new table in the default file group and inserts the resulting rows from the query into it

CREATE SEQUENCE [name] STARTS WITH [num] ... INCREMENT BY [num]

Creates a sequence with increments

CREATE/DROP INDEX index_name ON table_name (column_name)

Creates or Drops an Index as a search key

DROP table "tablename"

Delete a table to start from beginning

CREATE TABLE table_name ( column_1 data_type, column_2 data_type, column_3 data_type);

SQL statement that CREATEs TABLE

SQL

STRUCTURED QUERY LANGUAGE

SELECT

Select columns

*

Selects all columns to return

SELECT TOP 2/50 PERCENT

Selects the first 2, or first 50 percent of data from a table

GO command

Separates code

ALTER

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype ALTER TABLE table_name DROP COLUMN column_name

First Function

The FIRST() function returns the first value of the selected column. Only in MS Access SELECT FIRST(column_name) FROM table_name;

Format Function

The FORMAT() function is used to format how a field is to be displayed. SELECT FORMAT(column_name,format) FROM table_name;

Group By Function

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

Length Function

The LEN() function returns the length of the value in a text field. SELECT LENGTH(column_name) FROM table_name;

Max Function

The MAX() function returns the largest value of the selected column. SELECT MAX(column_name) FROM table_name;

Mid Function

The MID() function is used to extract characters from a text field. SELECT MID(column_name,start,length) AS some_name FROM table_name;

Min Function

The MIN() function returns the smallest value of the selected column. SELECT MIN(column_name) FROM table_name;

Upper Case Function

The UCASE() function converts the value of a field to uppercase. SELECT UPPER(column_name) FROM table_name;

What is the difference between UNION and UNION ALL?

The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

Diagram

This shows where the data is and how the data is related.

Exclusionary Full Outer Join

To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.

False

True or False, Commands are case sensitive

Local, Global

Types of temp tables

What does UNION do?

UNION merges the contents of two structurally-compatible tables into a single combined table.

--- edits a row in a table.

UPDATE

Update the table to include Taylor Swift's twitter handle. In the code editor type:

UPDATE celebs SET twitter_handle = '@taylorswift13' WHERE id = 4; SELECT * FROM celebs;

The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes.

USING

NULL

Unknown

Wide tables

Use sparse columns to increase total of columns that a table can have to 30,000

()

Use these types of brackets around conditions to make code easier to read

[ ], brackets

Use this when an object has the same name as a keyword

' '

Used around text strings for conditions for easier code reading and machine understanding

value (1, 2, 3)

Used to add data with the INSERT INTO function

/ * * /

Used to comment multiple lines in SQL

One-to-One

When one record in a table relates to only one record in another table

CREATE TABLE statement with constraints

create table "tablename" ("column1" "data type" [constraint], "column2" "data type" [constraint], "column3" "data type" [constraint]); [ ] = optional

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;

creating view syntax

Text

data type, String

Real

data type, a decimal value

Date

data type, formatted as YYYY-MM-DD for the year, month, and day

TRUNCATE TABLE

delete data in table, not table

DELETE FROM tablename WHERE col=val;

delete syntax

LAST()

function that returns the last value

Data types

http://www.w3schools.com/sql/sql_datatypes_general.asp http://www.w3schools.com/sql/sql_datatypes.asp

INSERT INTO statement

insert into "tablename" (first_column,...last_column) values (first_value,...last_value);

Insert Rows

insert into highAchiever (name, age) (select name, age from salesperson where salary > 100000);

INSERT INTO tableName(col1,col2,col3) VALUES (int,int,'varchar')

insert into more specific syntax

WHERE city LIKE '[bsp]%'; or WHERE city NOT LIKE '[!bsp]%';

lets you find a search with any character beginning that is not the following

WHERE city LIKE '[a-c]%';

lets you find a search with any character in a range

The type of view that actually replicates data is called a(n) ____ view.

materialized

CEILING(x) or CEIL(x)

returns the smallest integer value that is greater than or equal to x

SUM

returns the sum of values in a column

POWER(x,y)

returns the value of x raised to the power of y

ROUND(x)

returns the value of x rounded to the nearest whole integer

ROUND(x,d)

returns the value of x rounded to the number of decimal places specified by the value d

Join Two Tables

select Salesperson.Name from Salesperson, Orders where Salesperson.ID = Orders.salesperson_id and cust_id = '4';

How dates work

select User.name, User.phone_num, max(UserHistory.date) from User, UserHistory where User.user_id = UserHistory.user_id and UserHistory.action = 'logged_on' and UserHistory.date >= date_sub(curdate(), interval 30 day) group by (User.user_id);

Return empty values

select distinct u.user_id from User as u left join UserHistory as uh on u.user_id=uh.user_id where uh.user_id is null

ALL

selects all data

BETWEEN

selects all keyword within a range; Used in WHERE statement

DISTINCT

selects unique records, discards duplicate records

Describe an Inner Join

simple joins Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified. i.e. If you have a Table A and a Table B SELECT * FROM TableA INNER JOIN TableB ON TableA."col" = TableB."col" produces only the set of records that match in both A and B

When a view includes columns from more than one table, updates can only be applied to the table that includes the primary key for the view. t/f?

true

Avoid SQL Injection

txtNam = getRequestString("CustomerName"); txtAdd = getRequestString("Address"); txtCit = getRequestString("City"); txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)"; db.Execute(txtSQL,txtNam,txtAdd,txtCit);

SQL Injection 2

uName = getRequestString("UserName"); uPass = getRequestString("UserPass"); sql = "SELECT * FROM Users WHERE Name ='" + uName + "' AND Pass ='" + uPass + "'"

SELECT columnName FROM Table1 UNION ALL SELECT columnName FROM Table2

union all syntax

SELECT columnName, FROM Table1 UNION SELECT columnName, FROM Table2

union syntax

UPDATE tableName SET colname = valname WHERE col = val;

update syntax

%

used for modulo Boolean

*

used for multiplication

-

used for subtraction

PRO%

used for what a command starts with, located in LIKE for WHERE clause

INSERT INTO

used to INSERT new records on table

DROP TABLE

used to delete a table and all rows in the table drop table "tablename"

SELECT DISTINCT

used to return unique values in the result set. It filters out all duplicate values. Here, the result set lists each genre in the movies table exactly once. 1. SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in the specified column(s)

MID()

Extract characters from a text field

DATE()

Extracts the date part of a date or date/time expression

A(n) clause identifies what must exist or a requirement that must be met for a record to be included in the results of a query. T/F

FALSE

A(n) non-equality join is also known as an equijoin, inner join, or simple join. T/F

FALSE

An entity can be on 2nd Normal Form even if it has repeated values. True or False?

FALSE

Hierarchical queries MUST use the LEVEL pseudo column. True or False?

FALSE

If a numeric column is being used for comparison against the search condition, the search condition is enclosed in single quotation marks.T/F

FALSE

Results returned from a SELECT statement that include a GROUP BY clause will present the results in descending order of the column(s) listed in the GROUP BY clause. T/F

FALSE

Single-row functions return one row of results for each group or category of rows processed. T/F

FALSE

The COUNT function can only be used for numeric columns. T/F

FALSE

The JOIN keyword must be used in the WHERE clause of a SELECT statement. T/F

FALSE

A group function can be nested inside a(n)____.

GROUP AND ORDER FUNCTIONS

HAVING

GROUP BY clause: Satisfies a condition in "quotes", used for COUNT or plural clauses

GROUP BY

Gathers Data

OFFSET/FETCH

Has the ability to skip rows

Add a row to the table

INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 21);

RANK

If 2 or more rows tie for a rank, each row receives the same rank with this function

;

Indicates SQL Statement is Complete

Or

Keyword used to combine filtering criteria in which at least one of the criteria must evaluate to true in order for the record to be included in the result set

And

Keyword used to combine filtering criteria in which both of the criteria must evaluate to true in order for the record to be included in the result set

WHERE

Limits rows in SELECT and FROM

TOP

Limits rows returned to the specified number

TOP PERCENT

Limits rows returned to the specified percentage

CREATE or REPLACE VIEW

Makes a view to see data

UPDATE query

Makes changes to existing data in one or more rows in a table or view

Unique Constraint

Makes sure no duplicate values are entered into specific columns that do not participate in a primary key

30 characters

Maximum number of characters allowed for a SQL statement

NULL

Not considered equal to anything

NOT IN

OPERATOR ALLOWS YOU TO EXCLUDE A LIST OF SPECIFIC VALUES

____ aren't allowed in the CREATE VIEW command.

ORDER BY

DUAL table

One row and one column

Sparse columns

Reduce space requirements for NULL values

RDBMS

Relational Database Management System

Left Outer Join

Retrieves all rows from the left table and any related rows from the right table

Inner Join

Retrieves only related rows from 2 tables

SELECT query

Retrieves rows from a database

LEFT JOIN

Return all rows from the left table, and the matched rows from the right table

RIGHT JOIN

Return all rows from the right table, and the matched rows from the left table

DENSE RANK

Returns a rank of rows within the partition of a result set without any gaps in the ranking

EXTRACT()

Returns a single part of a date/time

Cross Join

Returns all possible combinations of all rows

Describe a Cross Join

Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax ("explicit join notation") or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria ("implicit join notation").

INNER JOIN, ON

Returns all rows when there is at least one match in BOTH tables

AVG-Aggregate fxn

Returns the avg value of a given column

CURTIME()

Returns the current time

DISTINCT

Returns the first instance of a value and ignores all further occurrences of that value

FLOOR(x)

Returns the largest integer value that is less than or equal to x

MAX -Aggregate fxn

Returns the largest value in a given column

LEN()

Returns the length of a text field

DATEDIFF()

Returns the number of days between two dates

RANK

Returns the rank of each row within the partition of a result set

ROW_NUMBER

Returns the sequential number of a row within a partition of a result set starting at 1 for the first row in each partition

CEILING(x), CEIL(x)

Returns the smallest integer value that is greater than or equal to x

MIN - Aggregate fxn

Returns the smallest value in a given column

SQRT(x)

Returns the square-root value of x

SUM -Aggregate fxn

Returns the sum of the numeric values in a given column

COUNT-Aggregate fxn

Returns the total number of values in a given column

To view the row you just created, under the INSERT statement type

SELECT * FROM celebs;

SELECT name FROM celebs;

SELECT statements are used to fetch data from a database. Here, SELECT returns all data in the name column of the celebs table. 1. SELECT is a clause that indicates that the statement is a query. You will use SELECT every time you query data from a database. 2. name specifies the column to query data from. 3. FROM celebs specifies the name of the table to query data from. In this statement, data is queried from the celebs table.

BETWEEN

SELECTS A VALUE IN A GIVEN RANGE

Which of the following types of joins refers to joining a table to itself?

SELF-JOIN

DATABASE

SET OF DATA BUT MADE UP TABLES

A(n) ____ is used to combine the results of two queries.

SET OPERATOR

SHOW TABLE

SHOWS ALL TABLES IN DATABASE

____ programming language designed to manipulate and manage data stored in relational databases.

SQL

<> or ! =

SQL operator for not equal to

LIKE

SQL operator for string comparison test, used in where clause

Temporary Tables

Tables stored in tempdb

Partitioned Tables

Tables whose data is horizontally divided into units that may have spread across more than one file group in a database.

USE command

Tells SQL Server which database to use/execute

Consider the recommended drawing conventions for ERD's. Indicate which of the following accurately describes diagramming conventions for entities and attributes:

The * means that an attribute is mandatory or required; the entity name should be singular

Example Between Statements

WHERE ProductName BETWEEN 'C' AND 'M'; BETWEEN #07/04/1996# AND #07/09/1996#;

Many-to-Many

When any records in one table can relate to multiple records in another table (which requires the use of a linking table)

Conditional selections used in the where clause for SELECT

= Equal > Greater than < Less than >= Greater than or equal <= Less than or equal <> Not equal to LIKE *See note below

Table Alias

A "nickname" for a table that can be used instead of spelling out the entire table name

FROM customer_info INNER JOIN purchases

Another option to write inner join

AVG

Average of all values returned by a query

DBMS

DATABASE MANAGEMENT SYSTEM

Database Management System

DBMS

AUTO_INCREMENT

Data type to auto increase the entries for a column in table

Tables

Database objects that contain all the data in a database.

Data type: date

Date value

Global temp table

Deleted when all users referencing the table disconnect from SQL Server instance

Local temp table

Deleted when user disconnects from SQL Server instance

NTILE

Distributes the returned rows into a specified number of groups

ALTER TABLE Persons DROP CONSTRAINT/INDEX uc_PersonID

Drop constraint

What is another name for a simple join or an inner join?

EQUIJOIN

Foreign key

Ensure the referential integrity of the data in one table to match values in another table

Unique

Ensures that each row for a column must have a unique value

Check

Ensures that the value in a column meets a specific condition

Foreign Key Constraint

Establishes/enforces a link between data in 2 tables to control data that can be stored

Database Management System

Software used to store and organize data in logical and easily retrievable manner.

System tables

Users cannot directly query or update these tables

The ____ function is used to determine how widely data are spread out within a group.

VARIANCE

varchar(size)

Variable-length character string. Max size is specified in parenthesis.

Local temp table

Visible only to current connection for user

System tables

Where SQL Server stores data that defines the configuration of the server and its tables

System views

Where info in system tables is available

IBM

Who developed SQL?

_ (underscore)

Wildcard character that is equivalent to any single character

%

Wildcard character that is equivalent to any string of zero or more characters

What are the different types of JOIN clauses supported by the ANSI- standard?

(1) Inner Join (2) Left Join (3) Right Join (4) Full Join (5) Cross Join

Describe a Full Join

(or Full Outer Join) Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries. ex: Produces the set of all records in Table A and Table B, with matching records from both sides if available. If no match, missing side will contain null.

Describe a Left Join

(or Left Outer Join) Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn't find any matching records in the right table. This means that if the ON clause doesn't match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table. Ex: Produces a complete set of recors from Table A, with the matching records (where available) in Table B. If no match, right side will contain null.

Describe a Right Join

(or Right Outer Join) Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN; i.e., the results will contain all records from the right table, even if the JOIN condition doesn't find any matching records in the left table. This means that if the ON clause doesn't match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.

INSERT INTO celebs (id, name, age) VALUES (1, 'Micheal Fasbander', 38);

1 is an integer that will be inserted into the id column 'Micheal Fasbander' is text that will be inserted into the name column 38 is an integer that will be inserted into the age column

CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);

1. CREATE TABLE is a clause that tells SQL you want to create a new table. 2. celebs is the name of the table. 3. (id INTEGER, name TEXT, age INTEGER) is a list of parameters defining each column in the table and its data type.

A standard user-defined table can have up to ... columns

1024

GUID

A 32 character hexadecimal string with over 1.2 septillion unique values

FOREIGN KEY

A FOREIGN KEY in one table points to a PRIMARY KEY in another table. CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) )

Non-Clustered Index

A process making it more efficient to find data by adding specific keys to data rows

Clustered Index

A process of physically arranging the rows in a database to make it more efficient to find data

SQL

A programming language designed for managing data in a RDBMS

Correlated Subquery

A query that is nested inside another query and is dependent on information from the outer query in order to execute.

Row

A record of related data in a database

OFFSET/FETCH

A result filtering option similar to TOP

View

A saved query that can be called and used like a data table which returns a data set but cannot take parameters

Stored Procedure

A saved query that can be executed to return a scalar variable or a result set and is able to take parameters

Transaction

A set of commands that all must execute successfully as a group otherwise all the commands must be rolled back (undone)

Column

A set of data having all the same datatype in a table spanning all rows (records)

Data Type: Aray

A set-length and ordered collection of elements

Data Type: Array

A set-length and ordered collection of elements

____ is a string of characters that the database recognizes as a valid command.

A statement

SQL Injection

Add to webpage, like: txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

DATE_ADD()

Adds a specified time interval to a date

FULL OUTER JOIN or FULL JOIN

Adds columns from one table to another by way of a join

INSERT query

Adds one or more rows to a table or view

HAVING

Affects the search condition after things have already been grouped

Select column1 AS column

Aliases "AS" used to make columns more readable

What is the rule of Second Normal Form?

All non-UID attributes must be dependent upon the entire UID.

Data Type: Float

Approximate numerical, mantissa precision 16 float

Data Type: Real

Approximate numerical, mantissa precision 7 real

Sum()

Built-in function used to calculate the total value of a column in a result set

Coalesce

Built-in function used to convert a null value to some other specified value in the result set

NullIf

Built-in function used to convert a specified value to a null in the result set

DatePart()

Built-in function used to retrieve a specified portion of a date/time value

Len()

Built-in function used to retrieve the length of a string

GetDate()

Built-in function used to return the current date/time

Substring()

Built-in function used to select a specific portion of textual value

Current_Timestamp

Built-in property used to return the current date/time

WHERE clause

Clause used to give search condition for requested row(s)

Batch SQL Code

Code statement; Code Statement2; Using semicolons allows multiple statements

UNION ALL

Combine both tables and show all duplicates

DROP TABLE

Command used to delete a table

set

Command used to make updates to a table

'%ITEM%'

Contains the following word, use with LIKE in WHERE clause

Linking Table

Contains two ID values and optionally additional information

How do you know when to use the different types of time in your design?

DEPENDS ON THE FUNCTIONAL NEEDS OF THE SYSTEM

date(size)

Date value

ORDERED BY

Determines the order of a result set

Equality, non-equality, and self-joins are broadly categorized as outer joins

FALSE

If the result returned from a subquery must be compared to a group function, then the inner query must be nested in the outer query's ____ clause.

HAVING

DROP

Indexes, tables, and databases can easily be deleted/removed with the DROP statement. DROP INDEX table_name.index_name DROP DATABASE database_name

+

Indicates concatenation in Sql Server

Union

Keyword used to combine multiple result sets that have the same amount of columns with the same data types

Order By

Keyword used to designate the sort order of a result set

Select

Keyword used to designate which columns to be returned in the query

From

Keyword used to designate which table to query

Having

Keyword used to filter the results returned based on a group

OFFSET/FETCH

Limits the number of rows returned

Check Constraint

Limits values that are accepted by one or more columns

PRIMARY KEY

MAINTAIN UNIQUENESS OF DATA

When resolving a M:M relationship, the new relationships will always be __________ on the many side.

MANDATORY

Data type: number(size)

Number value with a max number of column digits specified in parenthesis.

number(size)

Number value with a max number of column digits specified in parenthesis.

Data type: number (size,d)

Number value with a maximum number of digits of "size" total, with a max number of "d" digits to the right of the decimal.

number(size, d)

Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.

Database

Organized collection of data

A barrred Relationship will result in a Foreign Key column that also is part of:

PRIMARY KEY

EXCEPT

Query pulls values that are not common to both tables

EXCEPT

Returns any distinct values that are found in the left query that are not found in the right query

INTERSECT

Returns any distinct values that are returned by the queries on both the left and right side

COUNT(*)-Aggregate fxn

Returns the number of rows in a table

POWER(x,y)

Returns the value of x raised to the power of y

ROUND(x)

Returns the value of x rounded to the nearest whole integer

ROUND(x,d)

Returns the value of x rounded to the number of decimal places specified by the value d

CURDATE()

Returns to the current date

SQL Statement

SQL Statement

=

SQL operator for equal

>, <

SQL operator for greater or less than

> =

SQL operator for greater than or equal to

< =

SQL operator for less than or equal to

Structured Query Language

SQL stands for ...

ORDERED BY

Sorts data by specified column(s)

Default 'Food'

Specifies a default value when specified none for this column

SELECT clause

Specifies columns to be returned by query

UniqueIdentifier

Sql Server data type for a GUID

Data Type: Boolean

Stores TRUE or FALSE values boolean

Data Type: Time

Stores hour, minute, and second values time

Data Type: Date

Stores year, month, and day values date

Data Type: Timestamp

Stores year, month, day, hour, minute, and second values timestamp

OR

TRUE IF ONE EXPRESSION IS TRUE

IN

TRUE IF OPERAND IS EQUAL TO ONE OF THE EXPRESSIONS

DELETE Statement

The DELETE statement is used to delete rows in a table. DELETE FROM table_name WHERE some_column=some_value; Notice the WHERE clause in the SQL DELETE statement! The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

SQL Server Management Studio

The IDE used to interact with Microsoft's commercial database management system.

Normalization

The process of following a standard set of rules to separate data into logical related groups

Self Join

The process of linking a table to itself using table aliases. Useful in finding duplicate values in a single table.

SUM

Total of all values returned by a query

Aggregate Functions(def, type)

Used to compute against a "returned column of numeric data" from your SELECT statement i.e. summarize the results of a particular column of selected data MIN, MAX, SUM, AVG, COUNT, COUNT(*)

OFFSET/FETCH

Used to control the number of rows returned

Table(s)

Used to store data

Global temp table

Visible to any user after created

CONJUCTIVE OPERATORS

WHEN AND/OR ARE COMBINED IN PARENTHESES

[ ^ ]

Wildcard character that represents any single character that is not within the specified range/set

[ ], brackets

Wildcard character that represents any single character within the specified range or set

%

Wildcard indicator used in filtering criteria to indicate any number of characters before or after a specified value

GROUP BY clause (def, syntax)

Will gather all of the rows together that contain data in the specified column(s) and allow aggregate functions to be performed on the one or more col's Syntax: SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list";

Keyword

Word that has reserved meaning in SQL

Identifier

Word used to reference database objects such as tables, columns, etc

ALTER TABLE tableName CHANGE columnName name varchar(100)

altering table column name

UCASE()

function that Converts a field to upper case

TRANSLATE

function that let's you change how the context is written

ISNULL(), NVL(), IFNULL(), COALESCE()

function to specify how we want to treat NULL Values

INSERT INTO tableName VALUES (int,'val','val');

insert into syntax

WHERE city LIKE '[bsp]%';

lets you select to begin with any of the following characters

ALTER [Table, Database]

modifies a table/database

MOD(x,y)

modulo - returns the integer remainder of x divided by y (same as x%y)

The entire column heading for a column will be displayed, regardless of the assigned width of the column, if the column is defined as a(n) ____ column.

numeric

SELECT name FROM celebs;

only name column shows from celebs table

IN

operator that tests whether or not a value (stated before the keyword) is part of the list of values provided after the keyword; replaces compound operator need to use AND/OR

ORDER BY

optional clause to sort

ORDER BY clause

optional clause which will allow you to display the results of your query in a sorted order (either ascending order or descending order) based on the columns that you specify to order by SELECT column1, SUM(column2) FROM "list-of-tables" ORDER BY "column-list" [ASC | DESC]; [ ] = optional ASC = Ascending Order - default DESC = Descending Order

column IS NULL; column IS NOT NULL;

query used to search for null values

Tables are sometimes referred to as ---

relations

DELETE

removes data

RENAME TABLE tableName TO newTableName;

rename the whole table

AS

renaming, AS tot_dollar #rename col as tot_dollar

INITCAP

returns first character of each word upper case

MAX

returns largest value in a column

MIN

returns smallest value in a column

ABS(x)

returns the absolute value of x

SIGN(x)

returns the sign of input x as -1, 0, or 1 (negative, zero, or positive respectively)

SQRT(x)

returns the square-root value of x

SELECT table1.col1, table2.col2 FROM table1 RIGHT JOIN table2 ON table1.columname = table2.columname

right join syntax

SELECT INTO

statement selects data from one table and inserts it into a new table. SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2013 FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID;

NOT LIKE

used to show what is not contained in the word

DROP VIEW

DROP VIEW view_name

If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.

HAVING

Data Type: Variable Character

Character string. Variable length. Maximum length n varchar(64)

CREATE VIEW

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

DISTINCT Statement

In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values. Syntax: SELECT DISTINCT column_name,column_name FROM table_name;

SQL Functions

SQL has many built-in functions for performing calculations on data. Aggregate functions and Scalar Functions.

Are SQL queries case sensitive?

SQL keywords are NOT case sensitive: select is the same as SELECT

What is SQL?

SQL stands for Structured Query Language. SQL lets you access and manipulate databases.

CREATE TABLE

The CREATE TABLE statement is used to create a table in a database. Tables are organized into rows and columns; and each table must have a name. CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... );

DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' );

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins. SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

Having Function

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value;

IN Clause

The IN operator allows you to specify multiple values in a WHERE clause. SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...); SELECT * FROM Customers WHERE City IN ('Paris','London');

INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name; SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name=table2.column_name;

INSERT INTO

The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected. INSERT INTO table2 SELECT * FROM table1; INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;

INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table. INSERT INTO table_name VALUES (value1,value2,value3,...); INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

Lower Case Function

The LCASE() function converts the value of a field to lowercase. SELECT LOWER(column_name) FROM table_name;

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

LIKE Operator

The LIKE operator is used to search for a specified pattern in a column. SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; SELECT * FROM Customers WHERE City LIKE 's%';

Now Function

The NOW() function returns the current system date and time. SELECT NOW() FROM table_name;

ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. SELECT column_name, column_name FROM table_name ORDER BY column_name ASC|DESC, column_name ASC|DESC;

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key. CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

Round Function

The ROUND() function is used to round a numeric field to the number of decimals specified. NOTE: Many database systems have adopted the IEEE 754 standard for arithmetic operations, according to which the default rounding behavior is "round half to even." In this scheme, .5 is rounded to the nearest even integer. So, both 11.5 and 12.5 would be rounded to 12. SELECT ROUND(column_name,decimals) FROM table_name;

SELECT INTO

The SELECT INTO statement selects data from one table and inserts it into a new table. SELECT * INTO newtable [IN externaldb] FROM table1; SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;

SELECT TOP Clause

The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance. Note: Not all database systems support the SELECT TOP clause. SELECT TOP number|percent column_name(s) FROM table_name;

SELECT Statement

The SELECT statement is used to select data from a database. Syntax: SELECT column_name, column_name FROM table_name; SELECT * FROM table_name;

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order. SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion. Syntax: SELECT column_name,column_name FROM table_name WHERE column_name operator value;

Intersect

Use this keyword to return only values that are in the first query AND also in the second query. Same as INNER JOIN

No formal rules exist for drawing ERD's. The most important thing is to make sure that all entities, attributes, and relationships are documented on the diagram. The Layout is insignificant. True or False?

false

Which of the following operators is used when the criteria is based upon a search pattern?

like

BETWEEN operator

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates. SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

Which operators can be combined with other comparison operators to treat the results of a subquery as a set of values, rather than as individual values?

ALL & ANY

JOIN

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

AUTO INCREMENT

Auto-increment allows a unique number to be generated when a new record is inserted into a table. Very often we would like the value of the primary key field to be created automatically every time a new record is inserted. CREATE TABLE Persons ( ID int IDENTITY(1,1) PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

Data Type: Binary Object

Binary string. Fixed-length n Syntax: Blob Text Fixed Length Syntax: Binary (fixed up to 8K) Varbinary (<8K) Image (<2GB)

UDPATE VIEW

CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

What is CRUD?

Create, Read/Retrieve (Select), Update and Delete are the four basic functions of persistent storage.

Data Type: Decimal

Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal. decimal(6,2)

What goes at the end of each SQL statement?

Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row. CREATE TABLE Persons ( P_Id int NOT NULL CHECK (P_Id>0), LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

Count Function

The COUNT() function returns the number of rows that matches a specified criteria. The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column: SELECT COUNT(column_name) FROM table_name;

NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. CREATE TABLE PersonsNotNull ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

The DECODE function allows the user to specify different actions to be taken, depending on the value being used for the comparison. t/f?

true

The IN operator can be used with single-row, multiple-row, or multiple-column subqueries. t/f?

true

The LOWER function can be used to display upper-case characters in lower-case. t/f?

true

When you resolve a M:M by creating an intersection entity, this new entity will always inherit:

A relationship to each entity from the original M:M.

Schema

A schema is a collection of database objects (tables) associated with one particular database username. This username is called the schema owner, or the owner of the related group of objects. You may have one or multiple schemas in a database. Basically, any user who creates an object has just created his or her own schema. So, based on a user's privileges within the database, the user has control over objects that are created, manipulated, and deleted. A schema can consist of a single table and has no limits to the number of objects that it may contain, unless restricted by a specific database implementation.

Aliases (AS)

SQL aliases are used to give a database table, or a column in a table, a temporary name. Basically, aliases are created to make column names more readable. SELECT column_name AS alias_name FROM table_name; SELECT column_name(s) FROM table_name AS alias_name;

What can SQL do?

SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views

SQL constraints

SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint. Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement). CREATE TABLE table_name ( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, .... );

Data Type: Timestampe

Stores year, month, day, hour, minute, and second values timestamp

AND/OR Operators

The AND & OR operators are used to filter records based on more than one condition. Syntax: SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');

Average Function

The AVG() function returns the average value of a numeric column. SELECT AVG(column_name) FROM table_name

CREATE DATABASE

The CREATE DATABASE statement is used to create a database. CREATE DATABASE dbname;

INDEX

The CREATE INDEX statement is used to create indexes in tables. Indexes allow the database application to find data fast; without reading the whole table. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against. CREATE INDEX index_name ON table_name (column_name)

Last Function

The LAST() function returns the last value of the selected column. Only in MS Access SELECT LAST(column_name) FROM table_name;

SQL Dates

The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database. SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MI:SS SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS TIMESTAMP - format: a unique number