SESSION-2

Open Office Base

Database of Open office  is BASE. 

The file extension of BASE database is odb

The Base database is  composed of four components called tables, reports, query  and forms

Report : It is an effective way to represent data in printed format

Form: It is an interface where user can enter, view or change data directly in the table

Query: It is a way to get information from a database as per given condition or criteria.

Short cut key for creating a new database in base ?: Ctrl+N

Short cut key for open an already available database ?: Ctrl+O

There are two ways to create database in base?

(a)From table wizard (b) from Design view

You can easily create forms using ?

Form wizard

There are two ways to  create a query in Base ?

1.Using Query wizard (2) Using Query Design view

A base database is made up of four components named as?

Tables, reports, Queries and forms

There are two type of  Queries ?

summary and detailed Query

keywords or Operators are used to specify Criteria in SQL Query ?

LIKE, NOT LIKE, IN, NOT IN, AND,OR , NOT, BETWEEN, >,>=,<.<=,!= etc

Database Servers are referred as ?

Backend servers

Which command is used to create relationships among tables in Base? 

Relationship option in Tools menu

Write Commands to perform the following actions ?

Add new Record : record command in insert menu

Delete record: Delete command  in Edit menu

Creating and editing relationship: Relationship in tools menu

Sort records: click on column and click on sort  ascending or sort descending button

What are commonly used data type in Base data type:

INT: to store integer values

DECIMAL: to store  real or decimal values with  exact precision.

CHAR: to store fixed length  string with a maximum size of 255.

VARCHAR: to store variable length string a maximum size of 65,536.

DATE: store date values in the format YYYY-MM-DD

DATETIME: store combined date/time values in the YYYY-MM-DD HH:MM:SS format

What is the use of  binary data type in DBMS ?

BINARY TYPES:- Binary types are used for storing data in binary formats. It can be used for storing photos, music files or (in general file of any format) etc.

What do you mean by Aggregate Functions ?

They produce aggregate values of records. Functions produce a single value from a set of values. They are also called summary functions. These functions are used in summary Queries to group data in Queries

Some Functions are :COUNT, SUM, MAX,MIN,AVG

What is sorting ? How is it useful ?

Sorting is a process of arranging records in ascending or descending order on the basis of one or more columns . Sorting makes data easier to understand, analyse and visualise

Which command is used to create relationships among tables in Base? 

Relationship option in Tools menu

What is referential integrity ?

Ans: It is a system of rules that a DBMS used to ensure that the relationships between records in related tables are valid and that users do not accidentally delete or change related data.

What is SQL ? What are different categories of commands in SQL ?

SQL structured query language is a non procedural language  and set of commands that is used to  create, manipulate and process the database. 

1. Data Definition Language (DDL) DDL contains commands for defining  and modification of database objects  like  tables, databases, indexes, views, sequences and synonyms. Commands include : create , alter, drop, grant, revoke etc.

2. Data Manipulation Language (DML)  command that can be used to manipulate the database objects and to query the databases for information retrieval. e.g Select, Insert, Delete, Update etc. 

3. Transaction Control Language (TCL)

TCL include commands to control the transactions in a database system. The commonly used commands in TCL are COMMIT, ROLLBACK etc. 

What is the use of  binary data type in DBMS ?

BINARY TYPES:- Binary types are used for storing data in binary formats. It can be used for storing photos, music files or (in general file of any format) etc.

Explain the use of the following keywords ?

ALL , WHERE, BETWEEN, LIKE, IN , NOT IN, USE, NULL

ALL:  used with SELECT to display all records

E.g  SELECT all city from student;

DISTINCT: to eliminate duplicate column values from table

E.g SELECT DISTINCT city from student:

WHERE ; is used with SELECT command to specify the criteria in  SQL Query 

BETWEEN:  The BETWEEN operator defines a range of values that the column values must fall in to make the condition true. The range includes both lower value and upper value.

e.g. to display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is: 

SELECT ECODE , ENAME ,GRADE 

  FROM EMPLOYEE 

WHERE GROSS BETWEEN 40000 AND 50000 ; 

LIKE:

LIKE operator is used for pattern matching in SQL. Patterns are described using two special wildcard characters:

 1. percent(%) –The % character matches any substring.

 2. Underscore ( _ ) –The _ character matches any character. 

e.g. to display names of employee whose name starts with R in EMPLOYEE table, the command is :

SELECT ENAME 

FROM EMPLOYEE

WHERE ENAME LIKE ‘R%’ ; 

IN : To specify a list of values, the IN operator is used. The IN operator selects values that match any value in a given list of values. E.g. 

SELECT * FROM EMPLOYEE 

     WHERE GRADE IN (‘A1’ , ‘A2’);

NULL:  column having no value is said to have NULL value.

USE : to open or select database

Creating table: 

CREATE TABLE employee (ECODE integer , ENAME varchar(20) , GENDER char(1) , GRADE char(2) , GROSS integer ) ;

Inserting record: 

INSERT INTO employee VALUES(1001 , ‘Ravi’ , ‘M’ , ‘E4’ , 50000); or

INSERT INTO employee (ECODE , ENAME , GENDER , GRADE , GROSS) VALUES(1001 , ‘Ravi’ , ‘M’ , ‘E4’ , 50000);

To select only ENAME, GRADE and GROSS column from student table :

 SELECT ENAME , GRADE , GROSS FROM EMPLOYEE ;

to retrieve everything (all columns) from a table student :

SELECT * FROM student ;

To display details of employee whose name ends with ‘y’. 

SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘%y’ ;

To display names of employee whose name starts with R in EMPLOYEE table, the command is : 

SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE ‘R%’ ;

To display details of employee whose second character in name is ‘e’.

 SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘_e%’ ; 

To display details of employee whose  grade value is A1 or A2 : 

SELECT * FROM EMPLOYEE WHERE GRADE IN (‘A1’ , ‘A2’); 

To display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is:

SELECT ECODE , ENAME ,GRADE FROM EMPLOYEE WHERE GROSS BETWEEN 40000 AND 50000 ;

To display the details of employees in EMPLOYEE table in alphabetical order, we use command :

SELECT * FROM EMPLOYEE ORDER BY ENAME ;

display list of employee in descending alphabetical order whose salary is greater than 40000.

SELECT ENAME FROM EMPLOYEE WHERE GROSS > 40000 ORDER BY ENAME desc ;

Change the salary of employee in EMPLOYEE table having employee code 1009 to 55000. 

UPDATE EMPLOYEE SET GROSS = 55000 WHERE ECODE = 1009 ; 

Update the salary to 58000 and grade to B2 for those employee have employee code is 1001.

UPDATE EMPLOYEE SET GROSS = 58000, GRADE=’B2’ WHERE ECODE = 1009 ; Double the salary of employees having grade as ‘A1’ or ‘A2’ . 

UPDATE EMPLOYEE SET GROSS = GROSS * 2  WHERE GRADE=’A1’ OR GRADE=’A2’ ;

to remove the details of those employee from EMPLOYEE table whose grade is A1. DELETE FROM EMPLOYEE WHERE GRADE =’A1’ ;

To delete all records from employee table : 

DELETE FROM EMPLOYEE ;

To add a new column ADDRESS to the EMPLOYEE table, we can write command as : 

ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(50);

In table EMPLOYEE , change the datatype of GRADE column from CHAR(2) to VARCHAR(2). 

ALTER TABLE EMPLOYEE MODIFY GRADE VARCHAR(2);

In table EMPLOYEE , change the column ENAME to EM_NAME and data type from VARCHAR(20) to VARCHAR(30).

ALTER TABLE EMPLOYEE CHANGE ENAME EM_NAME VARCHAR(30);

To delete column GRADE from table EMPLOYEE, we will write :

ALTER TABLE EMPLOYEE DROP GRADE ;

DataType in SQL: 

Numeric: 1. INT 2. SMALLINT 3. FLOAT or DECIMAL or NUMERIC 

2. date & time: 1. date 2. time

 3. string: 1. CHAR 2. VARCHAR // CHAR , VARACHAR,DATE AND TIME VALUE must enclosed in single or double quotes.

  GROUP BY :Clause is used with SELECT to divide the table into groups. Groups can be done by a column name. If you wish to perform calculations on that column. You can use AGGRIGATE FUNCTIONS SUCH AS COUNT, SUM, AVG, MAX, MIN on columns etc., functions on the grouped column. Aggregate function produces a value for each group.

The HAVING Clause: is used to give condition with GROUP BY clause. The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

UPDATE command is used to update records in a table.

ALTER command is used to ADD , MODIFY or DROP column from a table in database

DELETE command is used to delete records from a table

DROP command is used to delete database or table 

ORDER BY : clause is used to order the records in Ascending or descending order. ASC keyword for ascending and DESC keyword is used for descending order

Constraints: 

Constraints are the conditions that can be enforced on the attributes of a relation. 

The constraints come in play whenever we are trying to insert, delete or update a record in a relation

/ Unique constraint 

Means that the values under that column are always unique. E.g.Roll_no number (3) unique 

Primary key constraint

  means that a column cannot have duplicate values and not even a null value. e.g. Roll_no number (3) primary key 

The main difference between unique and primary key constraint is that a column specified as unique may have null value but primary key constraint does not allow null values in the column. 

Check constraint 

limits the values that can be inserted into a column of a table. e.g. marks number(3) check(marks>=0) The above statement declares marks to be of type number and while inserting or updating the value in marks it is ensured that its value is always greater than or equal to zero. 

Default constraint 

Is used to specify a default value to a column of a table automatically. This default value will be used when user does not enter any value for that column. e.g. balance number(5)default = 0

JOIN :

Clause is used to combine rows from two or more tables, based on a related/common column between them.

 The join clause is used to combine tables based on a common column and a join condition.

 An equi join is a type of join that combines tables based on matching values in specified columns. The column names do not need to be the same.The resultant table contains repeated columns. It is possible to perform an equi join on more than two tables.

 The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only.