SQL简单语句 150分

SQL简单语句 150分

1. CREATE DATABASE [HIS]2. CREATE TABLE [Patient] ( p_id int CONSTRAINT PK_id PRIMARY KEY, --病人ID，自动编号 p_name nvarchar (50) not null, --病人姓名 p_sex nvarchar (1) CHECK (p_sex=Y'男' or p_sex=N'女'), --加检查约束,格式:check (条件表达式) p_birth smallDatetime, --出生日期 p_address nvarchar (200), --家庭地址 p_status int FOREIGN KEY REFERENCES PatienStatus(ps_id) --就诊状态,外码,引用PatientStatus的ps_id字段 ) CREATE TABLE [PatientStatus] ( ps_id int CONSTRAINT PK_id PRIMARY KEY , ps_name nvarchar (20) )3. ALTER TABLE [Patient] ADD COLUMN idcard char (18)4. SELECt p_name,DATADIFF(year,p_birth,getdate()) as 年龄,p_sex FROM Patient WHERe p_sex='Y'5. SELECt *,DATADIFF(year,p_birth,getdate()) as 年龄 FROM Patient WHERe DATADIFF(year,p_birth,getdate()) = ( SELECt DATADIFF(year,p_birth,getdate()) as 年龄 FROM Patient WHERe p_name='潘石灰' )6. SELECt d_name,COUNT(d_name) FROM Doctor WHERe d_profession='主任医师'7. SELECt d_depid,COUNT(d_name) FROM Doctor GROUP BY d_depid8. SELECt * FROM Doctor LEFT JOIN Department on dep_name = '门诊一楼' and Doctor.d_depid = department.dep_id