topic:
I. Single option
1. with respect to a reason?, 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 Unique Identifies a Row in The Table
2. 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 (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 . '' 'S.
3. An Insertion Operation Will ____iff The insertion Violates The Uniqueness Property of a KEY.
(a) succeed with warning (b) Crash the system (c) Fail (d) succeed without warning
4. An Insertion Operation Will _____iff The Inserted Primary Key Has A Null Value.
(a) succeed with warning (b) succeed without warning (c) Fail (D) Crash the system
5. For Two Tables To BE Union Compatible, Corresponding Column from Each Table Should Have Which of The Following?
(a) Different Domains (b) Different Names (c) The Same Domain (d) The Same Name
6. In The RELATIONAL MODEL, WHICH of the FOLLOWING IS TRUE ABOUT THE DATA TYPE OF A Column?
(a) IT NEED NOTBSTRACT 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 be an abstract data type.
7. 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 trat was deded to the table after the Table Was Designed (D) a KEY THAT WILL BECOME THE PRIMARY KEY WHEN THE PRIMARY Key Is Deleted8. What Does a Projection Operation DO?
(a) IT Extends The Number of Column in A Table. (b) IT SELECTS ROWS from a Table. (c) IT Extends The Number of Rows in A Table. (d) IT SELECTS Column from a table.
9. The Result of a set Difference Operation R - S Will B
(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 is no reference 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...
15. Which of the following sql commands can be used to change, add, or drop colorn 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 remand 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 buy
(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 database20. 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
© CopyRight 2004 Icarnegie, Inc. All Rights Reserved.
II. Q & A
Database Systems Practicalanswer 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:
. EnameprojectdeptKasperForecastAccountingKasperAuditAccountingKasperSpreadsheetAdminMohanForecastAccountingMohanAuditAdminMohanSpreadsheetAdminLinForecastAdminLinAuditAdminLinSpreadsheetAdmin3 Given relation s as:
enamedeptKasperAccountingMohanAdminLinAdmin4. Provide the results of the following operations. If an operation can not 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
CidNameAddresszip11george
23 Main St.1521822Barbara
.
Part
PnopnameUnitprice150X20.00152Y33.00153Z4.00155V15.00162W25.007.
ORDERS
Onocidreceivedshipped201110-DEC-9712-DEC-97211113-Feb-9815-Feb-98222226-Feb-98null8.
Invoice
OnoPnoQtyBilledPrice20152133.0020155460.0020162125.0022152399.0021150120.0021152266.0021153312.0021155460.0021162125.009 In the table ORDERS above, specify in SQL the requirements that:. 1. Received date can not 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 (ie, 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_dom17....);... 18 19. create table DEPARTMENT20 (name dept_name_dom primary key deferrable , 21. mgrid ID_DOM Foreign Key References EMP (ID) Deferrable, 22. Budget budget_dom23.); © Copyright 2004 Icarnegie, Inc. All Rights Reserved.
My answer, it is likely to be wrong, wait until the handsome review, I will give a standard answer: / ********************* **** ****************** /
Answer for multiple-kice:
1 ~ 5: ABACC
6 ~ 10: DDADC
11 ~ 15: AABCD
16 ~ 20: ABBCD
/ *************************************************** /
Answer for Practical:
1.
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
4.
1) ENAME PROJECT DEPT
Kasper Spreadsheet Admin Admin
Mohan Spreadsheet Admin
Lin forecast admin
Lin spreadsheet admin
2) ENAME PROJECT DEPT
Kasper Forecast Accounting
Kasper Audit Accounting
Kasper Spreadsheet Admin Admin
Mohan Forecast Accounting
Mohan Audit Admin
Mohan Spreadsheet Admin
Lin forecast admin
Lin Audit Admin
Lin spreadsheet admin
3) This will not return any value, because the statement means that all values belonging to R but not S are taken out, and it is obviously not such a value.
9.
1) ALTER TABLE ORDERS
Alter Column Received
Datetime Not Null
2) Alter Table ORDERS
Constraint Shipped1 Unique (Shipped)
Constraint shipped2 check (shipped = "" or saleped> 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 receific 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 receific 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));
