Additional Basic Operations-String Operations

SQL specifies strings by enclosing them in single quotes, for example, ’Computer’.
A single quote character that is part of a string can be specified by using two single
quote characters; for example, the string “It’s right” can be specified by “It”s right”.

The SQL standard specifies that the equality operation on strings is case sen?sitive; as a result the expression “’comp. sci.’ = ’Comp. Sci.’” evaluates to false.
However, some database systems, such as MySQL and SQL Server, do not distin?guish uppercase from lowercase when matching strings; as a result “’comp. sci.’ = ’Comp. Sci.’” would evaluate to true on these databases. This default behavior
can, however, be changed, either at the database level or at the level of specific

SQL also permits a variety of functions on character strings, such as concate?nating (using “”), extracting substrings, finding the length of strings, converting strings to uppercase (using the function upper(s) where s is a string) and low?ercase (using the function lower(s)), removing spaces at the end of the string (using trim(s)) and so on. There are variations on the exact set of string functions supported by different database systems. See your database system’s manual for more details on exactly what string functions it supports.

Pattern matching can be performed on strings, using the operator like. We
describe patterns by using two special characters:

• Percent (%): The % character matches any substring.

• Underscore ( ): The character matches any character.

Patterns are case sensitive; that is, uppercase characters do not match lowercase
characters, or vice versa. To illustrate pattern matching, we consider the following

• ’Intro%’ matches any string beginning with “Intro”.

• ’%Comp%’ matches any string containing “Comp” as a substring, for exam?ple, ’Intro. to Computer Science’, and ’Computational Biology’.

• ’ ’ matches any string of exactly three characters.

• ’ %’ matches any string of at least three characters.

SQL expresses patterns by using the like comparison operator. Consider the query
“Find the names of all departments whose building name includes the substring
‘Watson’.” This query can be written as:

select dept_name
from department
where building like ’%Watson%’;

For patterns to include the special pattern characters (that is, % and ), SQL allows
the specification of an escape character. The escape character is used immediately
before a special pattern character to indicate that the special pattern character
is to be treated like a normal character. We define the escape character for a
like comparison using the escape keyword. To illustrate, consider the following
patterns, which use a backslash (\) as the escape character:

like ’ab\%cd%’ escape ’\’ matches all strings beginning with “ab%cd”.

like ’ab\\cd%’ escape ’\’ matches all strings beginning with “ab\cd”.

SQL allows us to search for mismatches instead of matches by using the not
like comparison operator. Some databases provide variants of the like operation
which do not distinguish lower and upper case.

SQL:1999 also offers a similar to operation, which provides more powerful
pattern matching than the like operation; the syntax for specifying patterns is
similar to that used in Unix regular expressions.

Frequently Asked Questions

Ans: Reason to rename a relation is a case where we wish to compare tuples in the same relation. view more..
Ans: The natural join operation operates on two relations and produces a relation as the result. view more..
Ans: This chapter discusses techniques for securing databases against a variety of threats. It also presents schemes of providing access privileges to authorized users. view more..
Ans: SQL permits a variety of functions on character strings. Read to know about them. view more..
Ans: The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.  A department controls a number of projects, each of which has a unique name, a unique number, and a single location view more..
Ans: Entities and Their Attributes. The basic object that the ER model represents is an entity, which is a thing in the real world with an independent existence. An entity may be an object with a physical existence (for example, a particular person, car, house, or employee) view more..
Ans: A database usually contains groups of entities that are similar. For example, a company employing hundreds of employees may want to store similar information concerning each of the employees. These employee entities share the same attributes, but each entity has its own value(s) for each attribut view more..
Ans: An entity type DEPARTMENT with attributes Name, Number, Locations, Manager, and Manager_start_date. Locations is the only multivalued attribute. We can specify that both Name and Number are (separate) key attributes because each was specified to be unique view more..
Ans: There are several implicit relationships among the various entity types. In fact, whenever an attribute of one entity type refers to another entity type, some relationship exists. For example, the attribute Manager of DEPARTMENT refers to an employee who manages the department; the attribute view more..
Ans: Select clause uses. view more..
Ans: Entity types that do not have key attributes of their own are called weak entity types. In contrast,regular entity types that do have a key attribute—which include all the examples discussed so far—are called strong entity types view more..
Ans: If some cardinality ratio or dependency cannot be determined from the requirements, the users must be questioned further to determine these structural constraints view more..
Ans: The individual entity instances in an entity set and the individual relationship instances in a relationship set. In ER diagrams the emphasis is on representing the schemas rather than the instances. view more..
Ans: The UML methodology is being used extensively in software design and has many types of diagrams for various software design purposes. We only briefly present the basics of UML class diagrams here, and compare them with ER diagrams view more..
Ans: we defined the degree of a relationship type as the number of participating entity types and called a relationship type of degree two binary and a relationship type of degree three ternary view more..
Ans: SQL offers the user some control over the order in which tuples in a relation are displayed. view more..
Ans: The rapid advancement of the use of information technology (IT) in industry, government, and academia raises challenging questions and problems regarding the protection and use of personal information. Questions of who has what rights to information about individuals for which purposes become more important as we move toward a world in which it is technically possible to know just about anything about anyone. 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..

Rating - 3/5