Welcome Programmer: Database And SQL

Data :- Raw facts and figures which are useful to an organization. We cannot take decisions on the basis of data.

Information:- Well processed data is called information. We can take decisions on the basis of information

Field:- Set of characters that represents specific data element.

Record: Collection of fields is called a record. A record can have fields of different data types.

File: Collection of similar types of records is called a file.

Table: Collection of rows and columns that contains useful data/information is called a table. A table generally refers to the passive entity which is kept in secondary storage device.

Relation: Relation (collection of rows and columns) generally refers to an active entity on which we can perform various operations.

Database: Collection of logically related data along with its description is termed as database.

Tuple: A row in a relation is called a tuple.

Attribute: A column in a relation is called an attribute. It is also termed as field or data item.

Degree: Number of attributes in a relation is called degree of a relation.

Cardinality: Number of tuples in a relation is called cardinality of a relation.

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.

Foreign Key: Foreign Key is a key that is defined as a primary key in some other relation. This key is used to enforce referential integrity in RDBMS.

Candidate Key: Set of all attributes which can serve as a primary key in a relation.

Alternate Key: All the candidate keys other than the primary keys of a relation are alternate keys for a relation.

DBA: Data Base Administrator is a person (manager) that is responsible for defining the data base schema, setting security features in database, ensuring proper functioning of the data bases etc.

Relational Algebra

The relation algebra is the collection of operations on relations. Each operation takes one or more relations (tables) and produces another relation as its result. The operations defined in relational algebra are select, project, Cartesian product, union, set difference, set interception, natural join, division etc.

1. Select operation(denoted by σ ):- select operation is used to select rows from a elation <"

Let us consider the table item

ItemNo	Item_Name	Price
I1	Milk	10
I2	Bread	15
I3	Ice Cream	25
I4	Namkeen	20
I5	Cake	10

2. Project Operation (denoted by π):- Project operation select columns from a relation.

Consider above table Item

To display item name & price of all items from Item table we can write

π _{Item_Name,
Price} (Item)

Result will be

Item_Name	Price
Milk	10
Bread	15
Ice Cream	25
Namkeen	20
Cake	10

3. The Cartesian product operation (denoted by X ):- the Cartesian product of relation A and B is written as A X B. The Cartesian product yield a new relation having degree (Degree of A + Degree of B) and Cardinality (cardinality of A X Cardinality of B)

Consider the following table student and instructor

The Cartesian product Student X Instructor result in following relation

Adno	Stu_Name	Passed	Id	Inst_name	Subject
1023	Ajay	Y	101	Manoj	CS
1023	Ajay	Y	102	Subhash	ACC
6151	Sunil	N	101	Manoj	CS
6151	Sunil	N	102	Subhash	ACC
7575	Vinay	y	101	Manoj	CS
7575	Vinay	y	102	Subhash	ACC

4. The Union Operation (denoted by U):- it produces a relation that contains tuples from both operand relations.

Consider the following relations science and commerce

The result of Science U Commerce will be as follows

Adno	Name	Class
2190	Amit	XII
2345	Nihan	XII
5467	ajay	XI
5423	Sanjay	XII
7665	sumit	XI

5. The Set Difference Operation (Denoted by - ):- allows to find tuples that are in one relation but not in another relation.

Consider above relation science and commerce

The result of Science - Commerce will be as follows

Adno	Name	Class
2190	Amit	XII
5467	ajay	XI

6. The Set Interception Operation (denoted by ∩) :-Set Interception operation finds tuples that are common to the two operand relations.

Consider above relation science and commerce

The result of Science ∩ Commerce will be as follows

Adno	Name	Class
2345	Nihan	XII

Structured Query Language

SQL is a non-procedural language that is used to create, manipulate and process the databases(relations).

Characteristics of SQL

Ø It is very easy to learn and use.

Ø Large volume of databases can be handled quite easily.

Ø It is non-procedural language. It means that we do not need to specify the procedures to accomplish a task but just to give a command to perform the activity.

Ø SQL can be linked to most of other high level languages that makes it first choice for the database programmers.

Processing Capabilities of SQL

The following are the processing capabilities of SQL

1. Data Definition Language (DDL)

DDL contains commands that are used to create the tables, databases, indexes, views, sequences and

synonyms etc.

e.g: Create table, create view, create index, alter table etc.

2. Data Manipulation Language (DML)

DML contains command that can be used to manipulate the data base objects and to query the databases for information retrieval.

e.g Select, Insert, Delete, Update etc.

3. Data Control Language:

This language is used for controlling the access to the data. Various commands like GRANT, REVOKE etc are available in DCL.

4. Transaction Control Language (TCL)

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

Data types of SQL

Support the following data types

Data Type	Syntax	Description	Example
NUMBER	Number(n,d)	· Used to store a numeric value in a field/column · Where n specifies the number of digits and d specifies the number of digits after the decimal point.	Amt Number(6,2)
CHAR	Char (size)	Used to store fixed length string of length size	Name Char(20)
VARCHAR / VARCHAR2	varchar(size) / varchar2(size)	Used to store variable length string up to length size	Address Varchar2(30)
DATE	DATE	Used to store Date	DOB Date
LONG	LONG	This data type is used to store variable length strings of upto 2 GB size	Accno LONG
RAW/LONG RAW	RAW(bytes)/ LONG RAW(bytes)	Used to store binary data (images/pictures/animation/clips etc.) up to the size bytes	Address Raw(500)

1&2 mark questions

Q1. Define the terms:

(i) Database Abstraction

(ii) Data inconsistency

(iii) Conceptual level of database implementation/abstraction

(iv) Primary Key

(v) Candidate Key

(vi) Relational Algebra

(vii) Domain

Ans:. Define the terms:

i. Database Abstraction

Ans: Database system provides the users only that much information that is required by them, and hides certain details like, how the data is stored and maintained in database at hardware level. This concept/process is Database abstraction.

ii. Data inconsistency

Ans: When two or more entries about the same data do not agree i.e. when one of them stores the updated information and the other does not, it results in data inconsistency in the database.

iii. Conceptual level of database implementation/abstraction

Ans: It describes what data are actually stored in the database. It also describes the relationships existing among data. At this level the database is described logically in terms of simple data-structures.

iv. Primary Key

Ans : It is a key/attribute or a set of attributes that can uniquely identify tuples within the relation.

v. Candidate Key

Ans : All attributes combinations inside a relation that can serve as primary key are candidate key as they are candidates for being as a primary key or a part of it.

vi. Relational Algebra

Ans : It is the collections of rules and operations on relations(tables). The various operations are selection, projection, Cartesian product, union, set difference and intersection, and joining of relations.

vii. Domain

Ans : it is the pool or collection of data from which the actual values appearing in a given column are drawn.

2 marks Practice questions

1. What is relation? What is the difference between a tuple and an attribute?

2. Define the following terminologies used in Relational Algebra:

(i) selection (ii) projection (iii) union (iv) Cartesian product

3. What are DDL and DML?

4. Differentiate between primary key and candidate key in a relation?

5. What do you understand by the terms Cardinality and Degree of a relation in relational database?

6. Differentiate between DDL and DML. Mention the 2 commands for each category.

Database and SQL : 6 marks questions

1. Write SQL Command for (a) to (d) and output of (g)

TABLE : GRADUATE

S.NO	NAME	STIPEND	SUBJECT	AVERAGE	DIV
1	KARAN	400	PHYSICS	68	I
2	DIWAKAR	450	COMP Sc	68	I
3	DIVYA	300	CHEMISTRY	62	I
4	REKHA	350	PHYSICS	63	I
5	ARJUN	500	MATHS	70	I
6	SABINA	400	CHEMISTRY	55	II
7	JOHN	250	PHYSICS	64	I
8	ROBERT	450	MATHS	68	I
9	RUBINA	500	COMP Sc	62	I
10	VIKAS	400	MATHS	57	II

a. List the names of those students who have obtained DIV I sorted by NAME.

b. Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year assuming that the STIPEND is paid every month.

c. To count the number of students who are either PHYSICS or COMPUTER SC graduates.

d. To insert a new row in the GRADUATE table: 11,”KAJOL”, 300, “computer sc”, 75, 1

e. Give the output of following sql statement based on table GRADUATE:

(i) Select MIN(AVERAGE) from GRADUATE where SUBJECT=”PHYSICS”;

(ii) Select SUM(STIPEND) from GRADUATE WHERE div=2;

(iii) Select AVG(STIPEND) from GRADUATE where AVERAGE>=65;

(iv) Select COUNT(distinct SUBJECT) from GRADUATE;

Sol :

a. SELECT NAME from GRADUATE where DIV = ‘I’ order by NAME;

b. SELECT NAME,STIPEND,SUBJECT, STIPEND*12 from GRADUATE;

c. SELECT SUBJECT,COUNT(*) from GRADUATE group by SUBJECT having SUBJECT=’PHYISCS’ or SUBJECT=’COMPUTER SC’;

d. INSERT INTO GRADUATE values(11,’KAJOL’,300,’COMPUTER SC’,75,1);

e. (i) 63

(ii) 800

(iii) 475

(iv) 4

2. Consider the following tables Sender and Recipient. Write SQL commands for the statements (i) to (iv) and give the outputs for SQL queries (v) to (viii).

Sender

SenderID	SenderName	SenderAddress	City
ND01	R Jain	2, ABC Appls	New Delhi
MU02	H Sinha	12 Newtown	Mumbai
MU15	S Jha	27/A, Park Street	Mumbai
ND50	T Prasad	122-K,SDA	New Delhi

Recipients

RecID	SenderID	RecName	RecAddress	recCity
KO05	ND01	R Bajpayee	5, Central Avenue	Kolkata
ND08	MU02	S Mahajan	116, A-Vihar	New Delhi
MU19	ND01	H Singh	2A, Andheri East	Mumbai
MU32	MU15	P K Swamy	B5, C S Terminals	Mumbai
ND48	ND50	S Tripathi	13, BI D Mayur Vihar	New delhi

(i) To display the names of all Senders from Mumbai

Ans. SELECT sendername from Sender

where sendercity=’Mumbai’;

(ii) To display the RecIC, Sendername, SenderAddress, RecName, RecAddress for every

Recipient.

Ans. Select R.RecIC, S.Sendername, S.SenderAddress, R.RecName, R.RecAddress

from Sender S, Recepient R

where S.SenderID=R.SenderID ;

(iii) To display Recipient details in ascending order of RecName

Ans. SELECT * from Recipent ORDER By RecName;

(iv) To display number of Recipients from each city

Ans. SELECT COUNT( *) from Recipient

Group By RecCity;

(v) SELECT DISTINCT SenderCity from Sender;

Ans.

SenderCity

Mumbai

New Delhi

(vi) SELECT A.SenderName, B.RecName From Sender A, Recipient B

Where A.SenderID = B.SenderID AND B.RecCity =’Mumbai’;

Ans. A.SenderName B.RecName

R Jain H Singh

S Jha P K Swamy

(vii) SELECT RecName, RecAddress From Recipient

Where RecCity NOT IN (‘Mumbai’, ‘Kolkata’) ;

Ans. RecName RecAddress

S Mahajan 116, A Vihar

S Tripathi 13, BID, Mayur Vihar

(viii) SELECT RecID, RecName FROM Recipent

Where SenderID=’MU02’ or SenderID=’ND50’;

Ans. RecID RecName

ND08 S Mahajan

ND48 STripathi

3. Write SQL command for (a) to (f) on the basis of the table SPORTS

Table: SPORTS

Student NO	Class	Name	Game1	Grade	Game2	Grade2
10	7	Sammer	Cricket	B	Swimming	A
11	8	Sujit	Tennis	A	Skating	C
12	7	Kamal	Swimming	B	Football	B
13	7	Venna	Tennis	C	Tennis	A
14	9	Archana	Basketball	A	Cricket	A
15	10	Arpit	Cricket	A	Atheletics	C

a. Display the names of the students who have grade ‘C’ in either Game1 or Game2 or both.

b. Display the number of students getting grade ‘A’ in Cricket.

c. Display the names of the students who have same game for both Game1 and Game2.

d. Display the games taken up by the students, whose name starts with ‘A’.

e. Add a new column named ‘Marks’.

f. Assign a value 200 for Marks for all those who are getting grade ‘B’ or grade ‘A’ in both Game1 and Game2.

Ans : a) SELECT Name from SPORTS where grade=’C’ or Grade2=’C’;

b) SELECT Count(*) from SPORTS where grade=’A’;

c) SELECT name from SPORTS where game1 = game2;

d) SELECT game,game2 from SPORTS where name like ‘A%’;

e) ALTER TABLE SPORTS add (marks int(4));

f) UPDATE SPORTS set marks=200 where grade=’A’;

4. Consider the following tables Stationary and Consumer. Write SQL commands for the statement (i) to (iv) and output for SQL queries (v) to (viii):

Table: Stationary

S_ID	StationaryName	Company	Price
DP01	Dot Pen	ABC	10
PL02	Pencil	XYZ	6
ER05	Eraser	XYZ	7
PL01	Pencil	CAM	5
GP02	Gel Pen	ABC	15

Table: Consumer

C_ID	ConsumerName	Address	S_ID
01	Good Learner	Delhi	PL01
06	Write Well	Mumbai	GP02
12	Topper	Delhi	DP01
15	Write & Draw	Delhi	PL02
16	Motivation	Banglore	PL01

(i) To display the details of those consumers whose Address is Delhi.

(ii) To display the details of Stationary whose Price is in the range of 8 to 15. (Both Value included)

(iii) To display the ConsumerName, Address from Table Consumer, and Company and Price from table Stationary, with their corresponding matching S_ID.

(iv) To increase the Price of all stationary by 2.

(v) SELECT DISTINCT Address FROM Consumer;

(vi) SELECT Company, MAX(Price), MIN(Price), COUNT(*) from Stationary GROUP BY Company;

(vii) SELECT Consumer.ConsumerName, Stationary.StationaryName, Stationary.Price FROM Strionary, Consumer WHERE Consumer.S_ID=Stationary.S_ID;

(viii) Select StationaryName, Price*3 From Stationary;

5. Consider the following tables GARMENT and FABRIC. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii).

Table : GARMENT

GCODE	DESCRIPTION	PRICE	FCODE	READYDATE
10023	PENCIL SKIRT	1150	F03	19–DEC–08
10001	FORMAL SHIRT	1250	F01	12–JAN–08
10012	INFORMAL SHIRT	1550	F02	06–JUN–08
10024	BABY TOP	750	F03	07–APR–07
10090	TULIP SKIRT	850	F02	31–MAR–07
10019	EVENING GOWN	850	F03	06–JUN–08
10009	INFORMAL PANT	1500	F02	20–OCT–08
10007	FORMAL PANT	1350	F01	09–MAR–08
10020	FROCK	850	F04	09–SEP–07
10089	SLACKS	750	F03	20–OCT–08

Table : FABRIC

FCODE	TYPE
F04	POLYSTER
F02	COTTON
F03	SILK
F01	TERELENE

(i) To display GCODE and DESCRIPTION of a each dress in descending order of GCODE.

(ii) To display the details of all the GARMENTs, which have READYDATE in between 08–DEC–07 and 16–JUN–08 (inclusive of both the dates).

(iii) To display the average PRICE of all the GARMENTs, which are made up of FABRIC with FCODE as F03.

(iv) To display FABRIC wise highest and lowest price of GARMENTs from DRESS table. (Display FCODE of each GARMENT along with highest and lowest price)

(v) SELECT SUM (PRICE) FROM GARMENT WHERE FCODE= ‘F01’;

(vi) SELECT DESCRIPTION, TYPE FROM GARMENT, FABRIC WHERE GARMENT.FCODE = FABRIC. FCODE AND GARMENT. PRICE>=1260;

(vii) SELECT MAX (FCODE) FROM FABRIC;

(viii) SELECT COUNT (DISTINCT PRICE) FROM FABRIC;