![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Technical Articles » Collations and Comparisons |
CREATE TABLE "Albums" ( "No" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1), "Name" VARCHAR(50) COLLATE "ANSI_CI", "Artist" VARCHAR(30) COLLATE "ANSI_CI" NOT NULL, "Genre" VARCHAR(20) COLLATE "ANSI_CI" NOT NULL, "Year" INTEGER NOT NULL, "Label" VARCHAR(30) COLLATE "ANSI_CI" NOT NULL, "Imported" BOOLEAN DEFAULT FALSE NOT NULL, "NumDiscs" INTEGER DEFAULT 1 NOT NULL, "CoverArt" BLOB COMPRESSION 6, "Comments" CLOB COLLATE "ANSI_CI", "PurchasedOn" DATE, "PurchasePrice" DECIMAL(20,2), CONSTRAINT "No" PRIMARY KEY ("No"), CONSTRAINT "Artist" FOREIGN KEY ("Artist") REFERENCES "Artists" ("Name"), CONSTRAINT "Genre" FOREIGN KEY ("Genre") REFERENCES "Genres" ("Name"), CONSTRAINT "Label" FOREIGN KEY ("Label") REFERENCES "Labels" ("Name"), CONSTRAINT "UniqueKey_Name" UNIQUE ("Name") ) DESCRIPTION 'Contains CD albums in the collection'
SELECT * FROM albums WHERE Genre = 'ROCK'
SELECT * FROM albums WHERE Genre COLLATE ANSI = 'ROCK'
SELECT * FROM albums WHERE Genre COLLATE ANSI = 'Rock'
CREATE PROCEDURE Summaries(IN "SummaryType" CHAR(1) COLLATE ANSI_CI) BEGIN DECLARE Result CURSOR WITH RETURN FOR Stmt; CASE SummaryType -- Genres summary WHEN 'G' THEN BEGIN PREPARE Stmt FROM 'SELECT Genre AS Name, COUNT(Name) AS NumAlbums, SUM(PurchasePrice) AS TotalPurchases FROM Albums GROUP BY Genre'; OPEN Result; END; -- Labels summary WHEN 'L' THEN BEGIN PREPARE Stmt FROM 'SELECT Label AS Name, COUNT(Name) AS NumAlbums, SUM(PurchasePrice) AS TotalPurchases FROM Albums GROUP BY Label'; OPEN Result; END; -- Artists summary WHEN 'A' THEN BEGIN PREPARE Stmt FROM 'SELECT Artist AS Name, COUNT(Name) AS NumAlbums, SUM(PurchasePrice) AS TotalPurchases FROM Albums GROUP BY Artist'; OPEN Result; END; END CASE; END
CREATE PROCEDURE Summaries(IN "SummaryType" CHAR(1)) BEGIN DECLARE Result CURSOR WITH RETURN FOR Stmt; CASE SummaryType COLLATE ANSI_CI -- Genres summary WHEN 'G' THEN BEGIN PREPARE Stmt FROM 'SELECT Genre AS Name, COUNT(Name) AS NumAlbums, SUM(PurchasePrice) AS TotalPurchases FROM Albums GROUP BY Genre'; OPEN Result; END; -- Labels summary WHEN 'L' THEN BEGIN PREPARE Stmt FROM 'SELECT Label AS Name, COUNT(Name) AS NumAlbums, SUM(PurchasePrice) AS TotalPurchases FROM Albums GROUP BY Label'; OPEN Result; END; -- Artists summary WHEN 'A' THEN BEGIN PREPARE Stmt FROM 'SELECT Artist AS Name, COUNT(Name) AS NumAlbums, SUM(PurchasePrice) AS TotalPurchases FROM Albums GROUP BY Artist'; OPEN Result; END; END CASE; END
SELECT Genre AS Name, COUNT(Name) AS NumAlbums, SUM(PurchasePrice) AS TotalPurchases FROM Albums GROUP BY Genre
SELECT Genre AS Name, COUNT(Name) AS NumAlbums, SUM(PurchasePrice) AS TotalPurchases FROM Albums GROUP BY Genre COLLATE ANSI
SELECT MIN(Genre) AS Name FROM Albums
SELECT MIN(Genre COLLATE ANSI) AS Name FROM Albums
This web page was last updated on Wednesday, January 13, 2021 at 12:15 AM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |