SQL Learn SQL 101, 202 and 303 all in 1 Course. 10X Learning

I remember when I created my first database it seemed like a daunting task, but it doesn't have to be. I created my first database in a Enterprise scenario. For the purpose of this course just think of it like you are creating a file folder. You can't hurt anything so lets play.

In SQL Management studio right click on the "Databases" icon and click "New Database"

Lets set a name and we have our first Database Created. Now we have to start creating some tables.

Right click on the tables Folder and lets create our first Table.

Keep on following along and we will have all the tables created.

In this part, we will learn how to insert data into tables [See Lecture 4 for review]. This 8-minute video will guide you to indicate certain queries using SQL Enterprise Manager. We identify 4 major columns in our table, the ID, description, cost and price. In order for you to do a faster execution, the INSERT INTO can provide you with a template wherein you can set some VALUES for each column.

Inserting Data into Tables

This is probably the safest way to experiment with data. When you insert data into a table, it is like breathing life into your database. Without data your database may as well not exist.

Knowing your data is the first rule to Databases. You will hear administrators talk about this all the time. If you don't know your data then you have no use in the database and you will probably do more harm than good.

In this section I show you how to insert 1 record and also multiple records at one time.

Play the video and you’ll discover more exciting short cuts for your convenience.

Your basic Select statement is like the road down a never ending path of knowledge. The whole idea behind the select statement is you are asking the Database a question. In this case the question you are asking is "What data do you have that meets these requirements?

The SELECT statement will help you retrieve data results determined by your instructions by activating this declaration. This SQL tutorial will help you through with different strategic ways of using this statement, from simple to complex ways on how to extract your data sets from your table.

If you think about it like a search function. This is what I am searching for, what do you have that meets these requirements.

Select <- Is like the search command

* <- This is kind of like What data do you want to display

FROM [Table] <- is like the folder location you are searching from.

Where FileName = 'xyz' <- this is what information you are searching for

In this case, we will learn how to identify and discuss the anatomy of a SELECT statement.

This syntax below will select specific columns from your data table.

SELECT col_name, col_name
FROM table_name;

While, this SELECT statement below syntax will select all your data queries from your table.

SELECT * FROM Customers;

Anatomy

Select <- Is like the search command

* <- This is kind of like What data do you want to display

FROM [Table] <- is like the folder location you are searching from.

Where FileName = 'xyz' <- this is what information you are searching for

Sub-queries

Sub-queries can be on SELECT statements or on JOINTS. Sub-queries by definition is a query that is nested inside different SQL statements such as SELECT, INSERT, UPDATE and DELETE embedded in the WHERE statement. It is sometimes called as inner query and the one that contains subquery are known to be outer query.

We have to think of sub-queries as nested Select statements. A sub-query is a precision tool to simply pulling out exactly what data you want in the select statement. It is a precision instrument that is the first step into becoming a SQL Pro. A lot of people make the mistake of learning up to sub queries and then never taking another step. But we are going to teach you way more than this. If you are a crystal reports developer you will get a lot of use out of sub queries.

By testing our data, let’s proceed with this portion of our class wherein we will be able to learn how to use and manage our window in our database. We will be doing the T-SQL activity for our purchase order inorder for us to compare or join data queries.

The CASE expression has two formats: [https://msdn.microsoft.com]

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.
• The searched CASE expression evaluates a set of Boolean expressions to determine the result.

The Update Statement

Have you ever had your home address in 1000 documents on your computer and you wish there was an easy way to change it in all those documents. Well if those documents were stored as data in a database we could definitely help you with that problem.

Basically the update statement is a way to change data in the database. you can change a value from one thing to another using an update statement.

Update statement is one of the most helpful statement in SQL but at the same time, you need to be very specific and careful on using this statement for it can eventually ruin your database. Use this statement with a WHERE clause, this is to identify your data queries properly. It manages your data by modifying a specific data sets from your table/s, multiple set of queries, down to the whole data sets of your table in a snap. Cool, right?

See Update syntax below.

UPDATE table_name
SET col=val1, col2=val2, col3=val3…
WHERE some_col=some_value;

The Delete Statement

Have you ever had a girlfriend or 20 you wanted to remove from your memory :). If you have and your brain was a database accessible through SQL you could solve that problem right now.

The Delete statement removes data from a database never to return unless you insert it again.

DELETE statement is powerful yet one of the most decisive statement in SQL. So see to it to check your all your data records first, decide the unwanted queries before running the DELETE statement.

We’ll be giving you an example on how to delete a certain data from your table. Hit this play button and we’ll discuss it to you.

The Where Clause

This part of our SQL tutorial we will discuss another clause, the WHERE clause. This is used to identify the specific records by filtering and using indicated condition. If the given condition is satisfied, then it will provide you with a return value.

The where clause is the single most used part of a SQL statement. It is the brain of your statements. It is important to understand how to form a good where clause and that's why we are revisiting it here.

Inner Joins

Inner joins in SQL. This keyword will help you select all rows or specified rows from 2 or more tables and displays it as long as they match together else no data will be retrieved. This complex idea will eventually help you manage your database properly and when a situation arises it comes in handy.

We talk about how to take data from 1 table and data from another table and relate them to each other. Basically with an inner join you get to say

Take Table 1

and Take Table 2

If table1.XYZ = Table2.XYZ then these 2 records are related and lets connect them together.

In this lecture I show you how this works.

Now after the quiz, Let’s proceed with Left outer joins. We know now how Inner joins works, what it means that Left outer joins works the same conception but in a different method. Left outer joins returns row/s of data sets from the left table, matching rows in the right else the data will be NULL.

Go and check our example.

Group by Clauses in a SQL Database goes with a SELECT statement. It is used to identify specific groups of data sets from our database. This clause temporarily creates table to discover other sets of groups arranged based on this condition you provided to retrieve a certain data results. For a clear example, play this video.

Another SQL function that we will discuss in this course is the Aggregate Functions. This function performs calculations on a specific set of values and returns a value. The following listed below are few examples of aggregate functions that we can go and try it out.

• MIN
• MAX
• AVG
• COUNT
• SUM
• And etc.

In this instance, we will show you how TO CREATE, ALTER and DROP. The CREATE will produce view, ALTER is basically same as updating the view while DROP is like deleting and it can remove indexes, tables and databases.

SQL UDF stands for User-Defined Functions, its purpose is to specify acceptable parameters, do mathematical calculations and definitely return a value. In this case, we’ll pass strings and retrieve strings by using our sample database in a faster execution.

This video will provide you with a solid example on how to set data values and returning them using the concept of UDF.

Scalar and Table differ in terms of their return value, scalar retrieves 1 value while table retrieves all data tables.

Subquery can be used with a JOIN process to generalize our data. We will able to learn how to maximize the usage of our database by using a Subquery that temporarily returns a table and is given with an alias for us to use it on the outer select statement.

In this case, we will use the inner join in our database of products pull out the prices and purchased quantities and purchased IDs, orders from our sub-query and do some math.

This video will show you how to create an In-Line Table UDF, then naming the UDF and based on the given sub-query with some restrictions on the parameters.

We could change our subquery with UDF but in this scenario sub-query is more powerful that UDF unless if you decide to hardcode the User-defined functions.

Views, is one of the powerful tools that we can use in the SELECT statement. The data fields in the view are actually fields from one or more tables that we can reuse over and over again.

Stored procedures are the ability to pass parameters and it can store templates that handle requests in order to accept one or more parameter values.

If you want to run the stored procedure you need to run the exact statement.

This video will show you how to use stored procedures by doing some increase in the cost of the given products. [from 10% to 20%], so sit back and learn more about it.

Triggers on database will help you manage your queries. Here we will show you how to insert snippets. Snippets are actually small sets of reusable codes that you can edit and incorporate to a larger module of codes in our database. Triggers execute in a manner that you can do some transactions and reflect it on to creating SQL, alter SQL and drop SQL.

There are many ways to do it to a point that we’ll extract data on paper towels and verify logs base on the data’s time stamp as an example.

We will show you how to use templates to store SQL with no sweat. It is where you can add a few of your scripts that you can reuse anytime. SQL provided you some templates already, but if you plan to add your personal folder and template of scripts, follow the steps we provided you in this video.

Thanks for watching.
Let us know if you have any questions.