How to Write SQL Queries & Work With XML & XSLT

Database Development, Web Development

SQL

Vocabulary

Relational database – one or more tables where each table has 0+ records/rows of data, organized into fields/columns. Tables in a database have relationships between them in a one-to-one or one-to-many relationship.
Primary Key – uniquely identifies each record in a database table. Must contain unique values, and cannot contain NULL values.
Foreign key – the primary key of another table. e.g. two tables have a customerID field, which is the Primary Key of the customer table and a Foreign Key of the orders table.
SQL SELECT statements – used to select the data we want returned from the database and formulate our query we want answered by the database.

SQL SELECT Statement Characteristics

Before you start writing queries, it's a good idea to get familiar with the data you are working with first. If you are able, go in and explore the databases, tables, and fields you have to work with. If not, there are some queries to get you in the know (for MySQL). Type in the query:

SHOW DATABASES

to get a list of databases. Select a database to work with and use the query:

SHOW TABLES in DatabaseOne

to get a list of all the columns you have to work with in that table.

It is a common convention to write SELECT element keywords in all caps
The most common SQL SELECT elements are SELECT, FROM, WHERE, and ORDER BY
A SELECT statement has two parts: what columns you want to return and what tables those columns come from. Explicitly specifying the desired fields allows you to control the order in which the fields are returned.

SELECT *

FROM employees;

SELECT EmployeeID, FirstName, HireDate, City

FROM Employees;

Adding a WHERE clause adds one or more conditions that must be met by the selected data, limiting the rows that answer the query and are fetched. It can be combined with AND, OR and NOT operators.

SELECT *

FROM employees

WHERE City = 'Los Angeles';

Use this to get opposite results:

SELECT *

FROM employees

WHERE City <> 'Los Angeles';

You could get results for multiple cities by saying:

SELECT *

FROM employees

WHERE City = 'London' OR City = 'Seattle';

Use this for 2+ cities:

SELECT *

FROM employees

WHERE City IN ('Seattle', 'Tacoma', 'Redmond');

You can use a BETWEEN operator to see if a value is between two values:

SELECT *

FROM employees

WHERE HireDate BETWEEN '1-june-1992' AND '15-december-1993';

Use this to get opposite results:

SELECT *

FROM employees

NOT BETWEEN '1-june-1992' AND '15-december-1993';

The LIKE operator can be used to perform pattern-matching using wildcard characters: _ matches a single character, % matches a string of 1+ characters, [] matches any single character within the specified range or set, [^] matches any single character not within the specified range or set.
- ```
WHERE FirstName LIKE '_oe'
```
  finds all three-letter first names that end with 'oe' (e.g. Joe, Zoe)
- ```
WHERE LastName LIKE '%tone'
```
  finds all employees whose last name ends with 'tone'
- ```
WHERE LastName LIKE '%bara%'
```
  finds all employees whose last name includes 'bara' anywhere in the name
- ```
WHERE FirstName LIKE '[JZ]oe'
```
  finds three-letter first names that end with 'oe' and begin with either 'J' or 'Z' (that is, only Joe and Zoe)
- ```
WHERE LastName LIKE 'd[^o]%'
```
  finds all last names beginning with 'd' where the following (second) letter is not 'o'
ORDER BY lets you sort the returned results by one of the Column Names. It defaults to sorting ASC. You can add DESC to the end of the statement to get it to sort DESC

SELECT *

FROM employees

ORDER BY City

GROUP BY aggregates data that has similarities - if there's any duplicates in the data you can use GROUP BY to count the number of duplicates in your fields. For example, in a query that returns employee ID and first name, using GROUP BY can show how many people you have named Dan
LIMIT allows you to limit the number of results you return

SELECT *

FROM employees

ORDER BY City

LIMIT 100;

You can select data within a date range:

SELECT first_name, last_name

FROM people_city

WHERE address_city = "Los Angeles" AND birth_date BETWEEN '1991-11-01'

ORDER BY last_name

LIMIT 100;

To find data from the last 30 days write:

SELECT first_name, last_name

FROM people_city

WHERE address_city = "Los Angeles" AND birth_date >= (DATE_SUB(CURDATE(),INTERVAL 30))

ORDER BY last_name

LIMIT 100;

JOIN allows you to filter data using fields from two separate tables:

SELECT birthdate_city.first_name, birthdate_city.last_name

FROM birthdate_city

JOIN haircolor_city USING (user_id)

WHERE hair_color = "red" AND birth_date BETWEEN '1991-11-01' AND '1993-11-01'

ORDER BY last_name;

To check for null values use the IS NULL or IS NOT NULL operators with the WHERE clause:

SELECT birthdate_city.first_name, birthdate_city.last_name

FROM birthdate_city

JOIN haircolor_city USING (user_id)

WHERE hair_color IS NULL;

IN allows you to specify multiple values in a WHERE clause:

SELECT *

FROM customers

WHERE Country IN ('Poland', 'Canada', 'Ireland');

Aliases can help shorten complicated SQL queries. They're used to give a table or column a temporary name:

SELECT o.OrderID, o.OrderDate, c.CustomerName

FROM Customers AS c, Orders AS o

WHERE c.CustomerName = "Billy" AND c.CustomerID = o.CustomerID;

———

https://technet.microsoft.com/en-us/library/bb264565(v=sql.90).aspx
https://blog.hubspot.com/marketing/sql-tutorial-introduction
https://www.w3schools.com/sql/sql_select.asp

Retrieve XML Data using SQL

By storing an XML document within a SQL Server database, you're able to create queries that retrieve both relational data and specific information within the XML instance related to that data.

The easiest way to retrieve an individual value from an XML instance is to use the value() method. You call it by first specifying the name of the xml object that contains the target data and then providing two arguments: an XQuery expression and the data type of the returned value. XQuery is a scripting language used to access XML data. Here's an example of the value() method:

xml_object.value('xquery_exp', 'data_type')

Here's an example with an XML file of cars and then the SQL SELECT statement to return a list of all cars using the .nodes method:

<?xml version="1.0" encoding="UTF-8"?>
<Cars>
  <Car id="1234">
    <Make>Volkswagen</Make>
    <Model>Eurovan</Model>
    <Year>2003</Year>
    <Color>White</Color>
  </Car>
  <Car id="5678">
    <Make>Honda</Make>
    <Model>CR-V</Model>
    <Year>2009</Year>
    <Color>Black</Color>
    <Mileage>35,600</Mileage>
  </Car>
</Cars>

SELECT t.value('Make[1]','NVARCHAR(MAX)')+' '+t.value('Model[1]','NVARCHAR(MAX)') AS CarType FROM @cars.nodes('/Cars/Car') x(t);

Result:

Volkswagen Eurovan

Honda CR-V

———

https://docs.microsoft.com/en-us/sql/relational-databases/xml/retrieve-and-query-xml-data
https://stackoverflow.com/questions/4855476/extract-a-value-from-sql-xml-field
http://control.cyriouswiki.com/How-To+Write+SQL+to+Extract+an+XML+Value+from+a+Database+Field
https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-xml-questions-you-were-too-shy-to-ask/

Transforming XML Data Into Another Format Using XSLT

The standard way for you to transform XML data into another format is with Extensible Stylesheet Language Transformations (XSLT). You can convert XML documents into HTML, plain text, or a different XML schema using the built-in XSLTRANSFORM function. You can easily build an HTML page outputting the data from an XML file using XSLT.

———

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.xml.doc/doc/c0050648.html