/*
CIS 324 - Spring 2008

SELECT I

CHAPTER 7

pp. 215
Two categories of syntax: DDL and DML
We will do DML first and then return to DDL
SQL is a 4GL (how versus what)

http://www.webopedia.com/TERM/f/fourth_generation_language.html

Basic structure of SQL SELECT (DML)

SELECT attribute list
FROM table/view/SQL expression
WHERE condition(s)
GROUP BY attribute list
HAVING condition
ORDER BY attribute list [ASC | DESC]

The order of the statements is important. It must be in the above order.

Importance of learning SQL basics
   De facto query standard for relational databases

Monster.com job search

All things being equal, a query will
  exhibit better performance
  have better fault isolation
  have lower development cost
  be more scalable, portable, and reusable
  be more secure
than programming logic or a combination of programming logic and embedded SQL.

SELECT attribute list
pp. 232

In a practical sense, SELECT is a "print" statement.
SELECT "prints" a collection of rows and columns.

SELECT can print constants, the results of calculations, 
the result of system functions, or data from tables.

SELECT supports string concatenation

*/

--string, integer, function, calculation

SELECT 'This is a string', 200, GETDATE(), 1+1

SELECT 'This is a string', 200, GETDATE(), 1+1, 'This is a string' + ' plus another string.'

--string concatenation

/*
Colmn Alias
pp. 240

In almost all cases where the contents of a column are derived, 
i.e., not a value stored in a data table, it is best to provide
a column name or column alias.
This is standard software engineering best practice: meaningful identifiers
*/

SELECT 'This is a string' AS 'String', 200 AS 'Number', 
  GETDATE() AS 'SystemTime', 1+1 AS 'Calculation'

/*
Column aliases can also be used to support data/logic independence.
That is, changes to the underlying data structure do not necessarily
have an impact on existing, legacy applications.

For example, if the width or datatype of a column in a relation changes,
the CAST function along with an alias can preserve the former view 
of the data. Normally we would build a view, but an alias can also 
serve. We'll discuss views later.

CAST(expression AS data_type) 

List of datatypes

*/

SELECT 'This is a string' AS 'String', CAST(200 AS NVARCHAR) AS 'Number', 
  GETDATE() AS 'SystemTime', 1+1 AS 'Calculation'


/*
The real power of SQL for writing complex queries is:
The output of every relational operator is an another relation

P1. A relation (that is rows and columns) is a logical construct
P2. The result of a relational operator is an another relation

Ergo: Anywhere you can put a table, view, enumerated list, constant
or variable in a relational operator, you can put a query.

In other words, the SELECT statement above, 
can be treated as if it were a relation (table).
pp. 36 definition of a relation
pp. 61 characteristics of tables

We can embedd as a subquery an SQL expresssion 
in each of the clauses of the SELECT query
With each clause, there are constraints of course (more on this later)
*/

SELECT 'this is an inner query' AS InnerSubQuery

SELECT (SELECT 'this is an inner query' AS InnerSubQuery) AS 'InnerSub', 
'This is a string' AS 'String', CAST(200 AS NVARCHAR) AS 'Number', 
  GETDATE() AS 'SystemTime', 1+1 AS 'Calculation'


/*
Normally, we use the SELECT statement to retrieve rows and columns 
FROM tables in a database.
USE database
More on creating database later in the DDL section
*/

USE NorthWind

--You can run these queries in the "In class examples" assignment on SQL Exercise System
--Specify a table

--Wildcard character * pp. 232
--What is a table?

SELECT *
FROM Customers


/*
Why not use the * in applications/stored procedures/views?
1. Security and information hiding; need to know basis
2. Maintenance cost is reduced through application database independence (and reports)
3. MIP-O-Suction query; DOS attack (e.g. cartesian product of large tables)

FOR i = 0 to 4
  LOGIC...
FOR NEXT

When writing a query, I use the * at the end of the attribute list until I have
listed all the attributes I want and then I either delete or comment the * out.
*/

--MIP-O-Suction query
--For example, how many rows will be returned by this query?

SELECT * 
FROM Customers, Employees, Orders, [Order Details], Products, Categories

SELECT (SELECT CAST(COUNT(*) AS REAL) FROM Customers) *
       (SELECT CAST(COUNT(*) AS REAL) FROM Employees) *
       (SELECT CAST(COUNT(*) AS REAL) FROM Orders) *
       (SELECT CAST(COUNT(*) AS REAL) FROM [Order Details]) *
       (SELECT CAST(COUNT(*) AS REAL) FROM Products) *
       (SELECT CAST(COUNT(*) AS REAL) FROM Categories) AS NumberOfRows

/*
That is: 937,538,810,000 rows! 937 billion....


We could also ask the dbms to estimate for us before running the query

SET SHOWPLAN_ALL { ON | OFF }

*/
SET SHOWPLAN_ALL  OFF

USE Northwind

--For example, this query produces a Cartesian product and can take a long time to run
SELECT *
FROM Orders, [Order Details], Products, Customers, Employees, Shippers


SELECT CustomerID, CompanyName, ContactName, *
FROM Customers

SELECT CustomerID, CompanyName, ContactName--, *
FROM Customers

/*

This is a mis-use of aliases. 
Meaningful identifiers
Saves typing but makes the query less readable

*/
SELECT C.CustomerID, C.CompanyName, C.ContactName--, *
FROM Customers C



--single line comment
/* multiple line comment */




/*
Listing Unique Values pp. 255

DISTINCT

Produce a unique list of cities from the Customers table.

*/

SELECT City
FROM Customers
ORDER BY City

--This query gives me all the values, not a distinct list

SELECT DISTINCT City
FROM Customers
--ORDER BY City
--Be cautious with DISTINCT, if the column is not indexed, it can cause
--a sort which can be VERY expensive.


/*
Ordering a list pp. 253
ORDER BY attribute list [ASC | DESC]

Cascading order sequence or multilevel order

*/

SELECT *
FROM Customers
ORDER BY CompanyName ASC

SELECT *
FROM Customers
ORDER BY Country ASC, City DESC

SELECT *
FROM Customers
ORDER BY Country ASC, City DESC, PostalCode

--This won't work because PostalCode contains non-numeric values
SELECT *
FROM Customers
ORDER BY Country ASC, City DESC, CAST(PostalCode AS INT)


/*
Marketing just called and wanted to do a new mail merge on the customers
table. They want the list ordered first by city and then by companyName.

Oh, and don't forget, we need CompanyName spelled "CustomerName"
*/

SELECT CompanyName AS 'CustomerName', Address, City, PostalCode, *
FROM Customers
ORDER BY City, CompanyName

SELECT CompanyName AS 'CustomerName', Address, City, PostalCode--, *
FROM Customers
ORDER BY City, CompanyName