The interviewer did not expect that I could talk about one SQL for an hour

Supplementary homework 1

There are three relationships:

S(SNO, SNAME, AGE, SEX,Sdept)

SC(SNO, CNO, GRADE)

C(CNO, CNAME, TEACHER)

Try a relational algebraic expression to represent the following query:

Select * from S3 where id = S3 where id = S3 where id = S3 where id = S3

Select * from girls who have taken at least one course taught by Mr. LIU.

3. Query the course number of the course that WANG does not study.

Select * from student where at least two courses are selected.

5. Query the student ID of all elective courses taught by Ms. LIU.

Supplementary homework 2

Use SQL to express the following query:

Select * from student id where student passes all courses

Method 1:

Tip: Grouping by student number, get all course scores of each student. In a student’s group of scores, if all of his courses are more than 60 points, output the student number of that group of students

Select sno frome sc group by sno having(min(grade)>=60)
Copy the code

Select * from student where student id > 90 and student failed

Self connection:

Select sno from sc where grade >90 and sno in (select sno from sc where grade<60)
Copy the code

3, Query the course number and average grade

Select cno , avg(GRADE) from sc group by cno having avg(grade)<60
Copy the code

Query course number and course name with average passing grade

Select C.cno , Cname from SC,C where C.cno=SC.cno group by C.cno having avg(grade)>=60
Copy the code

Find out who has taken at least all the courses that student No. 2 has taken

Note: there is no such course Y, student 2 took y, but student X did not.

SELECT DISTINCT Sno FROM SC as SCX WHERE NOT EXISTS (SELECT * FROM SC as SCY WHERE SCy. Sno = '2' AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno))Copy the code

5. Find the average score of each course after removing one of the highest and lowest scores

The first step is to average all grades (excluding a high and low score)

select   avg(GRADE)   from   SC     
  where   GRADE   not   in (select   top   1   GRADE   from   SC order   by   GRADE)   
  and     GRADE   not   in (select   top   1   GRADE   from   SC order   by   GRADE   desc)  
Copy the code

Second, group all grades by course number CNO

SELECT CNO avg(GRADE)   from   SC     
  where   GRADE   not   in (select   top  1  GRADE   from   SC order   by   GRADE)   
  and     GRADE   not   in (select   top  1  GRADE   from   SC order   by   GRADE   desc) group by CNO
Copy the code

Select * from student where no score from course 7;

 Select sno fromsc where cno='7' and grade is null
Copy the code

Select * from student where score > 90 or below in course 7;

Select sno from sc where cno='7' and grade not between 60and 9
Copy the code

Select course id and name from all courses whose names start with “data”.

Select cno,cname from c where cname like '%'Copy the code

4, Query each student’s average score of all courses, output student id and average score.

  Select sno,avg(grade)from sc group by sno
Copy the code

5, Query the number of electives for each course, output the number of course and electives.

    Selectcno,count(*) from sc group by cno
Copy the code

Select student id, name, gender from course 7 where student id, name, gender

   Selects.sno,sname,ssex from s,sc where s.sno=sc.sno and cno='7'
Copy the code

Or:

 Select sno,sname,ssex from s where sno in

              ( Select sno from sc where cno='7' )
Copy the code

Select * from student where age = 1 select * from student where age = 1

    Selectavg(sage) from s,sc where s.sno=sc.sno and cno='7'
Copy the code

Or:

 Select avg(sage) from s where sno in

              (Select sno from sc where cno='7' )
Copy the code

Select * from course where there are more than 30 students.

 Select cno fromsc group by cno having count(*)>30
Copy the code

Query has not failed the exam student number.

    Select distinctsno from sc where sno not in

         ( Select sno from sc where grade<60 )
Copy the code

Or:

Select sno from sc group by sno havingmin(grade)>=60
Copy the code

Add three

1. Find the student id number and grade of C2.

Select sno,grade from sc where cno='C2'
Copy the code

2, find the student number and name of the elective course no. C4.

Selects.sno,sname from s,sc where s.sno=sc.sno and cno='C4'
Copy the code

Note that you can also use nesting in this case

Find the student id, name and grade of elective course no. C4.

3, find out the student number and name of the course named Maths.

   Selects.sno,sname from s,sc,c
    where  s.sno=sc.sno and c.cno=sc.cno andcname='Maths'
Copy the code

Note that you can also use nesting in this case

4, find the student id of elective course no. C2 or C4.

  Select distinctsno from sc where cno in ('C2','C4')
Copy the code

Or:

Select distinct sno from sc where cno='C2' or cno='C4'
Copy the code

5, find the student id of the elective course number C2 and C4.

   Select sno fromsc where cno='C2' and sno in

         ( Select sno from sc where cno='C4' )
Copy the code

Note that this can also be done by joining

Think about:

Select distinct sno from sc where cno=’ C2 ‘andcno=’C4’

Find out the names and ages of students who are not taking C2.

    Selectsname,sage from s where sno not in

         ( Selectsno from sc where cno='C2' 
Copy the code

Or:

 Select sname,sage from s where not exists

              (Select * from sc where sno=s.sno and cno='C2' ) 
Copy the code

Find out the names of all the students who took the database course. (3) with

Select snamefrom s,sc,c where s.no =sc.cno and c.cno=sc.cno and cname=' dB 'Copy the code

Find out the names of the girls who failed the database course.

Select sname from s,sc,c where s.no =sc.sno andc.cno=sc.cno and cname=' db 'and grade<60 and ssex=' female'; Select sname from s where ssex=' female 'and sno in (Select sno from SC where grade<60 and cno in (Select cno from C where ssex=' female' Cname =' database ')Copy the code

9, Find out the average grade of each course, output the course name and average grade.

  Selectcname,avg(grade) from sc,c

    wherec.cno=sc.cno  group by c.cno,cname
Copy the code

Can you also use nesting?

Find out the average score of each student, output the student name and average score.

   Selectsname,avg(grade) from s,sc

    wheres.sno=sc.sno group by s.sno,sname
Copy the code

Can you also use nesting?

11. Find out which courses at least 30 students have taken.

 Select cnamefrom c where cno in

         ( Selectcno from sc group by cno having count(*)>=30 )
Copy the code

Note that this can also be done by joining

Find out the names of students who have taken at least three courses.

  Select snamefrom s where sno in

         ( Selectsno from sc group by sno having count(*)>=3 )
Copy the code

Note that this can also be done by joining

Find out the names of students who scored 90 or more in all courses.

   Select snamefrom s,sc where s.sno=sc.sno

         group bys.sno,sname having min(grade)>=90
Copy the code

Method 2:

Select sname from s where sno not in

         ( Selectsno from sc where grade<90 )
Copy the code

As long as there is no less than 90 points, the output of the student id

Find out the names of the students who scored at least below the average in the database course.

Select snamefrom s,sc,c where s.no =sc.sno and sc.cno=c.cno and cname=' database 'and grade> (Selectavg(grade) from sc,c Where sc.cno= c.noand cname=' db ')Copy the code

Find out the average age and number of male and female students in each department.

    Selectsdept,ssex,avg(sage),count(*) from s group by sdept,ssex
Copy the code

16, Find out the student id number and name of the student with the highest average in the Computer Science Department (JSJ).

    Selects.sno,sname from s,sc where s.sno=sc.sno and sdept='JSJ'

    group bys.sno,sname

    havingavg(grade) >=ALL

         ( Selectavg(grade) from s,sc

           wheres.sno=sc.sno and sdept='JSJ'

           group bys.sno

         )
Copy the code

Check the pass rate for each course.

This problem can be divided into three steps:

Step 1: Get the number of students in each course

   createview  v_all(cno,cnt)

         as selectcno, count(*) from sc group by cno
Copy the code

Step 2: Get the number of people who passed each course

  createview  v_pass(cno,cnt_pass)

         as selectcno, count(*) from sc where grade>=60 group by cno
Copy the code

Step 3: Number of passers/electives in each course

 selectv_all.cno, cnt_pass*100/cnt  from  v_all, v_pass

     where v_all.cno = v_pass.cno
Copy the code

Query student id, name, average score.

   Selectsc.sno,sname,avg(grade) from student,sc

    wherestudent.sno=sc.sno

    group bysc.sno,sname

    havingavg(grade)<60
Copy the code

Can you also use nesting?

Query the number of students who fail on average.

   Select count(*)from student

    where sno in

         ( selectsno from sc group by sno having avg(grade)<60 )
Copy the code

Here’s a typical mistake

Select count(*) from sc group by sno havingavg(grade)<60
Copy the code

That’s how many courses each student has failed

Added four

1, query the name and office number of male salesmen whose salary is between 1000 and 3000 yuan.

SelectYname,Ono from YWY where Salarybetween 1000 and 3000 and Ysex=' male 'Copy the code

2. Query the number of salesmen in each office and output the office number and the corresponding number.

 SelectOno,count(*) from YWY group by Ono
Copy the code

3. Query the total amount purchased by each customer in May 2002, output the customer number and the corresponding total amount.

   SelectKno,sum(Fmoney) from FP

    where Fdatebetween '2002.5.1' and '2002.5.31'

    group by Kno
Copy the code

Query all customer numbers that have purchased more than 5 times in May 2002, and sort by customer number ascending.

Select Kno fromFP WHERE Fdatebetween '2002.5.1' and '2002.5.31' group by Kno havingCount (*)>5 order by KnoASCCopy the code

5. Query the average salary of male and female salesmen in each office.

   SelectOno,Ysex,avg(Salary) from YWY group by Ono,Ysex
Copy the code

6. Query the customer number, customer name and contact number of the goods purchased by Salesman Wang Hailiang in May 2002.

SelectKno,Kname,Phone from KH where Kno in (SelectKno from FP whereFdate between '2002.5.1' and '2002.5.31' and Yno in (Select Yno from YWY where Yname=' wanghailong ')Copy the code

Note that this can also be done by joining

Select * from salesman where salary is higher than 1538;

  SelectYno,Yname,Salary from YWY where Salary >

         ( SelectSalary from YWY where Yno='1538' )
Copy the code

Query the numbers and names of all other salesmen in the same office as Salesman no. 1538.

SelectYno,Yname from YWY where Yno! ='1538' and Ono in ( SelectOno from YWY where Yno='1538' )Copy the code

Query the number of the salesman with the highest total amount of sales.

    Select Yno fromFP group by Yno having sum(Fmoney) >=ALL

         ( Selectsum(Fmoney) from FP group by Yno )
Copy the code

Query the number, name, salary and average salary of all salespeople whose salary is higher than his.

Make use of self-connection

    SelectY1.Yno,Y1.Yname,Y1.Salary,avg(Y2.Salary)

    from   YWY Y1, YWY Y2

    where  Y1.Salary < Y2.Salary

    group by  Y1.Yno 
Copy the code

Add five

1. Find out the class code, number of students and average grade of each class.

  SelectBJDM,count(*),avg(CJ) from SC group by BJDM
Copy the code

2, find out each student’s class code, student’s name, the number of examination subjects, the total score.

    SelectBJDM,XSXM,count(*),sum(CJ) from SC

    group byBJDM,BNXH,XSXM
Copy the code

3, output a table, each student corresponds to a record, including fields: class code, student name, Chinese score, math score, foreign language score.

SelectSC1.BJDM,SC1.XSXM,SC1.CJ,SC2.CJ,SC3.CJ from SC SC1, SC SC2, BJDM= sc2. BJDM and sc2. BJDM= sc2. BNXH and sc2. BNXH= sc3. BNXH and sc2. BNXH= sc3. BNXH and sc1. KM=' language 'and sc2. BJDM= sc3. BNXH and sc1. KM=' language' and sc2. BJDM= sc3. BNXH and sc1. KM=' language 'and sc2. BJDM= sc3. BNXH and sc1. KM=' language Sc2. KM=' math 'and sc3. KM=' foreign language'Copy the code

4. Output a table with a record for each student with a score below 60, including fields: class code, student name, and lowest score.

SelectBJDM,XSXM,min(CJ) from SC where CJ<60 group by BJDM,BNXH,XSXM or  SelectBJDM,XSXM,min(CJ) from SC group byBJDM,BNXH,XSXM havingmin(CJ)<60Copy the code

5. Output a table with a record for each student with a score below 60, including fields: class code, student name, highest grade, average grade.

SelectBJDM,XSXM,max(CJ) from SC

    group byBJDM,BNXH,XSXM

    havingmin(CJ)<60
Copy the code

Please consider whether the following is correct:

    SelectBJDM,XSXM,max(CJ),avg(CJ) from SC

         where  CJ<60 group byBJDM,BNXH,XSXM
Copy the code

6, Output a table with a record for each student whose score is at least 60, including fields: class code, student name, average grade.

 SelectBJDM,XSXM,avg(CJ) from SC

    group by BJDM,BNXH,XSXM

    havingmin(CJ)>=60
Copy the code

7, Output a table with one record for each student, including fields: class code, student name, and average after removing a minimum score.

    SelectBJDM,XSXM,(sum(CJ)-min(CJ))/(count(*)-1) from SC

    group byBJDM,BNXH,XSXM
Copy the code

8, output a table with one record for each subject, including fields: subject, average grade after removing a minimum score.

  Select KM,(sum(CJ)-min(CJ))/(count(*)-1)from SC

    group by KM
Copy the code

Added six

Select * from student where age between 19 and 21; select * from student where age between 19 and 21;

Selectsno,sname,sage from student where sagebetween 19 and 21 and ssex=' female 'order by sagedescCopy the code

Select * from student where name = ‘Ming’;

Select count(*)from student where snamelike "% Ming %"Copy the code

Select * from ‘1001’ where id = 1;

    Select sno fromsc where cno='1001' and grade is null
Copy the code

Select student id, name from JSJ, SX, WL from JSJ; select student ID from JSJ, SX, WL from JSJ; select student ID from JSJ;

  Selectsno,sname,sdept from student

    where sdept in( 'JSJ', 'SX', 'WL' )

    order bysdept,sno
Copy the code

5. Calculate the total score, average score, highest score and lowest score of each course.

 Selectcno,sum(grade),avg(grade),max(grade),min(grade)

    from sc

    group by cno
Copy the code

Select * from student id where average score > 90;

Connection:

Selectsc. sno,avg(grade) from student,sc wherestudent.sno=sc.sno and ssex= 'male' group by sc.sno havingavg(grade)>90Copy the code

Nested:

Select sno,avg(grade) from sc where sno in (select sno from student where ssex=' male ') group by sno havingavg(grade)>90Copy the code

Select * from student where there are more than 2 elective courses.

select snamefrom student,sc

    where student.sno=sc.sno

    group bysc.sno,sname

    havingcount(*)>2
Copy the code

In this case, you can also use nesting

Select * from JSJ department where student elects course number

  Select distinctcno from student,sc

    where  student.sno=sc.sno and sdept='JSJ'
Copy the code

In this case, you can also use nesting

Select student_name from student_name where student_name = ‘1002’;

Select sname from student,sc wherestudent.sno=sc.sno and cno='1002' Select sname from student where sno in (select sno from sc where cno='1002' )Copy the code

Select student’s name, course number and grade.

    Selectsname,cno,grade from student,sc

    wherestudent.sno=sc.sno
Copy the code

Can you also use nesting?

Select * from student whose score is 80 or above; select * from student whose score is 80 or above;

Select sname from student,sc,course wherestudent.sno=sc.sno and sc.cno=course.cno and cname=' db 'and grade>80 Select sname from student where sno in (select sno from sc where grade>80 and cno in ( select cno from course where Cname =' database principle ')Copy the code

Select * from student where student_id = ‘1002’ where student_id = ‘1002’

 Select snamefrom student

    where sno notin ( select sno from sc where cno='1002')

    或： select sname from student

         where notexists

              (select * from sc where cno='1002' and sno=student.sno)
Copy the code

Can you also use a general connection?

If software testing, interface, automation, performance testing, test development, interview experience exchange. If you are interested in 1079636098, there will be free information links in the group from time to time. These materials are collected and sorted out from various technical websites. If you have good learning materials, you can send them to me privately, AND I will indicate the source and share them with you.

Query student ID and average score of student with highest average.

   Selectsno,avg(grade)

    from sc

    group by sno

    havingavg(grade) >=ALL ( Select avg(grade)

                              from sc

                              group by sno

                            )
Copy the code

Select * from student where the student’s score in each course is above the average.

You can calculate the average of each course first

Create viewc_avg(cNO,avg_grade) as selectCno, AVG (grade) from SC group by CNO Select distinctsno from SC where SNO notin ( Select sno from sc,c_avg where sc.cno=c_avg.cno and grade<avg_grade ) =========================================== SELECT DISTINCT Sno FROM SC SC1 WHERE SC1.SnoNOT IN ( SELECT SC2.Sno FROM SC  SC2 WHERE SC2.Grade <= ( SELECT AVG(SC3.Grade) FROM SC SC3 WHERE SC3.Cno=SC2.Cno ) )Copy the code

Or:

 SELECT DISTINCTSno

    FROM SC SC1

    WHERE NOTEXISTS

          (SELECT *

            FROM SC SC2

            WHERE SC2.Sno=SC1.Sno AND SC2.Grade <=

                       (SELECT AVG(SC3.Grade)

                         FROM SC SC3

                         WHERE SC3.Cno=SC2.Cno

                       )

          )
Copy the code

(3) Retrieve the names of female students who have taken at least one of the courses taught by Mr. LIU.

SELECT SNAME FROM S WHERE SEX= 'F' AND S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM SC WHERE SEX= 'F' The FROM C WHERE the TEACHER = "LIU")Copy the code

NOTICE: can be written in various ways, such as join query:

SELECT SNAME FROM S,SC,C WHERE SEX= 'F' AND SC.S#=S.S# AND SC.Copy the code

But the previous way is better.

(4) Retrieve the course number of the courses that WANG does not study.

SELECT C# FROM C WHERE C# NOT IN (SELECT C# FROM SC WHERE S# IN (SELECT S# FROM S WHERE the SNAME = 'WANG'))Copy the code

(5) Retrieve student ID numbers of at least two elective courses.

SELECT DISTINCT x. scno FROMSC X,SC Y WHERE x. scno = y.scno AND x. scno <> Y.scnoCopy the code

Notice: Connects the SC table automatically. X and Y are aliases of SC.

(6) Retrieve the course number and course name of all elective courses.

SELECT C#,CNAME FROM C WHERE NOT EXISTS (SELECT * FROM S WHERE S# NOT IN (SELECT * FROM S The FROM SC WHERE SC. = Arthur c. c # #))Copy the code

Semantic decomposition :(1) select the course number and course name. There are no students who do not choose this course.

Where, “students who do not choose this course” can be expressed as:

SELECT * FROM S WHERE SC #= C#Copy the code

SELECT * FROM SC WHERE S.S#=C.S# AND SC.C#=C.C#Copy the code

(7) Search the student id of the elective course taught by Miss LIU.

SELECT C# FROM SC WHERE C# IN (SELECT C# FROM C WHERE TEACHER='LIU')Copy the code

3.3 There are two basic tables R (A, B, C) and S (D, E, F). Try SQL query to express the following relational algebraic expressions:

Sigma PI A ® (1) (2) B = ’17’ ® (3) R * S (4)) PI A, F (sigma C = D (R (S))

(1)SELECT A FROM R

(2)SELECT * FROM R WHERE B= ’17’

(3)SELECT A,B,C,D,E,F FROM R,S

(4)SELECT A,F FROM R,S WHERE R.C=S.D

3.43.4 There are two basic tables R (A, B, C) and S (A, B, C). Try SQL query to express the following relational algebraic expressions:

(1) R∪S (2) R∩S (3) R-s (4) πA,B®πB,C(S)

(1)

SELECT A,B,C FROM R UNION SELECT A,B,C FROM SCopy the code

(2)

SELECT A,B,C FROM R INTERSECT SELECT A,B,C FROM SCopy the code

(3)

SELECT A,B,C FROM R WHERE NOT EXISTS (SELECT A,B,C FROM S WHERE R.A=S.A AND R.B=S.B AND  R.C=S.C)Copy the code

(4)

SELECT R.A,R.B,S.C FROM R,S WHERE R.B=S.BCopy the code

3.5 Describe the characteristics of relational algebra and tuple calculus of SQL language.

3.6 Try SQL query statement to express the following query of the three basic tables S, SC and C in the teaching database:

(1) Count the number of elective courses for students.

SELECT COUNT(DISTINCT C#) FROM SC
Copy the code

(2) Find the average age of students taking C4 courses.

SELECT C# FROM SC WHERE C#='C4' SELECT C# FROM SC WHERE C#='C4'Copy the code

SELECT AVG(AGE) FROM S, SC WHERE S#=SC # AND C#='004'Copy the code

(3) Ask the average score of each course taught by Mr. LIU.

SELECT C# FROM SC WHERE SC.C#=C # ANDTEACHER='LIU' GROUP BY C#Copy the code

(4) Count the number of students enrolled in each course (only for courses with more than 10 students). Ask to output the course number and the number of electives, query results according to the number of descending order, if the number of the same, according to the course number ascending order.

SELECT C#,COUNT(S#) FROM SC GROUP BY C# HAVING COUNT(S#)>10 ORDER BY 2 DESC, C# ASCCopy the code

(5) Retrieve the names of students whose student id is older than WANG but whose age is younger than WANG.

SELECT x.name FROM S AS X, S AS Y WHERE y.name ='WANG' AND X.S#>Y.S# AND x.age < y.ageCopy the code

(6) Retrieve the names and ages of all students whose names start with WANG.

SELECT SNAME,AGE FROM S WHERE SNAME LIKE 'WANG%'Copy the code

(7) Search for student ID and course ID with null score in SC.

SELECT C# FROM SC WHERE GRADE IS NULLCopy the code

(8) Find the name and age of the male student who is older than the average age of the female student.

SELECT SNAME,AGE FROM S AS X wherex. SEX=' male 'AND x.age >(SELECT AVG(AGE)FROM S AS Y WHERE y.ex =' female ')Copy the code

(9) Find the name and age of the male student who is older than all the female students.

SELECT SNAME,AGE FROM S AS X WHERE x.ex =' male 'AND x.age >ALL (SELECT SNAME,AGE FROM S AS Y WHERE y.ex =' female ')Copy the code

division

3.7 Try out SQL update statements to express each update operation of the three basic tables S, SC and C in the teaching database:

Insert a student tuple (‘ S9 ‘, ‘WU’, 18) into basic table S.

INSERT INTO S(S#,SNAME,AGE) VALUES('59','WU',18)Copy the code

Select * from STUDENT (S #, SANME, SEX); select * from STUDENT (S #, SANME, SEX); select * from STUDENT (S #, SANME, SEX);

INSERT INTO STUDENT(S#,SNAME,SEX) SELECT S#,SNAME,SEX FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE SC = 1 GRADE < 80 AND S.S # = SC. S #)Copy the code

(3) Delete unscored tuples from basic table SC.

DELETE FROM SC WHERE GRADE IS NULLCopy the code

(4) Delete all of WANG’s courses and grades.

DELETE FROM SC WHERE S# IN (SELECT S# FROM S WHERE SNAME='WANG')Copy the code

(5) All the failed grades of the elective MATHS class will be null.

UPDATE SC SET GRADE=NULL AND C# IN (SELECT C# FROM C WHERE CNAME='MATHS')Copy the code

(6) Raise the score of female students below the overall average by 5%.

UPDATE SC SET GRADE=GRADE*1.05 WHERE GRADE<(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S# FROM SWHERE SEX='F')Copy the code

(7) Change the score of C4 course in basic table SC, if the score is less than or equal to 75 points increase 5%, if the score is greater than 75 points increase 4% (with two UPDATE statements).

UPDATE SC SET GRADE=GRADE*1.05 WHERE C#='C4' AND GRADE<=75 UPDATE SC SET GRADE=GRADE*1.04 WHERE C#='C4 C#='C4' AND GRADE>75Copy the code

3.8 “Warehouse Management” relationship model has five relationship models:

PART (P#, PNAME, COLOR, WEIGHT)

PROJECT (J#, JNAME, DATE)

SUPPLIER (S#, SNAME, SADDR)

P_P (J#, P#, TOTOAL)

P_S (P#, S#, QUANTITY)

(1) Try SQLDDL statement to define the above five basic table, and describe the primary key and foreign key.

CREATE TABLE PART (P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL, COLOR CHAR(10),WEIGHT REAL, PRIMARY KEY(P#)) CREATE TABLE PROJECT (J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL, DATE DATE, PRIMARY KEY(J#)) CREATE TABLE SUPLIER (S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20), PRIMARY KEY(S#) CREATE TABLE P_P (J# CHAR(4),P# CHAR(4),TOTAL INTEGER, PRIMARY KEY(J#,P#), FOREIGN KEY (j #) REFERENCE PROJECT (j #), FOREIGN KEY(P#) REFERENCE PART(P#)) CREATE TABLE P_S (P# CHAR(4),S# CHAR(4),QUANTITY INTEGER, PRIMARY KEY(P#,S#), REFERENCE PART(P#), REFERENCE SUPLIER(S#))Copy the code

(2) Try to define the natural join of the three basic tables PROGECT, P_P and PART as a view VIEW1, and the natural join of the three basic tables PART, P_S and SUPPLIER as a view VIEW2.

The CREATE VIEW the VIEW1 (j #, JNAME, DATE, P#, PNAME, COLOR, WEIGHT, TOTAL) AS the SELECT JNAME PROJECT. J #, DATE, PART P#, PNAME, COLOR, WEIGHT, TOTAL FROM the PROJECT, PART, P_P the WHERE PART. P# = P_P. P# AND p_p. J#=PROJECT.J# CREATE VIEW VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY) AS SELECT PART. P#, PNAME, COLOR, WEIGHT, : SUPPLIER. S #, SNAME, SADDR, QUANTITY FROM PART, P_S, : SUPPLIER WHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S#Copy the code

(3) Try to query data based on the above two views:

1) Retrieve the numbers and names of parts supplied by suppliers in Shanghai.

SELECT P#,PNAME FROM VIEW2 WHERE SADDR='SHANGHAI'Copy the code

2) Retrieve the supplier number and name of the parts used in project J4.

SELECT S#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1 WHERE J#='J4')Copy the code

3.9 For the basic table SC in the teaching database, the following views have been established:

CREATEVIEWS_GRADE (S#, C_NUM, AVG_GRADE) ASSELECTS#, COUNT(C#), AVG (GRADE) FROMSC GROUPBYS#Copy the code

Determine whether the following queries and updates are allowed to execute. If allowed, write out the corresponding operation to convert to base table SC.

(1)

SELECT* FROMS_GRADE FROM SC GROUP BY S#Copy the code

(2)

SELECT S#,COUNT(C#) FROM SC WHEREAVG(GRADE)>80Copy the code

(3)

SELECTS#, SELECT S#,AVG(GRADE) FROM SC AS X and SELECT S#,AVG(GRADE) FROM SC AS X SELECTCOUNT(X.C#)>(SELECTCOUNT(Y.C#) FROM SC AS Y WHERE Y.S#='S4') GROUP BY S#Copy the code

(4)

UPDATES_GRADE SETC_NUM = C_NUM +1 where # = 'S4' Not allowedCopy the code

(5)

DELETEFROMS_GRADE WHEREC_NUM > 4 Is not allowedCopy the code

3.10 What is the significance of pre-processing for the implementation of embedded SQL?

The preprocessing method is to scan the source program with the preprocessor, identify the SQL statement, and process it into the function call form of the host language. The host language compiler is then used to compile the source program into the target program. In this way, SQL statements can be processed without augmenting the compiler of the host language.

3.11 What are the rules for using SQL statements in host language programs?

The following rules govern the use of SLQ statements in programs of the host language:

(1) in the program to distinguish between SQL statements and host language statements

(2) Allow embedded SQL statements to reference program variables (called shared variables) in the host language, with two provisions:

1) These variables must be prefixed with “:” to distinguish them from variables in the database.

2) These variables are defined by the program of the host language and specified with SQL DECLARE statements.

(3) The collection processing mode of SQL and the host language single record processing mode should be coordinated. A cursor mechanism is needed to convert set operations into single record processing.

3.12 How to coordinate the collection processing mode of SQL with the single record processing mode of host language?

Because SQL statements deal with collections of records, whereas host language statements can only deal with one record at a time, we need to convert collection operations to single-record processing using the cousor mechanism.

2.13 When do embedded SQL statements not have to involve cursors? When must cursors be involved?

(1)INSERT, DELETE, UPDATE statements, query results must be a unit group of SELECT statements, can be directly embedded in the main program, do not involve cursors.

(2) When the SELECT statement query result is multiple tuples, at this time the host language program cannot use, must use the cursor mechanism to send multiple tuples one at a time to the host language processing.

(e-commerce) database principle and application _ simulation examination questions and reference answers

1. Single option (1 point for each space, 10 points in total)

1. In an Access database, data is stored in () objects.

A. The form B. Query C. Statements D. table

2. If the data type of a field is text and the field size is 8, a maximum of () Chinese characters can be entered in the field.

A. 8 B. 4 C. 16 D

3. Text fields can hold up to () characters.

A. 250 B. 252 C. 254 D. 255

4. Access user operation interface consists of ().

A. 4 B. 5 C. 3 D

5. The following () icon is the symbol of a table object in Access.

A. B. C. D.

6. When designing Access tables, the “index” attribute has a value of ().

A. 1 B. 2 C. 3 D. 4

7. Access contains () types of data.

A. 9 B. 10 C. 7 D. 8

8. To open a report in a macro, the action to use is ().

A. openform B. openreport C. openable D. openQuery

9. The object for which data can be published over the Internet is ().

A. The form B. Statements C. Query D. Data access page

10. The module window consists of () parts.

A. 2 B. 3 C. 4 D. 5

2. Fill in the blanks (1 point for each blank, 20 points in total)

1. In the manual and file management phases, the program design __ relies on ___ data representation.

2. In a file system, the basic unit of data access is ___ record ____. In a database system, the basic unit of data access is ___ data item _____.

3. If A and B are many-to-many and B and C are 1-to-1, A and C are ___ many _____ and ___ many _____.

4. The duplicate ____ tuple ____ and the ___ property _____ with the same name are not allowed in a relationship.

5. The four types of users in the database system are ____ database administrator, database designer, application programmer, and end user _____.

6. In the process of accessing data from the database, two data buffers are used, the ___ system _____ buffer and the ____ user ____ buffer.

7. The attribute of the class number in the student relationship corresponds to the attribute of the main code of the class number in the student relationship, so ____ class number ____ is ___ foreign code ___ in the student relationship.

8. If A relation A has A1 attributes and A2 tuples and relation B has B1 attributes and B2 tuples, then relation A´B has ___A1 + B1 ____ attributes and ____ A2 ´ B2 ____ tuples.

9. Set a student relationship as S(student number, name), course relationship as C(course number, course name), and course selection relationship as X(student number, course number, grade). Find the expression of the information of all students who have selected courses as _____ O student number (X)______ and ____S ____.

10. R in a relationship, if X and Y and X and Z, there is _____ X – (Y, Z)…, call this function depends on the merger of the rules.

3. Fill in the blanks (1 point for each blank, 20 points in total)

1. A relationship is said to reach the ____ second ____ normal form if no non-primary attribute is partially dependent on any candidate code.

2. In SQL, column-level integrity constraints are divided into __6__ cases and table-level integrity constraints into __4__ cases.

In SQL, the columns in each view can come from different ___ table ___, which is a logical new relationship established by ____ ____ on the basis of the existing table.
In the SQL query statement, the group by option implements ____ group statistics ______ function, and the order by option implements ____ sort _____ function for the result table.

5. For a more complex system, the main task in the conceptual design stage is to draw the corresponding ____ local ER diagram ______ according to each local application of the system, and then draw _____ overall ER diagram _____ through comprehensive and overall design.

6. The goal of the machine realization stage is to get a database application system in the computer system that can meet the design requirements of ______, with perfect functions and convenient operation.

7. The user operation interface of Access is composed of five parts: title bar, menu bar, working area, tool bar and status bar.

The “table Designer” in the upper part of the window is composed of three columns, such as field name, data type and description.

9. A form in Access consists of three parts: header, body and footer.

4. Fill in the blanks (1 point for each blank, 20 points in total)

Set A relation R (A, B, C, D, E), its smallest function dependent sets FD = {A, B, A, C, (C, D, E}, then the relationship between the candidate code for _____ _____ (A, D), the candidate code is ___ pseudo-transfer function decision E ___.

2. Set A relation R (A, B, C, D, E), its smallest function dependent sets FD = {A, B, A, C, (A, D, E}, the relationship only to satisfy the first _____ _____ paradigm, to standardization as the paradigm of high level, you will get ____2____ A relationship.

3. In the actual database management system, there are three database operation modes: _____ command interaction, program execution, and window interface ______.

4. In SQL, the main key code constraints for ____ primary key________, keyword for ______foreignkey… yards outside constraints.

5. The base table belongs to the table in the global schema, which is ____ real table ____, while the view belongs to the table in the local schema, which is ____ virtual table ____.

In the new VERSION of the SQL query, the select option implements the projection operation, the from option implements the ____ connection ____ operation, and the where option implements the ____ operation.

7. Data dictionary is a description of ____ data ____ and ____ processing ____ in the system workflow.

8. The global schema in a relational database system consists of several basic tables. The relation between tables is realized by defining ____ primary code ____ and ____ external code ____.

9. In hotel management, there are four basic tables involved, they are room table, accommodation table, _ check-in table, consumption card table.

10. Passwords are case-sensitive when setting or revoking database passwords.

5. Fill in the blanks (1 point for each blank, 10 points in total)

1. The user operation interface of Access is composed of five parts: title bar, menu bar, working area, tool bar and status bar.

The “table Designer” in the upper part of the window is composed of three columns, such as field name, data type and description.

3. The form in Access is composed of three parts, such as header, body and footer.

4. In Access module is divided into two types: class module and standard module.

Six, according to the main textbook chapter four of the commodity library and teaching library, or in accordance with the following each SQL query statement to write the corresponding function, or in accordance with the following each function to write the corresponding SQL query statement. (4 points per 小题 20 points in total)

In the database named Commodity Repository are commodity Table 1 and Commodity Table 2, which are defined as:

Table 1(float: char(8), float: int)

Table 2(Product Code CHAR (8), Origin CHAR (6), Brand Char (6),)

In the teaching database, there are three tables: students, courses and elective courses. They are defined as follows:

Student (Student ID Char (7), Name Char (6), gender Char (2), date of birth datetime,

Professional char(10), Grade INT

Course (Course number CHAR (4), course name CHAR (10), Course credit int

Select course (student ID CHAR (7), course ID char(4), grade int)

1. Select distinct origin from commodities Table 2 Function: Query the distinct origin of all commodities from the commodity database.Copy the code

Select * from student where student id in (select student ID from select group by student id having count(*)=1) select * from student where student ID in (select student ID from select group by student ID having count(*)=1) 3. Select * from student where student id in (select student ID from student group by student id having count(*)<=2) or not exists (select * from student Where students. Student ID = course selection. Student ID) Function: Query all students who have selected 2 courses at most (including none) from the teaching database.Copy the code

4. Find the highest unit price for each category from the commodity database.

Select Max (unit price) as Max (unit price) from table 1 group by group nameCopy the code

5. Search the teaching library for all students whose names are at least one of the courses chosen by @m1 students.

Select * from students where students are distinct. Select student_id from student_id where student_id =any(select student_id from student_id where student_id =any) And name =@m1)Copy the code

Form a good habit

Wechat search a search ** [programmer yifan] ** pay attention to this graceful programmer, pay attention to reply [interview] I prepared a line of big factory interview materials and resume template, I hope we can find the right job, learning is a sometimes depressed, sometimes laugh on the road, come on. If you’ve worked your way into the company of your choice, don’t slack off. Career growth is like learning new technology. If lucky, we meet again in the river’s lake!

The interviewer did not expect that I could talk about one SQL for an hour

Related Posts

【 jian Finger offer】10.1 Fibonacci sequence

2021, front-end ape nervous, serious, lively year-end summary!

Hexo-2 – Site information configuration