Practical cases of SQL Server database connection queries and sub queries
cataloguepreface1. Query the student ID, name, elective course ID, and grades of all students2
Tip: Utilize simple single table queries and advanced multi table query skills, and flexibly use inner join queries, outer join queries, or sub queries according to query requirements. At the same time, it also utilizes two formats of inner join queries, three outer join query syntax formats, and sub query syntax formats.
preface
Inner join query (querying between different tables)
1. Query the student ID, name, elective course ID, and grades of all students
Method 1
USEXSCJGOSELECTstudent.sno,sname,cno,gradefromstudent,scwherestudent.sno=sc.sno
Method 2
USEXSCJGOSELECTstudent.sno,sname,cno,gradefromstudentjoinsconstudent.sno=sc.sno
2. Search for elective courses with the name&quo; Database Principles and Applications; Student's student ID and name
Method 1
USEXSCJselectstudent. sno, snamefrom student, sc, coursewherestudent. sno=sc. snoandsc. cno=course. cnonandcname='Database Principles and Applications'
Method 2
Selectstudent. sno, snamefrom studentjoinsconstudent. sno=sc. snojoincourseonsc. cno=course. cnowherecname='Database Principles and Applications'
3. Using aliases to query the student ID, name, elective course ID, and grades of all students
selectx.sno,sname,cno,gradefromstudentx,scywherex.sno=y.sno
Self connection query
4. Query the names, genders, and ages of all students older than Zhang Wenbao
Select A. sname, A. ssex, A. sage from studentA, studentBwhere B. sname='Zhang Wenbao' and A. sage> B.sage
Using the second format to implement inline queries (JOINON)
5. Use Format 2 to query the student ID, name, elective course ID, and grades of all students
SELECTstudent.sno,sname,cno,gradefromstudentjoinsconstudent.sno=sc.sno
External connection (left external connection)
6. Query the student ID, name, and corresponding course selection information of all students. If the student has not selected a course, the student ID and name also need to be displayed
SELECTstudent.sno,sname,cno,gradefromstudentleftouterjoinsconstudent.sno=sc.sno
Right outer connection
7. Query the basic information of course selection students (if there are actually foreign key constraints, this situation does not exist)
selectsc.sno,sname,cno,gradefromscrightouterjoinstudentonstudent.sno=sc.sno
8. Use the right outer link to query the student's student ID, elective course ID, course name, and credits, as well as list the course information for non student elective courses
selectsc.sno,course.cno,cname,creditfromscrightouterjoincourseoncourse.cno=sc.cno
Full external connection
9. Implement full external connection between student and sc tables
select*fromscfullouterjoinstudentonstudent.sno=sc.sno
UNION Joint Query
10. Query the age from the student table as‘ 19’ And‘ 20’ The department of the student, excluding duplicate rows
selectsdeptfromstudentwheresage='19'unionselectsdeptfromstudentwheresage='20'
11. Query the age from the student table as‘ 19’ And‘ 20’ The department of the student, including duplicate rows
selectsdeptfromstudentwheresage='19'unionallselectsdeptfromstudentwheresage='20'
Subqueries using IN or NOTIN
12. Search for student IDs and names of all elective courses
selectsno,snamefromstudentwheresnoin(selectsnofromsc)
Change to connection query implementation
selectdistinctstudent.sno,snamefromstudentjoinsconstudent.sno=sc.sno
Subqueries using comparison operators
13. Query the student ID, name, and age of students who are older than the average age
selectsno,sname,sagefromstudentwheresage> (selectAVG(sage)fromstudent)
Using any or all subqueries
14. Query the names and ages of students who are older than any student in the CS department
selectsname,sagefromstudentwheresage> any (selectsagefromstudentwheresdept='CS') ANDsdept!=' CS'select*fromstudent
Subqueries using EXISTS
15. Query the course information of existing students' elective courses
select*fromcoursewhereexists(select*fromscwherecourse.cno=sc.cno)
16. Search for course information that no students have yet taken
select*fromcoursewherenotexists(select*fromscwherecourse.cno=sc.cno)
View the course table
Extract data to another table
17. Query the information of CS students and generate a new table temp
select*intotempfromstudentwheresdept='CS'select*fromtemp
Subqueries in the Insert statement
18. Generate a new table SCL for all student and course information
INSERTINTOSCL(sno,cno)selectsno,cnofromstudent,course
Subqueries in UPDATE statements
19. I have taken an elective course; Front Page Design” Increase student grades by 5 points in the course
UPDATEscsetgrade=grade+5whencno=(selectcnoforomcoursewheresc. cno=course. cnonandcname='Frontend Page Design')
Delete subqueries in statements
20. Deleted elective courses; Front Page Design” Course selection information for the course
Deletefromscwhercno=(selectcnoforomcoursewheresc. cno=course. cnonandcname='Front Page Design ')
summary
This is an article about SQL; That's all for the articles on SQL Server database connection queries and sub queries. For more information on SQL Server connection queries and sub queries, please search for previous articles or continue browsing related articles below. We hope everyone can support Script Home more in the future!
Tag: queries Practical cases of SQL Server database connection and
Disclaimer: The content of this article is sourced from the internet. The copyright of the text, images, and other materials belongs to the original author. The platform reprints the materials for the purpose of conveying more information. The content of the article is for reference and learning only, and should not be used for commercial purposes. If it infringes on your legitimate rights and interests, please contact us promptly and we will handle it as soon as possible! We respect copyright and are committed to protecting it. Thank you for sharing.
