Investing Stock Market ABC’s

While most folks today trust mutual funds and their professional managers with their investments, it’s still important to understand the basics of the stock market. Although investing in individual stocks may not be right for everyone, a basic understanding of the stock market is essential to understanding the workings of our economy and business sector.

A stock is a portion of ownership in a company. Commonly referred to as a share, it is a small percentage of the total ownership pool for the corporation. Shareholders are stock owners, or people who have an ownership interest in the corporation. Today, shares are usually tracked electronically, but in previous decades shareholders would actually receive a certificate stating their ownership.

Why own stocks? First, you are sharing in the company’s profits. When a corporation shows a profit, they will sometimes distribute these profits to each shareholder, based on how much stock they own. This distribution is called a dividend. Company’s can elect to pay out their profits or reinvest them in the company, but as a shareholder, each time a payout is made you will receive your proportionate share.

Also, the value of your stock will rise and fall based on the company’s perceived value in the stock market. If you buy a share at $10.00 and it rises to $11.00 a share, you’ve made a dollar for each share you own, and subsequently sell. However, with this opportunity comes risk as well. If the share price falls and you sell, you’ll lose money. The more volatile the stock, the more opportunity for risk or profit.

Most shareholders track their stocks using the stock table. These appear confusing and difficult to read, but they are actually easy to understand with a little practice.

Ticker symbol is listed first. This is the abbreviated symbol that the stock market uses to identify your company. For example, GE is General Electric, WMT is Walmart. Once you select a company, you’ll need to know it’s shorthand name to track its progress.

Second, the company’s name may be listed. Some tables omit the name to save space, others list it to make tracking stocks easier.

The third item is the number of sales in the last trading day. This is listed in the 100,000’s, so 256 means 256,000 shares were bought and sold on the last day that the market was open.

Next are the high and low price, in that order. The high price is the highest per share price that the stock sold for on the previous trading day. The low price is the lowest price for that day. Since the price of the shares moves all day long, this is a good reference to see how much the stock is changing in a day.

Next, the closing price is listed. This is the last price that the stock traded for as the market closed. This will also be the beginning price for the next trading day.

After the closing price, the table will list the change, or the amount that the stock changed when you compare yesterday’s closing price with the closing price for the day before. This will be listed as a positive number (the stock went up) or a negative number (the stock sold for less yesterday than the day before).

Stock tables are found in many places, but most people check their daily paper or the Wall Street Journal. There are many internet sites that track stocks as well.

Of course, you’ll have to select a stock. Choose carefully or consult a professional, and good luck!

SQL join queries & Clauses

Steps For SQL Query execution

In this article you will explore

•  How to execute query within sql server 2005 environment
•  How to filter records with where clause
• How to arrange records with order by clause
• How to group records by group clause
• How to filter grouped records with having clause

Concept of Joins, explanation and example of

• Inner Join
• Outer Join
• Left Outer Join
• Right Outer Join
• Full Join or Cross Join
• Self Join
• Concept of sub queries, with explanation and example
• Concept of correlated sub queries, with explanation and example

  
Lets Start.....

For execution of Query, Open New Query Editor as shown in figure.                                            

Now lets execute different data manipulation statement.

Select Database to perform Database Operation using "use" Statement

--Select Database for Data Manipulation operation
use MySamplePractise

Select Statement Execution in SQL Server 2005

--Display each details of all Customers

select * from customer

--Display FirstName, City and Country Details of Customers
select FirstName, City, Country from Customer

--Using Alias to display details, Display "Customer Name" by concating FirstName and LastName of Customer


select FirstName + ' ' + LastName as 'Customer Name' from Customer

Filtering Records with "Where Clause"

--Display Customers from country USA

select * from Customer
where
Country = 'USA'

--Display Customers from All countries except USA

select * from Customer
where
Country <> 'USA'

--Display details of Customers whose CustomerID in a range from 1003 to 1008

select * from Customer
where
CustomerID >= 1003 and CustomerID <= 1008

--You can replace above statement by using Between statement.
--Between will show customer with customerID 1003 to 1008.

select *
from Customer
where CustomerID between 1003 and 1008

--Display Customers whose LastName starts from P
select * from Customer where LastName like 'P%'

--Display Customers whose country name ends with A

select * from Customer where Country like '%A'

--Display Customers whose FirstName or LastName consist of word khan

select *
from Customer
where FirstName like '%Khan%' or LastName like '%Khan%'

--Display Customers whose FirstName second character is A

select *
from Customer
where FirstName like '_A%'

Ordering Records with "Order by" Caluse
--Note: by default when neither ASC (for Ascending) or DESC (for descending) is specify, it is ordered in Ascending order.

--Display Customers of USA country ordered by state

select * from Customer
where Country='USA'
order by State

--Display Customers of USA country ordered by state in descending order.

select * from Customer
where Country='USA'
order by State DESC

--Order Customer by LastName in descending order and FirstName in Ascending order.

select * from Customer
order by LastName DESC, FirstName ASC

Grouping Records by "Group by" Clause

--Display Count of Customers in each country
select Country,Count(CustomerID) from Customer
group by country

--Display Count of Customers group the data by Country and State
select Country, state, Count(CustomerID) from Customer
group by country, state

Filtering Grouped data by "Having" Caluse

--Display Count of Customers country wise and display those countries only where more than 2 customers are available.

select Country,Count(CustomerID) from Customer
group by country
having Count(CustomerID) > 2


--Display Count of Customers from country "USA", state wise where in display details of those state only which have atleast 2 CustomerUsing both Where and Having Clause to Filter data.

--It will display only Customer Count from "NJ" state of "USA" country.
select State,Count(CustomerID) from Customer
where state='NJ' and country='USA'
group by state
having count(CustomerID) > 2

Using Order by to arrange Grouped data

--Display Count of Customers from country "USA", state wise and it should display details in descending order of state of those state where Customers are atleast 2

.

select State,Count(CustomerID) from Customer

where country='USA'

group by state

having count(CustomerID) > 2

Order by state desc

Now, lets display data from multiple table. For that we need to understand Joins so lets begin what is Join and how many different ways to display data.

Joins in SQL Server

Joins in SQL are used to combine the data from two or more tables on a common column between the two tables.

Different types of Joins in SQL Server and its explanation

• Inner Join
• Outer Join
• Left Outer Join
• Right Outer Join
• Full Outer Join or Cross Join
• Self Join

Lets go through the concept quickly...

Inner Join

 Inner Join return those rows which are common in both the tables. In other words, Inner Join displays only the rows that have matching rows in both the tables.
Note: Nulls are excluded. Another thing you may even write just "Join" instead of "Inner Join" as default join is "Inner Join"

Example of Inner Join

We can use two types of syntax to write Join Query, that is

• Join Query with ANSI Join Syntax
• Join Query with Microsoft Join Syntax

well i will go through both example, but we will follow ANSI Join Syntax as it is widely used, but according to me Microsoft Join Syntax is easy...

Use Northwind

Method 1: ANSI JOIN Syntax

select * from
orders inner join [order details]
on
orders.orderid = [order details].orderid

Method 2: Former Microsoft JOIN Syntax

select * from orders, [order details]
where orders.orderid = [order details].orderid

Both query will be generate the same result.

Now, lets turn to example from our SamplePractise Database

--Display Details of Customer who have placed order.

select LastName, FirstName, Country, OrderId, OrderDate from
Customer inner Join Orders
on
Customer.CustomerID = Orders.CustomerID


--or-- Note: Inner join is default join and so you can write just join instead of inner join.
select LastName, FirstName, Country, OrderId, OrderDate from
Customer Join Orders
on
Customer.CustomerID = Orders.CustomerID

--Display Details of Customer living in "USA" who have placed order and arrange data by latest order placed.

select LastName, FirstName, Country, OrderId, OrderDate from
Customer inner Join Orders
on
Customer.CustomerID = Orders.CustomerID
where Country = 'USA'
order by OrderDate DESC

     2.  Outer Join

Outer Join, return all the rows from at least one of the table mentioned in the Join clause. Lets understand 3 types of outer join

Left Outer Join

Left outer join returns all the records from left table and only matching records from right table.

Example of Left Outer Join:

--Display details of all Customers and order details for those customer who have place any order

select LastName, FirstName, Country, OrderID, OrderDate
from Customer Left Outer Join Orders
on
Customer.CustomerId = Orders.CustomerID

Right Outer Join

Right outer join returns all the records from right table and only matching records from left table.

Example of Right Outer Join:

--Display all product type and product available in it

select producttype, productname
from producttype right outer join product
on
producttype.producttypeid = product.producttypeid
order by producttype


Full Join or Cross Join

A cross join returns the sets of records from the two joined tables. If A and B are two sets then cross join = A X B.

Example of Full Join or Cross Join

--Display all orders and all its details

select * from orders cross join orderdetails
-or-
select * from orders, orderdetails

Self Join

A table is join to itself in a self join.

Example for Self Join:

--Display details of customer living in same state

select distinct a.* from customer a, customer b
where a.customerid <> b.customerid and
a.state = b.state
order by state

--Consider an Example of Employee Table, wherein you want to find details of all the employee drawing same salary

select distinct a.* from employee a, employee b
where a.empid <> b.empid and
a.salary = b.salary

Joining three or more table

In joining three or more tables, join applies to only one relationship, and each relationship represents an association between only two tables.

FROM table1 join table2

ON table1.primarykey = table2.foreignkey join table3
ON table2.primarykey = table3.foreignkey
The first join applies to table1 and table2, the second join applies to table2 and table3.

Example of Joining three or more tables

--Sample syntax

select * from
table1 left outer join table2 on (table1.field1 = table2.field1),
join table3 on (table2.field2 = table3.field2)

--Display details from customer, order and orderdetails table.

select c.lastname, c.firstname, o.orderid, o.orderdate, od.orderdetailsid, p.productname
from
customer c Inner Join orders o on (c.customerid = o.customerid)
inner join orderdetails od on (o.orderid = od.orderid)
inner join product p on (od.productid = p.productid)
order by o.orderid, od.orderdetailsid, p.productname

Sub Queries

A query within another query is called sub query.

Example of Sub Query

--Display details of customer who haven't placed any order

select * from customer
where customerid not in (select customerid from orders)

--Display details of customer belonging to "NJ" state of "USA" country has placed order.

select * from customer
where state = 'NJ' and
customerid in (select customerid from orders)

Correlated Sub Queries

A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself.

Example of Correlated Sub Queries

--Display order information along with amount of order.

select o.orderid, o.orderdate,
(select sum(od.amount) from orderdetails od
where o.orderid = od.orderid) as "Amount"
from orders o

--Display orderdetails along with product details.

select od.orderdetailsid,                           

(select p.productname from product p               

where od.productid = p.productid) as "ProductName", 

(select p.productprice from product p                

where od.productid = p.productid) as "ProductPrice", 

od.quantity as "Quantity",                           

od.quantity * (select p.productprice from product p  

where od.productid = p.productid) as "Amount"      

from orderdetails od                                    

Note: you can also use

ANY - To display any matching record within sub query

ALL - To display details of those records who match all the criteria of sub query