Database interview questions-CodePudding

The create table class01 (
Classno number (4),
The classname varchar2 (8)
);
Select * from class01;
Insert into class01 values (101, 'a class');
Insert into class01 values (102, 'second class');
commit;
The create table student01 (
Sno number (6),
Sname varchar2 (6),
Score number (3),
Classno number (4)
);
Insert into student01 values (1001, 'zhang, 77101);
Insert into student01 values (1002, 'bill, 55102);
Insert into student01 values (1003, 'Cathy', 99101);
The create table score01 (
Minscore number (2),
Maxscore number (3),
Level01 varchar2 (3)
);
The alter table score01 modify (level01 varchar2 (10));
Select * from score01;
Insert into score01 values (0 ', 'fail');
Insert into score01 values (60; seven, 'pass');
Insert into score01 values (70, 'good');
Insert into score01 values (80100, 'optimal');

Question 1: e-r relation design
Question 2: query the zhang SAN's score in the table level01 level

CodePudding user response:

1, the three entities, the student, class, grade level, respectively through the class ID and score associated
2, chosen by zhang SAN scores within the scope of the grading, similar score> field 1 and score CodePudding user response:

reference 1st floor wandier response:

1, the three entities, students, classes, grades, respectively through the class ID and score associated
2, chosen by zhang SAN scores within the scope of the grading, similar score> field 1 and score

The select level01 from score01
The where (select score from student01 where sname='* *') & gt; Minscore
And (select score from student01 where sname='* *') & lt; Maxscore;

A great god, and SQL statements should be how to write? Not really, pursuing big brand me!!!!!!!
CodePudding user response:
select level01
The from score01
The where (select score from student01 where sname='* *') between minscore and
Maxscore
CodePudding user response:
The
reference 3 floor jdsnhan response:

select level01
The from score01
The where (select score from student01 where sname='* *') between minscore and
Maxscore

Honey, you is written in the database?
I tried, no!
CodePudding user response:
reference 4 floor SuperHurley response:

Quote: reference jdsnhan reply: 3/f

select level01
The from score01
The where (select score from student01 where sname='* *') between minscore and
Maxscore

Honey, you is written in the database?
I tried, no!

The select level01 from score01, student01
The where (student01. Score> Minscore and student01. ScoreAnd student01. Sname='* *';
This can be found out, but the result is two records! Who can optimize a minute!
CodePudding user response:
reference 5 floor SuperHurley reply:

Quote: refer to 4th floor SuperHurley response:

Quote: refer to the third floor jdsnhan response:

select level01
The from score01
The where (select score from student01 where sname='* *') between minscore and
Maxscore

Honey, you is written in the database?
I tried, no!

The select level01 from score01, student01
The where (student01. Score> Minscore and student01. ScoreAnd student01. Sname='* *';
This can be found out, but the result is two records! Who can optimize a minute!

I performed, only a data query: good
CodePudding user response:
The select level01 from score01, student01
Where student01. Score>=minscore and student01. ScoreAnd student01. Sname='* *';
CodePudding user response:
refer to 6th floor qq646748739 response:

Quote: refer to the fifth floor SuperHurley reply:

Quote: refer to 4th floor SuperHurley response:

Quote: refer to the third floor jdsnhan response:

select level01
The from score01
The where (select score from student01 where sname='* *') between minscore and
Maxscore

Honey, you is written in the database?
I tried, no!

The select level01 from score01, student01
The where (student01. Score> Minscore and student01. ScoreAnd student01. Sname='* *';
This can be found out, but the result is two records! Who can optimize a minute!

I performed, only a data query: good

As shown in figure, I'll find the two
However, can be added distinct before level01, return the result is a
CodePudding user response:
Select
(select sc. Level01 from score01 sc where sc. Minscore & lt;=score and sc. Maxscore & gt; Score)
The from class01 c1, student01 s1 where c1. Classno=s1. Classno and s1. The sname='* *';
CodePudding user response:

nullnullnullnullnullnullnullnullnullnull