theWhiteFox DESIGNING & BUILDING WEBSITE / MOBILE APPS

thewhitefox

SQL Fun

2021 October 15th Written by theWhiteFox

Teaching a class of 20 students Structured Query Language (SQL - pronounced "ess-que-el") Fun-damentals. The following is a lesson plan I wrote and taught.

Aggregate functions

Perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

Useful aggregate functions:

  • AVG() - Returns the average value
  • COUNT() - Returns the number of rows
  • FIRST() - Returns the first value
  • LAST() - Returns the last value
  • MAX() - Returns the largest value
  • MIN() - Returns the smallest value
  • SUM() - Returns the sum

HAVING

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

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

Result:

LastNameNumberOfOrders
OConnor11
Henry27
Chinaski14
1SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
2INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID WHERE LastName=OConnor OR LastName=Henry GROUP BY LastName HAVING COUNT(Orders.OrderID) > 25;

Result:

LastNameNumberOfOrders
Henry27

CAST

Converts an expression of one data type to another in SQL Server. Syntax for CAST: CAST ( expression AS data_type [ ( length ) ] )

Table Student_Score Column Name Data Type StudentID integer First_Name char(20) Score float

Table Student_Score Rows

StudentIDFirst_NameScore
1Jenny85.2
2Bob92.5
3Alice90
4James120.1

Example 1

SELECT First_Name, CAST(Score AS Integer) Int_Score FROM Student_Score;

Result:

First_NameInt_Score
Jenny85
Bob92
Alice90
James120

Table Relationships

Some tables contain information related to other tables.

Join

Using the join statement, SQL has powerful tools for extracting related data from multiple tables. Typically unique id fields are used to create relationships. Unique id fields work well for creating and managing simple or complex relationships between tables. When a result is needed from multiple rows in multiple tables use a join query. The easiest way to understand joins is by using a venn diagram. Matching ids The simplest most common join is the inner join, this is the default join. Many databases including SQLite do not support a right join or full other join. Inter section of the tables, where the tables overlap a condition is met.

Exercise: Joins

Create a new databasewith the following SQL Select both tables Create a basic join. ON l.id = r.id; // join on the rows that the condition is met All the results from the left column and the inner join.

1Join SELECT * FROM Orders INNER JOIN Customers;
2
3SELECT firstName, lastName, orderAmount FROM Customers INNER JOIN Orders ON CustomerID =
4CustomerID;

View

A view stores the select statement as a table.

1CREATE VIEW employeeName AS
2SELECT firstName, lastName, FROM emloyees
3Select all from the view.
4SELECT * FROM employeeName

GRANT statement

Use the GRANT statement to give privileges to a specific user or role, or to all users, to perform actions on database objects. You can also use the GRANT statement to grant a role to a user, to PUBLIC, or to another role.

GRANT privilege-type ON [TABLE] { table-Name | view-Name } TO grantees

Example

To grant the SELECT privilege on table Managers to all users, use the following:

1GRANT SELECT ON TABLE Managers to PUBLIC
2
3From your CLI, run SQLite3.exe create new database.
4
5sqlite>../sqlite3.exe PatsClothesShop.db
6Customer Order
7
8sqlite> CREATE TABLE Customer(
9costumerID INT PRIMARY KEY NOT NULL,
10firstName CHAR(50) NOT NULL,
11lastName CHAR(50) NOT NULL,
12address VARCHAR(200),
13city CHAR(10),
14county CHAR(10),
15creditLimit....... REAL,
16costomerSince DATETIME
17);
18
19sqlite> CREATE TABLE Order(
20orderID INT PRIMARY KEY NOT NULL,
21orderDate DATETIME NOT NULL,
22orderAmount REAL,
23paymentType INT,
24customerID INT NOT NULL
25);
26
27sqlite>.tables
28Customer Order
29
30sqlite>.header on
31sqlite>.mode column
32sqlite>.timer on

Insert 5 customers like below

1INSERT INTO Customers (firstName, lastName, address, city, county, creditLimit,
2costomerSince)
3VALUES (1, 'Paul', 'Murphy' 32, 'Apt 1', 'Dublin', 15000.00, '2007-01-01 10:00:00' );

XML eXtensible Markup Language

Similar to HTML in it’s use of the tags

XML contains self-describing structured data but omits presentation (formatting) information. XML is used to store or transfer data between disparate computing platforms, operating systems, software application, etc.

XML is an open standard, as opposed to a proprietary data format. Common easily readable format for computer and humans, very portable. XML used extensively in the .NET Framework.

XML’s relationship to ADO.NET Datasets

Allows for a disconnected means of working with data, then allows for easy synchronization of changes back to the original data store.

• XML is used for syndicated RSS feeds • XML is used to store or transfer data between disparate computing
• XML contains self-describing

Create an XML file called cars. Create a root element /node carcollection, all XML documents have to have a root element. Inside the carcollection element /node create two elements called car these are the sub elements /children of carcollection. Inside the car elements create a sub-element called make. It can one or the other but not both or it can contain an attribute. Tag name are created by the programmer.

Pretty much free form, however follow a pattern to make it readable for others and yourself. A need for consentience valid documents the structure of the XML and follows the rules of XML a well formed XML document.

A valid document and well formed.

1<carcollection>
2 <car>
3 <make>DeLorean</make>
4 <model>Time Machine</model>
5 <year>1981</year>
6 </car>
7 <car>
8 <make>Cadillac</make>
9 <model>Ecto-1</model>
10 <year>1984</year>
11 </car>
12</carcollection>

XML Schema Document

Using C# and .NET Framework Class Library to open and navigate through an XML file Add seven linkLabels and a button Save the XML document in the bin /debug folder @”cars.xml” is the location of the XML file

1// StreamReader will retrieve the file from the source
2// and will convert it into a stream ready to be processed
3System.IO.StreamReader sr = new System.IO.StreamReader(@"cars.xml");
4
5// XmlTextReader
6System.Xml.XmlTextReader xr = new System.Xml.XmlTextReader(sr);
7
8// XmlDocument
9System.Xml.XmlDocument carCollectionDoc = new System.Xml.XmlDocument();
10
11carCollectionDoc.Load(xr);
12
13// using the InnerText property will give us just the data
14// ... since we are at the entire Document level, it will
15// give us *all* the values (no delimiter).
16linkLabel1.Text = carCollectionDoc.InnerText;
C# DB App