高分悬赏几道计算机SQL数据库的题！做出来了先支付100分，要是满意的话，在加赏50~

1. 查询雇员（employee）的姓和名

Select substring(username,1,1) as 姓 from employee

Select substring(username,2,2) as 名 from employee

2. 查询雇员的姓名

Select username from employee

3. 查询雇员数

Select count(*) from employee

4. 查询雇员的姓名和职务

Select username,,duty from employee

5. 查询雇员的工龄

Select year(getdate())-开始工作日期 as 工龄 from employee

任务2：条件查询

1. 查询雇员（employee）从事"Sales Representative"职务的有哪些人

Select * from employee where duty=’ Sales Representative’

2. 查询工龄超过15年的雇员

Select * from employee where cast( (year(getdate())-开始工作日期) as int)>=15

3. 查询姓以a开头的雇员

Select * from employee where username like ‘a%’

4. 查询姓的开头字母在m以后的雇员

Select * from employee where cast((substring(username,1,1) as varchar)>=’m’

5. 认为hire_date是雇员生日，查询巨蟹座的雇员

Select * from employee where birthday between ‘6-22 ‘ and ‘7-22’

任务3：联合查询

1. 查询雇员和雇员职位

Select a.id,b.duty from employee, as a,jobs as b

2. 查询雇员、雇员职位和雇员所在出版社

Select a.id,b.duty, b.publishing from employee as a,jobs as b on a.id=b.id

3. 查询雇员、雇员工资、雇员离本职位最高工资的差值

select a. ID,a.username,a.[雇员工资],b.[最高工资]-a.[雇员工资] as [差值] from employee a,jobs b where a.[职位]=b.[职位]

---1)

创建一张学生表，包含以下信息，学号，姓名，年龄，性别，家庭住址，联系电话

CREATE

TABLE

student

(

[id]

[int]

IDENTITY(1,1)

NOT

NULL,

[student_id]

[nvarchar](50)

NULL,

[studen_name]

[nvarchar](50)

NULL,

[age]

[int]

NULL

,

[sex]

[nvarchar](5)

NULL,

[address]

[nvarchar](200)

NULL,

[tel]

[nvarchar](20)

NULL

)

--2)

修改学生表的结构，添加一列信息，学历

education

alter

table

student

add

education

nvarchar(10)

NULL

--3)

修改学生表的结构，删除一列信息，家庭住址

alter

table

student

drop

column

address

--5)

修改学生表的数据，将电话号码以11开头的学员的学历改为“大专”

update

student

set

education='大专'

where

tel

like

'11%'

--6)

删除学生表的数据，姓名以C开头，性别为‘男’的记录删除

delete

student

where

studen_name

like

'C%'

and

sex='男'

--7)

查询学生表的数据，将所有年龄小于22岁的，学历为“大专”的，学生的姓名和学号示出来

select

studen_name,student_id

from

student

where

age<12

and

education='大专'

--8)

查询学生表的数据，查询所有信息，列出前25%的记录

select

TOP

25

PERCENT

*

from

student

--9)

查询出所有学生的姓名，性别，年龄降序排列

select

studen_name,sex,age

from

studen

order

by

age

desc

--10)

按照性别分组查询所有的平均年龄

select

avg(age)

as

age

from

studen

group

by

sex

---