Tohuwabohu excorcism

• Quotes

  “	don't use it, it is disabled in some databases
  '	wrap Strings
  `	wrap table names or columns

The Details about MySQL are here.

The tool, which generated the picture is called MySQL Workbench

Select Columns “Title,Salesrank” from the album databse, with the salesrank more than 10000:

SELECT  Title,Salesrank FROM album WHERE 10000;

Merges the duplicates

Select all Labels from the album database:

SELECT DISTINCT Label  FROM album WHERE 1;

SELECT DISTINCT Country FROM Customers

WHERE Country LIKE "M%" 
OR Country LIKE "S%"
OR PostalCode BETWEEN 2000 AND 3000
OR City IN ('London', 'Paris')



SELECT * FROM Customers
WHERE ( CustomerName LIKE 'G%' OR CustomerName LIKE 'S%')
AND NOT Country = 'Spain';

SELECT * FROM Customers
WHERE ( CustomerName LIKE 'G%' OR CustomerName LIKE 'S%')
AND NOT Country IN ('Spain', 'USA');

SELECT * FROM Customers
WHERE ( Country = 'Spain' AND CustomerName LIKE 'G%')
OR ( Country = 'Mexico')

Select all Titles and Prices ordered by the price, ascending | descending

SELECT Title, Price FROM album WHERE 1 
ORDER BY Price ASC

SELECT Title, Price FROM album WHERE 1 
ORDER BY Price DESC

SELECT * FROM Customers
ORDER BY Country, CustomerName;

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

Select all Titles and Prices ordered by the price, ascending | descending

SELECT Title, Price FROM album WHERE 1 ORDER BY Price ASC
SELECT Title, Price FROM album WHERE 1 ORDER BY Price DESC

--- 92	Cardinal	Tom B. Erichsen	Skagen 21	Stavanger	4006	Norway
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('AALfreddo','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

--- 92	Cardinal	null	null 	Stavanger	null	Norway
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');


--- insert many groups with parenthesis
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

To handle NULLs correctly, SQL provides two special comparison operators: IS NULL and IS NOT NULL.

They return only true or false and are the best practice for incorporating NULL values into your queries.

See how those evaluate to unknown?

+---------+---------+---------+---------+
|    =    |  TRUE   |  FALSE  | UNKNOWN |
+---------+---------+---------+---------+
| TRUE    | TRUE    | FALSE   | UNKNOWN |
| FALSE   | FALSE   | TRUE    | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
+---------+---------+---------+---------+

That means a query comparing null with = returns nothing.

--- 
--- no result
SELECT * FROM Customers WHERE Country = NULL

--- comparing with IS works
--- Number of Records: 2
SELECT * FROM Customers WHERE Country IS NULL


-- invert via NOT
SELECT * FROM Customers WHERE Country IS NOT NULL

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;


UPDATE Customers
SET Country='Burkinafaso'
WHERE Country IS NULL;

UPDATE Customers
SET ContactName='Juan';

--- delete with where
DELETE FROM Customers
WHERE Country == 'Burkinafaso'


--- remove all
Delete from Customers

--- delete with where
DROP TABLE Customers;
--- You have made changes to the database.

--- top 3 or LIMIT
SELECT TOP 3 * FROM Customers;
SELECT TOP 10 * FROM Customers ORDER BY CustomerID

SELECT TOP 50 Percent * FROM Customers;

SELECT * FROM Customers ORDER BY CustomerID DESC LIMIT 10;

SELECT TOP 5 * FROM
 (SELECT * FROM Customers ORDER BY CustomerName ASC)

CustomerName	Country

Alfreds Futterkiste 	Germany 
Ana Trujillo Emparedados y helados 	Mexico 
Antonio Moreno Taquería 	Mexico 
Around the Horn 	UK 
Berglunds snabbköp 	Sweden 

SELECT MIN(PostalCode) FROM Customers
WHERE Country = "Germany"

SELECT MAX(PostalCode) FROM Customers
WHERE Country = "Germany"

--- count PostalCodes from Germany in this DB
SELECT Count(PostalCode) 
FROM Customers
WHERE Country = "Germany"

--- count how many DISTINCT countries do we have in db
--- as nested query
Select COUNT(Country) FROM
( SELECT DISTINCT Country FROM Customers)

--- more efficient 
SELECT COUNT(DISTINCT Price) FROM Products;
SELECT COUNT(DISTINCT Country) FROM Customers;

SELECT SUM(Quantity)
FROM OrderDetails;

SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProdictId = 11;


--- use expressions in SUM to figure out the total revenue from all sold products cross all orders
SELECT SUM(Price * Quantity)
FROM OrderDetails
LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID;

SELECT AVG(Price)
FROM Products;

--- below average
SELECT * FROM Products
WHERE price < (SELECT AVG(price) FROM Products);

SELECT * FROM Customers
WHERE city LIKE 'L_nd__';

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

--- min 2 chars starts with a
SELECT * FROM Customers
WHERE CustomerName LIKE 'a_%';

--- any of the chards in the set: starts with b,s,p
SELECT * FROM Customers
WHERE CustomerName LIKE '[bsp]%';

SELECT * FROM Customers
WHERE CustomerName LIKE '[a-f]%';

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

--- alphabetically sorted
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;

--- "MyIDAlias With empty space"	Customer
--- 1 	Alfreds Futterkiste 
SELECT TOP 1 CustomerID AS "MyIDAlias With empty space", CustomerName AS Customer
FROM Customers;


--- let the DB concat address
--- Address
--- Obere Str. 57, 12209, Berlin, Germany
SELECT CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers
WHERE CustomerName="Alfreds Futterkiste";

Aliases to shorten table names, to shorten them

--- long
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;


-- short
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

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

--- The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;



--- The following SQL statement lists the number of employees having less than 20 sales
SELECT e.EmployeeID, LastName, FirstName, COUNT(o.OrderID) As Sales  FROM [Employees] AS e
LEFT JOIN "Orders" AS o
ON o.EmployeeID = e.EmployeeID
GROUP BY e.EmployeeID
HAVING COUNT(o.OrderID) < 20
ORDER BY Sales  DESC, FirstName DESC, LastName  ASC

--- suppliers with products of price under 20
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);



--- employees with orders
SELECT LastName
FROM Employees
WHERE NOT EXISTS (SELECT OrderID FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.

--- product name with productID having quantity=10 somewhere
SELECT ProductName
FROM Products
WHERE ProductID = ANY
  (SELECT ProductID
  FROM OrderDetails
  WHERE Quantity = 10);

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

Case expression

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

--- fall back to 0 for NULL "UnitsOnOrder"
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;


--- using boolean ISNULL
SELECT OrderID, 
CASE
    WHEN ISNULL(AmountDue) THEN "Paid in full"
    WHEN DATE(DueDate) < date(NOW()) THEN "Order is past due"
    ELSE CONCAT("Order is due on ", CONVERT(VARCHAR, DueDate, 121))
END
FROM OrderDetails;

--- procedure
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

--- execute
EXEC SelectAllCustomers;



--- procedure with args
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

--- exec
EXEC SelectAllCustomers @City = 'London';

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Ignores null values in Orders and Customers tables

SELECT * FROM Orders AS o
JOIN Customers AS c
 ON c.CustomerID = o.CustomerID
 WHERE OrderID > 10441


-- join 3 tables together
--- Achtung - there is only 1 SELECT but multiple joins
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders 
      INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
      INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

Takes null CustomerIDs from both tables.

Also shows Customers without Orders.

And Orders without Customers.

SELECT * FROM Orders AS o
FULL JOIN Customers AS c
 ON c.CustomerID = o.CustomerID
 WHERE OrderID > 10441

--- same as FULL OUTER
SELECT * FROM Orders AS o
FULL OUTER JOIN Customers AS c
 ON c.CustomerID = o.CustomerID
 WHERE OrderID > 10441

SELECT * FROM Orders AS o
LEFT JOIN Customers AS c
 ON c.CustomerID = o.CustomerID
 WHERE OrderID > 10441

SELECT * FROM Orders AS o
RIGHT JOIN Customers AS c
 ON c.CustomerID = o.CustomerID
 WHERE OrderID > 10441

-- show customer pairs from the same city
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City 
ORDER BY A.City;

-- show the customers, who are in the same city, like "Rancho grande"
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.CustomerName = "Rancho grande"
AND A.City = B.City 
ORDER BY A.City;

-- all cities, distinct union

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;


-- UNION ALL means, cities are NOT unique

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

When putting together Customer, Supplier you can remember who is who

by adding a AS Role column.

It will contain values Customer / Supplier

SELECT 'Customer' AS Role, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers

Waehle die DB aus, liste die tabellen.

use mydatabase
show tables

Führen Sie Titel, Preis und Verkaufsrang aller Alben des Künstlers Sting auf. Sortieren Sie die Liste aufsteigend nach dem Verkaufsrang.

SELECT title, price, salesrank WHERE Artist="Sting" ORDER BY Salesrank ASC;

Führen Sie die ersten 5 Tracks und den Albumtitel der Alben auf, denen kein Label zugeordnet ist.

SELECT track.trackno, track.tracktitle, album.title FROM
track INNER JOIN
album ON
track.fromalbum = album.asin
WHERE album.label IS NULL
LIMIT 5;

Listen Sie für jedes Label den durchschnittlichen Verkaufsrang der von ihm verlegten Alben auf. Es sollten nur Label aufgeführt werden, die mindestens 50 Alben verlegen.

SELECT album.label, AVG(album.Salesrank) As AvgAlbumSalesrank, COUNT(ASIN) AS AlbumsCnt 
FROM album
WHERE 1
GROUP BY Label
HAVING COUNT(ASIN)>=50

Führen Sie neben Titel und Künstler die minimale und maximale Kundenbewertung von Alben auf, die zu einem Album von Sting direkt ähnlich sind.

SELECT Artists.artist, Artists.title,Ratings.minRating, Ratings.maxRating  FROM

(SELECT * FROM
	
	(SELECT similardest  FROM
		(SELECT * FROM album WHERE Artist="Sting") AS StingAlbums
		LEFT JOIN albumsimilar 
		ON StingAlbums.asin=albumsimilar.SIMILARSOURCE
	) As SimilarIds
	
LEFT JOIN album
ON album.asin=SimilarIds.similardest) As Artists

LEFT JOIN

(SELECT album.artist, MAX(rating) As maxRating, MIN(rating) As minRating FROM
customerreview INNER JOIN
album ON
album.ASIN=customerreview.ALBUM
GROUP BY Artist) AS Ratings

ON Artists.artist = Ratings.artist;

Listen Sie pro Kunden seine Anzahl von Bewertungen und seine durchschnittliche Bewertung auf. Kunden, die auch eine Bewertung eines Sting-Albums durchgeführt haben, sollen in dem Ergebnis nicht auftreten.

CREATE VIEW StingRaters
AS
SELECT DISTINCT customerreview.customerid FROM
customerreview
LEFT JOIN album 
ON album.ASIN=customerreview.album
WHERE album.artist="Sting";

SELECT customerreview.customerid, COUNT(customerreview.rating), AVG(customerreview.rating) 
FROM customerreview
WHERE customerreview.customerid 
NOT IN (SELECT * FROM StingRaters)
GROUP BY customerreview.customerid

Drop VIEW Stingraters;

Führen Sie die Namen der ListMania-Listen an, die (mindestens) zwei Alben enthalten, die auch von Amazon als ähnlich bezeichnet werden.

SELECT Listname 
FROM

(SELECT DISTINCT BothJoined.LISTID1 AS LISTID
FROM
	(SELECT FirstJoin.LISTID1, FirstJoin.ALBUM1, FirstJoin.SIMILARSOURCE, FirstJoin.SIMILARDEST, listmaniaentry.LISTID AS LISTID2, listmaniaentry.ALBUM AS ALBUM2 
	FROM

		(SELECT l1.`LISTID` AS LISTID1, l1.`ALBUM` AS ALBUM1, albumsimilar.SIMILARSOURCE, albumsimilar.SIMILARDEST
		FROM listmaniaentry AS l1
		LEFT JOIN albumsimilar
		ON l1.ALBUM=albumsimilar.SIMILARSOURCE) AS FirstJoin

	LEFT JOIN 
		listmaniaentry
	ON FirstJoin.SIMILARDEST=listmaniaentry.ALBUM) AS BothJoined
WHERE LISTID1=LISTID2 ) ListIds

NATURAL JOIN Listmanialist

Führen Sie die Titel und Künstler aller Alben auf, die zwar von Kunden bewertet wurden,für die jedoch keine Track-Informationen in der Datenbank gespeichert sind.

SELECT * FROM
(SELECT album.asin, album.title, album.artist 
FROM album 
WHERE album.asin NOT IN (SELECT FROMALBUM FROM track) ) AS WithTrackInfo
WHERE WithTrackInfo.asin IN (SELECT ALBUM FROM customerreview WHERE RATING IS NOT NULL)

Listen Sie die IDs aller Kunden, die im Durchschnitt schlechter bewerten als der Gesamtdurchschnitt der Kundenbewertungen.

SELECT AvgCustomerRating.CUSTOMERID
FROM
(SELECT CUSTOMERID, AVG(rating) AS AvgRating
FROM customerreview 
GROUP BY Customerid) AS AvgCustomerRating
WHERE AvgCustomerRating.AvgRating < (SELECT AVG(customerreview.rating) FROM customerreview WHERE 1)

Ich habe eine Tabelle mit Zeiten und Zustaenden in der folgenden Form:

Date        Time    Device  Configuration
01.01.2012  12.00   8193    12345
01.01.2012  12.15   8193    12345
01.01.2012  12.35   8193    22375
01.01.2012  12.37   7191    32335

Wie findet man die max. configurations fuer jedes Date-Device Paar?

Date        Device  Configuration
01.01.2012  8193    22375
01.01.2012  7191    32335

Antwort:
“Group By” Kann mehrere Spalten nehmen und die Passenden operationen fuer jede Kombination dieser Spalten ausfuehren.

select `date`,`Device`,max(Configuration) as MaxConfig from logData
group by `date`,`Device`

First Stop mysql service.

# /etc/init.d/mysqld stop

Start MySQL server without password:

# mysqld_safe   – -skip-grant-tables &

Connect to mysql server using mysql client:

# mysql -u root

Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD(“NEW-PASSWORD”) where User=’root’;
mysql> flush privileges;
mysql> quit

Finally restart MySQL Server and test it.

# /etc/init.d/mysqld restart
# mysql -u root -p

open

/etc/mysql/my.cnf

add

bind-address            = 127.0.0.1

LOAD DATA LOCAL INFILE 'D:\\1PROJEKTE\\1Papierstaus\\DATA\\LVV\\2012-09-11_printing-context.lvl.txt.csv'
INTO TABLE `tbl_name`
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(`SystemTemperatur`, `Geraete-Nr.`, `Aktives-Setup`, `Firmware-Version`, `b4Ticket-Version`, `Fahrzeug-Nr.`, `Fahrer-Nr.`, `Transition`, `Datum`, `Zeitstempel`, `Logdatei`, `printingState`, `printedLines`, `linesToPrint`, `printError`, `cutterJam`, `paperInTray`, `cavity1Jam`, `cavity2Jam`, `paperAtEntry`, `coverClosed`, `coverLocked`, `temperatureCritical`, `bufferUnderrun`, `timeSinceLastPrint`, `analogPaperInTray`, `analogCavity1Jam`, `analogCavity2Jam`, `analogCoverLocked`, `analogPaperAtEntry`, `analogCoverClosed`, `analogCutterJam`, `analog24V`, `analogV`)