Installing SQL Server 2017 on Red Hat Enterprise Linux

In  this new training episode on SQL Server administration. I talked about how to install SQL Server 2017 on Red Hat Enterprise Linux.
 




Here are the steps:

1. Enable the Sql Server Repository

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo

2. run update and install sql server

sudo yum update
sudo yum install -y mssql-server

3. Configure Sql Server

sudo /opt/mssql/bin/mssql-conf setup

4. check server status

systemctl status mssql-server

5. open port 1433

sudo firewall-cmd –zone=public –add-port=1433/tcp –permanent
sudo firewall-cmd –reload

 




How to Enable XRDP On RHEL 7.4 Desktop

XRDP is the counterpart of RDP in the Linux world. RDP(Remote Desktop protocol) allows you to use the GUI rich environment of another computer.

In this tutorial I discussed how to enable XRDP on RHEL.




Below are the Steps to configure XRDP.

Step no.#1. Enable EPEL repository

rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

Step no. #2.  add nux repository

rpm -Uvh http://li.nux.ro/download/nux/dextop/el7/x86_64/nux-dextop-release-0-1.el7.nux.noarch.rpm

Step no #3. Install Xrdp and tiger vnc

yum -y install xrdp tigervnc-server

Step no #4.  Start the service

service xrdp start

Step no #5.  Check if the service is running

netstat -antup | grep xrdp

Step no #5.  Enable the service at runtime

chkconfig xrdp on
chkconfig vncserver on
or systemctl enable xrdp.service

Step no. #6  Open the firewall

firewall-cmd –permanent –zone=public –add-port=3389/tcp
firewall-cmd –reload

Step no. #7  Configure Selinux to allow xrdp

chcon –type=bin_t /usr/sbin/xrdp
chcon –type=bin_t /usr/sbin/xrdp-sesman




Querying SQL Server 2016 Part 3




In this episode we will complete the mini project to build a stock card for “Widewordimporters” Database. We will use join combine the data from different tables and union to merge the result set from different queries. Order by sorts the data by column positions in this demo.

Should you want to follow the examples in this training “worldwideimporters” database is found on this link.

to




Here are the queries that I have used.

— query from part 2
SELECT s.[StockItemID]
,[StockItemName]
,[OrderedOuters]
,[ReceivedOuters]
,[OrderDate]
,PO.[PurchaseOrderID]
,[LastReceiptDate]
,po.[SupplierID]
,[SupplierName]
FROM [Warehouse].[StockItems] s
join [Purchasing].[PurchaseOrderLines] pd
on s.StockItemID=pd.stockitemid
JOIN [Purchasing].[PurchaseOrders] PO
ON PD.PurchaseOrderID=PO.PurchaseOrderID
join [Purchasing].[Suppliers] SP
on po.SupplierID =sp.SupplierID
order by 1

I rearrange the select list

SELECT s.[StockItemID]
,[StockItemName]
,[OrderDate] as transactiondate
,’purchase’ as transactiontype
,po.[PurchaseOrderID]
,[OrderedOuters]
,[ReceivedOuters]
,po.[SupplierID]
,[SupplierName]
FROM [Warehouse].[StockItems] s
join [Purchasing].[PurchaseOrderLines] pd
on s.StockItemID=pd.stockitemid
JOIN [Purchasing].[PurchaseOrders] PO
ON PD.PurchaseOrderID=PO.PurchaseOrderID
join [Purchasing].[Suppliers] SP
on po.SupplierID =sp.SupplierID
order by 1

— copy the select list but use null if the data is not available. Start doing the Sales query
–starting again from the stock items

SELECT
s.[StockItemID]
,[StockItemName]
,null transactiondate
,’Sales’ as transactiontype
,null as [PurchaseOrderID]
,null [OrderedOuters]
,null [ReceivedOuters]
,null [SupplierID]
,null [SupplierName]

FROM [warehouse].[StockItems] s

— get the sales transaction

SELECT
s.[StockItemID]
,[StockItemName]
,pickingcompletedwhen transactiondate
,’Sales’ as transactiontype
,null as [PurchaseOrderID]
,null [OrderedOuters]
,null [ReceivedOuters]
,null [SupplierID]
,null [SupplierName]
,null as customername
,quantity as salesquantity

FROM [warehouse].[StockItems] s
left join [Sales].[OrderLines] so
on s.StockItemID=so.StockItemID

–select top 1 * from [Sales].[OrderLines] so
— get the customername

SELECT
s.[StockItemID]
,[StockItemName]
,so.pickingcompletedwhen transactiondate
,’Sales’ as transactiontype
,null as [PurchaseOrderID]
,null [OrderedOuters]
,null [ReceivedOuters]
,null [SupplierID]
,null [SupplierName]
,null as customername
,quantity as salesquantity

FROM [warehouse].[StockItems] s
left join [Sales].[OrderLines] sol
on s.StockItemID=sol.StockItemID
left join
[Sales].[Orders] so
on sol.OrderID=so.orderid

Add the sales quantity

SELECT
s.[StockItemID]
,[StockItemName]
,sol.pickingcompletedwhen transactiondate
,’Sales’ as transactiontype
,null as [PurchaseOrderID]
,null [OrderedOuters]
,null [ReceivedOuters]
,null [SupplierID]
,null [SupplierName]
,null as customername
,quantity as salesquantity
,customername
FROM [warehouse].[StockItems] s
left join [Sales].[OrderLines] sol
on s.StockItemID=sol.StockItemID
left join
[Sales].[Orders] so
on sol.OrderID=so.orderid
join [Sales].[Customers] c
on so.CustomerID=c.CustomerID

–combine the purchase and sales data using union. they must have the same select list

SELECT s.[StockItemID]
,[StockItemName]
,[OrderDate] as transactiondate
,’purchase’ as transactiontype
,po.[PurchaseOrderID]
,[OrderedOuters]
,[ReceivedOuters]
,po.[SupplierID]
,[SupplierName]
,null as salesorderid
, null as customername
,null as salesquantity
FROM [Warehouse].[StockItems] s
join [Purchasing].[PurchaseOrderLines] pd
on s.StockItemID=pd.stockitemid
JOIN [Purchasing].[PurchaseOrders] PO
ON PD.PurchaseOrderID=PO.PurchaseOrderID
join [Purchasing].[Suppliers] SP
on po.SupplierID =sp.SupplierID

union

SELECT
s.[StockItemID]
,[StockItemName]
,sol.pickingcompletedwhen transactiondate
,’Sales’ as transactiontype
,null as [PurchaseOrderID]
,null [OrderedOuters]
,null [ReceivedOuters]
,null [SupplierID]
,null [SupplierName]
,so.OrderID
,customername as customername
,quantity as salesquantity

FROM [warehouse].[StockItems] s
left join [Sales].[OrderLines] sol
on s.StockItemID=sol.StockItemID
left join
[Sales].[Orders] so
on sol.OrderID=so.orderid
join [Sales].[Customers] c
on so.CustomerID=c.CustomerID

order by 1, 3

add the ending inventory on the balance

SELECT s.[StockItemID]
    ,[StockItemName]
    ,[OrderDate] as transactiondate
    ,’purchase’ as transactiontype
    ,po.[PurchaseOrderID]
    ,[OrderedOuters]
    ,[ReceivedOuters]
    ,po.[SupplierID]
    ,[SupplierName]
    ,null as salesorderid
    , null as customername
    ,null as salesquantity
    ,null  [QuantityOnHand]
FROM [Warehouse].[StockItems] s
join [Purchasing].[PurchaseOrderLines] pd
on s.StockItemID=pd.stockitemid
JOIN [Purchasing].[PurchaseOrders] PO
ON PD.PurchaseOrderID=PO.PurchaseOrderID
join [Purchasing].[Suppliers] SP
on po.SupplierID =sp.SupplierID

union

SELECT
     s.[StockItemID]
    ,[StockItemName]
    ,sol.pickingcompletedwhen transactiondate
    ,’Sales’ as transactiontype
    ,null as [PurchaseOrderID]
    ,null [OrderedOuters]
    ,null [ReceivedOuters]
    ,null [SupplierID]
    ,null [SupplierName]
    ,so.OrderID
    ,customername as customername
    ,quantity as salesquantity
    ,null  [QuantityOnHand]
    
FROM [warehouse].[StockItems] s
left join [Sales].[OrderLines] sol
on s.StockItemID=sol.StockItemID
left join
[Sales].[Orders] so
on sol.OrderID=so.orderid
join [Sales].[Customers] c
on so.CustomerID=c.CustomerID
union
SELECT
     s.[StockItemID]
    ,s.[StockItemName]
    ,[LastEditedWhen] transactiondate
    ,’ending inventory’ as transactiontype
    ,null as [PurchaseOrderID]
    ,null [OrderedOuters]
    ,null [ReceivedOuters]
    ,null [SupplierID]
    ,null [SupplierName]
    ,null OrderID
    ,null customername
    ,null salesquantity
    , [QuantityOnHand]
    
FROM [warehouse].[StockItems] s
join[Warehouse].[StockItemHoldings] sh
on s.StockItemID=sh.stockitemid
order by 1, 3




Querying SQL Server 2016 Part 2




In this exciting Episode, I continue the lecture on querying Sql Server 2016. But in this episode you will not only learn SQL server but you will learn a lot of things. Inventory management, stock cards and basic accounting is a few more things that you will learn in this episode.




Here are the scripts I used for this training.
Step 1. Get the productid and name

SELECT [StockItemID]
,[StockItemName]
FROM [Warehouse].[StockItems]

Step 2 is to join stocks to orderlines (purchase order details)

SELECT s.[StockItemID]
,[StockItemName]
,[OrderedOuters]
,[ReceivedOuters]
,[PurchaseOrderID]
,[LastReceiptDate]

FROM [Warehouse].[StockItems] s
join [Purchasing].[PurchaseOrderLines] pd
on s.StockItemID=pd.stockitemid

Step 3 is to join stocks to purchaseorder (p.O.summary)

SELECT s.[StockItemID]
,[StockItemName]
,[OrderedOuters]
,[ReceivedOuters]
,[OrderDate]
,PO.[PurchaseOrderID]
,[LastReceiptDate]
,po.[SupplierID]
FROM [Warehouse].[StockItems] s
join [Purchasing].[PurchaseOrderLines] pd
on s.StockItemID=pd.stockitemid
JOIN [Purchasing].[PurchaseOrders] PO
ON PD.PurchaseOrderID=PO.PurchaseOrderID

— step 4 is to join stocks to purchaseorder (p.O.summary)

SELECT s.[StockItemID]
,[StockItemName]
,[OrderedOuters]
,[ReceivedOuters]
,[OrderDate]
,PO.[PurchaseOrderID]
,[LastReceiptDate]
,po.[SupplierID]
,[SupplierName]
FROM [Warehouse].[StockItems] s
join [Purchasing].[PurchaseOrderLines] pd
on s.StockItemID=pd.stockitemid
JOIN [Purchasing].[PurchaseOrders] PO
ON PD.PurchaseOrderID=PO.PurchaseOrderID
join [Purchasing].[Suppliers] SP
on po.SupplierID =sp.SupplierID
order by 1

Querying SQL Server 2016 Part 1




In this episode, I talk about querying SQL Server 2016.

If you have got your SQL server yet, you may download the installer from this link.

You can download the wide world importers database from this link.

Below is the query that I used in this video:
–return everything
select * from [Warehouse].[StockItems]
–query selected column
select [StockItemName],[UnitPrice], [QuantityPerOuter] from [Warehouse].[StockItems]

— multiply 2 columns in one
select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox from [Warehouse].[StockItems]

–sort by the column multiplication result
select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox from [Warehouse].[StockItems]
order by 2

–sort by the column multiplication result desc
select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox
from [Warehouse].[StockItems]
order by 2 desc

–select top 10 from the inventory
select top 10 * from [Warehouse].[StockItemHoldings]
— join the product information to stocks in the inventory
select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox , sh.QuantityOnHand
from [Warehouse].[StockItems] s
join
[Warehouse].[StockItemHoldings] sh
on s.StockItemID=sh.StockItemID
order by 2
— calculate for the costonhand
select [StockItemName],
[UnitPrice]*[QuantityPerOuter] as CostperOuterBox ,
sh.QuantityOnHand,
[UnitPrice]*[QuantityPerOuter]* sh.QuantityOnHand as costofinventoryonhand
from [Warehouse].[StockItems] s
join
[Warehouse].[StockItemHoldings] sh
on s.StockItemID=sh.StockItemID
order by 4 desc

— calculate the totalcost of inventory in the warehourehouse
select
sum([UnitPrice]*[QuantityPerOuter]* [QuantityOnHand]) as costofinventory
from
[Warehouse].[StockItems] s
left join
[Warehouse].[StockItemHoldings] sh
on
s.stockitemid=sh.stockitemid

 




Free New IT Trainings from Aware-IT.org




These are  the newest sets of free tutorials  tutorials from Aware-it.org

  1.  GIMP – Is on open source Graphic Imaging tools. If you want to draw or build images and stunning images you are going to like this tool.  You can access the tutorials from this link
  2.  Word Press is  an Open source content management system. If you want to write your own journal this is one of the tools that is right for you.  You can access the tutorials from this link
  3. Windows Applications, Tools and games – This module talks about applications, tools and Games that are there for you free. You can access these tutorials thru this link.
  4. .Net Core. This learning module is about developing applications for the open source environment. Please access this module thru this link 
  5. Oracle Database 12C. Oracle Database is an enterprise database management system. This application is used for storing data.  Access the Oracle database lenring module thru this link  .

For more traning, Visit us at https://www.aware-it.org

 

 

 

How to Monetize Your Website/Blogs




There are many ad provider to allow you To monetize your website or your blogs
For this article how ever we will be using adsense.

Step number #1. Sign-up to adsense.
https://www.google.com/adsense/

Step number #2. Create a Content
This could be a website or a Blog you can make use of your favorite topics
or write something about the places that you visit which you usually post in
Facebook. Initially you can create an account in http://wordpress.com

Step number 3. You need to register for a domain
You will need to register for a domain. You can do this with domain provider
like godaddy.com or you may go with brinkster.com which offer webhosting and
domain name as well

 

Step number 4.  Verify and Crawl the content of your web site
You need to crawl the content of your website so that it can be search by the
in your favorite search engine. For google you can make use of the goggle search console.
You can reach search console using this site- https://www.google.com/webmasters/

Step number 5. Create ad unit in Adsense
You next step is to create add unit and secure the script.

Step number 6. Incorporate the Ad unit Script to your content.
You need to incorporate your ad unit script to your content.

Step number 7. Share your content To social media
The next step is to Share your content to social media like
facebook, google plus, linked-in and Yammer.

List of Free and Evaluation Software For your IT training

Aware-IT makes it easy for you to learn IT by maximizing the softwares that are available for you free of cost.

Below are the list of software  that you can use.




Software Category Type download site
Ubuntu Server Server Operating System open Source https://www.ubuntu.com/download/server
Ubuntu Desktop desktop Operating Sytem Open source https://www.ubuntu.com/download/desktop
Redhat Enterprise Linux Server Operating System 30 Days Evaluation | Free 1 year use for developers https://access.redhat.com/products/red-hat-enterprise-linux/evaluation
Windows Server 2016 Server Operatig System 180 Days Evaluation https://www.microsoft.com/en-us/evalcenter/evaluate-windows-server-2016
Windows 10 Desktop Operating System 180 Days Evaluation https://www.microsoft.com/en-us/evalcenter/evaluate-windows-10-enterprise
Visual Studio 2015 Microsoft Developer Tool Free/eval https://www.visualstudio.com/downloads/
SSDT Microsoft Database dev tool Free https://msdn.microsoft.com/en-us/library/mt204009.aspx
SQL Server 2016 Microsoft Database Server Eval https://msdn.microsoft.com/en-us/library/mt204009.aspx
sql management studio Microsoft Database tool Free https://msdn.microsoft.com/en-us/library/mt238290.aspx
Starwind VSAN Virtual SAN Free/Eval https://www.starwindsoftware.com/starwind-virtual-san
Oracle Virtual Box Virtual Machine Free https://www.virtualbox.org/
Vmware player Virtual Machine Free/Eval http://www.vmware.com/products/workstation.html
Postgres Database Free/Eval https://www.postgresql.org/download/
SAP HANA Database Express Edition Free SAP Hana Registration Link
Python Programming Language Open Source Free Python.org
Visual Studio Code Programming Language Open Source Free Microsoft Visual Studio code
Eclipse Programming Language Open Source Free Eclipse
Opera Browser Browser Opera
Netbeans Programming Language Open Source Free Netbeans
Android Studio Mobile Development Open Source Free Android Studio

 




Windows Server 2016 Becomes Generally Available

Windows server 2016, the newest server operating system from Microsoft become generally available. For those who have MSDN subscription, It is now available from the MSDN site. For those who don’t have msdn you can download the evaluation version from Microsoft technical evaluation  center.

We had been playing with this  technology since technical preview 5 and the videos are available on the links below.

Some of the many cool features are.

  • Nano server
  • IIS 10
  • Windows power shell 5
  • Windows server container
  • Connected standby

Here are some of the link that you might want to visit as reference