Program in C

http://cbasicprogram.blogspot.in

What is an algorithm?

What is an algorithm?

Informally, an algorithm is any well-defined computational procedure that takes
some value, or set of values, as input and produces some value, or set of values, as
output. An algorithm is thus a sequence of computational steps that transform the
input into the output. Source: Thomas H. Cormen, Chales E. Leiserson (2009), Introduction to Algorithms 3rd edition.

In simple terms, it is possible to say that an algorithm is a sequence of steps which allow to solve a certain task ( Yes, not just computers use algorithms, humans also use them). Now, an algorithm should have three important characteristics to be considered valid:

1. It should be finite: If your algorithm never ends trying to solve the problem it was designed to solve then it is useless
2. It should have well defined instructions: Each step of the algorithm has to be precisely defined; the instructions should be unambiguously specified for each case.
3. It should be effective: The algorithm should solve the problem it was designed to solve. And it should be possible to demonstrate that the algorithm converges with just a paper and pencil.
4. 
   

Basic Sql statements

INSERT        

   INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');

UPDATE      

 UPDATE Customers

SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'

WHERE CustomerID = 1;

DELETE     

 DELETE FROM Customers

WHERE CustomerName='Alfreds Futterkiste';

SELECT WHERE

 SELECT * FROM Customers

WHERE Country='Germany' AND City='Berlin'; SELECT * FROM Customers

WHERE City='Berlin' OR City='München';

SELECT * FROM Customers

WHERE NOT Country='Germany';

SELECT * FROM Customers

WHERE Country='Germany' AND (City='Berlin' OR City='München');

SELECT TOP

SELECT TOP 3 * FROM Customers; SELECT * FROM Customers

LIMIT 3;

SELECT * FROM Customers

WHERE ROWNUM <= 3;

SELECT TOP 50 PERCENT * FROM Customers; SELECT * FROM Customers

WHERE Country='Germany' AND ROWNUM <= 3;

ORDER BY  

 SELECT * FROM Customers

ORDER BY Country ASC, CustomerName DESC;

Aggregate function

SELECT MIN(Price) AS SmallestPrice

FROM Products;

SELECT COUNT(ProductID) FROM Products;

SELECT SUM(Quantity) FROM OrderDetails;

LIKE              

 SELECT * FROM Customers

WHERE CustomerName LIKE 'a%'; // a at the start

SELECT * FROM Customers

WHERE CustomerName LIKE '%or%'; //or in any position

SELECT * FROM Customers

WHERE CustomerName LIKE 'a_%_%' //SQL statement selects all customers with a

CustomerName that starts with "a" and are at least 3 characters in length:

SELECT * FROM Customers

WHERE CustomerName NOT LIKE 'a%'; //

IN                   

SELECT * FROM Customers

WHERE Country IN ('Germany', 'France', 'UK');

SELECT * FROM Customers

WHERE Country NOT IN ('Germany', 'France', 'UK');

SELECT * FROM Customers

WHERE Country IN (SELECT Country FROM Suppliers);

BETWEEN

     SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products

WHERE Price NOT BETWEEN 10 AND 20;

SELECT * FROM Products

WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

JOIN              

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

INNER JOIN

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN   

 SELECT Customers.CustomerName, Orders.OrderID FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;

RIGHT JOIN

 SELECT Orders.OrderID, Employees.LastName, Employees.FirstName

FROM Orders

RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID

ORDER BY Orders.OrderID;

FULL OUTER JOIN

SELECT Customers.CustomerName, Orders.OrderID FROM Customers

FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

SELF JOIN     

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2,A.City FROM Customers A, Customers B

WHERE A.CustomerID <> B.CustomerID AND A.City = B.City

ORDER BY A.City; // join with itself

UNION          

SELECT City FROM Customers

UNION

SELECT City FROM Suppliers

ORDER BY City;

GROUP BY

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders

LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID

GROUP BY ShipperName;

HAVING 
  SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;

DDL:

PK/FK               

   CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int,

PRIMARY KEY (OrderID),

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

);

CHECK             

    CREATE TABLE Persons ( ID int NOT NULL,

LastName varchar(255) NOT NULL, FirstName varchar(255),

Age int,

City varchar(255),

CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')

);

INDEX         

        CREATE INDEX idx_lastname

ON Persons (LastName);

AUTO INCREAMENT

CREATE TABLE Persons (

ID int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL, FirstName varchar(255),

Age int,

PRIMARY KEY (ID)

);

VIEW                  

  CREATE VIEW [Current Product List] AS SELECT ProductID, ProductName

FROM Products

WHERE Discontinued = No;