Shulou(Shulou.com)06/02 Report--

I.

1. Create a new table test

Id varchar2 (20)

Name varchar2 (20)

Addr varchar2 (50)

Score number

Create table test

(id varchar2 (20)

Name varchar2 (20)

Addr varchar2 (50)

Score number

)

two。 Add primary key constraints to id

Alter table test add constraint pk_id primary key (id)

3. Find out the people whose scores are greater than 60 and arrange them in descending order.

Select * from test where score > 60 order by score desc

4. New data 001 zhangsan shenzhen 80

Insert into test (id,name,addr,score) values ('001ZHANGANJIANJIANZHANJIANG')

Change the score of zhangsan to 60

Update test set score=60 where name='zhangsan'

7. Delete zhangsan information

Delete from test where name='zhangsan'

II.

Student student table (Scandinavian SageSsex)

The course schedule of Course (CnameMagol T#)

SC (score score)

Teacher (teacher list)

Question:

1. Inquire about the student number and average score of the students whose average grade point is greater than 60; avg

Select group by Magnum avg (score) from sc where (select avg (score) from sc) > 60 group by

Select scurvy avg (score) from sc where group by s# having avg (score) > 60

2. Check the student number, name, number of courses and total score of all the students.

Select Student.s#,sname,count (C #), sum (score) from Student inner join sc on Student.s#=sc.s# group by Student.s#,sname

3. Inquire the number of teachers surnamed "Li"

Select count (*) from teacher where tname like' Lee%'

4. Inquire the student numbers and names of the students who have not studied the teacher Ye Ping.

Select Shop Magazine Sname from Student where s# in (select S# from sc where c # not in (select c # from Course where title = (select T # from teacher where Tname=' Ye Ping'))

5. Check the student numbers and names of all students whose course scores are less than 60 points.

Select Shop Magazine Sname from Student where s# not in (select S# from sc where score > 60)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.