Database : Collection of information about an organization  in form of tables.

    These tables are logically related with each other

Flat database: all the data is stroed in one file  then such a database is called Flat Database. A Spreadsheet is an example of flat database

Relational Database: When data is stored in multiple tables that are linked via common filed or column, then such database called Relational database

RDBMS : Relational Database Management System. Software or computer program that manages the relational  database effectively and efficiently. E.g MS Access, Base. Oracle, MySQL.

Relation: In RDBMS table is called Relation

Redundancy : duplication  of data

Data Inconsistency : Multiple mismatch copy of same data

( .odb)  : the file extension of base database is .odb

Components of table:

Data Item/Field/Column/Attribute: Column headers containing one type of information

Record/Row/Tuple: Row in a table . Record contain a complete unit of information.

Primary key:  Primary key is a key that can uniquely identifies the records /tuples in a relation. This key can never be duplicated and NULL. 

Composite Primary key: Primary key  that consist  of combination of  two or more columns

Alternative keys : The columns that have the unique  values for each record but are not selected as the primary keys are called alternative keys

Candidate keys: Primary keys and alternative keys together are called the candidate keys.In other words all attributes that are eligible to be a primary key , are called candidate keys.

This key can never be duplicated and NULL. For example rollno field can be primary key for a table student.

Candidate Key: Attributes of a table which can serve as a primary key are called candidate keys. 

Alternate Key: All the candidate keys other than the primary key of a relation 

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

Degree : This refers to the number of attributes in a relation.

Cardinality : This refers to the number of tuples in a relation.

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

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 is form ?   What are the uses of forms? 

Forms are the objects that are used to get data from users in an interactive way. Forms provide a user -friendly interface to enter data and the entered data is stored in the tables of the database.

Using forms you can  display, search, edit, insert and delete records from linked table of the database.

You can easily create forms using Form wizard. You can modify forms in design view

What is a database report ?

A Report is an effective way to  present data in a printed format.

It is a formal , presentable printed document that lists data in a formatted manner.

It is a formatted result of data coming from database objects like tables or queries.

Reports are very useful tools for decision making and analysis. You can create reports from one or more tables.

Reports are an easy way to represent data in a formatted way. You can easily create reports easily using Report wizard.

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.

RELATIONAL OPERATORS CAN BE USED WITH WHERE ARE :    <>,>,>=,<,<=

LOGICAL OPERATORS CAN BE USED WITH WHERE ARE: AND, OR, NOT

OPERATOR CAN BE USED TO SPECIFY LIST OF VALUES FOR CONDITION: IN AND NOT IN

CONDITION BASED ON RANGE CAY BE SPECIFY USING : BETWEEN AND , NOT BETWEEN AND

CONDITION BASED ON PATTERN MATCHES: % SIGN ANY STRING  AND _ FOR SINGLE CHARACTER  WITH LIKE OR NOT LIKE OPERATOR

E.G

Select name, city FROM emp WHERE CITY IN(‘jaipur’,’delhi’);

Select name, city FROM emp WHERE CITY NOT IN(‘jaipur’,’delhi’);

Select name FROM emp WHERE salary BETWEEN 5000 AND 15000;

Select name FROM emp WHERE salary NOT BETWEEN 5000 AND 15000;

Select * FROM emp WHERE (salary>=15000 AND CITY=’delhi’);

Select * FROM emp WHERE (salary>=15000 OR CITY=’delhi’);

Select * FROM emp WHERE (NOT CITY=’delhi’);

Select * FROM emp WHERE PIN NOT LIKE “13%”;

Select * FROM emp WHERE PIN  LIKE “13%”;

Select * FROM emp WHERE empname  LIKE “- - - D”;