sql example

-- SQL examples ---------------------------------------------------------------------------------------------------

-- http://inf-schule-db.informatik.uni-kl.de/SQLterra1.php --------------------------------------------------------
-- https://www.inf-schule.de/information/datenbanksysteme/terra/relationaledb --------------------------------------
CREATE TABLE `land` (
  `LNR` varchar(4) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Einwohner` decimal(20,2) DEFAULT NULL,
  `Flaeche` int(11) DEFAULT NULL,
  `Hauptstadt` varchar(30) DEFAULT NULL,
  `Kontinent` varchar(15) DEFAULT NULL,
  `KontinentFlaeche` int(11) DEFAULT NULL,
  `KontinentEinwohner` int(11) NOT NULL
);
-- ----------------------------------------------------------------------------------------------------------------
INSERT INTO `land` (`LNR`, `Name`, `Einwohner`, `Flaeche`, `Hauptstadt`, `Kontinent`, `KontinentFlaeche`, `KontinentEinwohner`) VALUES
('A', 'Österreich', '8.51', 83879, 'Wien', 'Europa', 10, 733),
('AC', 'Antigua und Barbuda', '0.09', 443, 'Saint John s', 'Nordamerika', 24, 523),
('AF', 'Afghanistan', '33.40', 652230, 'Kabul', 'Asien', 44, 4010),
('AG', 'Algerien', '37.40', 2381741, 'Algiers', 'Afrika', 30, 944),
('AL', 'Albanien', '2.80', 28748, 'Tirana', 'Europa', 10, 733);
-- ----------------------------------------------------------------------------------------------------------------
CREATE TABLE `ort` (
  `Name` varchar(30) DEFAULT NULL,
  `Land` varchar(50) NOT NULL,
  `Einwohner` int(11) DEFAULT NULL,
  `Laenge` decimal(10,4) DEFAULT NULL,
  `Breite` decimal(10,4) DEFAULT NULL
);
-- ----------------------------------------------------------------------------------------------------------------
INSERT INTO `ort` (`Name`, `Land`, `Einwohner`, `Laenge`, `Breite`) VALUES
('Kabul', 'Afghanistan', 1400000, '69.0000', '34.0000'),
('Kandahar', 'Afghanistan', 323900, '65.0000', '31.0000'),
('Masar-e Scharif', 'Afghanistan', 250000, '67.0000', '36.0000'),
('Abnub', 'Ägypten', 70791, '31.1500', '27.2700'),
('Abo el Matamer', 'Ägypten', 47432, '30.1800', '30.9200');
-- ----------------------------------------------------------------------------------------------------------------
SELECT name, einwohner, hauptstadt
  FROM land
ORDER BY einwohner DESC
-- ----------------------------------------------------------------------------------------------------------------
SELECT name
  FROM land
WHERE kontinent='europa'
-- ----------------------------------------------------------------------------------------------------------------
SELECT name, einwohner
  FROM land
WHERE einwohner>=10 and einwohner<=100
ORDER BY einwohner DESC
-- ----------------------------------------------------------------------------------------------------------------
SELECT name, hauptstadt
  FROM land
WHERE name LIKE '%Arm%' or name LIKE '%Bein%'
-- ----------------------------------------------------------------------------------------------------------------
SELECT name, laenge
  FROM ort
WHERE land='Deutschland' and laenge<7.768
-- ----------------------------------------------------------------------------------------------------------------
SELECT name, hauptstadt, kontinent
  FROM land
WHERE kontinent IN ('Asien','Australien')

-- https://www.youtube.com/watch?v=91iNR0eG8kE --------------------------------------------------------------------
import mysql.connector
db = mysql.connector.connect(
	host="localhost",
	user="root",
	passwd="root",
	database="testdatabase"
	)
mycurser = db.cursor()
mycursor.execute("CREATE TABLE Person (name VARCHAR(50), age smallint UNSIGNED, personID int PRIMARY KEY AUTO_INCREMENT)")
mycursor.execute("DESCRIBE Person")
mycursor.execute("INSERT INTO Person (name, age) VALUES ('tech with tim', 45)")
mycursor.execute("SELECT * FROM Person")
-- https://www.youtube.com/watch?v=kUBGiABFFHc --------------------------------------------------------------------
import mysql.connector
from datetime import datetime
db = mysql.connector.connect(
	host="localhost",
	user="root",
	passwd="root",
	database="testdatabase"
	)
mycursor = db.cursor()
mycursor.execute("CREATE TABLE Test (name varchar(50) NOT NULL, created datetime NOT NULL, gender ENUM('M', 'F', 'O'), id int PRIMARY KEY NOT NULL AUTO_INCREMENT)")
mycursor.execute("INSERT INTO Test (name, created, gender) VALUES (?,?,?)", ('Tim', datetime.now(), 'M'))
mycursor.execute("SELECT * FROM Test WHERE gender = 'M' ORDER BY id DESC")
mycursor.execute("ALTER TABLE Test ADD COLUMN food VARCHAR(50) NOT NULL")
mycursor.execute("DESCRIBE Test")
mycursor.fetchone()
mycursor.execute("ALTER TABLE Test DROP food")
mycursor.execute("ALTER TABLE Test CHANGE name first_name VARCHAR(50)")
db.commit()