1z0-071 Oracle Database SQL – COLUMN ALIAS AND CONCATENATION

3. Exception for Inner Single Quotes

What if there is already a single quote present in the string? It will fail because this single quote will interfere with the actual single quotes encompassing the whole string. This can be avoided by adding one more single quote. As an exception to the inner single quote. I’m going to run this SQL select. Today’s state is Systole from dual.

Today’s already has a single quote inside. If I run this, it will fail because this single quote is interfering with the encompassing single quotes. Now, I can avoid that by adding one more single quote, which will let Oracle treat the next single quote as just a regular single quote instead of these encompassing single codes. Let me run this. It works now.

4. Replacing the Quote Delimiters

I use large sentences with lots of single quotes and adding extra single quotes is confusing. Is there a easy way? Yes. We can replace the single quote operators if needed. Use this Q command to replace the quote delimiter. Let us look at the example. Select Date q open single Quotes Open Parenthesis today’s date is close Parenthesis close single Quotes double pipe sys date double Quotes q open Parenthesis Single Parenthesis Double Quotes from Dual now let me explain double Quotes Q Open Parenthesis and close parenthesis double quotes informs that the parenthesis are the delimiters.

Next Q single quote informs that I am going to use a new code delimiter followed by open parenthesis which is the beginning delimiter and then I can type whatever I want and close it with the ending delimiter, which is the close parenthesis then followed by a single quote. Let’s do hands on. This is the regular statement and this statement contains parentheses as the court delimiter. If I want, I can type whatever I want and it will work if needed, I can use any characters as delimiters. Parenthesis is just an example. Here. See? I replaced parentheses with this greater than less than symbol and it still works. Th bye.

5. DISTINCT – Avoiding Duplicates in the Results

We can avoid duplicates in the results by using the keyword distinct. Select Name from Products will bring all the rows. From the name column you can see Apple being present twice in the result. Select distinct name from products will bring only one row of apple.

Select distinct name type from Products will bring only one row that is unique using the combination of both the columns name and type. If you see the combination Apple and Fruit is different from the combination Apple and Computer or I can avoid duplicate rows from the entire table by using select distinct Star from Products.

6. Unlocking the HR Schema and Connecting

Now let us unlock the HR account, which is one of the sample schemas provided by Oracle, and try to use it in the subsequent videos I logged in as the user system. And when I did select star from DBA users where user name equals to HR, I noticed that the account has been locked. So let me unlock it. And let me also assign a password to the user. Okay, the command to unlock is alter user HR, which is the account name account unlock. And the command to assign a password is alter user username, which is HR account unlock identified by HR. Actually, since I have already unlocked, I didn’t need to specify this one. Okay, let me remove it just to show you. This will also work. Now let me connect click this new connection button. Let me call the connection name as HR, user name is HR, and I know I kept the password as HR, and I know the Sid of my database is demo dB. And let me test the connection. Success. Let me save the password and let me save this whole connection entry and connect. I’ve logged in as HR. Let me verify.

7. Explore a Schema using SQL Developer

Now let us explore HR schema I connected as HR and let me expand this. In there I can see a classification of objects like tables, views, indexes, etc. If I expand them, I will then see the objects owned by the user HR. For example, if I expand tables, I can see the tables owned by HR. These are the tables. Or I can see the views.

Now let us go to the tables and click a table. You see the columns of that table here. If I click here, then it changes. You can also see the data of that table here. You can also see the DDL or the command that would have been used to create this table from here. Or you can also do select star from these tables to see the data. So go ahead and explore.