(Each Answer should be at least 2 or 3 paragraphs in length) 1.) (15 Points) Des

ID: 3719961 • Letter: #

Question

(Each Answer should be at least 2 or 3 paragraphs in length)

1.) (15 Points) Describe the 3 major types of SQL statements and describe the 4 primary Data Manipulation statements.

2.) (15 Points) Describe all of the different options you have for the “Where” clause.(i.e. ‘Like’, ‘In’, etc)

3.) (10 Points) Describe the difference between “Order By” and “Group By”.

4.) (15 Points) Describe how the use of an INDEX will make queries using “Order By” much faster.

5.) (15 Points) Explain just what Data Integrity is and how the use of Constraints may result in better Data Integrity.

6.) (10 Points) Explain exactly how a “Join” works. What is a Cartesian Product?

7.) (15 Points) Briefly describe the difference and similarities between a “Join” and a ‘Correlated Sub-Query”. Discuss the advantages and disadvantages of each.

8.) (10 Points) Describe a Transaction. What is a transaction and what keywords are used to create transactions?

9.) (10 Points) What is the “Transaction Log”? Describe how it works and what it is used for.

10.) (10 Points) What are Triggers and Stored Procedures and how can they be used to make a database better?

Explanation / Answer

Q 1)

There are various types of SQL statements. These functions build database objects, manipulate objects, populate tables with data, update tables, delete tables, control access etc.

The major type of SQL statements are:

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE/ALTER/DROP Index

CREATE/ALTER/DROP View

INSERT :It is used to insert data into the table.

Syntax- INSERT INTO table-name VALUES (val1, val2, ..)

UPDATE: UPDATE SQL is used to update the value in a particular record in a table.

Syntax: UPDATE table-name SET column-name = new_value WHERE condition;

DELETE: Delete command is used to delete a particular or all records from a table.

Syntax: DELETE FROM table-name;

Q2)

The where clause in SQL specified in DML statement filters out the record based on a condition. WHERE clause is used to filter only those records that meets a specified conditions. One or more conditions can be joined in WHERE clause.

Syntax:

SELECT clo1, col2

FROM table-name

WHERE condition;

WHERE clause is not only used with select statement but also used update, delete etc. statements. Various types of conditions are given in where clause. The following possible conditions in where clause:

Operator

Description

Equal, checks for equality.

Not equal

Greater than

Less than

Greater than or equal to

Less than or equal to

BETWEEN

BETWEEN certain inclusive range

Search for specified pattern

Specifying multiple possible values of a column