Skip to content

Instantly share code, notes, and snippets.

@k-gun k-gun/nope.sql

Last active Jul 3, 2019
Embed
What would you like to do?
-- 2. Soru
SELECT "class", count(*) AS "count"
FROM "Student"
WHERE "grade" >= 70
GROUP BY "class"
HAVING count(*) > 1 -- 10
ORDER BY "count" DESC
-------
-- https://www.generatedata.com/
CREATE TABLE "Student"
(
"id" serial,
"firstName" varchar(20),
"lastName" varchar(20),
"class" varchar(20),
"grade" numeric,
CONSTRAINT "Student_pk" PRIMARY KEY ("id")
)
CREATE TABLE "Author"
(
"id" serial,
"firstName" varchar(20),
"lastName" varchar(20),
CONSTRAINT "Author_pk" PRIMARY KEY ("id")
)
CREATE TABLE "Book"
(
"id" serial,
"authorId" integer NOT NULL,
"name" varchar(100),
"isbn" varchar(20),
"isSigned" boolean,
CONSTRAINT "Book_pk" PRIMARY KEY ("id"),
CONSTRAINT "Book_fk" FOREIGN KEY ("authorId") REFERENCES "Author" ("id")
)
--------
INSERT INTO "Student" ("id","firstName","lastName","class","grade") VALUES (1,'Cooper','Dickson','TM-20B',37),(2,'Aretha','Barrett','TM-20C',56),(3,'Kathleen','Dunn','TM-20A',14),(4,'Cain','Trevino','SB-50B',78),(5,'Margaret','Duke','SB-5OA',4),(6,'Trevor','Anthony','TM-20B',62),(7,'Mallory','Gillespie','TM-20C',76),(8,'Maris','Mays','SB-50C',39),(9,'Benjamin','Alford','TM-20A',81),(10,'Eden','Mayer','TM-20C',54),(11,'Ivan','Holcomb','SB-50C',68),(12,'Ingrid','Carlson','TM-20C',95),(13,'Nehru','Sanchez','SB-50B',47),(14,'Chelsea','Holloway','TM-20B',64),(15,'Wyoming','Leach','SB-5OA',48),(16,'Zephania','Weber','SB-5OA',69),(17,'Tarik','Flowers','TM-20B',50),(18,'Keane','Cannon','SB-5OA',83),(19,'Victoria','Garrison','TM-20C',43),(20,'Ciaran','Hobbs','TM-20B',51),(21,'Neil','Ayala','TM-20C',88),(22,'Dorothy','Mcpherson','SB-5OA',79),(23,'Timon','Horn','TM-20B',12),(24,'Silas','Jacobs','TM-20C',83),(25,'Brady','Marks','TM-20B',57),(26,'Uma','Kelley','TM-20B',86),(27,'Brynn','Benjamin','SB-50B',38),(28,'Eliana','Macias','SB-50B',86),(29,'Nero','Kirkland','TM-20A',71),(30,'Elijah','Dennis','SB-5OA',85),(31,'Felix','Jenkins','SB-50C',40),(32,'Craig','Fields','SB-50B',32),(33,'Yeo','Alston','TM-20C',7),(34,'Wade','Rush','SB-50B',48),(35,'Seth','Barker','TM-20B',70),(36,'Quintessa','Swanson','SB-50C',33),(37,'Piper','White','TM-20C',68),(38,'Kyla','White','TM-20B',73),(39,'Summer','Roberts','TM-20A',10),(40,'Keaton','Nichols','SB-50C',14),(41,'Urielle','Levy','SB-5OA',1),(42,'Jason','Shannon','SB-50C',4),(43,'Fulton','Gonzalez','TM-20B',41),(44,'Jolene','Floyd','SB-50C',12),(45,'Abdul','Howe','TM-20A',72),(46,'Callum','Downs','TM-20C',32),(47,'Winifred','Baldwin','TM-20A',14),(48,'Colby','Rutledge','SB-50B',12),(49,'Hyatt','Combs','TM-20C',29),(50,'Kathleen','Curtis','TM-20A',61),(51,'Desirae','Vega','TM-20A',60),(52,'Sybill','Osborne','SB-5OA',64),(53,'Colby','Dunn','SB-5OA',31),(54,'Lucy','Richmond','TM-20C',25),(55,'Herrod','Golden','TM-20C',74),(56,'Emerson','Vincent','SB-50C',95),(57,'Vladimir','Flynn','SB-50C',97),(58,'Ciaran','Summers','TM-20B',79),(59,'Renee','Hardin','TM-20A',88),(60,'Samson','Chandler','TM-20C',41),(61,'Calista','Valencia','SB-50C',25),(62,'Shaine','Hood','SB-50C',27),(63,'Raymond','Owens','SB-50B',78),(64,'Shaeleigh','Cote','SB-50C',61),(65,'Reece','Humphrey','SB-50C',99),(66,'Jaime','Estes','TM-20C',20),(67,'Chiquita','Levine','SB-50B',11),(68,'Bernard','Montgomery','TM-20A',87),(69,'Jin','Garner','SB-50C',45),(70,'Callie','Pate','SB-50C',65),(71,'Jocelyn','Odom','TM-20C',84),(72,'Benjamin','Harvey','SB-5OA',89),(73,'Leila','King','TM-20A',36),(74,'Ivor','Riddle','SB-5OA',95),(75,'Xenos','Sheppard','SB-5OA',78),(76,'Colette','William','TM-20B',97),(77,'Larissa','Alexander','TM-20B',38),(78,'Fay','Mayo','SB-5OA',14),(79,'Charissa','Finch','SB-50B',48),(80,'Elijah','Ewing','TM-20A',86),(81,'Hedy','Chavez','SB-50B',60),(82,'Kaseem','Kelley','SB-50C',36),(83,'Daniel','Curry','SB-50C',38),(84,'Quentin','Mcclain','SB-5OA',92),(85,'Willow','Hawkins','SB-50B',4),(86,'Eleanor','Sutton','SB-5OA',45),(87,'Otto','Hurley','TM-20B',10),(88,'Lee','Murray','TM-20C',71),(89,'Katelyn','Conner','SB-50B',15),(90,'Gail','Levine','SB-5OA',13),(91,'Conan','Wade','TM-20B',71),(92,'Aline','Sandoval','SB-50B',83),(93,'Adara','Farrell','SB-50C',98),(94,'Honorato','Hawkins','TM-20C',81),(95,'Beau','Leon','SB-50C',44),(96,'Cyrus','Franco','TM-20C',41),(97,'Breanna','Bridges','SB-50C',29),(98,'Colin','Ellison','TM-20B',1),(99,'Hanna','Faulkner','TM-20C',25),(100,'Richard','Patrick','SB-50C',13);
INSERT INTO "Author" ("id","firstName","lastName") VALUES (1,'Katell','Walker'),(2,'Ashely','Sharpe'),(3,'Alexander','Trujillo'),(4,'Elaine','Meyer'),(5,'Tate','Carr'),(6,'Paloma','Grimes'),(7,'Charles','Harvey'),(8,'Upton','Hammond'),(9,'Sylvester','Fulton'),(10,'Dai','Rosales'),(11,'Pascale','York'),(12,'Echo','Yang'),(13,'Emerald','Vinson'),(14,'Caryn','Davis'),(15,'Stacey','Simon'),(16,'Liberty','Boyer'),(17,'Noelani','Velez'),(18,'Mira','Osborne'),(19,'Cameran','Franco'),(20,'Melanie','Yates'),(21,'Yoko','Adkins'),(22,'Mary','Haley'),(23,'Eugenia','Bowers'),(24,'Chiquita','Rush'),(25,'Sloane','Mcbride'),(26,'Maxine','Conner'),(27,'Raja','Woods'),(28,'August','Griffith'),(29,'Malcolm','Tran'),(30,'Abraham','Walton'),(31,'Octavius','Douglas'),(32,'Jonas','Emerson'),(33,'Ashton','Chavez'),(34,'Kato','Padilla'),(35,'Donovan','Norman'),(36,'Lucius','Ortiz'),(37,'Anastasia','Spence'),(38,'Emerald','Merrill'),(39,'Buffy','Holman'),(40,'Eugenia','Massey'),(41,'Nathaniel','Whitfield'),(42,'Troy','Randolph'),(43,'Lance','Suarez'),(44,'Logan','Padilla'),(45,'Baker','Bentley'),(46,'Wing','Dillard'),(47,'Walter','Horn'),(48,'Jaden','Jensen'),(49,'Carson','Gilbert'),(50,'Ulric','Parsons'),(51,'Aidan','Cline'),(52,'Thor','Foster'),(53,'Gwendolyn','Zamora'),(54,'Caleb','Middleton'),(55,'Fredericka','Aguirre'),(56,'Shellie','Rodriguez'),(57,'Graiden','Gilmore'),(58,'Sierra','Charles'),(59,'Yolanda','Dominguez'),(60,'Kessie','Mcfarland'),(61,'Maggy','Macias'),(62,'Isabella','Cash'),(63,'Freya','Garcia'),(64,'Brett','Perry'),(65,'Devin','Kerr'),(66,'Zane','Duncan'),(67,'Nash','Dale'),(68,'Garth','Salazar'),(69,'Clementine','Kirk'),(70,'MacKensie','Willis'),(71,'Gareth','Irwin'),(72,'Camden','Sargent'),(73,'Grant','Chang'),(74,'Keane','Hahn'),(75,'Sophia','May'),(76,'Lester','Hart'),(77,'Ali','Morse'),(78,'Cameron','Rocha'),(79,'Xaviera','Wallace'),(80,'Hedwig','Martin'),(81,'Jeanette','Collins'),(82,'Emerson','Cortez'),(83,'Chancellor','Duran'),(84,'Emerald','Watson'),(85,'Geoffrey','Bowers'),(86,'Jeremy','Flowers'),(87,'Noble','Barrett'),(88,'Stacy','Mcknight'),(89,'Lucas','Morris'),(90,'Craig','Mcmahon'),(91,'Chaney','Conrad'),(92,'Iliana','Baxter'),(93,'Bruno','Wright'),(94,'Juliet','Stephenson'),(95,'Katelyn','Thornton'),(96,'Leandra','Graham'),(97,'Cadman','Craft'),(98,'Rana','Garner'),(99,'Azalia','Graves'),(100,'Gary','Banks');
INSERT INTO "Book" ("id","authorId","name","isbn","isSigned") VALUES (1,2,'Cum sociis natoque','1981-0104-010344',true),(2,9,'Non, luctus','1906-1105-145223',false),(3,32,'Libero et tristique pellentesque, tellus','1931-0618-062042',false),(4,62,'Egestas nunc','1850-0724-184142',false),(5,95,'Phasellus nulla','2020-1108-182450',false),(6,13,'A arcu, sed et','1998-1031-214310',true),(7,88,'Tellus, suspendisse sed dolor fusce','1858-0114-130348',false),(8,20,'Suspendisse commodo','1955-0427-115451',true),(9,70,'Neque tellus','2125-1209-200401',true),(10,97,'Eget tincidunt dui augue eu','2096-1201-181753',true),(11,30,'Mauris sagittis placerat','1908-1223-183254',false),(12,10,'Tortor aliquam adipiscing lacus','2113-0904-164947',false),(13,68,'Orci lobortis augue scelerisque','2136-0603-201329',false),(14,4,'Ultricies ornare, elit elit','1940-0306-001044',true),(15,33,'Egestas nunc sed libero','1951-0520-225957',false),(16,79,'Sit amet, eu','1824-0618-030031',false),(17,56,'Iss volutpat nul','1975-0405-070453',true),(18,39,'Magna tellus faucibus leo','1836-1225-134850',false),(19,14,'Nunc ut erat','1952-0512-112556',false),(20,60,'Non','1901-0616-225706',false),(21,35,'Mollis phasellus libero','1875-1231-112639',false),(22,75,'Porttitor scelerisque neque','2128-0610-003017',true),(23,72,'Di donec','2203-1001-171644',true),(24,49,'Aliquet nec, imperdiet nec, leos','1908-0919-164420',false),(25,3,'Aliquet, sem','2172-0606-221201',false),(26,32,'Malesuada fames ac','1932-0229-005859',false),(27,46,'Enim mi tempor','1969-1231-050451',false),(28,1,'Pellentesque ut ipsum ac','2000-0704-030650',true),(29,96,'Lacus pede sagittis augue','1845-0513-062349',true),(30,55,'Nam','1985-0515-113942',true),(31,90,'Nec luctus felis','2130-1215-145538',false),(32,48,'Libero mauris, aliquam eu, accumsan','2194-0926-002203',true),(33,3,'Libero at auctor ullamcorper, nisl','1968-0130-085039',true),(34,79,'Amet risus egestas','1988-0702-115651',true),(35,73,'Vitae nibh','1965-0101-210649',false),(36,72,'Curabitur de egesli','2057-0901-203223',false),(37,83,'Mauris','2177-1207-160354',false),(38,17,'Lectus eu quis','1909-0127-042724',false),(39,40,'Phasellus ornare','2152-0105-170947',false),(40,41,'Ligula nullam feugiat','2102-1018-134738',false),(41,1,'Fusce','1860-0204-002700',false),(42,54,'Sem consequat nec','1864-0113-031023',true),(43,12,'Aliquet molestie tellus','2059-0616-070616',true),(44,99,'Consequat dolor vitae dolor','1902-0209-150806',true),(45,66,'Enimas','2195-0207-223109',true),(46,79,'Ella por decoie','2063-0302-040901',false),(47,24,'Ametto il depor','2133-0519-135929',true),(48,1,'Aliquam gravida','2076-1204-010743',false),(49,13,'Netus et aliquet','1955-0802-011300',false),(50,40,'Aliquet','2135-1120-084431',false),(51,14,'Eu mauris eu','2172-1015-195822',false),(52,84,'Sapiens','2172-1015-042323',true),(53,71,'Ornare elit fermentum','1979-0321-185304',true),(54,73,'Risus el nunc','2209-0906-144737',false),(55,17,'Gravida de aliquam','1862-1214-120946',true),(56,100,'Sed tortorie','2031-0711-184321',true),(57,51,'Enean egestas hendrerit neque','1977-1002-221428',true),(58,96,'Fuscis mollis','1987-0812-053016',false),(59,28,'Parturient montes, nascetur ridiculus','2184-0706-192232',false),(60,48,'Purus','2070-0704-025133',true),(61,12,'Gravida mauris ut mi?','2075-1115-144053',false),(62,29,'Ornare','2106-0730-145134',false),(63,24,'Metus il facilisis lorem tristique aliquet','1992-1206-085622',true),(64,3,'Quis arcuis','1914-1201-045842',true),(65,32,'Cubilia curae; donecius?','1951-1206-090456',true),(66,58,'Eget metus','2189-0209-045100',false),(67,60,'An enima','1928-0404-074114',false),(68,68,'Diam dictum sapien, aenean massa!','1955-0311-220035',false),(69,67,'Molestie pharetra nibh','2112-0518-174507',true),(70,100,'Ligulas','1857-1012-182654',true),(71,43,'Accumsan convallis, ante lectus convallis','1880-0624-131318',false),(72,98,'Egests a scelerisque','2205-0117-164350',false),(73,43,'Ipsum suspendisse','1888-1129-151458',true),(74,73,'Sociis il Porttitor','2073-0621-212929',false),(75,90,'Mollis lectus pede et','1941-0110-160438',true),(76,46,'Dignissim maecenas egissi','2009-0201-052436',false),(77,60,'Aptent taciti sociosqu ad','1966-0331-222818',false),(78,65,'Eu temporus','2191-0705-035545',true),(79,39,'Consectetuer euismod est arcu','2091-1213-102937',false),(80,98,'Sr. Interdum','2188-0506-175323',false),(81,95,'Magna tellus faucibus','2068-0119-065357',true),(82,77,'Esta nunci','1931-0821-044602',true),(83,26,'Liberos!','1913-0918-052053',true),(84,100,'Quam urabitur vel lectus','1886-0723-055657',false),(85,47,'Crasse','2007-0112-034948',true),(86,45,'Diid sapiens','2047-1011-120900',false),(87,97,'Fermentum risus, at','2059-1008-051549',true),(88,2,'Mi, aliquis gravidas','1936-0103-070641',true),(89,22,'Velit cras ilis luctus','1865-0909-004710',false),(90,35,'Sit amet','2149-0622-022359',true),(91,56,'Posuere vulputate, lacuras','1960-0621-171349',true),(92,15,'Mam ligula','2149-0103-132727',false),(93,84,'Et netussis','2022-0308-110737',true),(94,59,'Sitioses il damius','2109-0705-044007',true),(95,5,'Suspendisse sagittis vitae','1952-0208-053952',true),(96,88,'Mollis vitae posuere at velit','2004-0625-024905',true),(97,86,'Sagittis tempors,','1899-0508-030357',false),(98,23,'Lacus quisque imperdiet erat','1986-1229-052724',true),(99,95,'Mauris ut quam','2210-1129-192419',true),(100,17,'Elementumini','1945-0916-004650',true);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.