Common SQL statement query sharing _mssql

--Create a database (file: Primary data file Mdf==1, secondary data file ndf>=0, log file ldf>=1)
--File group: When 1mdf,5 ndf (1,2,2), 10 ldf (3,3,4), divided them into multiple groups storage

CREATE database studb;

--Create TABLE Teacher,student

CREATE TABLE Teacher
(
tid int () primary key auto_increment,
tname varchar (a),
Tage int)
; C6/>use Studb;

CREATE TABLE Student
(
SID Int () primary key auto_increment,
sname varchar (),
Sage Int (10),
Tid Int (a) REFERENCES teacher (TID) 
);

--FOREIGN KEY constraint: Who are you asking John's teacher??

--select teacher.tname from teacher,student where student.sname = ' john '
select T.tname from teacher T,student s where S.sname = ' john ' and T.tid = S.tid

--Create a timetable

CREATE TABLE Course
(
CID int () primary key,
cname varchar,
tid int (a) REFERENCES teacher (TID) c5/>);

--Create a score table

CREATE TABLE SC
(
SCID Int () primary key,
SID Int (Ten) REFERENCES student (SID),
CID Int (10) REFERENCES Course (CID),
score Int (a)
);

--Joint inquiry: Equivalence query
--1..

Select C.cname from Course c,student s,sc where s.sname = ' small Zhang ' and 
s.sid = sc.sid and c.cid = Sc.cid;

--2..

Select sname from student s,course c,sc where C.cname= ' Android ' and sc.score>=60 and s.sid
= sc.sid and C.cid = SC . CID;

--3..
--subquery: When the condition also wants to inquire, I only know the school number, I do not know "Xiao Zhang" this field, then you know Xiao Zhang's study number?

Delete from sc where sid = (select SID from student where sname = ' little Zhang '); 

--the symbol in the middle of the subquery must be the field (typically the primary foreign key) associated with both tables of the parent query and the subquery.

--4..

Update SC set score=score+5 where cid=????;

Select Tid from teacher where tname= ' Miss Li ' ==1
select CNAME from course where tid = 1 = = Course name, teacher Li teaches
select CID from Course where cname= ' android ' = = Course ID
Update SC set score=score+5 where cid=
(
Select CID from course where Cnam E=
(
Select CNAME from course where Tid =
(
Select tid from teacher where tname= ' Miss Li '
)
) 
   );