面试题(中等)（Interview questions (medium)）.docVIP

面试题(中等)（Interview questions (medium)） Two, moderate difficulty: (37 questions): 1. tables, student table S, curriculum C, student curriculum table SC, students can take multiple courses, a course can be selected by multiple students, through the SC table associated; 1) write table statements; Answer: build statements as follows (MySQL database): Create table s (ID integer primary key, name varchar (20)); Create table C (ID integer primary key, name varchar (20)); Create table SC Sid integer references s (ID), CID integer references C (ID), Primary key (SID, CID) ); 2) write out the SQL statement and ask the students who take all the elective courses; Answer: SQL statement is as follows: Select stu.id, from s stu Where (select count (*) from SC where sid=stu.id) = (select count (*) from C); 3) write the SQL statement and inquire for students who have chosen at least 5 or more courses. Answer: SQL statement is as follows: Select stu.id, from s stu Where (select count (*) from SC where sid=stu.id) =5; The structure of the 2. database table (Test) is as follows: (SQL) IDNAMEAGEMANAGER (the person in charge of ID) 106A30104 109B19104 104C20111 107D35109 112E25120 119F45NULL Requirements: list the ID and names of all age groups older than the person in charge Answer: SQL statement is as follows: Select from test employee where employee.age (select manager.age from test manager where manager.id=employee.manager); 3. has 3 tables (15 minutes): (SQL) Student student form (student number, name, gender, age, Organization Department) Course course schedule (number, course name) Sc elective table (student number, course number, grade) The table structure is as follows: 1) write a SQL statement, query the computer principle of the student number and name (3 minutes) Answer: SQL statement is as follows: Select stu.sno, stu.sname from Student stu Where (select count (*) from SC where sno=stu.sno and CNO = (select CNO from Course where cname=computer principle)) = 0; 2) write a S