Top banner

Thursday, April 29, 2010

SQL OR DBMS SQL QUERIES 3

Table Structure

Book
(BookID, Title, Author, Publishers, Price, DistID)
Distributors
(DistID, Name, City)

i) Display the details of books whose price is more than the average price of all books.

Select * from book where price > ( select avg(price) from book)

ii) Display the details of books written by ‘Groff’ and published by ‘TMGH’

select * from book where Author=’Groff’ and publisher=’TMGH’

iii) Create a view to show Title, Author, Publisher and Distributor’s Name & name this view as ShowDetails.

create view ShowDetails (Title, Author, Publisher, Distributor)
as select title, author, publisher, name
from book , distributor
where book.distid = distributor.distid.

SQL OR DBMS SQL QUERIES 2


Table Structure
Emp
(Empid, Ename, CompID, salary, Joindate, Gender, City)
Company
(CompID, CompName, City)

 i) Give name of companies located in those cities where TATA is located.

 Select companyName from company
where city in (select city from company where companyName = ‘TATA’)
 
 ii) Give the name of the employees living in the same city where their company is located.

Select ename from Emp e, company c
 where e.city=c.city
 
iii) Update salary of employee ‘Raj’ by giving him the salary of ‘Radha’ working in same company.

update Emp set salary = (select salary from Emp where Ename = ‘Radha’)
where Ename = ‘Raj’
and compid = (Select compid from emp where ename=’Radha’)

iv) Display how many male and female members have joined in January 2004.
 
Select count(ename) from emp
group by gender having to_char(joindate,’mon-yyyy’)=’jan-2004’

v) Display the total number of companies located in each city.

Select count(compid) from company group by city

SQL OR DBMS SQL QUERIES 1


Table Structure

Sales_Header
(orderno, orderdate, customerID, salesmanid, paymentStatus, TransactionType, paymentdate)
Sales_detail
(Orderno, ProductID, Qty, Rate, Amt)

i) Display the order, which were issued in first Quarter of Current year

Select orderno from sales_header where to_char(orderDate,’mm’)<4 and to_char(orderDate,’yy’) = to_char(sysdate,’yy’)

ii) Display the order number, order date, customer id and order amount for orders having value of 500 Rs.

select orderno,orderdate,cutomerid, amt from sales_header as h, sales_detail as d
where h.orderno = d.orderno and amt>=500

iii) Display the order detail where RIN001 soap is sold for Min of 50 Rs

select h.* from sales_header as h, sales_detail as d
where h.orderno = d.orderno and productid=’RIN001’ and amt>=50

iv) Display the order collected by Executive no ‘S120’

select orderno, amt from sales_header as h, sales_detail as d
where h.orderno = d.orderno and h.salesmanid = ‘S120’

v) Display the unpaid order in ascending order of Order no

select orderno, orderdate from sales_header
where paymentstatus = ‘unpaid’ order by orderno

vi) Assign the privileges to see the data from both tables to ‘Raj’

Grant select on sales_header, sales_details to ‘Raj’

vii) Display the various types of product sold to customers.

Select distinct productid from sales_header as h, sales_details as d where h.orderno = d.orderno

viii) Display all credit transaction, with their payment status done in Dec. 2003

select * from sales_header where transactiontype=’credit’ and paymentstatus = ‘paid’ and to_char(paymentdate,’mon-yyyy’) = ‘dec-2003’

xi) Display the details of total cash transaction done by each sales executives.

Select * , sum(amt) from sales_header as h, sales_detail as d where h.orderno = d.orderno and transactiontype = ‘cash’ and paymentstatus = ‘paid’ group by salesmanid

Tuesday, April 27, 2010

Vb Important Question per Chapters 10,11,12

Chapter 10 –

What do you mean by file organization? What are the different types of file organization?

What are the differences between a random file and a sequential file?

What are the three steps necessary to process data files?
List/tabulate and explain the file modes for data files.
What is the significance of a file number? What function can be used to determine an available file number?

Explain the differences between the Output and Append modes.

What is the format for the statements to read and write sequential files?

Explain what occurs when an Open statement is executed?

Explain the function EOF(N).

When would an On Error statement be used? Explain the three forms of the On Error statement.

Explain the function and use of the Err object. Explain its Source, Number and Description properties.

Explain the following –
QueryUnload event
The Raise method
The Resume statement and its three forms
Exit Sub and Exit Function statements
Get and Put statements
LOF function
Trim, LTrim and RTrim functions

How do we read and write a random file?

What does updating a data file mean?

Give example for using the InputBox function.


Chapter 11 –

Explain the difference between a data control and a data-bound control. List all controls that can be data bound or data aware.

Explain the BOF and EOF properties.

Explain the following properties –
Connect
DatabaseName
RecordSource
DataSource
DataField

Explain the following methods –
MoveNext
MovePrevious
MoveFirst
MoveLast
AddNew
Update
CancelUpdate
Delete

What steps are needed to add a new record to a database?

What steps are needed to delete a record from a database?

What steps are needed to change the data in a database record?

How can you check for the user deleting the only record in a Recordset?

Describe the following terms – file, record, current record, table, row, column, field, and key field w.r.t. a database.


Chapter 12 –

Differentiate among tables, dynasets, and snapshots recordsets

For which type(s) of recordset is a Find valid (FindFirst, FindNext, FindLast, or FindPrevious)? What types of data can be searched for with a Find?

For which type(s) of recordset is a Seek valid? Which fields in a recordset can be searched with a Seek?

What is SQL, and how is it used in Visual Basic?

When does a control's Validate event occur? How is the Cancel argument used? Explain the property Causes Validation.

Explain the following –

RecordCount and AbsolutePosition properties (write a small piece if code to display the record count and record number)

Reposition event

Explain the methods – FindFirst, FindLast, FindPrevious and FindNext

NoMatch property

BookMark property

Write a short note on MS DB Grid. Explain some of its properties.

Sunday, April 4, 2010

Vb Important Question per Chapters 7,8,9


Chapter 07 –

1. Explain List Box and Combo Box controls.

2. Name and describe the three styles of combo boxes.

3. Explain the following properties –

a. ListCount
b. ListIndex
c. List
d. AddItem
e. Clear
f. RemoveItem
g. Sorted

4. What are the different loop-constructs in VB?

5. Explain the difference between a pretest and a posttest in a Do/Loop.

6. Explain the differences between a Do/Loop and a For/Next loop.

7. How do the Left, Right, Mid and Len functions operate?

8. What is the purpose of Printer.Print? How do you control the horizontal spacing of printer output?




Chapter 08 –

1. Define the following terms –
a. Array
b. Element
c. Subscript
d. Array of controls
e. Subscripted variable

2. Why would a list box control need both a ListIndex property and an ItemData property?

3. How to setup and use a control array?

4. Explain Select Case statement with an example.

5. Explain the use of For Each/Next in traversing the array.

6. Write a short note on user defined data types. Give an example.

7. What do you mean by multidimensional array? How do we implement it?

Chapter 09 –

1. Explain the difference between ByVal and ByRef. When is each used?

2. What actions trigger the Initialize event and the Terminate event of an object?

3. What is a collection? Name two collections that are automatically built into VB.

4. What properties and methods are provided by the Collection object?

5. Write short notes on key features of object-oriented language.

6. Write a short note on class module.

7. Write a short note on Property Procedures.

8. (How do we create a new object using a class?) What are the three choices of creating a new object?

9. Explain early binding versus late binding.

10. Explain the following –
a. ActiveX control with an example
b. A DLL (Dynamic Link Library)?
c. API (Application Programming Interface)

Thursday, April 1, 2010

SQL OR DBMS SQL QUERIES

QCreate a table calling_card with the attributes company_name, card_number,
starting_value, value_left and pin_number
Assumptions:
o Attribute company_name may have upto 25 characters
o Attributes starting_value and value_left are measured in rupees and paisa
o Card_number may have up to 15 digits
o Pin_number is always 12 characters long

------->ANSWER

SQL>
CREATE TABLE CALLING_CARD_743
2 (
3 COMPANY_NAME VARCHAR(25),
4 CARD_NO VARCHAR(15),
5 START_VALUE NUMBER(4,2),
6 VALUE_LEFT NUMBER(4,2),
7 PIN_NO CHAR(12)
8 );

Table created.



Q Rewrite the CREATE TABLE command with attributes card_number identified as the
primary key and pin_number identified as unique.

--------->ANWSER
SQL> 
CREATE TABLE CALLING_CARD_743
  2 (
  3 COMPANY_NAME VARCHAR(25),
  4 CARD_NO VARCHAR(15) PRIMARY KEY,
  5 START_VALUE NUMBER(4,2),
  6 VALUE_LEFT NUMBER(4,2),
  7 PIN_NO CHAR(12) UNIQUE
  8 );

Table created.




QRewrite the CREATE TABLE command using named constraints.

 --------->ANWSER

SQL> DROP TABLE CALLING_CARD_743
  2 ;

Table dropped.

SQL> CREATE TABLE CALLING_CARD_743
  2 (
  3 COMPANY_NAME VARCHAR(25),
  4 CARD_NUMBER VARCHAR(15) CONSTRAINT CCARD_NUM_PK_743 PRIMARY KEY,
  5 STARTING_VALUE NUMBER(4,2),
  6 VALUE_LEFT NUMBER(4,2),
  7 PIN_NUMBER CHAR(12) CONSTRAINT CCPIN_NUM_U_743 UNIQUE
  8 );

Table created
 

Vb Important Question per Chapters 4,5,6


Chapter 4 –

1. Explain the purpose of relational and logical operators.

2. Differentiate between a comparison performed on numeric data and a comparison performed on string data. How does VB compare the Text property of a text box?

3. Explain If ...Else construct and nesting.

4. Define the term Validation. When is it appropriate to do validation? Give three different cases.

5. Explain Message Box function and give an example of five message box constants.

6. Explain a Boolean variable. Give an example.

Chapter 05 –

1. What does the term common dialog box mean? Name and explain atleast three types of common dialog boxes. What is the name of extension of the file in which common dialog box control is stored?

2. Explain the difference between a sub procedure and a function procedure.

3. What is a return value? How can it be used?

4. Explain the differences between ByRef and ByVal. When would each be used?

5. Explain the difference between a menu and a submenu.






Chapter 06 –

1. What does the phrase standard code module mean?

2. Discuss the difference between declaring a variable in the General Declarations section of a standard code module and declaring a variable in the General Declarations section of a form code module.

3. What are the purposes of a splash screen and About box?

4. Explain the Hide and Show methods.

5. Explain the Form Load and Form Activate events.

6. What do you mean by modal and modeless forms?

7. Explain the Load and Unload statements.

8. Explain the Me keyword.

9. How do we declare a global variable and global constant?

10. Explain global and static variables.

11. Explain Dim, Public and Private keywords (for this, refer to other chapters also).