首页 > 数据库 >数据库4

数据库4

时间:2024-06-14 17:47:50浏览次数:33  
标签:grade sno 数据库 sc cno where select

第四章

1.select * from sc

2.select sname, sage from student where sdept = '计算机系'

3.select sno, cno,grade from sc where grade between 70 and 80

4.select sname, sage from student

 

where sdept = '计算机系' and sage between 18 and 20

5.select max( grade) from sc where cno = 'c001'

6.select max(sage)最大年龄,min (sage)最小年龄from student

 

where sdept=’计算机系’

7.select sdept, count (*)人数from student

 

Group by sdept

8.select cno, count(*)选课人数,max (grade)最高分from sc

 

Group by cno

9.select sno, count (*)选课门数,sum (grade) from sc

 

Group by sno order by count(*) asc

10.select sno, sum (grade)总成绩from sc

Group by sno having sum (grade) >200

11.select sname, sdept from student s join sc on s.sno=sc.sno

 

where cno = 'c02'

12.select sname , cno ,grade

 

From student s join sc on s.sno = sc.sno

 

where grade > 80

 

order by grade desc

13.select c.cno, cname sdept from course c left join sc on c.cno = sc.cno

 

where sc.cno is null

14.select c1.cname,c1.s emester from course cl

 

join course c2 on c1.semester = c2.semester

 

where c2.cname = 'Java' and cl.cn ame != 'Java'

15.select sl.sname,s1.sdept,s1.sage from student s1

 

join student s2 on s1.sage = s2.sage

 

where s2.sname ='李勇’ and s1.sname !='李勇'

16.(1)select sname, sdept from student

 

Where sno in (select sno from sc where cno = 'co01′)

(2)Select sno, sname , cno, grade from student s

 

Join sc on s.sno = sc.sno

 

where sdept =数学系' and sno in (select sno from sc where grade > 80)

(3)select sname from student s join sc on s.sno = sc.snowhere sdept ='计算机系’ and grade = (

 

Select max (grade) from sc join student s on s.sno=sc.sno

 

where sdept ='计算机系')

(4)select sname , sdept ,ssex, grade from student s

 

Join sc on s.sno = sc.sno

 

Join course c on c.cno = sc.cnowhere cname ='数据结构'

 

And grade in (

 

Select max (grade) from sc join course c on c.cno=sc.cno

 

whe re cn ame =数据结构’)

17.select sname, sdept from student

 

where sno not in (

 

select sno from sc join course c on c.cno = sc.cno

 

whe reenameAJavaoOCIn.com

18.select sname, ssex from student

 

where sdept ='计算机系'

 

And sno not in (

 

select sno from sc )

19.Create table test_t (cOL1 int,

 

CoL2 char (10) not null,coL3 char (10) )

 

Insert into test_t (COL2) values (' B1')

 

Insert into test_t (COL1 ,COL2) values (1 ,'B2’ )

 

Insert into test_t values ( 2,'B3’,NULL)

20.Delete from sc where grade < 50

21.Delete from course where cno not in (select cno from course)

22.Delete from sc

From sc join student s on s.sno = sc.snoJoin course c on c.cno = sc.cno

where cname = 'Java' and grade < 60and sdept ='计算机系'

23.Update Course set credit = credit +2

 

where semester = 2

24.Update course set credit = 3where cname = 'Java'

25.Update student set sage = sage + 1where sdept ='计算机系'

26.Update sc set grade = grade + 5

 

From student s join sc on s.sno =sc.snoJoin course c on c.cno = sc.cno

 

where sdept ='信息系’ and cname ='计算机文化学’

27.select sdept, count (*)人数into Dept_Age from student

 

where sage >=20

 

Group by sdept

标签:grade,sno,数据库,sc,cno,where,select
From: https://www.cnblogs.com/yindantong/p/18248339

相关文章

  • 数据库6
    第六章:6.候选码:(学号,课程号)第一范式。因为有:学号→姓名,因此存在部分函数依赖(学号,课程号)p>姓名第三范式关系模式:学生(学号,姓名,所在系,性别)课程(课程号,课程名,学分)考试(学号,课程号,成绩),学号为引用学生的外码,课程号为引用课程的外码。7.候选码:学号属于第二范式。但有:......
  • 数据库5
    第五章:3.(1)Createviewt1(sno,sname,sdept,cno,cname,credit) As   Selects.sno,s.sname,s.dept,c.cno,c.cname,c.credit   Fromstudentsjoincoursecons.sno=c.sno(2)Createview t2(sno,sname,cname,grade)As   Selects.sno,s.sname,c.cname,c.grad......
  • 数据库8
    第8章:8.(1)图书(书号,书名,出版日期,作者名)读者(读者号,读者名,联系电话,所在单位)借阅(书号,读者号,借书日期,还书日期)(2)商店(商店编号,商店名,联系电话)商品(商品编号,商品名称,商品价格,商品分类)厂家(厂家编号,厂家地址,联系电话)订购(商店编号,商品编号,厂家编号,订购日期,订购数量)(3)学生......
  • 数据库7
    第7章:事务的概念及四个特征为原子性、一致性、隔离性和持久性。原子性指事务的原子性是指事务是数据库的逻辑工作单位,事务中的操作,要么都做,要么都不做。一致性指定事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。隔离性指数据库中一个事务的执行不能被其他......
  • 数据库1
    第一章:数据(Data):描述事务的符号记录称为数据。数据库(DataBase,简称DB):数据库是长期储存在计算机内的、有组织的、可共享的数据集合。数据库系统(DataBas,简称DBS):数据库系统是指在计算机系统中引用数据库后的系统构成,一般由数据库、数据库管理系统(及开发工具)、应用系统、数......
  • 数据库2
    第二章:2.实体之间的联系有一对一、一对多和多对多三种。例如:系和正系主任是一对一联系(假设一个系只有一个正系主任),系和教师是一对多联系(假设一个教师只在一个系工作),教师和课程是多对多联系(假设一个教师可以讲授多门课程,一门课程可由多个教师讲授)5.(1)一对多关系(2)多对多关系(3)多对......
  • 数据库3
    第三章10.(1)Createtable图书表( 书号nchar(6)primarykey, 书名nvarchar(30)notnull, 第一作者char(10)notnull, 出版日期smalldatetime, 价格:numeric(4,1)(2)Createtable书店表( 书店编号nchar(6)primarykey, 店名nvarchar(30)notnu......
  • Go黑帽子|文件搜索和数据库矿工
    文件搜索filepath.Walk遍历目录,regexp.MustCompile来匹配关键字packagemainimport( "fmt" "log" "os" "path/filepath" "regexp")varregexexs=[]*regexp.Regexp{ regexp.MustCompile(`(?i)user`), regexp.MustCompil......
  • 国产数据库介绍~目前最火的五款国产数据库,看看都知道哪些?
    一、TiDB:分布式NewSQL数据库简介TiDB是一款开源的分布式NewSQL数据库,由PingCAP公司开发。它采用了分布式事务和强一致性的设计理念,结合了传统关系型数据库和NoSQL的优点,能够满足高并发、大规模数据存储和实时分析的需求。优点分布式架构:TiDB采用分布式架构,可以水平扩展,支......
  • 实时api接入指南|1688商品详情实时数据接口(1688.item_get)图片、库存、规格、销量等数
    接入1688商品详情实时数据接口(1688.item_get),涉及到图片、库存、规格、销量等重要信息的获取,这些数据对于电商平台来说至关重要,可用于商品展示、分析市场趋势、优化库存管理等。下面将详细讨论如何接入此API接口,并有效利用返回的数据:注册与创建应用账号注册:需要进行账号注册......