SQL language (written review materials)

I. multiple choice questions

1. SQL is a () language that is easy to learn.

A. Procedural B. Non-procedural

C. Format D. Navigation

2. Data manipulation statements in SQL include select, insert, update, and delete. The most important and frequently used statement is ().

A. Select B. insert

C. Update D. Delete

3. The operations that cannot be completed on the view are ().

A. Update View B. Query

C. define a new table in the view. D. define a new view in the view.

4. the SQL language integrates data query, data manipulation, data definition, and data control functions. Which of the following functions is implemented by the CREATE, drop, and alter statements ().

A. Data Query B. data manipulation

C. Data Definition D. Data Control

5. In SQL language, the command for deleting a view is ().

A. Delete

B. Drop

C. Clear

D. Remove

6. The view in SQL is a database ().

A. External mode B. Mode C. Internal Mode D. Storage Mode

7. In the following SQL statements, () is not a Data Definition Statement.

A. Create Table B. Drop View

C. Create view D. Grant

8. to cancel the existing table s in the database, use ().

A. Delete table s B. Delete s

C. Drop table s d. Drop s

9. To add a CN (Course name) column to basic table S, use ().

A. Add Table S (CN char (8 ))

B. Add Table s alter (CN char (8 ))

C. alter table s add (CN char (8 ))

D. alter table s (add CN char (8 ))

10. The Student Relationship Model S (s #, sname, Sex, Age) and S attributes indicate the student's student ID, name, gender, and age. To delete an attribute "Age" in Table S, the optional SQL statement is ().

A. Delete age from S

B. ALTER TABLE s drop age

C. Update s age

D. alter table s 'age'

11. S (s #, sname, SAGE), C (C #, cname), SC (s #, C #, grade) are related ). S # indicates the student ID, sname indicates the Student name, sage indicates the student age, C # indicates the course number, and cname indicates the course name. The SQL statement used to query the names of all students whose age is not less than 20 is select sname from S, C, SC where clause. The content of the WHERE clause is ().

A. s.s # = SC. s # and C. C # = SC. C # And Sage> = 20 and cname = 'access'

B. s.s # = SC. s # and C. C # = SC. C # And Sage in> = 20 and cname in 'access'

C. Sage in> = 20 and cname in 'access'

D. Sage> = 20 and cname = 'access'

12. set the structure of a table s in a relational database to S (Sn, CN, grade). Sn indicates the Student name, CN indicates the course name, and both are score type. Grade indicates the score and numeric type, value Range: 0-100. If you want to insert "Zhang 'er's chemistry score 80 points" into S, you can use ().

A. Add

Into S

Values ('zhang 'er ', 'chemistry', '80 ')

B. insert

Into S

Values ('zhang 'er ', 'chemistry', '80 ')

C. Add

Into S

Values ('zhang 'er ', 'chemistry', 80)

D. insert

Into S

Values ('zhang 'er ', 'chemistry', 80)

13. set the structure of a table s in a relational database to S (Sn, CN, grade). Sn indicates the Student name, CN indicates the course name, and both are in the textbook type. Grade indicates the score, value Range: 0-100. To correct Wang's chemistry score of 85, you can use ().

A. Update s

Set grade = 85

Where Sn = 'wang 2' and Cn = 'Chemical'

B. Update s

Set grade = '85'

Where Sn = 'wang 2' and Cn = 'Chemical'

C. Update grade = 85

Where Sn = 'wang 2' and Cn = 'Chemical'

D. Update grade = '85'

Where Sn = 'wang 2' and Cn = 'Chemical'

14. In SQL, The subquery is ().

A. Return the query language of the Data subset in a single table.

B. Select the query statement for the field subsets in multiple tables

C. Select the query statement for the Field Subset in a single table

D. query statement embedded in another query statement

15. SQL is a () language.

A. AdvancedAlgorithmB. Artificial Intelligence

C. Relational Database D. Functional

16. S (s #, sname, sex), C (C #, cname), SC (s #, C #, grade) are related ). S # indicates the student ID, sname indicates the Student name, sex indicates the gender, C # indicates the course number, and cname indicates the course name. The SQL statement used to query the names of all boys enrolled in the "Database" course is the select sname from S, C, SC where clause. The content of the WHERE clause is ().

A.s. s # = SC. s # and C. C # = SC. C # And sex = 'male' and cname = 'database'

B .s. s # = SC. s # and C. C # = SC. C # and sex in 'male' and cname in 'database'

C. Sex 'male' and cname' database'

D. S. Sex = 'male' and cname = 'database'

17. If the following SQL statement is used to create a table SC:

Create Table SC (s # char (6) Not null, C # char (3) not null, score integer, note char (20); Insert the following row to the SC table, () rows can be inserted.

A. ('000000', '000000', 60, required)

B. ('20140901', '20140901', null, null)

C. (null, '123', 80, 'electives ')

D. ('123', null, 86 ,'')

18. Assume that student relation S (s #, sname, sex), course relation C (C #, cname), and student elective relation SC (s #, C #, grade ). To query the names of boys who take the "computer" course, relationship () is involved ().

A. s B. S, SC c. C, SC d. S, C, SC

Multiple choice answer:

(1) B (2) A (3) C (4) C (5) B

(6) A (7) D (8) C (9) C (10) B

(11) A (12) D (13) A (14) D (15) c

(16) A (17) B (18) d

Ii. Short answer

1. Explain the features of the SQL language.

A:

(1) comprehensive and unified. The SQL language integrates the functions of data definition language DDL, data manipulation language DML, and data control language DCL.

(2) highly procedural. To operate data in SQL, you only need to propose "what to do" without specifying "How to Do". Therefore, you do not need to know the access path, the system automatically selects the access path and runs SQL statements.

(3) Set-oriented operations. The SQL language uses a set operation method. Not only can the operation object and search result be a set of tuples, but also an object for one insert, delete, or update operation can be a set of tuples.

(4) The same syntax structure provides two usage methods. SQL is both a self-contained language and an embedded language. As a self-contained language, it can be used independently for online interaction, and can also be embedded into advanced languages.ProgramFor programmers to design programs.

(5) The language is simple, easy to learn and use.

2. Try the definition function of SQL.

A:

SQL data definition functions include defining tables, defining views, and defining indexes.

The SQL language uses the CREATE TABLE statement Definition to create a basic table, the ALTER TABLE statement to modify the basic table definition, the drop TABLE statement to delete the basic table, and the create index statement to create an index, the drop index statement deletes the index table. The SQL language uses the create view command to create a view. The drop view statement deletes the view.

3. Use SQL statements to create four tables in chapter 3 of Exercise 3.

A:

For the s table: S (SNO, sname, status, city );

Create s table

Create Table s

(SNO char (3 ),

Sname char (10 ),

Status char (2 ),

City char (10 ));

P (PNO, pname, color, weight );

Create a p table

Create Table P

(PNO char (3 ),

Pname char (10 ),

Color char (4 ),

Weight INT );

J (jno, jname, city );

Create a j table

Create Table J

(Jno char (3 ),

Jname char (10 ),

City char (10 ));

SPJ (SNO, PNO, jno, qty );

Create a SPJ table

Create Table SPJ

(SNO char (3 ),

PNO char (3 ),

Jno char (3 ),

Qty INT );

4. Use the SQL language for the four tables created in the above question to complete the query in Chapter 1 exercise 3.

A:

(1) Request the supplier number SnO that supplies the J1 parts of the project;

Select SnO

From SPJ

Where jno = 'j1 ';

(2) request the supplier number SnO that supplies the J1 part P1 of the project;

Select SnO

From SPJ

Where jno = 'j1'

And PNO = 'p1 ';

(3) request the supplier number SnO whose J1 part is red;

Select SnO

From SPJ

Where jno = 'j1'

And PNO in

(Select PNO

From P

Where color = 'red ');

Or

Select SnO

From SPJ, P

Where jno = 'j1'

And SPJ. PNO = P. PNO

And color = 'red ';

(4) do not use the project No. jno for the red parts produced by the Tianjin supplier;

Resolution:

The SQL statement is as follows:

Select jno

From J

Where not exists

(Select *

From SPJ

Where SPJ. jno = J. jno

And SnO IN

(Select SnO

From S

Where city = 'tianjin ')

And PNO in

(Select PNO

From P

Where color = 'red '));

Or

Select jno

From J

Where not exists

(Select * 1

From SPJ, S, P

Where SPJ. jno = J. jno

And SPJ. Sno = S. SnO

And SPJ. PNO = P. PNO

And S. City = 'tianjin'

And P. Color = 'red ');

Note: Start with table J to include engineering numbers that have not used any parts.

(5) At least use the project No. jno of all parts supplied by supplier S1.

Resolution:

The SQL statement is as follows:

Select distinct jno

From SPJ spjz

Where not exists

(Select *

From SPJ spjx

Where SnO = 's1'

And not exists

(Select *

From SPJ spjy

Where spjy. PNO = spjx. PNO

And spjy. jnon = spjz. jno

And spjy. Sno = 's1 '));

And spjy. Sno = 's1 '));

5. Test the SQL language for the four tables in Exercise 3:

(1) identify the names and cities of all suppliers.

(2) identify the names, colors, and weights of all parts.

(3) Find the engineering Number of the parts supplied by supplier S1.

(4) identify the names and quantities of various parts used by the Project J2.

(5) Find all part numbers provided by the Shanghai manufacturer.

(6) Identify the engineering name of the parts produced in Shanghai.

(7) Find the engineering Number of the parts not produced in Tianjin.

(8) change the color of all red parts to blue.

(9) replace P6, which is supplied by S5 to J4, with S3. Make necessary modifications.

(10) Delete the S2 record from the supplier relationship and delete the corresponding record from the supply relationship.

(11) insert (S2, J6, P4, 200) into the supply relationship.

A:

(1) identify the names and cities of all suppliers.

Select sname, City

From S;

(2) identify the names, colors, and weights of all parts.

Select pname, color, weight

From P;

(3) Find the engineering Number of the parts supplied by supplier S1.

Select jno

From SPJ

Where SnO = 's1 ';

(4) identify the names and quantities of various parts used by the Project J2.

Select P. pname, SPJ. Qty

From P, SPJ

Where p. PNO = SPJ. PNO

And SPJ. jno = 'j1 ';

(5) Find all part numbers provided by the Shanghai manufacturer.

Select distinct PNO

From SPJ

Where SnO IN

(Select SnO

From S

Where city = 'shanghai ');

(6) Identify the engineering name of the parts produced in Shanghai.

Select jname

From J, SPJ, S

Where J. jno = SPJ. jno

And SPJ. Sno = S. SnO

And S. City = 'shanghai ';

Or

Select jname

From J

Where jno in

(Select jno

From SPJ, S

Where SPJ. Sno = S. SnO

And S. City = 'shanghai ');

(7) Find the engineering Number of the parts not produced in Tianjin.

Select jno

From J

Where not exists

(Select *

From SPJ

Where SPJ. jno = J. jno and SnO IN

(Select SnO

From S

Where city = 'tianjin '));

Or

Select jno

From J

Where not exists

(Select * 1

From SPJ, S

Where SPJ. jno = J. jno and SPJ. Sno = S. SnO and S. City = 'tianjin ');

(8) change the color of all red parts to blue.

Update P

Set color = 'lan'

Where color = 'red ';

(9) replace P6, which is supplied by S5 to J4, with S3. Make necessary modifications.

Update SPJ

Set SnO = 's3'

Where SnO = 's5 'and jno = 'j4' and PNO = 'p6 ';

(10) Delete the S2 record from the supplier relationship and delete the corresponding record from the supply relationship.

Delete

From SPJ

Where SnO = 's2 ';

Or

Delete

From S

Where SnO = 's2 ';

Resolution: note the order of deletion. You should first Delete the records of parts supplied by supplier S2 from the SPJ table, and then delete S2 from the s table.

(11) insert (S2, J6, P4, 200) into the supply relationship.

Insert into SPJ (SNO, jno, PNO, qty)

Values (S2, J6, P4, 200 );

Or

Insert into SPJ

Values (S2, P4, J6, 200 );

6. What is a basic table? What is a view? What is the difference and connection between the two?

A: A basic table exists independently. in SQL, a link corresponds to a table.

A view is a table exported from one or more basic tables. The view itself is not stored independently in the database and is a virtual table. That is, the database only stores the view definitions, but not the view data. The data is still stored in the basic table of the exported view. A view is equivalent to a basic table. You can use a view as a basic table and define a view on The View.

7. Test the advantages of the view.

A: (1) views can simplify user operations.

(2) views allow users to view the same data from multiple perspectives.

(3) views provide a certain degree of logical independence for restructured databases.

(4) The view can provide security protection for confidential data.

8. Can all views be updated? Why?

A: No. A view is a virtual table that does not actually store data. Therefore, the update of a view must be converted to an update of a basic table. Because update of some views cannot be converted to update of the corresponding basic table in a meaningful way, not all views can be updated. For example, view s_g in overview 3.5.1 (student ID and average score)

Creat view s_g (SNO, gavg)

As select SnO, AVG (grade)

From SC

Group by SnO;

To change the average score, you must modify the score of each subject. However, we cannot know which course score changes lead to the average score changes.

9. Which view can be updated and which view cannot be updated? For example.

A: The row/column subset view of a basic table is generally updatable. For example, Example 1 in introduction 3.5.3.

If the attribute of a view comes from a set function or expression, the view cannot be updated.

For example, s_g view in introduction 3.5.3.

10. Try to describe the view update rules in an actual system that you are familiar.

A: (omitted)

Resolution: different systems have different requirements for view update. Readers must understand the requirements of your system for view update.

11. Create a supply view for the three construction projects, including suppliersCode(SNO), parts

Code (PNO), supply quantity (qty ). Complete the following query for this view:

(1) identify the various part codes used by the three construction projects and their quantity.

(2) identify the supply of supplier S1.

A: Create a view:

Create view v_spj

Select SnO, PNO, Qty

From SPJ

Where jno =

(Select jno

From J

Where jname = 'three ');

Query this view:

(1) identify the various part codes used by the three construction projects and their quantity.

Select PNO, Qty

From v_spj;

(2) identify the supply of supplier S1.

Select PNO, qty/* S1 supplies the part number and quantity of the three construction projects */

From v_spj

Where SnO = 's1 ';