SQL based questions

The establishment of five table, student(Id,sno, sname,sage,sex,subsidy),course(cno,cname),teacher(Tno,Tname),sc(sno,cno,scores),

tc(Tno,cno), Ask students to choose courses, namely the SC table in the CNO must be open for course courses, students' gender only for male or female students, each grant subsidy for age*0.7+30, Id for automatic growth, limited to 30.
Urgent!!!!! Answer when the best shots, do not say that I am stupid, I was a beginner, thank you

Started by Gary at November 23, 2016 - 11:55 PM

Using foreign key constraints and CHECK

Posted by Wright at December 06, 2016 - 12:50 AM

This is to find the book have a look of?

Posted by Marks at December 21, 2016 - 1:18 AM

This is not:

--drop table student

create table student
(
id int identity(1,1) check(id <= 30),
sno varchar(20),
sage int,
sex varchar(2) check(sex in ('Male ',' female')),
subsidy numeric(10,2) 
)

alter table student
add constraint ck_student_subsidy check(subsidy = sage*.07+30)


create table course(cno varchar(10) primary key,cname varchar(10))

create table teacher(Tno varchar(10),Tname varchar(10))

create table sc
(
sno varchar(10),
cno varchar(10)  foreign key references course(cno),  By foreign key references to limit the value of CNO
scores numeric(10,2)
)

Posted by Nelson at December 31, 2016 - 1:27 AM

Example is seldom wrote, said the idea.
Select the student (Id, SnO, sname, sage, sex, subsidy), course (CNO, CNAME), teacher (Tno, Tname), SC (SnO, CNO, scores), TC (Tno, CNO) table is built, and then write constraints.
The first is a foreign key constraint: ALTER TABLE SC ADD CONSTRAINT FK_SC_COURSE FOREIGN KEY (CNO) REFERENCES course (CNO) - function (SC CNO table must be open for course courses)
Then create a general constraint: ALTER TABLE student ADD CONSTRAINT CK_STUDENT check (sex = 'male' or sex='' - (female) gender role only for male or female)
When the automatic growth needs you can create table is built as follows:
create table student(
	id int identity not null primary key,
	sno int not null,
	sname varchar(50) null,
	sage int null,
	sex nchar(1),
	subsidy money null
)

In fact, the top two foreign key constraints and constraints can also be directly written on this, but do not recommend.
Behind two needs a little trouble (students each grants subsidy for age*0.7+30, Id for automatic growth, maximum 30 only)
Here used to trigger a rare, too much code to write. You are new to trigger may not learn, just to give you an idea
Create student table insert trigger, then get into when the number of queries the head office of student table, if greater than 30 is the trigger to delete the data currently inserted, if not greater than 30 is the value of the sage field, and then use the formula (sage*0.7+30) to get the value assigned to the subsidy field. All need is fulfilled

Posted by Basil at January 10, 2017 - 2:11 AM