Structured Query Language

Structured Query Language

memorize.aimemorize.ai (lvl 286)
Section 1

Preview this deck

NULL constraint

Front

Star 0%
Star 0%
Star 0%
Star 0%
Star 0%

0.0

0 reviews

5
0
4
0
3
0
2
0
1
0

Active users

0

All-time users

0

Favorites

0

Last updated

2 years ago

Date created

Mar 1, 2020

Cards (66)

Section 1

(50 cards)

NULL constraint

Front

Indicates that null values are allowed, ad that a row can be created without a value for this column.

Back

DELETE command

Front

Can eliminate rows. Removes any related rows.

Back

DISTINCT

Front

If you want DBMS to check for and eliminate duplicate rows

Back

business rule

Front

-If an EMPLOYEE row is to be deleted and that row is connected to any ASSIGNMENT, the EMPLOYEE row deletion will be disallowed. -If a PROJECT row is deleted, then all the ASSIGNMENT rows that are connected to the deleted PROJECT row will also be deleted.

Back

comparison operators

Front

>= greater than or equal to and <= less than or equal to

Back

American National Standards Institute (ANSI)

Front

Endorsed successive versions of SQL as national standards

Back

CREATE TABLE statement

Front

Used to create table structures.

Back

AND keyword

Front

Only rows meeting all the conditions will be selected.

Back

ASC keyboard

Front

Can be used to specify ascending [and descending] order when necessary.

Back

CASCADE keyword

Front

Means the same change should be made to the related Department column.

Back

MIN

Front

Minimum

Back

NOT IN phrase

Front

Select rows that do not have a Department value with any of these

Back

LEFT keyword

Front

If any row in the table on the left side of the FROM clause has no match, included in the results anyway.

Back

DESC keyword

Front

Can be used to specify [ascending and] descending order when necessary.

Back

GROUP BY clause

Front

Group rows by common values

Back

NOT NULL constraint

Front

Indicates that a value must be supplied when a new row is created.

Back

COUNT

Front

counts the number of rows in the result.

Back

NOT keyword

Front

Select rows that do not have a Department value with any of these

Back

CONSTRAINT keyword

Front

Identify table constraints (define primary keys) and can be used to implement various constraints.

Back

CHECK constraint

Front

Similiar to WHERE clauses in SQL theries.

Back

join operation

Front

Need to display data from two or more tables; form a new relation by connecting contents of two or more other relations.

Back

equijoin

Front

same as inner join. Original join operation.

Back

data sublanguage

Front

A language for defining and processing a database intended to be embedded in programs written in another language--in more cases, a procedural language such as COBOL, C#, or Visual Basic. Contains only constructs for data definition and processing.

Back

AVG

Front

Average

Back

outer join

Front

Used to avoid losing data when joining.

Back

DROP TABLE statement

Front

Drops the table's structure along with all of the table's data.

Back

AS keyword

Front

Assign a column name

Back

ORDER BY clause

Front

Can use to sort the rows.

Back

DEFAULT keyword

Front

Can be used to set initial values

Back

IN keyword

Front

A column should have one of a set of values.

Back

NO ACTION keyword

Front

No need to cascade update to the referenced primary key.

Back

Extensible Markup Language (XML)

Front

A markup language whose tags can be extended by document designers.

Back

inner join

Front

same as equijoin. Original join operation.

Back

IS NULL keyword

Front

Can be used in a WHERE clause to search for null values.

Back

asterick (*)

Front

Represents multiple characters

Back

HAVING clause

Front

After Groupby clause, can further restrict results to apply conditions to groups that are formed.

Back

MAX

Front

Maximum

Back

ALTER TABLE statement

Front

Add a restraint.

Back

OR keyword

Front

Rows that meet ant of the conditions are selected.

Back

INSERT statement

Front

Can be used to add data in relation by using SQL.

Back

NOT LIKE phrase

Front

Ex if want to find all the employees who work in the department that do not end in ing: Department NOT LIKE 'ing';

Back

data manipulation language (DML)

Front

Used to query and modify database data.

Back

percent sign (%)

Front

Used to represent a series of one or more unspecified characters. [SQL wildcards.]

Back

IDENTITY (M,N) phrase

Front

Illustrates how surrogate keys are defined in Microsoft SQL server.

Back

IS NOT NULL phrase

Front

Example: If you want to find all employees who do have phone numbers, you can use Phone is NOT NULL

Back

JOIN...ON syntax

Front

Alternative joining

Back

ON UPDATE phrase

Front

Shows what action should be taken if a value of the primary key changes

Back

ON DELETE phrase

Front

Shows what action should be taken if a row is deleted.

Back

LIKE keyword

Front

Used in SQL expressions to select partial values. used with underscore symbol to find values that fit a pattern.

Back

data definition language (DDL)

Front

Used to define database structures

Back

Section 2

(16 cards)

Advantages of defining primary keys

Front

1) Required for defining composite keys because the PRIMARY KEY column constraint cannot be used on more than one column. 2) By using table constraints, you can choose the name of the constraint that defines the primary key. 3) Using the table constraint to define the primary key allows us to easily define surrogate keys in some DBMS products.

Back

VarChar

Front

A variable-length character data type. Values are fixed length.

Back

SQL view

Front

A virtual table created by a DBMS-stored SELECT statement and can combine access to data in multiple tables and even in other views.

Back

PRIMARY KEY constraint

Front

cannot be used in more than one column.

Back

Query by Example (QBE)

Front

A style of query interface, first developed by IBM but now used by other vendors, that enables users to express queries by providing examples of the results they seek.

Back

RIGHT keyword

Front

Rows in the table on the right-hand side of the FROM clause are included.

Back

Structured Query Language (SQL)

Front

Consists only of constructs of defining and processing a database.

Back

SQL SELECT/FROM/WHERE framework

Front

Querying a single table in a database uses the SQL SELECT command with FROM and WHERE clauses: SELECT ColumnNames FROM TableName WHERE SomeConditionExists;

Back

wildcard characters

Front

Represent unknown characters in a pattern.

Back

question mark (?)

Front

An underscore to represent single characters

Back

UNIQUE constraint

Front

There cannot be any duplicated values in the column.

Back

UPDATE...SET command

Front

Can modify the values of existing data.

Back

SUM

Front

totals the set of values of a numeric column.

Back

underscore symbol (_)

Front

Represents a single, unspecified character. [SQL wildcards.]

Back

SQL built-in functions

Front

Calculate values based on the data in the tables.

Back

subquery

Front

A SELECT statement that appears in the WHERE clause of a SQL statement. They can be nested within each other.

Back