CH6 - Basic SQL

Question 1

6.2. List the data types that are allowed for SQL attributes.

Answer 1

• numeric, character string, bit string, Boolean, date, and time.
• numeric: INT, FLOAT, DECIMAL(i, j)
• character string: CHAR(n) fixed length, varying length VARCHAR(n)
• bit string: fixed length n—BIT(n)—or varying length— BIT VARYING(n)
• data: DATE
• time: TIME, TIMESTAMP

Question 2

What is EXCEPT

Answer 2

The result of EXCEPT is all records of the left SELECT result set except records which are in right SELECT result set, i.e. it is subtraction of two result sets.

Question 3

What is the difference between EXCEPT DISTINCT and EXCEPT ALL

Answer 3

The ALL operator leaves duplicates intact, while the DISTINCT operator removes duplicates

Question 4

Resources

Answer 4

https://mariadb.com/kb/en/except/

Question 5

What is GROUP BY

Answer 5

s. Each group (partition) will consist of the tuples that have the same value of some attribute(s), called the grouping attribute(s). We can then apply the function to each such group indepen- dently to produce summary information about each group. SQL has a GROUP BY clause for this purpose.

Question 6

What is HAVING

Answer 6

times we want to retrieve the values of these functions only for groups that satisfy certain conditions. For example, suppose that we want to modify Query 25 so that only projects with more than two employees appear in the result. SQL provides a HAVING clause, which can appear in conjunction with a GROUP BY clause, for this purpose. H

Question 7

What is ORDER BY

Answer 7

• SQL allows the user to order the tuples in the result of a query by the values of one or more of the attributes that appear in the query result, by using the ORDER BY c
• The default order is in ascending order of values. We can specify the keyword DESC if we want to see the result in a descending order of values. The keyword ASC can be used to specify ascending order explicitly.

Question 8

6.3. How does SQL allow implementation of the entity integrity and referential integrity constraints described in Chapter 3? What about referential trig- gered actions? 


Answer 8

• The PRIMARY KEY clause specifies one or more attributes that make up the primary key of a relation.
• The UNIQUE clause specifies alternate (unique) keys, also known as candidate keys
• Referential integrity is specified via the FOREIGN KEY clause,
• When referential integrity violated default is rejectt. a referential triggered action clause to any foreign key constraint to change action. The options include SET NULL, CASCADE, and SET DEFAULT. A

Question 9

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


Answer 9

• SQL allows a table (relation) to have two or more tuples that are identical in all their attribute values. Hence, in general, an SQL table is not a set of tuples, because a set does not allow two identical members; rather, it is a multiset (sometimes called a bag) of tuples.
• Some SQL relations are constrained to be sets because a key constraint has been declared or because the DISTINCT option has been used