rdbms lab file
TRANSCRIPT
-
7/29/2019 Rdbms Lab File
1/42
INDEX
Queries:
1. CREATE TABLE
2. INSERT
3. ALTER TABLE
a. ADD COLUMN
b. MODIFY COLUMN
c. DROP COLUMN
4. UPDATE
5. DROP TABLE
6. DELETE
7. TRUNCATE TABLE
8. LIKE and NOT LIKE
9. IN and NOT IN
10.ORDER BY
11.Primary Key
12.Foreign Key
13.GROUP BY
Experiments:
1. To write the queries for the retrieval of required data from the given tables.
2. To write a program in PL/SQL to print a string of characters.
3. To write a program in PL/SQL to add two pre-defined numbers.4. To write a program in PL/SQL to add two numbers which are taken as inputs from the
user.
5. To write a program in PL/SQL to subtract two numbers which are taken as inputs from
the user.
6. To write a program in PL/SQL to multiply two numbers which are taken as inputs from
the user.
7. To write a program in PL/SQL to divide two numbers which are taken as inputs from the
user.
-
7/29/2019 Rdbms Lab File
2/42
'Create Table' Query
Description:
The CREATE TABLE command is used to specify a new relation by giving it a name and
specifying its attributes and constraints. The attributes are specified first, and each attribute is
given a name, a data type to specify its domain of values, and any attribute constraints such as
NOT NULL. The key, entity integrity, and referential integrity constraints can be specified
within the CREATE TABLE statementafter the attributes are declared.
Syntax:
create tabletablename
(
attributename1 datatype(range) constraint1 constraint2,
attributename2datatype(range),
attributename3 datatype(range) constraint,
);
-
7/29/2019 Rdbms Lab File
3/42
Example:
-
7/29/2019 Rdbms Lab File
4/42
'Insert' Query
Description:
In its simplest form, INSERT is used to add a single tuple to a relation. We must specify the
relation name and a list of values for the tuple. The values should be listed in the same orderin
which the corresponding attributes were specified in the CREATE TABLE command.
A second form of the INSERT statement allows the user to specify explicit attribute names that
correspond to the values provided in the INSERT command. This is useful if a relation has
many attributes, but only a few of those attributes are assigned values in the new tuple.
Another form of INSERT command allows us to insert the values of a new tuple in the format of
a form. This type of INSERT query is executed using the '&' sign. The syntax of this type of
query is stated below.
Syntax:
INSERT without &:
insert intotablename(attriibute1, attribute2, ) values(value1, value2, value3, );
INSERT with &:
insert intotablename(attriibute1, attribute2, ) values(&attribute1, &attribute 2, &attribute3, );
-
7/29/2019 Rdbms Lab File
5/42
Example:
INSERT without '&':
-
7/29/2019 Rdbms Lab File
6/42
INSERT with '&':
-
7/29/2019 Rdbms Lab File
7/42
'Alter Table' Query
Description:
The definition of a base table can be changed by using the ALTER TABLE command, which is a
schema evolution command. The possible alter table actions include adding or dropping a
column (attribute), changing a column definition, and adding or dropping table constraints. It isalso possible to alter a column definition by dropping an existing default clause or by defining a
new default clause. Also, one can change the constraints specified on a table by adding or
dropping a constraint. To be dropped, a constraint must have been given a name when it was
specified.
Syntax:
Add Column:
alter tabletablename add (attributename datatype(range) constraint);
Modify Column:
alter tabletablename modify (attributename datatype(range));
Drop Column:
alter tabletablename drop column attributename;
-
7/29/2019 Rdbms Lab File
8/42
Example:
Add Column:
-
7/29/2019 Rdbms Lab File
9/42
Modify Column:
-
7/29/2019 Rdbms Lab File
10/42
Drop Column:
-
7/29/2019 Rdbms Lab File
11/42
'Update' Query
Description:
The UPDATEcommand is used to modify attribute values of one or more selected tuples. A
WHERE-clause in the UPDATE command selects the tuples to be modified from a single
relation. However, updating a primary key value may propagate to the foreign key values of
tuples in other relations if such a referential triggered action is specified in the referential
integrity constraints of the DDL. An additional SET-clause specifies the attributes to be modified
and their new values.
Syntax:
update tablename set attributename1 = value1, attributename2= value2 where attributename =value3;
-
7/29/2019 Rdbms Lab File
12/42
Example:
Before Updating:
-
7/29/2019 Rdbms Lab File
13/42
After Updating:
-
7/29/2019 Rdbms Lab File
14/42
'Drop Table' Query
Description:
If a base relation within a schema is not needed any longer, the relation and its definition can be
deleted by using the DROP TABLE command.
If the RESTRICT option is chosen instead of CASCADE, a table is dropped only if it is not
referenced in any constraints (for example, by foreign key definitions in another relation) or
views. With the CASCADE option, all such constraints and views that reference the table are
dropped automatically from the schema, along with the table itself. The RESTRICT option is the
default option for the DROP TABLE command.
Syntax:
drop tabletablename;
-
7/29/2019 Rdbms Lab File
15/42
Example:
-
7/29/2019 Rdbms Lab File
16/42
'Delete' Query
Description:
The DELETE command removes tuples from a relation. It includes a WHERE-clause to select
the tuples to be deleted.
Tuples are explicitly deleted from only one table at a time. However, the deletion may propagate
to tuples in other relations if referential triggered actions are specified in the referential integrity
constraints of the DDL.
Depending on the number of tuples selected by the condition in the WHERE-clause, zero, one,
or several tuples can be deleted by a single DELETE command. A missing WHERE-clause
specifies that all tuples in the relation are to be deleted; however, the table remains in the
database as an empty table.
Syntax:
Delete from tablename where attributename = value;
-
7/29/2019 Rdbms Lab File
17/42
Example:
Before Deletion:
-
7/29/2019 Rdbms Lab File
18/42
After Deletion:
-
7/29/2019 Rdbms Lab File
19/42
'Truncate Table' Query
Description:
The TRUNCATE TABLE command is used to delete all the data of the table while retaining the
structure of the table.
Syntax:
truncate tabletablename;
-
7/29/2019 Rdbms Lab File
20/42
Example:
-
7/29/2019 Rdbms Lab File
21/42
'Like' and 'Not Like' Queries
Description:
A feature of the SELECT query allows comparison conditions on only parts of a character string,
using the LIKE comparison operator. Partial strings are specified by using two reserved
characters: % replaces an arbitrary number of characters, and the underscore ( _ ) replaces a
single character. All the values that satisfy the LIKE condition in the given columns of the table
are retrieved and displayed.
NOT LIKE comparison operator is an exact opposite of the LIKE operator. It helps in retrieval of
all the values in the given columns of the table which don't satisfy the condition specified in theNOT LIKE operator.
Syntax:
Like:
select colname1, colname2, fromtablename where attribute1 like %constant%;
Not Like:
select colname1, colname2, fromtablename where attribute1 not like %constant%;
-
7/29/2019 Rdbms Lab File
22/42
Example:
Like:
-
7/29/2019 Rdbms Lab File
23/42
Not Like:
-
7/29/2019 Rdbms Lab File
24/42
'IN' and 'Not IN' Queries
Description:
The comparison operator IN compares a value v with a set (or multiset) of values V and
evaluates to TRUEif v is one of the elements in V. The IN operator can also compare a tuple of
values in parentheses with a set or multiset of union-compatible tuples. It retrieves all the values
that satisfy the IN condition.
NOT IN comparison operator is an exact opposite of the IN operator. It helps in retrieval of all
the values which don't satisfy the condition specified in the NOT IN operator.
Syntax:
IN:
select colname1, colname2, fromtablename where attribute1 in (value1, value2);
Not IN:
select colname1, colname2, fromtablename where attribute1 not in (value1, value2);
-
7/29/2019 Rdbms Lab File
25/42
Example:
IN:
-
7/29/2019 Rdbms Lab File
26/42
Not IN:
-
7/29/2019 Rdbms Lab File
27/42
'Order By' Query
Description:
SQL allows the user to order the tuples in the result of a query by the values of one or more
attributes, using the ORDER BY-clause.The default order is in ascending order of values. Wecan specify the keyword DESC if we want a descending order of values. The keyword ASC can
be used to specify ascending order explicitly.
Syntax:
Ascending Order:
select colname1, colname2, fromtablename where attribute1 = value1, attribute2= value2order by attribute asc;
Descending Order:
select colname1, colname2, fromtablename where attribute1 = value1, attribute2= value2order by attribute desc;
-
7/29/2019 Rdbms Lab File
28/42
Example:
Ascending Order:
-
7/29/2019 Rdbms Lab File
29/42
Descending Order:
-
7/29/2019 Rdbms Lab File
30/42
Primary Key
Description:
It is common to designate one of the columns in the relation as the PRIMARY KEY of the
relation. This is the column whose values are used to identifytuples in the relation.
The ENTITY INTEGRITY CONSTRAINTstates that no primary key value can be null and must
be unique. This is because the primary key value is used to identify individual tuples in a
relation; having null values or repeating values for the primary key implies that we cannot
identify some tuples.
Syntax:
create tabletablename
(
attributename1 datatype(range) constraint1 constraint2,
attributename2datatype(range),
attributename3 datatype(range) constraint
primary key (attributename1, attributename2)
);
-
7/29/2019 Rdbms Lab File
31/42
Example:
-
7/29/2019 Rdbms Lab File
32/42
Foreign Key
Description:
The conditions for a foreign key specify a referential integrity constraint between the two relation
schemas R1 and R2. A set of attributes FK in relation schema R1 is a foreign key of R1 that
references relation R2 if it satisfies the following two rules:
1. The attributes in FK have the same domain(s) as the primary key attributes PK of R2; the
attributes FK are said to reference or refer to the relation R2.
2. A value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for some
tuple t2 in the current state r2(R2) or is null. In the former case, we have t1[FK] = t2[PK], and we
say that the tuple t1 references or refers to the tuple t2. R1 is called the referencing relation and
R2 is the referenced relation.
A foreign key can refer to its own relation. For example, the attribute SUPERSSN in
EMPLOYEE refers to the supervisor of an employee; this is another employee, represented by
a tuple in the EMPLOYEE relation. Hence, SUPERSSN is a foreign key that references the
EMPLOYEE relation itself.
Syntax:
create tabletablename
(
attributename1 datatype(range) constraint1 constraint2,
attributename2datatype(range),
attributename3 datatype(range) constraint
foreign key (attributename1) references tablename(attributename));
-
7/29/2019 Rdbms Lab File
33/42
Example:
-
7/29/2019 Rdbms Lab File
34/42
'Group By' Query
Description:
The GROUP-BY clause is used in conjunction with the SELECT query. The GROUP BY-clause
specifies the grouping attributes, which should also appear in the SELECT-clause, so that the
value resulting from applying each function to a group of tuples appears along with the value of
the grouping attribute(s).
Syntax:
select colname1, colname2, fromtablename where attribute1 = value1, attribute2= value2
group by attribute;
-
7/29/2019 Rdbms Lab File
35/42
Experiment 1
Aim:
To write the queries for the retrieval of required data from the given tables.
Tables:
Works (p_name, c_name, salary);
Lives (p_name, street, city);
Located_in (c_name, city);
Managers (p_name, m_name) ;
Queries:
1. To select names of the people who work for First Bank Corporation :
select p_name from works where c_name = First Bank Corporation ;
2. To select names and cities of residence of the people who work for First Bank
Corporation :
select works.p_name, lives.city from works, lives where c_name = First Bank
Corporation and works.p_name = lives.p_name ;
3. To select names and address of residence of the people who work for First Bank
Corporation and earn more than 10000 :
select works.p_name, lives.street, lives.city from works, lives where c_name = First
Bank Corporation and salary > 10000 and works.p_name = lives.p_name ;
4. To select names of the people who reside in the same city in which they work :
select lives.p_name from works, lives, located_in where lives.city = located_in.city and
works.p_name = lives.p_name and works.c_name = located_in.c_name ;
5. To select names of the people who do not work for First Bank Corporation :
select p_name from works where p_name not in (select p_name from works where
c_name = First Bank Corporation) ;
-
7/29/2019 Rdbms Lab File
36/42
6. To select names of the people whose salary is more than the salary of every employee
working for Second Bank Corporation :
select p_name from works where salary > all(select salary from works where c_name =
Second Bank Corporation) ;
-
7/29/2019 Rdbms Lab File
37/42
Experiment 2
Aim:
To write a program in PL/SQL to print a string of characters.
Program:
set serveroutput on
begin
dbms_output.print_line(Hello);
end;
Output:
HelloPL/SQL procedure successfully completed.
-
7/29/2019 Rdbms Lab File
38/42
Experiment 3
Aim:
To write a program in PL/SQL to add two pre-defined numbers.
Program:
set serveroutput on
declare
num1 number := 30;
num2 number := 20;
num3 number;
begin
num3 = num1 + num2;
dbms_output.print_line(Sum is: || num3);
end;
Output:
Sum is: 50PL/SQL procedure successfully completed.
-
7/29/2019 Rdbms Lab File
39/42
Experiment 4
Aim:
To write a program in PL/SQL to add two numbers which are taken as inputs from the user.
Program:
set serveroutput on
declare
num1 number := &num1;
num2 number := &num2;
num3 number;
begin
num3 = num1 + num2;
dbms_output.print_line(Sum is: || num3);
end;
Output:
num1 = 30num2 = 20
Sum is: 50PL/SQL procedure successfully completed.
-
7/29/2019 Rdbms Lab File
40/42
Experiment 5
Aim:
To write a program in PL/SQL to subtract two numbers which are taken as inputs from the user.
Program:
set serveroutput on
declare
num1 number := &num1;
num2 number := &num2;
num3 number;
begin
num3 = num1 - num2;
dbms_output.print_line(Difference is: || num3);
end;
Output:
num1 = 30num2 = 20
Difference is: 10PL/SQL procedure successfully completed.
-
7/29/2019 Rdbms Lab File
41/42
Experiment 6
Aim:
To write a program in PL/SQL to multiply two numbers which are taken as inputs from the user.
Program:
set serveroutput on
declare
num1 number := &num1;
num2 number := &num2;
num3 number;
begin
num3 = num1 * num2;
dbms_output.print_line(Product is: || num3);
end;
Output:
num1 = 30num2 = 2
Product is: 60PL/SQL procedure successfully completed.
-
7/29/2019 Rdbms Lab File
42/42
Experiment 7
Aim:
To write a program in PL/SQL to divide two numbers which are taken as inputs from the user.
Program:
set serveroutput on
declare
num1 number := &num1;
num2 number := &num2;
num3 number;
begin
num3 = num1 / num2;
dbms_output.print_line(Quotient is: || num3);
end;
Output:
num1 = 30num2 = 2
Quotient is: 15PL/SQL procedure successfully completed.