Database And SQL Study Notes-1


  • 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
    the data
  • Hierarchical, Network and Relational Model are the three popular data models.
    However, the relational model is more widely used.

Hierarchical Model:

  • 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

Network Model:

  • 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
    access paths.
  • DBTG Codasyl was the first network database.

Relational Model:

  • 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

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

Different constraint
Unique Constraint Primary Key constraint
Default constraint Check constraint

Applying Constraint
Create a student table with filed student id, student name, father‟s name, age, class, adrress.

(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
emp table.

SELECT ename, sal
FROM emp;


Variations of select Command:
Selecting specific Rows……..WHERE clause
SELECT <column-name>[,<column-name>…….]
FROM <table name>
WHERE <condition>;

Example : Display the employee code, their name and their salary who are Manager.

SELECT empno,ename,sal
FROM emp

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

SELECT <column-name>[,<column-name>,……]
FROM <table-name>
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


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;


Logical Operators
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.

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

Syntax :
SELECT <column-name>[,<column-name>…….]
FROM <table name>
WHERE <condition>
ORDER BY <column-name>

Display the list of employee in the descending order of employee code, who is manger

ORDER BY ecode;


The INSERT Command:
The tuples are added to relation using INSERT command of SQL.

INSERT INTO <table-name>[<column list>]
VALUES (<value>,<value>,<value>,…..);

Example :
Enter a new record in student table

INSERT INTO student (sid,sname,fname,age,class,address);


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>
WHERE <condition>;

Delete all the records of employee whose salary is less than 3000

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.

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

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>

Example :
Drop the student table from the database

DROP TABLE student;


Some Example:
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
8085-microprocessor pdf file database-management-system-study-notes-pdf

Try Now – Database Management System MCQs
Practice Now – Database Management System Online Test