SQL Server 101 : Microsoft SQL Server for Absolute Beginners | R Kahn | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

SQL Server 101 : Microsoft SQL Server for Absolute Beginners

teacher avatar R Kahn

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

43 Lessons (3h 51m)
    • 1. Course and Instructor Introduction

      5:03
    • 2. Pre-requisites & Different Editions of SQL Server

      6:20
    • 3. Downloading Express Edition & sample database

      2:49
    • 4. Installing the downloads & Configuration

      18:42
    • 5. The sa Login

      4:02
    • 6. Wrap up - what we went over !

      1:12
    • 7. What is RDBMS ?

      12:13
    • 8. Where does SQL Server fit in ?

      2:16
    • 9. Basic database objects

      16:08
    • 10. Different Files that got installed - MDF & LDF

      3:08
    • 11. Wrap up - what we went over !

      1:22
    • 12. Getting started with SSMS

      6:31
    • 13. Getting around Object Explorer

      5:12
    • 14. Query Window

      5:59
    • 15. Pre built DB - system database

      4:20
    • 16. Wrap up - what we went over !

      0:58
    • 17. Bare Minimum : SELECTING a literal

      4:46
    • 18. Retrieving / Querying data from a table - using FROM clause

      6:27
    • 19. Playing with Column Names

      3:06
    • 20. Rearranging columns to your liking

      0:32
    • 21. Wrap up - what we went over !

      0:53
    • 22. Narrowing down and ordering result set - WHERE and ORDER clause

      16:45
    • 23. Narrowing down result set - BETWEEN / NOT BETWEEN

      9:05
    • 24. Narrowing down result set - IN and NOT IN operator

      4:53
    • 25. Narrowing down result set - Pattern Matching : LIKE and Wildcards

      10:48
    • 26. Wrap up - what we went over !

      0:51
    • 27. Dealing with nothing : NULL

      5:56
    • 28. Working with strings : Concatenation and other string functions

      14:49
    • 29. Working with dates : Date Functions

      7:26
    • 30. Some System Functions

      5:01
    • 31. Wrap up - what we went over !

      1:42
    • 32. Joining multiple tables - INNER JOIN

      8:49
    • 33. Join types - OUTER JOIN - LEFT / RIGHT / FULL

      8:09
    • 34. Join types - CROSS JOIN

      1:57
    • 35. Holy UNION !

      3:03
    • 36. A bite of Sub…….queries

      4:12
    • 37. Wrap up - what we went over !

      1:31
    • 38. DISTINCT and ALL Predicates

      2:22
    • 39. Aggregate Functions

      3:14
    • 40. GROUP BY clause / HAVING clause

      3:48
    • 41. WHERE and ORDER BY in aggregate queries

      3:30
    • 42. Wrap up - what we went over !

      1:00
    • 43. Bonus Section

      0:04
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

318

Students

--

Projects

About This Class

A *** MUST HAVE *** to get you quickly started with SQL Server regardless of the version you are working with !!!

In this course you will learn:

  • Start from scratch by installing Microsoft SQL Server Express edition

  • Requirements for installing Microsoft SQL Server

  • Core concepts of RDBMS

  • Comparison of MS SQL Server with Oracle, IBM etc.

  • Getting around SQL Server Management Studio

  • Details of System databases

  • Constructing SELECT statement starting with literals

  • Using FROM clause

  • Working with columns specified in the SELECT LIST

  • Using WHERE clause

  • Using ORDER BY clause

  • Using BETWEEN / NOT BETWEEN operator

  • Using IN / NOT IN operator

  • Pattern matching using LIKE operator and wild card characters

  • Working with NULL

  • Using string functions including concatenation

  • Using Date functions

  • SQL Server System functions

  • Working with JOINSINNER / LEFT OUTER / RIGHT OUTER / FULL OUTER / CROSS JOIN

  • Working with UNION / UNION ALL

  • A look at sub queries

  • Duplicate rows – DISTINCT / ALL predicates

  • Aggregate functions

  • Using GROUP BY clause 

  • Using HAVING clause

Meet Your Teacher

Teacher Profile Image

R Kahn

Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Course and Instructor Introduction: Welcome to SQL Server One a one for absolute beginners. We are on a learning journey, so sit back and relax. Now would be Rashid Khan. That is about me. I have a degree in computer science from New Jersey Institute of Technology, New Jersey USA. I have extensive professional experience with Microsoft stack of technology that include SQL Server ASUs Arrests S S I S S ISS ISS Yes, be dot net on darknet development. Also, I have worked with different types of companies belonging to different verticals that include finance, manufacturing, consulting and most recently, in delivering a cutting edge analytics solution for a Fortune 500 healthcare organization utilizing a child seven mes SQL server Assists Rs as this I s is this S and S P darknet Now who would be the target audience for the schools? Or what are the prerequisites for taking the schools? I expect zero prior experience with SQL Server or with any other flavor off data bees. You will need a PC, laptop or desktop. Your pick. You will also need SQL Server 2012 Express edition, which is freely available at Microsoft's site, and I will show you how to download and install it. You also need a high speed Internet connection. This course has been structured around eight modules in models one and two. We will get to know about required piece of hardware and software to install SQL Server 2012 Express edition. We will also understand basic configuration and get to know about s a log in. You will get to know what in our d. B. M s is what MDF and early files are In. Model three, you will get to know how to get around SQL Server Management Studio. You re window an object Explorer. We will also go over some of the pre built system databases and will We will also install a sample database called Adventure Works. In Model Four, we will go over a bare minimum select statement we will also know about from class. We will get to play that column names and finally we will also rearrange columns to our liking. Module five, we will focus our attention to narrowing down results set. We will see how their claws and order by clause work. We will also filter data based on date and time feels we will do some pattern matching, and we'll get to know about wild cards. Finally, we will also dig into not and in operators. In Model six, we will get to know how to deal with null, which is a special way of saying nothing or unknown in a skill. Several. We will work with strings and dates. You will also go over some of the system functions that will make our lives easier. In models seven and eight, River Goal were retrieving data from multiple tables, and we'll get to know what different types of joints are. We will also go over union sub queries. An aggregate functions. We will also get to know about group by Klaus and having clause in order to group and some rise over data. On completion of the course, you will get a bonus section with a lot of goodies in it. And, of course, you will also get a certificate of completion. Now some people ask me about how to succeed learning a new concept or a tool. I say there are only three days to succeed. Learning step one would be to practice. Step two would be to do more practice. Step three you guessed it even more practice. I would love to hear from you. Please feel free to drop me a line with your suggestions and or comments. Thanks for watching and happy learning. 2. Pre-requisites & Different Editions of SQL Server: in this lecture, we will go where different editions off SQL Server that are being offered by Microsoft, each one of them catering to specific needs off a particular type and size off organization . We will briefly touch upon different belts and vessels, that is yuning features that come along with each type of addition. Then they will get into details about required hardware and software for the installation off a skilled several on your machine There are three principal additions of SQL Server 2012 at the top of the food chain comes to Enterprise Edition. It is a premium offering from Microsoft and is rightly used to provide well West high performance mission critical solutions for large enterprises. It comes in world flavors 32 bit and 64 bit. Then comes the Business Business Intelligence Edition. It is more suited to provide solutions around business Intelligence bees data. It has reduced features when it comes to transactional based data and compares in tow. Enterprise edition Like Enterprise Early Edition, Microsoft offers this addition in both flavors 32 bit and 64 bit, and the third position comes to Standard Edition. It has reduced set of features and is a specifically tailored for small organizations that don't work with very large data sets. Like the other two orations, this one also comes for for 32 bit and 64 bit systems. However, please keep this in mind that a more detailed discussion about these three additions is beyond the scope of the scores. Please stay tuned for my next upcoming cause, where we will dive a little deeper. Now this begs the question as to how we can get to play with SQL Server without shelling out huge sums of money for this very purpose time. Feli Microsoft gives us two options, which I call additions off SQL Sever 2012 to get your hands dirty With first comes the developer a vision. This is available at nominal cost. For Microsoft, it may be used to build and test application. However, it is not licensed for production. It comes with all the bells and vessels of the Enterprise edition. Finally, we have an express edition which is absolutely free to use and his ideal for small organisations. This is the one that we will utilize for the schools. These are the system requirements for installing a skills over 2012 express oration on your machine. We can work either of these operating systems. Windows seven Windows Server 2008 are two Windows Server 2008 service back to Windows Vista Service back to for 32 bed system. We need Intel or any other compatible processor with speed. When gigahertz or higher. Microsoft recommends two gigahertz or higher. But for 64 bit system, we need 1.4 gigahertz or higher processor. And as far as memories concerned, we need 512 megabytes of RAM. Microsoft recommends two gigabytes or move in terms of a storage. We need 2.2 gigabytes of available hard disk space before starting installation. We need to enable Microsoft Darknet Framework 3.5 So it's back one and download and install Microsoft dot net Framework 4.0, Next, I will watch you through the actual steps of finding out the configuration off your system in order to prepare it for installation. The easiest way to find out hardware configuration off your machine would be to actually right click on your my computer icon, then click properties here. You will see the operating system installed and the hardware configuration it is running on . In my case, I have Indo Server 2008 are to data center operating system with an Intel processor having speed off 1.6 gigahertz. Also installed memory is six gigabytes. Now I need to find out how much of free hard disk space I have. Any turns out that I have plenty. We also need to find out the dotnet framework installed really to browse toe to this folder , depending on whether you have a 32 bed or 64 bit system. In my case, I have bought dotnet framework Question 3.5 and much version 4.0 installed. If you don't find it on your machine, please first install darkness dot net framework version 3.5 by downloading it from the U Earl shown on the screen. And then afterwards download and install dotnet framework version four point. Oh, by downloading it from the you Earl 3. Downloading Express Edition & sample database: In this lecture, we will download SQL Server 2012 Express Edition. Then we will also download adventure work, sample data bees and get ready for the next lecture. They will actually installed a product in order to download SQL Server 2012 Express addition, you will need to go to the Earl shown on a screen. Once on the site, we leave the language selections to be English and then click on download button here. We will select either this file if you have 64 bit operating system or we will select this fine. Assuming that you have a 32 bit operating system installed, notice the file sizes. In both cases, it is 1.3 gigabytes. In my case, since I have a 64 bit operating system, I will select the 64 bit installation file and we'll go ahead and click next. I'll save the downloading file on my desktop, and depending on my Internet speed, the download should finish. I will pause the screen for a while to let the download finish. Okay, I'm back and the file has been downloaded successfully. Now we need to go to the site to download Adventure Works Sample database here, click on the icon that say is SQL Server 2012 0 L T. P. Now go ahead and click on the Link That Say's Adventure works 2012 data files. Once again, I will save the download on my desktop and pauses. Scream to let the download finish. Okay, once again, I'm back and the file has been downloaded successfully. See, that was easy breezy, and we were prepared for our next lecture. We will actually install the downloads. 4. Installing the downloads & Configuration: When this lecture, we will see how to install SQL Server 2012 Express oration on your machine. You will be familiarized with some of the basic configuration options. After that, you will see how to put the downloaded sample data Bees Adventure works to use for our subsequent lectures in orderto bigon insulation, you would need to double click on installer that we had downloaded in previous lecture. Depending on security setting off your machine, you might get a prompt asking to weather run or not the executed. Since we have downloaded it directly from Microsoft site, we are fairly confident that Microsoft is more than capable off keeping Mull bears at bay. Go ahead and click one run. This begins extraction off installation files to a temporary folder. I will pause the screen and let the process finish. Okay, I'm back and looks like the extraction process is near about its completion. SQL Server installer initiates the actual installation process from here. Next, you will get a window that say's SQL Server Installation Center. Since we're not upgrading and already installed prior aeration of SQL Server, you will need to click on the top option after clicking set up Wizard takes over. Set up visit first attempts to check different dependencies that are needed for successful install. Hopefully on your machine. All dependencies are already there. Once all checks pass, click on OK, next we're taking to product updates. Step. Please be sure to check the check box to include SQL server product updates from time to time. Microsoft Patches Critical Wellner Abilities found in prowar version off its product. So it is highly advisable that you select option and make sure that your machine is connected to high speed Internet connection That will check for updates and Alfred completes. Ah, check it shows up. Result in my case and update was found and it will get included in our install procedure. Go ahead and click on next. This initiates the download and installation off set of files that are necessary. You could also find a list of tasks with their corresponding completion status. In my case, a prompt appears, indicating that once installation gets finished, I would need to restart my machine. I had two okay now once again, said it visited appears with a list of tasks as listed out in the left pane off the window during set up rules. Rules check. It gave me a warning. And since it is not an error, I can ignore warnings for now to remain within the scope of this goes next, you will get to select options for installation type task. Here. You may notice that since we are going for a new install and you believe the top option to be checked as a side note, since on my machine I had installed other versions of SQL Server, you may see them in the list of installed instances. Go ahead and click next. This takes us to license terms where you really to accept the license terms by clicking on the respective check box? If you would notice there is another check box which is for providing your consent to share your using data with Microsoft, we might just leave it unchecked as we would would not want Microsoft know what we do and what we don't meet our installed SQL server. Go ahead and click next. Now we get to select features to be installed. For the sake of this exercise, we will click on select all here. You would also see the folder part where shared features will be installed. Go ahead and click. Next set up. Visited will finish up another task and will take you to instance configuration tasks. Here you have an option to specify your instance either to be default or to be a named instance, while remaining within the scope of this course and without getting bogged down by a whole lot of details. Please note that since we may install SQL Server on the same machine more than once, we need to name each installs differently so that they might be referred toe unambiguously . Also, Justin, if f why I default, install instance could be referred either by the machine name itself or simply a period. Go ahead and select named instance and name it as SS two. K 12 e x p R E s s. Also, please take a note off SQL Server Directory and Reporting Services Directory, which we will refer to in our soup subsequent lectures. Go ahead and click next. If you have enough desk space, you will find yourself on several configuration tasks. Here we will go with the defaults as a detailed discussion about service accounts under the context off which usual desk ALS there were service runs is a topic for my upcoming at once level course without making any modifications. Go ahead and click next. This will take you to database engine configuration. Here we will actually make some modifications and no down. Some of the details first comes out. Indication mood offering two choices. Please keep this in mind that SQL Server will let you in or, in other words, authenticate you. Based on the context off window use variety you have logged into your machine or by providing a set of SQL server User I D and password. For the sake of this course, we will select mixed mode. Now, you will need to provide a secure password for a special SQL server. You variety named s A. You also need to confirm the same. A detained discussion about S a large in is in and an upcoming lecture Now would be the time to add current window user in their schools. There were administrators group off users by clicking on the corresponding button. Go ahead and click next. In my case, I got a validation error message indicating that the s a password that I choose is not a strong enough and does not need strong password requirements. I had to simply okay, there a message and Paride a stronger password. Now you would need to go to Data Directories tab and make a note of the different default directories, including User Day W's directory. In order to continue on, go ahead and click next on the reporting Services configuration tasks window. We leave defaults as it is and click on next For a reporting, you will leave the check box unchecked and continue. You'd need to click on next from here. Set up Wizard takes over and starts installation. For the sake of saving screen time, I will pause the screen and let it run. Okay, I'm back and it looks like installation was successful, as indicated by a status message by each of the SQL server components. In my case as previously, I got a prompt saying that I need to restart my machine, which I will okay, it Please keep this in mind that a summary log file is generated at the location shown. Now go ahead and close the set of visit by clicking on close button and closing down the parent wizard window. Now we will make use off our sample database school adventure works that we had downloaded in our previous lecture, locate the downloaded file and noticed the file extension. It should say M d F. We signifies the fact that it is a data file, a detailed discussion surrounding MDF that is a data file an L. D. F. That his log files will be held in an upcoming lecture, although it is not necessary. However, for the sake of conforming to a skills or installation directory structure, you will putting the Downloaded Adventure Works database in the user database directory that you had noted down in one of the power steps you need to browse to default user database directory. Here, you might notice several their trees as on my machine. I had installed several instances off different versions off SQL Server. You might also notice that all directories adhere to a particular naming convention. However, for the sake of this course, please focus to the on the directory that was created for the this particular instance of SQL Server that we just installed on reaching the destination directory. You need toe cut and copy the sample database to the directory. Now in orderto render the sample data is useful. You'd be performing an operation called Attaching a Database. We will do this via SQL Server Management Studio, which is a G u Y application that Microsoft provides us to interact with installed SQL Server. I will please keep this in mind that there are usually more than one way to achieve the same objective. And for the sake of the schools, we will be attaching database as compared to other database migration techniques that are out there. You will need to fire off a school server management studio from Start Menu. Once opened, you really to provide server name or the instance theme while keeping the server type selection to do be database engine. You could try to authenticate yourself by using s a log in credential or by windows authentication. Leave windows authenticate and take a authenticates selected and clicked click on Connect Ah, don we go. We get an error message saying that the civil was not found or was not accessible. Please working out of their message, go back to the server name and expand the drop down list and click on browse. For more option, expand the database engine node as shown on the screen. Here you will find a list of installed instances. You would also notice that the instance name is preceded by the machine name and then and then a backslash. So in my case, proper way to refer to the instance we just installed would be a l i F. That is the machine name. Ah, backslash and then ss two k 12 e x p r e s s. That is the instance name. Go ahead and select. Instance. We just installed an okie out of the window. Now try to connect. And, Lila, you will find yourself connected to you. Install instance of SQL Server. You will also notice an object Explorer in the left pane with different folders shown in a tree like structure. Click on the database node or folder, and it will expand, showing the databases that are available in this particular instance right click on database folder and select attach option on it. You will be presented with an attached database window. Go ahead and click on the add button. Shown this open opens up the default user database directory where we already had placed adventure works mdf I and one off our prior steps. Go ahead and select adventure works. MDF and okay, out of the window. If you notice under database Details Window. In my case, it is showing two files. 1st 1 being the downloaded MDF on the 2nd 1 LDF. This might not happen on you on your machine. In my case, by remaining within the scope of this course, I will select the log file row and click Remove button. However, please give this in mind that they should not be done with the production day babies at all . Go ahead and okay. Out of the window. Assuming there were no hiccups at your end, you would notice that a brand new database called Adventure Rex 2012 has been attached inside the database folder in Object Explorer. Now click upon the adventure works 2012 node and expand the tables. Fuller. He here you will notice all the tables that come with sample data base. Congratulations. You have laid the groundwork for our subsequent lectures. Thank you for watching 5. The sa Login: in this lecture, you will get to know what s a law Guinness and you will take some basic steps to secure. It s a Logan is a default log in with full administration rights for SQL Server for folks coming from UNIX Oli Next world it hold Hold similarity with the route or super user holding all the privileges to do anything with the server Mets fire off SQL server managements to you and explore some of the basic concepts Surrounding s a log in. Go ahead and connect to the installed instance Now expand security folder under Object Explorer by clicking on it. Now click Large Gins folder to expand it and to see list of available authorized users. For the instance, Locate the S a log in and right click on it and then select properties. This brings up log in properties window for this a user here you can notice that there are different options, including ability to change they say password in four Spotswood policy or even force the password to expire. Now click on a status page as shown in the left pane of the window. They should bring up status settings for this a log in here you can enable or disabled s a large in although it is beyond the school. But this course it is advisable to keep s a log and disabled in production databases. I ran into an ex interesting website that I thought to share with you. It tries to predict how long it'll take to crack your password by computing power that is available today. It can be reached at the link shown on a screen. Go ahead and type some password. That is easy to guess. For example, B a s s w o R d, and it appears that it can be cracked instantly. Now, if you try to type in a few phrases separated by dots, you would notice that the strength off your password increases exponentially. Go ahead and type in M Y dot S e See you are e dot three a s s w o r d dot 360 And you would notice that it say's it will take 100 and 54 quintillion years to crack your password . See, Better safe than sorry. Please be sure to select a very strong s a password if you're going to keep the logging enabled in your production environment. Thanks for watching 6. Wrap up - what we went over !: this completes first leg of our journey. Miss Model. We discussed about the prerequisites for installing SQL Server 2012 Express edition in terms of hard there as well as insult software. You saw a comparison of different editions of SQL Server that are being offered by Microsoft, tailored to specific needs, often organization. You downloaded and installed SQL Server 2012 Express oration and sample database called Adventure Works. You also got to know about special Super User S A. And you also got to know some of the best practices in handling the all powerful user. See you in next model. In the meanwhile, feel free to stretch out or grab a cup of coffee or tea. Although caffeinated, both contains some amount of antioxidants. Enjoy and thanks for watching. 7. What is RDBMS ?: in this lecture, we will see what an R D B. M s that is. A relational database management system is here. I will try to address the question of I before attempting to once or what an R D B. M s is. I'm sure all if you might have encountered a spreadsheet ID that you work in some shape or form or another beat Microsoft Excel or any other flavor of a spreadsheet, they provide a decent way of handling data. However, there are limitations to its capabilities. Let us continue our discussion around Microsoft, excel and ponder over some of the practical issues that we face were using it to work with our data. What if you are working with the data set that happens to have, say, 1,048,005 hundreds of intersex rules by 16,384 columns? What do you think would happen? Have you tried to load or open up an Excel spreadsheet off size 100 megabytes or more? Did you notice any performance issue? What if your company is performing planning, forecasting, budgeting and reporting utilizing employees scattered all across the country or even all across the Globe working collaborative Lee and or concurrently against the same D does it? What if your company is doing business in a highly regulated industry? Were not adhering to strict regulations? Might put the entire existence off your company at Joe Purdy Regulations such as Sarbanes Oxley, Dodd, Frank Basil, too You Data Prediction director. You will quickly realize that you have hit a brick wall and the questions that we went over where some, among many others Limitations of Microsoft Excel 2016 worsen Now would be an opportune time for a spreadsheet to meet our d. B. M s. That is relational database management system. What you see is an overly simplified, logical or conceptual diagram, often rd bms. At the most basic level, you have a field or an attribute, which are the smallest logical structure and holds an atomic or single piece off information. An example might be job title holding, Ah, value off CEO for folks coming from a spreadsheet, world Ah field might be loosely similar to a cell. Then you have records or rolls. A record is a collection off arbitrary number of fields making up single entry in the database It is also to be noted that different fields making up the collection store important properties off the same entry. For example, for a given row or record, we might have different properties or pieces of information related to that particular instance off entry, such as job title being CEO, but date being 21st December 1981. So on and so forth you'd also notice, just like a spreadsheet. We have columns here as well. Ah, column is a vertical entity that contains all information related to a specified field. For example, job title column stores all available drop titles in the companies such as CEO managing director. We be system in junior so on until foot. Then you have a table, which you can think as a collection. Off related data entries organized in an M by N matrix, each of them rose describe record on each of the in column. Our field in the record notice employees and department tables in the diagram, an important and central concept in RGB, Miss. World relationships. These define as to how different entities or tables are associate ID with each other. No, they're not simple lines connecting two different tables. Rather, they give a more meaningful description to our store data in our d. B. M s. Our relationship is defined by the keys or feels off the participating tables, the primary key in the parent table and the foreign key in the dependent table. Ah, primary key is usually a single column in a table that uniquely identifies a row contained in that table. There might be incenses there more than one call and provide uniqueness to a role. Then, in the then, in that instance, we call it a composite primary key, our foreign Caisse, simply one or more column in a table. To that refer to a primary key in another table. Let us dive a little deeper into relationship. Our parents single room might be related to strictly one row independent table. Then it would be a case off 1 to 1 relationship. There might be cases where a single row in parent table might be related to more than one rose in the dependent table. Then it would be a case off one to many relationship. There might also be a situation where several roads off parent table might be related to several rows of Dependent Table, in which case we get a many to many relationship. Let's take an example of sales order, header and sales already Dale tables. If you see at the screenshot, you would notice that for each order place, that is for each sales. I degenerated in the sales order header table. There are several products included in that single order signified by several rows in the sales order detailed table, all having the same sales order I devalue from the parent seals or headed table. In fact, for sailed Order I. D. For 3659 There were 12 different products ordered as shown in the sales already. Deal table. This is one to many relationship in action. Another important concept that relates to M s SQL Server 2012 is schema. Schema is nothing but a logical container that can hold me needed of these objects, including tables. For example, Etch our schema might have tables like employed department, while a sales schema, my contain sales order sales person tables. Please keep this in mind that a scheme eyes purely for organizational purpose. While we're discussing cool concepts related to an RD BMS normalization cannot be ignored. Normalisation is the process of designing gate abuse tables in a way that makes for efficient use of disk space. On that allows the vision manipulation and updating off the data. If you don't normally normalize, detain a table, then you are setting up your data for anomalies that can arise either when you update your data, that is update, anomaly or when you delete your data, that is deleted. Anomaly. Oh, are. When you insert new data that is, insert anomaly. A more detailed discussion about normalization and applying the technique often normalization is beyond the scope of the schools. However, please keep this in mind that these are some of the normal forms that we can arrive at by applying normalisation technique to over table one in if it means first normal. Form two NF means second normal Form three NF means her normal form. B C N f means voice codd normal Form four NF means fourth normal form five and if means fifth normal form. Last but not least, you know you need to know about SQL data Tie. Each column in a table has an Associated data type, which specifies what type of data that column can contain. For example, a price column must contain numeric values. Name must contain text data. Why Birth date Must contain date data types Without overwhelming yourself, You may please visit this site to get an overview of different data types that come with Microsoft. SQL Server Now that you have been acquainted with some of the core concepts related to RD VMS, a more formal definition off RD bms is warranted. So a relational database is a collection of data items organized, informally defined tables based on a relational model of data, as first proposed by E f card off IBM in 1970 in his Sammy no paper a relational model off data for large shared data bangs without getting too deep into the courts. Work. Please keep this in mind that a database management system to be considered a true relational it original database management system. It must a dear to God's 12 rules. And no, that picture has no resemblance to E. F court whatsoever. Thanks for watching 8. Where does SQL Server fit in ?: In this lecture, we will see as to how Microsoft SQL Server stacks up with its peers from other vendors, such as Oracle, IBM and others. Here, I will try to make a case for Microsoft SQL Server with help off. A report regarding DWS is published by Cartner, which is a US based independent research and advisory form. God knew, publishes a report called Garner Magic Quadrant, the one that you see on your left, innit? Garner ranks different vendors based on two criteria. Ability to execute and completeness off vision map to Y axis and X axis, respectively. As you can notice in its latest report, published in October of 2016 Microsoft SQL Server is clearly a leader in operational data bees market. However, you might hear from time to time that Microsoft SQL Server comes behind Oracle and IBM in terms of market share. That is because of the fact that seems set of tools that Oracle and IBM Cell are expensive as compared to Microsoft SQL Server, for example, a single license off Enterprise edition of Microsoft SQL Server containing all the useful tools and features out of the box, such as relational database engine data warehouse in Genesis s the E T Yell Indian S s. I s the job scheduling engine SQL Agent. The reporting in a engine, SRS and others costs around $28,000 while comparable set of tools from Rocca might cost around $93,000. Thanks for watching. 9. Basic database objects: In this lecture, we will see briefly as to what other objects besides stables are available in SQL Server. The sole objective of this lecture is to make you a vera off existence off some of the objects besides tables that we saw in an earlier lecture, each one of the object by design are suited to our particular set of needs or interacting with our store data. Hence their analogy with different tools. In a toolbox, I will go over these objects Onley in a cursory manner as a more in depth to the discussion about each of these objects is beyond the scope of the scores. Please stay tuned for my upcoming courses. Please keep this in mind that the pictures or drawings you might see from time to time in my lectures were no resemblance as to how these objects might manifest themselves in memory or while getting saved to disk drives. These are meant purely to describe logical structures and to convey a concept across some of the M s SQL server database objects, which we will discuss our tables, views constrains, stored procedure triggers indexes schema. - We have already seen what a table is you can think off table as a collection of related data entries organized in an M by N. Matrix. Each of the M Row describes a record on each of the N column Off field in the record notice employees in department tables in the diagram table. Certain objects that actually contain all data in a database is generally speaking tables in in a mess. SQL Server may be categorized into the following mean types. User defined tables. These are the ones that you create. For example, employees and department tables, system tables. They are. These are the set of tables where MSs schools over stores, data defining the configuration of the server and information about all other tables you cannot modify or Corey them directly. Rather, you have to use system views instead to gather information from them. Temporary tables. These are volatile in nature and are created and stored in a special system database called Temp TV. They are identified by table names having either a single hash or double hash as a prefix and are deleted when the user disconnected from the server. Simply put, a view is a SQL query that the user defines while creating it on it is a stored in the database. It may be termed as a virtual table, as it does not contain any data or information. Ah view derives its data from the tables, called base tables, on which it is based. Since the views refer back to be stables, it reflects the most current data in the based table. For example, if you notice the diagram, you would see that the view named Employees Under School Department is merely a saved SQL worry that fetches back columns, Employee I. D and last name from Employees Table while the column Department name from the department. Steve. So why would we need views? Well, views are beneficial to end users who do not know how to write complex SQL Corey's. All they need to do is to curry of you. Who's SQL? Statement had been defined by Nick by an expert. Depending on the business requirement. Views also act as a security measure to protect sensitive data as it restricts direct access to data contained in the underlying base tables or even all columns in the Beastie. Before discussing constraints, we need to understand the concept off data integrity while creating or modifying tables, you need to put in mechanisms in place that will ensure that at end of the day, when everything is said and then thousands of users have updated or deleted or inserted a dye into your data bees, your data remains in a correct and consistent state. This is achieved via creating different types of constraints that restrict the type and or value of the data that gets sold. You may code or create a constraint object as part of the definition of the column. It constraints, in which case it would be a column level constrain while on the other hand, you may code or create a constraint object at the table level, in which case it constrains two columns that are mentioned in its definition. There are, broadly speaking, three types of data. Integrity constrained constraint. Uh, safety loves if you me need to be accounted for and implemented by corresponding types of constraint objects domain constraints. These constraints ensure that a specific column or set of columns meet particular criteria that you define. Depending on the business leads of organization. For example, business needs off a normal vision. My dictate that unit price column in sales order detail table must be greater or equal to zero for each and every road that gets inserted or modified in the table. Generally speaking, domain constraints are implemented by creating check and or default constrain objects. NTT constraints These constraints are concerned with Rose and as to how they compare with other rose in the same table, for example, when you need to ensure that every row in the seals order header table must have a unique value for the column sales order I. D. And you define and create an entity constrained, which in this gaze would be a primary key referential integrity constraints. These constraints ensure that a value in a column must match the value in another column in either the same table or more, typically in a different table. For example, the values in sales already column in sales order, detail table must exist in sales. Alrighty column and it's pairing table that its sales order header to. In order to implement this referential integrity constrained, you'll need to define or code for and key constraint. Now let us go to some of the important constraint object that M s SQL Server allows us to create and as to which type off data integrity entity, friend, shell or domain they help to preserve while our databases being heavily used. Primary key constrain primary keys are the unique. Identify IRS for each full ah primary. He cannot be no and must not allow duplicates. For example, in sales order header table. The column Sales or i D must contain unique and not know values for each of the rows in the table, thereby making each rule or record unique. Within that table, you can have a maximum of one primary key defined for your table. Unique, constrained. It prohibits multiple rows from having the same value in the same column and allows Mel Value. If it is beast on a single call, you can have more than one unique constrained by our table. Foreign key constrained. It establishes relationships between tables. When you create a foreign, he constrained to what table you create. A dependency between the table for which you define foreign key that is referencing or a child table on the table. Your foreign key references that is referenced or appear in table Ah, foreign key value must match an existing value in the referenced or the parent table, for example, in the child table sales order detail, the column seals Alrighty has been defined as a foreign key. That references, says the primary key column, also named Sales or I D. In the sales order. Head a table check constrained. It restricts the value that is getting stored for a column or fed of columns. For example, noticed that sales order detail table as a check constrained defined on column unit price. Ensuring that any new Roy's inserted or modified the value in unit price column must be greater than or equal to zero default. Constrained default constraints defined what to do in case no value is supplied by you For a particle column on which you had to find the default constrained, for example, in sales order header table, a default constraint has been defined on column order date that ensures if you do not provide any value while creating a new row in the table. The server with this toward the current system date and time by default stored procedure. Simply put, is a group of one or more T SQL statements that is stored inside the data bees as an executable object rather than in a separate file. Please know that a comprehensive discussion about stored procedure is beyond the scope of the schools. Some of the reasons you might need to quote a creator stood with E. J. New data. Bees are reduced climbed server traffic. You only need to send an execution call to the stored procedure, as opposed to sending an entire group off T SQL statements, and it turns back the desired results, said stronger security. It provides a layer off abstraction, thereby enabling you to provide permission to use a stored procedure two different users without ever giving them any permission to modify the underlying objects with Table Stater's that the stored procedure, called might be referencing code reuse. You can encapsulate a group of T SQL statements that you repetitively use on. Instead, execute the encapsulating stored procedure. Ah, function or user defined function is an ordered set of T SQL statements stored as an object inside the databases and called to work as a single unit. As you notice in the picture, function usually takes in some input called input parameters 01 or more than one in count, and asked for the function definition input values are processed with the end result coming out of the functions as return values. Based on the nature of the return value off a UDF, it may be of two types scallop functions. Scatter functions can return a single value on Lee table valued functions. It lets you return a table of data rather than a single. Very. It is also worth while to mention here that SQL Server has multitude of system functions that come out of the box. They may be used by you to do some of the following tasks. Can work D two types of columns do data calculations, modify display formats and to provide collective output for group off Rose a scheme. I is nothing but a logical container that can hold me needed a database objects including tables, for example. Hrs schema might have tables like Employees Department, while a sale to scheme on my canteen sales order Sales person tables. Please keep this in mind that the scheme eyes purely for organizational purpose. Please note that we have deliberately not gone over trigger and index objects as they are beyond the scope of the schools. Please stay tuned for my upcoming courses. Thanks for watching 10. Different Files that got installed - MDF & LDF: in this lecture, we will see briefly as to which physical files at a store actual data God created on your machines File system SQL server database must be comprised of two different physical files . 1st 1 is a data file that contains all the start up information for the DWS to run and points to other files in the data bees. This is also termed as primary data file. Every database has one primary data file, and you is usually given extension off dot m d f. A database might have more than one data file. That is in addition to primary Data file, in which case additional later filed so called secondary data files often given a file extension off dot N d f. The 2nd 1 is a log file or the transaction log. It destroys the information about database transactions and all data modifications made by each session. In order for I D. W's to be operational, there must be at least one log file, and they're usually given a file extension off dot L D f. Please keep this in mind that the file extension start mdf dot n d f n dot L. D s are given by default on Lee, and the convention is not enforced by yesterday. Server. So you can give any file extension you like at the time of creating your database. Okay, enough with my drawings. Let us go and touch at least on the screen and see the physical files ourselves. If you recall lecture four off model one. I had asked you to know down different default directories, including user database territory under directory. Stab off the installation visit. Now is the time to visit those folders. Go ahead and open up your Windows Explorer and browse to your user database directory. Please note that yours user database directory location might not be identical to what you see on my screen. If you notice our sample database. Adventure works is made up of two physical files. One having dot MGF file extension. Which is the prime me de Delphi in while the file having extension off dot L d f is the log file or the transaction log of the data piece. Thanks for watching 11. Wrap up - what we went over !: this completes second module of the course in this model, you saw what in our dbm. Mrs. You also got to know some of the core concepts and terms associated with it, such as field or attribute records or rose table relationships. Primary key foreign key schema, normalization data types. You also saw a brief comparison off M s SQL server with its peers from other vendors such as Oracle and IBM. Then you saw some of the basic database objects which were tables, views, constraints, stored procedure, functions, schema. Finally, you got to know about the physical files that are needed for skills over to work properly. U N next model in the meanwhile, flee free to stretch out, grab a cup of coffee or tea, although caffeinated would contain some amount of antioxidants. Enjoy. Thank you for watching 12. Getting started with SSMS: In this lecture, you will get an overview of SQL Server Management Studio, which is the primary graphical tool that is used to connect to and work with the data bees instead of slide lectures. I have decided that walking you through the actual steps to use SQL Server Management Studio would be much more beneficial. Go ahead and fire up management studio from your start menu. What you see is the splash screen which should soon disappear when man I've been stood you fully loads up. Now connect to server dialogue box appears that has different options to select from. Let us visit them briefly limiting ourselves to the scope of this course. Leave the server type as database engine. Feel free to take a peek inside other available options which our analysis services, reporting services and integration services. These are other components off M s SQL Server, about which we are not concerned at this point. Then you have server name combo box list that displays all installed instances off SQL server on your machine. If somehow you are feeling to type in exactly the instance name that you selected during installation, there is an option to browse to see what instances off either local our network Instances of a skills over available expanded AWS engine under the locals over stab and select instance Name off your installation. In my case, since I have more than one instance off, local s skills are installed, they are listed here. I will select the name of the instance for express addition that I had installed on a machine after selecting OK out of the window. Finally, you have authentication combo box list for which you may leave the option off windows authentication. For now, go ahead and click connect button in order to connect to your server and assuming that everything else is in water, you should be successful in making a connection to your server. I'm going to click connect and let us see what happens. Hoops. What happened that is examined their message and as you can see, it is indicating that the server was not found or was not accessible. This brings us to an important concept off Windows service. So on a Windows machine, even the database engine is installed as a service, and in our case it appears that the service is not running. Go ahead and from your start menu, open up. SQL Server configuration Money manager Tool that Microsoft bundled in our installation Select SQL Server Services node in the left pane if not selected already in the right pane . What you see are the different components off different SQL server instances that are installed on my machine. I am interested only in the database server component off the SQL Server Instance, which is SS two K 12 e x p r E S s and I need to select it from the list one selected you would notice that the State column is seeing that the service is in a stopped state. Let us start the database over by keeping over the W B so selected in the right being on by clicking here. Notice I get a restarting service Progress bar And once our database server has it started successfully the Eiken turned screen and the column state now sees running. Let us now go back to our management studio and try once again to connect to our database server and way HLA b or in once in the first thing you would notice Ah, window on left side called object Explorer Object Explorer presents us a tree like structure with expandable nodes to explore different database objects. If, due to any strange reason you do not find object Explorer, please select Object Explorer minu Item from the View menu. As you continue on to whom your SQL server skills you, would you realize that there are more than one way to accomplish a given dusk. You have option to work with the Object Explorer and work with different data these objects from the G y itself. On the other hand, you may also sort to using T SQL Code, which is written and executed from within Another window that is called worry editor. Curry editor may be opened by either clicking here on the new Corey, but a button in the main toolbar are You may simply press control Key In Conjunction with N Key, we will keep our exploration off management studio ongoing and upcoming lectures off this material. Thanks for watching 13. Getting around Object Explorer: In this lecture, you will get into more details about Object Explorer. You will also see as how Object Explorer can make you productive without writing a single line of court object. Explorer is primarily used to manage DWS objects off a given data bees. It presents itself as a tree like structure where each node or folder can be expanded by clicking on the plus sign or simply double clicking. Clicking. You would notice that when a node get gets expanded, the plus sign becomes a minus sign. When you right click an object, it brings up a context sensitive menu that would allow you to perform many tasks without actually writing a single line of code. Let us continue our journey of exploration. Remember our favorite sales order, header and sales order Details stables. Let us find them and try to see how we can. No more details about their structure. Go ahead and click and expand the DDB. It's fooling the Object Explorer. Then expand our sample data Bees Adventure Works 2012. Here you will notice Fuller's, such as tables, views and program ability, among others. Go ahead and expand the table schoolers, and it's crawled down to find seals. Dot sales Order the deal and sales dot sales order Header table. Now let us examine sales order headed table by expanding its node. And what do we have here? You may notice different folders named after logical objects that we had covered in an earlier lecture. Things like columns, keys, constraints and others. Go ahead and expand the columns fuller. And here you get a list of columns that make up sales order. Header table. You may notice a golden key symbol against column sales order I D. And within parentheses. P K is written, which signifies the fact that this is the primary key off the sales order header table and the great key symbols that you notice are actually foreign. Keys that reference the primary key off some other table. Go back to column folder and click it in order to collapse the node. Now let us expand the Keys folder. Here you would notice a list of keys. Would primary and foreign keys defined for the table collapse the keys load and move on to Constraints Folder and please expanded. Here you would notice a list of constraints defined for the table. The Czech constraints are usually identified by C. K under school prefixed to the constrained name. Why default constraints are usually identified by B F under school prefix to the gun strain name. Let us now find our way back to the top to the tables folder bicolor Collapsing relevant notes. As you see on your screen. Here is the Views folder, and when you expanded it would you would notice a list of views defining the data bees Find your way out of use node by collapsing it. Now let us expand the program ability, node and hair. You may notice two folders relevant to this course. One is a stored procedures, and the other one is for functions last but not the least. On expanding the security folder, we can find our valuable scheme Object. Here is our human human resources schema and the sales schema that actors container for the respective database. Objects such as tables, views, stored procedures and others. Stay tuned for one off my bonus lecture on performing. Some of them were doing routine database tasks without writing a single line off T SQL code 14. Query Window: in this lecture, you will get into more details about Curry editor. It is very you would be executing t SQL statements. Curry Editor is the place where you would write you to you SQL statements and execute them against the database. Assuming that you were logged into SQL Server Management Studio, there are at least three ways to bring up the query editor or the quarry Window. You may click the new Cory button or from the file menu as, like this, or by using the shortcut key Off Control and N Once Query editor shows up, you would notice that a special Corey editor toolbar also shows up. Metas goal was some of the important to other options keeping ourselves within the scope of this course. This is the Connect Tool Command, which, when clicked upon request a several connection, if not already connected. Since we're already connected to our whatsoever, it is disabled. This is the change connection Tool command that would allow you to change connection by disconnecting and reconnecting the connection you are using on clicking. It brings up your connect to databases over window that we have gone over in one off our earlier lecture. This is available databases combo box that lists all the databases available in the server that you are currently connected to. Tsk accord that you write in. The Korea later executes against the database that you select from this list. This is execute tool Command that executes the de escalate query as written in the quarry. Editor are in case you if you highlight a portion off your Jewess de Escalate statements, it will attempt to execute on Lee that code fragment This is the pars to command that parses you to t SQL Query looking for syntactical errors without even executing them. This is the results to text tool command which one selected displays the result of your quarry in text format in the result pain and the bottom. This is the results too. Great. Tool command, which men selected displays the result of your quarry in a great format. In the result being in the bottom. This is the default option for Management studio. This is the results to fight to command. Richman selected places the result in output file as opposed to displaying them in the management studio. This is the Common Tool Command, which takes in the highlighted lines in the quarry window and comments them out so that they would do not execute. Comments are primarily used for documenting your code. This is the uncommon to command, which you versus the action of common to that is it takes in the highlighted lines, and a NCAA ments them so that they become part off The executed will block off code. While we're discussing the topic of comments, please know that there are two types of comments. 1st 1 would be multi line comment identified when closing slashes and ass terrorists, while the 2nd 1 is line by line comment, which could easily be created by selecting the lines as you want to comment out on baiI clicking calm and tool from the toolbar. Also, there is a status far at the bottom of Korea Richard that displays a few important pieces off information, such as the server you are currently connected to. They use the name of the user that who is connected, the name of the data bees that you are running your queries against total time it took to bring back the results set of your quarry. Total number off records brought back by U Curry. Lastly, like any text editor, you may also see if the quarry in your query window as a file for later use. In my case, I'm using a query file that is saved in my file system. Thanks for watching. 15. Pre built DB - system database: in this lecture, you will get to know about system data basis that come with my MSs trails over installation . Out of the box. Ah, you push that red button. What have you done? Everything will self destruct in about 2.5 seconds. Countdown begins now. Ha ha. Just kidding. SQL Server comes with five special system databases that are critical to its own mundane ings and management system. Databases are master M S, D B model temp TV and resource. Microsoft does not recommend to curry directly the system tables Instead, system catalogue views should be used. There were applicable master database contains all of the system level information off SQL Server. It also records the existence off all other databases and the location of those database files and records. The initialization information for SQL Server SQL Server cannot start if the monster databases unabated unavailable In SQL server system, objects are no longer stored in the master database. Instead, they are stored in the resource database. M S devi databases used to support a number of technologies within SQL server, including their schools over Agent, SQL server Management studio database, male and service broker SQL Server automatically mundane, a complete on linebacker and restore history within tables in M S D B model databases used as the template for all databases created on an instance of SQL Server. Think off it as a cookie cutter whose shapes deter mines the sheep off all other cookies. The entire content of the model little babies, including database options, are copied to the new data bees. Some of the settings off model are also used for creating a new temp DVD Toby is doing start of, So the model database must always exist on our skills. Our system Temp Devi System Databases is a shared temporary storage of source used by a number of features of SQL Server and made available to all users. It holds temporary user objects that explicitly created, such as global or local temporary tables. Temporary stored procedures, table variables. Were cursors tempt DVD databases recreated every time a school, sir, was it started based on the settings of the modern database, so that the system always starts with a clean copy of the data. Bees back up and restore operations are not allowed on temp TV. Davies Resource databases, a hidden system data bees were system objects are stored. We saw State of Vista start continues or data or user meta data. The physical names of the source databases are M s SQL system resource dot MDF and M s SQL system Resource thought LDF which are located at this location, and they should not be moved. Thanks for watching. 16. Wrap up - what we went over !: miss completes third model of the cause. In this model, you saw what SQL Server Management Studio is. You also got to know how to get around Object Explorer and dig into some of the database objects you also saw. Quarry Editor from Very Human execute T SQL statements. Finally, you got to know about five system databases off SQL Server, which are Master and Miss Devi model Temp TB Resource. See you in next model. In the meanwhile, feel free to stretch out or grab a cup of coffee or tea or look after nated would contain some amount of antioxidants. Enjoy. Thanks for watching. 17. Bare Minimum : SELECTING a literal: In this lecture, you will see how the most rudimentary or basic form of D SQL query or select statement work by selecting a literal value. Oh, and what you see on the screen is our our helpful detective helping us untangle the mysteries off SQL Server. Also, you would notice that there is a skeleton off select statement or syntax diagram and by specifying the world select itself, you are telling SQL Server that you want to simply read information stored in the database as opposed to modifying it. As you progress with our course, we will flesh out a more advanced T SQL statement. But for now, if you would notice the skeleton of Select Statement, it follows a particular syntax convention without intimidating yourself of any perceived complexity of this index diagram. Let me show us to how you read the Given Syntex diagram. Apart from the word select, a select statement might contain other parts as well, which are called clauses, for example, from plus their claws. Grobe, a clause having plus order by clause and others. The words that you see in upper case are actually t SQL keywords. The words that you see in idyllic. Our user supplied parameters off T SQL syntax, the vertical bar or the pipe operators upbraids Syntex are items enclosed in brackets or braces. You can use only one of the items. Square brackets signify optionals index items. You do not need to dive the brackets when writing your query. Curly braces signify required syntax items you do not need to type. The curly Brace has been writing your career. The most basic form of select a statement that you can write an SQL server is to return are literal value by a literal value. I mean that part of the statement or expression that is used to be exactly as what it is without changing it in any shape. Off home. A character string literal that is a set off alphanumeric A to Z in upper caps or a to Z and Lord cap O. R. 0 to 9 numbers and special characters such as the exclamation mark. The ad at Symbol, The hash symbol. These characters need to be usually enclosed within single courts like this. On the other hand, you may use the numeric literal value without surrounding it within a pair of single codes . Let us go through these statements. The 1st 1 is a specifying a string literal, whereas the 2nd 1 is a numeric literal. If you highlight the lines and then either had execute button or press F five on your keyboard, SQL Server will execute the query. Here we have the result off the Korean the results. Time since I ran to set of statements, two results sets are displayed and had there been any error in our curry error, messages would have been shown in the message. Tam. See, that was easy without ever going inside any table. Our select a statement returned the literal values that we provided. Thanks for watching. 18. Retrieving / Querying data from a table - using FROM clause: in this lecture, you will see how to build upon our select statement in order to retrieve data from a given table. Or were you using from Klaus before we continue our exploration off SQL Select Statement, please make sure you are pointing to our sample database. That is Adventure Works 2012. If you do not find this database to be selected in the database combo box list, please do either of these things go ahead and select these lines of T SQL statement and execute. Or you may go to the calmer box itself and select the data bees. From there from Klaus is the part of the Cilic query that defines the place from which you data should come from. It may be a table or even a view. Let us consider an example where we want to retrieve Onley. A set of columns from amongst all available columns defined in the table object. In this example, you would notice that the column standard to be retrieved are defined as a comma separated list after the select keyword, and it is referred as to select list. So you like to bring back log in I d job title, birthdate location, hours sick leave others. Columns on Lee from the table named Employees that is contained inside the human resources schema. Hence, the table is being referred by its two part name, first part being the schema name that contains to table and the second part being the table name itself separated by a doctor. Go ahead and select the quarry and execute. It brings back 290 rules. Let us move on to the next Cory. Think of a situation when you do not care about the columns in the select list and would want to bring back all the columns of the table without having to spell each one of them. For this purpose, you may use and Esther Risk or the star symbol in the select list, and this will bring back each and every column of the table. A word of caution. This technique should only be used and an ad hoc situation, and you should not make a habit to use asterisk in your select list. Using asterisk in your select list degrades the performance off your query. Furthermore, without having columns explicitly spelled out and using an asterisk instead, you risk breaking the calling application if and when either the columns of reordered or a new column is added in the underlying table. Go ahead for experimentation sake. Renda Curry. And you'd notice that it brings back all 16 columns of employee table along with its 290 rose. - While we continue to flesh out our select statement, let me share our productivity tip with you. Did you know that in the Query Editor, instead of writing object names, you can drag and robbed objects needed? And that writes down its name in the editor. Please watch me as I build up a simple curry using that technique. - Thanks for watching. 19. Playing with Column Names: in this lecture, you will see what you can do with column names off the columns in your select list. SQL Server allows you to rename a column in your select list. By using what is known as an alias, you need to use the optional keyword e s as to a specify an alias. Even if you omit a ski word, it would still work. Let us go over an example. If you notice the curry for For the table column. Business Entity i. D. I have defined an alias off employee I D for column name, job title and alias off job space title and call him for and for the column, birthdate and alias off B o B. If I remove the keyword, as you would notice that the Cory were still, I put the alias instead of the original column name. This is because the as keyword is optional to use. Let us see what else we can do with columns in the select list. If you notice this quarry that I'm injecting a literal value along with other columns in the salad list, the phrase an excellent employee shows up for each role in the results that as an additional column named as comment, lastly, I would like to bring something to you attention, which is a common pitfall and might leave you wondering as why the results said is showing erratic column header header. If, due to any reason you forget to separate two columns in the select list with the comma, then the values of the first column is brought back. However, it's column heading Name is taken from the second column. If you notice their story, I have left after out a comma on purpose. And if you look closely at the results said you would see that values from the column job title have been brought back, However, the column heading is is still saying birth date all if it because off a missing comma Thanks for watching. 20. Rearranging columns to your liking: In this lecture, you will see how you can rearrange the order of columns in the select list. If you notice the curry, you would see that I'm bringing the column gender Before the Marital Status column. You may easily rearranged the order of appearance of columns and you results that by simply using the desired order of columns in the select list itself, Thanks for watching. 21. Wrap up - what we went over !: this completes fourth module of the course. In this model, you saw how to construct a bare minimum select statement utilizing laterals. Then you saw as to how to retrieve data from a given table using from Klaus. Then we played with column names by using column aliases and by even injecting literal values in the column list. Also, you saw how to rearrange the order of column names. Depending on your business needs. See you in next model. In the meanwhile, feel free to stretch out, grab a cup of coffee or tea. Although caffeinated, both contain some amount of antioxidants. Enjoy. Thanks for watching. 22. Narrowing down and ordering result set - WHERE and ORDER clause: in this lecture, you will see how to use their claws in order to filter out on one to data. And you would also see how to use order by clause in orderto order. These all set based on the columns that you'd like to order upon before we delve into their and order by clause is off. Excuse select statement. Let me introduce you to an important concept called predicates. Relational database is heavily dependent off predicate logic, and a predicate is a statement or expression that either ever evaluates to true or falls or unknown. If you notice in the given example, expression one equals one evaluates to true, because one is indeed equal to one. On the other hand, in case of example of expression, one equals five, we can clearly see that this expression should evil way to false as one cannot be equal to five. For the sake of this lecture, we will focus on Lee on comparison predicates that utilize thes arithmetic operators, which are is the operator used to test the equality between two expressions. Is the operator used to test the condition of two expressions not being equal to each other ? Does the operator used to just the condition of two expressions not being equal to each other Is the operator used to test the condition of one expression being greater than the other? Is the operator used to test the condition off? One expression being greater than or equal to the other expression was the operator used to test the condition off. One expression not being greater than the other expression is the operator used to test the condition of one expression being less than the other. Is the operator used to test the condition off one expression being less than or equal to the other expression. There's operator used to just the condition of one expression not being less than the other expression. Apart from the arithmetic operators, you also need to be made of air off. Boolean logic operators reach our not and and they act upon any valid boolean expression and return either true or falls or unknown. Let us go over the tables that you see to get a clear picture of these logical operators. It has discussed the table for not operator first. This is how you would read the table if you apply, not operator on a Boolean expression that had evaluated to be true. The final result would be a false if you apply, not operator on Boolean expression that had evaluated to be false. The final result would be a true if you apply, not operator on a Boolean expression that had evaluated to be unknown. The finally reserved would be an unknown. This is how you read the end or Prater table. If you end to willing expressions that both evaluated to true, then the resultant would be true as well. If you end to Boolean expressions, we're either the expression evaluated to true, while the other one invalidated to be false, you will all always get false as the resultant you read the table for all operator in a similar fashion, the concept of operator precedence is something that needs to be kept in in your mind as well. In a situation when there are more than one operators in play in an expression operator, president, presidents deter mines. The sequence in which operations are to be performed when two operators in an expression have the same operator level precedents. They are evaluated, left to right, based on the position and expression. Furthermore, you may use parentheses to define your own president's order for operators, and everything within the Prentice's is evaluated first to yield a single value, and that value can be used by any operator outside the parentheses. You do not need to memorize the stable. This is just for the sake off reference. And don't forget to remember that a predicate may be used in any place off the select a statement. There are Boolean value is lead ID. Okay, enough with the predicates for now, and let us continue our discussion with the where. Clause off select statement. Let us jog up over memory. Go back to, ah, simple query from our previous module. Very attempt to bring back all rules off the table by specifying its column names in the select lis. The example. Curry you'll see on your screen screen is bringing back all column of the table employees, and it's easy in every role, which, in case it turns out to be 290 in count. Now consider some of the following business requirements off your report that might be required at your organization list. Employee I D job title and birth date off. Only male employees missed employee I D Job title. Higher date. Marital status off on Lee. Those employees that have accrued vacation RVers off more than 40 hours did you notice? In these business requirements, we not only have to restrict the columns that are being brought back, rather, we need to filter based on a given criteria and show on the doors ALS that meet that criterion. This is exactly where are rare. Clause comes to our rescue. From now on, I will first rate the problem statement or ah, hypothetical business requirement and go over the quarry that solves the problem. Your objective should be to break the problem statement into smaller parts, and your thought process should be something along these lines. Which table contains the data? What columns and with what alias, if any, are needed from the table. What should be the order of appearance of columns in the select list, where the set of criteria that needs to be used in our where Klaus, in order to filter out unwanted rose from our results, said. With this in mind, let us go to our first problem. Statement. Louisville first schoolwork worries that need to filter out unwanted rose based on a literal value lead a numeric string or a date value. Okay, this problem states that producer report Displaying columns. Employee I d Log in i d organization level and job title of those employees who belonged Organization Level of four. Go ahead and execute decree. The curry brings back on Lee 190 rolls out off its total 209 deals Moving on to this second problem it s states to produce a report displaying columns. Employee I d log in i d organization level and job title of those employees who are accountants, that is their job Title is accountant. Go ahead and execute a hurry the Korea brings back on the tools out of its total of 290 years. Next problem. Produce a report displaying columns, employee I d Log in i. D job title and date of hire off those employees who were hired on 1st July 2006 and from the result of Executor Curry, it is evident that only two sales representatives were hired on that particular date. Let us see the use of comparison operators problem states produce a report displaying columns, employee I d Log in i D Job title and accumulated vacation. RVers off those employees who have accumulated vacation hours equal to and greater than 40 hours. And after running the curry, it appears that as many as 181 employees have accumulated vacation hours totalling 40 hours or more. Next problem. Produce a report. This displaying columns. Employee I d. Log in i. D job title and date of hiring off those employees who had been hired on or before Burti January off 2003. After running the query, it turns out that out of 290 employees on Lee 80 Mido filter Criterion. Now let us move on to ordering returned results set using order by clause, you order the result Set off a curry by specifying a list of columns separated the comma in your order by clause, the default sore direction is ascending. While you may explicitly specified the sort direction by using either the keywords in your claws E s c o the world ascending itself R d E S c or the word descending itself. Please also keep in mind that null values soared to the top of results that you may also sort the results said on columns that are not in the select list of the curry. Furthermore, in your order by clause, you must simply mention orginal numbers of columns and you select list instead of a spelling out the column name, and it will still work. Fine. Let us cool with some of the examples problem. Sade's produce a report displaying columns. Employee I D. Log in i. D job title and date of hiring off those employees who had been hired on or before 30 January of 2003 and have those else set ordered by hiring date in an ascending order. If you noticed a curry, I have not specified any sore direction. And so it takes ascending sore direction by default. Look at the results. Set notice how hired eight columns value progress from the year 2000 till 30 January of 2003. For the modus. Also returned 80 year olds identical to problem five. Curry As we implemented only a sort order No Cory and the filtering criterion was left unchanged, moving on to the next problem. The problem of states to produce a report displaying columns employee I d. Log in i d Job title and accumulated vacation. RVers of those employees who have accumulated vacation hours equal to and greater than 40 hours and have those all set ordered on location. RVERS COLUMN In a descending water, notice how the keyword off D E. E S C is used in order by clause and from the results set, it is evident that only three employees have accumulated vacation hours off 99 hours, one being the CEO of the company itself and the other two being production technician. Moving on to the next problem problem. Steeds produce a report displaying columns, employee I, D Log and I D Job title and date of hire off all employees, beginning with the oldest employee forced. If you may notice the curry, I'm not using a very clause at all because the problem misty Problem statement states to look into all employees. Furthermore, I'm ordering based on birth. Date column, which was not included in the results set itself from the results, said it is evident that the oldest employee is a North American sales manager, but employee i d 274 moving on to the next problem. Produce a report displaying columns. Employee I d Log in i. D Job title end date of hire off all employees Ordering results said based on employee I D . In descending manner. If you notice the curry, I have a specified the orginal position of the column, as opposed to explicitly naming it in the Lord of a Clock. Thanks for watching. 23. Narrowing down result set - BETWEEN / NOT BETWEEN: In this lecture, you will see how to use between and not between, or graters. In order to filter out unwanted data, Metas once again jog up over memory and go back to a simple query from our previous model. Very attempt to bring back all rows of the table by specifying its column names in the select list. The example Curry you see on your screen is bringing back all columns of table employees, and it's each and every roll, which in this case it turns out to be 290 income. However, bringing back entire set off rose off a table is rarely required. More often than not, we are looking for a specific set of rules or even a single rule that satisfy a given set of criteria. And hence the need to filter out unwanted data between operator is used in scenarios where you would want bring back rose between and inclusive off a range of values you need to provide the minimum value and maximum value off the range of values. Separated biological and operator between operator may be used with dates, numbers as well as a string. With this in mind, let us go through a first problem statement. The problem states. Producer Report Displaying columns. Employee I D. Large and I D Organization Level and job title off Onley Doors. Employees Having Employee I. D. Values between one and 10. Go ahead and execute Hickory, and you would notice that it brings back 10 rolls off employees. Having employees ideas is starting with one and ending with 10 because remember, between operator are inclusive off the bond revalues. But what if if you wanted to exclude the boundary values and are interested in seeing employees I DS to do nine 1 May. To achieve this would be to use the simple, greater than and less than comparison operator. Go ahead and execute burglary and notice that it brings back on the eight rows showing employee I. D. S from 2 to 9, as discussed earlier between operator is inclusive of the boundary values. It may be simulate assimilated by using greater than equal to and less than an equal to comparison operators. Instead, go ahead and execute hickory, and you would notice that it brings back identical results said to the one brought back by Corey. One of this problem statement let us. Now move on to applying between operator on daytime columns. The Problem. Estate Produce a report displaying columns, Employee I D. Log in i D organization level job title and date of hire off. Only those employees, which were hired between 18 May 2006 and 1st July of 2007 order the result set by hiring date in a descending fashion. So if you notice the curry, you would see the range of date values are specified exactly in the format off their storage in the table for hire, date column and the date the trolls are enclosed within a pair of single coats, please know that we will be diving into more details about daytime values in an upcoming lecture. Please be patient. Go ahead and execute the Cory and it brings back seven rows. And it appears that on first off July 2007 two sales representatives were hired. Let us go over Problem three, where the requirement dictates us to apply between operator on character value before going ahead, please, coming out the very closet, the Cory and execute degree. No, the job title started. Start with C and with E. You will notice that chief executive officer is the first job title is starting with a C. They're as engineering manager is the first job title starting with an E. So would you expect that the values of chief executive officer and engineering manager satisfy our boundary conditions? If that is the case, you're unfortunately wrong, since ah, value off engineering manager will always be greater than an E because the job title off engineering manager starts with E and at least one additional character. So in other results, set engineering manager and onwards would not show up at all. Go ahead and uncommon the very close of the Cory and executed, and you would notice that it brings back 12 rules, and in it our chief executive officer is there. However, engineering manager is not present at all. A detailed discussion about working with strength is in in an upcoming lecture. Please be patient now let us see as to what are not between Operator is, as the name suggests, it does exactly the opposite of the between operator and looking for a range of values and brings back on Lee those rules which do not satisfy the condition as a specified in the between operator. Simply put, it finds values outside range of values a specified in your bear claws. Let's go over a problem. The problem. Misdeeds could use a report displaying columns. Employee I D. Log in i. D. Organization level and job title off only those employees having employee I D. Values not between one and 10. Go ahead and execute Hickory and notice that brings back 280 rules out of the to under 90 year olds in the table on employee I D starts with 11 and ends in 290. This would be an appropriate time to bring it to your attention, a common mistake that you might be prone to repeat. This is something I call Ah, gotcha moment. Please go over this curry and do you find anything out of place? Notice how the minimum and maximum value of the range off employees ideas got switched erroneously. So in a way that Korea's asking to bring those rules. Having employees I d less than equal to 10 and greater than equal to one. And on the top of the condition on negation that is not operator is applied since nor rules match the condition and because of the not being applied on top of the criterion, each and every rolls off the table would be returned. Go ahead and execute Degory, and you will get each and every roll off the table back. Totalling 290 year olds. Consider yourself warned. Thanks for watching. 24. Narrowing down result set - IN and NOT IN operator: In this lecture, you will see how to narrow down results, set beast on a list of values using in and not in operators as opposed to using a range of values. As you saw it between operator, Let us once again jog up our memory and go back to a simple Corey from our previous module , maybe attempt to bring back all rules off the table by specifying its column names in the select list. The example Corey, you see on your screen is bringing back all column of the table employees, and it's each and every roll, which in this case, it turns out to be 290. In count, However, bringing back entire set off roads off a table is rarely required. More often than not, we are looking for a specific set of rules or even a single roll that satisfy a given set of criteria. And hence the need to filter out unwanted data in operator is used in scenarios where you would want to bring back rows matching a specific list of values as opposed to ah, range of values. You may easily right ASU ease off, all predicated instead, off using in operator. However, the latter offers better readability and meant ability. Maintain ability off court. You may also use a sub curry with in operator. More details on sub query Coming up with this in mind, let us go through our first problem statement. The problem. States produce a report displaying columns. Employee I D Large and I D Organization level and job title of only those employees having employee I D values either three or five or seven. Go ahead and execute a Cory, and you would notice that it brings back three worlds of employees having employees I ds, we or five or seven. It is also to be mentioned here that you may rewrite the same Cory using or predicates instead of using in operator, and you would still get the same results said Go ahead and execute a curry and notice that it brings back three wars of employees having employees 35 and seven, not an operator, does exactly the opposite of the in operator and brings back rows having values other than those you list. - Go ahead and execute the cory and notice that it brings back 287 rows of employees out of 290 years. As is the case with the in operator, you may rewrite the score using not and all operators instead of not in operator, and you would still get the same results set. Thanks for watching. 25. Narrowing down result set - Pattern Matching : LIKE and Wildcards: In this lecture, you will see how to narrow down results set using, like, predicated, that gives you pattern matching capabilities. Well, coring, our SQL Server database. You will also see how to construct why cards which are set of characters. That trip is in the pattern you are looking for in you is all said. Okay, here we go again. I promised this type of example would be Xun. For the last time, let us once again jog up our memory and go over a simple gory where we attempt to bring back all rows of the table by specifying its column names in the select list. The example, Corey, you see on your screen is bringing back each and every roll off the table person, which in this case, it turns out to be 19,972 in count. However, bringing back entire set off rules off the table is rarely required. More often than not, we are looking for a specific set of rose or even a single roll that satisfy a given set of criteria and hence the need to filter out unwanted data like predicated is used in scenarios were, you know, only some part off value that you want your leader to be matched upon. In this case, you would need to use like keyword instead off opting for equality or any other operator y E cards. You may try to look for pattern based upon regular characters, in which case we grow. Characters must exactly match the characters specified in the character strength. However, while you guard characters that you construct, using the wildcard characters as shown in the stable can be mashed with arbitrary fragments of the character string, offering you more flexibility for filtering out your data. SQL Server offers four types of wild cards, which are the person signed. This represents any string of zero or more characters. The Under a school. This represents any single character, a list of characters enclosed within square brackets. This represents any single character within the specified range. For example, it will see all in lower caps or a set, for example, a B C. A list of characters enclosed within square brackets and preceded by a carrot symbol. This represents any single character not within the specified range or said with these in mind, let a let us go through our first problem statement. The problem is steeds Producer report displaying columns title first name, middle name and last name off. Only those people having their last name beginning with E L and have the results that ordered by last name in an ascending manner. If you notice the Cory, you will see that we are trying to match last name column toe a spring e l followed by your percent sign, Go ahead and execute Hickory and you would notice that it brings back 423 wards off persons having their last name beginning with a l moving on to the next problem. The problem States Producer report displaying columns title first name, middle name and last name off. Only those people making sure that their last name must have at least one Z and have those all set ordered by last name in an ascending manner. Go ahead and execute a Cory and you'll notice that it brings back 3247 rows off persons having at least one Z in their last name. Next problem. The problem States Producer report displaying columns, title, first name, middle name and last name off only doors. People have having their last name, not beginning with a L. And have the results said ordered by last name in an ascending matter. Go ahead and execute degree and you'll notice that it brings back 19,549 rose off persons having their last name, not beginning with a L moving on to next problem. The problem states produce a report displaying columns, title, first name, middle name and last name off. Only those people who have their last name beginning with B E. R. R and followed exactly by any single character and having have the results said, ordered by last name in an ascending manner. Go ahead and execute the curry. And you would notice that it brings back three wards off persons where their last name beginning Mitt B E. R. And followed exactly by any single character moving on to the next problem. The problem. Estate Producer Report Displaying columns, title, first name, middle name and last name off Ali does people where the last name beginning that B R and followed by either and a for an E and then followed by any number of characters and have the results that ordered by last name in an ascending manner. Go ahead and execute the Cory, and you would notice that it brings back six rules off person who have their last name beginning with B R and followed by either an A or an E and then followed by any any number of characters moving on to the next problem. The problem Estates. I would use the report displaying column title, first name, middle name and last name off. Only those people who have their last name beginning with B and followed by an letter ranging from A to Z and then followed by any number of characters and have the results that ordered by last name in an ascending manner. Go ahead and execute the query and you would notice that it brings back 1205 rose off persons who have their last name beginning with B and followed by any letter ranging from A to Z and then followed by any number of characters. If you look closely in effect, the Koreas bringing any person back whose last name begins with the letter B going on to the next problem. The problem estates could use a report displaying columns, title, forest name, middle name and last name off. All of those people who have their last name begging that B e. R, followed by any letter except our and then followed by any number of characters and have the results said, ordered by last name in an ascending manner. Go ahead and execute the Cory and you would notice that it brings back 12 rolls off persons . We have the last name beginning with B E E R, followed by any letter except our and then followed by any number of characters. Notice how the carrot symbol negates the criterion. Thanks for watching. 26. Wrap up - what we went over !: this completes fifth model of the cause. In this model, you saw how to narrow down results. Set off your select statement, utilizing the following their claws between not between operator in not in operator like predicated and wild cards characters. You also saw how to order you, Ezell said. Using or obey clause with sort direction, either ascending or descending. See you in next model in the meanwhile, flee free to stretch out or grab a cup of coffee or tea or look after needed. Both contains some amount of antioxidants. Enjoy. Thanks for watching. 27. Dealing with nothing : NULL: in this lecture, you will see what annul well you is. You will also see how to test the prisons or absence off null values. Using is no and is not know operators, respectively. Lastly, you will see how to substitute value. You provide Fornell values. Using that is no function. Anel value signifies the absence of data that is, data that is missing or unknown. It should never be confused with empty string or zero therefore, human artista value for its normal ability by using an equality off. Greater for this purpose, SQL Server provides you is no and is not know operators in order to test existence off anel value for absence over no value, respectively. Furthermore, please keep this in mind that result of any operator, for example, plus operator, minus operator and operator or operator, and others when either or print is no, always evaluates to now. For example, if you add 25 to annul, you'll still get now if you or annul with the true, you'll still end up with now. Likewise, if you are null with the falls, you still get another. SQL server provides you three functions to handle null values in columns is no function validates whether an expression is now. If yes, it replaces the Null Valley with an alternate value that you provide. Choral esque function returns the first non null value from a provided list. Off expressions not only function, it turns an L value when the two provided expansion expressions have the same value. Otherwise, the first expression is returned. Okay, with these in mind letters, go to our first example. Corey. The example Corey you see on your screen is bringing back all 19,972 rows off the table person. And if you notice the field, middle name contains both values as well as notes. Moving on to our first problem. The problem States produced a report displaying columns, Titan, first name, middle name and last name off Ali. Those people having their middle name as nails and have the results said, ordered by last name in an ascending manner, go ahead and execute Hickory, and you would notice that it brings back 8419. 99 rose off persons with their middle name as knows, moving on to our next problem. The problem states produced a report displaying columns title, first name, middle name and last name folly. Those people having their middle name as not Nel's and have the result set ordered by last name in an ascending manner. Go ahead and execute a curry, and you would notice that it brings back 11,473 rules off persons with their middle middle name as not notes. Moving on to our next problem. The problem States produced a report displaying columns, title, first name, middle name and last name. And make sure that for Rose that have a value in Middle name column you displayed as it is , while in cases off no middle name, you displayed a spring no middle name and also have the results that ordered by last name in an ascending manner. Go ahead and execute the Cory and you'd notice. That brings back all 19,972 rules off persons displaying middle name wherever available while displaying the string message off. No middle name in case off null middle names. Thanks for watching 28. Working with strings : Concatenation and other string functions: In this lecture, you will see how you can work with strings were using different string functions that SQL Server offers. I will begin the school lecture with an important concept of concatenation. Concatenation is nothing but to add up two or more pieces of the strings together, I will briefly go over the foreign concept. Can Captain ating strings using the plus operator can coordinating strings with null values , congratulating springs with other data types, such as a number using con cat function as introduced in SQL Server 2012 for Can Captain Ating Strings let us go over a simple example where I can Katyn eight to string laterals using the plus operator the problem states Producer Report displaying columns Business Entity I D and full name off All persons construct the full name for each person using the convention off last name comma and then the first name, as he noticed in the results, said full name column shows last name and first name separated by a comma for every rule. Let us see what happens when we try to can chitin eat Ah, string literal with a null as you made a call from an earlier lecture. When the any of the operation is annul, the expression always evaluates to novel. Hence, the result of the curry is not as well moving on to our next problem. The problem states produce a report displaying columns business NTT I D and full name off. All persons construct the full name of each person using the convention off First name, space, middle name space last team Also keep this in mind that middle name is unsalable and might have no values. Go ahead and run the curry and in the results said you'd notice that since we are using, that is no function. Ah, blank space value is substituted for any null in middle name column So in the results said no notes are shown at all. Okay, let us ponder over a question. What do you need to do to use numbers stored as a string in the table and perform arithmetic operations on it? Well, in that case, you attempt to convert the string into a numeric data type. This is done by either of the following two functions Cost function, convert function metas gore were an example. The problem is to eat producer report displaying a single column called Idee Under School. Full name off All persons Construct a column idee underscore full name off each person using the convention off business NTT I d. Underscore for its name. Underscore Middle name under school. Last name. Also, keep this in mind that middle name is now level and might have no values. And furthermore, for this entity, I D. Values are stored as numbers and not strings. Go ahead and run the quarry, and you would notice that with help off cast spoken word functions, the values in Business Entity I D column are first converted into strings and then contaminated with the rest of the string. As for the requirement now , let us go over Can cat function as introduced in SQL Server 2012 for congratulating springs . Please keep the following in mind regarding Can cat function. It takes a variable number of a string argument and can catch needs them into a single string. It requires a minimum of two input values. Otherwise an error is raised. All arguments are implicitly converted to string types and then can caffeinated, so no need forecast. Organ word function. Null values are implicitly converted to an empty string. So no worry about using is no function to take care off nails. Go ahead and execute the Cory and you. You see that was easy. Breezy and its output is what we wanted. Moving on to some other string functions. L Trim function removes any black space from the left side of this string, and our trim function removes any blank speeds from the right side of the string. Go ahead and execute the calories, and you would notice that leading and trailing blank spaces are discarded, left and right functions. These functions return a specified number of characters on the left or right side off a strength. Go ahead and execute degrees and you notice that in the first quarry you get the first re characterise off the string literal, while in the second quarry the last three characters of the strange trip moving on to our next function L E N function. This function returns the number of characters in a string. Let's score Ah ah problem. The problem states produced a report displaying columns business anti D i d. Last name and leant off last name off. All persons go ahead and execute the Cory, and you'd notice that the last column issuing the number of characters as contained in the last name field for every rule moving on to our another string function called care Index function or char Index function, However you want to pronounce it, this function returns the numeric starting position off our search string inside another string in case off no match it returns. Zero. The function may be instructed to ignore. Are given number of characters at the beginning on the string to be searched. Leah. An optional parameter off a start location. Let's score a problem. The problem states produce a report displaying columns. Business Entity I. D. Last name and position off letter Z in last name off. All persons go ahead and execute the hurry. And you would notice that in the last column, a number denoting the position of the letter Z in the Last name column is shown moving on to another string function called Sub String. This function returns a portion off a string siding at a given position and for a specified number of characters, go ahead and execute the quarry and you would notice that it brings back the fragment of string off lent off two characters, starting from the first character in the original string, which in this case turns out to be J. O. Joe moving on to our another string function. Upper and lower functions. These functions returned either or lower case or all uppercase off. Given a string, go ahead and execute the Curries and you would notice that on applying these functions on a mixed case string literal you get, you would get a result that is, either all upper case or lower case. Later. School were another string function called Replace Function. This function is used to substitute one string value for another. Go ahead and execute the quarries and you would notice that on applying this function on the given string literal, you would get a result where blank spaces substituted with an underscore. Thanks for watching 29. Working with dates : Date Functions: In this lecture, you will see how to utilize some of the common and frequently used date functions. You will also see as to how to convert a daytime value in tow. A string with a special formatting or style applied to it. Get date and system date time functions. Please note that both functions return current date and time off your server. However, system date time functions offers more degree of precision. Go ahead and execute the cory and compared the results, and you would notice that the output of system daytime function is more precise. That is go ah date function. The function is date add function. The function is used to add a number off time units to a given date. The number off time units that human add to a given date is year, month, our minute and others for complete details. Please refer to this table. Let his girl were a problem. The problem states produced a report displaying employee I D. Job title, higher date and two additional columns. First column showing the date After six months off fire and the second column showing the deed off one year after hiring, go ahead and execute hickory, and you'd notice as to how six months have been added to the higher date column and one year added to the high date column for each and every roll off the table. Date Def function. The function allows you to find the difference between two dates. Function requires three parameters. The date part and the two dates later school were a problem. Problem. Estates Producer Report Displaying employee I D Job title, higher date and two additional columns. First column showing the age of the employees as off today in Once and the second column showing the employees age in years. As of today, go ahead and execute the Cory and you would notice that the two additional columns first showing the age of employees in months as of today, because off using get date function on the second column shows age in years at the time off running the curry, moving on to other date functions, date, name and date. Part functions. These functions returned the part of the data specified in case off date name function. It returns the actual name off the date part. While the date part function returns on numerous value, - go ahead and execute the curry, and you would notice that two additional columns 1st 1 showing the month number of hiring date. Often employees while the second column shows name of the month when that employee was hired. Cohen Ward Function. We have already seen the use of this function when he wanted to concoct innate a number and string together. This function must use to convert a number data type into a strength along the same line. This function could be used to convert a data type into a spring. It has an optional parameter called style that can be used to format a date if you notice the table office styles, more commonly used ones are 11 for the U. S. A style of date values, while one or three is the British or French style of date values. - Go ahead and execute the Cory and notice the difference between us a style off writing, a date value and the British style of writing a deed value. Thanks for watching 30. Some System Functions: without going through an exhaustive list of system functions, you would be introduced only one system function, namely case function. The case function evaluates a list of conditions and returns one off multiple possible result expressions. There are two variations off a case expression relation. One. They that is a simple case. Here you come up with a simple expression that you want to evaluate off in a column name and a list off possible values. So if you're using a case expression with a column in your select quarry, you would be essentially mapping one set of defined values into another variation, too. Searched case. The searched case expression evaluates a set off Boolean expressions to deter mined. The result. Let a school were a problem. The problem states. Producer Report displaying employee I D. Job title gender. Please know that the column Gender contains M for male F for female, please make sure that you display the values engender as when an M is found display meal. When efforts found display female and for the remaining values display unknown. Also, make sure to alias the Gender column and and name it as elaborated. Gender. Go ahead and execute the cory and examine the results set. Let us go over another problem. The problem States Producer report displaying title Engender off all persons please know that the column title contains various types of values. Please make sure that you display the values engender as when either of these m s dot M R s dot or miss are found half the gender shown as fee meal for that role. While if m r dot is found in title than show gender as male for that role and for all other values of title, please display unknown in the Gender column. For those rules, - go ahead and execute the curry and examine the results Set. Thanks for watching. 31. Wrap up - what we went over !: this complete six model of the course in this model, you saw how to deviate null values. You also saw how to use is no function. Then you saw how to work with strings thanks like in coordination and utilizing other string functions such as contact function. L dream our dream functions left right functions l e n function char index function, sub string function, upper lower functions and replace function. You also saw how to use some of the date functions such as get get date system date time functions day dad function beat def function date, name, date, part functions using convert function with the style parameters. Finally, you also saw how to use case function and its two variations them being simple case and searched case. See you in next model In the meanwhile, feel free to stretch out or grab a cup of coffee or tea. Although caffeinated would contain some amount of antioxidants. Enjoy. Thanks for watching 32. Joining multiple tables - INNER JOIN: in prior lectures, you have been mostly writing Cory's that fetched rose from a single table. But what if the requirement is asking to bring back columns that are not limited to one table? Rather, they are spread across two tables, or even more than that, then that in such a situation, you'll be joining tables using Join Clause based on adjoining column. The circle on your left represents table seals order header, while the one on your right represents table sales order detail, As you can see and inner Dwyane Onley returns records that have matches in both tables. When you join two tables with inner join on Lee, the rose from the tables that match on the joining columns will show up up in the results. In order to join two or more tables, you should be a very as to how and on what columns they are related. That is their primary key and foreign key relationship in the given syntax noticed that the key word you know is optionally needs you, as signified by enclosing square brackets. And Corey would return on Lee those rows that have a match in both tables on the given, joining column. Also, please note that often the tables being quarried are given an alias using the as keyword and the select list contains columns coming from either of the joint tables but fully qualified names by pro fixing. The column names with the alias of the table that it is coming from, followed by your dot. Before we begin to see examples of energy wine, let me here with you my thought process when attempt to write a curry that involves joining one or more tables together when confronting a problem where join is needed, you might think along the following lines to come up with your solution, what columns are needed and in which tables. Those columns Rasyid Are those tables related and on what columns? That is. What is their primary key foreign key relationship? Okay, with these in mind, let us score some problems. The problem. States Producer Report that displays order. I d ordered eight Total Do you sales order detail. I D product I. D and ordered quantity. Please keep this in mind. That sales order header is the player and table and sales order detail is the child table. And for each sales order i d in the sales order headed table that is its its primary key. There might be more than one rose in the sales order detail table representing each line items off the given order that is one to many relationship before we attempt to write the actual Caray joining the tables letters do some exploration off the sales order header and sayings order detail tables. Go ahead and run these quarries and you would notice. Sales order header table returns a total off 31,465 rows while sales order detailed table returns a total off 121,000 and 317 rules. Total number of rows off board tables is an indication that a forgiven role in sales order Heather Table he might have more than one row in sales order. Detail table. Okay, now later school with a curry that solves our problem. Our left table is sales or a header table and write table Is sales already deal table. Both tables are being joined by using energy, wine, keyword and the columns on which they are being joined. Our sales order I d off board the tables also, you would notice that the tables have an alias defined and alias, followed by a daughter used to Qualify column names in the select list, signifying the table from which they are coming from. Go ahead and execute the curry and notice the number off roads returned. It is 121,317 rules, which is each and every rule in the sales order. Detailed table telling us that for every seals order, detail role or the line item off that order, they must be in order in its very in table of sales order. Head it'd from our last example. It should not be confused that the column name must match for joint toe a couple in two tables. There might be cases where, although column names are different, there exists of relationship by having a foreign key constraint defined. Let a school where Another problem. The problem states produce a report that displays customer i d. Person i d Business Entity I D and last name off all customers. Please keep this in mind that a customer is also a person, and the table sales got customer has a foreign key defined on its person. I D column that is referencing to the primary key column Business entity I d off the person table. So if you read the problem statement, it is evident that every customer in our system also happens to be a person. However, not every person is necessarily a customer. Let us go and explore the underlying tables. That is, sales got customer and person dot person go ahead and run these quarries and noticed the number of rows returned for person table. A total of 19,972 wars were returned, while for the customer table, a total of 19,008 and 20 was very turned, once again validating the proposition that not every person is a customer. Let us go over the quarry that salts are problem. And here you would notice that join is happening on a person i D. Column off the customer table and business entity I D. Column off the person table. Go ahead and execute the curry and you would notice that a total number of rows returned are 19,119 which is less than both that with a number of roads in customer table as well as the person table. This tells us that the results said, as is an intersection off rose crumble tables and must be an exact match in both of the tables in order to be included in the final whistle set that you get. Thanks for watching. 33. Join types - OUTER JOIN - LEFT / RIGHT / FULL: in this lecture, you will see how to use our two joins to bring back rose from one of the tables, even if the other does not contain a match For every rule, you will see how a left outer join works. If you may notice that the separate circles represent rose from customer and sales order header tables. In case off left outrage wine, we attempt to bring back all rose from the left table and only the matching rules from the right table. In other words, we are making the satisfaction off a given join criterion optional for the left table. If you notice as to how the circles that is, the result set of the left out rejoin is represented pictorially. You'd realize that in case off our two tables customer being the left table and sales order header being the right table, I left out. Rejoin Cory would return all customers whether they have blazed orders or not. You will also see how right outer join works once again if you may notice that the Serpent circle represents rules from customer and sales order header tables. However, our main table that is the customer table is on your right side. This time, in case off right out of wine, we attempt to bring back all rose from the right mean table and only the matching rose from the left table. In other words, we're making the satisfaction off a given enjoying criterion optional for the right main table. If you notice as to how the circles that is the result set of right out or is represented to really, you would realize that in case off our two tables, customer being the right mean table and sales order being the left table are right out to join. Curry would return the same results set. That is, all customers, Whether they have placed orders or not, there is a special use off outrage wine that I I would like to bring your attention to. What if you need to find all the roads in one table that do not have a corresponding rose in the other table? Once again, if you may notice that the separate circles represent rose from customer and sales order headed tables with our main table, that is the customer table being on your left side this time, For example, what if you need to find all customers who have never placed in order. You may do so for matching for null values in the sales order header table while joining the team two tables together. Finally, you may all to do a full outrage. Wine were all rose from the left side of join. Even if there is not a match and all rules from the right side, even if there is not a match shows up in the results, said Okay. With these in mind, let us go. Over the first example, the problem states produced a report that displays customer I D Sales or I D and ordered eight feels and bring back all customers regardless of them placing in order or not. If you notice the cory, you would see that the customer table is the mean table on the left side of join. As a result, we end up with all those customers regardless, if they have placed in order or not notice how for some customer i d. There are null values in sales alrighty and ordered eight columns indicating that customers with those customer I d are in the system. However, there they have not placed any orders yet. Moving on to next problem. The problem states produced a report that displays customer I d sales alrighty and ordered eight feels and bring back on on Lee those customers who have never placed in order. This is a slight new twist on our last problem. Notice as to how in the quarry we are excluding Rose from our lot right table by making sure that sales order idea is now, go ahead and execute the curry and you will get rose off only those customers who have never placed in any order moving on to next problem. The problem states produce a report that displays customer I d sales alrighty and order date fields and bring back all customers regardless of them placing an order or not, if you may notice, this is the exact Seamus problem three except that we will use right out. We're going to solve it. Go ahead and execute a curry and you will get an identical set off rose off all customers, regardless, who have placed in order or not. Thanks for watching 34. Join types - CROSS JOIN: In this lecture, you will see how to use cross join to produce all possible combinations of rose from both tables. Essentially across join is adjoined, with no joint conditions specified. And the final results said that you get from crosswind may be termed as a Cartesian product . Off the to set off rose coming from both tables, let a school were an example. The problem states produce report showing country region code, State province code tax type and tax frayed Please generate all possible combination of rose from tables for Acindar State province and seal. Start seeing stacks read before we attempt to write a quarry for the problem. Let us explore the tables involved. Go ahead and run these quarries and no down the total number of rows in each table. Now run across join quarry and hair. You would notice that the total number of rows returned are 5249 which is a multiple off 181 and 29. The number of rows of both tables in wild individually Thanks for watching 35. Holy UNION !: in prior lectures, you had seen how to bring back results sets from different tables and stitching them together in the middle, based on some join condition based on a common column, However, there are situations where you would want to stack vertically. Whistle set of one quarry on top of the results set off another. Cory, this is very you would be needing Union operator. Okay, with these in mind, let us go over first problem. The problem. States produced a report that displays a list of last names off all employees and sales person notice here that an employee is a person, so he's a salesperson. Before we execute the curry in its entirety, let us execute the two quarries separately that are joined by a union all operator. So we have 290 employees in our system whose last name shows up, and there are 17 sales persons in our system whose last name shows up. Go ahead and execute the entire quarry. You would notice that a total of 307 rows are returned, which is a some off the number of rows coming from each of the quarry individually. Also, you may notice that some of the last names, such as a bus, is duplicated. This is what union all operator does. It does not hear about duplicates. It's simply stacks. The results sits on top of one another, made a school where another problem. The problem is steeds produce a report that displays a list off last names off all employees and sales person. And make sure that no duplicate last name is shown. If you may notice, we're not using all keyword in the union operator, thereby indicating SQL server toe. Eliminate duplicate records in our final results. Set. Go ahead and execute the query and your notice that only distinct last names are returned. And now we have only one a bus. Thanks for watching. 36. A bite of Sub…….queries: In this lecture, you will see how to use nested sub Caries in the where clause, in order to filter out unwanted rose, a nested sub Khoury, simply put, is a quarry within another query. Please keep this in mind that there is another kind of sub Corey called core related sub Khoury, which is beyond the scope of this course. Okay, with these in mind, let us go over some of the examples. The problem states produce Report showing custom i. D. Account number on Ledo's customers who have pleased orders. Please write the quarry utilizing a sub quarry in an enlist instead of doing joins. As you may notice, that in the Korea nested in inquiry is being used to bring a list of customer ID's, which in turn are to be used by the main quarry using in operator. Go ahead and execute the curry, and you will get only those customers who have placed in order moving on to next problem. The problem. Misdeeds produce a report showing customer I D account number off on Lee. Those customers who have not placed any orders. Please write the quarry utilizing a sub Korean an analyst instead of doing joints, as you may notice that in this quarry as well, a nested in a quarry is being used to bring a list of customer I ds reaching. Turn out to be used by the main quarry using, not in operator. Go ahead and execute the curry, and you will get only those customers who have not placed in order. I met a school where a scenario where a nested sub Khoury returns null values, in which case we might get some unexpected results in case we are using a not in operator against the list of values returned by it. Go ahead and try to look into possible values of currency rate i d. By executing the nested inner Korean isolation, and you would notice that there are some null values along with other values. Now go ahead and execute the query in its entirety. And contrary to what we expected, we get zero Rose returned. That is because applying not in operator with null values resulted in unknown, which in turn led to no matches at all. Go ahead and execute the rectified curry and notice in the quarry that that we're reading out notes from our nested Corey and we get a result said back. Thanks for watching 37. Wrap up - what we went over !: this complete seventh module of the course, This model you saw how to get data using different types of wines. You saw what in in age Wyness. Then you saw different types of outrage. Wine starting left out. You also saw as to how to use out, trying to find those with no match. You also saw how to write a right outage wine. You also saw as to how to construct a full out. Then we saw another technique to get data from multiple tables are utilizing union operator and finally you saw what a nested sub query looks like. Do you in next module? In the Meanwhile, feel free to stretch out or grab a cup of coffee or tea, although caffeinated would contains some amount of antioxidant. Thanks for watching. 38. DISTINCT and ALL Predicates: In this lecture, you will see how to handle duplicate rules, a new results set, losing distinct and all predicates. You may use distinct keyword in your select list to eliminate duplicate records. Please note that returning off duplicate records is by default and therefore using all predicated is optional with these in mind letter school were our first problem. The problem states produce a report that lists product idea of all those products. Who's ordered quantity exceed 20 and have the results said ordered by product I d. In an ascending sort direction. Go ahead and execute a curry, and you notice that it brings back 167 rows with duplicate records moving on to our next problem. The problem states produced a report that lists product idea fall. Those products was ordered. Quantity exceed 20. Please make sure that you display only a distinct list of product. I D. And have the results said ordered by product I d. In an ascending sore direction. Go ahead and execute the query and notice that 28 rows shows up with no duplicate values at all. Thanks for watching 39. Aggregate Functions: In this lecture, you will see how to use aggregate functions that operate on a group of rose, giving a single results set as opposed to single row functions. Aggregate functions operate on a group of froze and return one result for group. The aggregate functions ignore on our values except count function that can be used with an asterisk or the star symbol to give the count of the rules. Even if all the columns are now, aggregate functions can be used as expressions Onley in the following the select list off a select a statement, either sub Khoury or an Outer Corey A. Having clause more details in an upcoming lecture. With these in mind, let us go over an example. The problem states produced a report that displays the number of rows of the table seals order header, and it should also display the maximum of total do field minimum of total. Do you field some off all values in the total? Do you feel and average value of total do column? - Go ahead and execute the curry and notice as to how these aggregate functions return a single value for all the roles present in the table. Thanks for watching 40. GROUP BY clause / HAVING clause: In this lecture, you will see how to group your data using group by class. You'd also see as to how to use having class to specify filter condition for aggregated rose, crewed by clauses used in the curry to divide rose off the results set into smaller groups . It is common to apply aggregate functions on columns around the disclose to produce summarized subsets off data. The select list of the curry might contain both aggregated as well as non aggregated columns. However, in case off non aggregated columns, they must be included in the group by Klaus Otherwise and Error Restaurant. Having clause gives the condition that day aggregated rules must meet in order to be returned in your results set. The having clause is used to qualify the results after the group by has been applied. With these in mind, let us go where some of the examples the problem states produced a report that displays a total for each sales. Alrighty. If you notice the cory, we have bought aggregate and non aggregate columns in our select list. Sales order I D column is a non aggregated column while we are aggregating line Total column before running the Cory. Let us coming out the group, make laws and see what happens. See, you get an error stating that all now on aggregate columns in the select list must be contained in the group by Klaus. Go ahead, uncommon the group by class and let let us move on to the next problem. The problem states produced a report that displays the total sales for each meal, starting with the year off 2004. If you may notice the curry, we are actually filtering out unwanted groups by using the having clause that specifies the filter criterion. Go ahead and execute the query and notice the results said. Thanks for watching. 41. WHERE and ORDER BY in aggregate queries: in this lecture, you will see how to use their and order by clause is in your aggregate Curries and some of the caveats off using them that you should be aware off. Generally speaking, you may use order by clause in aggregate queries as you had used them in the past with the following caveat. If a non navigate column appears in the order clause, it must also appear in the group by class, just like the select lis you may use. Where clause in your aggregate queries. Just like before, However, you may not use an aggregate expression in the where clause in an aggregate quarry. You use the fare class to eliminate rules before the groupings and aggregate, say replied. This is in contrast, having clause, which is used to filter results set after the groupings have been applied. With these in mind, let a school with some of the problems the problem states produced a report displaying customer I. D. Total amount you and have the results said ordered by their territory i d. By analyzing the problem statement, it is apparent that the results said cannot be ordered on territory. I D column. Go ahead and execute the query and notice the error message. This is the rectified version of the curry where we're trying to order the result set based on customer I. D. Instead, go ahead and execute the curry and you would get the results that back moving on to next problem. The problem states produce a report displaying customer I D. Total amount due and bring back on Lee doors. Rules for which territory? Ideas? Either one or two or three. If you notice the Cory, you would see as to how their claws is being used before the group by class. Go ahead and execute the curry and you would get the results back. Thanks for watching. 42. Wrap up - what we went over !: this completes eight and the final model of the course in this module. You saw how to group and summarize your data. You saw how to eliminate duplicate rose by using distinct clause. You also saw some of the aggregate functions such as count men, Max A. The some you also saw how to use grew by Klaus and you also saw how to filter out data once grouping has been applied by using having claws. Finally, you saw the usage of their and order by clause in aggregate queries, and some of the nuances associated with their use is it has been a great journey with you. Thanks for watching in the Meanwhile, I feel free to stretch out or grab a cup of coffee or tea or look after nated. Both contained some amount of antioxidants. Enjoy 43. Bonus Section: