|
|
SQL / Structured Query Language'SQL (Structured Query Language) was developed at IBM by Donald Chamberlin and Raymond Boyce in the early 1970s'. SQL now exists in most database packages including Access, dBase, MySQL, Oracle, PostgreSQL, SQL Server and Visual FoxPro. Standardizing of database languages to include SQL enables conversions between database to be easier and also enables programmers to write with different database packages without having to learn every last detail of the database package that is new to them. PL/SQL stands for Procedural Structured Query Language and this is what Oracle uses for their databases such as MySQL and Oracle database. T-SQL stands for Transact Structured Query Language and this is what Microsoft uses for their databases such as Access and SQL Server. PostgreSQL is a object relation database and it has it's own procedural languange called PL/pgSQL.
Some of the main constructs of SQL (Structured Query Language) includes:
Select-The select statement in SQL allows for the retrieval of information into database tables. Delete-The delete statement in SQL allows for the deletion of information into database tables. Insert-The insert statement in SQL allows for adding information into database tables.
Update-The update statement in SQL allows for updating information into database tables.
The following are examples of SQL statements that can act on database tables: Example Select statement: Select * from customers where Ship_State = 'WA' Example Delete statement: Delete from customers where Last_Name = 'Test Data' Example insert statement: INSERT INTO customers (First_Name, Last_Name, Ship_City) Values ('Bart', 'Simpson', 'Springfield') Example Update statement: UPDATE customers SET State='MO' WHERE CustomerID = '51525' AND Last_Name='Simpson' Join Statements Joining tables in different databases allows for comprehensive and complex reports on data rather than just returning a 1 flat file at a time for reporting purposes. An example of a SQL Query or report that might be useful would be to join a Sales Order table, invoicing table and a Accounts Receivable customer information table in order to see what customers have completed making their orders and which customers have been invoiced in 1 consolidated block of data. In this particular case the field in common for these different tables would be Customer ID. The Customer ID would be the Key Field in the Accounts Receivable customer information table and the Customer ID would be foreign keys for the Sales Order table and invoicing table. There are a large number of joins that can be written in Structured Query Language (SQL) There are inner joins, left and right outer joins Full outer joins, Cross Joins, Union All joins and merges. Inner Joins are exclusive, meaning, inner joins exclude more data or have a less records returned in a dataset than Outer Joins, which are inclusive. Therefore, this would mean that Full Outer Joins and Union All Joins would have largest amount of records returned from a dataset. Example Inner Join Statement: SELECT InvoiceID, Customers.Customer_Name, Invoices.Invoice_Date, Invoices.Date_Paid FROM Invoices INNER JOIN Customers ON Invoices.CustomerID=Customers.CustomerID; Example Left Join Statement: SELECT Customers.Customer_Name, Invoices.InvoicesID FROM Customers LEFT JOIN Invoices ON Customers.CustomerID = Invoices.CustomerID ORDER BY Customers.Customer_Name; Example Right Join Statement: SELECT Invoices.InvoiceID, Customers.Customer_Name, Customers.Phone1, Invoices.Date_Paid FROM Invoices RIGHT JOIN Customers ON Invoices.CustomersID = Customers.CustomersID ORDER BY Invoices.InvoiceID; Example Full Join Statement: SELECT Invoices.InvoiceID, Customers.Customer_Name, Customers.Phone1, Invoices.Date_Paid FROM Invoices FULL OUTER JOIN Invoices ON Customers.CustomerID=Invoices.CustomerID ORDER BY Customers.Customer_Name; The SQL CROSS JOIN produces a result set where the 1st table and the 2nd table are combined if no WHERE clause is used along with CROSS JOIN. If a where clause is used in the Cross Join then the result set is a combination of the 2 tables based on a criteria then this is known as a Cartesian Product. Example Cross Join Statement: SELECT Invoices.Product,Invoices.Quantity, Customers.Customer_Name, Customers.Customers_City FROM Invoices Cross Join Customers; Example Union SQL Statement: SELECT City, State FROM Customers WHERE State='Hawaii' UNION SELECT City, State FROM Suppliers WHERE State='Hawaii' ORDER BY City; Aggregate functions make calculations on sets of values. Some of the SQL aggregate functions are Avg(), Count(), Max(), Min(), StDev(), Sum() and Var(). Example Average function and statement: SELECT Avg(InvAmount) FROM Invoices Example Count function and statement: SELECT Count(*) From Invoices Example Max function and statement: SELECT Max(InvAmount) FROM Invoices Example Min function and statement: SELECT Min(InvAmount) FROM Invoices Example Standard Deviation function and statement: SELECT StDev(InvAmount) AS Standard_Deviation FROM Invoicing Example Sum function and statement: SELECT DISTINCTROW Sum(InvAmount) AS [Sum Of InvAmount] FROM Invoicing Example Variance function and statement: SELECT Var(InvAmount) AS Variance FROM Invoicing The SQL GROUP BY statement groups rows that have the same values into groups of rows. Example of a GROUP BY statement: SELECT PARTNO, PARTDESC, Sum(PRICE) AS SumOfPRICE, QUANTITY FROM Inventory GROUP BY PARTNO,PARTDESC, QUANTITY; The HAVING SQL statement is similar to the SQL WHERE clause. I found articles on the web that state "HAVING requires that a GROUP BY clause is present." The following example of a HAVING statement does not have a GROUP BY clause and the statement does run in T-SQL which proves web articles are wrong when they state that "HAVING requires that a GROUP BY clause is present" SELECT Max(InvAmount) AS ["Max"] FROM Invoicing HAVING (((Max(InvAmount))>100)); The following is a list of T-SQL functions.
The following is a list of reserved T-SQL keywords.
The following is a list of reserved T-SQL operators.
|
