Data Science for Beginners (with SQL Server Machine Learning and Python) | Artemakis Artemiou | Skillshare

Playback Speed


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

Data Science for Beginners (with SQL Server Machine Learning and Python)

teacher avatar Artemakis Artemiou, Awarded Database Expert, Trainer,Author.

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

21 Lessons (1h 50m)
    • 1. Welcome to the Course! What Will You Learn?

      3:39
    • 2. What is Data Science?

      2:21
    • 3. Let’s Compare Data Science with Machine Learning and Artificial Intelligence (AI)

      1:50
    • 4. Data Science Lifecycle

      6:16
    • 5. Section Recap (Introduction)

      0:41
    • 6. Section Overview (SQL Server Machine Learning Services)

      1:09
    • 7. What is SQL Server?

      4:34
    • 8. What are SQL Server Machine Learning Services?

      2:39
    • 9. How to Install SQL Server and Machine Learning Services

      15:23
    • 10. Section Recap (SQL Server Machine Learning Services)

      1:22
    • 11. Section Overview (Development Tools for SQL Server Machine Learning)

      1:03
    • 12. SQL Server Management Studio

      15:43
    • 13. Azure Data Studio

      10:15
    • 14. Section Recap (Development Tools for SQL Server Machine Learning)

      0:44
    • 15. Section Overview (Doing Data Science with SQL Server Machine Learning Services)

      2:05
    • 16. Python and R Packages Included in SQL Server Machine Learning Services

      5:42
    • 17. Writing and Running Your First Python Scripts in SQL Server

      7:52
    • 18. Writing and Running Your First R Scripts in SQL Server

      7:03
    • 19. Train and Score a Model in SQL Server Machine Learning Services

      14:41
    • 20. Section Recap (Doing Data Science with SQL Server Machine Learning Services)

      1:24
    • 21. What Have you Learned in this Course?

      3:13
  • --
  • 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.

31

Students

--

Projects

About This Class

In this class for beginners, you will get started with Data Science and SQL Server Machine Learning Services. You will learn the basics of Data Science, as well as, how you can start implementing Data Science projects in SQL Server, via its Machine Learning built-in feature.

Data Science, Big Data, Machine Learning and Artificial Intelligence, are the areas of technology that have been significantly evolved over the last few years. These technologies, are already heavily used by many organizations, in order to efficiently solve complex problems. Among other, they are used for predicting patterns based on large data sets and thus transform raw data into meaningful knowledge.

Examples where Data Science and Machine Learning are used, include: email SPAM and malware filtering, product recommendations on eCommerce websites, online fraud detection, early prediction of diseases and much more.

To this end, knowing Data Science and Machine Learning, is surely a competitive advantage in the job market, since as we evolve as a society and economy, these technologies are being used even more, for helping Business, Healthcare and many other sectors in our everyday lives.

Since all these technologies rely on large sets of data, also known as "Big Data", combining these technologies with a powerful Data Platform such as SQL Server, can further enhance the process of learning from data.

SQL Server, via its Machine Learning Services offering, helps you to easily implement Data Science and Machine Learning projects, directly against your data, either structured, or unstructured, or both.

Via my class "Introduction to Data Science and SQL Server Machine Learning", you will get introduced to Data Science and SQL Server Machine Learning Services. Among other, you will learn what Data Science is and what is the Data Science Lifecycle. Moreover, you will learn about SQL Server and its Machine Learning Services offering and see live demonstrations of installing the required services for getting started, as well as of how you can run Python and R code from within SQL Server.

Then, we will talk about SQL Server's development tools, which can be used for implementing Data Science projects on the platform and finally we will perform a simple Data Science project in SQL Server, where we will create, train and score a model, for predicting values based on the data input.

After you Complete the Class:

  • You will know what Data Science is
  • You will know the different stages in Lifecycle of a Data Science Project
  • You will know what SQL Server Machine Learning Services are
  • You will know how to perform a simple installation of SQL Server along with Machine Learning Services
  • You will know about the Python and R machine learning packages included in SQL Server Machine Learning Services
  • You will know how to start using SQL Server Management Studio and Azure Data Studio for working with SQL Server
  • You will know how to execute Python and R scripts directly from within SQL Server
  • You will know how to implement a simple Data Science project using Python and SQL Server Machine Learning Services

Meet Your Teacher

Teacher Profile Image

Artemakis Artemiou

Awarded Database Expert, Trainer,Author.

Teacher

Hi there! I'm Artemakis. I'm a Senior SQL Server and Software Architect, a professional Author and Speaker, and a former Microsoft Data Platform MVP (2009-2018). I have over 15 years of experience in the IT industry in various roles and I'm also a certified SQL Server Engineer.

Moreover, I'm the founder of SQLNetHub and TechHowTos. I'm the creator of the well-known software tools Snippets Generator, DBA Security Advisor and In-Memory OLTP Simulator. I'm also an author of many eBooks on SQL Server.

I currently serve as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).

I'm here at Skillshare, to share my expertise with you, in a simple and understandable way. My classes include ... See full profile

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. Welcome to the Course! What Will You Learn?: Hello and welcome to my online course, data science for beginners, we see where several machine-learning, my name is Esther max SMU, and I'll be your instructor in this course. This course, even though it is an introductory course, is no complex concepts and complex examples. Still, it is very useful for those who want to get started with data science along with the powerful Civil Service Data Platform and it's machine learning services of herring. So let's take a look what we are going to learn in this course. In this course you are going to learn about the science or the trees. What is its lifecycle? What are the differences between the science, machine learning and artificial intelligence? What are the similarities between these three areas, as well as to learn what are suicidal machine learning services. To this end, we are going to install together sewer Server along with sewer several machine learning services and see how we can use sequels servers and between development tools for doing data science with sequel server. Last but not least, we're going to see data science examples using SQL Server machine learning services. And she was servers extensibility to the Python and languages. A few words about me. I'm a senior civil servants software architect. I have over 15 years of experience with SQL Server and.net. She was surrounded and have always been migrates. Her passions in technology have been a Microsoft data platform MVP for nine years and a professional author, blogger and speaker, and a certified silver server engineer. Moreover, I'm the founder of civil net hub and I'm also involved in many community activities, having to do sewer server and.net. Now about the course requirements, it is good to know basic SQL Server knowledge because we are going to add some scripts we're going to work within she was server environment. Now regarding Python and learn knowledge, again, it's good to know but not a strict requirements are going to write some very simple scripts. But again, as I said earlier, it is not a strict requirement. Since, again, if you do not know Python and still you will be able to go with the flow of the course and understand these simple scripts. Now, who is this course for? This course is for people who are interested in getting started with data science along with SQL Server machine learning services. Combining data science with a powerful CEO survey data platform. And it's machine learning offerings. It's a great combination because you are using SQL Server as a platform. So you are using different data science techniques in database. So there's no need for moving data all over the place and you apply directly onshore data, your machine learning algorithms and models. So this is very exciting and I'm really looking forward to help you learn more about it through the course. Now, after the course, you will know what is the sciences, what is its main lifecycle? What are the similarities and differences between the science, machine learning and artificial intelligence? As well as you will know how you can start doing data science using SQL Server machine learning services. Essentially, after attending this course, you will be able to deep dive even more into the exciting world of data science. Great, let's begin the course. 2. What is Data Science?: The very first concept you need to learn. And also to start with the science is no other than what is the term data science? What does data scientists mean? Solis head to define it. There science is a phrenology field that combines many skills, such as algorithms and programming, mathematics and statistics for extracting knowledge and patterns from Rome data databases and more. Now, this involves analyzing both structured and unstructured data. Also, data science is related to data mining and machine learning. Actually, all of these are combined now to, to transform raw data into meaningful knowledge. And as mentioned earlier, these data can be either structured or unstructured or both. Note that many organizations already trust data science for helping them becoming more efficient and get the useful insights about their data and business by extracting knowledge from its data. And localization can predict may trends having to do with its field of business and thus become more efficient and more profitable. Now let's see some real life examples of data science. Let's see some applications of data science in real life. For example, spam filters now use data science. Also are commendations in, in commerce. For example, if you visit analyze store and you buy some products, then with data science and the store can recommend other similar products. Flops detection is another application of data science, speech recognition, and the list goes on. So you can see that data science is really important. Data science is already here. And among other, you can also see that the size is a very important niche in the IT industry. In the next lecture, we're going to compare that a science with machine learning, artificial intelligence that say I see there are differences and similarities. And how each one of these areas depends on another. 3. Let’s Compare Data Science with Machine Learning and Artificial Intelligence (AI): In this lecture, we're going to compare data science with machine learning and artificial intelligence that these AI or grant to see their differences, similarities, and how each one of these areas depends on another. So in the previous lecture, we talked about data science or the ti's. Now, let's talk about machine learning. What is machine learning? Machine learning is a process where computer system learns from data over time. It sees a subset of artificial intelligence and it is actually the link between data science and AI. Now, let's define what is artificial intelligence? That is Ai. Ai is the quest of mankind for achieving near human intelligence. So based on that, we beat AI models and AI machines in order to mimic human intelligence. And this requires a lot of computing power and data. Now this data is offered by data science via machine learning. So let us see this diagram. You can see that data science first analyses both structured and unstructured data and transform data into knowledge. Then machine-learning learns from this data. And as I said earlier, it is a subset of Ai. Then in the next step, artificial intelligence pops in and mimics human intelligence based on processed data feeds. So here you can see the relation between data science, machine learning, and artificial intelligence. In the next lecture, we're going to talk about the Data Science Lifecycle and see the different phases when doing data science. 4. Data Science Lifecycle: As in software engineering, where there is a lifecycle for software systems in a similar manner, there is a lifecycle for data science. Let's learn more about data science lifecycle. So what is the Data Science Lifecycle? Tease the full process of applying data science. There's a to solve a certain problem. To do that it passes through different phases. So this involves a series of steps. Let's see steps. The first phase is to understand the problem. What is the problem we are trying to solve using data science? The next step is data collection discovery, which is the phase where you collect the raw data to be used for solving the problem using data science. It is the phase where you discover all the required data in order to be used in your data science lifecycle. The next step after you gather the data is data modelling. So by analyzing your data, you apply data modelling on these data. After you model your data, it is time to develop your data model. That is the actual step. Where are you? Create your model? You train your model and you deployed. Deploying your model means that the model has been developed and it is deployed on a test environment first, in order to be tested by the users. And if the users give the user acceptance, then this model can also be deployed on production. Now let's talk even more about the different steps in, in Data Science Lifecycle. So the first step is to understand the problem to solve. So that means to understand what is the exact problem you are trying to solve within a science. It is a phase where you translate client requirements. A well-defined problem to be solved with science. Now in the face of data collection discovery, there is a major question to answer. Is the raw data available? You will also need to identify this habit of data to use if this applicable. Of course, after these two steps, the first step is to gather all the required data from all the relevant data sources. So at this point, you might gather structured data or unstructured data or both. So at this phase, you might also apply possible data pre-processing data to take care of missing values, data formats, et cetera. So with these preprocessing to bring your data up to a format that it is ready to be used by the next phase in your data science projects lifecycle, which is of course, data modelling. So inter-data modelling phase, you find the relationships between the different types and sets of data which will be used for solving the problem. At this point, know that relationship diagrams can help towards finding the property relationships. Between your different types and said sort your data, then the data can be stored in a relational database management system such as Microsoft SQL Server, MySQL, Oracle, and etcetera. The next phase is the phase of data model development and deployment. So in these phase usually lack the proper algorithms to be used against your data. So these algorithms will actually create and train your model. This is actually machine-learning because using these algorithms, along with creating a training the model, you end up having a model that learns from the data. You provide it via the previous phases in your data science products life-cycle. Know though that there are many types of machine learning algorithms available online. And the key is to find a proper algorithm for the problem to be solved. Two, you cannot just select random algorithms for any problem. The important thing here to note is that you need to find the proper algorithm for the problem we are trying to solve. Data science and machine learning. Now bow to your data models deployment. It is a task where integrated developed a model with an operational environment. So you first do that on a test environment and then use their acceptance follows. So after you deploy your model on a test environment, the end users can test the model, see the protections. The model generates. C, the output generated by the developed model. And they can either accept or send the model back to you for further development. Now, if your acceptance gives the Korean light, the final step is to integrate example there with a production environment and different case. As mentioned earlier, you will need to further refine the model in order to fully match the business needs as defined by your end users. Now let's talk a bit more about the user acceptance phase. They user acceptance phase is where the business user evaluation model results on the test environment. So you have your data input, you pre-processed data, you get it ready for classification. You create your model, you tenure model, and produce results. Now this is the data output. So these dead output is what the business user evaluates. And via these, it actually vibrates the model if it behaves based on the requirements given in the beginning of the data science projects lifecycle. Now, if everything okay, they usually the business user gives a green light, that is the acceptance actually to proceed with moving the data model to the production environment. Now, in the next lecture, we're going to review what you have learned in this section. 5. Section Recap (Introduction): Now let's recap what you have learned in these introductory section of the course. In this section, we talked about what is data science? What can you do with it? We, so some real life examples of data science. Also, we compared that assigns with machine learning and artificial intelligence. We saw there are differences as well as there are similarities and how these three areas are interconnected. Also, we turn about the Data Science Lifecycle, which is actually a series of phases that you follow when you implement and data science project. 6. Section Overview (SQL Server Machine Learning Services): Welcome to the overview of a new section of the course called sequel server machine learning services. In these new section, you will learn more about sewer server and especially its Machine Learning offering branded as she was server machine learning services. So what will you learn in this section? First, we are going to talk about sewer server. What is sucrose cyber? What can you do with SQL server? If you are aware of sewer server, if you have the basic knowledge of suicide are measured in the course requirements. Okay, you might skip these lecture. However, it's a good opportunity to refresh your knowledge about sewer server. Now, we do not know she was a server. It's a good opportunity for you to learn a few things about these powerful data platform. Then we will talk about sewer, several machine learning services, what they are and what they offer. Last but not least, we're going to talk about installing sewer server and machine learning services. And for this purpose, we're going to see a live demonstration. Great. Let's begin with a lecture about SQL Server. 7. What is SQL Server?: If you are familiar with SQL Server, then you might skip these lecture. However, if you'd like to refresh our memory about what is sewer server or if you're not very familiar with the powerful Coursera data platform, then this lecture will be very useful for you. So in this lecture we'll talk about sewer server, what it is and what it can offer. I included this lecture because in order to have SQL Server machine-learning services, one of the prerequisites is to have a SQL Server database engine as well. So prior to talking about sewer server first and we need to define water relational database management systems, also known as RDBMSs. So what are our relational database management systems, or RDBMSs, are based on the relational model of data. To this end, they use an approach to manage data using a structure and the language. They're well known, language, Structured Query Language. So here you see an example of a relationship between two entities, also known as tables. So we have the employees table with columns ID, code, firstName, lastName in location id, where Location ID is a foreign key which references the ID in a second table called location, the columns, ID, code and description. So in this example we see these two tables having a relation between them defined by the foreign key employees location. Which means that in the employees table, there is the location id column, which is a foreign key that references the ID of the location table. Now, what differentiates RDBMSs between them? Even though the majority of five DBMSs support NCS clear, for example, that is the standard secret language. They also have additional functions, systems TO procedures, et cetera. So each RDBMS, besides from supporting the standard Secret Language and the relational model of data, they also provide additional functionality via these functions, system store procedures and more. For example, in SQL Server, there is the TC Query Language, which stands for transact SQL, which is an implementation of the standard SQL language with additions in terms of system functions to oppositions, et cetera, like we talked before. For example, you can also find a secure server in-memory OLTP, she was seven integration services that is necessary, yes. Sql Server Reporting Services so that these SSRIs, she was Server Analysis Services, also known as SSAS, and much more. Now let's see some examples of popular relational database management systems. One such example is Microsoft SQL Server and other RDBMS ease Oracle Database. Mysql is another popular relational database management system. Postgres, SQL lite and model. Now let's see what you can do with SQL Server. Among other, you can store, organize, and procedure data with the use of databases and other database objects, such as views, stored procedures, functions, et cetera. Also, you can transform raw data into meaningful insights and knowledge. Moreover, two can process very large volumes of data on disk and in-memory. Also, you can perform business intelligence operations. For example, using sewer server, you can set up and data warehouse moral looking around complex analytics with SQL Server Analysis Services. Also, you can produce sophisticated reports using SQL Server Reporting Services that he says SRS. You can work with encrypted data and compliance. And also you can apply machine learning algorithms and operations against your data. And this is what this course is all about. Using sewer servers, machine learning capabilities in order to implement that assigns against your data. Besides that, you can also have the decorations it here we'll see what Server Integration Services, FSIS and much more. Now in the next lecture, we're going to talk about SQL Server machine learning services. 8. What are SQL Server Machine Learning Services?: Now let's discuss about civil server machine learning services. She was seven machine-learning services is a feature in SQL Server. It was originally sheet and sewer Server 2016 as our services and there was support for the mathematical language are. It was then rebranded in SQL Server 2017 as machine learning services. Since then, in every new version of SQL Server, new features and support for more languages is being added. She was David Machine Learning Services enables Sequence server users, Tran are Python and Java scripts against relational data in SQL Server, note though that you can use different packages and frameworks for predictive analytics and machine learning. The main benefit of sewer seven machine learning services is that you have the ability to perform machine learning in database. That means that you do not need to transfer data from SQL Server to other environments and other infrastructure in order to apply machine learning algorithms. You can do all of that in a single place. That place is a well-known SQL Server data platform. So these ads security, performance and efficiency to the whole process of machine learning. Now about suicide with language extensions via sewer server machine learning services. She was seventh 2017. Machine learning services support R and Python. So via Sequel Server 2017, when you have machine-learning services enabled, you can run scripts in the R and Python languages. In SQL Server 2018, Java support was also added. So in sewer Server 2013, you can run scripts of the language Python and Java. Now let's talk about what can you tell which ones have a machine-learning surfaces. With machine learning services agency, we're a server in a high level description. You can't have a machine that is a program actually that kinda learn from data without being explicitly programmed. Most specifically, you can't apply classification and categorization against your data. You can apply regression, predict continuous values. You can't detect anomaly in your data. And who counsel have recommendations. All of these based on our data in our server via secure server machine learning services. In the next lecture, we're going to talk about how you can install SQL Server machine learning services and see a live demonstration. 9. How to Install SQL Server and Machine Learning Services: In this lecture, we're going to see how we can install SQL Server along with suicidal machine-learning services on a development environment. But first, let's talk about the requirements. So about the installation prerequisites is not that you have two options for installing SQL Server machine learning services. The first option is to go with a new SQL instance and thus installed Sequel Server along with machine learning services. The second option is to use an existing SQL instance and that machine learning services signal feature. In this course and specifically in this demo, we will be working with the new SQL instance option in order to learn things from the beginning. Therefore, I will be using a single virtual machine that is in development environment where I will be installing SQL Server 2013 Developer Edition, along with machine learning services and also to see the process when installing seawalls Server along with machine learning services. Now about distillation prerequisites for a new SQL Server instance, you can check the MS dogs article on the following link where you can find the hardware and software requirements for installing seawalls server. You check all prerequisites. But the below are the most critical or periodic system requirement. Processor speed is CPU, memory, that is ram, hard disk requirements, and total net framework requirements. Last note, that's sewer service since the 2017 released, he supported on Windows, Linux, Mac OS, and Docker containers. In this demo, we'll be working with a Windows virtual machine. Now about distillation prerequisites for sewer server machine learning services. You can find the system requirements for installing machine learning services. On the below link, you check all prerequisites, but the below is the most critical, which is actually a SQL server instance. And in the case where she was have an instance is not present, you can install it along with suicide, have a machine-learning services. And this is actually what we're going to do in this live demonstration. Last to know that super seven Machine Learning Services as a post-it on both Windows and Linux. Now some considerations about suicide or machine learning services. Note that only from SQL Server 2018 or later, you can have a failover cluster of C, whatever machine learning services, as well as you can install machine learning services in two modes. The first mode is the in-database mode, which would be the one that will be used in these courses and demos. And the other mode is the standalone mode where you install machine-learning services on another machine. Not that I personally find the in-database mode more powerful because you have your data and your data science environment with support for Python and R and Java on the same controller. So by doing that, you do not have to move data between your database server and you are Python or Java server and so on. So you have everything on the same place, which is best when talking about performance. Security and overall efficiency of the whole process. Some other considerations even supported to have multiple versions of Python and not on the same machine known that it is not recommended because you might have some conflicts. Also note that there are some post configuration steps when installing and setting up SQL Server machine-learning services. For example, you need to enable in see what server surface area figuration support for running external scripts. So let's jump to our first demo and see how we can install sewer server machine. There is a machine and see how it can install sewers, have a machine-learning services on a virtual machine with windows. So this is my development environment where I will be installing SQL Server alone with machine learning services. So on this webpage, you can download a free special edition of sewer server, in this case, a developer vision in order to use it for our test environment. Note that there are certain terms and conditions when using Developer edition of SQL Server. The major one is that you cannot use the developer edition for production use. But as you can see here from the description, it is a full featured three edition licensed for use as a development and a test database in a non-production environment. And this is the purpose of this virtual machine to be used as hand development and test environments. So when clicking download now, you can't see that a wizard will be downloaded via which would be able to download the developer edition of 2018. I won't be completing these procedure since I have already downloaded sequel server 2018, develop parody Sean. So I saved the file on my C Software falter. So I run the wizard and using the wizard, I downloaded the ISO file of suicide over 2019, developed medication. So let's mount the ISO file by double-clicking on it. And let's run as administrator the setup process. Note that when installing SQL Server, there are many best practices that you need to take into consideration. For example, using different drives for beta and log files, temp db and so on. But since this she's a single virtual machine that serves as a test environment. I won't be following these best practices for this demo. Know however, that when working on production environments, you should be following all their performance and security best practices about the solution set of sewer server. So now let's proceed with installation. I will be performing new sequel server standalone installation. So I'm taking the first option. Let's close this. So here you can see that We'll be using the parity edition. Again, take into consideration the terms and conditions when using a developer edition of SQL Server. Note that there is a free edition of sorcerers well, that can be used for production. That is the explicity Shawn. However, it has some limitations was you need to take into consideration when making this option. So let's continue the installation with developing traditional sewer server. Here are the terms and conditions of their Developer edition of SQL Server 2013. And if you agree with tabs conditions, you can click on accept the license terms and proceed. Here. You can check for updates when installing SQL Server. So if you're having an interconnection, it is the commanded to do that in order to download the latest update of sewer server, I will be checking this option because I have an internet connection, this virtual machine. So now the visa checks for product updates, that is actually cumulative updates for sewer server or any other updates. Some other rules here. We continue. And now we have the most important part of the installation process. So here you can see that you can select what to install. In this demo, we'll be installing database engine services. Because if you remember what we have been discussing earlier in the lecture, I will be taking the option of installing a SQL Server database instance along with whatever machine-learning services. That's why a teak, the database engine services baton. And now you have two options about machine learning services. Here you can see that you can't have this option that is installing machine learning services and language extensions with support for Python and Java. This is in database. This is the first mode of machine learning services, as discussed previously in the lecture. And you can also have another option of installing a machine-learning Server, stand-alone server using R and Python. Since I will be using in database machine learning services, this is the option I need to check for my installation. I also need Client Tools, Connectivity, and I won't be selecting any other features. So let's proceed with installation of mine database engine and machine learning services with support for our Java and Python in database. Here you can specify a default or a named instance, since I do not intend to add more instances of sewer server, these virtual machine, I will be using the default instance. So now here in this dialog, you can select how you can install the JRE, that is the Java Runtime Environment. You have the option of installing or PNG ARE included with this installation or provide a locational offer different version that has been installing this controller. Since this is a new virtual machine. Just for this live demonstration, I will be installing or PNG ARE included with these installation of SQL Server. Here you can set the correlation for your sewer server database engine. We'll be using the default one. And as service accounts again, I will be using the default ones as suggested by the installation wizard. Here, this is my authentication few ratio, so I will be using Windows authentication mode and that will add a card user know that this point that it is far more secure to just use Windows authentication mode on your sewer server installation. Of course, that means that you have applications that support Windows authentication mode and do not require to have a sewer several user with the username and password. This is a data directors configuration, temp DB configuration. I will be leaving in a default here because I said earlier, DCS, important virtual machine with a single drive, maximum degree of parallelism. It is calculated based on the Neumann notes available for SQL Server memory configuration here you can set the minimum and maximum around to be used specially what server or you can leave the default and last file stream, which is not required for this demo. So here via this dialogue, we will be installing Microsoft R o pedagogies. And it has distribution are made available by Microsoft under General Public License Version two. Here you can check the terms and conditions and if you agree, you can click on Accept. Then you click on Next. This is dialogue about the Python installation. There are some terms and conditions and then relay that is end-user license agreement. Again, you can check it out and leave you agree, you can click on Accept and continue with installation. And now we can see a summary of the installation rules about sewer server and machine learning services. So if you find the summary to be okay, I can click on install and everything we selected will be installed. You can see that the whole process is pretty fast, even though I'm using simple Virtual Machine with limited resources. The whole installation of Sequel Server 2018 with any feature you select, it's very fast. So imagine if you do that on an even better environment. Seen some using a virtual machine with an internet connection. Note that the necessary packages for installing R Python and other libraries required by sequels have machine learning services are being downloaded from the internet. Now, if you do not have an interconnection are available on the machine, you're trying to install sewer server machined surfaces, then around to do that. So the wizard will let you know about some links where you can follow and download the packages from their mind. Only copy these packages locally on the machine you are running. The installation of sequence have machine glands, services, and the installer will use these files you placed on a specific falter in order to install these libraries. So let's wait a bit more because this process of downloading all these packages takes a little bit of time because in total, in downloads, a few gigabytes of data. So right after the wr downloads in PAG age, each and directly installed seat. So you can see that finally the installation was successful. So via this demo, we installed sewer Server 2013 Developer Edition database engine, as well as sequel server machine learning services with support for Java, Python, and R. Moreover, some client tools were installed and some other related services. So let's close the wizard and go to Supra Server Configuration Manager just to confirm that all services that were installed are up and running. So this is a SQL Server 2013 Configuration Manager. The council check the status of services via the Windows services component. But I prefer using SQL Server Configuration Manager because it also provide some other features that are not available in Windows services component. So we go to see what server services. And you can see that SQL Server, that is the database engine, is up and running as well as she whatsoever, Launchpad, SQL Server lunch, but it's the service used by SQL Server, machine learning services for executing external scripts. Constituents have a browser and SQL Server Agent stopped, but that's okay because I do not need these services to be used. These demos. So yeah, that means that the installation was successful. The proper services are up and running. Now the next step is to use the proper built-in developer tools from Microsoft inertia to work with SQL Server and machine learning services to this end, in the next section we're going to talk about SQL Server Management Studio, also known as SMS, as well as Asia Data Studio. But for the time being, let's proceed with a recap of the current section. 10. Section Recap (SQL Server Machine Learning Services): Now let's recap what you have learned in this section, sequel server machine learning services. In this section we've talked about SQL Server, but prior to that, we talked about a relational database management systems. We explained what our relational database management systems. We saw some examples and we explained that she were server is Microsoft's proprietary relational database management system and theory. Also, we discussed more about SQL Server and talked about what can you do with these powerful data platform. Then we talked about SQL Server machine learning services, which is Microsoft's Machine Learning offering via SQL Server. To this end, we've talked about the different versions of CR7 machine-learning services and what each version offers. Also, we discussed more about machine learning services in SQL Server and talked about what you can do by using these credit feature. Then we discuss about the requirements for installing SQL Server and machine learning services, as well as about the available options when installing SQL Server machine learning services. Moreover, we saw an interesting lighting demonstration where we installed SQL Server 2018 Developer Edition, along with sewer server machine learning services on a Clean Windows virtual machine test environment. 11. Section Overview (Development Tools for SQL Server Machine Learning): We continue the course with a new section, tight Alton development tools for suicidal machine-learning. Let's learn more about this section and see what you are going to learn. So in this section, we will be talking about sucrose Harvard development tools for doing data science. To this hand, we're going to talk about sewer Sever Management Studio, also known as, as, SMS and Asia tell us 2D. You note that these tools are free tools provided by Microsoft for working with Coursera data platform as well as Azure SQL database. Note that we will see also live demonstrations in this section. We are well-equipped whom download SMS and Azure there Studio, install it on the virtual machine we used for the previous demo where we installed sewer Server along with sewer several machine learning services and see how we can start accessing sewer server using these two powerful tools. Let's begin with what server management studio. 12. SQL Server Management Studio: In this lecture, we're going to talk about one of the main development tools for sequel server. This is a free tool provided by Microsoft and it's called sewer server management studio, also known as SMS. And she's a mesh, I said earlier is a free Windows-based Microsoft tool for sewer server. It is well established, integrated environment for creating, querying and managing SQL Server and Azure secret databases. It is also used for configuring SQL Server. It has many wizards that make things easier for you when it comes to managing seawater server. And with a powerful TC query language, you can do almost anything. Note that variability of some features is dependent on the sequel server version. In addition, that you are using and you are connected to via SMS. I said earlier, as his MS provides many ways as for performing different tasks easily. And the other great thing is that via this wizard, you can treat the whole operations to TC. That is something that can really help you towards automating different tasks. Some examples of what you can do sewer server management studio include creating databases, tables, indexes, et cetera, in positive and exporting data. Sitting up always on availability groups, planning, vulnerability assessments, setup, replication and more. This is a screenshot of SMS. So this is how it looks like. You can see that on the left it has a three via which you can access all the objects of the currently connected seawater very instance. So you start on the instance level, you go to databases. You can find that the divergence here then you can see there is a tab security, which has to do with the security of your super server instance. Several objects, replication, so on. This is an example of running query using the sample database Adventure Works, selecting the top 100 records from human resources department table. And you can see here below, this are my results. Now about the system requirements for SMS currently version 18. It suppose connecting on SQL Server 2008 through 2019 and non-words. Note at this point that she was Server 20082008 or two are not official supported anymore, but still with SMS, you can connect two such instances. Also the supported operating systems include Windows 8.1 and Windows ten 64-bit windows server 20162018, 64-bit. Windows Server 20122012, R2 64-bit, as well as Windows Server 2008 R2 64-bit. You can learn more about the system requirements for SMS as well as you can download it by following the below as dogs EEG. Now it's time to see a demo where we're going to install and start using SMS on the virtual machine we used earlier for setting up. She was sad about 2013, developed erudition along with sequel server, machine learning services. So here we are back to our test environment. We are in a previous demo. I have installed SQL Server 2019 Developer Edition along with sewer server machine learning services. Now it's time to download and install sewer server management studio version a team. It is very easy to find the the trust search for SMS. And this is their web page of the tool. Here you can find information about the current release of SMS. So besides the download link, you can find version information and many other resources like available languages which no previous versions and so on. So now let's click on download. And you can see that download of SMS just become. So. You save the installer location on there on disk right after it is now loaded. Just run the installer. It is very simple and we're going to check it out right after the download is completed. A few moments later you can see that the installer of C, whatever Management Studio has been successfully downloaded and we can't click on rand and lodge the installer. Let's close our browser window. We do not need it for the rest of this demo. So right now we're launching the installation wizard server management studio. And they selectional has become so here you select the location where sewer server management studio will be installed. I will just use the default one, C program files. So we click on install and the installation has just become. So let's first install the packages and then we'll start deploying a tool on our Windows environment. A kinda reminder is that suicide in Management Studio can only run on Windows machines. But if you're looking for a cross platform solution, Microsoft provides a second tool which is called Azure Studio, which can be used on different platforms such as Windows, Linux, and so on, are going to talk about Azure Studio in the next lecture. And likewise, we are going to see and demo of installing a start using a tool by connecting again to SQL Server. As you can see, the installation was successful and now a restart is required in order to finalize the setup. So we start our computer and waits until my boots. So know that our virtual machine has been successfully started. Let's search for SQL Server Management Studio. So we'll go to our program. We got to Microsoft SQL Server tools 18. And there you can see, among other tools, you can also see Microsoft SQL Server Management Studio 18. Let's click on it. Now we can see that Suez server management studio has started. And this is the SQL Server instance connection dialog. Here, if I remember correctly, where we installed sewer server, we selected the option of an default instance. So our sewer server instance to connect to is actually the name of our virtual machine. So you can either enter the name of the virtual machine or just US. Then dot sine in order to connect to these default to see what server instances on our machine. So you can see now we are connected in our SQL Server instance. So here you can see on your left that tree of database objects. And so, and then the diversities tab, you can see the system databases as well as any user databases. For the time being, we don't have a user database. But later on, I will create some SAP and databases and import some other In order to use them in subsequent demos. That's also the Security tab where you can review the security objects are just sewer server instance. For example, you can see the logins here, several roles, credentials and audits and so on. You can't, you'll always on high availability settings. Of course this is a standalone instance. We don't have such feature enabled under management, among other, you can view the sequel server logs. So if we double-click here the current block or pens, and you can see various information about the SQL Server instance and different events. So now let's run a new query and after to verify for example, the version of SQL Server. You can see this is SQL Server 2018. Developed heritage can. Now, let's enable external scripts. If you remember from previous lecture. This is one of the post configuration steps right after installing she was ever machine learning services that you need to perform. So you can either perform these using SQL Server Management Studio or Azure Data Studio scenes. We installed first sequel server management studio. Let's enable this setting via these tools. So we will need to execute the system stopped procedure as to configure. First, let's run it just to see some values. In order to see all the surface area configuration options when running SP configure, you need to show what one's options. So you need to enable this flag. But there is no need to do that right now. Because if you've seen line ten, this is the option we want to enable, external scripts enabled. You can see that the value is currently set to 0, so this option is disabled by default for security purposes. However seen we want to make use of sewers, have a machine learning services. It is a prerequisite to enable this setting, since by enabling the setting, you allows the server to execute external scripts. And that means that SQL Server, via these options, along with the sequel server, Launchpad salaries we talked about in the previous demo where when stocks it was seven machine learning services, it is able to run Python scripts are scripts as well as Java scripts, all of these in the database. So let's enable external scripts, enabled option Foucault here. We enter the option type here, one and after to set the flag to one there and V2, V1. And we reconfigure. With override. Ok. So we execute the first line and then reconfigure with override. Now let's run again SP configured to check if the set he was changed. And you can see that at line ten, now the random value for external scripts enabled it is set to one. I want further proceeding this demo. Quran Python harass creeps since this is included in subsequent demo. But before completing this demo. And that there are two battery introduced to use USD server management studio and generally SQL Server. Let's create together a sample database with the sample table and import some values. So there are two ways of doing that. You can either use the wizards or use TC equal. Let's use the wizard. So you right-click on databases, usually left noon database and you define the name of the database. Let's call it test the b1. Here you can see some options for the database to be created. For example, you can see the initial size, you can change that if you like. You can see the default autograph, Viking and the max size. You can check the path where the physical files will be start. Edward is here from the Options tab. You can control many options about the database. For example, you can control the recovery model. Here you can select fool, bulk, logged and simple. Since I'm not really interested in keeping detailed logs of these database, I will change their cover model to simpler. And here you can see many other options about the database to be created. In fighter groups. You can add more than Fighter Group. And via file groups, you actually control how your logical physical files of the database are stored. Ok, let's click the OK button. And now our database is being created. You can see it has been created already. So now let's open the database. And here you can see that the friend database objects, tables views external resources, programmability objects, for example, store procedures found Sean's triggers, assemblies, and so on. As well as service broke her storage and the security objects or the database. Now, let's create a table. So we radically the tables. Three was elected no table. And let's create a table. So right now in this dialogue, we can specify the columns. So let's create an id column of type integer, turn the allow nulls and right-click on need to select. To make these time, I've seen this hierarchy now. Then let's add a code column of the teletype varchar 15. Let's do not allow now says, well, OK, let's save our table and call it TBL test. So now our table is created. Let's refresh here and you can see if this is our table. By right-clicking on it. We can't take further actions these time against our table, so we can change its structure. We can select the top 1000 rows. Let's do that. Of course it is empty so we don't get anything in our results. Let's add it to the top 200 rows. And this way we are able to insert some sample data. So here in the ID w0 and w1 code one to code 23, code three, et cetera. Now let's close this and our changes were committed. Now, if we right click again on the table, we can select top 1000 rows and you can see our sample data. You can do much more using suicidal Management Studio, I guess you were server or Asia SQL database. You can administer the entire instance. You can create objects, setup, replication, high-availability. There are hundreds of options towards administering, as well as developing sequel server. But for the purposes of this course, you do not really need to know all of these tasks. So for our data science examples, we will be just using sample databases with sample data. And we will apply our data science logic against these sample data. So now that we have completed the demo of installing and start using SMS, that she was have a Madras studio. We're ready to continue with the next lecture. In the next lecture, we're going to install Azure Data Studio and see some initial steps of getting started with the tool. 13. Azure Data Studio: Now we'll discuss about the other free tool provided by Microsoft for developing administering SQL Server as well as Azure Sequel database. The name of this tool is Azure to the studio. So what he says shooters studio, Azure does studios have free cross-platform Maxwell tool for sewers, however, an Asia sequel database. It is Microsoft's cross-platform database tool for its on-premises data platform like SQL Server and cloud-based data platforms such as Azure SQL database and Azure SQL Data Warehouse. Now, Azure does studio cross platform means that it can run on Windows, linux, as well as on MacOS. Also, it offers a rich customizable dashboards. Moreover, it is extensible. So this is a screenshot of Azure Glass Studio. So you can see how Azure Data Studio look like. This is the first screen when you first launch as a studio where you have entire log, similar to SQL Server Management Studio for defining how you want to connect to your SQL Server instance. And by saying sewer server instance, these can be on-premises or on the cloud. This is another screenshot. For example, I'm connected to a SQL Server instance called Secret 2K, 1962. And this is the dashpot. Dashpot. And from here you can start a new query, can restore database, you can search for database, and so on. On the left here you can see again the three where can see the instance, the system databases, user databases. For example, I have exit database called sample db with sample table called DIBL test. And in a similar manner like innocence, you can access all these database objects and instance objects. Now about additional requirements for Edge Studio, there's a party operate systems include Windows ten, Windows 8.1, and the Windows eight. Windows server 20122012, R2, 20162013. It also supports macOS. It also supports running on Linux, Red Hat Enterprise Linux 7 for Red Hat Enterprise Linux, 7.3, SUSE Linux Enterprise server version 12, SP2, as relies on Voodoo Linux 16.04. Note about Windows only 64-bit versions are supported. And as well as for all these system requirements, they constantly change, they are constantly update. And you should check off at the below link. This is the MS dogs webpage for Azure Studio where you can find the download link as well as the late test system requirements. For example, support for newer versions of all these operating systems is being regularly added. So, yeah, make sure that you check the system requirements on this URL. Now let's see another demo. We're on the same test environment on our VM. We are going to install and start using Asia tele studio. So this is my test environment. I'm running this demo on the same VM like we installed. She was territories or 19 as well as SQL Server Management Studio, that is SSM s. Now, let's search for Azure Data Studio. You can directly follow the link provided in the lecture or you can search for it. And this is the web page. This is direct download links, but you can also visit the homepage of Azure does studio, that is stocks Microsoft.com, NUS as Crowl Asia to the studio. So here you can find more information about Azure Studio. You can learn about its features, find more information about each feature comparison with SMS. So with this comparison, for example, the feature comparison of edge there Studio with SQL Server Management Studio. You can decide what best suits your needs. If you are going to use a yoga studio or she was ever Management Studio or even both. End. Let's begin the download face and installation of his studio. So let's click on download and install Azure Data Studio. Here you select the platform. So I will be using Windows, since this is a Windows VM. And I will download the user Installer. Here you can see that the calibration is 1.16. So new veterans are big. So let's click on User installer and downloaded. Let's save it. And then downloads folder. Okay, so download has begun. Let's weigh the beat until the installer is downloaded on my local VM show. A while later we can see that Azure Studio has been downloaded. So let's append the downloads falter. So these, He's very installer. We downloaded about edge the studio. So let's double-click on it. We do not need to run these as administrators. Here you review the license agreement and if you agree, you can't continue and an installation has begun, it doesn't take long. Note that currently edge does to you is being setup only for my user, for the goods that I'm using currently on the VM. If you want to install it for all users, that's a different process. But I just want to install it for myself, even though this sounds a little bit selfish, but for the sake of the demo, I do not need to install this for all users since there's only one user, this demo user I'm using on this test virtual machine. So now you can see that has been installed and lens launch Azure does today. So these will be the first round of Azure that yesterday on this VM. And since it is a fast run here, you can allow you like Microsoft collect usage data or to improve the tool. Who can participate, who preview releases of age, distribute, and so on. Okay, now let's connect to the same ship or server instance we connected to before using SMS. That is the local instance. This is the default instance were installed in their very first dam on discourse and will be used with such indications scenes. And during installation of SQL Server, I allowed administrative access to the current user, that is the demo user. So let's connect. You can see connection was successful. Let's maximize this end. This is the dashboard, this is the server dashpot. So here you can find some information on that. This is sewer server version 15 that she was over. 2018 Developer Edition, the controller name always at so on. Here you have shortcuts to some tasks on this dialog you can find in databases to click on and delivers. For example, you can right click. You can select a new query managed database and so on. So this is the sample database we created early together in a demo using SMS IV, we click new query against this database. A new window will open connected to testing you on, and we can select its contents. So from table tests, select everything. Let's run the query. And you can see this is the sample data we are using SMS and now we are accessing this data using Asia Data Studio. So here you have some export options. For example, you can save these data, these great results. V, Excel, JSON, XML. And you can see a chart of this data as well. If you remember, prior to downloading Azure Data Studio in the MS. Dogs webpage of the tool, There was a comparison between Azure Studio and sewers that are measurements to deal. Well, both tools are very powerful. The main difference at the time is that SMS can be mainly used for administering SQL Server. But if you are really interested in developing sewer Server, Azure Data Studio might be a better feet because it is more developer friendly. So it has a more powerful intelligence. For example, it has some more built-in functions when working with data. Simple example, these export options for exploiting any data you get in the great results after you ran any query. So yeah, edge the studio is another powerful option. And the coolest part of all these is that both SMS and Azure Studio are both free to use. So let's do some more stuff here. For example, who are backed, who serve a dashboard? Let's right click on Test, the B1, select, manage. And here now you can see the table. For example, you can edit the data, you can script this table is created. You can select top 1 thousand and so on. So that was a simple example of how you can connect to a sewer server instance using edge that our studio work with. Your database has run queries export data. You can do much more. But in the rest of the demos in their course into the next section, I will be using SQL Server Management Studio. Because by the time you can achieve the same results using both tools. I selected sewer server management studio scenes. Personally, I'm more familiar with SMS. I have been working with SMS for over 15 years. So yeah, I can say that it's my favorite tool. So we will be working with SMS, further data science demos in the rest of the course. Okay, let's go back to the lecture and lets proceed with a recap of what you have learned. In this section. 14. Section Recap (Development Tools for SQL Server Machine Learning): Now let's recap what you've learned in this section. In this section we talked about the three microsoft client development and administration tools for silver. Silver. To this end, we talked about SQL Server Management Studio and the Azure Data Studio. We talked about what you can do using these tools. What are the installation requirements? We so live demonstrations where we downloaded the tools and installed it on our virtual machine test environment. And then we connect it to SQL Server installed in a previous demo. And we saw how we can navigate within SQL Server using these two great free tools provided by Microsoft. 15. Section Overview (Doing Data Science with SQL Server Machine Learning Services): By now you have learned what did the sciences well, can you do with the science? We've learned about data science in relation with machine learning and artificial intelligence. To learn about a data science project lifecycle. Then we talked about she was third machine learning services about what they are, what you can do them about the installation requirements. We saw a live demonstration as well as we've talked about Microsoft's free development administration tools for SQL Server and Azure SQL database, that is SQL Server Management Studio known as Ss, as well as Azure Data Studio. In this section, we're going to talk more about doing data science. We see we're server. So that's why the title of this new section piece, doing data science with super several machine learning services. Let's see what we are going to learn in these exciting new section. First, we are going to talk about the Python and R packages included in Suez haven't machine learning services. Note that in SQL Server 2018 years also support for the Java language. But in this section, we'll be focusing mainly on Python and R. And that's why we're going to talk about the Python packages included in see what server machine level. Moreover, we're going to see how you can write and run your first Python scripts in SQL Server. Similarly, we're going to see how you can write and run your first auras creeps in SQL Server. Finally, we're going to train and score a model in SQL Server machine learning services know that these model will be a simple one just to showcase how you can perform the whole process of classifying your data, designing a motel, tearing the motel, and running the modal score in a motel in Arthur together predictions based on your data input. Let's begin with a lecture where you are going to learn more. The Python packages included in C where several machine learning services. 16. Python and R Packages Included in SQL Server Machine Learning Services: In this lecture, we're going to talk about the Python and R packages included in sewer server machine learning services. Regarding open source packages, Note that the most common open source Python and R packages for machine learning are pre-installed in SQL Server machine learning services. You can get the list of all installed Python packages by running the script. As you can see, we call the SP execute external script systems TO procedure, which allows the server to run the below Python script. The script returns the list of the package names and versions of all installed Python packages on your environment. You can learn more about the Python machine learning packages in SQL Server by following the below link. Now, in order to get the list of package names and versions for our packages, you just run this script. Again, you are using SPX acute external script, but this time you are executing an R script again from within SQL Server. And these Asquith returns the package names and versions. You can learn more about the R packages in SQL Server by following the below link. Now besides the open source Python and R packages, let's take a look at the additional packages included with SQL Server machine learning services. Let's start with the Python packages. One such a package is ribose calibrate. These packages helps you implement their transformations and manipulation, statistical summarization, visualization, and many forms on modelling. You can learn more about this package by following the below link. Another Python package including sewer sermon children's services, it is Microsoft ML. This package at machine learning algorithms to create custom models for text analysis, image analysis, and sentiment analysis. On the below link, you can learn more about these package. Notice talk. There are packages included with SQL Server machine learning services. Once such a package, these are Ivo scale r. This is the primary package for scalable are it allows data transformations, manipulations, statistical summarization, visualization, and many forms of modelling. Also automatic workload distribution for parallel processing can be performed via these package. On the below link, you can learn more about revel scalar. Microsoft ML 4R is another package included in SQL Server machine-level serves. This package adds machine-learning algorithms to create custom models for text analysis, image analysis, and sentiment analysis. Again, you can learn more about this package by visiting the below link. Another R package is included with superset of machine learning services. It is all up are like the name implies. These package includes are functions that can be used for MDX queries against the SQL Server Analysis Services all up coop. So via this package I've shot you combine the power of machine learning along with powerful sewer Server Analysis Services, OLAP cubes. On the below link, you can learn more about this package as well. Moreover, another R package, including sewer seven machine-learning services. It is SQL QTLs. This package provides a mechanism to use R scripts in a TC crystal procedure. Then you can register that stored procedure within the database and around the store procedure from an Arc development environment. On the below link, you can learn more about SQL ROTC. Another package that is included, Microsoft are open. These actually then has distribution of from Microsoft. It is an open source platform for statistical analysis and data science and a 100% compatible with our. You can learn more about Microsoft R o pen on the below link. Now regarding installing new Python packages, you can't install additional Python packages using Microsoft's SQL MLE QTLs utility. Confine these utility on GitHub by following the below link. You can learn more about the process of adding new Python packages, NC17 machine learning services by visiting these links. Now about this, installing new R packages in sewer server machine learning services. Again, like in the case of Python packages, you can install additional R packages using Microsoft's as credible utility, utility. Again, you can find these utility on the belonged GitHub link. Also you can learn more about the process of adding new packages and sewers ever machine-learning surfaces. By visiting the below link. You can learn more about the packages including SQL Server machine learning services by visiting DC link. In the next lecture we're going to start writing and running first Python scripts in sequel server. Know that it would be helpful to have Python and our knowledge for creating and running Python and laughs creeps within the context of this course. However, I intentionally did not include these as a prerequisite for the course because I'm just going to write some very basic scripts in order to show the mechanism of SQL Server machine Dennis services. In addition to that, learning Python or R, it is not within the scope of this course. Let's proceed to the next lecture. 17. Writing and Running Your First Python Scripts in SQL Server: In this lecture, we're going to talk about writing and running your first Python scripts in SQL Server. Let's talk about the prerequisites first. First of all, units, your server machine learning services. Also, you need the Python language installed on the same environment. Note that the regarding these two prerequisites we're talking about in database, Sequel Server, machine learning services. And that's why you need to have all of these are the same place at this point, I would like to mention about Python knowledge that it is helpful, of course, but not a prerequisite for this course, because we will just write some simple scripts. So the main idea for running Python scripts from sequel server machine language services. It is to call the system stop procedure SP execute external script. However, it is important to note that in SQL Server, prior to running their pups stop physician unit to enable external scripts enabled using SP configure. So if you remember in the previous demo with the development tools for superscalar machine learning services, like as his MS and Asian at the studio in the demo where we installed as SMS enabled these flat on the surface area of sewer server. That is, I said, the value one for external scripts enabled and around their configure command. So by doing that, actually, now I'm able to run SP execute extent no scripts. You can learn more about the process of creating Python scripts and using them via the web server machine learning services on the below link. Now let's talk about running a simple hello world scripts in Python from within SQL Server. So in order to do that, you need again to run SP executives and the script. So let us discuss about the below calls. You can see we call SP execute external script. We define the language of the script to be executive, that is Python. Then we define the script. In this case, it is very simple because our output dataset, it is the same like the input dataset. And you can see here we specify the input data. So in this case, we select one as sample value and we return via their results sets command the Hello world back to the user. So if we execute these, are going to get a hello world back as an output of these python script ran from within SQL Server via Super saver machine learning services. So you can see here we execute the script and whether we get is hello world. We get the value one and we get the Hello World as a column name. But know that this is one of the many ways of getting Hello world using Python scripts from within sewer server. Now let's proceed to end demo and around some Python scripts in SQL Server. So this is a set of Python scripts for testing superset of machine learning services using Python. So the first script, it is what we talked about earlier in the lectures, where if we run this, we'll get a list of all the available Python packages in my environment along with their version numbers. Again, you can see the structure is always the same, so you execute. Sp execute external script. You define the language, in this case it is Python in Sao. Other examples I'm going to show in the next lecture will be r. Then you define the script. Also know that for every parameter you use the Unicode sign here. So I Python script is this one which will return Python package information. So let's execute this. And you can see below, all of these are the Python packages along with our veterans currently installed on mine environment. For example, here you can see scala b. It is one of the additional packages including sewers, had a machine-learning services. And in this way we can verify that this library is here as well. It is available for use. Okay, let's proceed with the rest of the screen. There is a set of scripts I prepared. These are very simple scripts in the Python language. Not that these scripts are not machine-learning scripts. Where I went to see a full data science and machine learning example later in subsequent demos. But I just wanted to show the capability of running simple or complex Python scripts from within civil server using machine learning services. So for example, this is a simple Python script where I'm creating here you can see an array of strings with a virus, car or motorbike and train. And for example, here I call print via call list, and then I call previa can list 0. So the first pin command will display the contents of the entire array. And the second one, we'll just display the first one. So if you execute this script on a Python development environment, you will eventually get the same result like the one you will get. Here. We seen SQL Server. Let's execute this. You can see the output. The first line, we get the values of the array, and then we get the first element as well. You can change that and try again, translate it, for example, the second element. So you can see the second line now displays motorbike. And so one simple example of a pyro scream executive via Sequel Server is we create an array of strings again with the cargo bike and train values. And then using a loop, in this case, for loop, we display its contents. So let's execute this. And you can see the output is car model pie entrain. Let's run another loop example. So here I specified an integer with the initial value 0, another integer with the value five. And then with a while loop, I'll display the current value each time of the i integer. So you can see a displayed from 0 to four. Now, let's retrieve some table contents via python. And this is the magic of in-database Python suppose. So in this script, what I'm going to do, I would be using the Adventure Works 2017 sample database. And I will use these select query as my input data. So I will select the name and cost, select columns from adventure work 2017 production schema location table. And then I will display the results of this query by passing my input data set. This is actually the results of this query is the output dataset. And with the result sets command, I will display the two columns, that is name. I specify again the data type and cost rate float. So let's run this. And you can see my script was successful. So this is a very simple example of how you can actually use Python in database with your data currently stored in the same environment in SQL Server. Ok, let's proceed with the rest of the lecture. So in the next lecture, in a similar manner like what we did with Python, who are going to ride around your first R scripts in SQL Server. 18. Writing and Running Your First R Scripts in SQL Server: In this lecture, we're going to talk about writing and running our first, our scripts in SQL Server and not about the prerequisites. First unit, she was heavy machine learning services along with our language installed on the same environment. Again, know that we are working with in-database machine learning services in this course. So that's why you need to have everything installed on the same place along with you or she was Harvard data platform. Now about having our knowledge in order to follow this demo, our knowledge is helpful. But notice strict prerequisites for the course. Because we will just write some very simple R scripts, confined similar scripts all over the internet. In beginner tutorials, you tease everywhere. So again, if you do not know, are still, you will be able to follow the flow of the demo. Now again, the main idea for running our scripts from India division sequel server is just like for Python, for example, just call the system store procedure SP, execute external script and pass the required parameters. You can learn more about creating and running our scripts in sequel server via sewer server machine learning services by following the below link. Now let's see a Hello World script in R from within our server. So you can see in this code snippet, again, you call SP execute external script. You define the language parameter. These time you specify R is the language. Remember in the previous lecture we defined Python. But this time it is our, again, our script is outputted assets is actually our input dataset. Now that in this script parameter you can write any script you like, because there is support for running our via the call to the SP execute external scripts stored procedure, along with defining the language are. Then you specify the input data. In this case again, select one S Hello, and we specify our results. It S Hello World integer. So these will print screen the value on along with the column name hello world. You can also create other HelloWorld scripts in R from which insecure server. This was just a simple example just to get the main idea of a very simple script executive via SQL Server. Okay, now let's jump to our next demo and around some ASP scripts in sewer server via she was ever Machine Learning Services. So back on our superstar back test environment. First I'm going to run the script for getting the list of our packages installed on my environment. So this is a script, if you remember from previous lectures. So you can see here, these are the variable R packages, my environment. Let's search from one of the additional packages Microsoft includes in superset of machine learning services regarding R. So you can see here you have Microsoft ML, Microsoft are, as Creel argued, tubes and so on. So everything is here and ready for us to use it. Now, this is a very simple script I prepared. It is similar to the other scribbler. Prepare for Python with a car or motorbike entrain string values in an array. In this case, it is called a vector. So I'm specifying this array and I'm printing its contents on screen. So again, with SP execute external script, I define as language. The language as a script and defined the script. And let's run it. Now though, you can specify other parameters as well, but it is not required for this very simple example. I'm just showcasing how easy it is to run our code from within SQL Server via sewers, have a machine-learning services and more exclusively via the SPX acute external script systems for procedure. So you can see the output of my co-teacher is car, motorbike and train. So executional was successful. Now similarly like we did for Python, let's run a for-loop. So the for loop in the R language, it is FAR and within parentheses you specify the counter. You specify the number of stars in the number it finishes, and within brackets you specify that quote to be repeatedly executed. So again, with SPX equity external script language are, and by specifying what I early described within the script parameter. Let's run this and see the outcome. Normally it should print on screen the values one to five. And you can see each time print run number which is correct. In each repetition, it prints a number, a number. So you can see 1-2-3-4-5 here. Similarly, you can change that. You can change this code. So by the time it he's still honor code, it will be successfully executed. So you can see now it counts from one to ten and so on. Now let we saw earlier in the python demo, again, I would like to show how you can combine R and SQL Server tables and other deliveries objects. So in this case, like I didn't Python example, I'm going to select the name and cost are columns from Adventure Works sample database I have on my test sewer server instance. And I will select from production schema the location table of these two columns. And this is my input parameters. So I will pass this input parameter as my output dataset within the script parameter. So using the results sets clauses, I will display two columns. The first one is a name which is varchar 50, and the other one is cost rate, which is float. I will display this on screen. So let's run this. So you can see it retrieved 14 rows. If you would like to double check, you can just run the code that actually against the web server. And you will see that you will get the same result. So you see, this is what you get, again, 14 records. Okay? So that was another simple example of how you can call our scripts directly from within SQL Server using the powerful sewer several machine learning services. And now let's proceed with the rest of the lectures. In the next lecture and live demonstration, we're going to see how we contain a score a modelling C will serve a machine learning services and what would be using the iris dataset. Let's talk more about it in the upcoming lecture. 19. Train and Score a Model in SQL Server Machine Learning Services: In this lecture and demo, we're going to train and score a model in sequel server machine learning services. We are going to use a well-known example in order to show the basic steps for modelling as small dataset and generating predictions. To this end, we are going to use the Iris dataset. This dataset is a multivariate dataset introduced by the British statistician and biologists Ronald Fisher in contains 150 records and 50 samples from each of three species of the iris flower, that is iris setosa, iris virginica, and ij's vesicles. The features measured by these dataset is the length and the width of the pulse and petals in centimeters. You can learn more about these dataset by visiting these two links. This is a picture of the beautiful iris flower. Just to get a look and feel on then dataset onto which would be working on using modelling via Sequel Server machine learning services, and generating species predictions based on the above-mentioned dataset. The goal of this exercise is by using SQL Server machine learning services to create the model based on the iris data set, to train the model, and to use the model for generating predictions that is actually predicting the iris flower species based on the four features measured. That is length and width of the sepal and petal. This demo is based on the below, MS. Dogs, octagon and Quickstart. So you can find more information about these exercise on the below link. Grades. Let's jump to the demo. So here we are back on our test environment. And we start this demo by first creating the database onto which you are going to further create tables. And though the necessary objects for performing the iris a demo. So first we create the iris SQL database. Okay, this is the simplest step, this demo. And we proceed to step two where we'll create a table for storing the iris dataset data. This table consists of seven columns. The first column is the ID column, which is the primary key. It is an identity column. Then we have the sepal length column, which is float. The same bandwidth. Again, float type petal length, and petal width. Again at these two are also float the types. Then we'll have the species, which is the description of the species and the species ID. So let's create the iris underscore data table. Let's refresh our trivial here. And you can see we have the iris SQL database. And if has table called Iris underscore data, which of course, at the time being it is empty. Now let's proceed to the third step, which is creating another table onto which we are going to store the model. So again, we switch contexts to our iris has current database and we will create a table called Iris underscore models. Now, this table has two columns. The first column is model underscore name, which is varchar 50, and it is the primary key. This is the name of the module. And the second column is the actual model, which is VD binary max. Let's create this table as well. You can see table created. Now let's proceed to Step four, where we're going to create a stored procedure for getting the data via Python script. So this top procedure, it is actually a call to a Python Script via the SP execute external script systems store procedure about which we talked in previous lectures. And this is a Python script. You can see here. It specifies the language Python. And the script imports data from these catalogs. So you can see that it imports the iris dataset. Also, you can see that there is no input data and the output data, it's actually the iris underscore data. And as you can see in the Python script above, the iris underscore data is actually the table that contains all the data about the Iris dataset, which is the retrieved via the load Iris method color. Now using their width result sets clauses, it returns sepal length, sepal width, petal length, and petal width, as well as the species name and species IT. So let's create the stored procedure. And yes, a stored procedure, get Iris dataset was successfully created. Now let's proceed to the next step, which is the actual phase where we populate our table with the iris data set data. To this end, again, we switch context to the iris SQL database. And then we call the stored procedure get Iris dataset, which is the stored procedure we created earlier and outputs their retrieved data. And using an insert into statement, we import this data into our iris underscoring data table. So let's run this stored procedure call along with an insert into statement. So now what actually happens is that she was ever machine-learning services. Take these stored procedure call, which includes a call to a Python Script for loading the iris dataset data is then retrieves the results of these call. And it stars the retrieved data into our SQL Server table called Iris underscore data. And this is one of the benefits for in-database machine-learning. You have everything at the same place, okay, you can see the message 150 rows affected. And these are the contents of the iris dataset. Let's proceed further and check the table contents now. So you can see this is our data. You can see that it has 150 rows. And just to get a better idea about the data, we have the sepal length, sepal width, petal length, and petal width, and the observation of the species name. So you can see the first 50 records contain observations regarding the sepal length, width, and petal length and width for the species setosa. Then the second set of 50 records contain observations about the legacy color species. And the last 50 records contain observations about the virginica iris flower species. Okay, we are ready to proceed to the next step where we are going to generate and train our model. So again, we switch context to the IRS as credit database. And again, we are going to create another stored procedure, this stuff procedures called generate underscore, iris underscore model. Now, this stored procedure again makes another Python code. And Let's talk more about it. So you can see here we specify the language Python, and then this is our Python script. It impose the library P goal, which is the machine learning library, which would be used for training our model. So you can see here that trend model is what we get as the output when passing our data in the peak oil library for training the model. So our input data are sepal length, sepal width, petal length, and petal width, as well as the species entity from the iris data bone. And we passes parameter the ten motel var binary max S output. So actually what we get is if our binary max object, which is the trained model. So let's create a stored procedure. And we can see it was successfully created. And now it's time to regenerate and train our model using the stored procedure. So first we declare a model variable of the type of our binary max with a carrying new model name. And we set the model name is and I've bias. Then we execute the stored procedure and we define our model variable as the output. Next, we clean the iris models table, which if you remember from the previous steps, it is a table used for storing the trained model. And we insert into this table our model Alawi, specifying the model name. So let's do that. So now it takes a little bit of time because it generates and train our model based on day 150 records currently in the iris data table. So our model is ready and we can double-check by Queen the iris 100 car models table. So you can see this is the model name, naive bias. Along with the model, all is left now is to run our model. Because now the model is generated and trained. And along with running it will, we'll get predictions for what kind of species we will get when using these input data. So let's jump to the last step. We add a would be creating another stop procedure. Histo procedure is called subject species. It takes as unequal parameter the model name. And then using these model name, it searches in the iris underscore mothers table in order to get them model. Then again via the SP execute external scripts, system store procedure, it calls another Python script. Again, these Python script uses a SQL library and using the predict method and as input data, the data from the iris data table heater, it has the prediction. And the prediction contains three-year course. The faster I caught the JIT. The second one is the species ID, and the third record is their predicted species based on the input data for the species. So let's clear the store procedure. And let's run the prediction. Great. Now, in order to better understand the predicted data, we will also select all the records from the iris data in order to compare with our source data. So let's do this. So what do we have here? The first record set is the iris dataset of 150 are caught, and the second dataset is a predictor species. So what does this information mean? For example, we see that in the predictions dataset for ID-1. Id-1 means that we have these values for sepal length, sepal width, petal length, and petal width. The actual species is 0, which is the iris setosa. And the predictor species is again the same. Again, for the rest of the course, we see the same. So the predictor species for the first 50 input records is the exact species like the one observed and recorded in the dataset. Now, these changes a bit when we go to the RefSeq color species. Well, we can see that for IT 53. Even though the record of species is one which is grassy color, they predicted species is species IT to the virginica species. So what does this mean? This means that based on the data of a record 53, which is 6.9 for sepal length, 3.14 several width, 4.94 petal length, and 1.5 for petal width, you might get not adversely colour species, but instead you might get the species with ID two, which is the virginica species. So this is the prediction we get for a record with ID 53. Again, you can see that there are not so many variations, so many differences between the species ID and the predictive species. And that means that the observations are very valid indeed. So this is very basic but excellent example in order to understand how data science and machine learning can be used for predicting different useful information based on data sets that are used for creating and training the model. So what we did in this demo is that we used the iris dataset in order to create and train a model. Then we run this model in order to generate predictions for us and compare the predictions with the actual data. We can see that in the majority of cases, the predictions we are the same like the actual data. In this case, the iris flower species. In some cases, the protected species ID was different than the actual species ID. And that means that, that specific data, it is highly possible that would give these different species when compared to the original data. Great, let's proceed with the rest of the course. In the next lecture, we're going to review what you've learned in these interesting section. 20. Section Recap (Doing Data Science with SQL Server Machine Learning Services): Now let's review what you learned in this section. That is the section titled doing data science. We see what server, machine learning services. So in this section, you've learned about the Python packages included in sequel server machine learning services. We've talked about the open source packages included as well as other packages for Python and are introduced by Microsoft and she was server machine learning services. Moreover, we talked about how you can arrive and run your first Python scripts and she were server, as well as how you can start writing and running your first asked creeps in SQL Server. Moreover, we've talked about how you can train and squatter model in SQL Server machine learning services. And we saw an interesting demo where we used the iris flower dataset in order to create a model, train the model and around the model, along with generating predictions for iris flower species based on the data input. Finally, we compare the predicted species with the original species included in the data set. Last but not least, you've learned many things in this section, not only theory, but also in practice. Since the section included many live demonstrations. 21. What Have you Learned in this Course?: Now let's recap what you've learned in this course. And this course, you've learned about data science. What is data science? What is its relation with machine learning and artificial intelligence? What are the similarities, what are the differences, and how these three areas are interconnected in order to achieve the goal of having near human intelligence. Moreover, we've talked about then data science lifecycle, which is the lifecycle of a data science project, where we've talked about the different stages in the implementation of such a project. Then we talk about suicide for machine learning services. To this end, we've talked about sewer server. Well it is and what you can do by using it. Then we talk about suicide or machine learning services, what they are and what they offer. And we've learned how sequence seven machine lemon services extend the functionality of the powerful sewer seven dare platform and what you can achieve by using sewer server machine learning services. Then we show a live demonstration. You've learned how to install SQL Server along with super server machine learning services. Then we've talked about the development tools for civil several machine-learning. To this end, we've taught about Suez Sever Management Studio, also known as has SMS. This is a well-known free Microsoft tool for developing and administering SQL Server and Azure SQL database or so we talked about another great free tool by Microsoft for working with sewer Server and Azure SQL database, that is Asia to the studio. And we've learned that Azure Data Studio is cross-platform. That is, it does not only run on Windows pad, it can also run linux environments. Last we solar demonstrations of how you can download, install, and start using these two great tools. Then we talked about how you can do data science with SQL Server machine learning services. To this end, we told about the Python and R packages included in SQL Server machine learning services or so we talk about how you can write and run your first Python and asked creeps in SQL Server using SQL Server machine learning services. Moreover, we saw how you can train and score a modern sewer server machine learning services. And for this purpose, we use the Iris dataset, where we imported the data of the iris dataset, created a model, trained the model, and run the model for generating predictions about the flower species based on four types of information. That is the sepal width, length, and the petal width and length. And via this simple yet very interesting, an excellent example, we show electrode a glimpse of the power of data science and machine learning using SQL Server machine learning services. And that is why this section also includes many interesting live demonstrations.