[DB foundation] (10)
Choose questions, from the following topics, the topic is worth 10 points.
Sybase
A. (5) Software's work is backed up in the backup database, but also backs up the Master database, why?
B. (5) Sybase's View creation?
Oracle
A. (5) Interpret the difference between archiving and non-archive mode and their respective advantages and disadvantages
B. (5) Two results interactions and interacting functions
Integrate
A. (10) Take a table of various databases of N-recorded records ... at least three ways.
[SQL common sense] (30)
A. (5) Compare Truncate and Delete:
B. (5) Compare Union and UNION ALLs, please exemplify (requires the result set)
C. (5) Compare the usage of Exists and In (Not in), please exemplify (requirement to write the result set)
D. (5) Compare the difference between Between and> And <, please exemplify (requirement to write the result set)
E. (9) Compare natural connection and external connections and designs of Cartesi, please exemplify (requires the result set)
[SQL Comprehensive App] (40)
The database used is not limited, but the database type used in the answer is required.
The following topics require SQL itself (requirement to consider coding level optimization)
It is best to have SQL, (the score rate is 100%)
If you can't use a SQL implementation, you can use multiple SQL implementations, (the score rate is 80%)
It is also possible to implement the stored procedure (the score rate is 70%).
In addition to writing the corresponding encoding, the answering questions are required to give a text description of the idea and implement steps.
A. (6) Squaining operation
The surface structure is as follows:
Serial number, total amount, amount 1, department
Record: 1 10 1
2 10 1
3 10 1
4 10 2
5 10 2
Now I want to sum up the total amount according to the department.
The result is:
Serial number, total amount, amount 1, department
Record: 1 30 10 1
2 30 10 1
3 30 10 1
4 20 10 2
5 20 10 2
B. (6) Field maximum
There are three fields, T1, T2, T3, how to return a field, the value is the maximum value of these three fields?
Requirements: Description of the topic, establish corresponding test data, write SQL, to get SQL result set
C. (6) Repeat of retrieval
How do I inspect a row in a table in a Table? (A column or multiple columns repeat)
Requirements: Description of the topic, establish corresponding test data, write SQL, to get SQL result set
D. (8) Requirements
There is a table: S # (student number), C # (course number), grade (grade)
Please write a school number of students who have learned the courses of students who have learned from 001.
Requirements: Description of the topic, establish corresponding test data, write SQL, to get SQL result set
E. (14) Outer connection application
There are two tables B1, B2, of which B1 has a list (ID1 Number (10)), and B2 has a list (ID2 Number (10)). Now you need to take the ID1, ID2, put it in Table B3, B3 For (B1ID Number (10), B2ID Number (10)), put the ID1 in B1ID, ID2 is placed in B2ID. In addition, there is no intrinsic connection.
How to implement it with the SQL statement.
Table B1 table B2 table B3
ID1 ID2 B1ID, B2ID
1 2 1 2
2 3 2 3
3 1 3 1
4 6 4 6
5 9 5 9
5 NULL 5
8 NULL 8
4 NULL 4
---------------------------------------------- ABC customers Description of the analysis: (1) Align customers in order of performance size.
(2) Accumulate the amount of all customers.
(3) The cumulative total amount is called a Class A customer within 55%.
At 55% - 85% of customers called B customers.
At 85% -100% of customers called Class C customers.
