Database System Experiment 4: SQL Language-SELECT Query Operation

Experimental environment

Windows 10 operating system, using MySQL 5.5 Command Line to complete the experiment

Experimental content

Experimentation Course Textbook (MySQL Database Technology and Experimental Guidance) Page 73, Question 1 "Experimental Contents and Requirements"
Based on jxgl database, use SQL statements to express the following queries

1. Retrieval of school numbers and names of male students over 23 years of age
2. Retrieving the names of female students taking at least one course
3. Search the course number of the course that Wang Lin did not study
4. Retrieval of student numbers for at least two elective courses
5. Retrieve the course number and course name of all the courses selected by the students
6. Retrieve the average scores of students taking all 3 credits per course

Steps and processes

First create the jxgl database for testing by following SQL statements

CREATE DATABASE jxgl;
use jxgl;

CREATE TABLE IF NOT EXISTS student(
sno CHAR(7) NOT NULL,
sname VARCHAR(16),
sage SMALLINT CHECK(sage>=16 AND sage<=45),
ssex CHAR(2) DEFAULT 'male' CHECK(ssex='male' OR ssex='female'),
sdept CHAR(2),
PRIMARY KEY(sno)
) ENGINE=InnoDB;

INSERT INTO student
VALUES
('2005001', 'Qian Heng', 18, 'male','Cs'),
('2005002', 'Wang Lin', 19, 'female','Cs'),
('2005003', 'Li Min', 23, 'male','Is'),
('2005004', 'Zhao Xin Ran', 16, 'female','Ma');

CREATE TABLE IF NOT EXISTS course(
cno CHAR(2) NOT NULL,
cname VARCHAR(20),
cpno CHAR(2),
ccredit SMALLINT,
PRIMARY KEY(cno)
) ENGINE=InnoDB;

INSERT INTO course
VALUES
('1', 'database system', '5', 4),
('2', 'mathematical analysis', '', 2),
('3', 'Introduction to Information Systems', '1', 3),
('4', 'Operating System Principle', '6', 3),
('5', 'data structure', '7', 4),
('6', 'Data Processing Basis', '', 4),
('7', 'C language', '6', 3);

CREATE TABLE IF NOT EXISTS sc(
sno CHAR(7) NOT NULL,
cno CHAR(2) NOT NULL,
grade SMALLINT,
PRIMARY KEY(sno, cno),
FOREIGN KEY(sno) REFERENCES student(sno),
FOREIGN KEY(cno) REFERENCES course(cno)
) ENGINE=InnoDB;

INSERT INTO sc
VALUES
('2005001', '1', 87),
('2005001', '2', 67),
('2005001', '3', 90),
('2005001', '4', 90),
('2005001', '5', 90),
('2005001', '6', 90),
('2005001', '7', 90),
('2005002', '2', 95),
('2005002', '4', 88),
('2005003', '3', 88),
('2005003', '4', 88),
('2005004', '4', 88);

Use SQL statements to express the following queries

• Retrieval of school numbers and names of male students over 23 years of age

SELECT sno, sname
FROM student
WHERE sage>23 AND ssex='male';

• Retrieving the names of female students taking at least one course

SELECT student.sname
FROM student, sc
WHERE student.ssex='female' AND student.sno=sc.sno
GROUP BY student.sno
HAVING count(*)>=1;

• Search the course number of the course that Wang Lin did not study

SELECT cno
FROM course
WHERE NOT EXISTS
(SELECT * FROM student, sc
WHERE sc.sno=student.sno
AND sc.cno=course.cno AND student.sname='Wang Lin');

• Retrieval of student numbers for at least two elective courses

SELECT student.sno
FROM student, sc
WHERE student.sno=sc.sno
GROUP BY student.sno
HAVING count(*)>=2;

• Retrieve the course number and course name of all the courses selected by the students

SELECT cno, cname
FROM course
WHERE NOT EXISTS
(SELECT * FROM student
WHERE NOT EXISTS
(SELECT * FROM sc
WHERE sno=student.sno AND cno=course.cno));

• Retrieve the average scores of students taking all 3 credits per course

SELECT AVG(grade)
FROM course, sc
WHERE course.cno=sc.cno AND course.ccredit=3;