Database System-I

1.What is foreign Key? What is its purpose?

A non key attribute, whose value are derived from the primary key of some other table, is known as foreign key in the current table.The table in which this non-key attribute i.e. foreign key attribute exists, is called a foreign table.

2.Define the terms  Tuple and  Attribute

Tuples: The rows of tables (relations) are generally referred to as tuples. Attribute: The columns of tables are generally referred to as attribute.

3.What do you understand by the terms Cardinality and Degree of the table?

Degree: The number of attributes in a relation determines the degree of a relation. A relation having 3 attributes is said to be a relation of degree 3.

Cardinality: The number of rows in a relation is known as Cardinality.

4.What is the main function of DBA.

The DBA must be a manager, more than a technician-seeking to meet the needs of people who use the data. Since many user may share the same data resource, the DBA must be prepared to meet the need and objective.

5.Write a query on the customers table whose output will exclude all customers with a rating <=100, unless they are located in Shimla.

SELECT *  FROM customers  WHERE rating >100 OR city =’Shimla’ ;

6.Write a query that selects all orders except those zeros or NULLs in  the amount field.

SELECT * FROM Orders  WHERE amt < >0  AND (amt IS NOT NULL) ;

7.Write a query that lists customers in descending order of rating. Output the rating field first, followed by the customer’s name and number.

SELECT rating, cust-name, cust-num FROM customers ORDER BY rating DESC;

8.Write a command that puts the following values, in their given order, into the salesman table: cust-name-Manisha, city-Manali, comm.- NULL, cust-num-1901.

INSERT INTO salesman (city, cust-name, comm.,cust-num)VALUES(‘Manisha’,NULL,1901) ;

9.What are DDL and DML?

The DDL provides statements for the creation and deletion of tables and indexes.

The DML provides statements to enter, update, delete data and perform complex queries on these tables.

10.What is the difference between Where and Having Clause?

The HAVING clause places the condition on group but WHERE clause places the condition on individual rows

11.What do you understand by constraints?

Constraints are used to enforce rules at table level when ever row is inserted, updated/deleted from table.

Constraints can be defined to one of the Two level.

Column Level: Reference to a single column. can be defined any type of integrity.

Table Level: References one or more columns and is defined separately from definition of the columns in the table.

12.Write some features of SQL?

Recovery ad Concurrency:- Concurrency is concerned with the manner in which multiple user operate upon the Database.

Security: The Security can be maintained by view mechanism.

Integrity Constraints-> Integrity constraints are enforced by the system.

13.Write various database objects available in SQL?

Table: A Table is used to store Data

View: A view is the temporary table created using Original table.

Sequence:   Sequences are used to generate Primary key value.

Index:  They are used to improve queries.

Synonym: They give alternative names to objects.     

14.Write the rules to name an objects?

The maximum length must be 30 character long.

The Object name  should not contain quotation mark.

The name must start with letter.

The use of $ and # is discouraged in the object name.

A name must not be a reserved name.

15.What are group Functions?

The aggregate functions are group functions. They return result based on groups of rows. The group functions are

AVG(), COUNT(), MAX(),  MIN(), SUM()

16.What are  column alias?

In many  cases heading table may not be descriptive and hence it difficult to understand. In  such case we use columns alias It will change column heading with column alias.