Database Development and Design Week 7: Lab 6 Walkthrough

Lab 6 instructions: (Copy and paste in your SQL Server)

use G0022211111;

–1 run the follow query. How many records are in the table?

Select *
from Cust_Orders;

–2 run this query below. It will enter 3 new records into your table

insert into Cust_Orders
values (1500, 908132, 665, ‘Cash’, ‘2018-03-08 00:00:00’, ‘Electronics’),
(1501, 876661, 128, ‘Credit Card’, ‘2016-05-18 00:00:00’, ‘Toys’),
(1502, 732027, 785, ‘Check’, ‘2018-03-08 00:00:00’, ‘Furniture’)

–3 run the follow query. How many records are in the table this time?

Select *
from Cust_Orders;

–4 Run the following queries. What happened to Order_ID 1502
begin transaction;

delete from Cust_Orders
where Order_ID = ‘1502’;

select *
from Cust_Orders;

–5 Run the following queries. Now what happened to Order_ID 1502

Select *
from Cust_Orders;

–6 Run question 4 again, this time add a commit statement.
–What happens to Order_ID 1502 now

–7 Try using Rollback. Can you undo the delete statement?

–8 Using question 2 as a guide, insert a new record, order_id =1600
— Run a Select * query to see if the record is in your table

–9 Adding a Begin Transaction statement, insert another row
— This time Order_ID 1601. Run Select * to confirm

–10 Try either a commit or rollback command – What happened?

–11 — Turn the query below into a View
Select s.Stud_NM as [Student Name], s.Start_dt as [Start Date], T.Subject, as [Tutor Name]
from Student as s
join Tutoring as t
on s.Stud_ID = t.Student_ID
join tutor as t1
on t.Tutor_ID = t1.Tutor_ID

–12 — Run a query from the view you just created where
——-Subject does not start with the letter G

–Bonus Question—-
–13 — Run a query from the view you just created where
——-tutor name does not end with the letter b

Here are the Excel files needed to create the tables:


Link to lesson with video for uploading Excel files into SQL Server:

What is MLOps (Machine Learning Operations)?

Machine learning has revolutionized the way businesses operate by enabling them to make data-driven decisions. However, building, deploying, and maintaining machine learning models can be a complex and time-consuming process. This is where MLOps comes in – it streamlines the entire machine learning lifecycle and enables organizations to manage their models at scale.

MLOps, short for Machine Learning Operations, is a set of practices, processes, and tools that automate the end-to-end process of building and deploying machine learning models. The goal of MLOps is to bridge the gap between data science and IT operations, enabling teams to collaborate effectively and efficiently.

In this article, we’ll explore the key components of MLOps and how they work together to make machine learning more manageable and scalable.

Photo by Mahdis Mousavi on Unsplash

Data Management

Data is the backbone of any machine learning model, and it’s essential to ensure that it’s clean, properly labeled, and easily accessible. MLOps teams must ensure that data is managed effectively throughout the machine learning lifecycle, from collecting and preprocessing data to selecting appropriate features and training the model.

Model Development

Model development involves building and testing machine learning models using appropriate algorithms and techniques. This process involves selecting the right architecture, training and testing the model, and tuning it to improve accuracy and performance. MLOps teams need to ensure that the models are transparent, interpretable, and easily maintainable.

Deployment and Monitoring

Deploying a machine learning model in a production environment requires a different set of skills and tools than building it. MLOps teams need to ensure that models are deployed in a consistent and repeatable manner, using containerization or virtualization technologies. They must also monitor the performance of the models continuously, detecting and addressing any issues that arise.

Model Management and Maintenance

Once a machine learning model is deployed, it needs to be maintained and updated to ensure that it remains accurate and relevant. MLOps teams must manage the model’s lifecycle, version control, and document changes made to the model. They must also ensure that the models continue to function correctly as new data is introduced or the production environment changes.

Benefits of MLOps

MLOps brings several benefits to organizations that rely on machine learning models, including:

  1. Scalability: MLOps enables organizations to manage machine learning models at scale, making it easier to deploy and manage multiple models across different business units.
  2. Reliability: By ensuring that machine learning models are tested, monitored, and maintained, MLOps helps to improve their reliability, reducing the risk of errors and data breaches.
  3. Efficiency: MLOps automates many of the tasks involved in building and deploying machine learning models, freeing up data scientists and IT teams to focus on more strategic tasks.
  4. Agility: MLOps enables organizations to respond quickly to changing business needs, making it easier to build and deploy new machine learning models as needed.


MLOps is an essential practice for organizations that rely on machine learning to make critical business decisions. By streamlining the machine learning lifecycle and automating many of the tasks involved in building and deploying models, MLOps makes it easier to manage machine learning at scale. With MLOps, organizations can improve the reliability, scalability, and efficiency of their machine learning models, leading to better business outcomes and increased customer satisfaction.

What is an API and how to use one

An API, or Application Programming Interface, is a set of protocols, routines, and tools for building software applications. It allows different software programs to communicate with each other, enabling them to share data and functionality. APIs have become increasingly important in modern software development as they allow for the integration of multiple systems, improving the overall user experience.

Photo by Christopher Gower on Unsplash

APIs can be used to perform a variety of tasks, such as retrieving data from a database, posting information to social media platforms, or accessing the features of a third-party service. In order to use an API, there are several important concepts and steps to understand.

API Concepts:

Before diving into how to use an API, it’s important to understand some key concepts:

  • Endpoint: An endpoint is a specific URL that you send a request to in order to access a particular service provided by an API.
  • Request Parameters: These are pieces of information that you include in your request to the API. They specify the details of the service you want to access, such as the data you want to retrieve or the action you want to perform.
  • Response: This is the data that the API sends back to you after processing your request. Responses are typically in JSON or XML format and include the data you requested, as well as any additional information, such as error messages.
  • Authentication: Many APIs require you to authenticate yourself before you can access their services. This usually involves providing a token or key that identifies you as a trusted user.

Using an API:

Here are the general steps you would follow to use an API:

  1. Identify the API you want to use: Determine which API you want to use based on your desired functionality. There are many APIs available for various purposes, and you may need to research to find the right one for your project.
  2. Get API access: You will need to register for an API key or access token in order to use most APIs. This usually involves signing up for an account and providing some basic information about yourself and your intended use case.
  3. Read API documentation: Each API has its own documentation that explains how to use it. Read the documentation carefully to learn about the available endpoints, request parameters, and response formats.
  4. Make API requests: Once you understand how the API works, you can start making requests to it. This typically involves sending HTTP requests with specific parameters and receiving JSON or XML responses.
  5. Parse and use API responses: After receiving a response from the API, you will need to parse the data in order to use it in your own application. This may involve transforming the data into a different format or extracting specific pieces of information.

Example of Using an API:

Let’s say you want to use the OpenWeatherMap API to retrieve the current weather data for a particular location. Here’s how you might do it:

  1. Identify the API you want to use: In this case, you would use the OpenWeatherMap API.
  2. Get API access: You would need to sign up for a free account on the OpenWeatherMap website and obtain an API key.
  3. Read API documentation: The OpenWeatherMap API documentation includes a section on current weather data that provides the endpoint, request parameters, and response format.
  4. Make API requests: You would use the endpoint provided by the API documentation, along with the appropriate request parameters (e.g. location), to send a request to the API. The API would then send back a response containing the current weather data for the specified location.
  5. Parse and use API responses: You would parse the JSON or XML response from the API in order to extract the relevant weather data (e.g. temperature, humidity, etc.) and use it in your program. Most programming languages (such as Python) have parsing libraries built especially for JSON or XML responses.


API’s are great tools for interacting with data stored in websites. The use of an API is limited to what the developer has set up, but the trade off to those limitations is access to data without having to interact with the website manually. You’ll find that most major applications and webservice providers have an API available, although some may require the purchase of a subscription to access the data.

Supervised vs Unsupervised Machine Learning

Supervised and unsupervised learning are two of the most common approaches used in machine learning. While both aim to discover patterns and relationships in data, they differ in the way they are trained and the types of problems they are best suited for. In this article, we will explore the key differences between supervised and unsupervised learning, and the types of problems they are best suited for.

Image by Gerd Altmann from Pixabay 

Supervised Learning

Supervised learning is a type of machine learning where the model is trained on labeled data, meaning the input data is accompanied by the desired output. The goal of supervised learning is to learn a mapping from inputs to outputs, which can then be used to predict the output for new, unseen data.

Supervised learning is commonly used for classification and regression tasks. In classification tasks, the model is trained to predict a discrete class label for a given input, such as whether an email is spam or not. In regression tasks, the model is trained to predict a continuous value, such as the price of a house based on its features.

Supervised learning algorithms are trained using a labeled dataset, which is split into a training set and a test set. The training set is used to train the model, while the test set is used to evaluate its performance. The goal of supervised learning is to minimize the difference between the predicted output and the actual output for the test set.

Some popular supervised learning algorithms include linear regression, logistic regression, decision trees, random forests, and neural networks.

Unsupervised Learning

Unsupervised learning, on the other hand, is a type of machine learning where the model is trained on unlabeled data, meaning there is no desired output. The goal of unsupervised learning is to find patterns and relationships in the data, without any prior knowledge of what to look for.

Unsupervised learning is commonly used for clustering, dimensionality reduction, and anomaly detection. In clustering tasks, the goal is to group similar data points together based on their features, without any prior knowledge of the groupings. In dimensionality reduction tasks, the goal is to reduce the number of features in the data while retaining as much information as possible. In anomaly detection tasks, the goal is to identify data points that are significantly different from the rest of the data.

Unsupervised learning algorithms are trained using an unlabeled dataset, which is often preprocessed to remove noise and outliers. Some popular unsupervised learning algorithms include k-means clustering, hierarchical clustering, principal component analysis (PCA), and autoencoders.

Supervised vs Unsupervised Learning

The main difference between supervised and unsupervised learning is the presence or absence of labeled data. Supervised learning requires labeled data, while unsupervised learning does not. This difference has implications for the types of problems that each approach is best suited for.

Supervised learning is best suited for problems where there is a clear desired output, such as classification and regression tasks. It is also useful when the goal is to make predictions on new, unseen data. However, supervised learning requires labeled data, which can be time-consuming and expensive to obtain.

Unsupervised learning, on the other hand, is best suited for problems where there is no clear desired output, such as clustering and dimensionality reduction tasks. It is also useful for exploring and discovering patterns in data that may not be apparent at first glance. However, unsupervised learning does not provide a clear way to evaluate the quality of the results, since there is no desired output to compare to.

In some cases, a combination of supervised and unsupervised learning can be used. For example, unsupervised learning can be used to preprocess the data and identify patterns, which can then be used to train a supervised learning algorithm.

Popular programming languages for Data Analysts

While this list is not exhaustive, the languages listed below are commonly used by data professionals like analysts, data scientists, and data engineers.

Photo by Isaac Smith on Unsplash

SQL (Structured Query Language):

SQL is a language used to manage and manipulate relational databases, which are commonly used to store large amounts of structured data. It is considered essential for data analysts as it allows them to extract insights and information from these databases.


Python is a high-level programming language that is widely used in data analysis and data science. It has a large ecosystem of libraries and frameworks such as Pandas, NumPy, and Scikit-learn that are specifically designed for data manipulation, analysis, and modeling.


R is another programming language that is designed for statistical computing and graphics. It has a large library of packages for data manipulation, visualization, and analysis, making it an essential tool for data analysts.


SAS is a software suite that provides a range of tools for data analysis and business intelligence. It is commonly used in industries such as healthcare, finance, and retail, and is known for its ability to handle large datasets.


Java is a popular programming language that is widely used in enterprise-level applications and big data processing. Its ability to handle large volumes of data makes it an essential language for data analysts.


MATLAB is a programming language used primarily for numerical computing and visualization. It is commonly used in scientific research and engineering, but is also used in data analysis and machine learning.


Scala is a programming language that is designed to be scalable and efficient, making it an ideal language for big data processing. It is often used in conjunction with Apache Spark, a distributed computing framework for processing large datasets.


It’s worth noting that the specific languages used by data analysts can vary depending on the industry, the type of data being analyzed, and the specific job requirements. However, a strong foundation in SQL and at least one of the programming languages mentioned above is generally considered essential for data analysts.

SQL Intro – SELECT, FROM, WHERE, and Order By

Probably the most commonly used SQL keywords, Select and From are the minimum commands you would need to see data from a table.

Select Statement is used to tell the database what data you would like to see

The Select Statement is made up of the following parts:

If you are not in my class, but want to follow along, here is a data file you can import into SQL Server:

Instructions for importing the Excel File to SQL Server : Instructions