Test and development surface economic database code problem

Posted by Lenbot on Mon, 20 Dec 2021 15:38:59 +0100

Accept the fact that you don't want to work, and then do it to the best of your ability.

Test and development surface (IV) database

1, SQL code question

1. Create database

create database edu;

2. Create a schema in the edu database. The name of the schema is the Chinese Pinyin of your own name.

A database can have multiple schemas. A schema usually includes multiple tables, views and indexes. The pattern is actually a name space, sunnygao Student and sunnyxin Students are different objects.

use edu; ## Change the current database
create schema sunnygao authorization gao;

3. In the created schema, create the following table:

student

DROP TABLE IF EXISTS `student`;
create table student(
sno char (8) comment'Student number',/*Student number*/
sname char (8) not null comment'Student name',/*Student name*/
sex char (2) comment'Student gender',
native char (20) comment 'Native place',
brithday datetime comment 'Student's date of birth',
pno char (4) comment'Specialty number',
dno char (6) comment '6',
classno char(4) comment 'Class number',
entime datetime comment 'Admission time of students',
home varchar (40) comment'Student home address',
tel varchar (40) comment'Student contact number',
primary key (sno)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

course

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cno` char(10) NOT NULL COMMENT 'Course number',
  `cname` char(20) NOT NULL DEFAULT '' COMMENT 'Course name',
  `cpno` char(10) DEFAULT '' COMMENT 'Prerequisite course',
  `experiment` tinyint DEFAULT NULL COMMENT 'During the experiment',
  `lecture` tinyint DEFAULT NULL COMMENT 'Teaching hours',
  `semester` tinyint DEFAULT NULL COMMENT 'Opening semester',
  `credit` tinyint DEFAULT NULL COMMENT 'course credit',
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

student_course

create table student_course(
sno char(8) not null,
cno char (10) not null,
score tinyint ,
primary key (sno,cno)
);

department (department information table)

create table department(
	dno char(6) not null,
	dname char(8) not null,
	dhome varchar(40),
	dzipcode char(6),
	dtel varchar(40),
	primary key(dno)
);

teacher

create table teacher(
	tno char(8) not null,
	tname char(8) not null,
	sex char(2),
	birthday datetime,
	dno char(6),
	pno varchar(20),
	home varchar(40),
	zipcode char(6),
	tel varchar(40),
	email varchar(40),
	primary key(tno)
);

teacher_course table

create table teacher_course(
	tcid smallint not null,
	tno char(8),
	classno char(4),
	cno char(10),
	semester char(6),
	schoolyear char(10),
	classtime varchar(40),
	classroom varchar(40),
	weektime tinyint,
	primary key(tcid),
	foreign key(tno) references teacher(tno),
	foreign key(cno) references course(cno)
);

4. Create and delete indexes

(1) Create a unique descending index in the sname column of the student table;

create unique index name_desc on student(sname desc);

(2) Establish an ascending index on the credit column of the course table;

create index credit_asc on course(credit asc);

(3) Build a clustered index on the sno column of the student_course table.

create cluster index sno_cluster on student_course(sno);
### An error will be reported. Generally, the default primary key is cluster index
create index sno_cluster on student_course(sno);

(4) On the student_course table, the student numbers are in ascending order, and the same student numbers are indexed in descending order according to the course numbers;

create index asc_cno on student_course(sno asc,cno desc);

(5) Build a clustered ascending index on the tno column of the teacher table.

create index tno_asc on teacher(tno asc);

(6) Delete the clustered index on the sno column of the student_course table.

drop index asc_cno on student_course;

5. Insert data

insert into student
values('20101001','Zhang San','male','Cangzhou, Hebei','1989-03-15','2','1','2','2010-9-1','Cangzhou City, Hebei Province',null),

('20101002','Zhang Qiang','male','Xingtai, Hebei','1990-05-01','2','1','2','2010-9-1','Xingtai City, Hebei Province','13145322345'),

('20101003','Zhang Xiaobing','male','Jinan, Shandong','1989-03-15','1','2','4','2010-9-1','Cangzhou City, Hebei Province',null),

('20101004','Li Yan','male','Jinan, Shandong','1989-03-15','1','1','2','2010-9-1','Jinan, Shandong','15194598762'),

('20101005','Li Xiao','male','Dezhou, Shandong','1989-03-15','2','2','2','2010-9-1','Dezhou, Shandong',null),

('20101006','Shangguanqing','female','Xi'an, Shaanxi','1989-03-15','3','4','2','2010-9-1','Xi'an, Shaanxi','15192598762'),

('20101007','Li Xiaofang','female','Xi'an, Shaanxi','1989-03-15','3','3','3','2010-9-1','Xi'an, Shaanxi',null),

('20101008','Shangguan Wenhong','female','Beijing','1989-03-15','2','2','2','2010-9-1','Beijing','15194598232'),

('20101009','Shangguan Wenhong','female','Baoding, Hebei','1989-03-15','1','1','2','2010-9-1','Baoding, Hebei',null),

('20101010','Zhang Qian','female','Baoding, Hebei','1989-03-15','2','3','2','2010-9-1','Baoding, Hebei','15123298762'),

('20101011','Liu Yingwei','male','Baoding, Hebei','1989-03-15','3','2','2','2010-9-1','Baoding, Hebei',null),

('20101012','Liu Wei','male','Baoding, Hebei	','1989-03-15','2','4','2','2010-9-1','Baoding, Hebei','15232598762'),

('20101013','Zhai Nan','male','Shanghai','1989-03-15','2','1','3','2010-9-1','Shanghai','15194598772'),

('20101014','Dou shicuo','male','Kunming, Yunnan','1989-03-15','3','4','1','2010-9-1','Kunming, Yunnan','15193248762');				 			 		



insert into course
values('1','Introduction to Database System ','3','20','72','1','3'),
('2','100%Master financial statements','3','30','54','1','2'),
('3','data structure','5','30','72','1','4'),
('4','c++Programming','5','15','72','1','4'),
('5','c Language programming',null,'18','45','1','3'),
('6','WEB development',null,'28','72','1','2'),
('7','Introduction to computer',null,'10','32','1','1');

insert into student_course
values('20101001','1',70),
('20101001','2',95),
('20101001','3',90),
('20101001','4',88),
('20101001','5',null),
('20101001','6',null),
('20101002','1',90),
('20101002','2',40),
('20101002','3',90),
('20101003','1',90),
('20101004','2',50),
('20101004','3',90),
('20101005','1',20),
('20101005','2',80),
('20101006','3',90),
('20101007','3',60),
('20101008','3',null),
('20101009','3',11),
('20101010','3',58),
('20101011','3',45);

insert into teacher
values('836001','Dong Qing','male','1975-04-26','1','lecturer','Shijiazhuang City, Hebei Province','50000','13798789765','dong@gmail.com'),
('836002','Li Liang','male','1975-04-26','1','associate professor','Shijiazhuang City, Hebei Province','834000','13728789765','liliang@gmail.com'),
('836003','Li Xiaoliang','male','1975-04-26','1','lecturer','Shijiazhuang City, Hebei Province','50300','12228789765','dong@gmail.com'),
('836004','Xiang Tian','male','1975-04-26','2','professor','Shijiazhuang City, Hebei Province','832000','13798389765','dong@gmail.com'),
('836005','Yu Xiaotian','male','1975-04-26','2','lecturer','Shijiazhuang City, Hebei Province','50300','13798789765','dong@gmail.com'),
('836006','Lu Chanjuan','female','1975-04-26','3','associate professor','Shijiazhuang City, Hebei Province','830200','13733789765','dong@gmail.com'),
('836007','Li Ranran','female','1975-04-26','3','lecturer','Shijiazhuang City, Hebei Province','833300','13727822765','dong@gmail.com'),
('836008','Zhuang Wen','male','1975-04-26','2','professor','Urumqi City, Xinjiang Autonomous Region','8200','13333789765','dong@gmail.com');

insert into teacher_course
values('1','836001','1','1','1','2010','8:00-11:00','Public education building 305','3'),
('2','836002','1','2','1','2010','8:00-11:00','Public education building 305','3'),
('3','836002','2','2','1','2010','8:00-11:00','c305','3'),
('4','836003','2','3','1','2010','8:00-11:00','c305','3'),
('5','836003','2','1','1','2010','8:00-11:00','v305','3'),
('6','836003','1','3','1','2010','8:00-11:00','v305','3'),
('7','836004','3','4','1','2010','8:00-11:00','b305','3');

insert into department
values('1','School of law and Politics','No. 20, south 2nd Ring East Road, Yuhua District, Shijiazhuang City, Hebei Province','50024','80788100'),
('2','College of Education','No. 20, south 2nd Ring East Road, Yuhua District, Shijiazhuang City, Hebei Province','50024','80788101'),
('3','Software College','No. 20, south 2nd Ring East Road, Yuhua District, Shijiazhuang City, Hebei Province','50024','80788102'),
('4','Digital Information College','No. 20, south 2nd Ring East Road, Yuhua District, Shijiazhuang City, Hebei Province','50024','80788102');

6. SQL query

1) Query the student number, name and native place of all students

select sno,sname,native from student;

2) Query the names and credits of all courses

select cname,credit from course;

3) Query the teacher's name, gender and contact number

select tname,sex,tel from teacher;

4) Query the course number, course name and theoretical class hours of each course

select cno,cname,lecture from course;

5) Query the names and contact numbers of all students, and add the string 'contact information' in front of them

select sname,'contact information'+tel from student;

6) Query the number of Teachers

select count(distinct tno) from teacher;

7) Query the name, home address and postal code of all teachers (use column alias)

select tname,home,zipcode 'Postal Code' from teacher;

8) Check the names of all students

select sname from student;

9) Query the teacher number of classes in this semester

select DISTINCT tno from teacher_course where cno in (
	select cno from course where semester = 1 
);

SELECT tno from teacher where tno in (
    select tno from teacher_course where semester = 1
);

10) Query student numbers of all selected courses

select distinct sno from student_course;

Query the student numbers of all students without elective courses (left outer connection)

The left outer connection lists all tuples in the left relationship, and the right outer connection lists all tuples in the right relationship.

select student.sno from student  ##  student. SnO SnO should be prefixed!
LEFT JOIN student_course ON student.sno= student_course.sno
WHERE student_course.cno is NULL;

11) Query the student number and name of students whose native place is Baoding, Hebei

select sno,sname from student where native='Baoding, Hebei';

12) Query the course name with course credits less than 3 points

select cname from course where credit<3;

13) Query the number and name of teachers in classroom c201 (left outer connection)

select teacher.tno,teacher.tname,classroom from teacher 
left join teacher_course on teacher.tno = teacher_course.tno 
where teacher_course.classroom='c305';
  1. Query the office phone of the software college
select dtel from department where dname='Software College';

15) Query the student number and course number of students with scores of 60-90 (multi table connection)

select sname,cname,score from student,student_course,course
where student.sno = student_course.sno 
and student_course.cno=course.cno 
and score>=60 and score<90;
  1. Query course information with 3 credits
select * from course where credit = 3;

17) Query the number of teachers in Block C of the teaching building (fuzzy query)

select tname,teacher.tno,classroom
from teacher,teacher_course 
where teacher.tno=teacher_course.tno
and teacher_course.classroom like '%c%';

18) Query the course number and course name of the advance course

select fc.cname,fc.cno,sc.cname 'Prerequisite course'
from course fc,course sc
where fc.cpno = sc.cno;

19) Query the basic information of students whose native place is Shandong Province

select * from student where native like "Shandong%";

20) The query name is 3 words, and the first two words are the student number and name of "Shangguan" (fuzzy query)

select sno,sname from student where sname like 'Shangguan_';

21) inquire the name, birthday and home address of the female student with major No. 3

select sname,birthday,native from student where dno='3' and sex = 'female';

22) query the course number and grades of students with student number '20101001', and the results are sorted in ascending order (ascending order)

select sno,cno,score from student_course where sno='20101001' 
ORDER BY sno ASC;

23) query the basic information of all courses, and the results are arranged in descending order according to the teaching hours. The same class hours are arranged in ascending order according to the credits.

select * from course order by lecture desc,credit asc;

24) query the names and birth dates of all male teachers, and the results are sorted from small to large

select tname,birthday from teacher order by birthday desc;

25) query the maximum and minimum scores of students taking elective course No. 2

select max(score),min(score) from student_course where cno='2';

26) query the number of all teachers

select count(tno) from teacher;

27) query the number of students with major No. 1

select count(distinct sno) from student where pno='1';

28) query the course number of more than 5 students (having)

Knowledge point: the where clause cannot use an aggregate function as a conditional expression.

The HAVING clause allows us to filter the grouped groups of data.

select cno from student_course group by cno HAVING COUNT(*)>5;

29) check the basic information of all students, the name of the college and the contact number of the college

select student.*,dname,dtel 
from student,department 
where student.dno = department.dno;

30) query the class time, place and weekly class hours of Teacher Li Liang

select classtime,classroom,weektime 
from teacher_course 
where tno in (
	select tno 
    from teacher
    where tname='Li Liang'
);

## Add a course name
select cname,classtime,classroom,weektime 
from teacher_course,teacher,course 
where teacher.tno=teacher_course.tno 
and teacher_course.cno = course.cno
and tname='Li Liang';

31) query the student number and name of students who have taken introduction to database system or data structure

select student.sno,student.sname 
from student
where sno in(
    select sno 
    from student_course
    where cno IN(
			select cno
			from course
			where cname='Introduction to Database System ' 
        	 or cname='data structure'
	)
);


select student.sno,student.sname 
from student
where sno in(
    select sno 
    from student_course,course
    where student_course.cno=course.cno
    and cname='Introduction to Database System '
    or cname='data structure'
);

32-67 it is required to query in at least two ways (for complex queries, views can be established)

32) inquire about classes with up to two boys

select classno from student
where sex ='male'
group by classno
having COUNT(classno)<=2
UNION ## Considering the absence of boys
SELECT classno
FROM student
WHERE classno not in (
	SELECT DISTINCT classno 
	FROM student 
	WHERE sex='male'
);

33) query the information of the college with at least one professor

select * from department
where dno in(
	select dno from teacher
    where pno='professor'
);

34). Find out each teacher and his teaching situation

select teacher.tname,course.*
from teacher,teacher_course,course
where teacher.tno=teacher_course.tno 
and teacher_course.cno = course.cno;

35). Query each course and the number of people selected

Left outer connection: lists all tuples of the left relationship

Right outer connection: lists all tuples of the right relationship

Because the number of people can be distinguished in both table tuples, there is no difference between the left outer connection and the right outer connection.

## External connection
select course.cname,count(*) 'Number of people'
from course LEFT JOIN student_course
on course.cno = student_course.cno
GROUP BY course.cno

## Inner connection
select course.cname,count(*) 'Number of people'
from course,student_course
WHERE course.cno = student_course.cno
GROUP BY course.cno;

Space is limited. Other topics are in the next article~~~

Topics: Database

Hot Topics

©2024 Programmer Think