Teradata Fundamentals: Hands-On SQL Training | Saad Qureshi | Skillshare

Teradata Fundamentals: Hands-On SQL Training

Saad Qureshi, Online Instructor

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
51 Lessons (5h 58m)
    • 1. Introduction

      2:28
    • 2. Teradata Installation Steps

      13:47
    • 3. Teradata Architecture

      8:48
    • 4. Primary Index in Teradata

      12:15
    • 5. Secondary Index in Teradata

      14:19
    • 6. Set and Multiset Table

      5:13
    • 7. No Primary Index(NOPI)

      10:22
    • 8. Types of Spaces in Teradata

      9:47
    • 9. Union and Union All

      3:43
    • 10. RAID Technology in Teradata

      6:01
    • 11. Create Database in Teradata

      2:14
    • 12. Create User in Teradata

      11:01
    • 13. Create Profile in Teradata

      5:33
    • 14. Basic SQL Commands in Teradata

      9:31
    • 15. Alter Command in Teradata

      9:10
    • 16. SELECT STATEMENT

      2:30
    • 17. Order By Clause in SQL

      5:20
    • 18. Extract Command

      5:22
    • 19. Concat String Function in Teradata

      3:40
    • 20. Substring Function in Teradata

      4:56
    • 21. Index Function in Teradata

      2:24
    • 22. Trim Function in Teradata

      5:21
    • 23. Upper and Lower Function

      0:56
    • 24. Arithmetic Operators

      2:57
    • 25. AND Operator Teradata

      4:23
    • 26. Between Operator Teradata

      6:00
    • 27. IN Operator Teradata

      3:15
    • 28. ISNULL Operator Teradata

      1:06
    • 29. Like Operator Teradata

      6:23
    • 30. OR Operator Teradata

      3:15
    • 31. Aggregate function in Teradata

      6:41
    • 32. Where and Having Clause

      9:43
    • 33. Aggregate Function Example Questions Teradata

      8:14
    • 34. Subquery Teradata

      12:55
    • 35. Subquery Example Teradata

      7:31
    • 36. Correlated Subquery Teradata

      2:11
    • 37. Joins Example Teradata

      19:27
    • 38. Rand And DenseRank Difference

      7:51
    • 39. Analytical Functions: Row Number

      11:44
    • 40. Rand & DenseRank Analytical Function

      6:10
    • 41. Where,Having and Qualify Clause

      6:29
    • 42. CSUM and MAVG

      15:33
    • 43. Case Statement

      9:24
    • 44. Coalesce Function

      2:49
    • 45. Partitioning Teradata

      8:13
    • 46. View Teradata

      5:11
    • 47. Macros Teradata

      9:55
    • 48. Fasload Utility Teradata

      5:11
    • 49. FASTEXPORT Utility

      3:01
    • 50. BTEQ Utility Teradata

      9:09
    • 51. Multiload Utility

      8:28

About This Class

Why Teradata Database:

Teradata is considered to be one of the most popular database management system,famous for large data warehousing applications .Also, It is capable of handling large volumes of data and is highly scalable.

Why to Learn SQL:

SQL aka Structured Query Language is the most popular database language so it's use is everywhere whether we talk about Database,Data warehousing,Data Science or BI.Many big companies like Deloitte,Amazon,Netflix etc using this language to retrieve data from database.Important thing needs to understand that,the basic syntax of SQL  for all databases is the same, so, if you have mastered SQL on Teradata, the use of SQL on other databases would not be a problem for you.

Career Perspective:

If you want to  pursue a career in one of the above fields this course is right for you.In this course, we will make your foundation by starting from very basic and then later in the course,we'll cover some advance topics , so, in-case you are very new or have no knowledge of SQL,by the end this course,you will attain maximum knowledge of SQL.

Another aspect of this course is that not only I am going through all the concepts but also give the practical demonstration by writing SQL queries in Teradata SQL assistant tool.

The pace of this course is very slow,means,I will emphasize ample time on the subject and will try to cover everything that I can.

After this Course:

Once your are done with the course,you will have maximum knowledge of SQL and can apply these concepts in different fields as mentioned above.

Cheers..!!

Have a Great Learning..!!!

Transcripts

1. Introduction: Hello. Welcome to the Teradata SQL Courts. My name is Sarkodie. She and I'll be the Jew throughout this course, as far as my background is concern, I have experience of working in different top notch companies. Also, I worked as a freelancer for several. My major expertise are gentle, his design still going, writing Shell script, getting in debt of it. Hosea So what is s trail invited? Choosing Teradata database. So when it comes to retrieving data from a database used EST will language. So why we are using especially Teradata database. Why no other databases like Oracle? IBM, my sq? So when it comes to handling large amount of debt, this database is highly pop especially. It is widely popular in dental warehousing application. Who are the featured customers off terror that BMW, HP intellects of traditionally some teachers customers. So major topics state we're going toe cover. Here in the course are territory that architecture will start from architecture part. Still, joints later in the course will covered kids coolies, functions, partition and primary index. Some territory utilities, etcetera To will start, we're going to use, uh, Teradata, SQL Assistant tool This tool in this tool we're going to write all our SQL queries. Teradata administrator to is responsible for giving rights to use the database. So when it comes to create bar reverse, um, off the careers, you can look into data warehousing. It'll consultant data signs be a developer, etcetera. So to be part of these feelings, you need to have great knowledge off escape. So not only this course in provide you a great foundation off Eskil. But it will also help you in building your failure. The practice test. You're going tohave some assignments projects, and in the end you will have a comprehensive one are exact. Thank you and have a great learning. 2. Teradata Installation Steps: Hello, everyone. In this particular lecture, we're going to see Teradata installation steps. So this is the guy that we're going to follow? The first step is we're have to download and install Veum were workstation player, So just copy this. Ah, open the chrome browser Down Lord, this VM beer workstation player. So I clicked on this link. No, this is the latest version that you're going to download. Download distillate s version. So look, it is going to download. Ah vm veer 15.5 point one million. So this is the latest version that you're going to download. So since I already have it in my system, I'm going to cancel this. So let me just open Veum beer. We in very workstation pro. Okay, my virgin ist 12. So you you can download the latest version from this website. So this is the first step that you have to download and install vehemently. Workstation player owned your computer system. Second step is you have to download terabyte expressed latest version, So two down nordeste irritate express. Use this link a copy. Biest here, download the latest terrorist express, will you? No, To access this download, you must blogging. So logging toe this logging toe This site Since I already have Teradata counts, I'm not going to create new count. Okay, so I'm just going to log in, log in. So what I'm doing, I'm downloading the Terrorist Express for Veum. Very place of territory. It expresses Di Terra Gator database. So look off, terror that express 16.20 So I will download this latest civilian. This is the latest version off Teradata. Okay, so I really down or death bled to death. I agree. It's a difficult start. The download process. Look, download process has been started. This is going to take some time to download. So since I already have it in my system, I'm just going to cancel this. Cancel this. Let me show you D de de Express, which is present in my system. A directory. So, look, I have already downloaded this fight. Did he express? Okay, so size of this file is properties. Okay? Side of the file list 14 db 14.1 GB. So this is the compressed version. This is the compressed version. Okay? No. Next what I'm going to do used the belittling toe down Lord Terra Gator Tools and utilities . So next after this, you have to download Terra Decca tools and utilities. Just copy this link and based here. So Teradata tools and utilities. So this is the latest for you on T T U 16.20 So I'm not going to use this version. The reason I'm not using this version because this version does not have Teradata Administrator told since I want to use Teradata administrator tools, I will download desperation PTU 15.10 version for Windows Teradata toes in utilities of windows. Okay, down your death. I agree. So, look, downward process has been started, So this is going to take around 20 minutes. So since I already have this in my sister, my will just cancel this. So I have already downloaded this in my system, which is this file TT you express. So what I'm going to do, I'm going to download. I'm going to install this in my system. So tt you based or taxi? Evidently this file? Yes. Okay. Next. Next I accept the terms and conditions click next. So let me just end the directory where I want to install. I want to install any directory. Okay, this click next. So I want to install or DBT driver for Teradata This utilities Fast Lord first export Buttigieg multi Lord t palm. So what other thing I want to Okay. Teradata, SQL Assistant, this is declined toe Teradata Administrator tool. Okay, so this is what I'm going to install in my system. Now click install. So the installation process has been started. It will take some time to complete the installation process. So meanwhile, what I'm going to do, I'm going to use the VM very workstation open a virtual machine. So But first, let me show you d park there. Deterrent. It expresses President E D Express 16.20 This is the latest version. So now I'm going to use this part, Open a virtual machine. Give this part here so I will click this fight dark vm exploit side of the cilice tree. Gaby like this. Okay, barber on this virtual machine. So do not do anything. So meanwhile, let's stick the installation status of terrorists, tools and utilities. - So logging routed the log in and good bar. Third is route No. Next what I'm going to do I'm going to start d Teradata database. So to start Teradata database, you will have to use this command first. What you are going to do? You are going toe right Click opening terminal So you can also click toe this start terror Decca But I'm showing you how to start Teradata database through the mind Biest start. So this is the command that you're going to use to start Teradata database Press Center. So this is going to start Teradata database look services stocking. So this may take some time to start their editor database. Meanwhile, I'm checking the status off the regular tools and utility so it is just about to finish click finish. So Teradata tools and utilities have been installed in my sister. So next I'm going toe open. Teradata Agnes Tree Tear interrogated a school assistant to But before that, I'm going to establish a connection or DBT driver. Well, DBC data sources 64 bit. Okay, here. I'm going to create new user, so select Teradata driver is terracotta Finish name. Get a source name. Let's say de de underscore user 2020 is D user name I P address. So, Victor, Now I want to provide the I P address in order to get the I P. Address. Okay. Started sexist me. Service has been started. 60. See? So what I'm going to do, I'm going to get the I P address. Used the command if gone fig So my i p addresses this I n e d. Address copy death and you have to Ah, okay. North bag based here. So this is the I P address. Okay, so or DBC data source a 64 bit add user Deep ascent characterized. The driver finish. Okay. D d underscores you for 2020. Next, I will provide the i p Which is this, I think. OK, North teeth. Make sure there is no space here. Okay. 192.168 Dark 100 darker physics user name of DBC fostered DBC click ok e d. And just go user 2020. Yes, Click. Ok, now next step is to open Teradata Administrator So let me just close this. So next time going toe open Teradata Administrator Tool Teradata Administrator I'm going toe connect toe this Users If I can sell this, I will connect it through this step Then this you. The Teradata administrator will be connected with issues. Now I'm connected now. Next rival opened E Teradata, SQL Assistant. Oh. So the sister tool, Where you going? To write all your SQL queries. So it is opening. So before before making a connection, you have to make sure that your Veum were injured. ETA basis up and running. So I have already started my database. So you have to make sure that you have to start your database before you're going to make a connection. So next I will meet connection to this user. Do de. I'm just go use their 2020. Okay, I'm connected. Toe disuse it. So So this is what I have from the installation steps. Thank you so much and have a great learning. 3. Teradata Architecture: Hello, everyone In this lecture, we're going to study Teradata in its architecture. So what is Teradata? The other dies One of the most popular relational database management system. It is widely popular in data warehousing applications, so the reason. It is very popular because off its ability off handling large volume off tecca. So now let's look at Teradata components in its architecture. So first Teradata component. There are five components of Teradata. Note passing injured Message Passing layer, which is also known as Beinart AMP, which is also known as excess more Do processor. Last but not the least virtual disk. So first, we are going to study node, so each individual server in a Teradata system is referred as in node. A note consist off its own operating system. CPU memory. Own copy of terror data are due. Bemis Forfeit in disk space. Okay, now let's look at Teradata, another component, which is known as parsing engine. So within passing engine, we have different components, like session control Parcel optimizer in dispatcher. So when a user logs onto Terra Data School assisting tool, it's the job. Off session controller to manage user session Session controller has the ability to manage upto 1 20 sessions. OK, so when a user submits a skill query, it is the job off sparser toe check. If the query sent tactically correct or not. Also part, Sir Tex, if the user has the required privileges off executing the query or not. Okay, so let's look at another component off passing engine, which is optimizer. So the job off optimizers toe create an efficient execution plan. Okay, so once the execution plan is created, it is the job of dispatcher toe past execution. Plan two m's through by Nick. Okay, look, dispatcher well past execution plan toe EMS through by neck or message passing Lee. Okay, let's study dis component message passing earlier. So message Passing Lear is another component off Teradata. So message passing Lear is basically a communication layer between parsing injured in your grams. Okay. It allows the communication between passing engine and aims, and also between the nodes. It received the execution plan from passing engine incense toe M. So message passing lier will receive the execution plan from passing engine in. Then it'll cento ems. Okay. It received the results from Aunt and Cento Parson in that also from AM's, it will receive the result and will pass it toe parsing injure. Okay, basically the communication layer between parsing engine in DRAMs. So let's look at another component off territory dove, it is called em. So what if the concept off AMP and is also known as virtual processes So the other ones that actually stores and retrieve the data from disk. So basically, I'm stored the data on your virtual. Just also am's retrieved a letter from your virtual disk. Okay. And I received a letter an execution plan from passing engine. It performs various calculations. Also, am's perform various calculations under data calculations like that, a conversion aggregation thought ing and finally am started at our own, your virtual disk. Okay, so this is the computer architecture off Terra data. We have the passing injured. We have message passing earlier, which is the communication lead between parsing engine and your amps. Am so your virtual processors, the perform various operations like aggregation sorting, filtering on your data and finally, the store that on your virtual this. Okay, so this is the complete architecture of Teradata. Let's look at the features of terra gator. So Teradata features are unlimited parallelism shared nothing. Architecture. So Teradata works in an independent parallel V. So look, there is no connection between and no connection between virtual disks. So that is the reason why they are known as shared nothing. Architecture, the work in a peril independent way. And there is no connection between each other. Am saying virtual gets their separate. Okay, look, ter feature off. Paradies Linear scalability. Teradata systems are highly scalable, so you can increase the capacity of tear it up by just increase the number off EMS. Okay, so that is the reason why Durant Highly scalable systems. So finally, last but not least, Teradata has robust utilities for Teradata supports utilities like Fast Lord, multi Lord Batik fast export. Okay, so these a robust utilities. So we're going to discuss all these utilities in the later part of the lecture. Okay, so now let's locate the storage architecture off Terra Decca. So let's suppose if I have four Ruth which are Toby inserted in the database Teradata database. Okay, so let's look at the example of first room. Okay. The first road will goto passing engine inside the parsing engine. I have the hashing algorithm hashing algorithm will run on the primary index off this first row. Okay, so it will generate the hash key debt hash key validator mine My data belongs to which am store it. I did it. Will goto am phone store it and to store it or it will go toe and three storage. Let's see this first row will go toe and one story. Okay, so let's say the hash key Greater my instead it will go toe am fun storage. It will go here in the Amster. One story. Okay, What about the second room? So let's say the hash key, debtor. My instead it will go toe am to storage. Okay, So look, third roving Go to, let's say M tree storage. Fourth row will goto am one story. Okay, so in Teradata that I will be evenly distributed across different time story. Okay. Teradata will not put Lord on single lamb story. Okay? It will distribute that that are evenly across different am storage. So let's look at the raw data retrieval architect. So let's say if I have this first, I want to retrieve this first row from database. Okay, So the first rule will goto passing engine here from here, it will go to message passing Lear. Okay, so from message passing Lear, it will search Odetta across all I'm storied but will goto employment storage. It will goto m two storage in entry story. Okay, it will search the letter across. All am stood it. Let's say it will get the data from amp one store it so from am fun storage, it will get the data from AM to an empty storage is it will get and transaction meaning that is not found. Okay, so from here, represented at her toe message passing Lear message passing lier Will pastor that auto passing into. Okay, so from passing engine that I will be same toe client. Okay, so this is the Teradata architecture er so I'm searching that with an i d. Which is not a primary index. So it will search the that are across all I'm storage to get the data. OK, so this is how Teradata retrieval architecture works. Hope you understood the concept. Thank you so much and have a great learning 4. Primary Index in Teradata: in this lecture, we're going to learn terror editors. One of the most important concept, which is called Prime Lee Index. So interrogate up primary indexes used for better performance for Let's learn more about primary index, so probably indexes created while creating a table so interrogate of and you're going to create a table, you have to specify the primary index. Otherwise, terror datable automatically create your first column as a primary index. So let me give you an example here. So this is my table. Die start workers. So in this table, I have SEC imply number as my prime Lee index. So there are two types of time the index unique, timely index and non unique crime UNIX. So if you do not specify anything with this primary index, it will be tricky death, non Junie Primary index. So this is an example off non unique primary. Next. OK, so in order to make this imply number unique primary index, I will have to write as unique giver their unique. So this is an example off unique primary knicks. So let's remove this. So let's executive this table, Okay, completed. Ah, dice refresh this database table so This is my table worker index. Look, Imply number. This is non Junie Primary Index. In order to make this unique primary index, I will have to write like this. Okay, unique primary. Look. First, drop this table. Drop table dice dark Worker Executive This program okay, nor table has been dropped to execute this Kuwaiti again. So create able completed refresh this database DI start abyss refresh no cable worker index , this is unique. Prime Lee index. Okay, so issued. Do not specify a primary index. Teradata will automatically create your first column s known unique prime index for Let's remove this. Drop this table. Okay. No, I'm not specifying any column as a primary. Next. Okay, let's execute this Grady table created. No check. Refresh this di start abyss. Refresh table. This is my cable worker. Okay, Index, employ number. So this will be treated death in non Junie primary. Next. Okay, so let's learn few more points about primary index. So we are done with the first point, which is primary indexes created vile creating a table. So next point, if primary index is used to specify which m gets to their total, So we have already understood the concept off terra date architecture in the territory. That architecture there is a component called parsing engine. Inside the parsing engine, we have hashing algorithm. Hashing algorithm uses primary index to generator. Keep that givers deter mine. Vic m gets today tomorrow. So this is my second point. Third point is, it provides the fastest way to access Director, so we will discuss this point later. But first, I'm going to discuss this point. A timely index may have in maximum off 64 columns. So in a table, you can have only one primary knicks, but you can create a primary index. With the combination off multiple columns, you can have a combination off 64 columns. Last point is parsing engine maintains Deep Primary index. So in short, with the help of primary index, their distribution is possible among different EMS. So I'm going to discuss this point. It provides the fastest way to access the director. So how it is going to provide the fastest? So let's suppose if I want to search their provident idea, which is not a primary next so data retrieval. Vidal Primary Index. So this is my first rope. So I'm going to search this row across all the M storage. So this is my SQL query Select star from imply Their ID equals to one. This idea is not a primary index. Okay, I'm applying a search condition on an idea which is not a prime. The index. So it will goto parsing engine from parsing engine. It will go to message passing earlier from message passing layer. It will goto amp want storage. Okay. It will go to M two storage M three story. It researched the letter across all m storage. Okay. And from storage em to storage and m three storage. So finally, it will get that letter from employment stories. So from M two and M three storage, it will get the end transaction, meaning that are not from from the storage. Okay, So it really sang Baghdad. Better to passing into finally passing in general. Send the data to play. So this is an example of searching that without primary index. So let's look at an example in which we're going to certain that didn't idea which is primary index. So this is my SQL query. So this is an idea employing the score i d. This idea is primary index, so I'm searching their provident idea, which is a primary index. So this is my first truck. So this road will goto parsing injured passing in general, maintain the primary index. So through primary index, I will get to know that my data is present, in which m story whether it is present in m front storage and to store it or it is present in M three storeyed. Let's say in this case it is present in employment storage. So it will go to message passing lier from message passing Lear. It will directly go toe deck em storage where their ties present. So in this case, it will goto am front storage. So from employment store it it will get the data so searching that of it, primary indexes one in population. So from message passing later percent Baghdad better toe passing into. So finally, parsing engine will send the data to client. So data retrieval vit primary in Texas fast compared toa that retrieval without primary knicks because in this case I will not have to search that are across all I'm storage. I will directly go to that particular AM store it. We're dead. Ice present. So in this way, primary Index provides the faster street to access the data. So let's learn more about primary index. So there are two types of crime being its unique primary indexed, non unique primary index. So what is unique Primary index? As the name suggests, Ah, unique primary index allows unique value. No duplicate values are allowed. It is one I'm operation in. Better distribution is even so let's look at data distribution with unique primary index. So Didiza, my unique Ruth all rose as unique in June. EQ Primary nick nor duplicate values are allowed So this first rival goto passing engine. So inside this parsing in and there is a hashing algorithm hashing algorithm will hash the primary index off this first room in a press generated Dickie deck. Evil deter mine, which m storage will get the better. So let's suppose in this case it will goto in front storage right about your to So it will go to M two storage workable grow three. It will goto entry storage, so data distribution is even across all I'm stood it. So when it comes to unique primary index debtor distribution is even across or lamb stories . So let's suppose if I had three Ruth in that case, each hamster ever of God one drawer each. So data retrieval with Junie Primary Index. So in case off data retrieval, it is one amp operation. So I'm searching that evident i D, which is unique primary index. So in this case, it will directly go toe AM front store it and it will get a letter from ample in storage So it will sandbag director toe parsing engine. Finally, parsing engine will send that letter to client, so searching that of it, unique primary indexes also violent operation. So what about non unique primary? Next non unique Primary index can accept duplicate values. It is also one EMP operation. So when it comes to non journey crime Lee Index, I'm goingto have a duplicate rallies. So these are duplicate value. So let's look at how that our distribution works when it comes to non unique primary index . So let's suppose this first wrote this first round will goto passing injured. So let's suppose this first row will goto amp one store it. Okay, work about the second row. Let's suppose the second Roble Goto aim to storage. So since this is a duplicate value the second driver duplicate Really it will also go toe am to storage. Okay, work about this RL destroys also duplicate value. So it will also go to M two storage. Okay, Vertebral destro. Let's suppose this rule will goto m three storage. So these are duplicate rose So duplicated rose are putting Lord, don't am storage So you have to take this into consideration. Vile selecting the primary in the extent the column you're selecting is a non journey crime Lee index should not contain so many duplicate values. Otherwise it really put Lord on your M storage and will affect the performance. So let's look at data retrieval, vit non unique prime phoenix. So this employ underscore idea is non Junik Primary index. So for this first rival, goto parsing engine parsing engine has the information off primary index. Let's suppose it will get this grow from Antoine storage. Okay, so from this, it will go to message passing layer from message passing later, it will goto passing, injured, finally a parsing, and then we'll send that letter to client. So this is how data retrieval vid known unique Primary index works. It is also Evan M. Operation Hope you understood the concept off primary index. In short, prime being exploits the fastest way to retrieve the letter. Also, it can be used to distribute data across different and storage. Thank you and have a great leading 5. Secondary Index in Teradata: in this lecture, we are going to discuss Secondary Index Interrogator. So the Concept Off secondary index is same as primary index, which is to get the result in much quicker time. So, just like Primary Index Secondary Index provides the fastest way to access the data. And also it can be used to a white full table skin elect Prime. The Index Secondary index can be created or dropped after the creation off the table, so primary indexes created during the creation off a table. Various. When it comes to secondary index, it can be created or dropped after the creation off the table. So in a table you can have only one primary index. But when it comes to secondary index, you can have more than one second remained it. So the question that comes into my mind this. What is the purpose Off secondary index if we already have primarily index in our table. So this is our question. So let's look at the reasons off creating a secondary index. So reasons for creating a second green, it's We know the fact that in a table I can have only one primary index no more than one primary indexes allowed in a table. So we also know the fact that certain that of it Primary Index can give me result in much quicker time. What if we come across? It's an area in which we have to search data other than primary, and it's in this case it will go for full table skin. Soto. Why this kind of situation? I will have to create a secondary index so that I can get the result in much quicker time. So let's look at the business scenario in which I can create a secondary index so considered the stable employed table in which I'm going to have these columns, rule number, first name, last name and contact number. Consider my business requirements is that I have to say data on the basis off rule number. So what I'm going to do, I'm going to set rule number as a primary and exhorted, I can get the result in much quicker. So let's say, with the passage of time, my business equipment changes now. My business is that I have to serve better on the basis off contact number, not on the basis off rule number. So in this situation. What options do I have so I cannot change this primary in death. Also in a table. I cannot have more than one primary index. Soto cater the situation. What I'm going to do. I'm going to set this contact number as a secondary index. Sort it. I can get the result in much quicker time. So different Types Off secondary index. There are two types of secondary index unique secondary index and non unique second re index. So first we're going to see unique secondary index. So when it comes to unique secondary index, all values will be unique nor duplicate values are allowed in a unique secondary index, so Teradata will create sub table on E champ once you create the unique secondary index only column Deceptive. A will contain below three informations Secondary Index value Secondary index Roy de last but not the least based Able Rieti. So let's look at unique secondary index creation in storage. So whenever you're going toe, create a unique secondary index, Teradata will automatically create sub table on each other. Like here. There are data has created a sub table on this stamp. On the same, Teradata will create susceptible for contact number only, champ. So this contact number is created as a unique second green. That's rule number is a primary index. Okay, so the next point is values from the unique secondary index column. In this case, contact number will be big one by one, insane to passing in and for hashing. So this contact number has been set as a unique second green X value Off this contact number will be same toe parsing injured inside the parting injured I'm going tohave. The hashing algorithm hashing algorithm will hash these values in a Christianity de hash key. After hashing the value using hashing algorithm in hash map, it will find the destination. And so from hash value, it will find the destination at for any particular really, let's say for this unique secondary index the destination and piss this okay, the destination and praise this so it will store this unique secondary index in the sub table off December in the Destination AIM index really will be stored along with secondary index, Roy D and based royalty. Okay, so I'm going toe have secondary index value. So in this case, I'm going to have this second green Xlu, then the secondary index Rieti then the based table Rieti. So 11 So this 11 here. So this value is present here. Okay, this row is president care for this particular secondary index value So unique. Secondary index value access. Let's say I want to serve that on the basis off unique second grade index. So let's there must unique secondary index value is 0 to 245875 OK, so follow the steps. So first hashing algorithm will hash the unique secondary index value and they presented the hash value. So using hash map, it will find the amp number where the secondary index value is stored. Let's say in this case it will be stored in the in this camp. Okay, 0 to 245875 It will goto this particular ramp from this particular AMP. It will search the based table Rieti, which is 11 No, it will go to imply sub table and fetch the row. I d off the employee base table. OK, Roy de 11 van steroid is received. It will fetch for AMP number way. The based table Raidi resides using Hashmat So from this base table, Roy Gate will goto this particular ramp from this particular AMP. It will fetch this room. Okay, This particular route, Ellen. So after that, it will. It will send this roto the parsing engine. Then the passing engine will sing back. This Roto declined for searching. That average unique secondary index came big to amp operation. It can also be one amp oppression. But, Max, it can be to amp Operation, so consider this value 637 So first it will goto hashing algorithm hashing algorithm will hash this particular unique secondary index Senate will generate the hash. Really? Okay, so using hash map, it will find the destination. And so let's suppose in this case, it will goto this particular 67. It will goto this particular amp so from this particular AM pick will get the based table Rieti. Okay, Trayvon for this based about Roe ideas present in this stamp Trayvon okay, it will fetch this particular Roy and it will send this particular roto parsing in and so passing in general. Then saying this Roto client. So in this case, for this particular unique secondary index, this is one and population, but for this particular unique second green x 0 to 45 feet 75 This is to AMP. Operation Sova Junik Secondary Index. I can have maximum to AMP operation, but I can also have one AMP operation for Let's discuss non unique secondary index so non unique secondary index can contain duplicate values. So this is my table in this table. I have a column first underscore name. I have said This column s non unique secondary index. This column contains duplicate values. Okay, so the difference between non unique secondary index and unique secondary indexes death non unique. Second dreamed acceptable rose, our EMP local. So let me give you an example here. This is my sub table, which is present in the EMP. So in this sub table, I have a column called Based Able Roy de, for the reason I'm calling non UNIX secondary next amp. Local because the based table Row I D off non unique secondary index will not get from other amp. I will get this particular based about Roy de from the local lamp. So, for example, for five fun is the based table. Roy de. I will get this particular room from this base table. So I will get this particular off from this particular ramp. Okay, what about this To one entry even I will get this from this particular room. I will not have to goto other AM toe. Get this particular room. Okay, That is the reason why it is called amp local from the local lamp. And I will get the information. So once you define first name as a non unique secondary index, Teradata will create a sub table Anvil store based about Freudian, the same m. So I will have the base doble Rieti in the same m in which the Roy's president Okay, so if in m contains duplicate first name only one susceptible roof for that name is built with multiple based Roy. These so in case of duplicate roast like here I have this duplicated Ro Bob. So I will have multiple based able Rieti. So 2131 These are multiple row ideas. So, Bob, I will have only one. So look, I will have only one drove it. Multiple based Able Roy it is Okay, So this is non Junie secondary index creation. So when you're going to retrieve that that are using known unique secondary index. Teradata will follow the following steps. Okay, first of all, parsing in general hash this particular index value in it'll generate the hash value. Okay, which will be circulated across or Lambs E Champ Nall start to match the hash value in their sub table. So I m for it does not have the hash value will not participate in this operation anymore. So hash really will be circulated across or lamps after that, Ample start to match the hash value. Okay, so let's suppose in this case, Ellen, it will goto this particular sub table. OK? It will get the based about Roy de from the based table. It will get this particular route. So this rule will be saying toe parsing engine passing engine will send back this roto client. So when it comes to non UNIX secondary index, it is all lamp operation, So hash really will be searched across or limbs. So you must be wondering what difference it makes searching that without knowing unique secondary index, so searching that of it out non UNIX country index will serve the debtor across or lands, but it will be full table skin. But when it comes to non unique secondary index, it will serve the data across or lens. But it will first get the based table Roy D. Ok, then it will go toe death particular Roy the okay. Like hair. 500 people such this particular based herbal royalty and literally get this 500 0 from this base table. OK, it will not have to searched the full table. OK, so drawbacks. Off secondary index for the first in the foremost straw back off secondary indexes secondary index values are storing slab tables. These sub tables are building or limbs. Secondary index requires additional physical structure from maintaining sub tables. Secondary index requires additional io, since susceptible needs to be updated for each room. On the contrary, primary indexes maintained on the parsing ends and there were no additional. I was required for maintaining the in next value. There's a difference between secondary index and the primary index who appeared us to the concept off secondary indexing Teradata Thank you and have a great learning 6. Set and Multiset Table: interrogated. There is a concept offset in multi sect tables, all table stakes existing Teradata are either SEC tables are multi Setubal's. So what is exactly the difference between certain multi sir tables? So sec table does not allow me to insert duplicate values? There s multi sect table allows me to insert duplicate values. So in order to better understand the concept off certain multi suitable, let me give you an example in which I'm going to create a table and we'll try to insert values in it. So first we are going to create a set table. So let's just copy paste this the Syntex create set table employ underscore five. This is the name off table, which I'm going to create. Let's pacify the database name, lets it dies for dicey for database in which I'm creating this imply underscore five table imply Underscore Number is my non unique primary index. If I have to make this implying the score number as unique primary Next, I have to write unique timeliness, but here it will be treated as non unique crime to UNIX. So if I do not write anything, let's say if I do not specify any SEC or multi sect table, it will be treated as SEC table. So let's I want to create a set table set table Guys, start implying the score. Five. So So my set table has been created, so let me insert values in it. Insert into dice. Dark imply Underscore. Five. So imply name like 7 to 3 Department name to one toe. Last name. Let's Sina first name. John Salary. 10,000. Ah, Social Security number. Let's say the baby capital. So this is my first road, which I'm going to insert in my day. Start implying the score five table So Vondra has been inserted. Let's say I've want toe insert the same row so it will be treated as duplicate row. As I said, Set table doesn't allow me to insert duplicate or it will first check if the throw is duplicate or not. So I'm not able to insert duplicate True in my table. So care drew Aaron die. Start implying the score five. So I'm not able to insert the duplicate row, so let's create a multi sect able first I'm going toe Drop this table drop table di start imply underscored. Fife drop this table table has been dropped. No creator. Much dissect, able multi sick. I have to specify this multi sector in order to make us multi sect able. No, let's just run. This table table has been created. Now insert this row in this die. Start implying the score. Five Tober Vonda has been inserted. Let's check it out. Select static from dies Dart imply underscore. Fife Vondra has been inserted. Now I'm going to insert the same row in the same table. So this is my multi sect able, so it will allow me to insert duplicate row. So I am going to insert destro one dro processed. So let me check this Steber. So too Rose have been inserted. So this is my duplicate row. Okay, So in multi set table, it has allowed me to insert the duplicate. True as well. So So this is the difference between sect and multi sec tables over debuting performance wise, which is better said table or multi sect herbal. Since we know the fact that set table check for duplicates value, so if there's a duplicate value, it will not allow that duplicate value. Toby inserted in a table, but in case of multi sect able, no duplicate value will be checked. So in terms of performance, multi set table is better because it does not have to check for duplicate values. 7. No Primary Index(NOPI): Hello, everyone in this lecture of we're going to learn the concept off. No primary index interrogator. So what is the concept off? No Prime Lee indexing Teradata. So whenever you're going to create a table interrogator database, you have to specify the primary index. Otherwise, territory travel automatically create your first column as a prime. The index are no primary index. It's simply a table without a primary index. This Sicher is introduced interrogator to again 0.0 version. Okay, and no primary index table distributes that that are randomly, but it will make sure that get a will be distributed evenly across different EMS. The insert better include table based on random distribution. So when it comes to know Primary Index, no hashing is involved. So therefore, that the Lord will be significantly faster, since there is no hashing involved in no primly index. So let's look at the example how to create a no primary index table interrogator interrogator. Whenever you're going toe, create a table with no primary index, a table must be a multi suitable. It shouldn't be a susceptible. The reason it shouldn't be acceptable because suitable will check for duplicate records in the purpose off Having no primary index is that to speed up the performance off data learning If it is a set table, it will check for duplicate records that could hinder the performance off data lording. So that is the reason why. And no primary index table will always be a multi suitable. So create a multi sect table here. Multi said table dice is the database name Dark Worker is the table name. Okay, employees underscore. I d watch our Dan. This is the data type. Okay. Name Varta, then e m p. Name under school name department. I d in cager position is the another column. Okay, position. It's a varta, then coma Last column off destabilised cilipi, which is an indeed your data type. So whenever you're going to create a table with no primary index, you have to specify the keyword. No primary in gets here. Okay? No primary index. No executor disc. Grady, look, table has been successfully created. No select star from die start worker So let me executor Difficulty now table has been successfully created. Currently there is no record in the table. Ok, now I will use insert in tow. Die. Start worker in tow. Die! Start worker Select star from die Start employees. Okay, so let me just quit this Di start employee table. OK, so all the roads off this table will be in circulating toe this die start worker table. OK, so let me just quickly This equity has been successfully executed. Now Select star from Die Start worker No, I will have records in the dye stockbroker table. Look. Okay, so this die start worker table doesn't contain any primary index. This is no privately index table. OK, this table contained the primary index Die start employees. So in this table, employees underscore ideas De Prime Lee Index. Okay, so this is how you create in no primary index. So let's learn more about no primary in its So let's look at the example off data storage with prime the index. So with primary index, the concept off hashing concerned to play. So consider I have these four rules Debtor Toby inserted dinner table so each row will have primary index. So look at the first row. It will goto parsing injured eso inside the parsing engine, we have the hashing algorithm hashing algorithm hashes deep primary Index off the first row . Okay? April generated the hash key debt hash civil deter mined. My data belongs to which m story. It will goto employment storage, M two storage or it will go toe and three storage. So? So let's say it will goto am prince storage. So Robin will goto implant storage. What about Moroto? Okay, Roto will goto em to storage. Three Will goto entry storage four Will goto am print storage so that air distribution will be evil across all I'm storage. So that timely index, the concept off hashing comes into play so hashing will slow down the process off loading. So let's look at the example off data storage with no prime the index. So when it comes to know, Primary Index parsing engine will distribute the data randomly, but it will make sure that that will be distributed even across all different times. Okay, so it will have even distribution off data. So let's look at the example of these rules now. I don't have the primary index. So the first rule will goto parsing Injun passing in general, randomly distribute this through. Let's say decides it will goto am front store. It Okay, so now it will go to him. Front storage. What about the second row? Let's say passing engine decides it will goto m two storage. Okay. What about the third row? It will goto entry storage for trouble. Goto m front store it so distribution of that I will be random. But it will make sure that that I will be evenly distributed across different EMS for the benefit off. Having no primary index is experience up the process. Soft data lording as there is no hashing involved. Parsing engine will randomly distribute the data. OK, so let's look at the example off Data retrieval weight prime the index. So with primary index parsing in general, maintain my data is present in which am storage identities present it and phone storage, M two storage or entry story. So let's say this road will goto passing Injun. So parsing engine has the information that this data is present in which I'm storied. Okay. For parsing engine really directly goto that particular ramp. Okay, so in this case, it will goto am front storage, so it will be one amp operation. Look, it will retrieve the data from employment storage. Okay, then from here, it will pass it to decline. So this is how you retrieved that of it, Prime Lee Index. So let's look at the example off de territory velvet. No primary index. So when it comes to know, Primary Index parsing engine doesn't know my data is present in which I am storage. Okay, so let's say I have this Route one, it will goto parsing engine from here. It will search the debtor across all am storage. Okay, since it doesn't know my data is present in which I am storage, so it will search it in all am storage. Okay, I am fun am to an entry. So from amp one, it will get the data from AM to an entry. It will not get any data. Okay, so from here, it will pass it to the message passing Lear from message passing Lee, It will pass this information toe parsing injured. Okay, so passing engine will pass this information toe kind. Okay, This is how you retrieve that of it. No primary index. So the benefit off having no primary index in the table is dead. It improves the performance off data loading. But the drawback is get data search becomes very slow as it will search the debtor across all I'm stood it. Okay, so now let's look at the uses in the limitations off. No primary knicks. So the purpose off no primary in this is to improve the performance of data lording. We normally require no primary index tables in the staging environment. As in the staging environment, we normally perform death unloading. So what are the limitations off? No primary index. We cannot create a no primary index on a set table. Multi Lord is not supported by no primary index table update and absurd. These two commands are not supported by no primary index tables. Okay, we cannot define partition Primary index in the hash index on no primary index tables. Okay, these are some of the limitations that our own no primary index tables. I hope you invested this concept, thank you so much and have a great learning 8. Types of Spaces in Teradata: Hello, everyone In Teradata, we have different types of spaces that be useful storing data. We have permanent space school space and we have temporary space. First, we're going to discuss permanence Piece. So what is the concept of Parliament space in Teradata? So when you are going toe, create databases, user stables, views they're going to be stored in your permanent space. Supermen in space is the least when their ties deleted or when objects are dropped from the database. Let me give you an example of permanent space. I'm going to create a table. So let's say I'm going to create a table in my die start abyss, create table dice Dark, ABC. Let's I d indeed. Jer Fellegi. Thank did you. So my table contains two attributes idea insanity. So let me create this table. So I'm going to execute this query able create, Able completed Not This table is stored in my permanent space. The only way it is going to be dropped when I'm going to drop it, You think? Drop command. So let me drop this table. Drop table Di start ABC so destabilise, created in my partner in space. Now I have dropped this table. But this is the concept of permanent space that databases, user tables, views they are going to be stored and your permanent space. So what is the concept? Off spool space Spuul space basically unjust portion after permanent space. So whenever you the runs equity, the output off your query is going to be stored on your spool space. So let's let me give you an example here, so select static from dies, Dort imply. So when I'm going to run this query, I will get an output. Look, I will get this output so this output is going to be stored in my school space. So when I'm going to log out my session, spuul space will be released. Now, my session is log out so this pool space will be released. So this place is mostly used in the volatile tables. So what is the concept off volatile tables? So this is the same takes off volatile table. When a volatile table is created, it is stored in my school space Cevennes user log decision. My volatile cable will be no longer available. So let me just copy paste a Syntex and let me explain to you the concept. So look, this is how you create a volatile cable. Create volatile cable. The sister name off table. Look, I haven't used the database name since volatile cables are created in user space. Norton Database. That is why I haven't specified that database name here. No logs means my terra date of a lot. Maintain any logs for this table. These are attributes off this table. So what? This own commit preserve rules mean? So this means that if you want your data in volatile table after you populated, you have to mention on commit preserve rules. Otherwise, after the Lord transaction, the dirt ties deleted. So let me give you an example. Let's say I have created this volatile table without mentioning this own commit preserve rules. What will happen if I don't mention this own commit preserve rules? Let me comment this out. No look, volatile table has been created, so let me insert values in my volatile cable. Insert into table name department. Let's save 123 celery tent over. Look, let me execute this query Vendrell processed. But when I am going to query this same Palander score table look like me created this table selects Terek from simple look. No data will be present because I haven't used own commit preserve ALS This own commit preserved growth will preserve my rose A device after lower transaction, my data will be deleted. So let me specify this own commit preserve Let me drop the stable drop table. Simple underscore Table for table has been dropped. Let me create this table again with this own commit preserve those so table has been created. Now let me insert values in it. So I will execute this query. So one dro processed Now if I'm going toe equated this table I will get this room because of this own commit preserve roof Look, I will get additional so my volatile table temporarily stores data in in a spool space Savannah session Isla goat. This data in the structure of this table will no longer be available. So let me lago decision. Look, when a log on the session again, this will not be present. So this table will not be present. It will be dropped. Look, object. Simple underscore table does not exist. So this is the concept off volatile table that volatile tables are created in a spool space . It is available for a time being. When you Lago Joe session, it is no longer be available for youth. So when you log on your session, you will see it will not be present in your database for the sister concept off volatile tables. So there is another concept which is called temporary space in terror. Decca. It is also the unjust portion off your permanent space. Mostly temporary space is used by global temporary tables. So what is the concept off global temporary tables and how it is different from volatile tables in global temporary tables. Venice user is logged out. Rules off the tables are delicate, but structure off a table remains their various in a volatile table. Both structure and rules off a table will be deleted. So let me create this noble temporary table so simple. Underscore Table one. Okay, create global temporary table. Simple underscore Table one. No log on commit preservers. I have already explained what this time means. So in global temporary table, I have to specify the database name, so dice dart simple Underscore Table one. Okay, let me create this Look. Table has been created let me insert values in a global temporary table. Insert into denk table. Um, which is diced or simple? Underscore. Table one one do 3 10,000 Vondra has been inserted. Let me quickly this select static from guy start Simple Underscore Table one. Look, I've got this result seven. When a lago decision, this road will be deleted, but it will retain the structure. Look, if I log on the session again, the rules off a table will be deleted, but it will retain the structure off our table. Look, if I created a stable again table structure means there. But that is deleted from the table. So this is the difference between global temporary table and volatile cable. Get in global temporary table that I've deleted, but it retains the structure off a table. Various in volatile cable board data instructor off a table are deleted. So hope you're industry. This concept. Thank you. 9. Union and Union All: Hello, everyone. In this lecture, we will learn about union and union. All union All includes all duplicate rose. Various union removes all duplicates. So the rulers number of columns must be same While you're taking union or union, all columns should have same data types. So in order to better understand the concept, let's see an example where we're going to youth union and union all function. So this is my cable guy start implied table. So in my table I have total 18 Ruth. So this is my second table. Select static from Dice Dart employees. So this is my second table, which contains Let me just run this six Ruth. So first I'm going to take union all So let me just copy paste the rules. So as the rules is, number of columns must be seemed Selects Terek from guys Start implied union. All so so in my employ table, I'm going to take a union off imply underscore Name vid. First name off This die start implies Column for this imply Underscore name. First name off. This die start implies color. So my first table contains eight angels. Second table contains only six rows so in total. Then when I'm going to take a union all in total, I'm going tohave 24. Does this will include the duplicate grows as well for Look, let me just elect Alexe. This also includes the duplicate truth. John John. So, as the rule says, number of columns must be seem so the first table has for Van column. The second table has one column. Second rule is columns should have same data type imply name. In my day, I start employed Table has Vaart Argueta type first name In my day Start Employees stable has worked our data type so bored data types are seem select Mikako Union. So this will exclude all the duplicate values. So I will have only 21 rose. So this has excluded the duplicate values. So in my result, I have no duplicate value. Look all distinct values, for that's the difference between union and union. All seven tons of performance, which is better union function, all union all function since we know the fact that union function removes to duplicate and it has to do extra effort to remove the duplicates. So in terms of performance, union always better because It does not have to do extra effort to remove duplicates. It includes all the duplicate values. Hope you understood the concept. Thank you. 10. RAID Technology in Teradata: Hello, everyone. Incorrect data. We use different data protection techniques to protect the debtor. There is a technique for dist failure and protection techniques by neck, which is also known ISS message passing lier protection techniques. Okay, in this lecture, we're going to study this level protection techniques. Okay. In case off any disk failure, there should be any mechanism that could save the debt. Okay, that could protect the data. So we are going to study rate technology, which is disk level protection, technique rates, stance or redundant array off independent discs, radio the technology that uses multiple physical days to protect that data from a single dis earlier. So basically, the purpose off ray technology is toe ensure that at the time off failure, there should be any mechanism toe protected it. Okay, so there are six different levels. Off rate technology red 01234 in five. Interrogate of youth Red one in grade five. In the recommended version is red vine technology. Okay, since interrogate of use only destroyed technologies. So we're not going to discuss these rare 023 and four. OK, so let's first study read one technology. So what is the concept off Redburn technology. So intrigued. One every disc will have another copy of disk. Okay, So meaning I'm going to have another desk where I'm going toe replicate the data. So in order to better understand the concept, let me show you the architecture off red vine technology. Okay, so this is my guest. So I'm going to have a similar desk like this where I'm going to replicate the data. So in gift, this just feels I'm goingto have another desk where I will have the date. Okay. So I can easily get their data from another desk in case off any feeling. Okay, let's suppose this just feels so if I want to retrieve this the second room, I can easily retrieve the data from this disc. Okay, So this is the benefit off grid one technology that I can easily get that a tough from another disc, So But the drawback off Rayburn technology is dead. It occupies extra space. So when I drive is down the system rights to the functional rife, but not to the field graph. Let's say if this drive is down, okay? The system is going to write data in this particular disc, which will be the functional, right? Okay, so after you replace the field disk, So after you replace this particular desk, the disk array controller automatically reconstructed it on the news risk so that I will be objected in the new disc. Okay, after the replacement of the fairy dust. Okay, so this is the concept off read vine technology. So indri 85 technology get is not duplicated but spread across multiple disks. In addition to this, there is another important information that is called parity, which is evenly spread across all this rate five technology performs piratical collision on the data in stores only the important data with which data can be rebuilt in case off any failure of this. So, basically, in reared five technology, we performed some parity calculation on the data and only get the important that are based on the parity calculations. Okay, so we perform different algorithms and get only the important information through which data can be rebuilt in case off any failure of this. So let me show you d architecture of reared five technology. So this is the architecture off raid five technology, their ties distributed across different discs. Look, block a block A to a tree Look that is not duplicated but spread across multiple. Oldest look, that is spread across multiple disks. In addition to this, there is another important information Get is called parity. So what is this parity so productive will contain only the important information. So through which I will recover the other data. OK, so eso in parity block which information is saved. So a raid five technology performed some parity calculation. Run some algorithms so only the important information is saved in parity block. Okay, so in parity block, only the important information is present. So through the important information, if the disk is down, I will get the other part of the information. I can recover the other part of the information. OK, so vendor drive is down the system rights to the functional rife, but not to the field. It's so in case this disk is down, the system will rightto other drives not to destroy bridges Don't. Okay, so after you replace the failed disk, the disk array controller automatically reconstructed their town, the nudist, So the discovery controller will automatically update the information in the recover disk. Book it. So this is the concept off raid five toe. Hope you understood the concept. Thank you so much and have a great learning. 11. Create Database in Teradata: Hello, everyone In this lecture, we're going to create a database in Teradata. So first of all, I'm going to open Teradata Administrator Tool, I will establish database connection. Okay. Connection has been established. Create databases. Okay. Database name Let Sick Booker work. Arrested database name owner is DBC. So when you're going to install tear editor in your system, there's going to be only one user. Deby seized the user. Basically. Okay, this DBC users will occupy all the space off entire system. Okay, but nine states Let's 100 MB spool Space letter So D m b them space Let turkey and be OK. So leave these things that averted before general and after journal. So desire for backup purposes. Okay, so before General Minster Teradata will take a snap shirt off tables before applying any changes, Okay, after General Minced Red Terracotta will take a snapshot after applying any changes. Okay, so let's create it. No, No. So database worker created. Okay, No open Teradata. SQL assistant established the database connection v invade underscore New. Okay, so now I'm going to add the database. Okay. Vehement disco. New A database. So database name is workers. Okay, look, database has been added. Okay, now I can easily create tables in the worker database. Okay, So this is how you create database in Teradata. Okay. Hope you industrial this concept. Thank you so much and have a great landing. 12. Create User in Teradata: Hello, everyone. In this lecture, we're going to learn how to create a user interrogator Administrator tool. So, as a database administrator, this is my job to create different users. Managers grant or revoked excess off different users. Okay, So in order to create a user first off, all I'm going toe open Teradata Administrator toe. But I have to make sure that my Veum, their machine is up and running. Okay, so first of all, I'm going to open Teradata Administrator tool. So I will establish a connection here. Connection has been established. So? So whenever you're going toe, install terror, attack our database on your system, there's going to be only one user which will be DBC. So d busy is the user that will occupy all the space in the entire system. So So whenever a new user is created, it will take some space from this DBC user. Okay, so let's create a use earlier. User. You, The name user lets a user underscore EP 02 So this is the name of Fuser. Onerous DBC. So if I write DBC here, this user underscore absoluto will take space from DBC If I write the d user. This user underscored AB 02 will take space from De de you. Okay, so I will write DBC for DBC is my first user which will be created when you're going to install Terra Gator that are based on your system. Okay, give a puffer there Permanent space. A permanent space is a space where your tables are created. So when you're going to execute the escalate query, the result off query is going to save on this pool space. OK, so that's the purpose off spools piece Temporary space is the space where your global tables are created. Okay, so let's give nmb dan MB So if you're you, there is such that it will have to create tables on on your system. Then you will have to specify the spaces. So if you have to use just select statements, then you don't need this places. You only required the school space. Okay, because pool spaces of space here that I say for temporary basis. So what about the account? So the concept of a countess account gift the property off the user. So look at these characters and stand for dollar l Stanfel no priety dollar M stance or medium priority. So this means that two CPU cycles will be allocated to perform the task. Okay, for medium priority, user for high priority user, four CPU cycles will be located, so rush will be slightly higher. Priority. Okay, so eight cycles will be allocated for doing this task. So it's stand for dollar are so let's it it's a medium priority user. M dollar him. So what are these variables? Basically, these variables are used to capture the resource consumption at this particular level, for example, and the so this me Instead, it will capture the resource off this particular user at device. Okay, so what about this? So this will capture dear source off this user or Levi's. OK, so let's I want to capture the resource off this particular user device, so priorities medium so Teradata will capture the resource of this particular user device. Okay, so look, so leave it. So default database. Let's say dice. So you that will connect this database. Okay, So profile, name time going to left profile a message. It's severely discuss the concept of profiling rule so later in the electricity's. Okay, so come bank defaulted journal etc. Dice. Give the database name here. Startup String. Leave it as it is. Comment. Let's say you want to give any description for this particular user. So this is important before general after general and fall back so before general means that snapshot will be captured before applying any changes on the table. OK, after general means that snapshot will be captured after applying any changes. This is before and this is after applying any changes on the table. So before general is used for undo the changes after journalists used to redo the changes. Okay, so let's say I will set us after General. Yes, So dual means that it will take a dual copy off the table off the changes that we have done . OK, so fall back means that Teradata will create a copy off table in the AM storage. Okay, it will take a back up off de table. It will create a backup in case off any failure, it will have the backup. So I'm not going to select the fallback option. Okay. Before general? No, After general years. So create a youth earlier, create so you that has been created Lose this option user underscore AP zero to this user has been created in the system. So no, I'm going to open Teradata SQL Assistant to there. Are there a skill assisting toe? Okay, so I will go here in the tools option defying or DBC date ourselves. Okay, so egg new user So the terror data finish Use it. Underscored app. Zero toe. Give the i p So I ps 192 dark one succeed 192 dark 168 Dark 92 Dark fund 29. Let me check this I p again. 92 dark 1 29 Ok. Ah, user name you. The name is this. This is the user name. This is the name. Okay, let's say ap 02 with the name give the positive. Okay. Okay. Yes. No. Okay, I guess in this is the user ab zero toe. Okay, Connect. Come here. AB zero to user. So this is my user ab zero toe, which I have just created in the system. Okay, So connection. No, the user doesn't have any access to get a basic dice. So currently, I don't have access to this database. Okay, look, select star from Dice dark employees execute death. Look, the gooder doesn't have select access to die. Simply so in orderto give access toe this dice database, I will open the Terra Gator administrator tool grant or revoke object, Right. System writes column level writes Logan writes connect to regs. So these are some of the options, so I will click this object. Right? Okay, So database name is dices the database name. Okay. Ah, object type table view. Macro. I will select all. Okay, full database? No. Here I will select the user. So my user issues er underscore AB zero to Okay, user underscore. Absolutely. So this is my user displayed. Okay, Now I can take this all So this will give all rights to user normal. Create drop. So look. So I'm going to give all rights to this particular user. Ground grand completed Now close. No executor. This particular equity look, Kuwaiti has been successfully executed. Okay, In order to revoke right for this particular user, I will go here. Object. Right database. Okay. You the name user underscore AB zero to display. Okay. Select all rebook. It seems that isn't okay. Database name is dice now. Revoke. Look revoked completed. If I able to execute this particular query, I have a lot get any results. So user doesn't have select access to databases. So this is how you create a user. Interrogate, administrator tool. Hope your industry, the concept. Thank you so much and have a great learning. 13. Create Profile in Teradata: Hello, everyone. In this lecture, we're going to learn how to create a profile for different users in Talladega. So But first of all, I'm going to explain the concept of profile. So basically, profile is a common user perimeters that can be applied to group off uses. Okay, so profile has different parent workers account default database, pool space, bastard restrictions. So deter some para meters off a profile. Okay, so we can link a profile to multiple users. Okay, We can create a profile linked to multiple users. So let's create a profiling Teradata. Then we're going to link it, toe multiple users for to create a profile. Tools create profiles here. Okay, Just give the name of profile. Profile underscored. 01 is the name of profile journal A Countess. The medium level security ical dollar M stands for medium level security and the it is going to track the resource consumption off user Davis. Okay. Medium level security and device track. Okay, no. Get a base dice is due. Database Name, spool status, Dan, MB temporary space lights, 10 MB. So password expiry date. So my password is going to expire in 10 days. Okay, Minimum length off passport should be five directors. Maximum lent or fake Character should be 20 characters, maximum land, Maximum attempts. After three attempts, my account will be locked. I will not able to use my account again. Okay, so what is the meaning? Off lock expires. It means that if I write three here, it means that within three days if I will not ableto release the password, my password will be expired. Okay, so I have to fix it within three days off this time. Okay? Reuse after me instead. So let's if I write to hear reuse after to it me instead. If my password expires, If I have to reset my password again, I will not able to use the previous two password. Okay, let's zero Now I can use the previous passwords. So in case I write three I've inaudible to use previous three pastored. Okay. Which I have used you the name. So by default it means that I cannot use you the name as a password by default it is no. So if I write vie for it means I can use you the name as a password. No, leave it as it gets restrict books so there could be some restricted works. Tech. I cannot use it as a password. Okay, so like a company name your own name for these are some vulnerable words. Okay? Which I cannot use it as a passer. So by default it is No, if I can you that I have to write viral. OK, so by defied, it is no did it's so by default, it is No Okay, I cannot use digits in in the passer. So if I have to use it, I will write by special characters. Let's say I have to use it to make Bossert strong. Okay? And for mixed case. Okay, so I can write viol? Yes, it OK, in case off mixed case apparel Orkest. So you the name is this. Okay, so I want to set this profile for these two users. Okay? So profile level perimeters take precedence over you. The level parameters. OK, so let's create it. So profile underscore 01 has been created at this said these two users are added in this profile. Younger scores 01 Okay, now close it. So now we're going to check if the profile ISS linked to a user or not. So I will click toe. This particular user lets a user and a score app, little toe klik. This tools modify you. Okay, look, profiling the score 01 has been linked toe this particular user. So also, I will check do you the underscore AB 01 Okay, now check this tools, mortify user. Look, profile has been linked to this particular user. Okay, Now, both these users have been linked with this particular profile. Okay, so the changes will be effective next time when you're going. Toe said the password. Okay, so this is the concept of profiling in Teradata. Hope you understood the concept. Thank you so much and have a great learning. 14. Basic SQL Commands in Teradata: Hello, everyone. In this lecture, we're going to discuss some most basic school commercial. First, we're gonna look at create table command, create Able Command, will create tables in a database. One stock table is created. We're going to insert values in it using insert command. After that, we're going to look at delete and drop Command the first created table using create command , I'm going to use create command to create a table, create cable name off database in which I'm going to create a table dice. Dice is a database name? Dordt. Name off table. Let's say I want to name a table student indeed, records, attributes or columns off a table. So let's define columns off a table. So my first column off student table will be student I d. Student I d. Then the data type. Let's I want to give in teacher data type in teacher student name Good. Then the land off character. Let's have one to give Dan characters, so if a lent off a student named exceeds 10 characters, it will not allow me to insert this value in a table. It has to be not more than 10 characters for this attribute Student Underscore name. So my next attribute is Student batch student age indeed, Yeah, another important thing by creating a table that I cannot have an attribute which species? So I cannot define this attribute with spaces. So the desire my four attributes off the student table. So when you're creating a table, you have to specify the primary index. Otherwise, Terada Travel assigned your first column as your primary index. It will be treated as known Unique primary index. Let's have to specify this primary index student i. D. So in order to make a unique primary index, I have to write here unique. Otherwise, it will be treated as known unique primary necks, so this student underscore idea will be treated as non unique. Primary Index Selects Creator Table Here Create table completed. This table has been successfully created, so now I'm going toe insert values in it. So let me just Grady this stable dice dart student. So table structure has been created, but I have to insert values in it. Insert in tow. Name of table dice Dart Student first student I D Villages in teacher data type 123 then student name. Let's say, John Student badge. Let's it 2000 five Student itch. Let's see. Drink. Even so, this is my first value, which I'm going to insert in my table. Von Droop Vandross processed. Let me just quickly this table Vondra has been inserted. Let me insert 4 to 5. Truth do 345 four, 56 seven It dub Kim Rock broke Do 22 24 25 seven 55 So let me just insert these values. Four statements processed. Let me just quickly this student table. All draws have been inserted. So using insert command, we have inserted the Decca. So the next commander we're going to discuss it is delete command. So using delete command, I can delete a single row off a table. I can also delete multiple and all rose off a table. So let's use delete command. So this is my table. Select static from guy starts student. Let me just created this table. So I will use delete command and delete a specific grow using delete command delete from die start student there student I d equals to 1 27 so it will delete a row over there. Student I D equals to 1 27 So in my table this is the row their student I d is 1 27 so it will delete only a single room. So let me just execute the query. So one drop processed. So there is. Let me just equated this table student. So in my table there is no row with student i d 1 27 This row has been deleted. So in case you want to delete multiple rows, you will like this to drink I d. In 1 27 1 26 1 25 So it will delete 1 26 this road. And this or two rules 1 26 and 1 25 to rose processed. So I only have two rules President and marketable 1 26 and 1 25 Both have been deleted. So in case you want to delete all rose from the table, you will use deletes toe delete from diced art student So you think this combined you'll be able to delete all rose from the table. Let's just execute disc amand two rows processed as my table contains only to Ruth. Look, all those have been deleted So in case you want to drop the cable using drop command, you will be able to drop the table. So let's use drop. Come on. Drop table diced art student So 18 dropped herbal computer, Your table is no longer be available. Injured database So let me just quickly this object I start student does not exist. So I have to create a new table because my table has been dropped using drop command. So these are some of the basic SQL command statically using Teradata. Hope you're undisturbed. The concept. Thank you. 15. Alter Command in Teradata: Hello Everyone in destructive we will discuss all turn Update commands interested. I school So interrogator Alta Command issues toe ed, delete, modify or rename columns in a table. Vera's object commanders used update Cruz in a table. So in order to better understand the concept, let's discuss these commands. But first I'm going to use all to command. So this is my table, guys talk imply for using Alta Command. I'm going toe. Add another column in my table. So let's use alter command toe. Add a column in a table altar table, then name off table, which is die. Start, imply and column name, Let's say date of. But then specify the data type. Let's say I want to give debtor type Varta the length off this gator type. Let's have one to specify 10 characters, then semi colon. So this is my complete Kuwaiti. This query is going toe. Add another column in my table. So let me execute this look. Altar table completed. Vendrell processed like me. Execute this query. Die! Start implying, Look, another column has been added. So right now there are no values in my column. I can drop the column as well So in order to drop the column, I will just drop drop If I'm going to execute this query this ill drop this gate off birth Qallab, Select me Executor Disk ready soap. So when you are going to drop the column, you don't have to specify the data type. So let's execute this again. Look, table altar table computer to row processed Select me, Execute this guy start employed table. So look, the column has been dropped. So you think, Alter Command, I can modify the column as well. So let's say this is this is my column position. Underscore. And so what is the debtor type of this column like me? See this? So here I can see the debtor type position wards or 50. The data type of this column is worth or 50 for using Alta Command. I can increase the length off this data type. Currently the length of the starter type is 50. I can increase to 60 as a so I will use Ed Egg, then the name off column position underscore. This is the name of column then D data type var Dohr. Let's I want to increase the data type increased the length of data type lecture. Want to give water? 60 Currently it is water 50. So when I'm going toe execute this query. This will increase the length off the data type. I cannot decrease the land, but I can increase the length off data type using this altar command. So let me just to refresh this. I thought table imply Gollum's look position underscore The data type lent has been increased. Now it is 60 Abusing altar command. I can rename Column Zezel. So look select static from guys Dort imply Look salary. Let's have born to rename the Selvi column So I will use rename Altar table die start imply rename salary Do imply underscore salad e So this will change this name toe imply underscore salary So when I'm going to execute this query, the name off the salary column will be changed. Look, altar table completely Two roads processed So let me execute Discovery again Look, imply underscore facility So the name has been changed. So let me change it again. Do salary all charitable computer to rule processed look So it has changed again. So the next command that we're going to discuss areas update commands in Teradata SQL Object Command is used toe update records in a table. So let's see the example off Object command. So this is my table. So this is my table. Die start implied table. So in my table I have a column called Salary. So using up, get command, I will increase each celery often imply by 500 Abdic dice Dart implied certain salary equals two salary plus 500. So currently Shelley off Andy is 1500 Vili, um, 3500. So when I'm going to execute this Kuwaiti, it will become 2000. 4000 two d doesn't. So let me just execute this query. A dangerous processed. So let's just see the salary off Andy. Now it has become 2000 salary off William becomes 4000. So using this command, I have increased each salary often imply by 500. You think abject command I can object multiple columns as well. So this is my first column, which will be objected. Sex l equals to sell replace 500. So the second column I'm going to specify areas. Position position equals two. Senior manager sm stand for senior manager. Okay, there, Department. I d equals two. Let's just seven department I G goes to seven. Let me explain The Kuwaiti Such a legals to salary plus 500 position equals two senior manager Very department I days equal to seven. Very department ID equals 27 Look, these rules sexuality equals two salary plus 500 increased salary by 500. So I will get 1,073,500 and position becomes senior manager after I'm going to execute this Grady, the position of these rows become and shame in each salary will be implemented by 500. Select me. Just execute this query. 20 processed there Selects Terek were department I d equals 27 Look. Department ID equals 27 Position becomes senior manager. ASEM in salary has been incriminated by 500. So this is how you use up. Get command toe update records in a cable 16. SELECT STATEMENT: Hello, everyone. In this lecture, we will learn about select statement and we will see the use off. Selects frequented were close. So let's see the use off. Select statement. So So in a Skrill select statement is used to display the records off a table. So select static from guys dark imply. So let me explain to you the concept this select statement static means display all the records off table from this table dies. If the name of database employ is the name of table, so employed table is present in my dice database. So if I'm going to execute this equity, this will give me all records from the stable. So let's execute this Prestes Green Burton for the execution off this query. So I will get all the rules from the stable. Let's I want to get imply underscore name and position column. So I will write, imply and just go name. I will give the name off column position. So in order to get the specifications columns, I will have to specify the column names here in stared off hysteric. So this will give me reserve itself. Imply name in position column so that select statement. I can use their clothes. Severe closes used to filter the result. So alleged I want to use their closer there. Employ name equals to Andy. So I am using very close to filter the records off this table so their employer and disco name equals two angry. So this will give me the results from this table were imply Name equals two nd. So let's execute describing. So I will get this rule there imply name equals two angry. So this is the concept. Off were cloth and we used were closet select statement toe filter the records. 17. Order By Clause in SQL: Hello, everyone. In this lecture, vivid learn about order by clause in a still major and minor sort in a script. So order by clause is used to sort the data in ascending order or in descending order. So let's look at the example off or the back loss. So this is my table. Die. Start implying. Let me quit this table. So in my table I have a column called Shelly. So using order by clause, I'm going toe sort the salary column in descending order. Look how I can achieve this. Every Jewish order by salary. Name of column, which is salary, which I'm going to thought in descending order then D E S C. Cheever. So this will sort this celery column in descending order. So let me execute this Kuwaiti. So look, the highest Shelley has come first. Then the second how your salary Then the list goes on. So let's I want to sort this column in ascending order. I will use E s c. This will sort the salary gallery ascending order. Look, the Louis ality come first, Then the second lowest salary. Then he turned lower salary and the list goes on. So I can also write the same quickly like this, ordered by five. So in my table. Celery column is the fifth column. This is my first column. 2nd 3rd 4th 5th So I can specify the columns number to sort the result. So in this example fifth columnists, Celery Cola. So let's execute disk ready. Look, Shelley has been sorted in ascending order. This is how you use order by clause to sort their data. So what is the difference between major and minor sought? So when you are going to sort multiple columns, there's a concept called major and minor sort. So with order by clause, I can specify multiple Collins. So let's specify multiple columns here ordered by Alexa Position underscore Descending Goma Salary ascending. So this is this. This is my major start, and this is my minor sought. So when I'm going to execute this Grady, it will first thought this position column in descending order and it will sort this Shelly column in ascending order where there is a tie. Let me explain this thing to you. So first I'm going toe exit your discredit. Look, this is a guy, so here it will sort this in ascending order. The salary values in ascending order. Officer, Look, These two values have been sorted in ascending order. One officer has Shelley 2001 Officer s 3000. So it has sorted these two values in ascending order. 2000 has come first, then three doesn't has come second. So look at managers. So there is a tie here. The result off the Selvi column has been sorted in ascending order. Look, 12,500 has come 1st 10 13,000 14,000 and 14,000. Like white in executive Director am. Look at the result off him. 7000 has come first, then 8000. 9000. 10,000. Let's suppose if I change this to ascending to descending. So let me execute this. Grady, look, the values off. Look, these values are started in first descending order. Then as there is a tie duplicate value. So in the salary column, the result will be sorted in descending order. Look. Okay, look, Officers, treat other office arrest 2000 salaries off. Officer will be sorted in descending order. S I have mentioned the salary in descending order. So let's support ascending order in descending for? Let me execute this look, E So look at this. A living off a cistern. Managers am these. So these results are sorted in descending order. 10,000 has the highest ality office sister manager than 9000. 8000. 7000. Look at the result of director. So too did Evan has the highest century of director then. Scientific 1000 in executive 4000. Both have similar salaries for, like, twice. 18. Extract Command: Hello, everyone in this lecture villain about extract amount. Interrogator. So, interrogator, when you're dealing with date and time stamp value, you used extract amount. So basically the main purpose off extra commanders to get the portion off day, month and year from dick value. And it can also be used to extract or minute and checking from time stem value. Look at the example here. This is my current date, which is 22 13 6 15 So you think extract Kamar Aiken, get here from this date month from this date and day from the date Also, I can get or from this current time minutes from this current time in seconds from this current time, let's see an example in which we're going to use extract amount to extract year, month and day from date. So this is my table. Die start sales. So in this table, I have a column sale underscore month. So from this column, I will get Year day in month. So let's use extract command toe Get here Day month. I feel underscored. Munt extract year from this column Name fear underscore Month extract Month from sale Underscore Month column This is my column name Den Coma extract Game from sale Underscore month. So this is my complete Kuwaiti. So using extract amount, I will get year, month and day. So let me just execute this query. Look, this is my sale month from this sale. Underscore Month column I have extracted year, which is to turn seven month, which is Fort month and gay, which is first. Look, this is what I get from this, Grady. So let me give you another example in which I'm going to use extract Command selects Terek from dice Dark job history. So this is my table in which I have the information off. Employee I d implies joining date and implies resignation date using extract Amman. I've really doctor minder number off experience off often imply so I will use extract command here. Imply I d extract year from resignation date minus extract. You're from joining date, so extract here from resignation date. So from this resignation underscored date column, I will get that year from this park. I will get the ear. So here miners here from joining debt, I will get the number of experience s number off expedience. So let me just execute disk Ready. So number off experience is 37 for this imply sex. Select me. Include joining and resignation date. Ever joining get resignation date. So let me just execute disk ready. Look, I've got the number off experience. This is my joining Dick. This is my resignation date. Resignation date miners joining their This will give me the number of experience for this Imply I d. So this is the concept off extractor mind. 19. Concat String Function in Teradata: Hello, everyone In Teradata we have different string functions that are used to Manu player dust String manipulation could be off extracting a portion off a string Concurred in a different strengths together dreaming blanks from a string converting a string toe a parlour case So these type off manipulation that we do with the district. So let's use dysfunction in our school query. So first we're going to use this can coordinate function in our school, Grady. And we will see the use of dysfunction. So So this is my table in which I'm going to apply can coordinate function Die start implies fuck and catenary function is usedto combine different strings together. So this is the sign off uncoordinated function. So here this is my table in my table I have first name last name. So with the help of this concatenation function, I'm going toe combine first name and last name look first name, then the concatenation function. Then the last name, then coma salad e look, first name and last name have been combined. So So the sister use off concoction Eric function that it is used to combine different strings together. Now look, let me give you another example in which I'm going to use Can captain eight function. So let's say I'm going to write. My name is can get in it then first name I can get in it. Last name. So my name is first name. So it will give me first name. Last name? No can get in it. I'm using in a district and my solidity is then concoct in it this string with finality. So my name If this is the string that I'm using, people could can coordinate first name and last name. Then it will concoct innate this and my salary is it Will can coordinate with the Celtic column. So look what the result I will get if I am going to execute disk ready. My name is Tom Ground and my salary is 25,000. Look, So look as information. Look, I've used Alias as alias information information. My name is Tom Grant, and my salary is 25,000. This is the use of concocting a function that it combines multiple strings together 20. Substring Function in Teradata: Hello, everyone. In this lecture we are going to discuss sub string function. So in SQL sub string function is used to extract a portion off a string. So in order to better understand, the concept lets you sub string function. So this is my table. Die start employed table. So in my cable this my column imply underscore name. So I am going to apply sub string function on this column imply in the school name. So look, employ and just go name school MMA sub string imply Underscore name then 1st 4 So let me explain to you what it means. So this means first means this is the starting position. This means move four characters from the starting position. So let me give you an example here Elect me Ronaldo, let me take this string Granado So where does this mean? So this is my starting position First are are is a starting position Move four characters from the starting Boetticher It means 1st 2nd 3rd 4 I will get our or any from the string. Look, let me run this query. You will better understand the concept. So look, Ronaldo from this Rinaldo string I will get r o N e. So what about district Viliami from the starting position? First move four characters. So this is my starting position. Move four characters Means I will include the first character as the toe first, 2nd 3rd food. I will get the blue eye and I So look here, Vili. Um w i l I So let me give you another example. Three to now. The starting position is three. So from third position, move to directors forward. So look worked about this Trump. Take this example Three, two. So the starting position of the string is 1st 2nd 3rd You so from this position moved to directors. So I will include this position as then for you m So I will get the string. Um from this trump look U m workbook Andy starting position is turd 1st 2nd turd Be Move to directors the 1st 2nd So I will get Divi from District de Vie. So let's say I just want to have first corrector or festering so I will write 1st 1st So I will get the first character off a string Look from Andy. Starting position is a move First Director. So I will include the first character. First, I will get a look. I will get the first character off every string selection. I want to get the last character off a string. So in order to get the last character, I should know the length off a string. So let's lent. I will use land function toe, get the length off a string imply name, so this will give me the length off a string. Let's say the length off this nd stringers. Four. So four and then move one corrector. So I will include this character's off. 1234 Then move on Corrector. I will only get by from the string. So let me just run this query. Look, why am oh? It will print the last character off history. So this is the concept of sub string function that extracts the portion off a string 21. Index Function in Teradata: Hello, everyone In this lecture, Ribble discuss index functioning Teradata. So index function Interrogators used to locate the position off a corrector industry. So let's discuss dysfunction. So let's say this is my table. Die Start imply. So I'm going to apply the index function here Index imply name if the column off dysfunction. So let's say I want to know the position off corrector e in this column imply in their school name. Imply name. So let me just run this query. So look, considered this first drink, Andy. So in my Andy String, the position off directory is first. I will get the first position. What about William? So in William, the position off director is 123455 So let's I want to know the position off. Oh, anecdotal. Let me just run this. So let's look at this string Ronal. It'll select me. So in major Naldo string the position off correct Arrow is second, so it will give the position off first doctrines. So my first occurrence off. Oh, come, Zach. Position to forget ever. It has given me a second position in case in a string. There is no character. Oh, I will get zero position, like in Andy, so I will get zero position. Likewise in William, I will get zero position like this game, Alex. So this is the Concept off index that it look, it's deep position off a character in a string. 22. Trim Function in Teradata: Hello, everyone in this lecture river Discuss trim functioning Teradata. So interrogator trim function is used to remove spaces from a string. Also, it can be used to remove unwanted characters from a string. So in order to better understand the concept, let's see an example there we are going to use trim function. So this is my table. Die start simple table. Let me execute this query. So in my table I have a column first under school name. So in my column I have a string John. So it has spaces at the start of the string breakfast spaces at the end of the string, in barber spaces at the start of the string, I'm going to use trim function to remove all the spaces from the start and from the end of the string, so lets you stream function air dream, then the name off column first underscore name on good school name. So let me a vehicular discovery. So look, it has removed all the spaces. Let me just youth alias for this column let me execute again. So look, it has removed all the species. So this is the benefit off. Using trim function deck it removes all the spaces, both from the start and from the end of the string. So let's see another example in which I'm going toe removed unwanted corrector. So first I'm going to insert the unwanted characters in my table, so lead to this is where cable static from. So you know I'm going to insert some unwanted characters. Look so these rows contains unwanted directors like Tripoli, the the hair sign Dender to play at the end of the string. Let me just insert the these values so two rows have been inserted like me just created this table again. So train first name. Now I want to remove the unwanted character A from the start of district, So I will use leading Correct that e I want to remove correct Terry from the start of the string from the name off column First underscore name so it will remove corrected e from the start of this string. So let me just executing this query so I'm no longer have the corrector e look every good Bobby in case I want to remove from the end of the string, I will use trailing function trailing e from column name Look, This string contains is at the end off this drink so it will remove always. So let's prepare a query in which we're going to remove all spaces. All unwanted characters like Tripoli did the hash signs. So let's make a query. So in case I want to remove all is from the start And from the end of the string, I will use boot bought a from first ring. This will remove a from starting from the end. Look now I want to remove this has sign used dream grilling use board signed boat from this part. Okay, let me just execute Discovery has sign has been removed. Now remove all the species. So I was used Dream So this will remove all the species the stream function Look all spaces all ears from the start and from the bank And all has signs have been removed from the string. So this is the use off cream function that it removes unwanted characters. It removes spaces. Hope you invested the concept. Thank you 23. Upper and Lower Function: Hello, women. So let's discuss apparent lured functions. Interrogator. So upper function converts a string toe upper case Lower function converts festering to lure gifts. So let's discuss this. So this is my table. Die start employee table. So let's use lurid upper functions. A birth imply name Lourdes Implying him. So this will convert district toe upper case the Silicon Vert district to lower case. So let me execute this query. Look. So in my first column, all values are in upper case. In my second call up, all values are in lower case. So this is the concept off apparent lower case. 24. Arithmetic Operators: Hello everyone. In this lecture we will learn about operators in SQL So in SQL there are different types off operators Are automatic operators comparison operators, logical operators. So first we're going to discuss and automatic operators so that automatic operators can perform a automatic operation on numeric values. D that plus addition operators subtraction operator, multiplication division These operators are called arithmetic operators. Let's to see an example off automatic operators. So this is my guy start employee table. So in my employ table, I have a column called Salary. I've warned toe add the salary by 10,000 So, celery, let's sell it e Plus 10,000. I will use addition operative, so I will get it. 10,000 has been added in each salary. So let's just say I want to use multiplication operator. Multiply by 10 so every celery is multiplied by 10. It was 1000. Now, after multiplication, it becomes 10,000. 3000. This becomes $30 so I can use Devean operator. Look 100 divided by 10 1000 or do better and it becomes 103 100. Like vice, I can use obstruction celery miners. So let's just see, this is what I get I can use these operators in were close. So there, let's say, selects Terek their salary blast 1000 less than 20,000. So this is what I get. So these all cell resort less than 20,000. What about 10,000? Dr. Burgert seven. Rules Diva Sandri's left and 10,000. So Century plus 1000 Lesson 10,000. These operators are very helpful when it comes to performing automatic operations like addition subtraction, Devean multiplication. 25. AND Operator Teradata: Hello everyone. In this lecture we will learn about comparison operators and logical operators. So first comparison operators. So these are my comparison operators. This comparison operators are used for comparison purposes. So these are logical operators and or between in like is non These are known as logical operators less discussed these operators one by one first and operator the disses my die start employed table. So we're discussing and operator when you're using and operator in escalate query Every condition has to be true otherwise it will not give you a result. Let me give you an example here So let's it select static from die Start imply there salary greater than 10,000 So this is my first condition So now I am using and are protected and imply name equals two elex So this is my second condition. This is my first condition which is true This is my second condition which is also true So let me just run this query imply name equals to Alex Alecks and his salary is greater than 10,000. So both conditions are true So it will display me reserved So Alex Celery greater than 10,000 Select me give you another example. Select static from die start imply virtual re greater than 10,000. So this is my first condition which is true. I'm using and operator and imply and position equals to see you. So disconnection is not true Dis condition is true but this condition is not true. So if I run this Kuwaiti I will not get any result. So let's say and position equals two Director So eso I will get these visits. So let me write another condition with and operator and imply name He grows too blurted John So this condition is true This condition is true but this condition is not true So I don't have any imply name John who? Celery is greater than 10,000 and his position is director Let me just run this so I will not get any result. So this is Let me run this condition Were celery greater than 10,000 and position Nichols to director So imply name Hobart. So I have an employee whose name is who barked And his salary is greater than 10,000 And he is director. So I will get dishes room Let me just run this Grady so I will get this. Rojo barked. So as I said that and operated, all conditions have to be true in order to get the result. Hope you understood the concept off end operator in a school. Thank you. 26. Between Operator Teradata: everyone in this lecture, we will learn about between operators. So when we used between operator in our school Katie, you basically specify range a parent and a lower range so we can use between operator with numbers, Tex or dates. So, in order to better understand the concept, let's say I want to use between operative ID numbers, so select static from die start imply this is my employee table. So I'm going to use between operator on this salary column and I'm going to specify range There, celery big clean, let's say 10,000 and 20,000. So this is my a polemic and this is my lure limit. So let me just run this. So it will include 10,000 in 20,000 desert so will display all rules. Their salary ranges from 10,000 to 20,000. Also, it really include 10,000 and 20,000. So look, 10,000 and 20,000 are also included in my result. So this is how you use between or proactive ID numbers. Let's say I want to use between operator of it next. So let me just run this query very imply name between e de severe imply name between E and E. So it will display all rose There imply name starts with corrected e director Be director, See, But it will not include their imply Name starts sick corrector d in orderto include very imply name Star said corrector d I was used Linda, sign here. This limbed Ussing. No, If I ran this query, this will also include an imply name which starts from corrector D. Look a b. See, there is no rule present in my employ table. There imply name starts with director. See, that is why it is not present. In my result, so be D B. So imply name which starts from corrector d is also included in my result because I have used limbed a sign here. So this is part of the same text then you are using between operative it Tex in case off numbers and dates, you don't have to specify this limb doesn't Both upper and lower limits are inclusive. But if you're using between operator Rick text you have tow include this limbed assign to lure limit in order to make it inclusive. So let's say I want to use between or protectorate dates in order to do that. I will use sales table di start seals table. So in this table, there is a column US date So sale underscore Munt their seal underscore month. Big Clean said a problem. It 04 01 2007 and 06 01 2007 There is another point I want to mention here this sale underscore month column is off dead data type. So I have to convert this into a gay data type. These two, verily So I have to convert this I will use to underscore debt, then coma month, days, years, then the bracket to underscore gate month here. Now this part is converted into date. This part is converted into dick. So this appel emit in the Blue Limit boat are inclusive for let me just to run this so it will display all the result ranges from this date toe Dace, date This date and this date are part off my reserves. Look. First apple to him. Seven in 1st June 2 2007 So this is how you use between operator with dates 27. IN Operator Teradata: Hello everyone. In this lecture we will learn about in operator in SQL So when you are using in operator in a school query, it basically allows you to specify multiple values in a condition In operator is a matter for multiple or conditions. So in order to better understand the concept, let me give you an example here So select static from die start employed table Let me quit this there celery in now here I am specifying multiple values 10,000 3000 80,000 So 10,000 and 3000 These two salaries are present in my employ table. But this 80,000 century is not present in my employ table. It will only display the result off 10,000 and 3000 liquid. Just query this so in in operator, If one of the value merges it will display the result off debt value here to values are matching 10 13,000 Let's say 180,000 in 80,000. These two salaries are not present in my table so it will only match this 10,000 salary. It will display the result off 10,000 Selvey Look, the soul Ruoff 10,000 So, as I said, it is multiple or condition. If I If I have to write in the form off or operator, I will write it. Their cell equals to 10,000 or salary girls to 90,000 or sell equals 2 80,000 So this is a matter for multiple or conditions. Let me give you another example. Veer Imply name in Andy. Andy, Bob. Jack. So this Jack is not present in my employ name, so it will only display the result off Bob in. Andy look, Bob In Indy, two rules have been displayed. Prince, let's say prints sprints is not present in my employ name. It will only display Andy. So, as I said, if one of the value matches equal displayed a result, it does not have to match all values present in the list. So this is the concept off in operator Hope you industrial this concept. Thank you. 28. ISNULL Operator Teradata: Hello, everyone. In this lecture, Vivi, learn about another operator which is called is null operator. So is null operator Tex, if there is any null value present in my data or not. So in Teradata null value is represented with question Mark. So this is my employee table. So look. And in my celery column, there is a null value which is represented by a question mark. Now let me check it. Using is null operator veer celery. It's not for different display the road where salary equals TUNEL jik So it has displayed the room where celery equals to know celery is normal. It will exclude the Nalle Drew. 29. Like Operator Teradata: Hello. Everyone in this lecture reveal discuss like operator in a spell. So in a skill like operator is used to search a patron in history. So we'd like operator the youth to Vile Card Operators Percent Inc. A new school so percent signed represents 01 or multiple characters underscore signed represents single corrector. So let's see an example. So this is my table. Guys start employing table the d Either some off my questions, which I'm going to solve using like, operator. So I'm going to apply, like, operator in imply underscore name. Call them. So this is the column in my employ table. So my first question is find imply name that starts with Get that e so select static from die start imply there imply name We will use, like operator here like that starts it corrected e single Gort e then person shine So it means the first character will be a Afterwards it can have zero character, one character or can have many characters. So if I let me include imply name here imply name, so it will display all the names that starts it. Director E So Andy Anderson elected all name starts a directory, so imply named that ends it. Get that M Okay, single goat get ends. It corrected him. I m then this percent sign before I m So it means zero too many characters before em. So when I am going to execute this Kuwaiti, it will display all names Debt. Languid character em Vili. Um, it ends. It corrector m came. My next question is I have to find this er patron in a string so it can be in any position at the start middle, or it can be at the end of the string. Single court, then e r percent sign after er in percent sank before here. It means I can have zero too many characters after er or I can have you know, too many character before e. R. So let's run this, Grady said Roger Anderson. In my imply Underscore Name column. There are two names which contained this year string. So in my first name, E. R is present at the end of the string in my second name, you are is present at the middle of the street. So this is how you get e r. Patron in any position that My next question is fine. Imply that has are in the second position. So are in the second Polish. In that case, I will use. And those score saying so Underscore Sign means I can have only a single director. So as my question says, I will have our character, our in the second position. So I will write Underscore first. Then I will write our after deck. I can have zero too many characters so my first corrector could be from A to Z but it will be a single director. My second corrector will be our. After that, I will have zero too many characters. So let's just run this Kuwaiti for these names, contain are connected in the second position Trump and broke to my next question iss find imply name that started correct, Terry in must be four corrector inland So single court that inside my single good I will write first a correct Terry as my questions. A historic name starts with directory Then it must be four correct turbulent So I will use underscore Then again underscore Then again in the score for total, I will use three underscore signs so it will start from CrackBerry. Then I will have three single directors. So my string becomes four characters inland. So let me just run this look. I will get Andi and Alex. These two names are for character relent. And there, named starts. It corrected. He look Andi and Alex. So what is my next question? That imply name that starts it directory. Innings it corrector. And so that starts its single Gort inside the single good right e then percent sign then. Right? And it means between E and and I can have zero character. I can have many characters. Leader. Too many directors. So look what result I will get. So these are some of the examples off, like, operator hope your industry, the concept. Thank you. 30. OR Operator Teradata: Hello everyone. In this lecture we will learn about all operator in escalate So our operator is different from and operator in a vedette so in or operator, if one of the condition is true, secret displayed the result off dead conditions. So let's just see an example here. So this is my table. Die, start, employ. So there celery greater than 70,000 or employ name he goes to, let's say Andy. So in my cable I haven't employee whose name is Andy. This is the road so select Eric from Die Start employ their celery greater than 70,000. So in this table there is no employees who celery greater than $70. So this condition is falls. This condition is true. As I said, when you're using or operator in SQL Query, If one of the condition is true, it will display the result off dead condition. So I will get the result off this condition. So let's check it out. Look, this condition is true, so I will get the result off this condition. So let's say or imply name equals to let's say rock, so I will get that result off Destro Rock and this road to rose. I will get to Rose from this cable. Let's check it out. So Andy Rock select me. Use another or operator for a vision equals to see you. E let me quickly This employee table. So in my employ table I have a column position. So in this column there is no see you. So all I have Officer, Executive Director am manager. But there is no see you. So this condition is fourth. So in this Grady, this condition is false. This condition is for these two conditions are true. So my query evil display there is world off only these two conditions. So let me run this query. Look, I would only get the result off These truth there implies equal toe nd and very political too drunk. So this is the concept off or operator? I hope you invested the concept off or operated. Thank you. 31. Aggregate function in Teradata: Hello, everyone. So let's study aggregate from Children. SQL So, in a skill, we have different aggregate functions like down some average maximum minimum. So Maikon function will return the number of rows in a table and it will ignore eternal value. Some will cal cleared the some off value, so the main definition off aggregate function is get aggregate function is a function. Were the values off, Multiple rules are grouped together to form a single well, So in order to understand this concept, we will see this example. Let's say I have a table century table in which I have to Collins designation in salary. So this is my SQL Grady, and I'm using in aggregate function some celery. So the full return, a single room, a political cleared the some off all the Ruth and will return a single room so it will return a single over just 93,000. So the total sum is not getting thousands. So the sister definition deck multiple truths are grouped together to form a single value here. Like here, it has returned a single value, so let's practically explored the concept. So this is my employee table. So in my employ table. There is a column called Celery in my celery column. There is in a low value. So let me take account off, uh, this table. So this will return. The number of rules in a table for the total number of fraud I have is 18. So in my employ table there are 18 Ruth. So let's take account off. Celery column. So in my salary column, there is one null value, so it will ignore the value in return account. 17 celery. I will get 17 for, like life. So let's stay this some some salary. Celery. That's the average minimum value. I will return a minimum value. So these are aggregate functions. So the next day's grew by close. So we use grew back laws with aggregate functions like some average maximum minimum, etcetera. So group back laws is the Estrella Command that is used to grope identical values in a column. So let me give you an example. I have a salary table in my salary table. I have three columns implied designation celery. So I am using an aggregate function, which is some on salary column. This is my designation column, and I am applying grew back laws on this designation. Columnist In my designation column, I have two similar values director, which is repeating twice. Manager, which is repeating twice. So it's pretty club the similar value as one and will return a single room. So director, it will return a single director and will eggs celery off director look director next century off the directories 58,000 director to deter other $20 which is 58,000. Manager during 20,000 which is too dear to other value off am Welcome as it is. I am 15,070 executive Evict Executive Insanity will come editor So these two values will come as it is because these are not repeating values. So director value will be clubbed as one and it will add the celery off. Director both salaries off. Director, This son in this land and for manager, it will lead to let the sending off manager as there for 38,000 which is two tiered father . So I can grow by multiple columns as well. So this is an example of grouping multiple columns here imply, and I'm applying. Grew by Klaus on imply and designation, and I'm applying the aggregate function on salary. So this is an example. This is an aggregate column because I have applied the aggregate function on the salary column. These two are my no in aggregate columns. So here we're going to consider the values off these two column. So John Director. So it is repeating twice. John Director John, Director. Salary to a deterrent. $28. So it will add the salary off John and Director, which is 58 others for Mike and Manager is a Kring only one time. So it will come as it is Mike and manager. Likewise, Mike and am it will come as it is Donald and Manager. It will come as it is bread executive. It will come enter test the only April club the value off John and director. So salary 30,000 Frontier Teller, which is 58 other. So this is just a concept off group by cloth with multiple columns. So this is how you youth group back loss with multiple columns in the next lecture. We're going to see the difference between there and having claws, and we're going toe use it with the group bike loss 32. Where and Having Clause: Hello friends. So let's study having clause in Teradata SQL So having close can only be used in select queries which contains aggregate function or grew by close. So if he further want toe filter the group or some rise data, we are going to youth Having close let's see the practical implementation off having cloth . This is my employee table. Let's say I want to use an aggregate function some on salary column fatality. So let's say I want oh group. The position called him will be shown group by there. Celery corrected in 10,000 group by Alicia having let's of UN death. So having now here I'm going to use having clothes, having position equals to let the manager No, here have I'm using having cloth. Having fully share equals to manager, I will get the result. So let's see how it is different from we're close. Let's see the difference between there and having close. We can use their clothes with update and delete command, but for having claws, the can or Jews Having close with update and delete, their Lord is used to filter rules off a table. Having clause is used to filter group River. So let's see an example. Let's see. Select Starik from Di Start imply. No, they're fairly greater and 1000 I'm using Were cloth can no you with having draw? No. Because after my definition off having claws, we can only use having claws after the group wizard. So I cannot use having low there. It will give me a better. What about the third point? Once the data eyes group, we can only use their clothes toe filtered e non aggregate column result but only if their clothes nor Jews before Let me explain this point and in close can be used to filter both aggregate and known aggregate columns. Okay, let me explain this point. Lord said, this is my employee table. Look, I'm 13 group by position, so no see the difference between very and having cloth? No. First I'm going toe. You're very close. Their position equals two. Manager. I can use their clothes here because this is known aggregate condom. This is aggregate column. So I asked for my definition. Once they get guys grouped we can only use were closed toe felt that unknown aggregate column, but only if their clothes is no Jews before here. Their clothes is not used above the group by Klaus. So I can You were close here. Look, I can you But I cannot do their cloth. The aggregate column This is my aggregate column there I have used aggregate functions. If I want to filter the result off this aggregate column, I will use having gloss. Look, this is the main difference between very and having close. Look, I cannot you very close here some silly lurch here. Some salary greater than 10,000 greater than it will give me a letter. Look, editor No, If I use having claws, it will be okay. It will give me the river so we cannot do their cloth vidi Aggregate column, which is some. But we can use their claws with deep known agree Get column. But there is a condition if we haven't used a Faircloth above grew by cloth. So the 50 condition so having close can be used to filter bought aggregate a non agree get column. So I can felt that this column with having close look position having position equals store manager. Okay, So what if I have used their close above, grew by close. Look, we're Let's say provision equals to manager. Group by position. Look, there is no salary greater than and talking. Look, no check. Can I use their claws? The death known aggregate color. This is the point. First organized group we can only use were close to filter the non aggregate color but or leave their closest nor juice before So here they're clauses used before group by Klaus. So if I want to further use their cloth, it will give me matter. So I cannot reduce Look provisioning close to manager. It will give me a better. So Editor select has more than you cannot have More than one were cloth so I will use having claw there. Having there is no manager look thick Manager equals two having so the system in difference between having and we're cloth. Let me summarize the concept that if we're closets used before grew back lost, We cannot use their closet. No, in aggregate column. But if we're closing nor Jews before grew by close, we can use it. Look, we can use it. We can use it position. We can use it. This is the more, most important point You need to remember We cannot. Jews there close with this with this aggregate column. This is my aggregate, Conner. I can never filter the result off and we get column. It were cloth. I will juice having closet, having some salary. Greater than can't order this point. You need to remember for any interview. Question Dantos. Look, I cannot use their growth here. It will give me a better. I hope you understood that difference between were in having close. Thank you. 33. Aggregate Function Example Questions Teradata: Hello, Friends. Allegedly Some practice questions on aggregate functions. So this is my simple table. Die. Start implying. So my first question is write a query toe, get the highest Lewis. Some and every salary off all employees. So this is my imply table. This is my question. Ah, yes. Lowest. Some every selling off all employees for high insanity. I will use Max salad E for Louis. Shelley Ivory used men some 30 for average salary able use every Jeep. So no, run this. I will get maximum minimum total and every chili off old implies. So what is my second question? Right? Equity toe. Get the every century in the number of employees working with the department number seven The copy best this. So in my employ table, I have the information off department I D. So, Department number seven. I want to know the average salary in the number of employees working with department number seven. So everything ality and number off implies. I've warned the count, working with department number seven there. Department ID equals to sever equals tooth. Ever. So there are three employees who are working with department seven. They have ritually leave this So what if my next question, write a query toe, get the number for implies with same department. So I have to copy this. Get done a murder if implied, that the same department. So let me just go. Elytis, check the department. I d. First. So there are two implies who are working with department. I did one for department. I get three. There are 123 three implies. No, it's just a bit of a Kuwaiti for this question. Get the number of employees. Okay, department I d go. Goodbye. By department. I d in DC I'll get to know so ordered by one ascending order. So department for the park for department I d Even there are two employees for department I get to. There are one employees like vice for department number 11. I have one imply for department number seven. There are three implies. So this is the question. Right? Equity to get done. A murder from President, Same department. What if the next question right equity to get the department I d. In the total sanity people in each department let me copy paste trip department I D. Which is this and total salinity. Total salary for total salary. I will do some in total salary payable in each department group by department. I d so or did by one. So for department ideal one, but total salary is 17,500. Department I D to total salary is 12 other. Likewise. So this is the question. So my next question is write a query toe, get the implies position in the maximum salary off the employees where the maximum salary is greater than or equal to 10,000. So first Okay, First, I will quit this imply table, so I want to know implies position. So first position, a vision, maximum salary off the employees. Maximum salary. Okay. Implies Alicia and the maximum salaries off the employees. Their maximum salary is greater than or equal to 10,000. Group by Alicia. Let's say I have two managers. So from two managers, I will have to get the maximum salary meant to director from Director. I will get to know the maximum salary. So the maximum salary of director if 29 32 Children. So what about the manager Maxim facility off the manager is to Dean told her. So this will give me. So look, no. The next part off my question is whose maximum salary is greater than or equal to 10,000. So here, having maximum sanity greater than equal toe 10,000 so did are two positions. Manager in directed. So this is my last question. Hope you can just sort these questions on aggregate function. Thank you. 34. Subquery Teradata: Hello, everyone. In this lecture, we will learn about subclavian f girl some queries equity within another SQL Grady. So let me give you an example here for defense. My Estelle Kuwaiti. So in this Kuwaiti, I have the outer park in the inner part. So the inner part is known as the sub Clady. So this is my out of our prevent. This quake is going to be executed. The inner part is going to be executed first. Then the value off in a part is going to be compared with the outer part off Mike lady. So sub queries can be used with select insert, update and delete statements alone with operator like equals two less than greater than greater than equals two between etcetera. Here I have used in operator. So in order to better understand the concept off subcommittee, let's do some practice questions, own some equity. So these are my practice questions which I'm going to do. My first question is like me copy paste er so right, right. A query to display the first name last name and celery off doors implies who get more. Send reading the employee whose idea if 334 So in this question, there are two parts. This is my first part. This is my first part, and this is my second part off my question. So first I'm going to calculate the salary often imply whose ideas? 334 This part is my inner part off the query. So let's calculated Selects Terek from die. Start implies this is my table in which I'm going to write a school. Grilli. So there imply I d equals 2334 Reality. This is my inner part off this question. Look, the outer part off my query is right. Equity to display the first name last name and celery off doors implies select first name, last name salad E from di Start employs. We're we get more Sandy. Were salary greater than disability? The inner part off my query will be in closed in a Prentice's break it. So this is my complete Grady. This inner part is going to be executed first. Then the result off this inner part will be compared with out the part off. Mike. Weighty This So we're celery greater than this trend Other. Let's run this. This is what I get from disk Lady. So what is my second question? Right? I skill quickly to display the first name and ask him for all employees who earn more than every cent. Let's copy. Paste it. So So First, I'm going to calculate every disability. This is my inner part off my lady food. Select every salary defaced my everything Which is this last name? Okay, first last name from Okay, we're salary greater than every facility. This is my complete Kuwaiti for these are my employees First name last name who earn more than the everything free Their salaries are created And there every salary off all employees. So this is my second question worked about 1/3 question Let's copy. Pasted. So in a part off Mike Lady is right. So right that Cueto display first name and last name for all employees who belong to departments 35 11th I'm going toe calculate implies who belonged to department 35 10 So let's just remover. So I will use die store Department Department I d Okay, there, nurse, Just given Alias e your department I d in 35 Dan department I d either three, five or 10 Department I d you don't department. I do. This is my employee table dice Dort employ. We're be dark department I d in This is my inner Kuwaiti or some lady. This is my outer part off my Grady first name. Last name. Apply the department I D in. So this is department idea. These are implies who belonged toe department 35 or 10. This tree bark. So what is the fourth question? Calculate the second highest salary. Often implied using subcommittee no second. How you silly? No, it's just removed this dice door employ. Okay, facility. This will give me all 30 list off also. So from this list, I will get the maximum 70 Amused Max for the maximum facility from the listers. 28,000. Let's just strike 20 years old. Okay, No second highest salary. Select Eric. Strong dice, Dark employ. We're fatality less than so. This is my maximum facility. So this is my okay from this query I will get Didn't maximum facility from this greedy with just 28,000. So from death list. Okay, Salary less than 20 year 2000 salary. Leicester 28,000. I will get all the list Salary left in frontier Tugger. I will get all salaries accepting gay target. So list off all seven centuries from this list, I want maximum sanity That maximum salary will be my second high acidity. So, Max, this will be my second. How your sanity? We just 25,000. Nurse, just check it. Lady Dyes, Dart imply. It's so the second highest. Then it is first going to your talents. Second, how your salary is 25 food? What about the last calculate? The third highest century often imply using sub quitting, I must say. Want to calculate that turd I get? So So this result will give me the second. How? Your salary? No. What if 25,000 so select, so dies Dark imply we're reality left, then disability. So this result will give me second highest salary foot. Certainly. So this result will give me list off all salaries less than second highest. So if I write so the third How your salary is 23,000 nurses jacket flick, aesthetic from dyes. Talk imply fatality. 1st 2nd turd, which is 23 times 35. Subquery Example Teradata: Hello, everyone. In this lecture we will see how we can use insert of debt and delete command using some quickly. So these are my examples off abject delete and insert comer. So my first question is, let me just copy paste it of Dick. First name to who bark a last name choking off implies were imply Get more century than the imply. Whose ideas? 339 1st I'm going toe find implies who Celery iss greater than imply salary. Whose ideas? 339 So? So this is my We're imply I d equals 2339 salary More than that. Employed. Whose ideas? 339 So first celery often imply whose idea is 339 this so select static from dies Dort Employ where celery Greater than more than get Morceli Dinner Imply whose idea? If 339 So so first name is bread Last name If Lee So this is my imply who salary is more than the imply whose I gave 339 So I will update his first name and last name. So I used update command of debt said so. Either you said giver toe abject first name equals to Hobart, then coma. Last name equals toe again. Imply I d 336 So currently the first name in the last name off implying Get 336 If Bradley the ones I updated the first name in the last name will be changed So let me just run this wonder Processed No Selects Terek from dies dot Imply there imply I d equal to 336 Hobart in King. So it has been updated. Delete rule off, imply stable will imply Get more celery than the employees who I do this 339 Okay, this is my question, Dele Drew's off implies caper, they're imply gets more celery Then the employee whose idea is 339 from die start implies Okay, who's imply I d is 339 1st there imply I d equals 2339 Salad e Now the first part off my question very imply get more sanity. So select static from die start imply their fidelity Greater than this ality whose ideas? 339 Cilipi So the leaved so delete from there is only one rule delete from Di start employees were celery greater than disability, so I will. It's a wonder processed. So what is my turn question? Insert data. Let me just copy, paste it. Insert data into another table from employ stable their imply ideas. 331 food. Select static form dice. There imply ideas. 331 So I want to insert death row into another table. So insert indoor name they believe dies dark. Let's see able to So this is my cable, so select static dice dark. Hey, buddy, too. It has no erected. So I want to insert the through in tow in this table. So when I execute this Grady, this rule will be inserted into this table. Die store, table to table. So let me just run this The Wonder Process table do different how you insert decked out into another cable using sub Clady. 36. Correlated Subquery Teradata: Hello friends. So in this example we are going to see the difference between non core elected, some quickie and co related security. And we will see how a non coordinated security is different from correlated sub pretty So first non correlated subcommittee. So in non correlated sub pretty the inner part off my query is going to be executed first and it is going to be executed only one time. Then the value off in a part of Macquarie is going to be compared with the outer part. So another important point is that I can run the inner part off my sub pretty independently . Look So this is my inner part. So there is in court related sub query the inner part off My query cannot be run independently. Look, reason being reason being the inner part off My query is dependent upon the outer part off Mike Weighty. So that is why in core elected sub Clady, the inner part off my critic cannot be run independently. There is in non correlated subcommittee weakened Runda in apart independently. One more important point is in non correlated subcommittee This inner part is going to be executed. Only one time and the value off inner part is going to be compared vidi out apart there as in court elected sub Clady, each time the outer query is going to be executive iniquity is also going to be executed. So Lester's Runde non correlated sub query in cedar result, I will get this result with deep non go related sub query. So with correlated subcommittee, I will get. This is disputed. So that's the main difference between correlated in non core, really good. 37. Joins Example Teradata: Hello, everyone. In this lecture, we are going to do practice on a stroll joints for these are my practice questions. Before I get into these questions, there are three tables in which we're going to write SQL Queries. So which arm are these tables? Guy? Starting ploy table in which I have all the information off imply imply First name, last name imply I d department I d. That end in price sanity department table in my department table. I have the information of departments department I d Manager I D. So what about dies? Dark job underscore detail table in this table. I have the information off designation employee I d started and they dis started Tells me that in which did Imply has started his job. So end it imply, has finished his job. So this is started indicts. So let me begin with the first question. So right a still ready to display first name? Last name and department named for implies were earning a salary between 10,000 to 20,000. Let me copy bay this question. So write SQL query right. The SQL query to display first name last name in department name for each imply for implies were earning a salary between 10,000 to 20,000. So first name and last name thes two attributes I'm going to get from this imply table. These two columns First name, last name, department name. I will get from department table, so I will make inner joy in a joint DI store department. Yeah, if you say Alias beef on condition. So in my in my employ table, employ stable. I have the department I d department I d in my department neighbor. I also have the information off department idea. I'm going to join employees department ITV it departments department I d. Look, department I d on a dark department. I d. My idea equals to be your department idea. From with last part of this question is were earning a salary between 10,000 to 20,000. We're solidity. He adored facility between 10,000 and 20,000. So let me clean. It is first name a dork list. Name a door. Last name, department name be door department name. They're a torch. Led between 10,000 and 20,000. So did out four employees whose salary ranges between 10,000 to 20,000. So department name. First name, last department name. So this is my first question. So work about my second question, right? A skill quickly to display first name, last name and department and for employees working in departments 11 12 18. So this is my second question working in department 11 12 or 18. So first, run this. I have to make a little bit changes in my first question query for a steam lasting department working in department there in 11. 12. 18. This is my eager first name. Last name. Depart. My name implied department. Theodore Department. I debido department. I do their Beetle department I D. In a level 12 or 18. So there are two implies for this department. I d either. So I guess select static from Di Start department. Let me check department i d 11. So 12 department I did doesn't exist in the department. I did table. That is I It hasn't showed me in the result. Look, So there are only two implies 11 or 18 department I get well doesn't exist in the department table. So what about the toad question? Right? It s still pretty to display the designation department name, Full name first and last name off implies. And starting date for all jobs that started door or after first generally ended with before General full, This ending gate is basically a resignation. So in disgusting, I'm willing Gate three tables first. Select static. Let me copy basis. So my first part off the question is to display designation department name, full name first and last name off imply. Okay, the second part, if starting date for all the jobs that started on after first, generally turned four and ending with before first generally covered it. So in this question, I will get three tables. Phone dies, dark imply implies the last table di start job did. So these two table vacatur. The first part off this question in the last part of this question will be catered by this table home from this table. I will get the information off. Started, ended. Look started ended. So first I have to join these two tables. Then afterward, I'm going to join this last table, which is dog job. Underscored the deal e just copy. Paste be. You know, doing on e dart imply I d equals toe No. Either department idea equals toe be door department idea. So in my department, I d I have department I d In my implied stable. I'll throw the information off department I d. So I will join employees in department table with department I d. So just run this, you know, Join now I'm going toe join the turntable, which is own condition on the joining condition is look in my job. Unless core detail table I have the information off employee table, so I will join. Implies imply i d that job underscore Detail implies idea for a dark employ. I d he growth door. This is my seat. Ever see dark imply idea. Okay, My last part off the question is starting good for all employees which started on or after first generally to turn for there, Star. Did this see Dark started it greater than equals two 1st June ready to turn four. So I will convert this drink in a dig. In order to do that, I will use to date to underscore dead. In this day, I'll be able to make a comparison day. You okay? And see dark ended. Start indicts, lest any could do first, Generally. Good. Pull it in. So this is make complete Grady designation. See your designation E dart first name, then get the need be dark. E dork. Last name after Deke. Question first name and last and full name. It requires full name. So here, I'm going to concoct in it first and last name designation, first name, last name department and which is which I'm going to get from this department table using Alias be dart department name. So now this is my foot. Grady, run this query. I'd get done for mission. Look, I will get to those. So no. Next look at the third question. Right on a scale Kuwaiti toe display. Those implies which contain which can do a letter. Ito, the first name in the department name is either I did technology or a charge to write SQL Query to display those implants which contain a literary toe. Their first name and the department name is Ida. I did technology or a charge. The leg static from E. In a joint di start department. Okay, I will engage tables implies and department. So ask. But my question it requires, uh, the first name in department name E dark. First name do park. Where Name? We just be your department name on a brand new shuhn on a dark department. I d equals to be their department. I d. Okay, which contains a literary toe. Their first name there your dark first name night and percent e 1% asked for the question display. Those implies it contained a letter e do their first name. So if there is an imply which contains electorally, it will displayed in the department name is I I did technology or a job. And do you know your department name in I d Technology or a job? So Jim into for Jame contains lead Terry And it also belongs to this feature. So there is no imply which contains a literary, and it belongs to either i d. Our technology. There is only one person James Will belongs toe hr. And it also contains a landry in the name. So what is my last question? Right? Skill created to display all the departments, including those which do not have any imply display all departments, including those they do not have any imply. So in this case, I will engaged. Two tables. Department cable, an employee table imply stable. Di start department A. But okay, I will youth right out there. Join e be on a dark department. I d equals toe be dart department. I d. It will display all those departments. No, shipping is my department, which does not contain an imply. So it has no value. First name. You don't first game last name. So shipping is my department which does not contain any imply. 38. Rand And DenseRank Difference: Hello everyone. In this lecture, VV learn about rank and 10 throwing functions and we will see the difference between them first rank function. So when we use rang function in my SQL Quaid e e True is assigned a rank so rank from shin will assign drink toe vitro Another important point about drank if then to similar values are in a ranking column. It will assign a same rain so it also skips ranking if Davor duplicate. So in order to understand these points, let me give you an example Here let's say I have an employee table in my employ table I have two columns, name and century led shaver toe apply rank on this celery column So this is my Escalade Kuwaiti This is my ring function rank for Lord by this overkill Evil then apprentices bracket So inside the print to this bracket I have order by clause which is Monday tree close order by celery descending as ring from employees. So when I execute this Kuwaiti a full first sort this every column in descending order then it will apply rang function on this salary. So after Dad I will get this result so so each drove will be assigned at rank some first. Next look at the first rule. It pull a sign of first Frank. So what about the second row? It will assign a second ring. So the total desert duplicate the second entered Rome have same values. So asked for my definition offering. If there are two similar values in a ranking cooler, it will assign a same brain. Second rule Intergroup will have the same ring because these two values are similar. What about Defour? True. So after the duplicate values, the next route that will come will have a different range, which is rare for but the important thing here. Toby noted that it has kept one rank, which is ter drink. As I said earlier, that rang function skips ranking if days are duplicated. So here is a duplicate. The second row in the third row dither duplicate. So the next road that comes will have a different ring. But it will skip ranking convertible the next row. So it will give five. Frank. So what about this hell? This row here in this room, the previous value in this value, our shame. So it will assign a family drink. But when the next group comes, it will skip one drink, which is 60. This is the concept offering debt. It leaves hole in the ranking process. So what about digged answering? So it also shines during toe. Each Lou two similar values and Drinking column will have same rank, but the difference between rank and dense frank is rank fun. She leaves hole in the ranking process if there is a duplicate, but then strength function doesn't skip ranking. If there's a duplicate, let's look at the example here. This is my against rank function over order by celery descending as Dan strength. So this is my they look. Two similar values in the ranking column will have same brain. This is my ranking column. These are similar values, so it will have same rain. So afterwards, when a neuro start, it will give me a different rank, which is three. So you must have noticed that dense rang function does not skip ranking If there is a duplicate group, the second editor drew values are saying, but after that, when a new rule starts, it will not skip any linking. It has given me a turd drank. So these values are same, so it will give the same rank. So these Ruth. So what about the next row? So the next rank will be five after this row. So the point here that I want to communicate to in debt dancing function does not skip ranking if they duplicate Vera's rang function skips. Ranking if there's a duplicate for this is the difference between rank and dense rank function. So let's just see an example. So this is my employee table, so I'm applying rank function on the celery cola. Then I will apply dense drink frank, then over key work, then bracket ordered by celery descending order. I can write ascending order. Barkero. I'm I'm sorting this column in descending order food if rank one So like blind name, certainly. So this is my ring function? No, I'm also applying dense rang function dance, dense rank. We'll work or don't buy salad e defending dense rink. What if two. So let's just see the difference. And so let's just see their difference. So let's just see their difference. Food. This column is my RANG function column. This column is my dance training function for look, the first fail You look look different. These two values are same. So it will have same rank, board rank, and then frank values will be same. So 44 worked about this. Look, rank function there is skipping happening. As you know, that rang function skips rank. If there is a duplicate look, these values are duplicated. So here it has skipped fifth rank. So these two look, these two values the same. So the next when, when it goes to next through it will skip the seventh rank in case off frank function. So no skipping is happening in dense Frank Donald look so desta MegaFest between rank and dense string. 39. Analytical Functions: Row Number: the first an electrical function back. We're going to discuss Syria's role number. So when we used rule number in my skill, Grady e true off a table is assigned a unique value. So row number assigns a unique number each room. So with rule number, we used order by clause It is men Dadri close that for use with row number Partition My claws, It is optional, but partition by close does it basically divides my data in different sets. So without partition by cloth, my data will be treated as one unit. So in order to better understand the concept off rule number let's see an example here, Consider I haven't employed table in my employ table. I have two attributes or columns, name and salaries. So this is my espirit Kuwaiti. I'm applying rule number here grow number then followed by over G word then followed by this order by salary descending. So this order by clause if mandate without order by clause, I will get an error. So as a rule one from implied table So what? This rule number death so e true Off this table will be assigned a unique numbers. It will start from one. Then afterwards he drove Will be include minted by one. So here it starts from one. Then afterwards, when the next we'll start, it will be incriminated by 12345 So this is the concept of rule number. Let's see an example. Very youth partition by clothes. And we will see the difference if we don't use partition by close in my row number. Okay, so let's get started. So this is my employee, Deborah. Die start employed table. So I'm going to apply rule number. Here. Go number. We'll keep word for word, but would give word or did by fidelity descending Phil in fly name position. So rule number then followed by over Cheever. Then inside the apprentices bracket I have ordered by celery descending, which is mend a treatise order biased mandate trees. So when I execute this Eskil Grady first it is going toe thought the salary column in descending order. Then it will apply rule number for each row off this table. So if I run this look so every rule so every rule is assigned as unique. Number 1234567 Up till you're Dean because I have totaled 18 does. Let's have one to use partition by cloth. Here, let's I want to use partition by own this position Color So partition but police. So without partition by cloth, it will treat my column F 1 June it this whole column as one unit. So with partition by cloth, April, divide the rules in different groups. So I will have a partition off. Director, executive manager, officer A cistern manager. So I will have different partitions. So let's just run this Kuwaiti and see the difference. So look, I have a partition off. Assistant manager. Look, this is my first partition. This is my second partition. This is my third partition. This is my fourth partition rule number will be applied on every partition Separately in my aim, partition row number is applied separately like the director, executive manager and officers. So looking very new Partition start rule number is reset proven. Look when a new partition starts, so number if reset to one. So let's just see the use case off rule number. So display the top five centuries often imply, like me copy paste. This question displayed it or 5 30 is often applied. Let's remove this partition by cloth. So just quickly this so I want top five centuries often imply So here I want to filter the result off this column so toe filter the result off a lap functions re youth qualify. So qualify Rule one less than I think disk weighty will give me top 500 often employ Well, not just run this, Grady, so I will get it off. Fight centuries often imply. Look, if I include the surgery, call them here 30. So I will get a top five sellers often imply so the next question is displayed atop two salaries often imply with respect to position. So here I will use partition by gloss partition by lots of position Look left then three with respect to pollution. So first it will divide my rules in different partitions. Then of it all, I functions like rule number we will use qualify to filter the result. So I will use qualify here with this thes Collins. I will use their cloth if I want to filter the result of these columns. So let's just run this top two centuries often implied with respect to pollution, Sister Manager, director, executive manager, Officer with either my top two salaries with respect to position. So we're test make toad. Question displayed atop two salaries off. Director. So, Veer, we're fairly there. Pollution. He closed toe directed. Let me just copy is disgusting. Top two sailors off character. These are my nor analytical functions. So I was used their clothes with them toe filter Their results like their position equals director Rule number one This is my an electrical function here. I'm born toe filter the records off an electrical function proven that is I have used Qualify. Next Just run this. I will get the top two centuries of director. I can get this without you. Think partition by claws That look, If I don't, I will stay. Get Look. Okay. What is my last question? Displayed a duplicate rules offer table folk displayed a so in orderto get the result off this question either youth imply underscore new table So select static from dice Dordt imply underscore New day before this is my table. So from this table I will get the duplicate truth. So either use rule number here. Let's say you idiot e dart This will Give me all the truth. Coma rule number, then ordered by no partition by I d or Dubai I d. Let's just run this Kuwaiti. So on the basis off i d. It has partitioned the data. Look, this is my first partition. Look, first partition. This is my second partition. This is my third partition. Look. Rule number is applied on every partition. Look, this is my first partition. Rule number 123 And when a new partition starts rule number 37 to 1. This is my second partition Returned partition. So question is displayed A duplicate rules off a table. So I just want to retain this first true or first partition first, draw off second partition in the first drawer. Third partition. So here I will use qualify and Rule one third rule. Rule one. Do a test. Run this first, Roven greater than one. So this will display the duplicate Ruth. So I will get the duplicate growth or forgive. I hope you invested the concept in the youth case off rule number. Thank you. 40. Rand & DenseRank Analytical Function: Let's say I have an implied table. So in my employ table I have a salary column in which I have all the imply salary. So my business say's that I want to know the sixth highest celery often imply. Let's check it out how I can get this six tie Celery often imply this is my employee table . So this is my family Gollum in my celery column. The six tie Celery is This is my first highest 1st 2nd 3rd 4th 5th 6th 6 Tyus ality is $12 . 12,000 is my ah six. Tie your sanity. So So First I'm going to use rang function rank lead she can I get the six tire salary using this rank function a work or good bye celery descending salary This So let's check it out. I will use qualify Qualify equals 26 So my first hire Phil idiot to be tolerant 2nd 29,000 toadies $25. Using this rang function, four tire facilities 20,000 dated a duplicate value. The fourth row in the fifth through have same values. So when the next row comes so it will skip the fifth rank So fifth row has rank four and the sixth row has rank six. So it has skipped the fifth ring. So I cannot get the six tire salary using rang function because in my salary coolant there are duplicate values. So I will not ableto get it. Look, I will get the incorrect value eth rank one rank when he goes to fix. So I will get incorrect. Well, you This is giving me 13,000. But in actual the six tire salary if felt other now either use dense rank So look, dance, drink So the dense rank Next Check it out. I will get the six Tyus entity Let's see the difference Drink over ordered by solidity descending as drink one This is rank So look see the difference because off skipping because of duplicate values I cannot get the fixed higher salary using this rank function The only way I can get it if there are no duplicate values in the celery Caller One more important thing I want to tell you that if there are no duplicate values in the column, row number rain and dense drink will behave the same. Let's see an example here The leg static phone die, Start imply. So in in my like label, this imply name has no duplicate values. So I'm applying row number rank and then strike on this imply name. You will see the output off road number, rank and death rink will be same. So let's do it. That rule number over ordered by imply name descending or maybe a finger just got based it as one as very important. So let's just run, Def, Look, the output off rule number rank and this dense drink are just seen for a dro will be assigned a unique value because there are no duplicate values in the imply name. Hope you understood this concept. Thank you and have a great learning. 41. Where,Having and Qualify Clause: Hello, friend. So in this lecture we're going to see the difference between very Klaus having and qualify . So let's explore. This is my imply table di Start imply so their closets, youth tojust filter de Rosa for table. So let's I want to use their club there. Where Imply name. Employ, name, name like it's it. Give the pattern E. So this would give me the revert. They're imply name like E M percent more than zero. Too many characters on the right is zero too many characters on the left side, so greatly did so I've got the result imply name Sally Unless I want to get the salinity so just really felt that that was offered table and will give me the result. So what if if I want to use the aggregate function like some average minimum, So let's apply an aggregate function, which is some so group by imply name. No, this is an aggregate function. So let's so now if I want to further filter the result off aggregate function, this some salad e dysfunction I will use having close having notes. Is some Liddy greater than here? Toland. So I will get the result here. I cannot use their claws. This is the main difference that when you when you are going to use an aggregate function and with aggregate function, you will use having close, not their cloth. So So now what is the purpose? Off qualify. So the use qualified with analytical functions like playing dense ring rule number. So let's meet in antiquity. So let's say I want to make another Kuwaiti Really imply name. Okay, silly rule number who imply name insanity are part off this Ah table imply name. These two columns are part off. Ah, this table. So I'm applying row number on salary column. It will generate another column so ordered by it fatality descending order as a rule number . So it will give a room number. So this salary column. So I will have Let's see, very imply name like look rule vying to tree. So this rule one is not part of this imply table. So no, this is an electrical function. Room number is an analytical function. So with analytical functions, we use qualify No qualify. Rule one equals to let the Roman less than five look no one less than five. So This is the main difference between qualify and there close so I can use if I weren't Oh , Fator These columns. I can use their clothes with them like their salad e greater than 37,000. That's a E. And let's insanity lady less. Then let's 12,000 just left Implant over qualified road No one less There, clave look. So with an electrical functions, we use qualify toe filter devil with aggregate functions We youth having cloth look having gloss with simple columns just to filter the roads off Rose off a table The youth there close no filter did was offer table. Okay, this is the main difference between these hope You understood this concept. Thank you. 42. CSUM and MAVG: Hello, friends in this lecture, we're going to solve questions related to Camilla native, some moving average and moving down. So for to solve these questions, I have a cable which is die start since table. So in my table I had a few columns like Store Underscore numbers Sale underscored month, Broad Accord, projected sales and actual sale. So let's your first question. So my first question is, calculate the communicative actual sales or running sales on sales table to select star from Dice Dark Field. So let's just execute this So I have a column store Underscore number, see underscore Mont product Gored. Projected sales off these products in the actual sale off this pregnant. So first question is I have to calculate the communicative actual feel. So how I'm going to calculate the communicative actual sale, I will write some actual sale actual underscored scene. But if the column over Cheever or did by actual sale descending order, dent rules unbounded preceding, so let me first execute discredited. Then I will explain word ish rules on bonded proceeding means. So let me just add few columns in my Kuwaiti so keep order gored. Goemon store number. Okay, So let's just we'll collect me again on the actual see. So let's just executed if look, this column is communicative sale column. So what? This rule unbounded preceding means. So this means Let's just copy paste this the rules unbounded preceding So this means So this is the actual sale column. The first value will come additive formula for calculating the communicative summers. When this clause is present in my query current value plus add all previous really starting from first value. So when the first row comes, the value will come as it is. Okay, When the second row comes 15,500 it will eg the previously So when the next when the ter drew comes current value plus egg all previous rallies. So from the starting position, add all values. So Tagen doesn't plus add all these values so don't tell will become 44,500 when the four through comes correct value which is 13,500 plus add all these values so the total will become 58 event So what about this current Really, which is 12,500 plush Add all these values So my total will become same deterrent siphoned . So this is what I mean by rules on bonded proceeding. It will add all values starting from first value. So there is another the rules to proceeding. There is another clear clause. We just rose to proceeding, so this means current value plus egg up to previous two values. So let me just execute this so the first value will come Advocates Okay, The second value current value plus add all previous upto two will use so current value plus at all previous to value. So I have only one value. So I will only add van value here for the second group. So I will have 30 Want other work about this turd group Current values 13,500 ad all these two values in the current value So I will get 14 4500 What about this fourth value which is 13,000 for 13,500 plus 13,500 Add these two values in the current value. So I've elect previous to value so previous to value for destroyed dish And this I will have these two values in this current value. So what about 12,500 I Villard these two values in the current Really, which is 1205 front. So I will get Turkey 9500. So this is what I mean by rules to proceeding currently plus egg previous two values. So if there are only one value add one will drop There is no previously show The current will you like here there is no previous really show the current, will you? So what about the next rules? Between two proceeding and two folding. So what do I mean by this rules to preceding means egg toe previous values in egg to next values. So let's just use this glass. The rules to proceedings Let's just executed this Look, This is the current value is 15,000 in the current value, there is no previously. So I will only egg next to values at 15,000 in 13,000 at 528,500. So I will lead these two values in the current. Really? So I will get 4 to 4500 in this room. I will get in this value. I've led these two values in the previous only Von Really, which is 15,500. I will get 58,000 in the turd grew in this 13,500. I will add these two values and I really like these two values in the current value which is 13,500. So I will get the value to 72,500. So what about this, Ruth? Between Let's just got replaced. You rules between unbounded preceding an unbounded following. So it means at current value plus at previous all values from the first value. Okay, bless next, all values last value. So let me just explain this. Just copy Pissed and Peaster, Tear peace disclosure. So let's just executed. So this means in the current value. So there is no previous value. I will act all values all next rallies tell the last room. What about the second value? So in the second value in the 15,500 ivy lag, all values Alexis values and all previously. So I will get one lack 84,500 to do another 500 ivy leg, all next values and all previous values because the limit is on bonding. There is no limit. So road between unbounded proceeding and unborn drink falling at all. Next values and egg, all previously please. So in the first question I'm using rules on born get proceeding. So let's just remove all just executor death. Look, it would only egg all previous values. So look, let's suppose this is my current values 12,500 in this cell, you could egg all previous finals. Okay, so what's my next question calculator? Communicative actual seals or running sales per store on sale stable. So I want to calculate the cumulative scene but store. So I will use partition by Klaus, partitioned by store number and got school number. Or go by. Actual sees rules unborn Get proceeding. So let's just execute def burst or so This is one partition, which is Sten Store store number 10. This is my second partition. So look Rose and burned it proceeding. 13001st value will come as Curtis the next value. It will act previous value so it will become 27,000. But about the next partition can teeter 20 23 talent for lead the previous 11,000. It will add the previously so it will become 23,500. Work about this next partition, which is 30 partition, 12,500. It will come another case. It will lead the previously you know. So the value becomes 23,000 Worked about this 10,000 so it will lag all previously these two values in the current value. So this is my next Western. So calculate the moving average actual sales per store permanent. So I have to calculate the moving average average at your store. Actual seal partitioned by I've worked Perstorp apartment. So partition by store number and sale underscore month. So I will have to partitions. So let's just executed. Look, just use Roven proceeding Rule one proceeding It will act only one previous room Okay, in the current value seal underscored month Execute death look but month apartment and burst or sub Camilla tive Every Jawf store number 10 in the month off. Generally to turn seven is 13,500 store number 10 months first made to turn seven the average actual sale If 13,500 work about this store number 20 Montas first Fab 2007 Actual say live moving of average actual sailors. 11,000. So this is how you calculate moving average actual sale Apartment Barstow Here. I have partition store number in chill month. So in this way I will get D moving actual sale, Part mont and per store. So what's my next twister? So find out the moving count off stores selling a particular product. It's account product. Court over. Partition by store number rules. Ungrounded proceeding. So store number product Court, Come. So this is aggregated count. This is moving Kung. So that is why I have group by the collars. Because I have used aggregated counter. So I will compared aggregated counterfeit communicative are moving count. So let's just execute this query. So this the moving count. So here I have partition store number. So this is my first partition. So the moving countess for the first partition is one. Do so This is first partition. So the the aggregated Countess Von When the next true comes, it could add the previous values. So my next count becomes two D moving conch. So this is the second product in the moving Congress to look at this partition. Their store number is 30. This partition. So first moving counters one. Then toe de entry. This row comes. It will add all previous values in the current. Well, that is I hear the common history. Look, this 40 this partition first values one. When the north next row comes, it will add the previous value in D currently. So the moving count becomes toe Look. So this information is showing deck for store number. How many products to have? Look, this is my first product for store number 10. This is my second product for store number 10. Look at this partition 30 Store number 30. I have three products. 1st 2nd 3rd 1st product. A strawberry second Ivorian thirties mango. Look 41st product of guava. Second is a record. So this is how you can clear the moving count of store selling a particular product. Hope you're industrial. The concept. Thank you. 43. Case Statement: Hello. Everyone in this lecture reveal sturdy gay statement in SQL Soak. A statement in SQL is like defense statement. If a condition is met, it will return a value. Look at the example here. This is my simple table in my table course Underscore Name and credits are the Collins. So I am applying k statement on credits column. So look at here in my school. Grady case credits when one then one credit went toe them to credit, then treat entry graded. So if I interpret this case statement in the language off, if else statement it will be like s credit equals to one then Britain. One credit if credits equals two to then return to credit If credit sequence a tree, then return free credit. So when you are going toe end the case, take menu, will Jews and give erred then s credit aliens. This will be the column name. So in my in this school query, I will have to column scores underscore name and credit Alias. In this column I will get one credit because here credit equals to one. That is why it will return one credit. Look at the output one credit because the value of credits equals to one. So K statements are of two types. We have valued guests and we have searched kiss. So what is exactly the difference between valued inserts? Case in value case, you search for particular value. Look at the example your case credits when. One, then one credit. It means if credit equals to one, then return one. Credit if credit equals toe, then took credit. If credit equals 23 then three credit. You can only check with the equality, but when you're using search case, it provides too many options you can search. It proved less than equal to operator with equal to operator less than operator like operator. So in order to better understand the concept, let's see the practical example off valued in search kiss. So this is my table. Die start imply table like me. Quit this table. So in my table, I have a column position. Underscore column. So in this column, I have implies position officer, Executive director, assistant manager. These are implies position. So you think a statement? I've warned toe categorized this position, so if position equals two officer, it will be categorized Death low salary. If position equals executive, it will be categorized as close. And if position equals two manager, assistant manager, then it will be categorized death, medium sanity. If position equals two director, it will be categorized us high. Salvi so I can achieve that Using case statement using value case. So let me use the case statement in my Escalade e the gifts, then followed by name off column provision, then used. Then giver position. Vin officer, then low salary. Then position equals two executive, then low salary. Then position equals two. A cistern manager, then medium salad. E vin provisioning equals to manager, then medium sanity and high salinity. Other than these positions, all other positions will be categorized as high sanity. So when I'm going toe end a k statement I will use and giver then the name off column as salary status. So when I'm going to execute this skull query, I will have two columns. Let's say I've learned to give position, so I'm going to have to Callers position column, then the salary underscore Status column in this column. I'm going to get all the all the status low salary, medium sanity in the high salary So let me execute this A skill Kuwaiti So look officer has locality Executive has lost ality Director has high salary Look assistant manager medium salad e manager mediums led for using valued kids I have categorised implies position toe low, medium and high salary So let me give you an example off search kiss So let me just remove all this select Starik from die Start imply so I will youth this salary column for search case statement So that's an idea Off search cases If celery less than 5000 it will be categorized as low salary If salary greater than 5000 in less than 15,000 it will be categorized as medium salary Salary above 15,000 will be categorized as high salary Let me implement the scenario using search case statement gifts Then Cilipi left in 5000 Then loose ality then fidelity greater than 5000 and salad e less than 15,000 Then medium salad E is hi facility and as salary status. So this is my skill quality I have used less than greater than operators So this is an example off search gear statement So this is my complete skull quickly. Let me and Vic your disk ready. So look at the output. If salary left in 15 to 5000 it will be categorized as low Sally, look. 6000. It is less than 5000. If celery greater than 5000 and salary less than 15,000 it will be categorized. Us medium salad e celery above 15,000 will be categorized US high salinity. So this is an example Off search case statement in search case. You can use different operators, like less than greater than less than equal toe like operator. So hope you understood the concept. Thank you. 44. Coalesce Function: Hello, everyone. In this lecture we will discuss about police function in SQL. So in a school, colleagues function returns to first normal expression among its arguments. So in order to understand the meaning of district meant let me give you an example here. This is my contact in four table in my table I have the information of person named person business wound Sell food in home food. So let's say I want to find out the best way to contact each person. So if a person has business food, sell food in home food, I will contact this person through business food. If a percentage sell food in home food, I will contact him through cell phone. If a person has only home phone, I will contact him through home full using coalesce function. I can achieve that. Kohli's then the expressions businesswoman sell food and home food. So look at the first row off these three expressions. So, in my first rule, the first nominal value is this. I will get this value in my second row. The first normal value is this. So I will get this early in mature grew. The first nominal value is this. So using colas function I can achieve that lets you school is function in an SQL query. So? So this is my table. Die. Start contact info. I have the information of business. Full cell phone, home food. So I'm going to youth. Cool a sanction here, Khalife, then the expressions business. Foon, go Mom, sell food. Go My home food blended bracket but sin name. So this school, a suntan will return the first normal expression. So look at the first room. These three expression Look at these three expression. So the first non null expression is this. So in my second row, the first non null expression is this immature grow. The first normal expression is this So let me execute disk ready. So in this way you can get the first normal expression. 45. Partitioning Teradata: Hello Everyone in this lecture river learned about another important tanner that a concept it is called partitioning. So partitioning is a mechanism get its youthful in improving the performance off Eskil queries. So parquet shinning divides my data in different partitions. Look at the example here. My data has been divided in different partitions like Jan Fab Mart April Me. If I'm required to search the data off March using a still quickie instead of full disk scan, it will goto particular partition and we'll get that are tough from here in this way, it a wide for this excess tests, improving the performance as full. The skin is eliminated so interested that we have different types of partitioning. We have ranged based partitioning, case partitioning, multi level partitioning, and we have character based partitioning. First, we're going to discuss range based partitioning, so you're going to apply partitioning when you're going to create a table. Identities read with partitioning, case partitioning, multilevel partitioning or character with partitioning. Here, in this example, I have applied range based partitioning in range based partitioning. You specify range here I have specified range from first generally 2015 Telcel cleared June 2015 interval a set one month, it means in total I will have six partitions. Jan Fab Mart, April, May and June. Let's suppose if I change an interval toe two months, then in this case I will have three partitions. 10 in February, going to first Partition Mart approval going toe, second partition, May and June will going toe turn partition for depending upon the need in the urgency off debt, I can also define intervals like days ors years. Let's suppose if I define a range from first generally 2015 10. 38 generally 2015 zero Von took your Jen very Let's suppose if I define an interval here 10 days Stang De So in this case, I will have three partitions. Data from First General Little Tent generally will go into first partition 11. 28 generally will goingto second partition 21st Gen Telcordia General going toe. Third partition. In this way, I can specify intervals like ARDS minutes, days, months, years, etcetera for, like me change this range from first Gen very 2015 Tell Oh dear. June 2015. So let me, Jane Dissenter will toe one month. So in this case I will have total six partitions. So let's suppose I have two wins. M one and M Toe de Sam's are connected toe these virtual deaths. So my desk has been, though I didn't different partitions. Jane, Fab March, April, May and June. My second disc Like with Jan Feb. March April May Angel. So let's suppose if I won't get off March so I will prepare a school query. Select static from this table there. Or do it underscored. Did equals do 2015 03 Let's suppose I've owned the dirt off 25th month. So what will happen? So instead of searching their time whole disk, it will goto this particular partition, which is march. It will goto this particular partition and we'll get the data from this partition. So in this way I will get the debtor in much faster time, compared toa their cavities nor partition. So the sister benefit off using partition in my table debt it a wides full dished skin, thus improving the performance off SQL query. So the next partitioning that they're going to discuss serious case based partition in case based partitioning is defined by partitioning. By then, case underscored. And then the conditions here, I'm going to have four partitions. My first partition will be their order. Cost less than 1002nd will be ordered Cost less than 2000 3rd will be order cost left and 5004 partition will be unknown. So let me give you an example of case based partitioning. For this is an example. I have two m's. Let's suppose I have to worms. I am Front and M two. So data their order cost Leicester 1000 will come here in the first partition. They're less than order cost. Less than $2 will come in the second partition. Their order cost less than 5000 will come in the third partition. Let's suppose I want to search data. Their order cost equals to 1500 select static from this table there. Or did cost or did cost equals to 1500. So it will come directly toe this part to this partition in the second partition. So from this partition it will get the data. So instead of searching all parts off desk, it will come directly to the second partition. So in this way, it is going to get the data in much faster. So this is kissed best partitioning. So the next is multi level partitioning in multi level partitioning, you specify more than one partition. So here I have the range partitioning in the case partitioning. So if the business requirement is such that there needed debt are on urgent basis and more frequent basis. So in that case, you can apply multi level partitioning Where you going? Toe have more than one partition. So there is another partitioning which is called corrected based partitioning. So in character based partitioning their ties partition based on directors. So if you look at this example last name that star for directory will come in first partition last name that's started character be will come in second partition from a do the I'm going tohave 26 characters. So in total, I'm goingto have 27 partitions If I'm going to include this partition as well So last name that starts said character other than a to z will come in this partition In total I'm goingto have 27 partitions So you define character based partitioning by partition by case underscored end Dendy conditions hope your district the concept of partitioning in terracotta Thank you 46. View Teradata: Hello. Everyone in this lecture river learned about views in Teradata SQL and we will also see the advantages off using views. So views in a school and nothing but a saved escalate quickly revert. Normally complex are big school queries. Results are stored using views. So let's see an example in which we're going toe preparing a skill query and we're going toe store. The result off school, Grady using views. It's a leg static from die Start imply This is my first table. So I'm going to use joins. Combine this table with another table, which is card department table select. This is my department table in this table. I have this department I d through this department idea. I'm going to join this imply table, you know, join department e be on condition. Igor Department I d equals to be dark department. I d. So let me quit this imply name. He don't imply name. Theodore. Position a dark facility. Be dark. Department name for this is my SQL query. Let me execute discredit. So the result off this escort, Grady, is not saved anywhere. So you think view, I can save the result off this high school quickie. So let's create of you create view, then the name off you, let's say imply underscore view. So this is my name. Name of you? Yes. This select execute disk. Ready, Create view completed. So in order to retrieve the data from view, I will use select statement Select static from name off. You bitches imply underscore view. Look, data has been saved in this imply underscore view. So any time, if I'm required the information off this SQL Grady instead off running this skulking Quaid e with joins, I will just execute this view, which is imply underscore view, you think? Select statement. So in this Lee, I can avoid complex joins, aggregate functions and many other things. So let's say I don't want the salary column, in my view. So in order to modify the result off view, I will use replace view statement. So lets you here in this result, I don't want this salary column, so I will just write, replace view, remove the salary column and from this Grady. So let's execute this replace you completed. So let me execute this escalate. Grady, look, I have modified this view using replace you. Statement the lecture. I don't find this view anymore. So I've used drop view statement to drop this year. So drop view, then the name off, you imply underscore view is my name. So let me execute this. Drove you completed de imply underscore view Look imply underscore view doesn't exist So this has been dropped. So what are the advantages off using view? So these are the advantages so user can be given access only to views instead off base tables. So instead of giving access to these imply in department tables, I can create a view and can only give access toe this particular table imply underscore view. So this is the benefit off using view. You can save the desire a school query result using views and later can be used if it requires. So so, these are some benefits off creating of youth 47. Macros Teradata: Hello, everyone. In this lecture, we will discuss macros. Interrogate I school. So I like views. McCracken store multiple SQL statements. It can also accept perimeters and can contain DEEDLE statements like delete, alter or drop so in micros as it can contain multiple SQL statements. So if one off the Escalade statement in macro feels that all the statements are rolled back So let's create a macro in which we're going toe have multiple esten statements, Select stare IQ from Dice Dort imply this is my first SQL statement. So let me extract department to Dean 11 9 10 via department I d in to gain 11 9 Then it implying name, position, sanity. So this is my first estrus treatment. So let's prepare a second SQL statement. Select Starik from guys door department. So let me execute this dude there. Department I D. In 5 15 11 22 So this is my second escalate statement. So, using macro, I'm going to store these two statements, So let's create a macro here. Create met group, then the name off Macro, Let's support imply underscored Mac, as then the brackets inside these brackets, I'm going tohave the SQL statement So here I have to escalate statements then the semi colon at the end of this bracket. So this is my complete and school statement for creating a macro. So let's execute this statement. Create macro completed. So in orderto execute macro, I will use e x e c Cheever e x e c then the name off macro imply underscore. Mac is the name. So look, execute death to look first, First statement. This is my first statement. Result off a statement This is the result off second statement Distinct mint. So I'm going toe have the result off all statements in macro and I'm going to execute the macro. So let's the both here. If I used third statement id select stare IQ from dice Dort seals feels vera a cure sale greater than to thine tozser. So this is my third escalates treatment. I have added another SQL statement. So So here I will use replace macro to modify the results off this macro. Now let's execute this replace macro completed. So let's execute this macro using e x e c Cheever, execute this. Look now I'm goingto have three esseker statement First, this result of this Siskel statement shaking The result of this Siskel statement Turd result off dish SQL statement so I can modify the macro using replace give erred. So in order to drop the macro either use drop give erred Grow up Macro Then the name off Macro, let's exit your this. So look macro has been dropped So let's create a metro in which we're going to use para meters So first remove all this So select Starik from die Start imply this is my SQL statement I'm going to create a macro here with perimeters create macro Then the name off macro imply underscore One is the name Mac and this could imply one on Desko Tyvon Beck, It's Cilipi Indigent. So this is my para meter, which is facility veer salary greater. Then this perimeter which is colon Dennis, ality this perimeter. Let's say x x yes, bracket. So this is my complete Kuwaiti off creating macro vit perimeters. So this is my single perimeter which I have mentioned. This is indeed a perimeter. So let's execute this SQL query. Create macro completed. So in order to execute this macro, I will use e x e seek Evert, then the name most macro, which is Mac underscored Imply underscore one Dender, indeed. Your value. Perimeter value. Here, let's suppose 10,000. It means then this macro is going to be executed. It will display all the result off implies Were salary greater than 10,000. So this is how you execute Metro. Look, macro has been executed. In the result, all values have been displayed. There shall re greater than 10,000. Let's support If I want to specify salary greater than 5000 it will display all values off this implying table Very celery greater than 5000. So let's execute this look. It has displayed all the result were celery greater than 5000? So this is an example off creating my Crovitz single perimeter. I can specify multiple parameters here. So let's specify another perimeter vibe, which is also an integer perimeter coma vie. Indeed. Er and department I d equals toe call in then the name of perimeter vie. So this is my SQL query. At the end of this query, I will have to specify semi Colon. Then at the end of this bracket, I will have to mention semi colon. So since Meg underscored implying the score. One macro is already created and we're doing some changes like we are adding a perimeter in my macrophages already created, So we will use replace giver for any changes. So let's execute this. Create macro completed changes have been made. So in order to execute debt, I will have to mention the values perimeter values to so first parameter value is 5000. It means display all the records off imply table, where shall Regretted and 5000 and department I d equals to vie. Now I have to mention the value off by Let's suppose the value of why I'm mentioning here is three. So this becomes there shall regretted in 5000 in department I d goes to three. So let's exit through this. There are three rules were department idea is equal to three and celery greater than 5000. So this is the concept off macro in macro. You can specify multiple Haskell statements. You can also give perimeters. You can also use d deal statements like delete all current drop. Hope your industry this concept. Thank you 48. Fasload Utility Teradata: Hello. Everyone in this lecture river learned about views in Teradata SQL and we will also see the advantages off using views. So views in a stool and nothing but a saved escalate quickly revert. Normally complex are big school queries. Results are stored using views. So let's see an example in which we're going toe preparing a skill query and we're going toe stored. The result off school, Grady using views select static from die start imply this is my first table. So I'm going to use joints toe. Combine this table with another table, which is called department table select. So this is my department table in this table. I have this department I d through this department idea. I'm going to join this imply table, you know, join department. He be on condition Igor department I d equals to be dark department. I d. So let me quit this imply name. He don't imply name. E dark position, A dark facility. Be dark Department name for this is my SQL query. Let me executor discreetly. So the result off this escort, Grady, is not saved anywhere. So you think view, I can save the result off this Siskel quickie. So let's create of you create view then the name off you, Let's say employ underscore view. So this is my name. Name of you? Yes. This select execute disk Ready, Create view completed. So in order to retrieve the data from view, I will use select statement Select static from name off. You bitches imply underscore view. Look, data has been saved in this imply underscore view. So any time, if I'm required the information off this SQL Grady instead off running dish skulking Quaid e with joints. I will just execute this view, which is imply underscore view, you think Select statement. So in this Lee, I can avoid complex joins, aggregate functions and many other things. So let's say I don't want the salary column in my view. So in order to modify the result off view, I will use replace view statement. So lets you here in this result, I don't want this salary column, so I will just write, replace view, remove the salary column and from this Grady. So let's execute this replace you completed. So let me execute this escalate e look. Ah half modified this view using replace you statement the lecture. I don't find this view anymore. So I've used drop view statement to drop this year. So drop view, then the name off, you imply underscore view is my name. So let me execute this. Drove you completed de imply underscore view, local imply and a Scorpio doesn't exist So this has been dropped. So what are the advantages off using view? So these are the advantages. So user can be given access only to views instead off base tables. So instead of giving access to these imply in department tables, I can create a view and can only give access toe this particular table imply underscore view. So this is the benefit off using view. You can save the desire a school query result using views and later can be used if it requires. So so, these are some benefits off creating of youth 49. FASTEXPORT Utility: Hello, everyone. In this lecture, we will learn about another Teradata utility, which is called fast Export. So fast export utilities used to export data from Teradata tables into faithfuls. It can also generate the data in different Former's. So how this fast export utility is different from but big export, so fast export exports the data in different blocks. It works in Apparel V to Lord that they attend different blocks. It is also useful for loading a large volume of data. But when it comes to Buttigieg utility butting export utility, it processes wonder at a time which is very much time consuming and not advisable. Vented, it comes to dealing large volume of data. So let's see an example of fast export. So this is my fast exports script, which is saved with dark affects extension. So let me open the script. So the first section off my script contained the log table D D Dark employ under school log . Teradata will automatically create this log table. Okay, then the database I P and user name and password database name, then began export session toe. Two sessions will work in a parallel with export data from Teradata tables dot export out file. This is the part where I'm going. Toe export data. This is the file where I'm going to export TD dart Imply vendetta in this side at the end off fast export script, I will write dart and export. Then log off. See if this file in order to run the fast export script, I will first open command prompt. I will go in my e Director Revere descriptors Present CD a copy de ir. In order to run the fast export script, I will write f e x b space left then then the name of fast exports script Fast export dark affects breast center Look data dark text file is created for Let me open this file. Look, data has been exported from this table. This is my table de de dart. Imply von. Let me show you this table. This is their table from which the data has been exported 50. BTEQ Utility Teradata: Hello, everyone in this lecture with Dylan about another Terra Gator utility with this call Buttigieg utility, but extends for basic Teradata Kuwaiti and pronounced as batik So interrogator. But take utility can be used to run different day deal statements. Demon statement also can be used to create macros and stored procedures so that particular ability I can also import export data. So let's see an example in which we are going to use Buttigieg utility and we will act different. D deal India misstatements in it. So this is my part there. My batik script is present. So batik script asi with dot batik extension. So let me open the script. So in the first section off my script, Ivan mentioned a database I p user name and password. Then I will mention De Deal or Deimel statements. Here I'm creating a table create table TD Door Temple so create Able is categorized as video statement. So here I'm using to deal statements. This is first table creation. This ist second table creation. He did or timeto. After that, this insert command insert into gigi Door tempt one select Eric from Guy Start imply so it will insert all values off Die Start, imply Table in tow This tempering table this ill insert all values off department table in tow Attempt to table So this is my table dice Dark implied table This is my first table. It will insert all values off this table into TEM front table. This is department Table, so it will insert all values of department table in tow This stable temp toe. After that at the end off despotic script, I will write dark log off. I will save this file with dark but big extension. So let me just cancel it. So in order to run the batik stripped Ivan open command prompt cmd Then I will go into the part where my script is present. Since my script is present in e directly, I will going toe you directory then the part. I will copy the part from here Just pieced apart here the i R. So this is my big strip the deal dark but which I'm going to run. So in order to run the batik script, I will write but big b d eat you speech less than then the name off script which is D deal doored Batik breast center. So my script has been successfully completed. Insert completed. Normal. They're dead. Total elapsed time was one second. So let me just quickly dis table open this file p d dark them run so dist Able contains all values off imply table. It selects Terek from De de Dark Templin. So it has all values off imply table. So let me check the second table, which is time to table. This is time to table just greatly disturbed. So I've got all values off department table So in my book geek script Ah, have used different d deal in Deimel statements. So if I'm using lots of D deal and demons statements, I will make a batik script. And later if I'm required to use the same script again, I will just run the script. So using batiks trip, I can export that. Important it up. I can export data from Teradata table into files. Also, I can import data into terra gator tables from flat file. So let's see an example in which we're going to export that I using but pig script. So So this is my part their export batik skip this present So let me open escape. So in the first part of my script, I will mention the database I p then the user name and password. So select static from td dot imply von for from this table, I will export data in files are text file. So this is the part They're filed. Our text file will be created and they're tougher. Imply Vontobel will be exported in this text. Tried so I nd ia she data means that I will be exported in the fall for Mitt. So let me save the script in orderto run escaped. I vill open command prompt. I will going toe the directory where my script is present. Just copy based the part here direct treats to D de ir. In order to run the script, I will write batik space less than then the name off Script export Dark but gig So press enter. Look, file has been created, So data has been exported in the fourth form it So in order to make this data more presentable in order to export data in report form it. I will write a report then the same script again. Inka, look that I have been presented in report Form it. So control the even the same script again. Inter. Look, now it has exported the data in the form. Form it. So this was my export skip. So let's see the import script. So this is my important Buttigieg script. Let me open the script. So this is the file from which I'm going toe import data. So this is the file filed or text file. I'm going toe import that in this table implied to table TD dart imply to table. So let me just quickly this table select static from so it has no rules. So using then the attributes off table, This insert command will insert values off this file so these values will be important in this table. So let me just save this script. So I want to run this kept de ir, but big less than import guard fatigue. Press enter? No, but the script has been completed. Let me take the employees to table. Look, data has been imported in the imply to table. So in this way you can import that using batik script. So what is exactly the difference between loading data using Buttigieg script and loading that they're using fast load script. So when you're loading data using batik script, it processes one row at a time. So when it comes to fast load, it works in a parallel V to lure the debtor in different blocks. So therefore, fast lower is super fast in loading their data. 51. Multiload Utility: Hello, everyone. In this lecture, we will learn about another Teradata utility, which is called multi Load, so multi Lord can load multiple tables at the time. It can load up to five. Empty are populated tables at a time, so the difference between Multi Lord and Fast Lord is in fast lords. You can lure data only in one empty table, very edge. In multi lord, you kill or data upto five empty or populated tables at a time so it can also perform different types of stars such as insert, delete and update. And the important point is it can perform up to 20 Deimel operations in a script. Demel oppressions are like insert update delete. Dis commands are Deimel operations. So in order to understand how multi load script works, let's see an example in which we're going to lure data in two tables. One table will be empty and workable will have some there turn it. So let's see an example. So this is the directory in which my multi lord script is present, so multi lord script to save it Dark ml extension. So this is my text right, which I'm going to Lourdes in my table. So first I'm going toe open the multi load script. So this is my script. Multicolored script. In the first section of the script, I'm going to mention the lock table here. Lock table, STD Dark imply in their school log. So they're at the travel Maintain Lords in this table. This table will be automatically created by Teradata for the thinking section in the second section. I'm going to mention the database I p user name and password. This DBC is user name. This DBC is password in the third section. I'm going to mention the able in which I'm going to load data here e d Dark imply Ivan is my table in which I'm going to lure this data This data. So I will start with dark begin M Lord tables in the name of table in which I'm going to lure data. Then inside this section, I will mention dark Leo imply Ivan. So let me show you. Does this imply one? If the text trying and this is the layout off this data imply i d this is my imply. I d imply name. This is imply name Department. I did this. This is position and this is salad E. So this is the layout off Destructor. This is the Leo. So the next part off my script days there, I'm going toe mention the Deimel script. So here I'll start with Door Demon label, then the name off label here. E m p l A B. Even this is the name off label inside the label. I'm going to mention the script. So here, insert into de de dark employment, then the attributes off this table imply underscore idea implying that school name department I D. Pollution salary. Then the values imply Underscored i d this imply name Manager Ancelloti. So after that, I'm going to mention the part in which my text file is present. So So this is the part where my employ wonder text violence present, which I'm going to Lourdes in my database. So it is comma separated data, as I have mentioned, comb my hair. Look, it is comma separated layout imply of one. After that, I'm going toe lose this loud by writing Leo, imply von apply this label this label, then the name off label dot and I m blowed. So this part off my script is going to lure data in my t d dark imply von table. So let me show you the table. Select stare IQ de de dark imply one. The second table is imply toe, which is empty in my first neighbor. I have some data in my second table. I have no data. So this part off my script is going to lure data in my first table, which is this in the second part table t d dot imply toe. In the second part of my script, this part is going to lure data in my imply, to table this table, which is empty. So this part is going to lure data in imply to table 30 d dot imply do then the attributes , then the insert command that is going to Lourdes that are in my employ to then the part in which my text file is present, which I'm going to Lourdes data in my data base. It is common separated than the closing part off this. Ah, skipped. Okay, Anglo Lord. So at the end off multi load script, you will mention log off. So this is my complete script. Visual or data in two tables. The first table is imply Vontobel, which has some values in it. The second table is empty, which is implied to table. So in order to run the multi load script, I will open command Prompt search Your cmd have an open command prompt. I will go into my directory in which my script is present here. My script is present ing e directory E colon CD. Just copy paste apart. Here a copy. Biest de ir. So this is the script which I'm going to run it. So in order to run the multi load scrape, you will write M Lord, then space less than sign then space then the name of script imply Dark em in press enter. So look, my script is running someone multicolored script has been successfully completed, so let me check if the data is inserted in their table or not. So this was my table imply to which was empty. So let me just quickly this table Look, data has been populated, so look at the first table. Imply von Look. Data has been populated, so I have inserted these rows. Look, these Ruth 5 57 don early one Manager diesels hope you industrial the use off. Marty Lord, thank you