675
社区成员
发帖
与我相关
我的任务
分享fuxi
1.Degree of a relation is the number of entity types involved in the relation
One Unary
Two Binary
Three Ternary
e.g.:
Unary ? employee manager-of employee
Binary ? employee works-for department
Ternary ? Customer purchases items from a shop keeper
给你一个关系 判断为几元
2. ER图 判断某关系是
one-to-one (1:1)
one-to-many (1:N)
many-to-one (M:1)
many-to-many (M:N)
3.例给你：一个电影明星可以演多部电影，但电影里最多能出现一次这个电影明星 应建怎样的表？应该是：明星的表格里包含电影id，电影id作为一个外键出现在明星的表格里。
4.判断范式
给你一个关系和范式。让你判断该范式为几范式
5.If the value to be inserted into a cell is unknown, or it does not have any value. It is represented as NULL。NULL is not equivalent to zero, blank or empty string。此题为关于NULL判断题
6.关于foreign key .一张表可以有0个，1个或多个外键，同时外键可以为null值 在第一章page29还有page30
Foreign key
P29 The set of attributes whose values are required to match with the values of a column in the same or another table.
Points to remember
Foreign key values do not (usually) have to be unique.
Foreign keys can also be null .
To enter the data in child table corresponding data must be present in master table or NULL is the default entry in child table in the referenced column ( FK column)
p30 Points to remember
Foreign Key column values must match with the values of the corresponding Key column. This is also known as Referential constraint.
Self-Referencing table is a table which has a Foreign Key referring to its own Key.
7.判断某操作属于DDL 还是DML 还是DCL
例如：drop 属于哪个操作？
DDL (Data Definition Language)
Create
Alter
Drop
Truncate
DML (Data Manipulation Language)
Insert
Update
Delete
SELECT
DCL (Data Control Language)
Grant
Revoke
Commit
Rollback
8.Aggregate functions
Min（）Max（） it can work on non numeric data types also
AVG（）SUM（）It works only on numeric data type
COUNT(*) = Number of rows (all)
COUNT(columnname) = Number of rows that do not haveNULLValue
9.运行SQL查询语句，让你选择查询结果是什么
10.写一个SQL语句 判断是什么查询
Independent Sub Query
Correlated Query
Sub-queries:
A sub-query is a query within a query.
A sub-query answers the queries that have multiple parts
The sub-query answers one part of the question
The sub query is enclosed in parentheses
Independent SUB QUERIES:
Inner query is independent of outer query
Inner query is executed first and the results are stored
Outer query then runs on the stored results
Example:List the name of item whose unit price is greater than the unit price of item id ‘BAK101’
SELECT itemname FROM item
WHERE unitprice
>
(SELECT unitprice FROM item WHERE itemid=‘BAK101’ );
Sub-queries:
A sub-query is a query within a query. A sub-query answers the queries that have multiple parts; the sub-query answers one part of the question, and the parent query answers the other part. When you nest many sub-queries, the innermost query is evaluated first.
SubQueries can be used in SELECT , FROM, WHERE and HAVING clauses.
For e.g :
Select all sales reps who have a higher quota than sales rep 101.
We need to analyze this query and understand how to break it into sub problems
First we need to find out what is the quota of sales rep 101
Based on this info, we need to select sales reps who have a higher quota than this value
So, the inner query will find the quota of sales rep 101 and the outer query will extract sales reps exceeding this quota value. The solution would look like:
SELECT Rep
FROM SalesReps
WHERE Quota >
SELECT Quota
FROM SalesReps
WHERE Empl_Num = 101;
11.Transaction Properties:ACID
12.还有一个数据库管理比传统文件管理系统的优点：
13.锁问题 我考了好几道 就是例如：一个事务给添加了一个共享锁 其他事务还能给加什么锁。记住锁区别就好了 课件第七章page20下面有解释
SHARED LOCK和EXCLUSIVE LOCK
Locking is a technique to have a controlled access to the resources like a database, tablespace , table, rows and columns. While these resources are put under lock by some transaction, other transactions have very restricted or no access to these resources, depending on the locking mode.
Locking is one of the most widely used techniques in commercial RDBMS products to achieve consistency while supporting concurrency of transactions.
Basically resources can be locked either in Shared (S) mode for Read purpose or in Exclusive (X) mode for Update, Delete or Insert purpose.
Tablespace: The logical part of the database which represents collection of the structures like tables, etc created by various users.
SHARED LOCK
This locking technique allows a higher transaction concurrency. When a particular resource like a table or a row is locked in the shared mode by one transaction, all other transactions can perform the read operation on the locked resource, but no updates or modifications are possible by other transactions. Usually SELECT operation takes the S lock on resources.
EXCLUSIVE LOCK
This is the most restrictive lock. Once a transaction puts the X lock on a particular resource, no other transaction can put any kind of lock on this resource. This resource is exclusively reserved for the first transaction and no other transaction can use it for read or write operation. Hence this X lock allows the least concurrency.
Usually INSERT/UPDATE/DELETE operations put the X lock on resources before writing/modifying/deleting operations.
If transaction T1 locks the resource A (database/tablespace/table/row/field) in shared(S) mode, another transaction T2 can also lock the same resource A, in shared(S) mode.
If transaction T1 locks the resource A (database/tablespace/table/row/field) in shared (S) mode, another transaction T2 can not lock the same resource A, in exclusive (X) mode until T1 releases its S lock on the resource A.
If transaction T1 locks resource A in X mode no other transaction can lock resource A in any other (S or X) mode until T1 releases its X lock on the resource A.
14.还有第七章page23 也考过。后面page24有个matrix挺难记的 我锁的题几乎都是蒙的
P23 Intent Share
This lock has the intention to share the requested node. This also allows the requester to explicitly lock the descendants of this node in S or IS mode.
Example: Transaction SummaryReport can lock entire database DB_BANK_DETAILS and tablespace TS_CUST_DETAILS in IS mode and ACC_DETAILS table explicitly in S mode.
Intent Exclusive
This lock has the intention to have exclusive access to the requested node and allows the requester to explicitly lock the descendants in IX or X modes.
Example: Transaction BalanceUpdate can lock database DB_BANK_DETAILS, the tablespace TS_CUST_DETAILS and the table ACC_DETAILS in IX mode and lock the row R7 explicitly in X mode.
Shared Intent Exclusive
The Combination of Shared and Intent exclusive lock is referred to as Shared Intent Exclusive Lock or SIX Lock.
A share and intent exclusive lock (or SIX lock, pronounced as the separate letters S I X rather than like the number six) indicates an S lock at the current level plus an intention to insert, delete, update data at a lower level of granularity. Think of a SIX lock as an S lock plus an IX lock. Only one transaction can be granted a SIX lock on a table at a time.
A SIX lock on a table indicates an intention to read all of the rows in the table and to delete/update/insert to a few. The S lock in the SIX lock at the table level covers all of the rows. Rows that are updated will obtain X locks, but only after IX intention locks have been obtained on the pages that contain them.
A SIX lock is stronger than a S lock or an IX lock. When a transaction obtains a SIX lock on a table, only that transaction will be able to modify data in the table. In this respect, a SIX lock slightly resembles an X lock. With a SIX lock, however, other transactions that want to read some of the data (read data at the row or page level and obtain an IS lock on the table) are allowed to proceed, so concurrency is better than with an X lock. Lock mode compatibility will be described in greater detail later in this module.
If other transactions obtain S lock on row of a table or S lock on page of a table the SIX transactions wants to modify, the SIX transaction must wait until the S locks are released before it can modify the data.
Other transactions that want to read all of the data (obtain an S lock on the table) or that want to write to any portion of the data are not allowed to proceed until the SIX lock is released.
A SIX lock is also called a share sub-exclusive lock.
Page: It is part of a table. Usually in one page multiple rows are stored.
References: Relational Database Management System courseware material authored by “Hanumesh V.J. & Seema Acharya” , E&R Department, Infosys Technologies Limited
15.UNDO and REDO
undo : just contains<Ti start>
redo : contain <Ti start>and<Ti commit>
16.创建索引的好与坏 第七章page46
还有primary keys 默认创建索引
P46 Advantages of having an INDEX
Greatly speeds the execution of SQL statements with search conditions that refer to the indexed column (s)
It is most appropriate when retrieval of data from tables are more frequent than inserts and updates
Disadvantages of having an INDEX
It consumes additional disk space
Additional overhead on DML statements
17.Oracle里的LIKE 操作 有一个替代字符“？”
18.有几个oracle操作考
NVL(expr1,expr2) :if expr1 is null,NVL() return expr1,
expr1 isnot null,NVK()return expr2.
substr('asdfasdf',3,4)=?
我竟然考了一个substr('adfsadf',-2,3)=?
最好自己做作实验试试 还有个decode()操作 不知道你课件有没有 以前给我们的里面有 后来的课件就没有
One Unary
Two Binary
Three Ternary
e.g.:
Unary ? employee manager-of employee
Binary ? employee works-for department
Ternary ? Customer purchases items from a shop keeper
给你一个关系 判断为几元
2. ER图 判断某关系是
one-to-one (1:1)
one-to-many (1:N)
many-to-one (M:1)
many-to-many (M:N)
3.例给你：一个电影明星可以演多部电影，但电影里最多能出现一次这个电影明星 应建怎样的表？应该是：明星的表格里包含电影id，电影id作为一个外键出现在明星的表格里。
4.判断范式
给你一个关系和范式。让你判断该范式为几范式
5.If the value to be inserted into a cell is unknown, or it does not have any value. It is represented as NULL。NULL is not equivalent to zero, blank or empty string。此题为关于NULL判断题
6.关于foreign key .一张表可以有0个，1个或多个外键，同时外键可以为null值 在第一章page29还有page30
Foreign key
P29 The set of attributes whose values are required to match with the values of a column in the same or another table.
Points to remember
Foreign key values do not (usually) have to be unique.
Foreign keys can also be null .
To enter the data in child table corresponding data must be present in master table or NULL is the default entry in child table in the referenced column ( FK column)
p30 Points to remember
Foreign Key column values must match with the values of the corresponding Key column. This is also known as Referential constraint.
Self-Referencing table is a table which has a Foreign Key referring to its own Key.
7.判断某操作属于DDL 还是DML 还是DCL
例如：drop 属于哪个操作？
DDL (Data Definition Language)
Create
Alter
Drop
Truncate
DML (Data Manipulation Language)
Insert
Update
Delete
SELECT
DCL (Data Control Language)
Grant
Revoke
Commit
Rollback
8.Aggregate functions
Min（）Max（） it can work on non numeric data types also
AVG（）SUM（）It works only on numeric data type
COUNT(*) = Number of rows (all)
COUNT(columnname) = Number of rows that do not haveNULLValue
9.运行SQL查询语句，让你选择查询结果是什么
10.写一个SQL语句 判断是什么查询
Independent Sub Query
Correlated Query
Sub-queries:
A sub-query is a query within a query.
A sub-query answers the queries that have multiple parts
The sub-query answers one part of the question
The sub query is enclosed in parentheses
Independent SUB QUERIES:
Inner query is independent of outer query
Inner query is executed first and the results are stored
Outer query then runs on the stored results
Example:List the name of item whose unit price is greater than the unit price of item id ‘BAK101’
SELECT itemname FROM item
WHERE unitprice
>
(SELECT unitprice FROM item WHERE itemid=‘BAK101’ );
Sub-queries:
A sub-query is a query within a query. A sub-query answers the queries that have multiple parts; the sub-query answers one part of the question, and the parent query answers the other part. When you nest many sub-queries, the innermost query is evaluated first.
SubQueries can be used in SELECT , FROM, WHERE and HAVING clauses.
For e.g :
Select all sales reps who have a higher quota than sales rep 101.
We need to analyze this query and understand how to break it into sub problems
First we need to find out what is the quota of sales rep 101
Based on this info, we need to select sales reps who have a higher quota than this value
So, the inner query will find the quota of sales rep 101 and the outer query will extract sales reps exceeding this quota value. The solution would look like:
SELECT Rep
FROM SalesReps
WHERE Quota >
SELECT Quota
FROM SalesReps
WHERE Empl_Num = 101;
11.Transaction Properties:ACID
12.还有一个数据库管理比传统文件管理系统的优点：
13.锁问题 我考了好几道 就是例如：一个事务给添加了一个共享锁 其他事务还能给加什么锁。记住锁区别就好了 课件第七章page20下面有解释
SHARED LOCK和EXCLUSIVE LOCK
Locking is a technique to have a controlled access to the resources like a database, tablespace , table, rows and columns. While these resources are put under lock by some transaction, other transactions have very restricted or no access to these resources, depending on the locking mode.
Locking is one of the most widely used techniques in commercial RDBMS products to achieve consistency while supporting concurrency of transactions.
Basically resources can be locked either in Shared (S) mode for Read purpose or in Exclusive (X) mode for Update, Delete or Insert purpose.
Tablespace: The logical part of the database which represents collection of the structures like tables, etc created by various users.
SHARED LOCK
This locking technique allows a higher transaction concurrency. When a particular resource like a table or a row is locked in the shared mode by one transaction, all other transactions can perform the read operation on the locked resource, but no updates or modifications are possible by other transactions. Usually SELECT operation takes the S lock on resources.
EXCLUSIVE LOCK
This is the most restrictive lock. Once a transaction puts the X lock on a particular resource, no other transaction can put any kind of lock on this resource. This resource is exclusively reserved for the first transaction and no other transaction can use it for read or write operation. Hence this X lock allows the least concurrency.
Usually INSERT/UPDATE/DELETE operations put the X lock on resources before writing/modifying/deleting operations.
If transaction T1 locks the resource A (database/tablespace/table/row/field) in shared(S) mode, another transaction T2 can also lock the same resource A, in shared(S) mode.
If transaction T1 locks the resource A (database/tablespace/table/row/field) in shared (S) mode, another transaction T2 can not lock the same resource A, in exclusive (X) mode until T1 releases its S lock on the resource A.
If transaction T1 locks resource A in X mode no other transaction can lock resource A in any other (S or X) mode until T1 releases its X lock on the resource A.
14.还有第七章page23 也考过。后面page24有个matrix挺难记的 我锁的题几乎都是蒙的
P23 Intent Share
This lock has the intention to share the requested node. This also allows the requester to explicitly lock the descendants of this node in S or IS mode.
Example: Transaction SummaryReport can lock entire database DB_BANK_DETAILS and tablespace TS_CUST_DETAILS in IS mode and ACC_DETAILS table explicitly in S mode.
Intent Exclusive
This lock has the intention to have exclusive access to the requested node and allows the requester to explicitly lock the descendants in IX or X modes.
Example: Transaction BalanceUpdate can lock database DB_BANK_DETAILS, the tablespace TS_CUST_DETAILS and the table ACC_DETAILS in IX mode and lock the row R7 explicitly in X mode.
Shared Intent Exclusive
The Combination of Shared and Intent exclusive lock is referred to as Shared Intent Exclusive Lock or SIX Lock.
A share and intent exclusive lock (or SIX lock, pronounced as the separate letters S I X rather than like the number six) indicates an S lock at the current level plus an intention to insert, delete, update data at a lower level of granularity. Think of a SIX lock as an S lock plus an IX lock. Only one transaction can be granted a SIX lock on a table at a time.
A SIX lock on a table indicates an intention to read all of the rows in the table and to delete/update/insert to a few. The S lock in the SIX lock at the table level covers all of the rows. Rows that are updated will obtain X locks, but only after IX intention locks have been obtained on the pages that contain them.
A SIX lock is stronger than a S lock or an IX lock. When a transaction obtains a SIX lock on a table, only that transaction will be able to modify data in the table. In this respect, a SIX lock slightly resembles an X lock. With a SIX lock, however, other transactions that want to read some of the data (read data at the row or page level and obtain an IS lock on the table) are allowed to proceed, so concurrency is better than with an X lock. Lock mode compatibility will be described in greater detail later in this module.
If other transactions obtain S lock on row of a table or S lock on page of a table the SIX transactions wants to modify, the SIX transaction must wait until the S locks are released before it can modify the data.
Other transactions that want to read all of the data (obtain an S lock on the table) or that want to write to any portion of the data are not allowed to proceed until the SIX lock is released.
A SIX lock is also called a share sub-exclusive lock.
Page: It is part of a table. Usually in one page multiple rows are stored.
References: Relational Database Management System courseware material authored by “Hanumesh V.J. & Seema Acharya” , E&R Department, Infosys Technologies Limited
15.UNDO and REDO
undo : just contains<Ti start>
redo : contain <Ti start>and<Ti commit>
16.创建索引的好与坏 第七章page46
还有primary keys 默认创建索引
P46 Advantages of having an INDEX
Greatly speeds the execution of SQL statements with search conditions that refer to the indexed column (s)
It is most appropriate when retrieval of data from tables are more frequent than inserts and updates
Disadvantages of having an INDEX
It consumes additional disk space
Additional overhead on DML statements
17.Oracle里的LIKE 操作 有一个替代字符“？”
18.有几个oracle操作考
NVL(expr1,expr2) :if expr1 is null,NVL() return expr1,
expr1 isnot null,NVK()return expr2.
substr('asdfasdf',3,4)=?
我竟然考了一个substr('adfsadf',-2,3)=?
最好自己做作实验试试 还有个decode()操作 不知道你课件有没有 以前给我们的里面有 后来的课件就没有
...全文
请发表友善的回复…
发表回复
q107770540 2010-08-23
- 打赏
- 举报
substr('asdfasdf',3,4)= 'fasd' 从第4位截取 截取长度是4
