- A database consists of a number of tables. Each table comprises of rows(records) and
columns(attributes). Each record contains values for the corresponding attributes. The
values of the attributes for a record are interrelated. For example, different cars have
different values for the same specifications (length, color, engine capacity, etc.).
- In the database oriented approach, we store the common data in one table and access it
from the required tables. Thus the redundancy of data decreases.
- The database oriented approach supports multiple views of the same data. For
example, a clerk may only be able to see his details, whereas the manager can view the
details of all the clerks working under him.
- Multiple views of the same database may exist for different users. This is defined in the
view level of abstraction.
- The logical level of abstraction defines the type of data that is stored in the database
and the relationship between them.
- The design of the database is known as the database schema.
- The instance of the database is the data contained by it at that particular moment.
- The Database Administrator has the total control of the database and is responsible for
the setting up and maintaining the database.
- A Data Model is the methodology used by a particular DBMS to organize and access
- Hierarchical, Network and Relational Model are the three popular data models.
However, the relational model is more widely used.
- The hierarchical model was developed by IBM in 1968.
- The data is organize in a tree structure where the nodes represent the records and the
branches of the tree represent the fields.
- Since the data is organized in a tree structure, the parent node has the links to its child
- If we want to search a record, we have to traverse the tree from the root through all its
- parent nodes to reach the specific record. Thus, searching for a record is very time
- The hashing function is used to locate the root.
- SYSTEM2000 is an example of hierarchical database
- Record relationship in the network model is implemented by using pointers.
- Record relationship implementation is very complex since pointers are used. It supports
many-to-many relationships and simplified searching of records since a record has many
- DBTG Codasyl was the first network database.
- The Relational Model, organizes data in the form of independent tables (consisting of rows
and columns) that are related to each other.
- A table consists of a number of rows (records/tuples) and columns (attributes). Each
record contains values for the attributes.
- The degree of the table denotes the number of columns.
- A domain in the relational model is said to be atomic is it consists of indivisible units. For
example, name is not atomic since it can be divided into first name and last name.
- E. F. Codd laid down 12 rules (known as Codd’s 12 rules) that outline the minimum
functionality of a RDBMS. A RDBMS must comply with at least 6 of the rules.
- A Super Key is a of attributes that collectively identify a entity in a entity set. For example,
the bank account number is a super key in the bank accounts table.
- A Candidate Key (also known as Primary Key) is the smallest subset of the super key for
which there does not exist a proper subset that is a super key.
- Out of the multiple candidate keys, only one is selected to be the primary key and the
remaining are alternate keys.
- A foreign key is the primary key of a table that is placed into a related table to represent
one-to-many relationship among these tables.
Structure Query Language
What is SQL?
- When a user wants to get some information from a database file, he can issue a query.
- A query is a user–request to retrieve data or information with a certain condition.
- SQL is a query language that allows user to specify the conditions. (instead of algorithms)
Types of SQL commands:
- Data Definition Language commands (DDL Command): All the commands used to create,
modify or delete physical structure of an object like Table.
For eg. Create, Alter , drop
- Data Manipulation Language command (DML Command): All the commands used to
modify contents of a table are comes under this category.
For eg. : Insert, delete, update commands
- TCL Command: These commands are used to control Transaction of DML commands.
For eg. Commit, rollback
Concept of SQL:
- The user specifies a certain condition.
- The program will go through all the records in the database file and select those records
that satisfy the condition.(searching).
- Statistical information of the data.
- The result of the query will then be stored in form of a table.
Basic structure of an SQL query:
- General Structure SELECT, ALL / DISTINCT, *,AS, FROM, WHERE
- Comparison IN, BETWEEN, LIKE “% _”
- Grouping GROUP BY, HAVING, COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )
- Display Order ORDER BY, ASC / DESC
- Logical Operators AND, OR, NOT
TYPES OF SQL STATEMENTS:
a) DDL (Data Definition Language):- Create ,Alter,Drop.
b) DML (Data Manipulation Language):- Select,Delete,Insert,Update.
c) DCL (Data Control Language):- Grant,Revoke.
d) TCL (Transaction Control Language):– COMMIT,ROLLBACK,SAVEPOINT.
Constraint is a condition applicable on a field or group of fields.
Two types of constraint:
Column Constraint :- apply only to individual column
Table Constraint :- apply to groups of columns
Unique Constraint Primary Key constraint
Default constraint Check constraint
Create a student table with filed student id, student name, father‟s name, age, class, adrress.
CREATE TABLE student (sid char(4) PRIMARY KEY, sname char(20) NOT NULL, fname char(20), age number(2) CHECK (age<20), class char(5) NOT NULL , address char(50));
Select command is a query that is given to produce certain specified information from the database
Select Statement is used as
SELECT <column name>,[,<column name>,……]
FROM <table name>;
Example: Write a query to display the name and salary of the employee in
SELECT ename, sal FROM emp;
Variations of select Command:
Selecting specific Rows……..WHERE clause
FROM <table name>
Example : Display the employee code, their name and their salary who are Manager.
SELECT empno,ename,sal FROM emp WHERE job=‟MANAGER‟;
Searching for NULL (IS NULL Command):
The null value in a column can be searched for in a table using IS NULL in the WHERE Clause
WHERE <column-name> IS NULL;
Example Display the employee code, name and their job whose Dept.No. is Null.
SELECT empno,empname,job FROM emp WHERE DeptNo IS NULL;
IS NOT NULL Command:
Example: Display the name and job of those employees whose dept No is not Null
SELECT ename,job FROM emp WHERE deptno IS NOT NULL;
The logical operators OR, AND, NOT are used to connect search conditions in the WHERE clause.
The uses of logical operators are understand by these following
Display the name of manager whose salary is more than 5000
SELECT ename FROM emp WHERE job=‟MANAGER‟ and sal>5000;
Write a query on the customers table whose output will exclude all customers with rating<=100,
unless they are located in Shimla.
SELECT * FROM customers WHERE rating>100 OR city=‟Shimla‟;
Sorting Result- ORDER BY Clause:
The resulting column can be sorted in ascending and descending order using the ORDER BY
FROM <table name>
ORDER BY <column-name>
Display the list of employee in the descending order of employee code, who is manger
SELECT * FROM emp WHERE job=‟MANAGER‟ ORDER BY ecode;
The INSERT Command:
The tuples are added to relation using INSERT command of SQL.
INSERT INTO <table-name>[<column list>]
Enter a new record in student table
INSERT INTO student (sid,sname,fname,age,class,address); VALUES(101,‟Mohan‟,‟Pawan‟,15,‟8‟,‟Jaipur‟);
sid sname fname age class address
101 Mohan Pawan 15 8 Jaipur
The DELETE Command:
The delete command removes the tuples from the tables. This command remove the entire row
from the table and not the individual field. So no filed argument is needed.
DELETE FROM <table-name>
Delete all the records of employee whose salary is less than 3000
DELETE FROM emp WHERE sal<3000; To delete all the record from the table: DELET FROM<table-name>;
The UPDATE Command:
The UPDATE command is used to changes some values in existing rows. The UPDATE command
specifies the rows to be changed using the WHERE clause, and new data using the SET keyword.
Update the salary of employee to 5000 whose employee code is 1011.
UPDATE emp SET sal=5000 WHERE empno=1011;
The ALTER TABLE Command:
The ALTER command is used to change the definition of existing table.
a)It can be used to add columns to a table.
Syntax (to add a column to a table):
ALTER TABLE <table-name> ADD <column-name>
<data type> <size>;
b)To modify existing columns of a table:
ALTER TABLE <table-name>
MODIFY (Columnname newdatatype (newsize));
To modify column job of table emp to have new width of 30 character
ALTER TABLE emp MODIFY (job char(30));
The DROP Command
The DROP command is used to drop the table from the database. For dropping a table all the
tuples should be deleted first i.e the table should be empty.
DROP TABLE <table-name>
Drop the student table from the database
DROP TABLE student;
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‟ ;
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) ;
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 ;
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) ;
For Offline Study you can Download pdf file from below link