Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

DATABASE > UNIT 1-3 > Flashcards

UNIT 1-3 Flashcards

1
Q

SQL includes commands to create database objects such as tables, indexes, and views, as well as commands to define access rights to those database objects.

A

data definition language (DDL):

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

SQL includes commands to insert, update, delete, and retrieve data within the database tables.

A

data manipulation language (DML):

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

You merely command what is to be done; you don’t have to worry about how it is to be done.

A

SQL is a nonprocedural language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

a group of database objects—such as tables and indexes—that are related to each other.

A

Schema

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

They help distinguish the kinds of data that can be stored or processed inside our database objects.

A

Data Types

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Fixed character length data, 1 to 255 characters

A

CHAR

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Variable character length data,1 to 2,000 characters.

A

VARCHAR

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Numeric data. decimal(9,2)is used to specify numbers with two decimal places and up to nine digits long, including the decimal places. Some RDBMSs permit the use of a MONEY or a CURRENCY data type.

A

Decimal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Integer values only

A

INT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Small integer values only

A

SMALLINT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

formats vary. Commonly accepted formats are: ’DD-MON-YYYY’, ’DD-MON-YY’, ’MM/DD/YYYY’, and ’MM/DD/YY’

A

DATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

a collection of related data held in a table format within a database. It consists of columns and rows.

A

TABLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

In ___________ and flat file databases, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and c.

A

relational databases

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Each _____ type has an associated T-SQL statement format with various implementations.

A

constraint

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

A column or combination of columns whose values uniquely identify each row in the table

A

PRIMARY KEY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

A column or combination of columns used to establish and enforce a link between the data in two tables

A

FOREIGN KEY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Ensures that no duplicate values are entered in specific columns that do not participate in a primary key

A

UNIQUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Enforces domain integrity by limiting the values that are accepted by a column

A

CHECK

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Defines column values stored when no value has been assigned

A

DEFAULT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Designates that a column will accept null values

A

Nullability

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

_____ can be used to improve the efficiency of searches and to avoid duplicate column values.

A

indexesCREATE[UNIQUE] INDEX indexname ON tablename(column1[, column2])

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

is a function that returns the current system date

A

getdate()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

is a TSQL function that add a particular date interval from a given column or value. Its accepts the following parameters :

A

dateadd()dateadd ([day type such as year, day, hour, minute etc.], [integer value], [column name or date value])

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

is a MySQL function that subtracts a particular date interval from a given column or value.

A

date_subdate_sub([column name or date value], INTERVAL [integer value] [day type such as year, day, hour, minute etc.])

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Remember the rule of precedence

A
  1. Perform operations within parentheses.2. Perform power operations.3. Perform multiplications and divisions.4. Perform additions and subtractions.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

We use ___ if both conditions are true, ___ if at least one of the condition and ___ if we want to negate the result of a conditional expression.

A

AND, OR, NOT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

ANSI-standard SQL allows the use of _______ in conjunction with the WHERE clause

A

Special Operators

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Used to check whether an attribute value is within a range

A

BETWEENselect * from tbl_food where fd_price between 10 and 100;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Used to check whether an attribute value is null

A

IS NULLselect * from tbl_food where fd_name is null;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Used to check whether an attribute value matches a given string pattern

A

LIKE

31
Q

Used to check whether an attribute value matches any value within a value list

A

INselect * from tbl_food where fd_name = ‘choco’ or fd_name = ‘mocha’; can be handled more efficiently with:/SQL Server and MySQL/select * from tbl_foodwhere fd_name in (‘choco’,’mocha’); Take note however that if you are using a LARGE list, IN operator tends to be slow.)

32
Q

Used to check whether a subquery returns any rows

A

EXISTS

33
Q

means any single character NOT within a range or a set.

A

REGEXP [^]

34
Q

means any single character within a range or a set.

A

REGEXP[]

35
Q

The _______ command can also be used to add table constraints.

A

ALTER TABLEALTER TABLE tablenameADD constraint [ ADD constraint] ;

36
Q

You could also use the ALTER TABLE command to remove a column or table constraint.

A

ALTER TABLE tablenameDROP{PRIMARY KEY|COLUMN columnname |CONSTRAINT constraintname}

37
Q

You can alter an existing table by adding one or more columns.

A

alter table tbl_productadd p_salecode char(1)

38
Q

We can change a column definition as long as there is either no data or the existing column fits into the new column definition.

A

// SQL Serveralter table table_namealter column column_name datatype; // MySQLalter table table_namemodify column column_name datatype;

39
Q

If we want to change the size of column pt_name in table tbl_part from varchar(500) to varchar(250)

A

// SQL Serveralter table tbl_partalter column pt_name varchar(250);// MySQLalter table tbl_partmodify column pt_name varchar(250);

40
Q

Occasionally, you might want to modify a table by deleting a column. Suppose that you want to delete the V_ORDER attribute from the VENDOR table.

A

alter table tbl_vendordrop column v_order

41
Q

To define the primary key for the table tbl_part, use the following command:

A

ALTER TABLE [table_name]ADD PRIMARY KEY ([column_name])ALTER TABLE [table_name]ADD FOREIGN KEY ([column_name]) REFERENCES [main_table] ([column_name]) [optional on delete/update cascade]

42
Q

The_____ clause is especially useful when the listing order is important to you

A

ORDER BYselect columnlistfrom tablelist[where conditionlist][order by columnlist [asc|desc]]

43
Q

SQL’s _____ clause produces a list of only those values that are different from one another

A

DISTINCTselect distinct v_codefrom tbl_product;

44
Q

Creates a database schema

A

CREATE SCHEMA AUTHORIZATION

45
Q

Creates a new table in the user’s database schema

A

CREATE TABLE

46
Q

Ensures that a column will not have null values

A

NOT NULL

47
Q

Ensures that a column will not have duplicate values

A

UNIQUE

48
Q

Defines a default value for a column (when no value is given)

A

DEFAULT

49
Q

Validates data in an attribute

A

CHECK

50
Q

Creates an index for a table

A

CREATE INDEX

51
Q

Creates a dynamic subset of rows/columns from one or more tables

A

CREATE VIEW

52
Q

Modifies a table definition (adds, modifies, or deletes attributes or constraints)

A

ALTER TABLE

53
Q

Creates a new table based on a query in the user’s database schema

A

CREATE TABLE AS

54
Q

Permanently deletes a table (and its data)

A

DROP TABLE

55
Q

Permanently deletes an index

A

DROP INDEX

56
Q

Permanently deletes a view

A

DROP VIEW

57
Q

COMPARISON OPERATORS

A

= < > <= >= <>

58
Q

LOGICAL OPERATORS

A

AND OR NOT

59
Q

SPECIAL OPERATORS

A

BETWEENIS NULLLIKEINEXISTSDISTINCT

60
Q

AGGREGATE FUNCTIONS

A

COUNTMINMAXSUMAVG

61
Q

Restricts the selection of grouped rows based on a condition

A

HAVING

62
Q

Orders the selected rows based on one or more attributes

A

ORDER BY

63
Q

Modifies an attribute’s values in one or more table’s rows

A

UPDATE

64
Q

Restricts the selection of rows based on a conditional expression

A

WHERE

65
Q

Groups the selected rows based on one or more attributes

A

GROUP BY

66
Q

Deletes one or more rows from a table

A

DELETE

67
Q

Permanently saves data changes

A

COMMIT

68
Q

Restores data to their original values

A

ROLLBACK

69
Q

Checks whether an attribute value matches any value within a value list

A

IN

70
Q

Returns the number of rows with non-null values for a given column

A

COUNT

71
Q

Checks whether a subquery returns any rows

A

ECISTS

72
Q

Limits values to unique values

A

DISTINCT

73
Q

Checks whether an attribute value matches any given string pattern

A

LIKE

DATABASE (5 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions