Examples of SQL training languages

Enquiry --① in the Student table, query each student's number, name, date of birth information. Select Study number , name , date of birth from student --② the name and home address of the student who studied the number. Select name , home address from student where study number =' 0002 ' --③ Find out all the boys ' school numbers and names. Select Study number , name from student where sex =' male ' --① finds the student's number and score in the Grade table in the ~90 range. Select Study number , score from grade where fraction between --② in the grade table for the average score of the students in the course number. Select avg( score ) from grade where course number =' 0003 ' --③ in the grade table to find out the number of students who have studied each subject. Select Course Number ,count(*) number from the grade group by course number --④ The students by the date of birth from the big to the small sort. Select * from student order by date of birth desc--⑤ Check the student's number and name of all the students whose surname is "Zhang". Select Study number , name from student where name like ' Zhang% ' --3. For the student table, list the student's number, name, gender, sex order ,-the date of birth and home address, the same sex by the number from small to large order. Select Study number , name , gender , date of birth, home address from student Order by gender , study number ; --4. Use the GROUP BY query clause to list-The student's academic number and average score on the average score above. Select Study number ,avg ( score ) average score from grade group by study number Having avg( score ) >' n '; --5. Use the Union operator to associate the student's number, name, and student in the table with the name "Zhang"--course The course number, course name of the table is returned in a table, and the column name is u_ number, u_ nameSelect Study number , name from student where name like ' Zhang% ' UNIONSelect Course number u_ number , course name U_ name from course

--6. nested queries. --① Find the names and date of birth of all students who are the same gender as "Liu Weiping" in the student table. Select name , date of birth from student where sex = (select sex from Student where name =' Liu Weiping ' ) --② use in subqueries to find the student number, name, and gender of the course number you are studying. Select Study number , name , gender from student where study number in (  Select Study number from grade where course number =' 0002 ' or Course Number =' 0005 ') --③ lists the number of students with the score of the student with the lowest score of the student 's high-level course numbers and scores. Select Course number , score from grade where study number =' 0001 ' and fraction > (Select min( score ) from grade where study number = ' 0002 ') --④ the number of students who scored higher than the student's highest score for the students, plus the course numbers and scores. Select Course number , score from grade where study number =' 0001 ' and score > (Select max( score ) from grade Where study number =' 0002 ') --7. Connection query. --① Query The student's number, name, and score in the ~90 range. Select a. School Number , name , score from student a,grade B where a. Study number =b. Study number and score between --② to learn the student's number, name and score of the course "C language Programming". Select a. School Number , name , score from student a,grade B,course C where a . Study number =b. Study number and course name =' C language programming ' and b. Course Number =c. Course Number --③ Inquires all the male students to choose the course situation, request to list the student number, the name, the course name, the score. Select a. School Number , name , course name , score from student a,grade B,course C where a . Study number =b. Study number and gender =' man ' and b. Course Number =c. Course Number --④ Query the highest score for each student's chosen course, requiring a number, name, course number, and score. Select a. School Number , name , course number , score from student a, grade B where a. study number =b. Study number and score in (Select max( score ) from grade group by Study number ) --⑤ The overall scores of all students, asking for the number, name, and total scores of students who did not have an elective course. --Tip: Use left outer connection. Select a. School Number , name , total from student a left join (Select Study number ,sum ( score ) The total from the grade GROUP BY study number B on a. Study number =b. School Number --⑥ adds a data row for the Grade table: study number, course number, and score. For all courses, ask for the course number, the course name, the number of electives, and the course column values that are not available in the course table. --Tip: Use the right outer connection. Insert into grade values (' 0004 ',' 0006', () Select a. Course Number , course name , number of electives from course a right join (Select Course number , Count( number of students ) elective from grade group by course number b on a. Course number =b. Course Number

Index --(1) Use Transact-SQL statements to create a unique index by using the course Number column of the course table. Create unique index cou_in on course( course number ) --(2) Create a nonclustered index, named Grade_index, for the score field of the grade table of the STUDENTSDB database using SQL Server Management platform and Transact-SQL statements respectively. Create index grade_index on grade( score ) --(3) Create a composite unique index, named Grade_id_c_ind, for the Studentsdb and course number fields of the grade table of the database. Create unique index grade_id_c_ind on grade( school number , Course number ) --(4) Create a clustered index and a unique index on the grade table using the SQL Server Management platform. Create unique clustered index gr_inde on grade( school Number , Course number ) --(5) Rename the index Grade_index to grade_ind using the system stored procedure sp_rename. sp_rename ' Grade.grade_index ',' Grade_ind ',' index ' --(6) Delete index grade_ind using SQL Server Management platform and the Transact-SQL statement, respectively. Drop index grade. Grade_ind

View--(7) in the STUDENTSDB database, based on the student table, use the SQL Server Management platform to establish---A view named V_stu_i so that the view displays the student's name, gender, and home address. Create view v_stu_i asSelect name , gender , home address from student --(8) in the STUDENTSDB database, create a view with the Transact-SQL statement named V_stu_c,---Show the student's number, name, course number of the course, and use the view to find the student number. Create view v_stu_c asSelect a. School Number , name , course number from student a,grade B where a. Study number =b. School Number Select * from v_stu_c where study number =' 0003 ' --(9) Create a view called V_stu_g based on the student table, the course table, and the Grade table .---View has the student number, name, course name, and score for all students. ---Use the view v_stu_g to query the student number for all the courses and achievements of the students. View information for students---the graph numberCreate view v_stu_g asSelect a. School Number , name , course name , score from student a,course b,grade C where a . Study number =c. Study number and b. Course Number =c. Course Number Select *from v_stu_g where study number =' 0001 ' --(10) Modify the View V_stu_c, respectively, using the SQL Server Management platform, and Transact-SQL statements,---to show the number of students, their names, and the amount of courses each student learns. alter view Select distinct b. School Number , name , number of courses from (Select Study number , count( number ) course number from V_stu_c group by study number ) as a( number of courses ),V_stu_c b where a. Study number =b. School Number ---(11) Use the Transact-SQL statement ALTER view to modify the view v_stu_i so that it has a column name, name, and gender. ALTER VIEW v_stu_i as SELECT Study number , name , gender from student ---(12) Add a row of data to the student table using view V_stu_i: School number, name is Brenda, gender is female. Insert into v_stu_i( School Number , name , gender ) values(' 0015 ' ,' url ',' female ') ---(13) Use the view v_stu_i to delete the student record of the school number. Delete from v_stu_i where study number =' 0015 ' ---(14) Use the view V_stu_g to modify the score for the higher mathematics of the student whose name is Liu Weiping. Update v_stu_g set fraction =' + ' where name =' Liu Weiping ' and course Name =' Advanced mathematics '

Examples of SQL training languages