一份sql试题

最新推荐文章于 2021-07-29 20:43:53 发布

Awinye

最新推荐文章于 2021-07-29 20:43:53 发布

阅读量4.5k

点赞数

分类专栏： sql server 文章标签： sql spreadsheet table database null tuples

本文链接：https://blog.csdn.net/awinye/article/details/598884

版权

sql server 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

题目:

一.单项选择

With respect to a relational table, what is a key?

(a) A minimal subset of columns that uniquely identifies a row in the table
(b) The ID and password needed to access the table
(c) The subset of all the rows and columns in the table that are visible to all users in the database system
(d) The one column that uniquely identifies a row in the table

Which of the following is true about the number of primary keys and alternate keys with respect to a table?

(a) There can be many primary keys, but only one alternate key.
(b) There can be only one primary key, but many alternate keys.
(c) There can be only one primary key and only one alternate key.
(d) There can be many primary keys and many alternate keys.

An insertion operation will _____ if the insertion violates the uniqueness property of a key.

(a) succeed with warning
(b) crash the system
(c) fail
(d) succeed without warning

An insertion operation will _____ if the inserted primary key has a NULL value.

(a) succeed with warning
(b) succeed without warning
(c) fail
(d) crash the system

For two tables to be union compatible, corresponding columns from each table should have which of the following?

(a) different domains
(b) different names
(c) the same domain
(d) the same name

In the relational model, which of the following is true about the data type of a column?

(a) It need not be atomic and it cannot be an abstract data type.
(b) It must be atomic and it cannot be an abstract data type.
(c) It need not be atomic and it can be an abstract data type.
(d) It must be atomic and it can be an abstract data type.

What is an alternate key?

(a) Any key that is not a primary key
(b) The key to use when the primary key does not work
(c) A key that was added to the table after the table was designed
(d) A key that will become the primary key when the primary key is deleted

What does a projection operation do?

(a) It extends the number of columns in a table.
(b) It selects rows from a table.
(c) It extends the number of rows in a table.
(d) It selects columns from a table.

The result of a set difference operation r - s will be

(a) tuples in r after deducting their values by the corresponding values in the tuples in s
(b) tuples in s after deducting their values by the corresponding values in the tuples in r
(c) those tuples that are in s but not in r
(d) those tuples that are in r but not in s

10.

Which of the following is true about primary keys and foreign keys holding NULL value?

(a) A primary key can hold a NULL value and a foreign key cannot hold a NULL value
(b) A primary key cannot hold a NULL value and a foreign key cannot hold a NULL value
(c) A primary key can hold a NULL value and a foreign key can hold a NULL value
(d) A primary key cannot hold a NULL value and a foreign key can hold a NULL value

11.

In contrast to _____ tables, a view refers to _____.

(a) base, a virtual table
(b) empty, non-empty tables
(c) non-empty, empty tables
(d) virtual, base tables

12.

When removing a table from the schema, using the CASCADE option would

(a) remove the table and all references to it
(b) recursively remove the table and all other tables that the removed table refers to
(c) remove the table and all other tables that the specified table refers to.
(d) remove the table if there are no references to it

13.

In a transaction, COMMIT specifies that

(a) all updates (including inserts, deletes, modifications) of a transaction are about to be made permanent in the database
(b) all updates (including inserts, deletes, modifications) of a transaction are to be made permanent in the database
(c) only the inserts and deletes of a transaction are to be made permanent in the database
(d) only the inserts and deletes of a transaction are about to be made permanent in the database

14.

Which of the following is true about the physical storage of tables defined by views?

(a) Extra physical storage is needed for storing the tables defined by views, only if views define additional non-existing columns.
(b) There is no extra physical storage needed to store tables that a view defines.
(c) Extra physical storage is always needed to store tables that a view defines.
(d) Extra physical storage is needed to store tables that a view defines, only when rows are inserted into the view.

15.

Which of the following SQL commands can be used to change, add, or drop column definitions from a table?

(a) MODIFY TABLE
(b) UPDATE TABLE
(c) CHANGE TABLE
(d) ALTER TABLE

16.

Which of the following SQL commands can be used to destroy and remove a table from the schema?

(a) DROP TABLE
(b) DESTROY TABLE
(c) REMOVE TABLE
(d) DELETE TABLE

17.

In SQL, which of the following operators are used to check for set membership in a SELECT statement?

(a) MEMBER and NOT MEMBER
(b) IN and NOT IN
(c) SUBSET and NOT SUBSET
(d) COMPONENT and NOT COMPONENT

18.

In SQL, the results of a _____ statement can be used to process a _____ statement.

(a) INSERT, SELECT
(b) SELECT, INSERT
(c) INSERT, DELETE
(d) DELETE, INSERT

19.

In a transaction, a ROLLBACK is used to

(a) roll all the updates (including inserts, deletes, modifications) of an aborted transaction into the database
(b) roll only the inserts and deletes of an aborted transaction into the database
(c) discard all the updates (including inserts, deletes, modifications) of a transaction from the database
(d) discard only the inserts and updates of a transaction from the database

20.

When a string whose length is strictly less than n is entered as the value of a field whose SQL data type is CHAR(n), the system responds by

(a) padding the end of the string with spaces to length n before storing it
(b) padding the end of the string with NULL characters to length n before storing it
(c) re-prompting for the entry of a string whose length is exactly n
(d) storing the string as is

二.问答类

Database Systems Practical

Answer the following questions:

1. Let the following relational tables be given: R = (A, B, C) and S = (D, E, F) where A, B, C, D, E, and F are the attributes (columns). Write the SQL statements that will express each of the queries given below:

1. Π_A(R)

2. σ_{B = 13}(R)

3. Π_A,B(R _{C = D} S)

2. Given relation r as:

ename	project	dept
Kasper	Forecast	Accounting
Kasper	Audit	Accounting
Kasper	Spreadsheet	Admin
Mohan	Forecast	Accounting
Mohan	Audit	Admin
Mohan	Spreadsheet	Admin
Lin	Forecast	Admin
Lin	Audit	Admin
Lin	Spreadsheet	Admin

3. Given relation s as:

ename	dept
Kasper	Accounting
Mohan	Admin
Lin	Admin

4. Provide the results of the following operations. If an operation cannot be performed, state the reasons. Do not write the equivalent SQL statements.

1. σ_{dept = 'Admin' AND project <> 'Audit'}(r)

2. r U s

3. Π_dept(r) - Π_dept(s)

5. Consider the following Mail Order database:

CUSTOMER

CId	Name	Address	Zip
11	George	23 Main St .	15218
22	Barbara	3 Walnut St .	15217
33	Waquim	82 Straton Av.	15280

PART

Pno	Pname	UnitPrice
150	X	20.00
152	Y	33.00
153	Z	4.00
155	V	15.00
162	W	25.00

ORDERS

Ono	CId	Received	Shipped
20	11	10-Dec-97	12-Dec-97
21	11	13-Feb-98	15-Feb-98
22	22	26-Feb-98	NULL

INVOICE

Ono	Pno	Qty	BilledPrice
20	152	1	33.00
20	155	4	60.00
20	162	1	25.00
22	152	3	99.00
21	150	1	20.00
21	152	2	66.00
21	153	3	12.00
21	155	4	60.00
21	162	1	25.00

9. In the table ORDERS above, specify in SQL the requirements that:

1. Received date cannot be undefined and that

2. the Shipped date, if it is not NULL, should be greater than the Received date.

10. Translate in SQL the following queries. Note: The "Received" column in ORDERS table above refers to when the order is received by the system (and not when the ordered items are received by the customer).

1. Get the part number of parts that cost between 10 and 25 dollars.

2. For each part sold in 1998, list the total quantity sold in 1998. Sort your results in ascending order by the total billed price for each part for that year. Parts are considered sold when an order is received. The BilledPrice column in the Invoice table indicates the total price billed for that part (i.e., quantity sold multiplied by unit price minus discount, if any).

3. Get those parts that were not sold in 1998. A part is considered sold when an order is received.

11. Given the following schema definitions, specify in SQL2 the referential integrity constraint on the EMPLOYEE relation that will prevent an employee from being assigned to a non-existent department. 12. create table EMPLOYEE

13.   (id      id_dom primary key deferrable,

14.    name    name_dom,

15.    salary salary_dom,

16.    dname   dept_name_dom

17.    );

18.

19. create table DEPARTMENT

20.    (name   dept_name_dom primary key deferrable,

21.     mgrid id_dom        foreign key references emp(id) deferrable,

22.     budget budget_dom

23.            );

我交的答案,很可能有错,等老帅评讲后,我再给出标准答案:

/*********************PARTI*********************/

Answer for Multiple-choice:

1 ~ 5：ABACC

6 ~10：DDADC

11~15：AABCD

16~20：ABBCD

/*********************PARTII*********************/

Answer for Practical:

1)SELECT A

FROM R

2)SELECT B

FROM R

WHERE B=’ 13’

3)USE Pubs

SELECT A,B FROM R INNER JOIN S

ON R.C=S.D

1) ename project dept

Kasper Spreadsheet Admin

Mohan Spreadsheet Admin

Lin Forecast Admin

Lin Spreadsheet Admin

2) ename project dept

Kasper Forecast Accounting

Kasper Audit Accounting

Kasper Spreadsheet Admin

Mohan Forecast Accounting

Mohan Audit Admin

Mohan Spreadsheet Admin

Lin Forecast Admin

Lin Audit Admin

Lin Spreadsheet Admin

3)这里将不会返回任何值，因为语句的意思是取出列dept中属于r但不属于s的所有值，显然不存在这样的值。

1)ALTER TABLE ORDERS

ALTER COLUMN Received

datetime NOT NULL

2)ALTER TABLE ORDERS

CONSTRAINT Shipped1 UNIQUE(Shipped)

CONSTRAINT Shipped2 CHECK(Shipped = “” OR Shipped > Received)

10.

1) SELECT Pno

FROM PART

WHERE UnitPrice > 10 AND UnitPrice <25

2) SELECT Ono,Qty,BilledPrice

FROM INVOICE

WHERE Ono IN (SELECT Ono

FROM ORDERS

WHERE Received LIKE "%1998")

COMPUTE SUM(Qty)

ORDER BY BilledPrice ASC

3) SELECT Ono,Pno,Qty,BilledPrice

FROM INVOICE

WHERE Ono NOT IN (SELECT Ono

FROM ORDERS

WHERE Received LIKE "%1998"))

11.

CREATE TABLE EMPLOYEE(

id_dom NUMERIC(20) NOT NULL,

name_dom VARCHAR(40) NOT NULL,

salary_dom VARCHAR(40) NOT NULL,

dept_name_dom VARCHAR(40) NOT NULL,

PRIMARY KEY (id_dom));

CREATE TABLE DEPARTMENT(

dept_name_dom VARCHAR(40) NOT NULL,

id_dom NUMERIC(20) NOT NULL,

budget_dom VARCHAR(40) NOT NULL,

PRIMARY KEY (dept_name_dom),

FOREIGN KEY ( id_dom ) REFERENCES EMPLOYEE ( id_dom ));

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=347411

[ 点击此处收藏本文] 发表于 2005年04月14日 3:05 PM

href="http://blog.csdn.net/SoakTech/Services/Pingback.aspx" rel="pingback" /> <script>document.write(" ");</script>

DragonShard 发表于2005-04-17 12:01 PM IP: 220.167.42.*

这里的blog程序急需改进!!!

shinji1984 发表于2005-04-21 9:56 AM IP: 220.167.41.*

不如自己做一个吧.

jaye 发表于2005-07-06 12:37 AM IP: 61.186.252.*

我尝试用储存过程来建立一个表。
要求此table的名字是动态赋予的，但是因为对储存过程的不了解，请求大家的帮助
主要是
1。N'[dbo].'+@tablename 写错了不知道怎么写
2。CREATE TABLE '[dbo].'+@tablename 也不知道怎么写

CREATE PROCEDURE [dbo].copyadminproducts
@tablename nvarchar(20)
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].'+@tablename and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table '[dbo].'+@tablename
CREATE TABLE '[dbo].'+@tablename (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[productsname] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[productsid] [int] NULL ,
[creteddate] [datetime] NULL
) ON [PRIMARY]
ALTER TABLE '[dbo].'+@tablename WITH NOCHECK ADD
CONSTRAINT [PK_@tablename] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
ALTER TABLE '[dbo].'+@tablename ADD
CONSTRAINT [DF_@tablename_creteddate] DEFAULT (getdate()) FOR [creteddate],
CONSTRAINT [IX_@tablename] UNIQUE NONCLUSTERED
(
[productsid]
) ON [PRIMARY] ,
CONSTRAINT [IX_@tablename_1] UNIQUE NONCLUSTERED
(
[productsname]
) ON [PRIMARY]
GO

月如发表于2005-11-03 12:44 AM IP: 222.20.230.*

真是好啊，实事更新考题就好了