-- SQL statements, using the Kratylos DB

mysql kratylos
	-- uses .mylogin.cnf to safely store user name and password

show tables;
	-- commands must end with semicolon.
	-- table and column names are case-insensitive.

describe projects;

-- | Field        | Type             | Null | Key | Default 
-- +--------------+------------------+------+-----+---------
-- | language     | varchar(50)      | NO   | MUL | NULL    
-- | langCode     | varchar(7)       | YES  |     | NULL    
-- | version      | varchar(255)     | YES  |     | NULL    
-- | provenance   | text             | YES  |     | NULL    

-- querying

select * from projects where language = 'BelizeKriolEnglish';
select * from projects where language = 'badaga';

select language, version, provenance from projects
	where language = 'BelizeKriolEnglish';
	-- multi-line commands work; the semicolon finishes them.

select language from projects where provenance like '%ebible%';

select language from projects where language like 'aust%';

select language from projects where language like 'aust%'
	group by language;

select language, version, `public` from projects 
	where version like '%water%';

select language, version from projects 
	where version like '%water%' and public = 1;

select language, version from projects 
	where version like '%water%' and public = 1 
	order by version desc;

describe languages;

-- | Field     | Type         | Null | Key | Default 
-- | code      | varchar(7)   | NO   | PRI | NULL    
-- | name      | varchar(100) | YES  |     | NULL    

select projects.version, langCode, languages.name
	from projects
	join languages
	where projects.version like '%water%'
		and projects.langCode = languages.code;

select p.version, g.name
	from projects as p
	join languages as g
	where p.version like '%water%'
		and p.langCode = g.code;

select version, name from projects
	join languages 
	where version like '%water%' and langCode = code;

select substr(version,1,20), name from projects
	join languages 
	where version like '%water%' and langCode = code;

select substr(version,1,20), upper(name) from projects
	join languages 
	where version like '%water%' and langCode = code;

select count(*) from projects;

select format(count(*), 2) from projects;

-- creating/destroying table, inserting/deleting row

create table cs316 (
	mykey varchar(100) not null default '',
		-- general advice: don't allow null fields
	value varchar(100) not null default '',
	primary key (mykey)
);

describe cs316;

insert into cs316 values ('foo', 'fooValue');
insert into cs316 values ('bar', 'barValue');
insert into cs316 values ('foo', 'fooValue'); -- disallowed

select * from cs316;

select mykey from cs316 where value like '%rv%';

delete from cs316 where `myKey` = 'foo';

drop table cs316;

-- vim:filetype=sql nospell