Where Clause Predicates




SQL includes a between comparison operator to simplify where clauses that
specify that a value be less than or equal to some value and greater than or
equal to some other value. If we wish to find the names of instructors with salary
amounts between $90,000 and $100,000, we can use the between comparison to
write:

select name
from instructor
where salary between 90000 and 100000;

instead of:

select name
from instructor
where salary <= 100000 and salary >= 90000;

Similarly, we can use the not between comparison operator.

We can extend the preceding query that finds instructor names along with
course identifiers, which we saw earlier, and consider a more complicated case
in which we require also that the instructors be from the Biology department:
“Find the instructor names and the courses they taught for all instructors in the
Biology department who have taught some course.” To write this query, we can
modify either of the SQL queries we saw earlier, by adding an extra condition in
the where clause. We show below the modified form of the SQL query that does
not use natural join.


select name, course_id
from instructor, teaches
where instructor.ID= teaches.ID and dept_name = ’Biology’;


SQL permits us to use the notation (v1, v2,...,vn) to denote a tuple of arity n
containing values v1, v2,...,vn. The comparison operators can be used on tuples,
and the ordering is defined lexicographically. For example, (a1, a2) <= (b1, b2) is true if a1 <= b1 and a2 <= b2; similarly, the two tuples are equal if all their
attributes are equal. Thus, the preceding SQL query can be rewritten as follows:

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, ’Biology’);



Frequently Asked Questions

+
Ans: The database administrator (DBA) is the central authority for managing a database system. The DBA’s responsibilities include granting privileges to users who need to use the system and classifying users and data in accordance with the policy of the organization view more..
+
Ans: Whenever a person or a group of persons needs to access a database system, the individual or group must first apply for a user account. The DBA will then create a new account number and password for the user if there is a legitimate need to access the database. view more..
+
Ans: Sensitivity of data is a measure of the importance assigned to the data by its owner, for the purpose of denoting its need for protection. Some databases contain only sensitive data while other databases may contain no sensitive data at all. view more..
+
Ans: SQL includes a between comparison operator to simplify where clauses that specify that a value be less than or equal to some value and greater than or equal to some other value. view more..
+
Ans: Four main control measures are used to provide security of data in databases: Access control, Inference control, Flow control, Data encryption view more..
+
Ans: The SQL operations union, intersect, and except operate on relations. view more..
+
Ans: Null values present special problems in relational operations, including arithmetic operations, comparison operations, and set operations. view more..
+
Ans: Aggregate functions view more..
+
Ans: The main Idea for the Sampling Algorithm is to select a small sample , one that fits in main memory of the database of transaction view more..
+
Ans: The main idea of Sampling Algorithm is to select a small sample , one that fits in main memory , of the database of transaction view more..
+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: We have restricted our attention until now to the extraction of information from the database. Now, we show how to add,remove, or change information with SQL. view more..
+
Ans: We introduced the natural join operation. SQL provides other forms of the join operation, including the ability to specify an explicit join predicate, and the ability to include in the result tuples that are excluded by natural join. We shall discuss these forms of join in this section. view more..
+
Ans: In our examples up to this point, we have operated at the logical-model level. That is, we have assumed that the relations in the collection we are given are the actual relations stored in the database. view more..
+
Ans: A transaction consists of a sequence of query and/or update statements. view more..
+
Ans: Integrity constraints ensure that changes made to the database by authorized users do not result in a loss of data consistency. view more..




Rating - 3/5
543 views

Advertisements