Create tables for school MySQL database
CREATE TABLE teachers (
teacherid int(11) NOT NULL auto_increment,
lastname varchar(15) NOT NULL default '',
firstname varchar(15) NOT NULL default '',
email varchar(30) NOT NULL default '',
phone varchar(20) NOT NULL default '',
hiredate date default NULL,
rate int(11) NOT NULL default '30',
PRIMARY KEY (teacherid)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table teachers
--
INSERT INTO teachers VALUES (1, 'Smith', 'John', 'johns@amail.com', '727-123-1234', '2000-07-01', 62); INSERT INTO teachers VALUES (2, 'Jefferson', 'John', 'johnj@amail.com', '727-987-1234', '2005-07-01', 51); INSERT INTO teachers VALUES (3, 'Barry', 'James', 'jamesb@amail.com', '727-987-8888', '2003-09-01', 51); INSERT INTO teachers VALUES (4, 'Murphy', 'Michael', 'michaelm@amail.com', '910-987-1234', '2000-07-01', 61); INSERT INTO teachers VALUES (5, 'Williams', 'Julia', 'Juliaw@amail.com', '727-111-8888', '2007-09-01', 51); INSERT INTO teachers VALUES (6, 'Niven', 'John', 'boring@yahoo.com', '727-098-4567', '2011-09-01', 20); INSERT INTO teachers VALUES (7, 'Merry', 'Christine', 'merryc@gmail.com', '212-123-0987', '2011-09-06', 30); INSERT INTO teachers VALUES (8, 'Gerald', 'Greg', 'greg@hotmail.com', '212-123-0923', '2009-09-01', 30); INSERT INTO teachers VALUES (9, 'Ross', 'Michael', 'michaelr@gmail.com', '910-092-3746', '2010-08-09', 20); INSERT INTO teachers VALUES (10, 'Kelly', 'Michael', 'michaelk@yahoo.com', '727-098-1237', '2008-07-03', 25); INSERT INTO teachers VALUES (11, 'Karon', 'Norm', 'normk@gmail.com', '727-123-288', '2010-09-02', 30);
CREATE TABLE students (
studentid int(11) NOT NULL auto_increment,
lastname varchar(15) NOT NULL default '',
firstname varchar(15) NOT NULL default '',
email varchar(30) NOT NULL default '',
phone varchar(30) NOT NULL default '',
age int(11) NOT NULL,
gender varchar(6) NOT NULL default '',
startdate date default NULL,
PRIMARY KEY (studentid)
) ENGINE=MyISAM AUTO_INCREMENT=10038221 DEFAULT CHARSET=latin1 AUTO_INCREMENT=10038221 ;
INSERT INTO students VALUES (11, 'Petrov', 'Michael', 'michael@yahoo.com', '272-123-0987', 21, 'male', '2010-12-03'); INSERT INTO students VALUES (12, 'Johnson', 'Michael', 'jmichael@yahoo.com', '272-123-0111', 25, 'male', '2010-12-01'); INSERT INTO students VALUES (13, 'Williams', 'John', 'jwilliams@yahoo.com', '272-321-0111', 25, 'male', '2010-12-02'); INSERT INTO students VALUES (14, 'George', 'Lee', 'brat@yahoo.com', '272-321-2222', 30, 'male', '2010-12-01'); INSERT INTO students VALUES (15, 'James', 'Molly', 'molly@yahoo.com', '272-111-2244', 30, 'female', '2010-12-03'); INSERT INTO students VALUES (16, 'Michaels', 'Holly', 'holly@yahoo.com', '272-111-2222', 20, 'female', '2010-12-02'); INSERT INTO students VALUES (17, 'Brown', 'Cindy', 'cindy@yahoo.com', '272-111-2266', 23, 'female', '2010-12-06'); INSERT INTO students VALUES (18, 'Barklay', 'Julia', 'julia@yahoo.com', '272-111-2299', 23, 'female', '2010-12-06'); INSERT INTO students VALUES (19, 'Cremette', 'Alison', 'alisonc@yahoo.com', '272-111-2218', 25, 'female', '2010-12-03'); INSERT INTO students VALUES (20, 'Folkner', 'James', 'james@yahoo.com', '272-111-2249', 25, 'male', '2010-12-03'); INSERT INTO students VALUES (1, 'Holden', 'Michael', 'holdent@yahoo.com', '272-321-2222', 30, 'male', '2010-12-01'); INSERT INTO students VALUES (0, 'Woods','Cindy', 'cindyw@gmail.com', '555-555-5555',27, 'male', '2010-11-14');
CREATE TABLE student_course (
studentcourseid int(11) NOT NULL auto_increment,
studentid int(11) NOT NULL,
courseid int(11) NOT NULL,
paid tinyint(1) NOT NULL default '0',
PRIMARY KEY (studentcourseid)
) ENGINE=MyISAM AUTO_INCREMENT=1
DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO student_course VALUES (1, 21, 9, 1); INSERT INTO student_course VALUES (2, 11, 2, 1); INSERT INTO student_course VALUES (3, 13, 8, 1); INSERT INTO student_course VALUES (4, 18, 7, 0);
INSERT INTO student_course VALUES (5, 19, 6, 1); INSERT INTO student_course VALUES (6, 21, 5, 0); INSERT INTO student_course VALUES (7, 19, 4, 1); INSERT INTO student_course VALUES (8, 14, 3, 0); INSERT INTO student_course VALUES (9, 16, 2, 1); INSERT INTO student_course VALUES (10, 18, 1, 0); INSERT INTO student_course VALUES (11, 20, 10, 1); INSERT INTO student_course VALUES (12, 17, 1, 1); INSERT INTO student_course VALUES (13, 14, 1, 0); INSERT INTO student_course VALUES (14, 13, 1, 0); INSERT INTO student_course VALUES (15, 11, 1, 0); INSERT INTO student_course VALUES (16, 19, 1, 0); INSERT INTO student_course VALUES (17, 1, 1, 1); INSERT INTO student_course VALUES (18, 17, 11, 0); INSERT INTO student_course VALUES (19, 1, 2, 1); INSERT INTO student_course VALUES (20, 12, 2, 0); INSERT INTO student_course VALUES (21, 15, 2, 1); INSERT INTO student_course VALUES (22, 12, 3, 0); INSERT INTO student_course VALUES (23, 13, 3, 1); INSERT INTO student_course VALUES (24, 17, 5, 0); INSERT INTO student_course VALUES (25, 15, 5, 1); INSERT INTO student_course VALUES (26, 15, 6, 0); INSERT INTO student_course VALUES (27, 12, 6, 1); INSERT INTO student_course VALUES (28, 14, 6, 0); INSERT INTO student_course VALUES (29, 16, 6, 1); INSERT INTO student_course VALUES (30, 20, 6, 0); INSERT INTO student_course VALUES (31, 18, 6, 1); INSERT INTO student_course VALUES (32, 16, 9, 0); INSERT INTO student_course VALUES (33, 13, 9, 0);
CREATE TABLE course (
courseid int(11) NOT NULL auto_increment,
coursename varchar(50) NOT NULL default '',
hours int(11) NOT NULL default '100',
cost float NOT NULL default '1000',
PRIMARY KEY (courseid)
) ENGINE=MyISAM AUTO_INCREMENT=1
DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO course VALUES (1, 'Visual Basic', 360, 1999.95); INSERT INTO course VALUES (2, 'Java', 500, 2999.95); INSERT INTO course VALUES (3, 'C++', 550, 3999.95); INSERT INTO course VALUES (4, 'PHP', 300, 999.95); INSERT INTO course VALUES (5, 'HTML', 200, 699.95); INSERT INTO course VALUES (6, 'Pearl', 300, 1699.95); INSERT INTO course VALUES (7, 'CSS', 400, 899.95); INSERT INTO course VALUES (8, 'Assembly', 400, 1699.95); INSERT INTO course VALUES (9, 'JavaScript', 200, 999.95); INSERT INTO course VALUES (10, 'Python', 300, 999.95); INSERT INTO course VALUES (11, 'Unix', 700, 1000);
CREATE TABLE schedule (
scheduleid int(11) NOT NULL auto_increment,
courseid int(11) NOT NULL,
teacherid int(11) NOT NULL,
starttime time default NULL,
endtime time default NULL,
startdate date default NULL,
PRIMARY KEY (scheduleid)
) ENGINE=MyISAM AUTO_INCREMENT=1
DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO schedule VALUES (1, 2, 1, '09:00:00','15:00:00', '2010-01-11'); INSERT INTO schedule VALUES (2, 1, 9, '18:00:00','21:00:00', '2010-01-11'); INSERT INTO schedule VALUES (3, 4, 4, '08:00:00', '11:00:00', '2010-01-12'); INSERT INTO schedule VALUES (4, 6, 4, '12:00:00', '15:00:00', '2010-01-12'); INSERT INTO schedule VALUES (5, 10, 4, '16:00:00', '19:00:00', '2010-01-12'); INSERT INTO schedule VALUES (6, 5, 5, '08:00:00', '11:00:00', '2010-01-12'); INSERT INTO schedule VALUES (7, 7, 5, '12:00:00', '15:00:00', '2010-01-12'); INSERT INTO schedule VALUES (8, 3, 3, '09:00:00', '15:00:00', '2010-01-11'); INSERT INTO schedule VALUES (9, 9, 6, '16:00:00', '19:00:00', '2010-01-12'); INSERT INTO schedule VALUES (10, 8, 8, '16:00:00', '19:00:00', '2010-01-12'); INSERT INTO schedule VALUES (11, 1, 7, '09:00:00', '15:00:00', '2012-08-01'); INSERT INTO schedule VALUES (12, 2, 7, '18:00:00', '22:00:00', '2012-09-02'); INSERT INTO schedule VALUES (13, 1, 2, '09:00:00', '15:00:00', '2012-09-01'); INSERT INTO schedule VALUES (14, 2, 2, '18:00:00', '22:00:00', '2012-09-24'); INSERT INTO schedule VALUES (15, 9, 5, '09:00:00', '15:00:00', '2012-09-01'); INSERT INTO schedule VALUES (16, 10, 5, '18:00:00', '22:00:00', '2012-09-01'); INSERT INTO schedule VALUES (17, 3, 10, '09:00:00', '12:00:00', '2012-09-03'); INSERT INTO schedule VALUES (18, 3, 11, '13:00:00', '18:00:00', '2012-09-03');
Setting environment for using XAMPP for Windows. Sergey@SERGEYSK c:\xampp718 # mysql -u michael -pPassword7@ Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 10.1.26-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec)
MariaDB [(none)]> use test; Database changed MariaDB [test]> show tables; +-----------------+ | Tables_in_test | +-----------------+ | choi | | course | | questions | | schedule | | scores | | student_course | | students | | teacher_student | | teachers | | usernames | | userpictures | | visitors | +-----------------+ 12 rows in set (0.00 sec)
MariaDB [test]> show columns from students; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | studentid | int(11) | NO | PRI | NULL | auto_increment | | lastname | varchar(15) | NO | | | | | firstname | varchar(15) | NO | | | | | email | varchar(30) | NO | | | | | phone | varchar(30) | NO | | | | | age | int(11) | NO | | NULL | | | gender | varchar(6) | NO | | | | | startdate | date | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 8 rows in set (0.10 sec)
MariaDB [test]> show fields from teachers; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | teacherid | int(11) | NO | PRI | NULL | auto_increment | | lastname | varchar(15) | NO | | | | | firstname | varchar(15) | NO | | | | | email | varchar(30) | NO | | | | | phone | varchar(20) | NO | | | | | hiredate | date | YES | | NULL | | | rate | int(11) | NO | | 30 | | +-----------+-------------+------+-----+---------+----------------+ 7 rows in set (0.03 sec)
MariaDB [test]> show fields from teacher_student; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | tstid | int(11) | NO | PRI | NULL | auto_increment | | teacherid | int(11) | NO | | NULL | | | studentid | int(11) | NO | | NULL | | +-----------+---------+------+-----+---------+----------------+ 3 rows in set (0.07 sec)
MariaDB [test]> show fields from course; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | courseid | int(11) | NO | PRI | NULL | auto_increment | | coursename | varchar(50) | NO | | | | | hours | int(11) | NO | | 100 | | | cost | float | NO | | 1000 | | +------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.07 sec)
MariaDB [test]> show fields from schedule; +------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+----------------+ | scheduleid | int(11) | NO | PRI | NULL | auto_increment | | courseid | int(11) | NO | | NULL | | | teacherid | int(11) | NO | | NULL | | | starttime | time | YES | | NULL | | | endtime | time | YES | | NULL | | | startdate | date | YES | | NULL | | +------------+---------+------+-----+---------+----------------+ 6 rows in set (0.26 sec)
MariaDB [test]> show fields from student_course; +-----------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------+------+-----+---------+----------------+ | studentcourseid | int(11) | NO | PRI | NULL | auto_increment | | studentid | int(11) | NO | | NULL | | | courseid | int(11) | NO | | NULL | | | paid | float | YES | | NULL | | +-----------------+---------+------+-----+---------+----------------+ 4 rows in set (0.06 sec) MariaDB [test]>
SELECT s.firstname, s.lastname, t.firstname, t.lastname FROM students s INNER JOIN teacher_student ts ON s.studentid=ts.studentid INNER JOIN teachers t ON ts.teacherid=t.teacherid and t.teacherid=9 order by s.lastname;
SELECT CONCAT_WS(" ", `firstname`, `lastname`) AS `whole_name` FROM teachers SELECT CONCAT_WS(" ", firstname, lastname) AS whole_name FROM students SELECT CONCAT(Ifnull(FirstName,' '),' ', Ifnull(Lastname,' ')) FROM students SELECT CONCAT(Ifnull(Lastname,' '),', ', Ifnull(FirstName,' ')) FROM students
SELECT CONCAT_WS(" ", t.firstname, t.lastname) AS "Teacher name", CONCAT_WS(" ", s.firstname, s.lastname) AS "Student name", sc.starttime, c.coursename, sc.endtime FROM teachers t INNER JOIN schedule sc ON t.teacherid=sc.teacherid INNER JOIN course c ON sc.courseid=c.courseid INNER JOIN teacher_student ts ON sc.teacherid=ts.teacherid INNER JOIN students s ON ts.studentid=s.studentid and c.coursename='PHP' or c.coursename='JavaScript' order by t.lastname, s.lastname ;
insert into schedule(scheduleid, courseid, teacherid, starttime, endtime, startdate) values (0, 2, 3, '08:00:00', '12:00:00', '2018-05-01');
SELECT CONCAT_WS(" ", t.firstname, t.lastname) AS "Teacher name", CONCAT_WS(" ", s.firstname, s.lastname) AS "Student name", sc.starttime, c.coursename, sc.endtime FROM teachers t INNER JOIN schedule sc ON t.teacherid=sc.teacherid INNER JOIN course c ON sc.courseid=c.courseid INNER JOIN teacher_student ts ON sc.teacherid=ts.teacherid INNER JOIN students s ON ts.studentid=s.studentid and t.lastname='Ross' order by s.lastname, c.coursename;
INSERT into teacher_student(tstid, teacherid, studentid)
values (0, 3, 2);
SELECT CONCAT_WS(" ", t.firstname, t.lastname) AS "Teacher name", CONCAT_WS(" ", s.firstname, s.lastname) AS "Student name", sc.starttime, c.coursename, sc.endtime FROM teachers t INNER JOIN schedule sc ON t.teacherid=sc.teacherid INNER JOIN course c ON sc.courseid=c.courseid INNER JOIN teacher_student ts ON sc.teacherid=ts.teacherid INNER JOIN students s ON ts.studentid=s.studentid and s.lastname='James' order by t.lastname, c.coursename;
SELECT CONCAT_WS(" ", t.firstname, t.lastname) AS "Teacher name", CONCAT_WS(" ", s.firstname, s.lastname) AS "Student name", sc.starttime, c.coursename, sc.endtime FROM teachers t INNER JOIN schedule sc ON t.teacherid=sc.teacherid INNER JOIN course c ON sc.courseid=c.courseid INNER JOIN teacher_student ts ON sc.teacherid=ts.teacherid INNER JOIN students s ON ts.studentid=s.studentid and studentid in (SELECT studentid from teacher_student where
Which teacher has most students:
SELECT count(studentid), teacherid from teacher_student GROUP BY teacherid;
SELECT CONCAT_WS(" ", t.firstname, t.lastname) AS "Teacher name", count(ts.studentid) FROM teachers t INNER JOIN teacher_student ts ON t.teacherid=ts.teacherid GROUP BY t.teacherid ORDER BY t.lastname, t.firstname;
Which student has most teachers
SELECT count(teacherid), studentid from teacher_student GROUP BY studentid
SELECT CONCAT_WS(" ", s.firstname, s.lastname) AS "Student name", count(ts.teacherid) FROM students s INNER JOIN teacher_student ts ON s.studentid=ts.studentid GROUP BY s.studentid order by s.lastname, s.firstname;
How many courses each each teacher teaches?
SELECT t.teacherid, t.firstname, t.lastname, count(s.courseid) FROM teachers t INNER JOIN schedule s ON t.teacherid=s.teacherid GROUP BY t.lastname order by t.lastname;
How many courses each student attands?
SELECT s.firstname, s.lastname, count(sc.courseid) FROM students s INNER JOIN teacher_student ts ON s.studentid=ts.studentid INNER JOIN schedule sc ON ts.teacherid=sc.teacherid GROUP BY s.lastname
If you want the full tutorial, buy my book on amazon.com:
Learn SQL By Examples: Examples of SQL Queries and Stored Procedures for MySQL and Oracle Databases (paperback)
https://www.amazon.com/dp/1546996346
http://www.amazon.com/dp/B009PD6A2U
CREATE DATABASE STUDENTSDB
GRANT ALL PRIVILEGES ON STUDENTSDB.* TO 'user777'@'localhost' IDENTIFIED BY 'Elepant$47'
INSERT into teachers values
(0, 'Wilson','Jesika','wilsonj@gmail.com','727-123-7777','2000-07-01',90);