Basic knowledge of database exercises

Select question

1, to ensure that the database of logical data independence, need to modify is | | a| |.

A: Mapping between mode and outer mode

B: Mapping between pattern and inner mode

C: Mode

D: Level Three mode

2. Data independence of database system is embodied in | | b| |.

A: The application will not be affected by changes in the data

B: The application is not affected by changes in the data storage structure and the logical structure of the data

C: Storage structure is not affected by changes in storage policies

D: does not affect other storage structures due to changes in some storage structures

3, the relational data model is the most important data model at present, its three elements are | | b| |.

A: Entity integrity, referential integrity, user-defined integrity

B: Data structure, relational operations, integrity constraints

C: Data increase, data modification, data query

D: External mode, mode, internal mode

4, | | b| | Access path is transparent to the user, resulting in higher data independence, better security confidentiality, and simplifies the work of programmers and the creation of database development.

A: Mesh Model

B: Relational model

C: Hierarchical model

D: All of the above

5, to ensure the data independence of the database, need to modify is | | c| | 。

A: Mode and external mode

B: Mode and internal mode

C: Two-layer mapping between level three modes

D: three-layer mode

6, the conceptual model is the first layer of abstraction in the real world, the most famous model of this kind of model is | | d| | 。

A: Hierarchical model

B: Relational model

C: Mesh Model

D: Entity-Relationship Model

7, the following | | d| | is not the responsibility of the DBA database administrator.

A: Completeness constraint description

B: Define the database schema

C: Database Security

D: Database management system Design

8. The following list of the three stages of the development of database management technology, there is no special software to manage the data is | | d| |. I. Human Management Phase Ii. File system phase III. Database phase

A:I and II

B: Only II

C:ii and III

D: Only I

9. The relationship between database (DB), database System (DBS) and database management system (DBMS) is | | a| |.

A:dbs includes DB and DBMS

B:dbms includes DB and DBS

C:DB includes DBS and DBMS

D:dbs includes DB, which is the DBMS

10, in the development process of data management technology, has experienced the artificial management stage, the file system stage and the database system stage. In these phases, the highest data independence is | | a| | Stage.

A: Database System

B: File system

C: Manual Management

D: Data Item Management

11. The conceptual model of the database is independent of | | a| |.

A: Specific machines and DBMS

B:e-r Chart

C: The Information World

D: The Real World

12, in the database technology, the object-oriented data model is a kind of | | a| |.

A: Conceptual model

B: Structural model

C: Physical model

D: Image model

13. The data model is used to represent the relationships between entities, but different database management systems support different data models. In a common data model, do not include | | b| |.

A: Mesh Model

B: Chain-like model

C: Hierarchical model

D: Relational model

14. The DBMS provides SQL in two ways, one of which is to embed SQL into a high-level language called | | b| |.

A: Query Language

B: Host language

C: self-contained language

D: Conversational Language

15, the image relationship between the database level three mode is correct | | b| |.

A: External mode/Internal mode

B: External mode/mode

C: External mode/External mode

D: Mode/mode

16, database technology using hierarchical method to divide the structure of the database into multiple levels, is to improve the database | | b| | 。

A: Data Normalization

B: Independence of data

C: Regulatory Compliance

D: Sharing of data

17. A data collection of structural changes stored on the computer's external storage media, whose English name is | | c| |.

A:data Dictionary (abbreviated DD)

B:data Base System (abbreviated DBS)

C:data Base (abbreviated DB)

D:data Base Management System (abbreviated DBMS)

18, for the database system, responsible for the definition of database content, determine the storage structure and access policy and security authorization work is | | c| |.

A: Application developers

B: End user

C: Database administrator

D: Software designers for database management systems

19, in the database, the physical independence of the data refers to | | c| |.

A: The mutual independence of database and data management system

B: Mutual independence of user program and DBMS

C: The user's application and data stored on disk are independent of each other

D: The logical structure of applications and data is independent of each other

20. The model that represents and implements the relationship between entities through pointer links is | | d| |.

A: Relational model

B: Hierarchical model

C: Mesh Model

D: Hierarchical and mesh models

21. Relational Data Model | | d| |.

A: can only represent 1:1 connections between entities

B: can only represent 1:n connections between entities

C: can only represent m:n connections between entities

D: Can represent the above three relationships between entities

22, in general, a database system outside the model | | d| |.

A: There can only be one

B: There can be at most one

C: Minimum of two

D: There can be multiple

23, the database three-level mode, the real existence is | | d| |.

A: External mode

B: Sub-mode

C: Mode

D: Internal mode

24, in the database, the root cause of inconsistent data is | | d| |.

A: The amount of data storage is too large

B: no strict data protection

C: No control over the integrity of the data

D: Data redundancy

25. The dividing principle of hierarchical mode, network model and relationship model is | | d| |.

A: Record length

B: Size of File

C: The degree of complexity of the connection

D: The link between data

26, the database management methods have | | d| |.

A: Batch processing and file system

B: File systems and distributed systems

C: Distributed System and batch processing

D: Database systems and file systems

27, in the database design with a relational model to represent the relationship between entities and entities. The structure of the relational model is | | b| |.

A: Hierarchical structure

B: two-dimensional table structure

C: Network structure

D: Package Structure

28, the main purpose of the application database is to | | c| |.

A: Addressing confidentiality issues

B: Resolve Database Integrity issues

C: Shared Data issues

D: Solving the biggest problem of data

29, Mode and internal mode | | a| |.

A: There can only be one

B: There is at most one

C: Minimum of two

D: There can be multiple

30, in the database of non-relational model, the basic level of contact is | | b| |.

A: Two record types and many-to-many links between them

B: Two record types and one-to-many links between them

C: Many-to-many links between two record types

D: A one-to-many connection between two records

31. One of the founders of database technology e.f.codd has published many papers since 1970, mainly on | | c| |.

A: Hierarchical Data Model

B: Mesh Data Model

C: Relational Data Model

D: Object-oriented data model

32, in the database technology, in order to improve the logical independence and physical independence of the database, the structure of the database is divided into user-level, | | c| | and three levels of storage level

A: Administrator Level

B: External level

C: Conceptual Level

D: Internal level

33, the largest feature of the database system is | | c| |.

A: Level Three abstraction and level two independence of data

B: Data sharing

C: Structuring of the data

D: Data Independence

34. The language used to define and describe the logical structure of a database in a database management system is called | | a| |.

A: Data Definition language

B: Database sub-language

C: Data Manipulation language

D: Data Structure language

35, in the relational database design with | | c| | To represent the relationship between entities and entities.

A: Tree structure

B: Package Structure

C: Two-dimensional table structure

D: Graph structure

36, the following options are not the basic characteristics of the relational database is | | a| |.

A: Different columns should have different data types

B: Different columns should have different column names

C: Independent of the Order of the rows

D: Independent of the Order of the columns

37, the following relationship: patient (patient number, patient name, gender, date of birth, unit) medical (patient number, doctor number, doctor's name, diagnosis date, diagnostic results) among them, the external code in the medical relationship is | | a| |.

A: Patient Number

B: Patient Name

C: Patient number and patient name

D: Doctor number and patient number

38, false with relations R and S, relational algebraic expression R (r-s) is represented by | | a| |.

A:r∩s

B:r∪s

C:r-s

D:rxs

39, the connection operation in the relational algebra is by | | b| | Combination of operations.

A: Selection and projection

B: Selection and Cartesian product

C: Projection, selection, Cartesian product

D: Projection and Cartesian product

40, the relationship model, a code is | | c| |.

A: Can consist of multiple arbitrary attributes

B: up to one attribute

C: Consists of one or more attributes whose value uniquely identifies a tuple in the relationship

D: None of the above

41. Relational algebra operations are based on | | c| | -based operations.

A: Relational operations

B: Predicate calculus

C: Set operation

D: Algebraic operations

42. Remove all records from a database file that satisfy a certain condition the operation of forming a new database file is | | c| | Operation.

A: Projection

B: Connect

C: Select

D: Copy

43, in general, when the relationship R and S natural connection, the requirement R and S contains one or more common | | c| | 。

A: Record

B: Yes

C: Properties

D: Tuples

44, a relationship only one | | d| | 。

A: Candidate Code

B: External code

C: Super Code

D: Main code

45, two relationships when there is no public attribute, its natural join operation behaves as | | b| |.

A: The result is an empty relationship

B: Cartesian product operation

C: Equivalent connection operation

D: Meaningless operation

46, there are two relations R and S, respectively, contains 15 and 10 tuples, then in R∪s, R-s, R∩s, the number of tuples is not likely to appear in the case is | | b| |.

a:15,5,10

b:18,7,7

c:21,11,4

d:25,15,0

47, take out some columns in the relationship, and eliminate the repetition of the relationship between the tuple algebra operation called | | b| |.

A: Take A column operation

B: Projection operation

C: Connection operation

D: Select operation

48, participate in the difference between the two relations of operation | | b| |.

A: The number of attributes can be different

B: The number of attributes must be the same

C: A relationship contains a property of another relationship

D: Property names must be the same

49, if d1={a1,a2,a3},d2={1,2,3}, then a total of tuples in the D1xd2 collection | | c| | A.

A:6

B:8

C:9

D:12

50, with attribute a,b,c,d, the following expression is not the relationship is | | c| |.

A:r (A)

B:r (A,B,C,D)

C:r (AXBXCXD)

D:r (A, B)

51. The wrong description of the relationship model is | | d| | 。

A: Based on a rigorous mathematical theory, set theory and predicate calculus Formula One of the basis

B: The majority of microcomputer DBMS take relational data model

C: Using two-dimensional table to express the relationship model is one of its major features

D: A DBMS that does not have a connection operation can also be a relational database management system

52, the code in the relational database refers to | | d| |.

A: The only field that can determine the relationship

B: Non-changing special reserved words

C: Key Important fields

D: a property or attribute collection that uniquely identifies a tuple

53, natural connection is an effective way to form a new relationship. In general, when using a natural connection to the relationship R and S, it is required that r and s contain one or more common | | d| |.

A: Tuples

B: Yes

C: Record

D: Attribute

54, set W for R and S natural connection after the result, and W,r and s the number of tuples is p,m,n, then the three meet | | d| |.

a:p< (M+n)

B:p≤ (M+n)

c:p< (MXN)

D:p≤ (MXN)

55. Create a table with the following SQL statement student:create table student (NO char (4) Not null,name char (8) Not null,sex char (2), age INT) That can be inserted into the student table is | | b| |.

A: (' 1031 ', ' Zeng Hua ', male, 23)

B: (' 1031 ', ' Zeng Hua ', null,null)

C: (NULL, ' Zeng Hua ', ' Male ', ' 23 ')

D: (' 1031 ', NULL, ' Male ', 23)

56. The operation that cannot be done on the view is | | c| | 。

A: Update View

B: Query

C: Define a new table on the view

D: Define a new view on the view

57, SQL language Set data query, data manipulation, data definition and data control functions in one, where the CREATE, DROP, ALTER statement is the implementation of what kind of function | | c| |.

A: Data Query

B: Data manipulation

C: Data definition

D: Data Control

58. In the SQL language, the command to delete a view is | | b| |.

A:delete

B:drop

C:clear

D:remove

59.

Views in SQL language view is database | | a| | 。

A: External mode

B: Mode

C: Internal mode

D: Storage mode

60. In the following SQL statement, | | d| | is not a data definition statement.

A:create TABLE

B:drop VIEW

C:create VIEW

D:grant

61. To revoke a table s that already exists in the database, available | | c| |.

A:delete TABLE S

B:delete S

C:drop TABLE S

D:drop S

62. To add a list of CNAME (course name) in basic table S, available | | c| |.

A:add TABLE S (CName CHAR (8))

B:add TABLE S ALTER (CName CHAR (8))

C:alter TABLE S ADD (CName CHAR (8))

D:alter TABLE S (ADD CName CHAR (8))

63, Student Relations mode Student (sno,sname,sex,age), the attributes of Student respectively indicate student's number, name, gender, age. To delete an attribute "age" in table student, the optional SQL statement is | | b| |.

A:delete age from S

B:alter TABLE S DROP Age

C:update S Age

D:alter TABLE S ' age '

64, has the relation s (sno,sname,sage), C (Cno,cname), SC (Sno,cno,grade). Sno is the student number, sname is the student's name, SAGE is the student's age, CNO is the course number, and the CNAME is the course name. The SQL statement to query the names of all students who are not less than 20 of the age of the elective "ACCESS" is the select SNAME from S,C,SC WHERE clause. The content of the WHERE clause here is | | a| |.

A:s.sno = Sc.sno and C.cno = SC. Cno and Sage>=20 and Cname= ' ACCESS '

B:s.sno = Sc.sno and C.cno = SC. Cno and SAGE in>=20 and CNAME in ' ACCESS '

C:sage in>=20 and CNAME in ' ACCESS '

D:sage>=20 and Cname= ' ACCESS '

65, the structure of a table s in the relational database is SC (sname,cname,grade), wherein SName is the student name, the CName is the course name, both are character type, grade is the result, the numerical type, the value range 0-100. To insert the "Zhang Yi chemistry score of 80" into s, you can use | | d| |.

A:add into SC VALUES (' Zhang Yi ', ' chemistry ', ' 80 ')

B:insert into SC VALUES (' Zhang Yi ', ' chemistry ', ' 80 ')

C:add into SC VALUES (' Zhang Yi ', ' chemistry ', 80)

D:insert into SC VALUES (' Zhang Yi ', ' chemistry ', 80)

66, the structure of a table in the relational database is: SC (Sname,cname,grade), wherein SName is the student name, Cnamee is the course name, both are character type, grade is the result, the numerical type, takes the value range 0-100. To correct a chemical score of 85 for Wang Er, then available | | a| | 。

A:update SC SET grade=85 WHERE sname= ' King II ' and Cname= ' chemistry '

B:update SC SET grade= ' "WHERE sname= ' King II ' and Cname= ' chemistry '

C:update grade=85 WHERE sname= ' King II ' and Cname= ' chemistry '

D:update grade= ' "WHERE sname= ' King II ' and Cname= ' chemistry '

67. In the SQL language, subqueries are | | d| | 。

A: The query language that returns a subset of the data in a single table

B: Select a query statement for a subset of the fields in multiple tables

C: Select a query statement for a subset of the fields in a single table

D: Query statement embedded in another query statement

68. The view mechanism in SQL improves the database system's | | d| |.

A: Completeness

B: Concurrency control

C: Audit Technology

D: Security

69. The SQL language grant and REVOKE statements are primarily used to maintain the database's | | c| |.

A: Completeness

B: Reliability

C: Security

D: Consistency

70, in the security control of the database, the authorized data object's | | a| |, the more flexible the authorization subsystem is.

A: the smaller the range

B: The more detailed the constraint

C: Security

D: Large constraint Range

SQL Design

Open and run the X:/xsgl.sql file to create the XSGL database.

Try to complete the following query, stored as the title number. SQL, such as: A.sql, B.sql ... J.sql.

A. Check the age of the 19-year-old "Liu" surname student.

B. Inquire about all the courses and achievements of "Li Yong", showing the result as name, course name and achievement. (implemented with a connection)

C. Check the course name of all courses offered by "Li Yong". (implemented with nested queries)

D. Inquire and "Liu Chen" The information of other classmates who are studying in the same department.

E. Query the number of students in each department, showing the result as a department name and number of people.

F. Query the number of courses that are elective for more than 3 students.

G. Check the names of students with average score above 80 (inclusive).

H. Check the names of students who have at least 95001 of the courses selected.

I. The "3" course has been increased by 10% for female students.

J. Delete the course selection information that has not yet been achieved. (Results are empty)

SQL Design • Reference answer:

--a. Search for "Liu" students aged 19

SELECT * FROM student where sage=19 and sname like ' Liu '

--b. Enquiries on all courses and achievements of "Li Yong", showing the result as name, course name and grade. (implemented with a connection)

Select Sname,cname,grade

From STUDENT,COURSE,SC

where Student.sno=sc.sno and Course.cno=sc.cno and Sname= ' Li Yong '

--c. Check the course name of all courses offered by "Li Yong". (implemented with nested queries)

Select CNAME from Course

where CNO in

(select CNO from SC where Sno in

(select Sno from student where Sname= ' Li Yong ')

)

--d. Queries and "Liu Chen" information about other students in the same department of Learning.

SELECT * FROM Student

where sdept=

(select Sdept from student where Sname= ' Liu Chen ') and Sname<> ' Liu Chen '

--e. Query the number of students in each department, showing the result as a department name and number of people.

Select Sdept as System name, COUNT (*) as number of people

From student

GROUP BY Sdept

--f. Query the number of courses in which more than 3 students are enrolled.

Select Sno from SC

GROUP BY SNO have Count (*) >=3

--g. Check the names of students with average scores above 80 (inclusive).

Select Sname from Student

Where Sno in

(Select Sno from SC

GROUP BY Sno have AVG (Grade) >=80)

--h. Query the student's information for at least 95001 of all courses selected.

Select Sname

From student

Where not Exists

(SELECT * FROM SC Sc1

Where Sno in (' 95001 ') and not Exists

(SELECT * from SC where Sc.sno=student.sno and Sc1.cno=sc.cno))

--------to increase the female classmate's "3" course performance by 10%.

Update SC Set grade=grade*1.1

where cno= ' 3 ' and Sno in (select Sno from student where ssex= ' women ')

--j. Delete course selection information that has not yet been scored. (Results are empty)

Delete from SC where Grade is NULL

Basic knowledge of database exercises