MySQL SQL SELECT * FROM
t_student: student table
t_teacher: teacher table score: grade table course: each table in the curriculum table is associated with the curriculum table associated teacher table: c_id: course number t_id: teacher number c_name: course name grade table associated student table and curriculum table: s_id: student id , c_id: course id score: course grade
/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.24 : Database - demo
**************************************************** *****************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`demo` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `demo`;
/*Table structure for table `course` */
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c_name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`t_id` int(11) DEFAULT NULL,
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*Data for the table `course` */
insert into `course`(`c_id`,`c_name`,`t_id`) values (1,'Chinese',1),(2,'Math',3),(3,'English',2);
/*Table structure for table `score` */
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`s_id` int(10) NOT NULL,
`c_id` int(11) DEFAULT NULL,
`score` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*Data for the table `score` */
insert into `score`(`s_id`,`c_id`,`score`) values (1,2,'90.00'),(1,1,'87.00'),(1,3,'68.50'),( 3,1,'79.00'),(3,2,'89.00'),(3,3,'99.00'),(2,3,'86.00'),(2,1,'79.00'),( 2,2,'87.00');
/*Table structure for table `t_student` */
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`s_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`s_name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`s_birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`s_sex` char(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*Data for the table `t_student` */
insert into `t_student`(`s_id`,`s_name`,`s_birthday`,`s_sex`) values (1,'Zhang Wang','2021-07-25 16:09:46','male'),( 2,'Li Si','2021-07-25 16:09:42','Female'),(3,'Zhang Wuji','2021-07-25 16:10:14','Male');
/*Table structure for table `t_teacher` */
DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher` (
`t_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`t_name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`c_id` int(11) DEFAULT NULL,
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*Data for the table `t_teacher` */
insert into `t_teacher`(`t_id`,`t_name`,`c_id`) values (1,'Zhang San',1),(2,'Li Si',3),(3,'Wang Wu',2 );Query related exercises
-- Query all student information
SELECT * FROM t_student;
-- Sub-query: Find the student ID, name, birthday, gender of the students who passed the grade of 01
SELECT * FROM t_student s JOIN score sc ON sc.`s_id` = s.s_id WHERE sc.`score` > 60 AND sc.`c_id` = (SELECT c_id FROM course WHERE c_name = 'language');
-- Subquery: View the job number and name of the teacher who teaches Chinese
SELECT * FROM t_teacher WHERE t_id = (SELECT t_id FROM course WHERE c_name = 'language');
-- Associated query: Find all boys' Chinese grades, student numbers, names, and 01 grades
SELECT * FROM t_student s JOIN score sc ON sc.`s_id` = s.`s_id` JOIN course c ON c.`c_id` = sc.`c_id` HAVING s.`s_sex` = 'male' AND c_name = 'language ';
-- Related query: Find the math scores of students born in 1990, student number, name, birthday, 02 score
SELECT * FROM t_student s JOIN score sc ON sc.`s_id` = s.`s_id` JOIN course c ON c.`c_id` = sc.`c_id` HAVING c.`c_name` = 'math' AND s.`s_birthday ` BETWEEN '1990-01-01' AND '1990-12-30';
-- Query the list of courses taught by each teacher, and display the list of teacher ID, teacher name, course ID, and course name. Make an alias for the data table, the teacher alias is t, and the course alias is c
SELECT t.`t_id`, t_name, c.`c_id`, c_name FROM t_teacher t JOIN course c ON c.`c_id` = t.`c_id`;
-- Query the courses taught by each teacher, display: teacher ID, teacher name, course ID, course name, and sort by teacher ID in reverse/descending order (desc).
SELECT t.`t_id`, t_name, c.`c_id`, c_name FROM t_teacher t JOIN course c ON c.`c_id` = t.`t_id` ORDER BY t.`t_id` DESC;
-- Query Professor Zhang San's courses, and display: teacher ID, name, course ID, and course name. This requires the use of a where condition
SELECT * FROM t_teacher t JOIN course c ON c.`c_id` = t.`c_id` WHERE t_name = 'Zhang San';
-- Query the grades of each student and each course, and display a list of student ID, student name, birthday, gender, course name, and course grades.
SELECT s.`s_id`, s_name, s_birthday, s_sex, c.`c_name`, score FROM t_student s JOIN score sc ON s.`s_id` = sc.`s_id` JOIN course c ON c.`c_id` = sc. `c_id`;
-- Query the grades of each student and each subject, and display a list of student ID, student name, birthday, gender, course name, and course grades. First, the course id is in positive order (asc), and then the course grades are in reverse order (desc).
SELECT s.`s_id`, s_name, s_birthday, s_sex, c_name, score FROM t_student s JOIN score sc ON sc.`s_id` = s.`s_id` JOIN course c ON c.`c_id` = sc.`c_id` ORDER BY c.`c_id`, score DESC;
-- Query all students with scores of 80-90, grades, and sort by student grades in reverse order, display list: student ID, student name, birthday, gender, course name, course grade
SELECT s.`s_id`, s_name, s_birthday, s_sex, c_name, score FROM t_student s JOIN score sc ON sc.`s_id` = s.`s_id` JOIN course c ON c.`c_id` = sc.`c_id` WHERE score BETWEEN 80 AND 90;
-- Query the student number, name, birthday, gender of students who have failed grades
SELECT * FROM t_student s JOIN score sc ON sc.`s_id` = s.`s_id` WHERE score < 60;
-- Query the student number, name, birthday, gender of students who have studied courses taught by Mr. Zhang San
SELECT * FROM t_student s JOIN course c ON c.`t_id` = (SELECT t_id FROM t_teacher WHERE t_name = 'Zhang San');
-- Query the math teacher's information, display the teacher's job number, teacher name, course number, course name,
SELECT t.`t_id`, t.`t_name`, c.`c_id`, c.`c_name` FROM t_teacher t JOIN course c ON c.`t_id` = t.`t_id` WHERE c.`c_name` = ' math';
English