UNIT 1-3

Question 1

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.

Answer 1

data definition language (DDL):

Question 2

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

Answer 2

data manipulation language (DML):

Question 3

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

Answer 3

SQL is a nonprocedural language

Question 4

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

Answer 4

Schema

Question 5

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

Answer 5

Data Types

Question 6

Fixed character length data, 1 to 255 characters

Answer 6

CHAR

Question 7

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

Answer 7

VARCHAR

Question 8

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.

Answer 8

Decimal

Question 9

Integer values only

Answer 9

INT

Question 10

Small integer values only

Answer 10

SMALLINT

Question 11

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

Answer 11

DATE

Question 12

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

Answer 12

TABLE

Question 13

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.

Answer 13

relational databases

Question 14

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

Answer 14

constraint

Question 15

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

Answer 15

PRIMARY KEY

Question 16

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

Answer 16

FOREIGN KEY

Question 17

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

Answer 17

UNIQUE

Question 18

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

Answer 18

CHECK

Question 19

Defines column values stored when no value has been assigned

Answer 19

DEFAULT

Question 20

Designates that a column will accept null values

Answer 20

Nullability

Question 21

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

Answer 21

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

Question 22

is a function that returns the current system date

Answer 22

getdate()

Question 23

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

Answer 23

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

Question 24

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

Answer 24

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

Question 25

Remember the rule of precedence

Answer 25

1. Perform operations within parentheses.2. Perform power operations.3. Perform multiplications and divisions.4. Perform additions and subtractions.

Question 26

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.

Answer 26

AND, OR, NOT

Question 27

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

Answer 27

Special Operators

Question 28

Used to check whether an attribute value is within a range

Answer 28

BETWEENselect * from tbl_food where fd_price between 10 and 100;

Question 29

Used to check whether an attribute value is null

Answer 29

IS NULLselect * from tbl_food where fd_name is null;

Question 30

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

Answer 30

LIKE

Question 31

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

Answer 31

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.)

Question 32

Used to check whether a subquery returns any rows

Answer 32

EXISTS

Question 33

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

Answer 33

REGEXP [^]

Question 34

means any single character within a range or a set.

Answer 34

REGEXP[]

Question 35

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

Answer 35

ALTER TABLEALTER TABLE tablenameADD constraint [ ADD constraint] ;

Question 36

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

Answer 36

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

Question 37

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

Answer 37

alter table tbl_productadd p_salecode char(1)

Question 38

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

Answer 38

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

Question 39

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

Answer 39

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

Question 40

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.

Answer 40

alter table tbl_vendordrop column v_order

Question 41

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

Answer 41

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]

Question 42

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

Answer 42

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

Question 43

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

Answer 43

DISTINCTselect distinct v_codefrom tbl_product;

Question 44

Creates a database schema

Answer 44

CREATE SCHEMA AUTHORIZATION

Question 45

Creates a new table in the user's database schema

Answer 45

CREATE TABLE

Question 46

Ensures that a column will not have null values

Answer 46

NOT NULL

Question 47

Ensures that a column will not have duplicate values

Answer 47

UNIQUE

Question 48

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

Answer 48

DEFAULT

Question 49

Validates data in an attribute

Answer 49

CHECK

Question 50

Creates an index for a table

Answer 50

CREATE INDEX

Question 51

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

Answer 51

CREATE VIEW

Question 52

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

Answer 52

ALTER TABLE

Question 53

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

Answer 53

CREATE TABLE AS

Question 54

Permanently deletes a table (and its data)

Answer 54

DROP TABLE

Question 55

Permanently deletes an index

Answer 55

DROP INDEX

Question 56

Permanently deletes a view

Answer 56

DROP VIEW

Question 57

COMPARISON OPERATORS

Answer 57

= < > <= >= <>

Question 58

LOGICAL OPERATORS

Answer 58

AND OR NOT

Question 59

SPECIAL OPERATORS

Answer 59

BETWEENIS NULLLIKEINEXISTSDISTINCT

Question 60

AGGREGATE FUNCTIONS

Answer 60

COUNTMINMAXSUMAVG

Question 61

Restricts the selection of grouped rows based on a condition

Answer 61

HAVING

Question 62

Orders the selected rows based on one or more attributes

Answer 62

ORDER BY

Question 63

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

Answer 63

UPDATE

Question 64

Restricts the selection of rows based on a conditional expression

Answer 64

WHERE

Question 65

Groups the selected rows based on one or more attributes

Answer 65

GROUP BY

Question 66

Deletes one or more rows from a table

Answer 66

DELETE

Question 67

Permanently saves data changes

Answer 67

COMMIT

Question 68

Restores data to their original values

Answer 68

ROLLBACK

Question 69

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

Answer 69

IN

Question 70

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

Answer 70

COUNT

Question 71

Checks whether a subquery returns any rows

Answer 71

ECISTS

Question 72

Limits values to unique values

Answer 72

DISTINCT

Question 73

Checks whether an attribute value matches any given string pattern

Answer 73

LIKE