Describe the four clauses in the syntax of a simple SQL retrieval query.

 Question: 4.4. Describe the four clauses in the syntax of a simple SQL retrieval query. Show what type of constructs can be specified in each of the clauses. Which are required and which are optional?

Solution:

1. SELECT
2. FROM
3. WHERE
4. ORDER BY
5. Limit
   

SELECT

List of attribute names to be received by the query

FROM

The tables that these attributes with be retrieved from

WHERE

Conditional boolean expression to identify certain tuples to be retrieved (optional)

ORDER BY

Attribute list to order the result by (optional)

LIMIT BY 

This is used to limit the number of rows (optional)

How does SQL allow implementation of the entity integrity and referential integrity constraints

 Question: 4.3. How does SQL allow implementation of the entity integrity and referential integrity constraints described in Chapter 3? What about referential triggered actions? 

Solution: 

Entity integrity

Referential integrity

Entity integrity constraints

In the entity integrity constraint, it states that no primary key value can be NULL because. Primary key value is used to identify the individual tuples in a relation. And having NULL values for the primary key implies that we cannot identify some tuples.

Referential integrity constraints

It is specified between two relations and is used to maintain the consistency among tuples in the two relations and referential integrity constraints states that, a tuple in one relation that refers to another relation and must refer to an existing tuple in that relation.

Referential triggered actions

Schema designer can specify an alternative action to taken when a referential integrity constraint is violated by attaching a referential triggered action clause to any foreign key constraint.

List the data types that are allowed for SQL attributes.

 Question: 4.2. List the data types that are allowed for SQL attributes. 

Solution:

Numeric data types

Integer numbers

• INT
• INTEGER
• SMALLINT
• BIGINT

Floating-point (real) numbers

• REAL
• DOUBLE
• FLOAT

Fixed-point numbers

• DECIMAL(n,m)
• DEC(n,m)
• NUMERIC(n,m)
• NUM(n,m)

Character-string data types

Fixed length

• CHAR(n)
• CHARACTER(n)

Varying length

• VARCHAR(n)
• CHAR VARYING(n)
• CHARACTER VARYING(n)

Long varchar

Large object data types

Characters:

CLOB

CHAR LARGE OBJECT

CHARACTER LARGE OBJECT

Bits

BLOB

BINARY LARGE OBJECT

Boolean data type

Values of TRUE or FALSE or NULL

DATE data type

Ten positions

Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD

How do the relations (tables) in SQL differ from the relations defined formally

Questions 4.1. How do the relations (tables) in SQL differ from the relations defined formally in Chapter 3? Discuss the other differences in terminology. Why does SQL allow duplicate tuples in a table or in a query result?

Solution:  SQL allows a table to have more than one tuple that are identical in all their attribute values while others don't

The reasons why SQL allows duplicate tuples are

• Duplicate elimination is an expensive operation
• User may want to see duplicate tuples in the result of query