Databases

Using the University database / schema, explain the 'integrity constraint' on the relationship between COURSE and SECTION as discussed in class and in the text. Please Be Specific.

(Grader, the student's answer should specifically mention the inability to remove a Course while it is being referenced by a Section instance.) The entities COURSE and SECTION share a common attribute course_number. The attribute (course_number) uniquely identifies each record / row in the COURSE table. The course_number identifier is used by the entity SECTION to identify which course is being offered for a specific course / semester / year. The constraint is that users are restricted from deleting a course whose course_number is being used by any row in SECTION. If we allowed such a course to be removed, the result would be rows in SECTION referencing non-existent courses.

What are the three disadvantages of storing an application's data in flat files outlined in the slides?

1. A file is an unstructured stream of bytes and the file's 'metadata' is trapped in the application's code / logic. 2. Each application maintains its data in separate files resulting in different representations of the same UOD entity producing Information Silos within the enterprise. 3. Critical information may be duplicated across several applications / files making it difficult to ensure proper maintenance

Describe the relationship between... 1. Schema and Relation 2. Relation and Entity 3. Relation and Attribute 4. Attribute and Domain 5. Relation and Tuple 6. Attribute and Tuple

1. A single schema maintains a multiple relations. Relations must be uniquely named in the schema. 2. A relation represents an entity from the problem domain. 3. A relation maintains multiple attributes. Attributes must be uniquely named in the relation. 4. Each attribute is a member of a Domain. Domains describe the meaning and allowed values that can be assigned to a relation's tuple. Domains are roughly equivalent to data types. 5. A relation maintains multiple tuples. If relation == table, then tuple == row. 6. Each tuple maintains a value for each attribute in the relation. The value must be either a member of the attribute's domain or NULL.

1. What is the name of the SQL mechanism used to qualify (identify) which table a column belongs to in a SQL statement? 2. Provide an example in a select statement involving two tables.

1. Aliases 2. Something using aliases like.... SELECT C.FIRST_NAME, C.LAST_NAME, A.CITY FROM CUSTOMER AS C, ADDRESS AS A WHERE A.ZIPCODE like '75%' AND A.CUSTOMER_ID = C.ID;

1. What feature does the Candidate and Primary key share in common? 2. Which (candidate or primary) type should be used to define foreign key relations? 3. What is the difference between a Natural and Surrogate key? 4. Which (Natural or Surrogate) is preferred when defining a primary key?

1. Both candidate and primary keys should uniquely identify every row in the table. 2. The primary key should be used to create foreign key relations between two tables i.e. the referenced table's attribute should be the table's primary key. 3. A natural key is an entity's attribute that is found in the problem domain e.g. an employee's social security number or email address. A surrogate key is an attribute that does not represent an entity attribute but is defined only to uniquely identify each table row e.g. an auto-increment ID column. 4. Generally, a surrogate key should be created for each table that is referenced by a foreign key or otherwise needs to be uniquely identified.

1. Describe the meaning of the abbreviations DDL and DML. 2. What are the two types of DML supported by many database vendors? 3. Why is a stored procedure more efficient than manipulating rows / records in the client application?

1. DDL: Data Definition Language is used to create, delete, and modify Schema, Entities (tables), Attributes (Table Columns), Views, and other objects maintained by the database server. DML: Data Manipulation Language is used to CRUD information into / from the database entities i.e. Insert, Select, Update, and Delete rows from tables. 2. The two types of DML are Nonprocedural and Procedural DML. Procedural DML performs set-oriented operations on the target entities. Procedural DML provides procedural programming language capabilities such as iteration, branching, temporary variables that allow the developer to integrate complex business rules into the database / schema. 3. The use of a stored procedure is more efficient because the business logic encoded in the procedure is executed on the database server as opposed to retrieving the records across a network connection and performing the data manipulation at the client application.

What are the three DBMS roles described in the slides and the responsibilities assigned to each role?

1. Database Administrators: The DBA is responsible for creating and maintain the DBMS and its databases. 2. Application Developers: Responsible for identifying their applications entities, attributes, and relationships. Works with the DBA to create a database that meets their application's needs. 3. Applications and End Users: Applications or Users that CRUD database information. Applications are given roles that restrict their DBMS access to specific databases. Users seldom are given direct access to the DBMS but CRUD data though the services provided by an application.

What the six advantages of a DBMS are described in the slides?

1. Enforces enterprise standards for representing and managing entities. There are not different versions of the same entity defined for separate applications (no information silos). 2. Reduces application development time. Simplifies the implementation of an application's persistence. 3. Flexibility in how the application data is structured. The database schema can be modified to accommodate new applications or requirements without changing existing code / services. 4. Flexibility in how the application's data is maintained. The methods of maintaining the data on disk is hidden from the application's implementation and can be changed without changes to the application itself. 5. Centralizes the management of enterprise data. A single DBMS can meet the needs of many applications. 6. Centralizes the access of enterprise data. A single repository that can be accessed to gather and aggregate information from many applications.

Define each of the following as being an example of an Entity or Referential Integrity Constraint. 1. Tuple values must conform to their attribute's domain. 2. A relation's attribute that identifies tuples in a second relation. 3. Non-null attributes must be assigned a value. 4. Key definitions must uniquely identify every tuple.

1. Entity 2. Referential 3. Entity 4. Entity

Identify each of the following as describing either logical or physical independence. 1. The ability to provide role-specific views of the database. 2. The ability to add additional attributes to an entity without breaking existing functionality. 3. The ability to assign objects (schema, tables, etc) to drives without disturbing the schema's design. 4. The view of the database as files, buffers, and indexes. 5. Views.

1. Logical 2. Logical 3. Physical 4. Physical 5. Logical

Describe the effects of each of the following CREATE TABLE column constraints. NULL NOT NULL UNIQUE CHECK(expr) DEFAULT(value)

1. NULL: The column cannot be unassigned or assigned a NULL value. 2. NOT NULL: That a non-null value must be assigned to the column. 3. UNIQUE: The values assigned to a column must be unique (no duplicates). 4. CHECK(boolean expr): The boolean expression must evaluate to true for the insert or update to complete. 5. DEFAULT(value): If a value is not provided for the column in an insert statement, the given default value will be used to assign a value in the new tuple.

1. What are the five entities in the University schema? 2. What are the three attributes that are the primary identifiers (keys) for three of these entities? 3. What are the five relationships between these entities? For example, one of these relationships is Course.course_number to Section.course_number.

1. Student, Section, Course, Grade_Report, Prerequisite 2. Student.student_number, Section.section_identifier, Course.course_number (Foreign Keys are not included here). 3. Course.course_number to section.course_number, Student.student_number to Grade_Report.student_number Section.section_identifier to Grade_Report.section_identifier Prerequisite.course_number to Course.course_number Prerequisite.prerequit_course_number to Course.course_number

What are the four advantages that DBMS offer over flat files as given in the slides?

1. The DBMS approach maintains an application's persistent data in a single database. The data owned by all enterprise applications can be maintained in a single DBMS. 2. The DBMS maintains both the data and the metadata (data description) and makes both available to authorized users & applications. 3. The DBMS isolates how the data is physically maintained from the applications accessing the data. The physical organization of the data can be modified (e.g. to improve performance) without having to modify the application. 4. The DBMS can create new data by combining a database's exiting data in application-specific 'views'.

1. Describe how a client's role is used to protect the database's information from unauthorized access. 2. How is a user's role established? 3. Provide an example of a role and access restriction from the University schema.

1. The database administrators can configure a user / client's role to provide or deny access to the schemas, tables, view, and other objects maintained by the database server. 2. The database administrators assign roles to database accounts. These accounts are used to authenticate access to the database server. 3. From the University example, we described a situation where the Student entity maintains sensitive information (e.g. a social security number) which is not visible to the role Instructor but is visible to the role Bursar.

1. How does a DBMS client gain access to the services provided by the database server? 2. What is the protocol used to retrieve information from the database server after client access (connection) has been established?

1. The database server is like most other servers. Clients must authenticate themselves to the server using an ID / Password. Once the client has authenticated themselves to the server, the session is assigned a role which determines their access to the server's database. 2. The client retrieves information from the database using a two phase request / response protocol. The client submits a query (select statement) to the database server and receives the data set produced by the query (called the 'result set').

What are two situations described in the slides where the order of a relation's attributes are important?

1. The default ordering of the tuple's attribute values in a SELECT's result set. 2. The order that the INSERT statement's values are assigned to a new tuple's attributes.

1. Describe the effects of these two MySQL index declarations. UNIQUE INDEX `ID_IDX` (`CUST_ID` ASC) INDEX `ZIPCODE_IDX` (`ZIP_CODE` DESC) 2. Which index is suitable for use as a primary key and why?

1. The first index declares that the values assigned to the CUST_ID column must be unique i.e. two rows cannot share the same value. The first declaration will order the table rows in an ascending value order. The second index places no restrictions on the uniqueness of the values assigned to the ZIP_CODE column. This index will physically order the table rows in descending value order. 2. The index 'ID_IDX' is suitable for a primary key because it is unique and one of the requirements of any type of key is that it uniquely identifies each row.

What are the two circumstances under which the order of the attributes as defined in the create effect CRUD data in the table?

1. The order that the default SELECT * presents the attributes (columns) in the result-set. 2. INSERT statements accepts the new tuple values in column ordering

1. What is the default ordering of a relation's tuples when retrieved by a SELECT statement? 2. What is the default order of a tuple's attributes when retrieved by a SELECT statement? 3. What SQL mechanism can be used to specify a result set's tuple ordering?

1. There is no default ordering of the tuples when retrieved from a relation i.e. the tuples will be returned in any order. 2. The values in a tuple will be returned in the order of the relation's attributes as defined when the relation was created / defined. 3. The SELECT statement provides an optional 'order by' clause that specifies the order of the returned tuples according to the value of the attributes contained in the result set tuples.

1. What is the goal of a "Three Schema Database Architecture"? 2. Describe each of the schemas in this architecture.

1. To separate the user applications from the physical database. 2. Each Schema level: 2.1. External Schema: The schema that the database presents to its users. The tables, attributes, and other database objects that the end-user can see and manipulate. This is the Conceptual Schema filtered by the user's role. 2.2. Conceptual Schema: The database's schema as defined by the database administrators. The actual tables, attributes, etc. defined by the database's data definition. 2.3. Internal Schema: The mapping of the schema (database) objects to a physical installation. For example, 1) The location of the database on the filesystem. 2) The mapping of tables to files, etc. 3) The allocation of memory and other system resources to the database's installation.

How do we implement a N-M relationship between two relations?

A N-M relationship requires an addition table called the "Join Table". The Join Table maintains the referencing foreign keys to the primary keys on the two relations being joined. In the Company Schema, the table 'works_on' maintains an N-M relationship between Employee and Project. Also note that the Join Table can maintain its own attributes that describe something about each instance of the relationship e.g. the number of hours each employee works on each project.

What is the relationship between a database and a DBMS?

A database is a collection of entities (tables), entity attributes (columns), and entity instances (rows) that support the persistence needs of a single application. A DBMS is a system that maintains one or more database. The DBMS provides language parsers, services that maintain database tables on the system's disk, authenticates and authorizes users to access specific databases, concurrency control, and other functions

Describe the meaning of, and relations involved in, a Foreign Key relationship.

A foreign key describes a relation attribute that maintains the value of another relation's primary key. The FK defines a relationship between the two relations. The relation that maintains the primary key is the referenced relation. The relation that maintains the foreign key is the referencing relation. In the Company Schema, the relationship between employee and department is implemented by the FK employee.dno to the primary key department.dnumber.

Describe the (simple) nested query and the correlated nested query (correlated subquery). Describe how these two types of queries are evaluated.

A simple nested query is an independent subquery (select) that provides a result that is used by the outer query to perform some filtering / comparison. (See Slide Using Nested Queries) A correlated nested query is a subquery (select) with one or more filter expressions that are bound to the outer query. In the slide "Correlated Nested Queries", the subquery filter attribute E.dno is bound to the EMPLOYEE row in the outer query. When evaluating a statement with a simple subquery, the subquery can be evaluated once to provide the result needed by the outer query to perform its filtering. When evaluating a statement with a correlated nested query, the subquery must be re-evaluated for each of the rows identified by the outer query i.e. the average department salary must be identified for each employee in the outer query. (Note: This is a general explanation and the DBMS may be able to optimize / reduce the number of actual queries that are performed).

Describe the meaning of 'table scan' and how table scans can be avoided.

A table scan describes the condition where the execution of a query results in the query processor scanning (visiting) every record in a table. This is a performance issue when the table is large. Table scans can be avoided by rearranging the schema or adding a new index to the database.

Describe the meaning and purpose of Triggers in a database design. Research and describe the statements and conditions where a trigger can be attached in the MySQL DBMS. Research and describe why database triggers are evil.

A trigger is a mechanism that executes a function (stored procedure) on the database server itself. Triggers are attached to specific tables and their execution is 'triggered' under conditions that are described when the Trigger is created (CREATE TRIGGER ...). In MySQL (and most other DBMS) a Trigger is attached to a table and is executed when tuples are inserted, updated, or deleted from the table. The trigger can be specified to execute before or after the insert, update, or delete operation. The trigger can optionally include conditional statements that determine when should execute. In practice, triggers are problematic and should be avoided. Some of the reasons cited in the following reference are: 1) Behaviors execute automatically (asynchronously) and without apparent reason. 2) They are difficult to debug. This is especially true when Trigger A can trigger Triggers B, C, D,etc. producing a cascade of database modifications and / or exceptions that are impossible to understand. 3) They execute on the DBMS which makes them difficult (impossible) to debug or to even know they exist. There are several other valid reasons. See: http://rwijk.blogspot.com/2007/09/database-triggers-are-evil.html

Describe what VIEW objects are. What is the reason for providing views in our schema? Describe Query-Based Views and Materialized Views. What are the disadvantages of each view type given in the slides?

A view is a virtual table whose contents / tuples are generated by a select statement that is embedded in the VIEW's CREATE statement. A view provides a method of encapsulating (hiding) commonly used or complex queries from the database users. Second, like interfaces from OO languages, a view provides a façade to the database users whose implementation can be modified without the effecting the clients that make use of the view. That is, the select statement and / or underlying tables can be modified and no changes will be required of the clients so long as the view's attributes remain unchanged. A query-based view is implemented as an underlying select statement which is evaluated each time the view is queried. A second type of view is called a Materialized View which is implemented as a physical table whose tuples are provided by the view's select statement. The materialized view must be periodically updated so that its contents remain synchronized with the underlying base tables. The disadvantage of the query-based view is that the view's select statement must be evaluated each time the view is queried. This may be processing intensive. The disadvantage of the materialized view is that its contents will become invalid i.e. no longer synchronized with the underlying base tables. How quickly the materialized view becomes invalid depends on how quickly the base tables change.

Describe the issue with the inner-join that the use of the outer-join solves. Describe the left outer-join and the right outer-join.

An inner join between two relations will produce a new (join) relation that contains tuples where there is a match between tuples in both the left and right hand tables. But consider the case where there are tuples in left table without a matching tuple in the right (or vice versa). The issue with inner joins arises when a join must include all the tuples in either the left or right hand tables e.g. a report on employee-project participation when some employees are not assigned to any projects. The left outer join will produce a join table with the inner join tuples and the tuples from the left table that have no match with the right table. These unmatched left table tuples will contain null entries for the columns provided by the right table. The opposite is true of the right outer join; all the tuples from the right table with null entries for the left table columns.

Describe the effects of each of the following foreign key constraints? ON DELETE RESTRICT ON DELETE CASCADE ON UPDATE RESTRICT ON UPDATE CASCADE

Answer 1. ON DELETE RESTRICT: If an attempt is made to delete a record that is being referenced by a foreign key, the delete operation will be rejected. This is the default. 2. ON DELETE CASCADE: If an attempt is made to delete a record that is being referenced by a foreign key, the delete operation will delete both the target row and any rows in referencing foreign key tables. 3. ON UPDATE RESTRICT: If an attempt is made to update a referenced key value, the update operation will be rejected. This is the default. 4. ON UPDATE CASCADE: If an attempt is made to update a referenced key value, the value of the key, and the value of foreign keys in referencing tables will be updated.

What are the two uses for schemas in a database management system suggested in the slides?

Any answer that suggests both 1) maintaining a name space and 2) restricting users / client access to the tables owned by a specific application.

Describe the CHAR, VARCHAR and CBLOB data types.

CHAR: This is a fixed length character string. The length is assigned when the column is created i.e. CHAR(10). If an insert or update attempts to assign a value that is larger than the given length, the DBMS will reject the operation. VARCHAR: This is a variable length character string with a maximum size e.g. VARCHAR(200). If an insert or update attempts to assign a value that is larger than the maximum length, the DBMS will reject the operation. Both CHAR and VARCHAR can be filtered in a WHERE clause. CBLOB: Is a Character BLOB (Binary Large OBject). A CBLOB column can maintain very large amount character data e.g. millions and even gigabytes. However the contents of a CBLOB cannot be filtered in a WHERE clause.

What is the acronym used to describe the four basic operation that can be performed on a database's data? Briefly describe each operation in terms of tables, rows, and attributes.

CRUD Create: Create (insert) a new row into a table. Retrieve: Retrieve one or more rows from a database table. Update: Update the attribute values associated with a specific row, or rows of a database table. Delete: Remove (delete) one or more rows from a database table.

Describe the two possible outcomes to the changes made to the database's state during a transaction's execution.

Commit: The changes made to the database's state during the transaction are made permanent. Roll-Back: The changes made to the database's state during the transaction are erased.

What are the two types of languages associated with maintaining a DBMS? What purpose does each language serve?

DDL: Data Definition Language is used to define the tables, table attributes, relationships between tables, and other object maintained by a database. DML: Data Manipulation Language is used to CRUD data into / from the tables defined by the DDL.

1. Define the constraints imposed by a relation's key(s). 2. Describe the difference between: 1. A Super Key and a Key. 2. A Primary Key and a Candidate Key.

Each of a relation's keys identifies one or more of the relation's attributes. The combined values assigned to these attributes must uniquely identify every tuple in the relation i.e. there cannot be two tuples in the relation that maintain the same key attribute values. A Super Key is a set of attributes that uniquely identify every tuple in the relation. A Key is a minimal set of attributes that uniquely identify every tuple. That is, A Candidate Key is a Key in the relation. The Primary Key is the Key that that will be used to identify the relation's tuples in foreign key relationships.

What are the database-specific definitions of: Entity, Attribute, Relationship, and Metadata?

Entity: An object or concept found in the problem domain which needs to be persisted and accessed. Attribute: A property of an entity identified by a unique name and a data type. Relationship: An association between two entities maintained by the database. Metadata: Data about Data or the data maintained by the DBMS which describes a database's tables and attributes.

What were the three entities and attributes used to identify the course names taken by Student 8 during 2008? What order were the entities access in?

Grade_Report, Section, and Course. 1. From GRADE_REPORT, identify the SECTION_IDENTIFIERs of sections taken by STUDENT_ID = 8; 2. From SECTION, identify the COURSE_NUMBER associated with the section_identifiers and YEAR = 08. 3. From COURSE, identify the COURSE_NAMEs associated with the COURSE_NUMBERs identified in step 2

What are the parallels between database design and object-oriented analysis and design?

In both cases, the application architect is interested in identifying the important entities, attributes of the entities, and relationships between entities in the problem domain. In most cases, the entity classes identified in OOA&D are the entities that will need to be persisted in the DBMS.

If two customers have 'NULL' addresses, does it mean the customers share the same address?

No. The NULL address means that the customer's addresses are unknown or the customer has no address.

Describe the three tiers in the classic three-tier application architecture.

Presentation-Tier: The system components responsible for GUI i.e. presenting information to the users. Business-Tier: The system components responsible for implementing business rule as service interfaces. Service-Tier: The system components responsible for accessing the DBMS (and external services).

Identify and describe the three DBMS subsystem that lie between the user's query and the stored database. List in their order of execution.

Query Compiler: Compiles a DML query into a set of primitive data operations against the database tables. Query Optimizer: Optimizes the performance of the data operations against the database's physical structures. Runtime Processor: Executes the optimized query instructions against the physical data stored on the system disks.

Rewrite the following query without the ALL clause and using the MAX() function (the result sets should be identical in case that wasn't clear). SELECT lname, fname, salary FROM EMPLOYEE WHERE salary > ALL (SELECT salary FROM EMPLOYEE WHERE dno=5);

SELECT lname, fname, salary FROM EMPLOYEE WHERE salary > (SELECT max(salary) FROM EMPLOYEE WHERE dno=5);

Describe the result sets produced by these two select statements. select * from employee, department where employee.dno = department.dnumber; and select * from employee, department;

Select 1 pairs up each employee with their assigned department producing the expected 40 rows in the result set. Select 2 pairs up every employee with every department producing a cross-product of both relations and a result set of 240 rows. i.e. |employee| * |department|

Note: Understand the difference between maintaining the foreign key on the referenced or referencing side of the relationship. Difference between 1-1 and 1-M, and efficiency of retrieving the relationship. Note: Understand Cascade Delete. The basic structure of the CREATE, SELECT, INSERT, UPDATE, and DELETE statements. Understand the function of, and abstract structure of an INDEX object. Understand under what conditions the use of an alias is mandatory. The two basic schema modification commands. Be able to write inner, left outer, and right outer joins using the explicit notation.

Shooo, brah, you thought I'd help you with that??

Describe how grouping and aggregation functions are combined to create summary reports. Present a query which provides the average employee hours worked per project. Use the explicit join notation.

Summary reports usually include aggregation (summation, average, min and max, etc.) of information within groups of tuples (department, categories, dates, etc.). For example, the average employee salary grouped by department. This indicates the need to 1) group tuples in the result set by a specific attribute value and then 2) aggregate attribute values exclusively within each group (as opposed to across the entire result set). select pno, avg(hours) from employee join works_on on ssn = essn group by pno;

Describe the IN and EXISTS clauses. Provide a simple example of each.

The IN clause identifies tuples (returns true) containing a value found in a nested relation (usually a nested SELECT statement). SELECT * FROM U WHERE U.a IN (SELECT * FROM V WHERE ...) The EXISTS clause identifies tuples containing a value that is used to produce a non-empty relation in a correlated nested query. SELECT * FROM U WHERE EXISTS (SELECT * from V where U.a = V.a)

Describe why Customer[first_name, last_name] would not make an appropriate key for this relation.

The definition of a key is that every tuple in the relation must be uniquely identified by the combined values assigned to a relation's keys. In this case, the combined values assigned to first and last name will not uniquely identify every customer as there are many examples of two people with the same first and last names.

Describe results of these two statements: select * from employee where lname like 'Smi%'; select count(*) from employee where lname like 'Smi%';

The first statement provides a result set containing zero or more rows of the employees whose lname attribute start with 'Smi'. The second statement provides the number of rows (integer) in employee that matched the filter criteria.

Answer these questions in the context of inner joins: 1. What are Join clauses (conditions)? 2. What are Filter clauses (conditions)? 3. How do Explicit joins differ from Implicit joins? 4. Which type of join (explicit vs implicit) is easier to understand? Why?

The join clauses define the relationship between the two tables participating in the join. The join clause defines the base set of tuples from the joined tables. The filter clauses which tuples defined by the join clauses are contained in the query's result set much like the filter (where) clauses filter tuples from a select on a single relation. Explicit joins describe their join clauses in the FROM portion of the SELECT statement. Implicit joins mix their join and filter clauses in the WHERE portion. Explicit joins are easier to understand because the join and filter clauses are presented in different portions of the SELECT statement. It is easier to understand how tables are joined when their clauses are separated from the filtering clauses.

1. What is a relation's primary key? 2. Referring to the Ecommerce Schema, why is it impossible for the following statement to update (modify) more than one tuple? UPDATE customer SET email=Joe.Blow@gmail' WHERE `id`=123;

The relation ecommerce.customer defines the attribute id as a key. This means that there must be zero or 1 tuples in the relation with the id attribute = 123.

What is the tri-state boolean logic employed by DBMS? Describe why (True AND Unknown) = Unknown but (False AND Unknown) = False. Describe why (True OR Unknown) = TRUE but (False OR Unknown) = Unknown.

Tri-state logic maintains a value is one of: TRUE, FALSE, or UNKNOWN (NULL). An UNKNOWN / NULL boolean value is neither true or false. Its boolean value is unknown and until its value is known the results of boolean expressions containing an unknown value may not be known. (false AND unknown) is false regardless of the value of its second attribute. (true AND unknown) will remain unknown until the value of its second attribute is determined. (true OR unknown) is true regardless of the value of its second attribute. (false OR unknown) will remain unknown until the value of its second attribute is determined.

How do we eliminate duplicate rows from our result set?

We can use the DISTINCT qualifier for the columns we wish to eliminate duplicates.

How can we sort by column value the rows that are returned in a SELECT's result-set? Is it necessary that the columns used to order the result-set are also returned in the result-set's columns?

We use the SELECT statement's ORDER BY clause. This allows us to specify the ordering of the rows in the result set by specific column's values i.e. ORDER BY C1, C2, ..., Cn. No. The columns used to order the result set do not need to be in the set of columns returned by the SELECT.