SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
DROP TABLE IF EXISTS student_course; DROP TABLE IF EXISTS student; DROP TABLE IF EXISTS cohort; DROP TABLE IF EXISTS udegree_course; DROP TABLE IF EXISTS course; DROP TABLE IF EXISTS udegree; CREATE TABLE udegree ( udegree_id VARCHAR(50) PRIMARY KEY, udegree_name VARCHAR(50) NOT NULL ); INSERT INTO udegree (udegree_id, udegree_name) VALUES ('DS','Data Science'), ('IT','Information Technology'), ('CS','Computer Science'), ('MATH','Mathematics'); CREATE TABLE course ( course_id VARCHAR(50) PRIMARY KEY, course_name VARCHAR(50) NOT NULL, course_level INTEGER NOT NULL, course_credits INTEGER NOT NULL ); INSERT INTO course (course_id, course_name, course_level, course_credits) VALUES ('DBMS','Database management systems',9,15), ('ISD','Introduction to software development',9,10), ('IN','Introduction to networking',10,15), ('LINALG','Linear algebra',10,15), ('STAT','Statistics',9,20), ('ALG','Algorithms',10,15), ('CALC','Calculus',9,10); CREATE TABLE udegree_course ( udegree_id VARCHAR(50) NOT NULL REFERENCES udegree(udegree_id), course_id VARCHAR(50) NOT NULL REFERENCES course(course_id), mandatory INTEGER, -- 1 if it is mandatory, 0 if it is not. PRIMARY KEY (udegree_id, course_id) ); INSERT INTO udegree_course (udegree_id, course_id, mandatory) VALUES ('DS','DBMS',1), ('DS','ISD',1), ('DS','STAT',1), ('DS','ALG',0), ('IT','DBMS',1), ('IT','ISD',0), ('IT','IN',0), ('IT','LINALG',1), ('IT','CALC',1), ('CS','DBMS',1), ('CS','ISD',1), ('CS','IN',1), ('CS','STAT',0), ('CS','ALG',1), ('MATH','LINALG',0), ('MATH','STAT',1), ('MATH','CALC',1); CREATE TABLE cohort ( udegree_id VARCHAR(50) NOT NULL REFERENCES udegree(udegree_id), start_year INTEGER NOT NULL, PRIMARY KEY (udegree_id, start_year) ); INSERT INTO cohort (udegree_id, start_year) VALUES ('DS',2018), ('DS',2019), ('DS',2020), ('IT',2016), ('IT',2017), ('IT',2018), ('IT',2019), ('IT',2020), ('CS',2017), ('CS',2018), ('CS',2019), ('CS',2020), ('MATH',2018), ('MATH',2019), ('MATH',2020); CREATE TABLE student ( student_id VARCHAR(50) PRIMARY KEY, student_name VARCHAR(255) NOT NULL, student_lastname VARCHAR(255) NOT NULL, student_udegree_id VARCHAR(50) NOT NULL, student_start_year INTEGER NOT NULL, student_birthdate DATE NOT NULL, student_email VARCHAR(255) NOT NULL UNIQUE, FOREIGN KEY (student_udegree_id) REFERENCES udegree(udegree_id), FOREIGN KEY (student_udegree_id, student_start_year) REFERENCES cohort (udegree_id, start_year) ); INSERT INTO student (student_id, student_name, student_lastname, student_udegree_id, student_start_year, student_birthdate, student_email) VALUES ('s1','Mike','Roberts','DS',2018,'1984-06-17','mike.roberts@fakedomain.com'), ('s2','Bob','Michaels','DS',2018,'1988-08-18','bob.michaels@fakedomain.com'), ('s3','Elena','Johnson','DS',2018,'1993-06-05','elena.johnson@fakedomain.com'), ('s4','Fred','Nash','DS',2018,'2001-08-22','fred.nash@fakedomain.com'), ('s5','Arleen','Brown','DS',2018,'1991-12-22','arleen.brown@fakedomain.com'), ('s6','Anna','White','DS',2019,'1985-02-19','anna.white@fakedomain.com'), ('s7','Jeremy','Horn','DS',2019,'1990-03-06','jeremy.horn@fakedomain.com'), ('s8','Melissa','Stevens','DS',2019,'1999-06-12','melissa.stevens@fakedomain.com'), ('s9','Rick','Jackson','DS',2019,'1985-04-07','rick.jackson@fakedomain.com'), ('s10','Greg','Mason','DS',2019,'1998-09-15','greg.mason@fakedomain.com'), ('s11','Fred','White','DS',2020,'1991-03-26','fred.white@fakedomain.com'), ('s12','Jeremy','Brown','DS',2020,'1982-12-01','jeremy.brown@fakedomain.com'), ('s13','Arleen','Jackson','DS',2020,'1999-03-31','arleen.jackson@fakedomain.com'), ('s14','Rick','Nash','DS',2020,'2000-08-20','rick.nash@fakedomain.com'), ('s15','Melissa','Johnson','DS',2020,'1987-06-03','melissa.johnson@fakedomain.com'), ('s16','Greg','White','IT',2016,'1989-05-07','greg.white@fakedomain.com'), ('s17','Arleen','Jackson','IT',2016,'1981-09-18','arleen.jackson2@fakedomain.com'), ('s18','Fred','Michaels','IT',2016,'2000-03-03','fred.michaels@fakedomain.com'), ('s19','Fred','Brown','IT',2016,'1994-07-23','fred.brown@fakedomain.com'), ('s20','Anna','Horn','IT',2016,'1985-04-19','anna.horn@fakedomain.com'), ('s21','Mike','Nash','IT',2017,'1991-08-06','mike.nash@fakedomain.com'), ('s22','Fred','Stevens','IT',2017,'1981-12-22','fred.stevens@fakedomain.com'), ('s23','Elena','Brown','IT',2017,'1992-02-19','elena.brown@fakedomain.com'), ('s24','Rick','Brown','IT',2017,'1986-02-04','rick.brown@fakedomain.com'), ('s25','Greg','White','IT',2017,'1989-05-07','greg.white2@fakedomain.com'), ('s26','Arleen','Johnson','IT',2018,'1980-08-04','arleen.johnson@fakedomain.com'), ('s27','Rick','Jackson','IT',2018,'1982-08-22','rick.jackson2@fakedomain.com'), ('s28','Bob','Nash','IT',2018,'1988-07-15','bob.nash@fakedomain.com'), ('s29','Mike','Horn','IT',2018,'2003-08-22','mike.horn@fakedomain.com'), ('s30','Melissa','Roberts','IT',2018,'2000-10-16','melissa.roberts@fakedomain.com'), ('s31','Greg','White','IT',2019,'1989-05-07','greg.white3@fakedomain.com'), ('s32','Melissa','Stevens','IT',2019,'1990-10-26','melissa.stevens2@fakedomain.com'), ('s33','Jeremy','Mason','IT',2019,'2002-12-14','jeremy.mason@fakedomain.com'), ('s34','Anna','Nash','IT',2019,'1999-04-03','anna.nash@fakedomain.com'), ('s35','Elena','Roberts','IT',2019,'1999-03-17','elena.roberts@fakedomain.com'), ('s36','Jeremy','Michaels','IT',2020,'2001-01-19','jeremy.michaels@fakedomain.com'), ('s37','Fred','Stevens','IT',2020,'1990-06-23','fred.stevens2@fakedomain.com'), ('s38','Rick','Horn','IT',2020,'1988-06-10','rick.horn@fakedomain.com'), ('s39','Mike','Nash','IT',2020,'1986-02-23','mike.nash2@fakedomain.com'), ('s40','Greg','Mason','IT',2020,'1982-08-04','greg.mason2@fakedomain.com'), ('s41','Fred','Horn','CS',2017,'2000-07-17','fred.horn@fakedomain.com'), ('s42','Arleen','White','CS',2017,'2001-02-16','arleen.white@fakedomain.com'), ('s43','Greg','Brown','CS',2017,'2003-09-11','greg.brown@fakedomain.com'), ('s44','Jeremy','Jackson','CS',2017,'1992-04-02','jeremy.jackswon@fakedomain.com'), ('s45','Rick','Roberts','CS',2017,'1995-06-28','rick.roberts@fakedomain.com'), ('s46','Melissa','Nash','CS',2018,'1997-12-09','melissa.nash@fakedomain.com'), ('s47','Mike','White','CS',2018,'1986-01-17','mike.white@fakedomain.com'), ('s48','Bob','Horn','CS',2018,'1986-02-10','bob.horn@fakedomain.com'), ('s49','Elena','Johnson','CS',2018,'1999-10-25','elena.johnson2@fakedomain.com'), ('s50','Rick','Mason','CS',2018,'1994-10-28','rick.mason@fakedomain.com'), ('s51','Fred','White','CS',2019,'1994-04-30','fred.white2@fakedomain.com'), ('s52','Elena','Johnson','CS',2019,'2003-12-31','elena.johnson3@fakedomain.com'), ('s53','Bob','Nash','CS',2019,'2003-06-25','bob.nash2@fakedomain.com'), ('s54','Jeremy','Roberts','CS',2019,'1991-05-05','jeremy.roberts@fakedomain.com'), ('s55','Anna','Stevens','CS',2019,'2000-09-09','anna.stevens@fakedomain.com'), ('s56','Rick','Mason','CS',2020,'2003-12-29','rick.mason2@fakedomain.com'), ('s57','Elena','Nash','CS',2020,'1991-12-25','elena.nash@fakedomain.com'), ('s58','Mike','Roberts','CS',2020,'1986-04-28','mike.roberts3@fakedomain.com'), ('s59','Arleen','Michaels','CS',2020,'1997-12-31','arleen.michaels@fakedomain.com'), ('s60','Fred','Horn','CS',2020,'2000-07-17','fred.horn3@fakedomain.com'), ('s61','Rick','Nash','MATH',2018,'1984-06-27','rick.nash2@fakedomain.com'), ('s62','Elena','Johnson','MATH',2018,'1996-05-02','elena.johnson4@fakedomain.com'), ('s63','Bob','Michaels','MATH',2018,'1980-09-07','bob.michaels2@fakedomain.com'), ('s64','Fred','Horn','MATH',2018,'1983-11-15','fred.horn2@fakedomain.com'), ('s65','Anna','White','MATH',2018,'1995-11-07','anna.white2@fakedomain.com'), ('s66','Melissa','Johnson','MATH',2019,'1980-09-12','melissa.johnson2@fakedomain.com'), ('s67','Elena','Michaels','MATH',2019,'1984-06-12','elena.michaels@fakedomain.com'), ('s68','Mike','Roberts','MATH',2019,'1981-09-30','mike.roberts2@fakedomain.com'), ('s69','Anna','Jackson','MATH',2019,'1980-10-13','anna.jackson@fakedomain.com'), ('s70','Jeremy','Horn','MATH',2019,'1997-05-12','jeremy.horn2@fakedomain.com'), ('s71','Fred','Nash','MATH',2020,'2002-01-18','fred.nash2@fakedomain.com'), ('s72','Elena','Jackson','MATH',2020,'2002-01-18','elena.jackson@fakedomain.com'), ('s73','Jeremy','Roberts','MATH',2020,'1995-04-02','jeremy.roberts2@fakedomain.com'), ('s74','Rick','Horn','MATH',2020,'1989-01-06','rick.horn2@fakedomain.com'), ('s75','Jeremy','Stevens','MATH',2020,'1993-06-16','jeremy.stevens@fakedomain.com'); CREATE TABLE student_course ( student_id VARCHAR(50) REFERENCES student (student_id), course_id VARCHAR(50) REFERENCES course (course_id), grade INTEGER, PRIMARY KEY (student_id, course_id) ); INSERT INTO student_course (student_id, course_id, grade) VALUES ('s1','DBMS',NULL), ('s1','ISD',70), ('s1','STAT',55), ('s1','ALG',NULL), ('s2','DBMS',85), ('s2','ISD',90), ('s2','STAT',80), ('s3','DBMS',NULL), ('s3','ISD',80), ('s3','STAT',45), ('s3','ALG',70), ('s4','DBMS',85), ('s4','ISD',90), ('s4','STAT',55), ('s4','ALG',NULL), ('s5','DBMS',NULL), ('s5','ISD',60), ('s5','STAT',65), ('s6','DBMS',50), ('s6','ISD',67), ('s6','STAT',72), ('s6','ALG',NULL), ('s7','DBMS',71), ('s7','ISD',78), ('s7','STAT',49), ('s7','ALG',NULL), ('s8','DBMS',NULL), ('s8','ISD',54), ('s8','STAT',55), ('s10','DBMS',93), ('s10','ISD',90), ('s10','STAT',85), ('s10','ALG',73), ('s11','DBMS',NULL), ('s11','ISD',64), ('s11','STAT',77), ('s11','ALG',82), ('s12','DBMS',46), ('s12','ISD',59), ('s12','STAT',60), ('s12','ALG',66), ('s13','DBMS',NULL), ('s13','ISD',71), ('s13','STAT',75), ('s14','DBMS',97), ('s14','ISD',50), ('s14','STAT',86), ('s14','ALG',53), ('s15','DBMS',70), ('s15','ISD',69), ('s15','STAT',42), ('s16','DBMS',NULL), ('s16','ISD',65), ('s16','IN',NULL), ('s16','LINALG',73), ('s16','CALC',88), ('s17','DBMS',64), ('s17','ISD',76), ('s17','LINALG',64), ('s17','CALC',58), ('s18','DBMS',NULL), ('s18','ISD',78), ('s18','IN',49), ('s18','LINALG',80), ('s18','CALC',74), ('s19','DBMS',47), ('s19','ISD',58), ('s19','IN',53), ('s19','LINALG',67), ('s19','CALC',52), ('s20','DBMS',NULL), ('s20','LINALG',75), ('s20','CALC',64), ('s21','DBMS',93), ('s21','ISD',77), ('s21','LINALG',58), ('s21','CALC',57), ('s22','DBMS',62), ('s23','ISD',65), ('s23','IN',NULL), ('s23','LINALG',81), ('s23','CALC',93), ('s25','DBMS',NULL), ('s25','ISD',76), ('s25','LINALG',90), ('s25','CALC',95), ('s26','DBMS',49), ('s26','ISD',43), ('s26','IN',NULL), ('s26','LINALG',52), ('s26','CALC',70), ('s27','DBMS',76), ('s27','ISD',71), ('s27','IN',59), ('s27','LINALG',97), ('s27','CALC',75), ('s28','DBMS',69), ('s28','ISD',53), ('s28','LINALG',78), ('s28','CALC',79), ('s29','DBMS',NULL), ('s29','ISD',69), ('s29','IN',NULL), ('s29','LINALG',93), ('s29','CALC',59), ('s30','DBMS',63), ('s30','ISD',69), ('s30','LINALG',79), ('s30','CALC',95), ('s31','DBMS',NULL), ('s31','ISD',76), ('s31','LINALG',90), ('s31','CALC',95), ('s33','DBMS',NULL), ('s33','ISD',92), ('s33','LINALG',73), ('s33','CALC',85), ('s34','DBMS',59), ('s34','ISD',69), ('s34','IN',71), ('s34','LINALG',72), ('s34','CALC',52), ('s35','DBMS',63), ('s35','ISD',68), ('s35','LINALG',80), ('s35','CALC',91), ('s36','DBMS',NULL), ('s36','ISD',70), ('s36','IN',NULL), ('s36','LINALG',70), ('s36','CALC',72), ('s37','DBMS',62), ('s37','ISD',76), ('s37','IN',84), ('s37','LINALG',88), ('s37','CALC',81), ('s38','DBMS',NULL), ('s38','ISD',91), ('s38','IN',98), ('s38','LINALG',90), ('s38','CALC',88), ('s39','DBMS',71), ('s39','ISD',59), ('s39','IN',64), ('s39','LINALG',69), ('s39','CALC',72), ('s40','DBMS',NULL), ('s40','ISD',70), ('s40','LINALG',73), ('s40','CALC',80), ('s41','DBMS',61), ('s41','ISD',68), ('s41','IN',73), ('s41','STAT',NULL), ('s41','ALG',90), ('s42','DBMS',69), ('s42','ISD',78), ('s42','IN',83), ('s42','STAT',91), ('s42','ALG',54), ('s43','DBMS',51), ('s43','ISD',58), ('s43','IN',54), ('s43','ALG',60), ('s44','DBMS',87), ('s44','ISD',74), ('s44','IN',63), ('s44','STAT',NULL), ('s44','ALG',60), ('s45','DBMS',91), ('s45','ISD',88), ('s45','IN',70), ('s45','STAT',66), ('s45','ALG',90), ('s46','DBMS',78), ('s46','ISD',75), ('s46','IN',64), ('s46','STAT',NULL), ('s46','ALG',87), ('s47','DBMS',60), ('s47','ISD',52), ('s47','IN',53), ('s47','STAT',80), ('s47','ALG',71), ('s48','DBMS',81), ('s48','ISD',83), ('s48','IN',90), ('s48','STAT',NULL), ('s48','ALG',59), ('s49','DBMS',91), ('s49','ISD',70), ('s49','IN',56), ('s49','ALG',70), ('s50','DBMS',60), ('s50','ISD',71), ('s50','IN',79), ('s50','STAT',49), ('s50','ALG',62), ('s51','DBMS',60), ('s51','ISD',62), ('s51','IN',90), ('s51','STAT',NULL), ('s51','ALG',73), ('s52','DBMS',57), ('s52','ISD',52), ('s52','IN',50), ('s52','STAT',NULL), ('s52','ALG',70), ('s53','DBMS',74), ('s53','ISD',73), ('s53','IN',69), ('s53','STAT',81), ('s53','ALG',77), ('s54','DBMS',62), ('s54','ISD',71), ('s54','IN',66), ('s54','ALG',70), ('s55','DBMS',62), ('s55','ISD',78), ('s55','IN',83), ('s55','STAT',80), ('s55','ALG',95), ('s56','DBMS',60), ('s56','ISD',54), ('s56','IN',47), ('s56','STAT',33), ('s56','ALG',76), ('s57','DBMS',81), ('s57','ISD',80), ('s57','IN',63), ('s57','STAT',NULL), ('s57','ALG',76), ('s58','DBMS',73), ('s58','ISD',78), ('s58','IN',89), ('s58','ALG',59), ('s59','DBMS',64), ('s59','ISD',78), ('s59','IN',77), ('s59','STAT',NULL), ('s59','ALG',90), ('s60','DBMS',61), ('s60','ISD',68), ('s60','IN',73), ('s60','STAT',NULL), ('s60','ALG',90), ('s62','LINALG',71), ('s62','STAT',77), ('s62','CALC',65), ('s63','LINALG',NULL), ('s63','STAT',54), ('s63','CALC',71), ('s64','LINALG',88), ('s64','STAT',90), ('s64','CALC',72), ('s65','LINALG',57), ('s65','STAT',62), ('s65','CALC',78), ('s66','STAT',66), ('s66','CALC',64), ('s67','LINALG',NULL), ('s67','STAT',78), ('s67','CALC',95), ('s68','LINALG',52), ('s68','STAT',60), ('s68','CALC',70), ('s69','STAT',76), ('s69','CALC',83), ('s70','LINALG',61), ('s70','STAT',68), ('s70','CALC',91), ('s71','LINALG',NULL), ('s71','STAT',63), ('s71','CALC',80), ('s72','LINALG',65), ('s72','STAT',57), ('s72','CALC',90), ('s73','LINALG',54), ('s73','STAT',61), ('s73','CALC',59), ('s74','STAT',91), ('s74','CALC',86), ('s75','LINALG',NULL), ('s75','STAT',68), ('s75','CALC',83); SELECT student_id ,student_name as "first name" ,student_lastname as "last name" ,student_udegree_id as "deg_id" ,student_birthdate as "dob" ,student_email ,student_start_year FROM student WHERE CONCAT(student_name,' ',student_lastname,' ',student_birthdate) IN (SELECT CONCAT(student_name,' ',student_lastname,' ',student_birthdate) FROM student s GROUP BY student_name ,student_lastname ,student_udegree_id ,student_birthdate HAVING count(*)>1) AND UPPER(concat(student_name,'.',student_lastname)) = UPPER(SUBSTRING(student_email,1,len(student_email)-15))
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear