Q:- Why do we use indexing? Give a suitable example in support of your answer.
1. A DBMS may use different file organisations for its own purposes.
2 .A DBMS user is generally given little choice of file type.
3 .A B+ Tree is likely to be used wherever an index is needed.
4. Indexes are generated:
5 (Probably) for fields specified with `PRIMARY KEY' or `UNIQUE' constraints in a CREATE TABLE statement.
6. For fields specified in SQL statements such as CREATE [UNIQUE] INDEX indexname ON tablename (col [,col].
7. Primary Indexes have unique keys.
8. Secondary Indexes may have duplicates.
9.An index on a column which is used in an SQL `WHERE' predicate is likely to speed up an enquiry.
10.this is particularly so when `=' is involved (equijoin) no improvement will occur with `IS [NOT] NULL' statements
Q2:- : Under what circumstances, we will use foreign key? Give an example
A foreign key establishes a relationship, or constraint, between two tables
we will create two simple database tables. They are not well designed, but will demonstrate the power of foreign keys!
=> employee: a table of company employees where each member is assigned a unique ID
=>borrowed: a table of borrowed books. Every record will reference a borrower's employee ID.
We will define a foreign key relationship between the employee's ID in both tables. This provides a couple of advantages:
1. It is not possible to enter an invalid employee ID in the ‘borrowed' table.
2. Employee changes are handled automatically by MySQL.
Q3:- Produces a new relation with some of the attributes of relation and remove duplicate tuples, give two different examples.
A relation is defined as a set of tuples that have the same attributes. A tuple usually represents an object and information about that object. Objects are typically physical objects or concepts. A relation is usually described as a table. It is organized into rows and columns. All the data referenced by an attribute are in the same domain and conform to the same constraints.The relational model specifies that the tuples of a relation have no specific order and that the tuples, in turn, impose no order on the attributes. Applications access data by specifying queries, which use operations such as select to identify tuples, project to identify attributes, and join to combine relations. Relations can be modified using the insert, delete, and update operators. New tuples can supply explicit values or be derived from a query. Similarly, queries identify tuples for updating or deleting. It is necessary for each tuple of a relation to be uniquely identifiable by some combination (one or more) of its attribute values. This combination is referred to as the primary key..
Q4:- Can we use a virtual table for security purpose? Justify your answer and give an example to create a view.
Yes we can use a virtual table for security purpose, the view's virtual table are the fields of one or more real tables in the database. You can use views to join two tables in your database and present the underlying data as if the data were coming from a single table, thus simplifying the schema of your database for users performing ad-hoc reporting. You can also use views as a security mechanism to restrict the data available to end users. Views can also aggregate data (particularly useful if you can take advantage of indexed views), and help partition data. In this article we will look at these different types of view to see when we can take advantage of a view for our application.
Q5:- Write the syntax of all commands Which are used to define definition if data?
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object
DML:Data Manipulation Language (DML) statements are used for managing data within schema objects.
* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency
DCL :Data Control Language (DCL) statements.
* GRANT - gives user's access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command
TCL:Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
Q6: Consider the table EMPLOYEE and Department with following fields:
A: List all the employees whose location is ‘Pune' and dept_name is ‘Computer'.
SELECT * FROM EMPLOYEE WHERE LOCATION = ‘PUNE' AND DEPT_NAME LIKE 'COMPUTER';
B: Count the total number of departments. Also count the total number of employees whose dept_name is ‘computer'
SELECT * COUNT FROM EMPLOYEE WHERE GROUPBY = DEPT_NAME;
SELECT * COUNT FROM EMPLOYEE WHERE DEPT_NAME LIKE 'COMPUTER' ;
C: Add a new department ‘R&D' in the database.
ALTER TABLE WHERE EMPLOYEE R&D VARCHAR(30);
D: List the names of employees whose salary is greater than 50000.
SELECT NAME FROM EMPLOYEE WHERE SALARY>50000;
E: List the names of employees having maximum and minimum salary
SELECT NAMES FROM EMPLOYEE WHERE SALARY LIKE'------‘AND NOTLIKE'----‘;
F: Now change the department name from ‘computer' to ‘software design', wherever applicable in the table.
UPDATE TABLE EMPLOYEE SET DEPARTMENT NAME LIKE 'SOFTWARE DESIGN' WHERE DEPARTMENT NAME LIKE 'COMPUTER.