SQL for Newcomers | Sameh Sharaf | Skillshare

SQL for Newcomers

Sameh Sharaf, Data player

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
120 Lessons (10h 46m)
    • 1. Introduction

      1:09
    • 2. Programs used in this course

      1:06
    • 3. Class Materials - Download PostgreSQL

      2:07
    • 4. Class Materials - Install PostgreSQL

      2:34
    • 5. Class Materials - Download SQLEctron

      1:31
    • 6. Class Materials - Install SQLEctron

      0:34
    • 7. How to connect to PostgreSQL - Credentials

      1:23
    • 8. How to connect to PostgreSQL using SQLEctron

      1:33
    • 9. So.. What is a database??

      1:28
    • 10. Let's import some data

      2:21
    • 11. How to browse data with SELECT

      6:00
    • 12. Data types in SQL

      2:01
    • 13. Finally some fun stuff: Let's find useful data

      0:58
    • 14. SELECT and filter data with WHERE keyword

      8:33
    • 15. Filter data with a range using BETWEEN

      2:47
    • 16. Filter data based on a set of values using IN

      4:35
    • 17. Filter data by date

      5:39
    • 18. More data to be filtered by date

      3:24
    • 19. Yet another query to filter by date

      1:03
    • 20. Filter by strings (names, words, ...)

      4:46
    • 21. More ways to filter by string

      5:19
    • 22. More queries filtering by string

      9:41
    • 23. Filter by state

      1:32
    • 24. Case-insensitive filtering

      3:54
    • 25. Now with more complex queries: Filter with more than one condition

      10:03
    • 26. Filtering using OR

      12:15
    • 27. Filter data with NOT

      9:24
    • 28. AND, OR & NOT Operators: Which is considered first?

      6:26
    • 29. Basic math operations in SQL

      5:30
    • 30. Modulo operation in SQL

      4:25
    • 31. It's payday! Calculate employees' net salaries

      6:21
    • 32. Filter data with math equations

      2:43
    • 33. String concatenation with '||' operator

      5:57
    • 34. Concat strings with numbers

      1:50
    • 35. Give your columns a cool name

      4:10
    • 36. A report for Payroll

      7:05
    • 37. Order records by column

      6:21
    • 38. Order records by more than one column

      12:07
    • 39. Alias names cannot be used for filtering

      1:57
    • 40. Limit records retrieved from your query

      7:56
    • 41. Select unique column values using DISTINCT

      2:42
    • 42. Using DISTINCT with more than one column

      5:44
    • 43. What is NULL?

      5:01
    • 44. More complex queries with CASE statements

      10:03
    • 45. Your knowledge under a test: Calculate employees net salaries

      9:16
    • 46. Let me introduce you to functions

      4:10
    • 47. SUM function in SQL

      4:12
    • 48. AVG function in SQL

      4:09
    • 49. COUNT function in SQL

      6:32
    • 50. MIN & MAX functions in SQL

      3:48
    • 51. Count unique values in a column

      3:45
    • 52. Use functions with dimensions

      5:53
    • 53. Group by more than one column

      4:52
    • 54. Now for some tough workout

      8:53
    • 55. Filter by aggregate functions with HAVING keyword

      8:13
    • 56. Character Functions - UPPER, LOWER & INITCAP

      7:49
    • 57. Character Functions - CONCAT & LENGTH

      10:24
    • 58. Character Functions - LPAD & RPAD

      13:29
    • 59. Character Functions - TRIM

      3:35
    • 60. Character Functions - REPLACE

      9:18
    • 61. Number Functions - MOD & ROUND

      11:13
    • 62. Number Functions - TRUNC

      6:56
    • 63. Auto Data Conversion in SQL

      10:34
    • 64. Cast Date to Character

      10:15
    • 65. Cast Number to Character

      9:22
    • 66. Cast Timestamp to Character

      6:38
    • 67. Cast Character to Number

      7:34
    • 68. Cast Character to Date & Timestamp

      8:47
    • 69. INSERT a New Record to a Table

      8:44
    • 70. Insert Without Specifying Column Names

      9:10
    • 71. UPDATE a Record's Field Values

      7:12
    • 72. CAUTION! How an UPDATE Query Can Go Wrong

      4:08
    • 73. Update a Record Using Single-Row Functions

      4:15
    • 74. DELETE a Table's Record

      6:08
    • 75. A Good Exercise: Extract Employee's Email Address

      1:50
    • 76. Solution: Extract Employee's Email Address

      12:53
    • 77. SQL Data Types Revised

      12:34
    • 78. Bookstore Data Set: Let's Move It to Database

      3:51
    • 79. Create Your First Table!

      8:19
    • 80. Insert Data to Your New Table

      7:31
    • 81. Change Column Data Type

      7:11
    • 82. Rename Column

      3:33
    • 83. Add a New Column to Table

      4:11
    • 84. Constraints in SQL

      1:04
    • 85. Primary Keys

      14:04
    • 86. NOT NULL Constraint

      4:33
    • 87. UNIQUE Constraint

      4:21
    • 88. DEFAULT Constraint

      4:10
    • 89. Foreign Keys

      7:31
    • 90. Create Foreign Keys

      6:19
    • 91. Foreign Key Properties

      6:15
    • 92. DROP Column

      0:40
    • 93. DROP Table

      1:01
    • 94. Create a Table by Select Query

      4:35
    • 95. A Trick to Create Empty Table Using Select Query

      2:45
    • 96. Introduction to Joins

      2:40
    • 97. Entity Relationships Diagram (ERD)

      9:13
    • 98. Cartesian Join

      9:27
    • 99. Use Aliases when Joining

      6:43
    • 100. My Problem with Cartesian Join

      7:42
    • 101. Inner Join

      5:56
    • 102. Join Types in SQL

      2:59
    • 103. Outer Join

      8:25
    • 104. Nested Queries

      6:14
    • 105. Insert by Select Query

      5:38
    • 106. Insert with Nested Queries

      5:59
    • 107. Combination Relationships - UNION

      3:27
    • 108. Combination Relationships - UNION ALL

      1:44
    • 109. Note about Combination Relationships

      1:51
    • 110. Combination Relationships - INTERSECT

      0:58
    • 111. Combination Relationships - EXCEPT

      1:25
    • 112. Access Control in SQL

      1:21
    • 113. Create User

      3:01
    • 114. Grant Select to User

      2:30
    • 115. Grant Insert & Update to User

      3:04
    • 116. Revoke Privilege from User

      1:51
    • 117. User Groups

      4:29
    • 118. Views

      3:29
    • 119. Schemas

      1:32
    • 120. Create Schema

      4:11
13 students are watching this class

About This Class

Hi!

I'm Sameh Sharaf. I am a data engineer. I have worked on data for about 8 years now so I guess I can say I'm experienced!

This course will teach you how to use SQL (Structured Query Language), a language so prominent for relational databases, databases such as MySQL, PostgreSQL, SQLlite and more.

SQL now is a great skill to have if you want to work as a data analyst, data scientist, data engineer, database administrator or even a developer or software engineer, since all such titles need to use databases and data in a daily basis.

I'm a beginner, is this course for me?

Sure! This course was done for beginners. Even students with experience in SQL may find it useful too to memorize some keywords and functions, as well as learning more about the bits they may miss.

Is there any prerequisites to attend this course?

The only thing you need is a PC or laptop with average performance with any operating system installed on it. We'll be using Windows 10 for this course but the OS does not matter since the tech we'll be using is cross-platform and can work on Linux and Mac.

Is this class complete yet?

This course is still in progress and I'm keep working on it. I'll update it eventually.

Transcripts

1. Introduction: hi and welcome to my course SQL. For newcomers, I'm Santa. Shut off, amended engineer and having working on data and databases for about eight years. In this course, you will learn how to use Eskil, whether you are students studying databases or an employee who needs to work with databases and crunch some data and once s scale to be one of your key skills. This course is differently for you. Ehskyoo is a standard language which is essential to work with all population, all databases, whether it's open, source or commercial. So if you learn it, you're good to go and use any of those products shown here. This course will use Oscar scale. Why? Because it's open source. So it's free. It proved to have the best quality performance comparing to the other open source. Relational databases available at has good documentation with comprehensive explanation for each s scale, key work and function. So if you need to learn more by yourself after this course, it will be quite useful. Okay, so without further ado, let's start and hope you enjoy the journey 2. Programs used in this course: Okay, let's start by downloading and installing the needed applications to begin this course. What we're gonna do is we're gonna install a relational database manager or an RDM s like Boss Cresskill by using Post Chris Que el. It's because open source of it's free of charge. It proved the performance and quality off path free and commercial are the M S systems that we have today, and it has a better documentation in case if you need any help, you can simply look up into the Internet and our documentation on their website next. But I'm going to use as a sequel editor in order to connect and Ron and execute our sequel scripts. What are we going to use in this course? Is sequel electron. It proved to be good. It's free. It's open source, it's light on. It has many futures that we cannot use in this course. So let's start together and deludes and install those two applications 3. Class Materials - Download PostgreSQL: let's start by the ruling post Crisco. So from Google, I'm gonna type us crystal. And here you go. I'm gonna check that ludes. And from here you can see that Proscar School supports multiple operating systems, including Lennox, Mac and Windows. Since I'm using windows, I'm gonna check windows and from the space you can't see what Proscar school versions available. But the time of this video, it waas 10. And as you can see for version 10 it supports the following 64 bit and 32 bit Windows platforms. In case you don't know which windows version you're using, you can go to this stop and from my commuter or this PC, I come, you can choose properties from here. You can see what system type do you have so you can choose which version off windows you need to install and our food. So from here I have succeeded off operating system, So I'm gonna choose this the next time I'm going down with that, so going to delude installer at it will regard me to a new page which is DDB Post quests. From here we have enterprise db dot com, which from here we can delude or stressed girl. So I'm gonna choose my version, which we're gonna use 10.1 discourse and my operating system, which is Windows 64. And it's I would now will take time and here you go. 4. Class Materials - Install PostgreSQL: okay? I don't deserve you. Now on, let's stop Click. Uh, but next out and started my default factory here. Next. So you have the packages that will be installed for prosperous school. I'm gonna keep old. And next here's actually where your data will be stored. So in case like a few items stole Oscar school Oh, you'll still find your data here to be restored later on. I'll keep the same people. And here's gonna ask us for the super user or the root user for prosperous. So since we're not doing other tests on our local machines, we can choose any password then we want. But later on, you should consider a very powerful password for yourself when you need to launch any database. The future zoomed password recited again. And then next here's the ports at this beautiful port, which is 5432 Remember this because we needed later, I'll give the people one and press next. Here's the local a piece. You you're using any language other than English. You can choose it here as a different language for your databases will keep on the people, togo and next race. And here we go. It's going to take some time to be patients on a me speed up the video and my insulation is gone. So before that, I'm gonna unchecked that stack builder option from here, since we don't need it to open it for now. And I'm going to finish then solution. So here you go. We're all done with insulting their database or screw school. Next, we need a sequel editor, which we need to connect to our database and run our sequel scripts. 5. Class Materials - Download SQLEctron: Now let's start with installing the sickle editor that we're gonna use to connect to Oscar school and run our skirts. So from here, I'm gonna tie sequel Electron. This is the editor that we can use from the first thing here. We will choose electron, and we can tell them that could be since we get using all windows instead of the terminal. So sick electron is an open source and free tool to use. And it's available for early knocks. Mac and Windows. I'm gonna double dickweed. And here it will show me the latest version release of this video. You have 1.2 h You can sell unless version based on the time that you are now looting. Installing cyclic trump. From here, you can see multiple lines for the wounds based on your operating system or windows. You need to choose day X e file. Where might you need to use that TMG And for leaders, for example. They can I use your baby in the TV and for home so that the end she There's also the come breast files as well at the source code. We're going for you CSI here, Teoh out selected and 6. Class Materials - Install SQLEctron: and it's Tunstall's here on. We're gonna see the local nice. All right, so we install Sick Electron for now. Let's see, How can we connect to get at the base? 7. How to connect to PostgreSQL - Credentials: now in order to connect to post preschool, meet the following information. First, we need the user which we're gonna use the root user or the Super User, which is called prosperous A user which you define password When we installed Boss Crystal Next you need the database that you need to connect. Proscar School allows you to who? We ate multiple databases based on your means. So the default database, which will be using is called also Boskalis. Sounds easy for now. Next the host The host is the server which posture school is insult into. Since we insult what's crystal on our local machine, the server will be local host all the I P 127 point old 127.0.0 point one since local horses easier for us to use so we can use locals. Next the ports which would be fine with me installed possible. If you kept the default pour, it will be 5432 If you change the port, you can use that fastball. Let's see, How can we put that in sick Electra 8. How to connect to PostgreSQL using SQLEctron: back to sick electron. Let's add and connected Proscar school from the add button here, I'm gonna click it and fill the information for the name of connection. You can name it sickle course or any name that like from that they're based high. We're gonna use posture school and from the server address, which is the horse we get a call it local host the port. You will be the full 15432 In case you change it, you can put your own toward the user. We're gonna use that super user which is prosperous. And the password you added When you install posture school finally the initial database or key space, which is the default database that Bosco school creates for us, which is prosperous. To test our information, we can click, test and see if it works. And here you go. Connection is death and successfully connected. Press save. And here we have our connection. Ready for us could connects. And you are all good. If you see this, then congrats. You're now connected to post Chris school. Your machine is already and let's start discourse 9. So.. What is a database??: what is the database? That database is a collection of information, said an organized, so it can be easily accessed, updated and managed. Original database or a tabular data base is set of data organized into tables. You can think of tables as spreadsheets, but it's far more complex in design and can hold millions off rose in order to access an update, data and racial databases. We use the language called Sequel or structured query language, which we will learn in this course using sequel language we can access, our data can updated, we can get the queries and all the data that we need from the database databases can be used for various specialised catalogues. Such a sales and Venturi's employee profiles, students, grades and much more. Any day that you can store, you can use databases. Racial databases are usually managed by a manager system or an engine, which controls that of its users access and what can and cannot be accessed by users and says, well, as mentioned data right into this. Hence you hear the term national database management system or our GMs 10. Let's import some data: Okay, So after we prepared our database and mean still lost Chris Gayle on our machine So we need some data to experience on and learn and discourse have generated some random data to use it in the scores you can deluded from the attachment. In this video, you can't find a sequel file, which can there. And after that, we're gonna show you how to import the data into your database. Let's start by opening Sick Electron connect your debt. Abi's all right. So from here, we can go to file and up into a from here. I'm gonna open the file that you don't. So here you go year the scripts that will be going to use to build our databases our tables on and the data that we gonna use together Don't get scared if you don't understand. What is that? We will their old this eventually. So what you gonna do now? Is it conceivably we can't execute. And here you go when it say's quays gives you did successfully for old does it means that every query or every script that is written here has been done successfully. Ansip's everything is working and all is good, but it can notice is. But he go from here and you click on the right click in prosperous and versus database you're gonna she see here and you fold or their three cold public. This is the default schema for prosperous. When you open this directory or schema, you can see two tables, departments and employees. When you look on those, that means you have successfully created the data that we're gonna use in this course for All right, so let's move to the next section. 11. How to browse data with SELECT: Okay, so now we're going to start learning sequel. So first we're gonna do here is I'm gonna close this town to begin to start fresh, and I'm gonna maximize the window so we can see more. Now, we're gonna start together with Learning sequel by learning the first statement that needs to know. First statement is cold. Select. What select does is it gets the data that we need from any table that we want or any query that we require. So sell it is the main statements in sequel we're gonna do here is I'm gonna show you how to use select statement to get the data that we eat from any table. Let's say employees first from the editor. I'm getting tired. Select this where prosperous can know that. Get me the data that needs after select. We can assure here what columns that need from the state. Since we have no idea about how easy it what does it store? I'm going to let that get me older. Collins. In this day we use star, which is a shift in eight select star. It means that it me, all the columns from a particular table or view or any resources that we are reading from. So we're gonna define what stable that we're gonna reach for. So we say after that from and the name of the table. Now we have two tables, departments and employees, and it gets employees will be more fun to work with for now. So we're gonna use employees goods, please. And he that's you. I'm telling Bosco School to get me all the data from employees with all columns industry. Let's see the results by clicking, execute and you. But you see, here is all the data in employees Please take note that this data is randomly generated. So it's not real data I stayed out. We're gonna use in the score, so we have the freedom to do whatever we want. So as you can see from the employees table, what we have here are multiple cons from the eighties First names, last names, at versus state zip codes and tones and other data that we can be used believe so Tebow consist off columns like I d First thing and last thing. It consists off records or rolls each row because a record, because it holds data for an entity. An entity can be anything like an employee, a customer, a product, a vehicle, our anything that we can define to be an entity on an object to be stored in the day. Davies. So for each column and each shroh we haven't value like here we have first name Ili Or do you wanna, which are values? Those values are what we need most when we save or when we query from our databases so we can see that we could select from the table that we want, which is employees and see the data. All right, so since the team was a bit big for us, if we can filtered or let's say for now we need to select Onley assuming columns like OK, but I need Here is the first name last name and home off the employees. So instead of selecting star here rename it comes that you need to show and support each column name with a comma. So what I do here is I say, first name, last name. And so here s a little three columns. First name, last thing and for when I execute this query, But you can see Here is the three columns that I defined here in the square. First name last name in for this is cleaner to see in case if you want to see only the names off those employees and their phone numbers gets, if I want to call them or contact them or whatever, please make no. That sequel in general this time is that the road here is case insensitive. If he's like whatever character that I topping, if its capital or small, it will do the same. So no sweat about the goods here and types Olympic that on it was from that. This and I write it will give me the exact same results, no ears at all. So here you have it. For now, we can see the data that he needs or can actually see the data that we want to work on. Have fun with that and see what they don't know we have. So we get a move on and talk more about how can we filter our data more and see only the particle earlier that we need to see 12. Data types in SQL: okay before going into Filter and our data. Using the select statements, we got to know something called data types in databases. We have three main reader times. We have numbers. They spend strings numbers such like salaries, commission rates or any number of value that we use our cold numbers and they are stored as numbers in the data. Please. We have dates such as highly, which is the calendar that we use and rehab streets. We called the words and sentences any characters after big characters strings. So they are string off counters. Strings can be first and last. Names can be the emails and job titles and also phones bones. They're not considered numbers that way. Air considered strings or characters because we tied them in a way that they have simple pattern or format, accusing dashes or spaces between the numbers so they can be recognizable and easy to read . So when we want to filter later in our database, we need to make sure and take care of the data types that we cannot filter that sometimes they're misconception between numbers, dates and strings that they're not being used. Teoh when people literally that the databases. They use a very on optimized or in easy raise ways to filter there the later and they have some problems, how it's filled in later and get the data that they need just because they didn't think good. Look about the data that they have in the table, that they're kind of filter. So, yeah, the data times that we have and we're gonna take a look and how to filter based on those statements, hopes. 13. Finally some fun stuff: Let's find useful data: okay. Your bus wants some data. Let's say you boss comes to you and asks you to get the data that he needs or she needs from the database. So, for example, they may ask, warned employees with the base and already more than or equal to $5000 maybe want them to fire them or to for tax purposes or whether thanks. They want to know the employees with base salary between 1000 and $2000. So between the range that they need old employees between one and 2000. Next and last, they need employees with the celery brackets off $1600.700 or 4200. So we're gonna see together. How can we get the reader for your boss? Let's go. 14. SELECT and filter data with WHERE keyword: Let's start with getting the employees with base salary more than $5000. Starting from the last step that we did here, we're gonna see war employees who have such big separate. What we gonna do here is after typing select the name of becomes that you know, we need to show and from which table that we need to read from, which is April. Years we're gonna do next is we need to filter the data means that we need all the base that employees with base salaries or to £5 in order to do that, we're going to see here is we cannot type where after selecting from a table, we need to Children stayed up. So we time where so we can't define what are the filters or conditions that he needs. So we canceled this state after we're we can't set sell its debt. Okay, which column that we need to feels at all. If you see that data here, we're going to see that the convent is holding the salary off those employees. It's called base underscore salary. So from here, after where and going to type base, underscore Saturday and take note that you should be careful about the column names. Sure its case insensitive, but doesn't mean you have to tie tied a wrong name for the columns. So based on this course salary, we need the number. That is more than 5000 here. Since we're storing numbers were going to say that we need to filter the records so we can only see the records with values over 5000. So I'm going here, and I'm getting use. The opiates were great through that. So it tells that. Okay, give me all the dates out that the base salary is greater than and the value will be fine. So this query will get us first name, last name and phone from employees where the base salary is greater down 5000. Let's see what it gets. How is getting from here? And we So here you go. We are employees with Saturday's $5000 and you should be jealous folk there. Okay, cool. Zero. Here you go. We could answer the first question, but if you can't remember, we say that we need all the employees for Greater Dan or it called to $5000. How can we do that like Okay, they have a sign, which is great. We have a sign here in in sequel, which is called greater now than or equal to. How can you do that? After what type? Greater Dan we can use. The sign equals. So here sequel can understand that. Okay, give me all the data, All the records with base salary, Greater Dan, or equal to $5000. So if you try it now, you can see we have a different number of froze than before. The work was 77. Now we have 84. Sure. Here we can see the base Saturday, which is okay. Not not very convenient into let's see the base salary so we can add it from here and say base Saturday. Terry can see the base salaries off those employees, which is even better for us to make sure that we're getting rightly that we need. So you gonna run executes? And here we can see the base salaries and sure, you know, is greater down or equal to thousands. As you can see here happened equally. Gala, She's $5000. But if you choose, they said great down we have equal. You can see gala anymore. And if you look here, sure, you have, like, 77 record, which we can now check in this video. But you can find that you can see 5000 value anymore here on if you even look more, you can see all from 5000 or 5100 also like, let's say maybe 9000 or yeah, I have here any $500. So here we answered the first bush to recap We use where to flip through the data that he needs. We use the cone that we filter upon, and we use the sign, which is indicator that OK, it's greater or smaller performed and see if we choose Lester or smaller. What we gonna get? And sure, naturally, we're gonna get older employees with salaries less done, time goes views less than or equal to 5000. We're getting name pIease war. Let's down or equal to £5 And let's DeKlerk against fine gala, you go find another one for France whose salary is $5000. All right, So as you can see, you can get the data that we needs using where close Well, so Okay, what if you want all the employees where they're big salary doesn't equal to $5000. Well, you have a son for that, which is a bit weird because I gave would be used, usually in methods is that we used the equal sign with this drop on it. But here, we're gonna use in sequel is signs this down and greater. In this case, we say that if me the employees where the base salary doesn't equal to 5000 if it on this, it will show me employees with salaries not equal to a constant. And if you look here, you won't find Gala or France to make sure. Here we have 386 rolls. If I am this filter and select all the employees who get me all the roles was in, including the employees with serial 5000. So as you can see, it's three months. Three, which means, like you have knowing employees salaries. That is exactly $4000. All right, so let's move to the next requests from your boss 15. Filter data with a range using BETWEEN: moving to the next request where the employees with base salary between 1000 and $2000. As you can see, we could answer the first across now going to the 2nd 1 Let's see, How can we do this together, Going back to seek electron. Now, we've got a filter based on the range that really needs, which is between 1000 and $2000. The filter big, some range in sickle. What we use is the term or a keyboard hold between. What I'm gonna do here is I'm going to type where, which because we need to filter based on range, which is one times into $2000. Then, sure enough, we need to use the column, which is base on the score salary is the column depicting a filter on as we mentioned before. No, you're gonna say that, Okay, we need the values between 1000 and 2000. And as I'm speaking off, it's the exact same in sickle as simple as that. So I'm gonna say between 1000 and 2000. So here, if you see the data returns, you see all the base Saturdays are between 1000 and $2000 including the values 1000 as you can see here and 2000. So as simple as that, you may notice here that I put a seeming column at the end of victories. Usually what we can do here is we add the semi colons. So in case we're typing more than one query, which is possible, But you have to make sure that when you write multiple careers at same time, they don't collide with each other at what it can do here is to make okay. This way cleaner is I can't separate into multiple lines. In this case, it will work perfectly. So it's more readable, big of you, and every close fights online will be much easier to be read Syrian. We can understand that. OK, select the first name last name phone in base salary from employees where the base salary is between 1000 in $2000. As simple as that 16. Filter data based on a set of values using IN: the less request from her boss, ISS warned employees with the salary brackets 1627 100 or 4200. This is a simple as before, with some more typing. We can filter that. So no sweat. Let's see how. Okay, so we need to filter based on three values 1600 president 142 100. If you can look here, we see that we can use any signs of any arithmetic signs, uh, greater than or less than since you need those exact values using equal. It's one value won't get us to the next value If, for example, I say very salary equals to 1600. But yeah, we will be stopped with no value, no data for 27 4200 and between will get us all the values between any range, so this won't help us much. What we're gonna use here is a new term or keyword calls in in databases we have what we can call sets and sent is like, um, a group of values which are different from each other. There are equal, or we can say they're not similar to each other or the best way to say that it has unique values. A set has no two values of the same value. So we're gonna use a set here in simple In order to get that we're going to see here is we're gonna say that, OK, select the data from employees where the base salary is one off those values in this sense . So the key worker can they use here is in, and I will show you this one I'm gonna leads on. I'm keeping the base underscore Saturday. Since we're filtering using this column, what I'm going to say is, I need all the data from employees where the base salary is in dissects you. You say what values that we need to look for, which are 1600 when it 700 and 42. I'm going through those problems with is a square. So what I said here is give me all the employees who's big. Settling is either 1600 won 700 or 42. The database will look into each record in employees and see if the value off still based sound is in this set. It will return for us. Let's see, Here you go If you see big Saturday, you will find exactly those three values. 1600 2700 42 market. So here we get the exact values for the base salaries that we need. And your boss should be satisfied by now. All right, so we said together, how can we work with numbers? So we so that arithmetic signs we saw how can use between four ranges and how can use e four sets? Sure. Uh, between and in. And even the arithmetic values can be used for dates, instruments, but we will see later how it's different from members, which is important. All right, so we see here, how come you use the filters or the were conditions with numbers you're gonna see. How can you do that with dates? So let's go. 17. Filter data by date: your boss comes again and he or she wants to know. Warden employees hired Sore Toe, doesn't five and who were hired between 2000 and eight and 2010 and were hired on 22nd of August 2010. How come he answered that? Let's start one by one and let's see how can get the employees who were higher before 2005 going back to Sick Electoral. We're gonna start fresh and let's select from employees stable here. We need to know which column that gives us the date where each of Lloyd he was hired. If you can take a look, you can find that the higher date is the column that he needs here. So we're gonna use that and we can filter. Based on this, sir, how can he feels are based on the dates I'm gonna say here that where high school dates the first cushion waas to get all the employees were higher before 2005. So let's use they're less sign and to this and five and let's see what happens. Oops, there's an ear, but it says here that you can't use the, uh, let's down. Saudis between and dates and the number or what they call an indigent. So what kind of do here dates doesn't work this way. What we need to define is when you define a date or want to express it eight you need to express the full day, which is the year the mom and today. So if I want to come here and say that, Okay, I need old employees before 2005. What we need to do is I need the employees were hired before the first off January off 2005 . To how can we type this first for dates? You always use single carts when you deal. It dates when use dates. You used to put them between single cards. So sequel can know that this is a dates and it's not a number. No. You're gonna say that he needs all the boys before first of anybody off to five. So how can we say that is we type the date at this former. We start with the year which is just by he was a dash. Then we say that you need January, which is the mom month. It's one which means January Yeah, we use another dash separate between the year, month and day. So for today we need the first of shadow, which is also what? So here we go. What we say here is get the old employees. Who's higher? Date is before the first of January 2000. Let's see, if it works, execute. And here you go. You can see all the employees whose higher date is before 2005. Figure down. It's stored by a lock, but yeah, as can see, we can see any employees with the higher date 2005 and literal. Great. So you see how can deal with dates, have content filters with dates? Sure, these are the same. So we can use greater down so we can get all the employees who are hired from 2005 and later here you go. And sure, if you want to includes the first of January, we need to use greater than or equal to. It didn't change because we don't have anyone who was hired and first of January go simply it will be. The new year will be a public holiday, so no one will be hired by that time sure who can use doesn't equal or not equal to seven can't all their employees who are not equal or weren't hired on this states. So here we go. Here's how can we filter based on dates. Next we get checked up the other two questions or reports that your boss has to do. 18. More data to be filtered by date: All right, So we answer the first question and going next, or employees were higher between 2008 and 2000 term so here, the same way we filled with numbers when we needed a range. We can use the same four dates. So using between it will help us. Hilter based on range if you go here using the higher dates as a column and the tight between So you want to say that, OK, give me all the employees that are between six and eight and 2010 which means give me all the employees were higher between first of January. 0 2000 eights on to 31st off December for 2000 because we need to cover three years 8 2009 and 10. We start with it gives an eight its first off January UNWTO or ends the ends off 2010. In this case, we cover all the three years it is an 89 Enter. Let's see. Cool, right? So higher date is two in a setting, John going through jobs and nine and 2010. The up but much people isn't mine Warranty. Oh, you go So as you can see, let alone we're gonna see How can we sort or or our data since, like in database, it's not guarantee that it sorts is to, as you can see, that there can be, like, a bit random or not sorted so you can see that favor towards about that. But anyway, as you can see here, we have the data that needs, which is in the range that we look for. So you may ask Okay, Why very first of December? Because if I go to and say, OK, give me between 2008 and those in 10 like this, you see that 2010 will be missing and you go, we can't see aching from two, because simply have you chosen it chose that first of January of 2%. But yeah, what about the rest of the year that you have 3 306 5 days, sir, to cover those, we need to say that you can't give me until very first. Oh, December. So in this way, many people do this mistake and they don't know this that to take care about this, Be careful. So you need to cover the whole year so we can use their first off 19. Yet another query to filter by date: the last request from your boss is who are the employees who were higher on 22nd of August , who lives in my I think this one will be quite easy for you already. Let's see, who's that? Employees or employees who were hired on 22nd of August, who lives in 10. I'm gonna do olders on and filter the higher date. Using equal A periods must be so before the use single cards and we define which take exactly that we need. Which is it isn't 10 August, which is eight. This is a month and 20 seconds. So here you're saying so like old employees, I was hired. Aid equals two second of August 15 time. Let's see. And here you go. Yeah, all employees who was hired interested in August a simple as that 20. Filter by strings (names, words, ...): Now we want to answer those questions. We want the employees with the last name Hollis employees living in Florida on the including with this phone number in Sicko. It's quite simple to get this data. Let's find out. Starting fresh First began its account, whose employees with the last name Hollis. I'm gonna go here and say, Select Star all the columns from me, please. Where their last name, which is last on the score name as their column to look into strings of characters in sequel as we did in dates, we need to use the single cuts as well. So here I'm going to say, I need the last name equal to all This night down, I put the string of characters between single cones. I'll trade if Dad it between single cults and execute this. People give me and here. So Jimmy was back and let's run victory. And here you can see the employees with this last name office. It's in Florida. His mail. There are dates. Jack Tensile. It's several. Please make sure that it's not a case with the sickle language that we learn. Strings are case sensitive means that it depends how do you type it? And how is it stored here? As you can see, the last name is old stored in Capital Letter as the first letter. So they come here and use smaller of all this and look for it. It will return no results. It doesn't mean there's an era. Simply means that it couldn't find the last name with this string of characters. So make sure that when you look for something in strings or characters, it's case sensitive. Next, we're gonna look for the employees who are stated in Florida for that. If you look here, what we need is the column states. So I'm gonna go from here and say, Select all from employees where the states equals two fl which states for for you. I'm gonna run this. And as you can see now it's the old employees. They're sitting in Florida. Sure enough, if I use the small letters I want to find anything. It's because all states are in capital letters in this too. Next we look forward. The phone number provided for us here. Sure, because it can't remember it. I will copy it from here and look for it here So the column is phone, which will help us find before number US employees. So I'm gonna you say where the phone equal to. Here's the single calls based a whole number here. And run the query. Mango. We could find being agree with this phone number. So here you go. You have, like, own book so you can use can't find a phone number that you want. If you're looking for someone, this phone number, that symbol s that. 21. More ways to filter by string: now for more complicated questions. What if you want to know? For the employees who lived in the states, that second letter is the letter A or employees War or four letters, which started a and ends with meat. I want to know. Also warned employees. Was your titles started the World Data or the Employees phone number, which ends with the number 566 or even the employees address has the war. Washington Let's find out. How can we quay such more complicated questions? I think we will start with two first questions. First, we need to know what employees who states Second letter is literally in order to know that first, what we need to look for here is the States as the column that holds all employees states. Yeah, we can't use equals. Gus Pickles will take the exact string of characters that we provided and said here, What we need to know is what employees with the state's off second letter is a first we can't use equal sign here. Instead, we can use something called like means like Okay, look for employees with the States like this pattern or four months so we can use what they called pattern warm are or wild cards. So he gonna say that, OK, find all the states with the second literal Hey, So first, we're gonna use single cults as usual for strings. Next here, we're gonna say that. Okay, look for the second cancer, which is a but what about the first cancer? We don't care about it. So in this case, for a single character, we use the wild card sign, which is the underscore in this case sequel will understand that. Okay. Okay. All the states with the second letter? A none. Regardless, what is the first lotteries? So here it will get all the states that ends. Or a second letter is the letter a Let's see how it goes. Great. So if you look into the states here as you can see all those employees State second letter is the letter A for fun. Let's try for example, second little to be Oh, here you go. What about you want? See the states? The first letter is B and here you go. So as simple as that. Okay. Next, If you want to see all the employees, the first name starts with a and the last letter is e And there or Fuller's How can we do that? As we see here, each underscore sign represents one slot in peace. We need to look for a world of four letters were going to say that we can use the underscore signs as many times we want to fill those letters. That way we use them as a wild card. So you look for all the names or the first things. So we gotta check here for first thing. No, What we want is the first letter of Ruby A. And I'm using Kevin letter because all the first name start with a cup here. I want the last one to me. So what about those two? I fill them up with underscore. So we have two signs here that while card that Okay, I don't care about those two letters. Just check. Everything started a and ends with you. Let's try out and here you go. Amy, start a man ends with E. So you have it 22. More queries filtering by string: now once. Look up, then please. With the shop tackles started better employees. Which one number ends with 566 And the address is that at the world. Washington As you can see here, this one is more complicated. Since we don't know how many characters that we have with states and the name that we look up earlier in the 1st 2 questions, you know exactly how many characters or letters that we have. But here I don't know exactly your job titles off or numbers or the address is we four are four numbers. It may be a bit easier, but what about addresses with, you know? Okay, the club, maybe 20 or even 50 or 100 characters. So how can we use that? Let's find out. Coming back first. We need to look up the job titles which star? The war that, as you can see here, we need to look up first. Which column that has the job title, which is John Underscore title. I'm going to use that and cup for titles off data I gotta say, John, underscore title. I'm gonna use Look, since here I can't use equal sign because I'm not exactly looking for that as a word. But the job titles that has the word better. Starting with better we're gonna use Here is a single call size usual on. I'm going to use the word data from here. How can we say that? Okay, look up for all job titles which start data. Let's see if I use underscore, for example, and run this well, I couldn't find anything since. OK, we don't have a job title with only five characters. Which sort of data? Maybe I can okay at as many other scorers they want. And yeah, I could find something like data entry. Yes, we have here six characters which I couldn't look like this but this one is not efficient enough. It's not practical. So what can we use is another wild card which is much more efficient. It's the person Sigh person sign here. What it does is it tells you that Look as many characters as you can find. So the underscore looks for only one character while the person sign books for as many characters from zero to infinity. So here But I say is look for all the subtitles which start with data regardless off how many characters you have after that. Let's see now what this will get us. And sure, because us much more later than the previous query that get us using the underscore sign. Let's see the job titles and all. Start with data. You have that analysts engineer architect insights. It's Serra. We have database administrator. It doesn't start exactly that, but that's Abi's. This one was located Census food because we use the wildcard underscore or sorry percent site. But it's okay. I want to only find the world is exactly that. And a space I simple us really a space here. Cruel Kate. All the subtitles which start with the word data and that, for example, database or Data Lake for any upper word that starts with D. A. T A. For example. Now let's look for the phone number, which ends with 566 So from here I'm gonna little this and use for, like, single courts. And I want all the phones, which ends with 566 So to say that Okay, I want the bones that end or ends with 566 I'm going to use the wild card person. So knows that. Okay, regardless, how many characters that you had before? You're gonna look for 566 at the ends? That's wrong. This And here we go. As you can see, we have to phone numbers and our end with 566 Next, if you want to look for the address is which has or had the world. Washington, How can we do that? As simple as that? Let me show you. We're gonna look for the address we can use, like since equals doesn't work here. Single courts, as usual. And here we could look for Washington. So for here, I'm telling you that. Okay? Find all the addresses with the world. Washington. So from here, we're gonna use is the wild card for sure, because I don't know how many characters before and how many characters after that. It's here. We put the wild card here and we put another wild car. Your as well. Let's try out. And as you can see, get away the addresses with the world. Washington. What about if you can use this? Let's see this trick. I want all the addresses with Washington and ends with number nine for example, as you can see here, how does this work? So I'm gonna adhere. Nine. What? It means this Look for all the address is which have the world Washington and ends with mine, as you can see, can be only one result, which is the Washington with the number nine. I'm gonna delete this. That's Seymour Examples. I want, for example, everyone that Washington and have the letter streets. Regardless, If it's hand or in the middle, how can we do that? Is we say s t here and we can at this one car as well. Which means look for everything that has the word Washington first, then streets Ste bedroom. If we run this, you can see that it gave me to resource. No, Huh? Washington Street. So regardless, street came in being here, but gave me, as a result because street is the end. So when you use a wildcard even at the ends of the string, it accepts it because it depends that okay, I give you the result, which either ends or contains streets. So as I conceived this example, wild car can be so expendable so you can check for everything from zero to infinity. Well, if they use underscore, for example here. Sure, this one won't return anything, but let's try. So even if, Like Stu waas the ends with streets in case we use underscore, it should be at least one character. It is another underscore say's that. Okay, fine. Everything that has streets and two characters. When my friends anything. Either that use three Diego did find this one goes here. I'm telling it. Okay. I want exactly three characters. What the others? That person sign. Give me that. OK, fine. Between zero to infinity of barristers. So here you go. You can see two weasels have fun and trade himself. And if you're done, let's move to the other section 23. Filter by state: okay. We did the complicated questions. Now we have this final question we need. All the employees will live in both California and usually see as we so that Okay, we get a single state for each one. But what about if we need more than one state? Let's find out coming back here. If we remember when we look up sword, multiple values, what he used is the keyboard in here runs the exact same. So if I'm looking for employees, we live in either California or New Jersey. I'm going to see here. Where states in either one of those. So going to use state in up in those brackets and single coughs. I'm gonna say you need California or New Jersey. So this case who get me old employees who are either stated in California or New Jersey Here we go. Yeah, please. For involved user, See, in California. As simple as that 24. Case-insensitive filtering: Now the question How can we escaped house? What if we don't know how exactly? The names have been sword in the stable and want to look them up. We don't carrying about what they have. Capital letters or smaller hands have been tied. The name or the address. If you come back here and for all the employees, means where their address at the world. Washington like this, we'll see no results. Wow, If you use the W as capital lets her, you can find them. Okay, how can we escape that? We don't want to know that exactly how it's being gone. I was being written that's being tied and can escape those caps. Social in sequel language is using I like I like means insensitive. Like it will look for all the patterns that is in here, regardless, bill caps, capital letters or small legs. Let's find out. Here you go. We could find the same results regardless. If Washington has a capital letter or small letter, if for fun, do this and still the same results. So if you want to escape the case, sensitivity can use, I like instead their names as well. If you look for Amy or not, you're the first thing you go. We found it regardless. If it's capital or a small letter and as again see we can use like and I like the same as equals while equals is case sensitive. So this one. What words are hard if I use I like without any wild cards whatsoever. Sorry, it's sayings. There's a problem here. Burned again. Here you go. Just make sure that when you write, run any anything on Sick Electron. Don't select everything because it will see that. Okay, once one only this scripts and for some issues. So just make sure about that, you know, and we'll get so always remember, What do you want to skate? Consisted of characters use I like instead of Flike. It's the exact same up like, but the only difference is that it's capes, the capital, small characters issues. But you had Tom and we saw together. How can we use filters for strings, numbers and dates in order to get the data that he needs? It's wrong. Going to see how can we do more complex filtering like, for example, if you want. Okay, everything with the base salary above $3000 they live in New Jersey. How can we do that? Let's see together in the next section. 25. Now with more complex queries: Filter with more than one condition: now to a more complex, great let's say we've got all the influence who are unusual. Asi with base salary above $3500. As you can see here, we have more than one filter to work on. First it's the state, which is New Jersey, and then we have the base salary. What about this question? What all the employees hired before 2015 with base salary off $1600 encryption rates Let's down or equal to 35%. And this question. We have three filters. First, it's the higher dates. Second, the base salary. Enter the commercial rates. Last question is, we want the addresses off all constant care representatives who are sited in Kansas with the city code 67 4 time. Yeah, also, we have three filters. First, we need to check all the customer care representatives by their job titles, which is the first filter. Then we have concerts, which is the skin filter, which is the state and last is the sicko. Let's see how we can solve. Those can start with getting all the employees who are in usually see with Saturday's above $3500. So if we start fresh and well type select. Sorry. Drop employees where our first filter is estate. So we're gonna say that states is equal to New Jersey, which here will be N j. So if you're on this alone, we can see that we get all the employees from New Jersey. Okay, great. So what about if you want all the employees are New Jersey on and they have the Saturday, which is about $3500. To do that, we can add a new filter or a new condition adding a new condition. It will be as easy as saying, Aunt. So what I'm saying here is that okay? I want hold employees from user see, and their base salary is above $3500. So after on, I will mention the base salary and above, which is greater than very 500. Let's see how it works. Your first we have to state, which is usually see and a future. Here you find the big Saturday which all are greater than $3500. We need the employees were higher for 2015 with base Saturday of $1600 commission rate less than or equal to 35% going back to Sick Electron. First, we need the higher dates, so remove does and say we need the higher dates, which is before 2015. So we're gonna say it's less than the dates. And as we mentioned before we use single comes, we can say 2015 January and first. So we need all the employees who were hired before the first of January told is interesting . Next, we're gonna check the big salary, so we're gonna see and the base Saturday equal to $1600. We have another filter or another condition, which is we need that commission percentage or commission raids to be less or equal down 35% the future. This column here may not be all visible. Copy it and pass it here. It's the commission, BCT or percent. Your chose that. Usually we have the big Saturday, and we have how much commission or how much, increasing their Saturdays or raising the salaries they have. This is how, like usually some companies do when they stored their employees data. They use the base Saturday and then the increase it based on the commission. So here we want all the commission percentage, which is lower or equal to very frank percent. To do that, we're gonna see hands. Your mission percent is less or equal to as mentioned in the question. So to mention that we need 35% in sequel can do is we can do it like this. Well, it what we can do is we can and should like this. This one, it means that the commission percentage as 35% that strict didato. So get this. And here you go. We have to employees who are hired before 2015. As you can see here, one is who lives in five and the other one is 14. They have a base salary off $1600 you go and the commission rates is less or equal to try 35% which is here it's 6% and the other one is 11%. So here we used three conditions or three filters to get the data. That means let's see, the last query. The last quake is we want all the addresses. Oh, the customer cares representatives who are sited in Kansas with this sicko 674 10 So how can we get this? First, we need to filter by the shop tighter. So we're gonna say we need everyone from the customer care, Russ. Second, we want them to be sitting cancers, which is the States. And finally, it's the zip code. Let's see, How can we do this? All right, I will move those from the less query and we're gonna start. So her order to filter by job title we're gonna use jump underscore title to be equal to some appear representative for now because they know I don't know exactly how it's gonna be . So instead, I'm gonna use I like, So I'm gonna say customer here and I'm gonna use the wild card after that, which is a percent sign. So this case will get me a little shop titles that start with customer care. Next estates, which is Kansas. It's gay. Ennis and Leslie. We need to sit code Zika is 67 for 10 Let's try that looks, there's problem. But it mentions here is that I'm using the zip code as a number, which is wrong. Is it good here in our table? He stored as a string of characters. So instead of using it like this, I should put it in single counts. My dad. So you did say Is that okay? No period doesn't exist. Comparing between corrector varying or strings with an integer, The corrector varying or string is echoed. And this one was a number before I put the same here, it's right again. And here you go. Let's see this guy first. He's from Kansas, their states. Let's see. Jumped item. And yep, he is a customer care representative. Yeah, I was lucky to get this and less about the zip code. Get this 67 for 10 So, as you can see here, we can do much more complex queries in order to get our data. Since we need more information or we need to filter the data as much as possible so we can get the data. That means 26. Filtering using OR: Now let's check out other top of queries that we make face. What about your free one? All the employees who were hired either before do those in 11 in Maine or after different first off August. Do those in 30. As you can see here, it's different, and we can choose between because in between, we have a range. But here we either want all the people who are before May of two lives in 11 or after August off 2013. So it's been won't help us here. What about if we want the employees who live in Texas or they're working as software engineers? As you can see here we're using or like, they're either living in Texas or their work as software engineers. So as you can see here be and relationship, it doesn't help us in this case. What about if you want the states with employees hired after to present 16 working as managers or their last name starts with T here we have three times off filters, but what they want is all three. So here we can't use the relationship because if you filter based on hands, it means that we want people who are hired Effort 2016 working as managers. And their last name starts with T, which is totally different than what they ask you. They want either hired Upshur 2016 the work as managers or their last name start with T. How can we solve this? Let's start with the first square. All right, To, as usual, we get a select star from employees where you what you need is we need all the people were hired before May 2011. So we're gonna say higher days unless thou means before who was in 11 May the first. Okay, so next they want the people who are also hired after August off 2030. So we need either or so in this case, we have the old relationship which can help us in this case. So I'm gonna say I want all the people who were hired either before May of 2011 or after August of 2013. So I'm should say, or higher days greater that or after in deeds through this and 13 August off. Very first, see? And here if you check the higher dates, which I will filter here to make it easier. All the hair dates here are either before May of 2011 or after August of 2000 person. So if you go down, as you can see, we have to visit 11 here until April. True that it hasn't sorted and can seize later. How can sorted and literally can see that all the dates after that is since September 2030 . So all the people were hired between June of 2011 and August of 2013 were in shown here. And here's where or can be helpful. Next, we will sold the next query. Employees live in Texas or working as software engineers. Here we have two filters, which is the states and the shop title, but they want him. Boss. We want both people living in Texas or the people working as after engineers. It's not and relationship there is a difference here. If it's an an means like you can get me the people who live in Texas on and they're softer engineers. But this great say, is that okay? Bring me all the people who are living in Texas or we're working a softer engineers coming back here. I'm gonna remove all those since we don't need them at the first filter is states is equal to Texas, which is TX, or we need the people who work as Salter in Cheers. So how can we filter base image on a job title? So the column name is John Popper score title in the table. And here I'm looking for a software engineer. So gonna use I like since I don't want to hear much about that. Que sensitivity and software in here used star instance that we can see all the columns and let's execute this. So, as you can see here, we get all the people either from sixes or the people are not from Texas. They should be software engineers. Here we go. So here, as you can see, your relationship gets bought off both sides, like Okay, get me all the people from Texas hands also at those people who work as software engineer. So the people Lauren's working at such an engineer there from Texas because they're the group which belongs to Texas. And for the people here, which is steak is not Texas. They're software engineers since the way they came from the group, which they worked as software engineers. As you mentioned, like, If I use I like here, why didn't I use equal to Let's see here. I won't get softer in jail because simply suffering is near. Oh, this one is from Texas. Yeah, it get me. This one was okay. It's a software engineer, but he's from Texas so he could get to the group, which is Texas. But I can't find any other software engineers since this one doesn't work, Probably unless I do this on this. That's why I like to school, because I don't care about the case sensitive aspect and imports it. One is not from Texas, but he's a software engineer. The last query, maybe like a bits fuzzy or a bit complicated, But we can do it together. We'd all states with either the employees higher after with his 16 worked as managers over there. Last name starts with the letter T. Let's see, How can we do this one by one? Cruz the hired. It will get us all the people hired after 2016. As we see before, it's easy. Next we need the people who are working as managers become that Help us helps us to get them managers is job title. And we look for the word manager and for the people that their last thing start with T, we're gonna use last name. Come, let's sit together. Okay? Going back or so goes as usual. And here I am looking for only the states. So I'm going to select all the state here. Next, we have three conditions or three groups off employees fares. The people were higher before to theirs. And 60 before or less than who gets in 16 or the next group were the next group. Who are the people who are working as managers? So job title. And I'm gonna look for managers, so I'm gonna see I like, so I won't care about that. Your case sensitive or not. And here, uh, I'm looking for a manager, but I don't know exactly what's up with Manager. So you know, I'm going to use a wild car. I'm going to use percent sign between the manager, so I'm gonna look it. All the managers that I have in my table Let's go, is the people it was last name star with her tea. How can you do that? If you remember Who can use also last name with the like, since we're looking for all the world's or all the last things with cancer, See? So since we needed to start at letter T, we don't use a wildcard before because we want the world to start with the letter T. And since last name will be a capital letter, I'm going to use a capitalist here, So no problem with using we can use, like where I like. It's it's not a problem you cannot keep like this. Show you how Comey use various things. And here I'm gonna use the wild car. So okay, I want all the words or all the last names which start at 9 30 So we have three groups people hired before 12 isn't 16. People were as managers and people last name. Start with T. Let's run this. Here you go. You're sort of the states that have those three groups, and I think though this hour like 50 states, but anyway, you can look up and to make it like, more clear, let's see each run toes As you can see, that database sticks more times since. Okay, you need more data, so it will take more time. Eventually as more You your great can be complicated. It will take more time. And like the more data that you have, more records arose You have in the table could take even more and more time. 27. Filter data with NOT: All right, So let's see more types of ways that we can face. For example, what if someone asks us? Yes, we want all the employees who are not in Texas, New York or California here is different. I want to look for the people or the employees were not base in does three states. What about if they ask us that? Okay, give me all the job titles without any space character. How can we do this? How can we get all the addresses that don't contain the word? Maryland, for example, sold this. Let's see how together coming back to Sick Electron. Let's see how can get the employees who are not based in those three states Texas, New Jersey and California. Yes, Aziz Usual with select star from employees. Where? So before we. So how can we get the people or the employees from Okay, we have, like, more than one choice. So if I told you that we want all the people in those three states, what you usually do is okay. What people were that states him. And here we use a set. So we have Texas. We have New York and have California here it will get us the employees war base induce three states. And sure enough, as you can see those are the employees living there. Okay, What if I want the employees who are not banks and does three states? What kind of me too? This is a symbol. As it gets with one keyword. It can't reverse everything and all the reasons altogether, simply by using the keyword not in our we're close. It will you upside down. And it will get us the employees who are not in Texas, your nor California. How can we use? Not as we can read it as simple as we say, where the States is not in Texas, New York and California. So here I'm coming and sitting and typing where states knots and sure you shouldn't use is because he is not doesn't exist. Encircle, at least for this case. So we're state not in Texas. New York, California. As you can see, the cool thing about sequel is, but the language is quite clear. Explains itself. Let's try this. Here we go. We can see all the states accepted three. And to make it easier, I'm gonna select only Estate column There we go. You can take a look. You can look for all the records or rose that we have here, but you won't find those three. So not is quite handy for such queries. What about who won the job titles without the space character means that. Okay, we're gonna check the shop title, which is not like the space character. How can we interpret this? How can me type that in sickle? I'll show you coming back to Sick Electoral. I'm gonna move this condition, and here I'm gonna say this sometimes. So if you want to look for about John, okay, with no space characters, we're gonna do that. The reverse. For now, we're gonna show you how we can get the shop titles with it's based culture to do that going to use like since its best characters, they don't have capital. Small letters will be the same single coats as usual. And we're gonna use this space here. I want to mention something here that when you use strings with spaces, you need to be extra careful in sequel. Yeah. We can use as many spaces as we wants. It's a problem. A little like you can keep as many spaces that they want. And it's a good practice in case you can at clean your coat so it can be easier to be read by others. But when used in single cults, so you have to make sure that Okay, uh, each space is considerate here. You should take into consideration when using spaces inside the single calls when using strengths and comparing two instruments anyway, So we're gonna use the space. And we're looking for the space character in middle of the job title since usually, like if there, if the records are clean, you won't find spaces before and after the job titles. So I'm gonna use wildcard person. Sign to here will get me all the job titles who have the space character. I'm gonna run this, and as you can see here, it runs in his star. I'm gonna clean my code. I don't like it's be quick, Missy, But see, ago, I'll make it even better. But using title here, instead of seeing all those columns And here you go you are the drop battles with space, at least one space. So as soon as it finds a space at least one space. It will get it for you. Okay. What we want is it's also opposite, which is okay. I want all the shop titles without any space characters. So to do that, we want the reverse, which is okay. How can we use this as you remember, We have the nuts Key word. So to use it we can't go here and we say is up Tyto No, like and as a cilic as we see it in the English language we're seeing we're shop title. Not like this butter or this former off a string that we're looking for, which is this space. It's from this. And, uh, we have the shop titles without any spaces with which we can say that. OK, job titles with one more Seo treasure accountant, Receptionist, controller, etcetera. So not helps us. We're okay. Getting the reverse off the condition that we have here the condition with like and space with the wild card signs means like Okay, get me all the shop titles with the space character. When we used months, it will get me the reverse. The opposite of the conversation here the less query that we have here. We want all the addresses that don't contain the word Barry Lines. And I think by now, because I think that six square was harder than this. Maybe so. This one is quite easier. Let's sit together. So here, I'm gonna fix it. So we want the addresses, which doesn't contain Maryland. So I'm gonna say you were address. Not like or I'm going to use. I like since I don't want to go into that capital letter and smaller thing. So I'm gonna use instead of two space, I'm gonna say Mary lands like that. Let's see the results. There you go. We can see year that we can find a word. Maryland in any of those addresses can take a look as much as he wants. And you will find out that. Okay, this condition get gets us old. The addresses which don't contain the work money, guns 28. AND, OR & NOT Operators: Which is considered first?: Now we want to run this query, which shows me the first name, last name, state and base salary for all employees who are in Florida. And the base salary is either 1800 or $2200. So you know I'm using the and in your relationship Sue to see that I want everyone from Florida on and either the base areas 1800 or turns 200. So let's look together. What will be the result for this? If I run this and check the results here, you will see that we have employees from Florida whose salaries are either turns 200 or 1800. But also, if you notice we have employees from other states, which we didn't mention here, what it wanted is we want all the employees from Florida only. So what is that? To understand what happens, we need to understand how the end and or opiate report here. Let's say that in math we have the plus and the multiplying operators, which both work the same way as on it. For example, if they say that I have this which is two plus three times five, what is it equal to here. Some people may do the mistake that they do the addition first. So they say that it's too plus three which is five then they do five times five which supposed to be 25 which is completely wrong. It doesn't work this way. Actually, in commuters, usually an s scale what happens is that multiplication has higher priority. Done the edition. So what happens actually is the multiplication will happen first. Then the edition will happen later. So what actually is the result will be three times five first, which is 15 Then after that too will be added. So the rial result in this case will be 17 which is not you're looking for if you want the result to be 25. So how to fix that usually is by using parenthesis. So if you but burnt pieces like that And so what happens is everything inside the princes will be happening first. Then the multiplication will happen later. So here what happens is you add to and three together. So I could give you five and then five times five will be 25. So this is how it works in math. The same thing works for ons and or operators in a skill. Always remember that we can say that the ants operator equal to the times and the or is equal to the plus. So what happened here is that the and relationship was applied first. So what a scale understands? Is that okay? You want every every every employee from Florida and they're being Saturday is 1800 or I want everyone with the salary of 2200 which what shows us here? You have everyone with the Saracens 200 we have the 1800 for only from Florida. So how can we fix this? We can fix it by using parentheses. So to fix it, I'm going to use the princesses like that, and here it will be more clear. I want all employees from Florida who Saturdays are either 1800 or $2200. As you can see, it's more readable this right? So always use parentheses when you have multiple and in all relationships are operators in your query, so it can be readable even for you and folks you get a work with. So let's warm this again and we will see that now it gives me a lot the employees on Lee from Florida and with the base area of either 2200 or 1800. So this is what exactly what we wanted here. Now I'm gonna check with you. They are not relationship. So, as we check before in our previous video that they're not relationship will OK, do the opposite or the negative off the condition that comes after. So, for example, a paint used not here where not state equals two Florida and check the results. So you will see you that. And give me all the employees with the base Saturday 18 hundreds or tens to hundreds, but with states, everything except Florida. So what if I use the knots operator here something, everything from Florida and notes base salary equal to this orders. So if I run this, for example, for fun, we'll see that I have everyone from Florida and with base salaries not 1800 or 2200. And you can check the results here that we have known off those two values here. So just to New mountain, you that about that not relationship and how it works. And sure, you can put it everywhere that who wants, and it will switch to the condition that comes next. So it's either one condition as we did for states or the parentheses, which is the group of conditions for the over operator. 29. Basic math operations in SQL: Now we're going to talk about the matter operations in SQL. So as in math, we know the four basic operations which are addition, subtraction, multiplication and division. So in a scale, we can define those bite plus sign minus sign the star signed for multiply and the slash sign for division. We have 1/5 operator here in SQL, which is called the Model, which we will check together. What does it do here in sq? Let's see together. How can you use the math operators in Eskil? I'm gonna show you now How can we use the map or period towards in the scale? First thing, I want to show you a trick that it's not necessary to select from a table if we need to do any operations in Eskil. So what we can do is we can say select one. And if I run this query, you will see that it will return me a dummy value, which is number one. So we're gonna use this to show you some other operations. If I say here one plus one and execute this simple query, it will give me the result, which is to which but we thought off. If I run another query and I will see that we can say Select five minus two and let's start together. What will give us? You can guess. It's three. A symbol is that. What about the multiply? If I say select five times three and run this query, Here you go. You will see the number of 15 5 times three is 15. Quite typical. Now let's come to division. This 1 may be a bit surprising and a bit tricky. Birch. It's quite easy. Don't worry about this if I say select and let's say five, divided by two. So what we know here is if we use a calculator and do that, it will give us 2.5. As a result. Pride. Let's run this and see. You can check here that the crazy returned the value, too. And what about the fraction, which is the half 2.5? That should be the complete value, especially that you know that in unusual going to use the commuters, it's supposed to return that really values because any fraction can make a make a big difference in the results. If we do any, like big calculations, especially for Saturday's. For example, as we will see in the next video fingers mess scaled, you should make sure that what type of fed what the number type for the sides off the division are. You can see here that we use an integer, which means that a number without a fraction in both sides off the division here. So when we divide into your values together, the result will be an integer. Keep this in mind. So how can we trick this if I want their floating value, the fractions, the real value? Let's say it should be at least one side off the division operation is a real number or a floating number. How can we do this in a skill by simply doing this trick, which we can add 2.0, which means that a scale now knows that one side off the division operation is a real number, so it should return a riel value. As a result. Let me run this and we'll see how it goes as you can see here now, it returned the real value off the division as we expected. Five, divided by two, will return 2.5 as a result, which is the more accurate results we expect from math. But if I do the opposite and use 5.0 divided by two, what will give us the same result, which is 2.5 the same. If both sides are floating numbers, real numbers. Sorry, it will give us to a 0.5. So this is how division works in a skill. Keep in mind that always if you want the real values off any division one side, at least off the division operation should be a feel number. Sorry can get the real result off the division. 30. Modulo operation in SQL: in the last video, we talked about the four month period teachers in a scale which are blast minus, multiply and divide. Now we're gonna talk about the math operator in a scale which is the multiple. So what is them? Moldy little critter in the scale for people who worked on programming and developments, Any language. They may have an idea about the multi look creator, but for newcomers, the model operator may be new to them. So the model off period er is simply the remainder off the division for the two sides of the equation. You give you a simple example. If we say that select five divided by two, it will give us sure the value too. So for this equation, what is the remainder off the division for five divided by two. As you know, the remainder is what what is left from when we divide five by two, which is you should be number one. So if you took it out the moldy a lot greater here. And I say select five moldy, low to which is the percentage sign for the model. And there on this you will see that their remainder or the result off this query will be number one, which means this is the remainder of the division off five, divided by two. Let's take another example so we can say seven or yeah, I can't say eights model three. So it model three. But don't give us. You know that the number that can be divided by three is six. So it will be six divided by three, which is to and in case we want to divide eight by three. Serve. The results of the Modelo or the remainder off this equation should be too hair on this. As expected, the remainder off. Dividing eights by three is to take another example, which is, let's say, 19 multiple. Five. Here we know the maximum number, which is available by five from 1 to 19. It's 15 in this case, so if you see 15 divided by five will give us three. Which is there? Result off the question. And what about the model or the remainder off? 19 Defending by five. It should give us for which is what the model give us, as you can see when I say 19 more. 05 It was their meat four, which is the remainder off the division equation if I say 19 divided by five. So the idea of the model is that it will return me the remainder off the division for the both sides off the equation. And it's to know something that doesn't matter if one of the size of the equation is a Friel number here, the model is always returning that remainder off the integer division between the two sides . Sir, it's unlike you know, when we do the division here and we say 19 divided by five in a floating number which in return will return me affording number, which is 3.8, which is the real value off the division. The Modelo will return me the integer division between 19 and five. So this is their idea off the Modelo in sq. And actually, it's in any programming language you're gonna use in any computer, so this one can be very useful for some cases 31. It's payday! Calculate employees' net salaries: Finally, it's baby. So what we need to do is we need to pay our employees for the end of this month. What we need to calculate here is the net salary of all the employees of the company. If you can remember from our table employees, what we have is the base salary off each employee in addition to the commission percentage , which we need to calculate. So the matter operations that we took in the last videos will help us show what is the net salary for those employees. So what we need to do is we need to write a query which calculates than its salary, which is the base salary plus the commission. If we come back to our tables employees, we see that we have all the details that we needs for each employee from the first name, last name, address and states. In addition to that, if you go to the rights, we can shake that we have the base salary and the commission percentage. So how it's being populated is the commission percentage off each employee is the percentage off the increase on or the rays on their space Saturdays. So I want to make this great cleaner so we can take the first name, last name based Saturday and the commission percenters. Now we can see it better and cleaner. So we have the first name, last name, base salary and commission percentage. So how this being calculated? How can we get the net salary? Let's take an example. For example, Audrey, All Teoh Artery has a Saturday of $1600 as a base salary, and in addition to that she has a commission percent of off 61%. This 61% will be calculated from her base salary. So if you want to check together here, how much is Audrey's commission? So what we can do is we can multiply 1600 by the Commission percentage, which is 0.61 If I run this query alone, you'll see that $976 should be added to Audrey's based Saturday. So it's a little sad. Ary off artery should be 600 plus 9 +76 bus. So what we want is to calculate in its Harry off artery, which is $2576. So how can we do that for all the employees. Sure, I have over 300 employees here, so it's not, you know, ideal to do this manual calculation. And this is why we use computers and databases in first place because they're so powerful that they can do the calculation than us. So I hope you can understand how the calculation should be done so we can move n right to query. So if you come back and as we mentioned, we need to calculate the commission percentage feature employees. So based on the matter operations that we took and the multiply operator, which is the star, it will help us calculates the commission based on the base salary. So if you checked this query here, but it gives us is the first name last name and the operation, which is the base salary times a commission percentage, which should return the commission that should be added to the base salary for each employee. If I run this after that out and I'll see that I was able to calculate the commission for each employees and here's old real and this is her commission, as we calculated manually, it earlier so cool we could get the commission for each employee. But now we need to add it to the base salary so we can get the net salary for each employee and we can pay them today. So what we want to do is we need some changes on this quay in order to work. So as we mentioned that, we need to add the commission to the base salary. So what a magnitude is to make it readable and easier for you to understand. First, I will add the parentheses for the commission equation, which could calculate the commission for each employee. Now what you want to do is add this commission to the base salary. So what we want to do is I say, base salary plus and the commission rate. Hopefully, this can be understandable bit. Maybe it's a bit confusing, especially for people who are not very good in maths, but I hope I could explain as good as possible. So what can see here is I couldn't add the commission to the base salary. In this case, if I execute this query again, you will see that we could get the Nets Hadary off those employees and his Audrey. And here's her next Saturday to make sure that you could see it. What we can do is we can show the base salary a similar set so we can do a comparison and return back to Audrey. You can see that the base Saturday for Audrey is 1600 while the next Saturday after adding the commission, is 2500 and $76 so we can see that we could successfully calculates the net Saturday for each employee here. 32. Filter data with math equations: we saw together. How could we calculates the mid Saturday for each employee and show it as a separate column in our query by using the select statement when we selected the first name last name the base salary and finally, the calculation for the net salary for each Simply so this Saturday has been applied on each record off our query so it could calculates the mid salary for each employee. What we can do as well is we can use in the matter. Painters on the were close as well. So what we can do is we want to show, for example, all the employees whose commission is over $2000 for example. So if you go back here and I want see the base, the commission for those employees to make sure that it shows me only the ones with the commission over 2000. So here's what I can say is I want holy employees with the bases with the commission off over 2000. So if I run this, you will see that here in the new comb we have all the commission, which is over 2000. So keep in mind that we can use the math operations in the wear statements as well. It can be very useful if you want all the details, but you can do the calculations in the where statement instead. So what you can do is you can simply don't show it. I need all the employees regardless, and I don't care about what is their net salary. But what a care is. Their commission is over two thousands, which will show me here. So the defense you don't need to show all the results in the silicon statements which can do is you can add your filters. They filter based on a matter operation. Sure, this can work on any condition that he wants, as long as your math operation is correct and valid for the scale language. 33. String concatenation with '||' operator: in this video, we're gonna talk about string concatenation. What we mean by that is joining two strings together into one. Let's see this example. If I have towards, for example, hello and work. I can join those two strings together into one. Using concatenation upgrades were I always keep in mind that when using strings not stored in records, you need to use the single goats, as I did here. So if I go here and I say that I need to join or calm Katyn eight those towards into one. This is an operator in a scale that helps us do so, which is by pipe. If it's confusing and you can find it in your keyboard, it should be above your in turkey and press shift and back stash so you need pipe pipe. It's an operator to calm Katyn AIDS or join two strings into one. Let me one this and we will check together the result and we can see that it returns Hello , word in one string. What we can do is we can add as many strings that you want. So, for example, I can add 1/3 string, which is exclamation park or two exclamation marks. If I run this, I'll see that it could can catch innate or join three things into one. So what's the problem here if you check hello word. It's talked into one which is not usually what we right when we want to write words, we separate words with his space, so it's much easier to write them or read them. Sorry, so how can we do that here? Here's a trick. Since the concatenation doesn't add the space between the strings for us, what we need to do is we need to add the space ourselves. So what I can do here is a can do this trick, which is between hello and world. I can't adds a string, which is a space. So what it can do is open single courts. And inside those, I can add a space letter that's right now and execute this query. And as you can see, we can see how the word and the space is between the two words. So this is how we can't separate between strings in case what we need to do is we need to join two strings together, but without sticking the characters so we can at spaces or dashes or slashes or any separator that can help us do so. So what we can see now is we have a very great example in our table employees. If I go back and I can check, we have the first name and last name from employees. We're on this, too. This table has all the first names and last names for the employees of the company. What I want to do is I want to create or I want to show a new column, which is, for example, the full name off those employees. But I want to do is I want to see the full name and I want them in one column instead of two, as I have here. So what we can do is we can add 1/3 column in our select statements, and we can concatenation or join first name and last name together. So if I do this by pipe and then last name here, I will come. Captain eight, we're joined the first name and last name off the employees. And if you took together, we will see that we have the first thing last name and the fooling which is now we can see it in the third. Calm here. Sure, we have the same problem that the concatenation doesn't separate the words of the strings from each other so we can do the trick. We did before and we can add the space ourselves. So here I'm gonna at third a string which is between the first and last name. Keep a space, the single calls and I will join it to west it like this. And sure, the concatenation is being done from left to right. So the concatenation will be happening on the first name, then a space, then the last thing. It's like any math operation that we have. It's from left to right now. If I run this, you can see that the third column represents the full name off our employees. This is how we can use the string concatenation for our advantage. Keep this always in mind. It's a very good trick to do in your quays. 34. Concat strings with numbers: before moving to the next video. Blessing won't show you something about string concatenation with numbers. So if I come and say this select hello and I want to come Katyn ate it with the number nine , which we know that hello is this ring and nine is an integer. So if I tried to calm Captain eight or join those together, the result will be a string joining hello and the number nine into a drink. So if you want to come Katyn eight numbers and strings together, those numbers will be in the same shape or the same way that they are being written. So what a scale does is it converts those numbers into strings. Then come cat tonight them with the other strings in the equation. And sure, but we can do is either the number is before or after. That doesn't matter. So if I run this now, we can see that number 10 is in the start off this result off the string, then hello and then nine and sure, but it can do is a cancer. Pray them with the space. So if I need to separate does and they have a group of numbers and strings together to become caffeinated or joint so we can see how this can be. Don't this can be useful, who we want to form addresses or form any type of numbers and strings together, and you can create a new column in your select statement and show it off. 35. Give your columns a cool name: in the last video we saw, How can we come? Katyn Aid strings and numbers together. So what we can do is we can form new columns based on multiple columns that we have principal. Here we have the first name and last name, which are string columns. What you can do is we can convert them into one column, which can be called full name, as we didn't hear. You can see that the first name and last name has been joined together to have the full name for each employee. But if again, check it out and the name of the columns a bit quirky, it's not and a real name off the column. It's It depends on what I D are using or what the debates are using so it can define its own column name in case you're if showing a new column in your select statement, Big song, several ones. But what we can do is we can choose our our name for the new column that we have created, for example, year. You can call this column full name instead of this quirky name here. How can you do this in a scale? It's so simple and it's cold alias thing. We can define aliases as a replacement or alternative name for our columns. So it's not about Onley creating the new columns it based on the select statement. But even the existing columns you can change their names in case they're not clear are we want to export it as a report, and you want a more obvious or more clear earnings for your boss or for anyone that you're reporting to two. If you want to do an alias for this column and we want to name it full name, it's as simple as that. What I can do is after the column equation that, say, which will commute us the full name. Call him, you would say, as which is a key war to show that, but we want is we want to name this column in a different thing or a new ning. So after as I will use double courts and it's not single card, single colts is for strings, but double courts is for column names over for aliases. So here I will say full name. So what I did here is a name This column full name If I wonder story, you'll see that the new name. Now what we have is full name. If you want to make sure that everything is right and to make it more clear, what I can do is I can choose several columns here, and I will shew that as you see the first thing Last name at fooling full name is a newly created column that it had its that concatenation of first name and last name separated by you Space and sure do not mix things up. If I do select star from employees and I want this part, you won't find full name here because what we did is we didn't a create a new column and we didn't added to the table just like the select statement. What will show us in this query that we want? So make sure that if you understand that select statement doesn't do any changes on the existing table, it just shows you what foreign Lazara questions that you're doing on this data, which is a cool particle select secret so you can keep their old data in your table and it can do whatever questions but ever changes or transformations that you need to do so you can shoot the leader, which is the powerful part of databases in general. 36. A report for Payroll: now, since we learned everything that we need to know, let's make a full report for the payroll department. What they need is the following details from the employees They want their I DS names, email addresses, job titles and their nets Saturday. So let's start together to work on this query and export the report going back to Sick Electron well, right together the quay to generate this report that the payroll department needs. So if we start, we can say select, and what we can do is in a scale. It's quite flexible to use as many lines as you need to know. What you can do is for to make it more clear, we can use each line for a colony. So first they need the I. D. So it's as simple as the RD column in employees. And if I want, show you all the columns that we have just to make it easier. Here you go. Here, we can see all the columns needed, and we'll start one by one. So first, what we need is the I d. Just quite symbol. Next. What they need to do is the full name or the employee's names, which is surely they need the first name and last time. But instead of doing like that, what is better is we can simply create a new column and call it the full name, as we did before. So it will be more clear for the payroll department. So I'm gonna do this. I'm gonna con Katyn eights first in the last name, spreading them by space. So we're having the full name off those employees. And to make it clear, we can add an alias and I will call this full thing. So so far, we have this okay, which is like, two out of five. I think so. We're almost there. I think so. Next what they need to do is they need the email addresses if I can show you the email address again for our table, however, in the sport, and we'll see that we have not the address, by the way. But we have the email address or it's called email. So you can see here the email waas created by using the first letter off the first name on and the last time off each employee. But what it misses is the full email. Sometimes they do that because usually companies use their domain name to create any emails . So it's no need to store the domain name for each email, since it should be clear but for sure, when we want to do the report, we need the full email address here. So what we need to do here is we need to come Katyn AIDS or join the name of the email, the first part with the ATS Onda domain name off the company. Let's say that the common his name is Acme. Okay, so what we can do is M. Lioce at acme dot com. I simple as that. To do that, what we can do is I'll say email and concatenation with the domain off the company so you will join it with at Give me that calm, which is that you know of a random, lean, random company name. This way, we'll having the first part of the email and joining the domain name off the company. So we'll be having the full email address. And sure, what you want here is we want a cool name for this column, so we will use an alias and we call it email mattress. So this way it will show a better result. Next, What we need is the shop title, so just shut up. Title is quite straightforward, so here it is. You have the job title, which is each employee's position in the company, so we will show it as a shop title. Easy as that. And finally, we need the mid Saturday off those employees. And if you can't remember from the last futures that we did and we have the base salary and commission percentage, and if you can't remember that question, it's OK. I will just write it here, which is the base salary, adding the commission, which How can you calculate this is based battery times, the commission percent ish, and since we need a cool name as well for this, so I'll call it the Net Saturday in an alias. So this is the creative that that payroll department needs.