DBMS SET 3
Part A-(Q1-Q20)
1. What does acronym SQL stands for?
- Standardized Query Language
- Structured Query Language
- Simple Query Language
2. List at least three examples of one-to-many relations:
- mother and children, currency and bank account, inhabitants of town
- authors and books, prime ministers and countries, client and rented car
- students and courses, currency and bank account, flight and tourist
3. SELECT – SUM( ) – FROM – GROUP BY
- is the projective query
- is the action query
- is used for creating queries which divide items into groups according to a given attribute and an aggregate function
4. Which are the basic types of DB models?
- networked, structured, centralized
- networked, hierarchical, relational
- relational, hierarchical, action
- aggregate, projective, action
5. Key word DISTINCT will:
- sum different data
- list different values only once
- add the distinct values
- none of these
6. What does acronym DBMS stands for?
- Database Management System
- Database Management Structure
- Database Management Standards
- Database Management Studies
7. Keyword COUNT when used in query will:
- group dynaset by defined attributes
- answer the question “How many…?”
- answer the question “What is the sum of…?”
8. DML stands for:
- Database Management Language
- Data Manipulation Language
- Data Multiplication Language
- None of these
9. Which format does not belong to the type of Number:
- integer
- scientific
- currency
10. Basic table structure is defined by:
- attributes, fields
- attributes, fields, values
- records, fields, values
11. John is working in the customer table and needs to know what customers are located in
Florida. To find the information, he would.
- create a new table
- create a new query
- create a new form
- utilize the Database Wizard
12. Where does the DBMS store the definitions of data elements and their relationships?
- Data file
- Data dictionary
- Index
- Data map
13. When the DBMS translates logical requests into commands that physically locate and
retrieve the requested information, it is fostering data .
a) Integrity
- Inconsistency
- Independence
- Mining
14. All of these are true about a database except
- it is a shared, integrated structure
- it must contain multiple tables
- it stores user data
- iit stores metadata
15. Metadata is:
- raw facts
- user created data
- data about data
- warehoused data
16. The DBMS stores definitions of the data elements and their relationships in a:
- fixed length record
- data dictionary
- fixed length field
- information diary
17. is defined as “the condition in which all of the data in the database are consistent
with the real-world events and conditions.”
- data dictionary
- data integrity
- data anomaly
- data processing
18. _____is a DBMS function in which the DBMS creates and manages the complex structures required for data storage.
- data dictionary management
- backup and recovery management
- data storage management
- database communication interfaces
19. The manages interaction between the end user and the database.
- DM query engine
- DBMQ
- DBMS
- DP
20. The hierarchical database model is based on a .
- Tree Structure
- Lack of a child segment
- Lack of a parent segment
- Matrix
Part B (Q21-40)
Write SQL commands
Q1: To create the above table structure with proper constraints.
Answer:
CREATE TABLE Library
(No INTEGER (9) primary key not nulll,
Title CHARACTER (50) not null
Author CHARACTER (50) not null,
Type (CHARACTER (10) not null,
Pub CHARACTER (30) not null,
Qty INTEGER (9) not null,
Price CURRENCY (Rs) not null,
Q2: Select all the PROG type published by BPB from Library.
Ans:
SELECT Library. Title, Library. Author, Library.Qty, Library.[Price (Rs)]
FROM Library
WHERE ((“Type”=”PROG” And “pub”=”BPB”));
Q3: Display a list of all books with Price more then 130 and sorted by Qty.
Ans:
SELECT Library.Title, Library.Author, Library.Type, Library.Pub, Library.Qty
FROM Library
WHERE ((“price”>”130″))
ORDER BY Library.Qty;
Q4: Display all the books sorted by Price in Ascending Order.
Ans:
SELECT Library.Title, Library.Author, Library.Type, Library.Pub, Library.Pub, Library.Qty, Library.[Price (Rs)]
FROM Library
ORDER BY Library.[Price (Rs)];
Q5: Display a report. Listing Book No. current value and misplacement charges for each book in the above table. Calculate the misplacement charges for all books Price* 1.25
Answer:
SELECT Library.No,Library.price,mscharges as Library.price*1.25
Q6: Count the number of books published by PHI
Answer:
SELECT Distinct count(Library.Pub)
FROM Library
HAVING (((Library.Pub)=”PHI”));
Q7: Insert a new book in the Library.
Ans:
INSERT INTO Library values (11,”CISCO”,”john”,”NET”,”BPB”,7,100);
Q8: Count the no of books in each Type.
Ans:
SELECT Distinct count (Library.Pub)
Q9: Add one more attribute “No of copies” in the above table.
Ans:
ALTER TABLE Library
ADD No of copies INTEGER (9)
Q10: Update the new attribute value with 2 for each book.
Ans:
INSERT INTO TABLE Library
(No of copies)
Values
(2);
Q11: Count the total number of Publishers.
Ans:
Select Distinct Count(Library.Pub) from Library;
Q12: Drop table Library.
Ans:
DROP TABLE Library ;
Give the Output of the following SQL commands.
Q1: Select Count(Distinct Pub) from Library
Output:
Syntax error (missing operator) in query expression ‘count(Distinct Pub)’.
Q2: Select MIN(price) from Library where price<150
Output:
MIN(PRICE)
———-
40
Q3: Select qty, SUM(qty) from Library GROUPBY Type.
Output
Syntax error from clause
Q4: Select AVG(price) from Library where qty<3.
Output:
AVG(PRICE)
———-
145
Q5: Select * from Library where Qty IN(Select average(Qty) from Library));
Output:
Error
Extra) in query expression ‘Qty IN(Select average(Qty) from Library))’.
Write relational algebraic expression for:
Q1: Find all books of Prog type.
πTitle (σtype=PROG(Library))
Q2: Find Pub, who publish FND Books.
Πpub(σtype=FND(Library))
Q3: Find all the PROG type published by PHI from Library.
πTitle (σtype=PROG^ σpub=PHI(Library))
