SQL QUERIES

[sibij]

Writing SQL Queries:
   
            SQL SELECT Queries--A SQL SELECT statement can be broken down into numerous elements, each beginning with a keyword. Although it is not necessary, common convention is to write these keywords in all capital letters.
 SELECT
FROM
WHERE
ORDER BY

The SQL SELECT DISTINCT Statement 

SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;


If we want to retrieve all of the information about all of the customers in the Employees table, we could use the asterisk (*) as a shortcut for all of the columns, and our query looks like
       
               
                   

Code: [Select]

SELECT * FROM Employees;
If we want only specific columns (as is usually the case), we can/should explicitly specify them in a comma-separated list, as in
 
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees

The WHERE Clause
 we want to do is to start limiting, or filtering, the data we fetch from the database. By adding a WHERE clause to the SELECT statement, we add one (or more) conditions that must be met by the selected data.
 

Code: [Select]

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City = 'London'
you wanted to get the opposite, the employees who do not live in London, you would write
   

Code: [Select]

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City <> 'London'
you can also use the standard equality/inequality operators that you would expect. For example, to get a list of employees who where hired on or after a given date, you would write

Code: [Select]

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE HireDate >= '1-july-1993'
  we can write more complex conditions. The obvious way to do this is by having multiple conditions in the WHERE clause. If we want to know which employees were hired between two given dates, we could write

Code: [Select]

SELECT     EmployeeID, FirstName, LastName, HireDate, City
FROM       Employees
WHERE      (HireDate >= '1-june-1992') AND (HireDate <= '15-december-1993')


SQL also has a special BETWEEN operator that checks to see if a value is between two values (including equality on both ends). This allows us to rewrite the previous query as

Code: [Select]

SELECT    EmployeeID, FirstName, LastName, HireDate, City
FROM      Employees
WHERE     HireDate BETWEEN '1-june-1992' AND '15-december-1993'
 use the NOT operator, to fetch those rows that are not between the specified dates:

Code: [Select]

SELECT    EmployeeID, FirstName, LastName, HireDate, City
FROM      Employees
WHERE     HireDate NOT BETWEEN '1-june-1992' AND '15-december-1993'
if we want to check if a column value is equal to more than one value? If it is only 2 values, then it is easy enough to test for each of those values, combining them with the OR operator and writing something like

Code: [Select]

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City = 'London' OR City = 'Seattle'
However, if there are three, four, or more values that we want to compare against, the above approach quickly becomes messy. In such cases, we can use the IN operator to test against a set of values. If we wanted to see if the City was either Seattle, Tacoma, or Redmond, we would write

Code: [Select]

  SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City IN ('Seattle', 'Tacoma', 'Redmond')
The ORDER BY Clause

To sort the data rows, we include the ORDER BY clause. The ORDER BY clause includes one or more column names that specify the sort order. If we return to one of our first SELECT statements, we can sort its results by City with the following statement:

Code: [Select]

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
ORDER BY City
The SQL INSERT INTO Statement

INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two ways.

Code: [Select]

INSERT INTO table_name(column1, column2,column3, ...)
VALUES (value1, value2,value3, ...);
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES('Cardinal','Tom B. Erichsen', 'Skagen 21','Stavanger','4006','Norway');It is also possible to only insert data in specific columns.

Code: [Select]

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal','Stavanger','Norway');SQL NULL Values
A field with a NULL value is a field with no value.

Code: [Select]

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL
The SQL UPDATE Statement
UPDATE Syntax

Code: [Select]

UPDATE table_name
SET column1=value1,column2 = value2, ...
WHERE condition;UPDATE Table
The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
The SQL DELETE Statement

Code: [Select]

The DELETE statement is used to delete existing records in a table
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste'