Q. What is database?
A. Database is structured collection of data that is stored in a computer so that a program can retrieve those data/records by queries.
Q. What is Oracle? Is a database application and data warehouse application. An SQL Plus is an analyzer tool based on oracle application.
Q.A. DDL – Data Definition Language
Is used to define the structural characteristics of your databases.
Q. DDL SQL commands include the following:
1. Create: to create objects in the database
2. Drop: delete objects from the database
3. Alter: alters the structure in the database
4: Truncate: remove all records from a table, including all spaces allocated for the records are removed.
5. Comment: add comments to the data dictionary
6. Rename: rename an object
Q.A. DML –Data Manipulation Language
Data manipulation language statement query or manipulate data in existing schema objects.
They enable you to:
1. Select: retrieve data from the a database
2. Insert: insert data into a table
3. Update: updates existing data within a table, the space for the records remain
4. Delete: deletes data from a database tables, depend on WHERE clause.
Q.A. DCL- Data Control Language Is used for the control of data. That is a user can access any data based on the privileges given to him. This is done through Data Control Language. Some of the DCL commands are:
1. Grant: gives user’s access privileges to database
2. Revoke: withdraw access privileges given with the GRANT command
TCL: Transaction Control Language
For revoking the transaction and to make the database we use TCL. Some of the TCL commands are:
1.Commit: save work done
2. SavePoint: identify a point in a transaction to which you can later rollback
3. Rollback: restore database to original since the last COMMIT
4. Set Transaction: change transaction options like isolation level and what rollback segment to use.
Q.A. RDBMS – Relational Database Management System
A database management system that allows data arranged in a tabular form to be related to data in other tables via common fields.
RDBMS in the Market:
Oracle
MS SQL Server
DB2
Siebel etc
Q. What is constraint?
A limitation of any kind to be considered in planning, programming, scheduling, implementation or evaluating programs.
A. Constraint means Limitation:
a) Budget will be $200000.00
b) This application will be made by Java script.
Q.A. Primary Key: In database design, a primary key (PK) is a column with a unique value that can be used to identify a unique row in a table. It also be not null and has to be indexed (descendent or ascendant).
An example of primary key is student first name, student class, and student “Social Security numbers.”
Q. What is a foreign key?
A. In database design, a foreign key (FK) is a value that can be used to makes relationships between two or more tables. (It refer to other table primary key)
Q. What is Unique Key? In database design, a unique key refers to the set of columns which have a unique set of values for each row of the table.
Q. what is candidate key?
A. Candidate Key: It has some properties like primary key and it could be null
OR
A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys.
Q.A. Compound Key: In database design, a compound key (also called a composite key or concatenated key) is a key that consists of 2 or more attributes.
Q. what is Join?
A. A join combines two or more tables to retrieve data from multiple tables.
Q. Different kinds of Join?
A. a) Self join or Inner/near joins/Equijoin/Non-Equijoin
And
b) Outer Join: àà An outer join includes rows from tables when there are no matching values in the tables.
1) Left outer join: clause we indicate that we want to get all rows from the left table listed in our FROM clause (we will also called it the first table), even if they don’t have a match in the right (second) tables.
2) Right outer join: gets all rows from the right ( second) table listed in our FROM clause, even if they don’t have a match in the left ( first) table and returns NULL values for the columns from the left table we don’t have match for.
Non-Equijoin: returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.
Self Join: Joins a table to itself as though it were two separate tables.
Q. What is Inner/Equijoin and example?
A. the EQUIJOIN joins two tables with a common column in which each is usually the primary key.
OR: Definition: An inner join is a (syntax) join that selects only those records from both database tables that have matching values.
The syntax for an EQUIJOIN is
SELECT TABLE1.COLUMN1, TABLE2.COLUMN2...
FROM TABLE1, TABLE2 [, TABLE3 ]
WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME
[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN_NAME ]
Look at the following example:
select Employees.last_name, Employees.first_name, Departments.department_name
from employees , Departments
Where Employees.department_id = Departments.department_id
order by Employees.Last_name
This SQL statement returns the last name, first name, and department name by joining Employees, and Departments table.
Q. What is Union?
Is to select related information from two tables
Q. Why we use SQL?
A. a) to manipulate data.
b) Execute data
Q. What is normalization?
A. Is a process of decomposing large tables in two small tables. Also is a technique for designing relational database tables to minimize of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies.
Q. what is clauses?
A. Command’s of SQL.
Q.A. Each row is called Record or Tuple.
Q. What is Drop?
Delete whole table
Q. What is Delete?
Delete data depending on where clause
Q. What is Truncate?
Delete all rows of table
Q. What is Stored Procedure?
Only happen when SQL Query executed
Q. What is Trigger?
Happened on runtime, delete or updating data.
Q. What is difference between SQL and SQLplus?
SQL is a Structured Query Language and SQLplus is an analyzer.
Q. What is different between VarChar and VarChar2?
VarChar works below 8I and VarChar2 does not below 8I
1. VarChar=2000biytes 2. VarChar2=4000biytes
Q. What is % Sign? Ans: Like
Select table name where last name like B%=Barry
Q. What is different between Primary Key and Unique Key?
Primary Key – does not hold null value
Unique Key- does hold null value
Q. What is Aggregate Functions? Count, Max or Min, Sum and sometime they use Avg.
Q. What is different between Where and Having?
Where clause can be used any type of sql statements but having clause can be used with only group functions. We use Where and Having to narrowing down search criteria.
Q. What is data?
Implicit meaning or data is information that has been translated into that is more convenient to move or process.
Q. What is good data base Design?
1. Easy to maintain and modify
2. Easy to modify
Q. What is Database Design Method? 1. Requirement analyzs.
2. Data Modeling
3. Normalizating
Q. What is Spool?
Spool command creates a print file of the report.
Q. What is the different between Union and Join? Union is to select related information from two tables and Join combines two or more tables to retrieve data from multiple tables.
Q. What is Group by?
Specifies the summarization and arrangement of the data.
Q. What is Having?
Used with the GROUP BY to specify the criteria for selection of the data in the final groups.
Q. What is Order by?
Specifies the sort order of the data.
Q. What is Select?
Specifies the columns that will be displayed in the result set.
Q. What is From?
Specifies the tables that contain the column data used in the SQL statement.
Q. What is Where?
Specifies the conditions that will be used to filter unwanted rows of statement.
TERMINOLOGIES:
Data: static values stored in the database
Information: is data which has been processed
Null: is missing or unknown value. Null does not represent spaces or zeroes.
Table: The primary structure in a database and represents a single subject.
Data Table: A table which stores values.
Field: Is the smallest structure of the database contains the attributes of the data table should contain only one valuer.
Record: Is a unique instance of an item(row) in a table. The record is composed of all the fields in a table.
View: A virtual table created from fields from one or more tables.
Why we use view: 1.DBA creates view for security purpose.
2. Retrieve the data
Data Integrity: Refers to the validity, consistency and accuracy of the data.
Primary Key: Refers to the field of a table which uniquely identifies each row and the table.
Index: Indexes are optional structures associated with table.
Q. What is a Character operation? Ans: Is/is not/like/-/%/=
Q. What Logical Operator? Ans:
Q. What is Date Operator? Ans: =/>/=
Q. What is Special Operator? Ans: Between(Search range)
Q. What is Sequence? A sequence is a database object that generates unique sequential values.
Q. What is Synonym? A synonym is an alias for any schema object such as a table or view.
No comments:
Post a Comment