 Hello everyone, I am Mrs. Sunita Do. In this video, the topic covered here is structured query language, basic structure of SQL. What is SQL? SQL is a standard language for managing data in a relational database management system. So this language is used to perform the operations on the record stored in database such as updating record, deleting record, creating and modifying tables, views, etc. Learning outcome At the end of the session, students will be able to write the query for given problem statement using operation like and order by clause. In this video, we are going to cover the string operation using like not like and ordering the display of tuples using order by clause. Before considering the string operation, consider what is the meaning of wildcard character? A wildcard character is used to substitute one or more character in a string. Wildcard characters are used with the SQL like and not like operator. Under the following table, symbol percent represents zero or more character. Example, WH percent find WH, white, whole, will, etc. Simple underscore represents a single character. Example C underscore T finds cot comma cat. Simple opening and closing square bracket represent any single character within the bracket. H in square bracket OAT finds hot and the hat but not heat. Simple carrot represents any character not in the bracket. H in square bracket OAT finds heat but not hot and hat. Simple hyphen represent a range of character. So C in bracket A hyphen B T finds cat and CBT. Consider some examples of pattern matching by using the wildcard character percent and underscore. Peri percent matches any string beginning with the peri. Peri percent A finds any values that ends with the A. Percent IDGE percent matches any string containing IDGE as substring. Three underscore matches any string of exactly three characters. Three underscore percentage matches any string of at least three character. These patterns are case sensitive that is upper case character do not match lower case character or vice versa. SQ specifies the string by enclosing them in the single code. For example, Perirage. If a single code character is a part of a string then string can be specified by using two single code character. For example, the string it's right can be specified by it two single code as right. SQ supports a variety of string operations such as quantization using two pipes or the bar converting from upper to lower case and vice versa find the string length extracting the substring etc. For the patterns to include the special pattern or the wildcard character SQ 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. To illustrate consider the following patterns which use a backslash as a escape character. For example, AB backslash percent CD percent so here escape character backslash matches all the strings beginning with AB percent CD and the AB two times backslash CD percent here the escape character backslash matches all the strings beginning with AB backslash CD. Now consider the syntax of these two operations like syntax select column 1 column 2 and so on from the table name where column n like pattern and not like syntax select column 1 column 2 and so on from table name where column n not like pattern. All the examples that are covered in this video are related to the banking enterprise. Now consider the example of like operation. Find the name of all the customers whose street include the substring main. The bank store each customer name the street and the city where the customer leaves. Customer may have the accounts and can take out loans. A customer may be associated with a particular banker who may act as a loan officer or the personal banker for that customer. For example, so for this example we required the relation customer which stores the information about the customer like customer name, customer street and the customer city. From the customer table the street for the tuple number six and the eight is main and the customer name for the street main are Hayes and Jones hence the result of this example contains these two tuples Hayes and Jones. So the query for the given problem statement is select customer name from customer where customer street like in single quote percent main percent. Now consider the example of not like operation. Find the name of all the customer whose street does not include the substring main. So once again consider the relation customer from this customer table except for the tuple number six and eight street for all the tuples does not contain main. Hence the result of this query contains the 10 customer name. So the query for this problem statement can be written as select customer name from customer where customer street not like in single quote percent main percent. Till now we consider what is wild car character and the string operation using like and not like. Now pause this video and reflect on these questions for a minute or two minutes and write your response. Once you return the answer to this question then you can restart playing this video. Question is which of the following option is used to match all the strings beginning with a percent b backslash c percent d. I hope all of you have completed this activity here. We have to find out the correct option which match all the strings beginning with a percent b backslash c percent d. Here the string consists of the wild character percent and the black backslash character. To treat these character as a normal character we have to use the escape character backslash before these character that is a backslash percent b two times backslash c backslash percent d. Since the string should beginning with the a percent b backslash c percent d we use percent after d that is a backslash percent b two times backslash c backslash percent d percent hence the correct option is c. Now consider the ordering of the display of tuple using order by clause. SQL offers the user some control over the order in which the tuple in a relation are displayed. This clause causes the tuples in the result of a query to appear in a sorted order. By default the order by clause lays the item in the ascending order. To specify the short order we may specify DESC for the descending order or ESC for the ascending order. The syntax is select column list from table where condition order by column 1 column 2 and so on up to column n ASC or DESC. Here square bracket means optional part. Example list in alphabetic order the name of all the customers having a loan in perrage branch. For this example relation required are loan and the borrower. A loan originates at a particular branch and can be held by one or more customer. A loan is identified by a unique loan number. So the relation loan contain loan number, branch name and the amount. Second relation is a borrower which denote the association between the customer and the bank loan that the customer have. So the relation borrower contains the filled customer name and the loan number. Here loan number is a common attribute between these two relations. So we can use this loan number attribute to combine these two relations on the common attribute that is the loan number. Here example is we have to list in alphabetic order the name of all the customers having a loan in the perrage branch. So we consider the tuple from the loan relation where the branch name is perrage. So for such two tuples the loan numbers are L15 and L16. For the loan number L15 and L16 in the borrower table customer names are Adams and Hayes. So the result of this problem statement consists of these two tuples in ascending order. Now the query for this example is select distinct customer name from borrower.loan where borrower.loan number is equal to loan.loan number and branch name is equal to perrage order by customer name. So these are some references. Thank you.