database normalization exercises

Label:1. Query the sname, Ssex, and Sclass columns of all records in the student table. Select Sname,ssex,sclass from Studeng 2, the inquiry teacher all units namely does not duplicate depart column. 3. Query all records of student table. SELECT * FROM Studeng 4. Query all records from 60 to 80 of the scores in the score table. SELECT * from score where degree >= and degree 5. Check the record of 85, 86 or 88 in the score table. SELECT * FROM score where degree = degree = or degree = 88 6

=A.cno) The Sno, Sname, and sbirthday of all students who were born in the same year as the student number 108. SelectSno,sname,sbirthday fromStudentwhere Year(Sbirthday)=(Select Year(Sbirthday) fromStudentwhereSno='108') The names of teachers who have more than 5 students in a given course. SelectTname fromTeacherwhereTnoinch(SelectTno fromCoursewhereCnoinch(SelectCno fromScoreGroup byCno having Count(*)>5) Query The Tname and prof of teachers with different titles of "computer department" a

I. A database comprising four tables: Student table (Student), course Schedule (Course), score table (score) and teacher Information sheet (Teacher). The structure of the four tables, as shown in table 1-1 (i) to table (iv), is shown in table 1-2 table (i) ~ table (iv). Create four tables with SQL statements and complete related topics.Table 1-1 table Structure of the databaseTable (i) Student (student table) Field name Data type

5. Check the record of 85, 86 or 88 in the score table.Select * from where inch (+,+)6, inquires the student table "95031" class or the sex is "the female" the classmate record.Select * from where class='95031'or=' female '7. Query the student table for all records in descending order of class.Select * from Order by desc8, in CNO Ascending, Degree descending query score all records of the table.Select * from Order by desc9. Check the number of students in "95031" class.Select Count (1from

(select Sal from EMP where deptno=10); MYSQL does not supportMysql specification, modify or delete records in a table, do not allow querying of the same table in subqueriesERROR 1093 (HY000): You can ' t specify target table ' EMP ' for update in FROM clauseSolution: Temp TableDelete from emp where deptno=10 and Sal >= all (select T.sal from (select Sal from emp where deptno=10) t);35. Lower the salary of the highest-paid employee by 30%.Analysis: Update emp Set sal = sal*0.7 where Sal = (selec

50 hours per year S1Select Gradeid as grade number,count as course number from Subjectwhere classhour>Group by Gradeid--02. Query the average age of each grade student:Select as grade number,avg(DATEDIFF(yy,birthday,GETDATE as average age from studentGroup by Gradeid--03. Query the total number of students in each grade including Beijing (like '% Beijing ')Select as grade number,COUNT(1 as total number from studentwhere like ' % BEIJING% ' Group by Gradeid--04. Check the average number

Recently downloaded a set of Oracle database exercises (no answer), I practice the answer on Oracle, if the wrong place, please do not take it amiss. Save to the blog to facilitate their own later lookup. Related tables: /* Student table /CREATE TABLE Student ( Sno varchar2 () primary key not NULL, sname varchar2 (), Sage Number (2), ssex varchar2 (5) )/ * Teacher Table/ CREATE TABLE Teac

operation:Back Time-Front timeDate subtraction The default result is the difference in daysMonths_between (Back Time-front time)Months_between default return result is the number of months differenceB. Value of date takenExtract functionExtract (year from Sysdate)3. Paging QueryRownum:oracle-specific keywords for pagingA. Nested implementation paging QueryPaged queries are nested with two queries, internal queries are used to specify upper bounds, and result sets return rownum, and external que

, change the value of variables to change the results of SQL statement executionb Using bound variables, you can reduce the parsing of SQL statements and reduce the resources consumed by the database engine on SQL statement parsing.C uses bound variables to improve programming efficiency and reliability, reducing the number of accesses to the databaseD using bound variables, the query optimizer will estimate more real than the literal variablesParsing

=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8C/48/wKiom1hnzuPQQz5eAAHkbmJaH_M121.png-wh_500x0-wm_3 -wmp_4-s_3996861951.png "title=" 2016-12-31_232841.png "alt=" Wkiom1hnzupqqz5eaahkbmjah_m121.png-wh_50 "/>Note: the preg_match function is used for regular judgment if (!preg_match ($repat, $pass))The echo "password format is incorrect"else if ($pass! = $zaicipass) Determine if the password is consistent two times, if inconsistentecho "Two times password Inconsistent"The Header,die function works a

=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8C/48/wKiom1hnzuPQQz5eAAHkbmJaH_M121.png-wh_500x0-wm_3 -wmp_4-s_3996861951.png "title=" 2016-12-31_232841.png "alt=" Wkiom1hnzupqqz5eaahkbmjah_m121.png-wh_50 "/>Note: the preg_match function is used for regular judgment if (!preg_match ($repat, $pass))The echo "password format is incorrect"else if ($pass! = $zaicipass) Determine if the password is consistent two times, if inconsistentecho "Two times password Inconsistent"The Header,die function works a

105 3-245 75 109 3-245 68 103 3-105 92 105 3-105 88 109 3-105 76 101 3-105 64 107 3-105 91 108 3-105 78 101 6-166 85 107 6-166 79 108 6-166 81 Table (iv) Teacher Tno Tname Tsex Tbi

waiting, go to MySQL to verify it.Verify success!2.Mysql> CREATE table if not exists upwd (uid Int (ten) primary key, Upwd varchar (+), email varchar (40));Import datamysql> INSERT INTO Upwd (UID, upwd, e-mail) select Uid,paw,email from upwd_12306;Query OK, 131653 rows affected (0.52 sec)records:131653 duplicates:0 warnings:03. mysql> SELECT * from upwd where email like ' 4145965 ';4. mysql> CREATE VIEW upwd_90 as SELECT * from upwd_12306 where substring (gmsfhm,7,4) >=1990;Query OK, 0 rows Aff

); --Commit statement 2. Query statementsThe first step is to get the following result:Teacherid MONDAY Tuesday Wednesday Thursday FRIDAY--------- ---------- ---------- ---------- ---------- ----------1 0 1 0 0 01 0 0 1 0 02 1 0 0 0 03 0 1 0 0 01 0 1 0 0 0The query statements are: Select Teacherid, case if Weekday=1 then 1 else 0 end MONDAY, case when weekday=2 then 1 Else 0 end Tuesday, case while weekday=3 then 1 else 0 end Wednesday, case when weekday=4 then 1 Else 0 End thursday,In the

elective number is "3-105" course and the result is at least higher than the elective number "3-245" classmate's CNO, Sno and degree, and according to degree from high to low order.30, inquires the elective number is "3-105" and the result is higher than the elective number is "3-245" the course schoolmate's CNO, Sno and degree.31. Check the name, sex and birthday of all teachers and classmates.32, query All "female" teacher and "female" classmate's name, Sex and birthday.33. Check the scores o

, inquiries and "Li June" with the gender of all students sname.Select sname from student where ssex= (select Ssex from student where Sname= ' Li June ')44, inquiries and "Li June" with the same sex and classmates sname.Select sname from student where ssex= (select Ssex from student where Sname= ' Li June ') class= (select class from Studen t where Sname= ' Li June ')45. Check the scores of all the "male" students who have enrolled in the "Introduction to Computer" course.SELECT * FROM score wh

= s2.id and s2.grade_id = S3.id GROUP BY s3.id ;3, the structure of the table is as follows:Books (book ISBN, book name, author number, publishing house, publication date);Author (author name, author number, age, gender);Requirements: use SQL to query the author's name, book name, and publisher of the age less than the average age. Analysis: The tables used are books and authors two tablesMethods: After combining the two tables, we found the information about AVG (age) less than the average age

\U8BDD"); Box1.add (lblnewlabel_2); Lblnewlabel_3=NewJLabel ("\u5355\u51fb\u5f55\u5165"); Box1.add (Lblnewlabel_3); Basebox.add (BOX2); Text1=NewJTextField (); Box2.add (Text1); Text1.setcolumns (10); Text2=NewJTextField (); Box2.add (TEXT2); Text2.setcolumns (10); Text3=NewJTextField (); Box2.add (TEXT3); Text3.setcolumns (10); Button=NewJButton ("\u5f55\u5165"); Box2.add (button); Add (Basebox); Button.addactionl

sname. S.sname Select student s where S.ssex in (select Ssex from student where ssex= ' man ') 44, inquiries and "Li June" with the same sex and classmates sname. S.sname-Select-Student s where S.ssex in (select Ssex from student where ssex= ' man ') and S.class in(select class from student where Sname= ' Li June ') 45. Check the scores of all the "male" students who have enrolled in the "Introduction to Computer" course. S.* from score s inner joins course C on c.cno=s.cno inner joins stude

"95033" class students. Select AVG (degree) from student A,score b where a.class= ' 95033 ' and B.sno=a.sno 20, the inquiry score to choose to learn many courses of the students score is the record of non-highest scores. Select T.sno from Score t where t.degree 21. The result of the inquiry is higher than the record of "109" and the grade of the course number "3-105". SELECT * FROM score where cno= ' 3-105 ' and degree> (select Max (degree) from score where Sno = ' 109 ') 22. The SNO, sname