Database Experiment 1. Database creation and basic query

(1) use update Statement modification student In the table 1 Bar record , Record the ： full name 、 Gender and college attributes are changed to the true information of the students doing the experiment , Other attribute values cannot be modified .

update student
set sname='Vijurria',ssex=' Woman ',sdept=' Business School '
where sno='201215121';

(2) Query age is greater than or equal to 19 Years old and less than or equal to 21 Information for all students aged .

select *
from student
where sage>=19 and sage<=21;

(3) Query that the course name contains “ data ” Two character course number and course name .

select cno,cname
from course
where cname LIKE'% data %';

(4) Check the penultimate of all student names 2 The word is “ writing ” Information about our students .

select*
from student
where sname LIKE'% writing _';

(5) Query the number of courses selected by each student , The query result includes the student number , Number of courses selected , And sort them in descending order according to the number of courses selected .

select  sno,count(cno)
from  sc
group by sno
order by count(cno) desc;

(6) Check the highest score of each course , The query result includes course number 、 The highest score in this course , And the result only shows that the highest score is greater than or equal to 95 Information about .

select  cno,max(grade)
from  sc
group by cno
having  max(grade)>=95;

(7) towards student Insert in table 1 Bar record , The recorded value is ：('201215999',' linghu chong ',' male ',21,' Performance Academy ')

insert 
into student(sno,sname,ssex,sage,sdept)
value ('201215999',' linghu chong ',' male ','21',' Performance Academy ');

(8) towards student Add... To the list favorite（ hobby ） Column , Its data type is varchar(200), That is, the maximum length is 200 Variable length data type .

alter table student add sfavorite varchar(200);

(9) to student Tabular sname Property adds a uniqueness （unique） Constraints .

alter table student add UNIQUE(sname);