Dev Exp 02 DBMS
Dev Exp 02 DBMS
RollNo 2300330109006
Theory &Concepts:
View definition-
The SQL DDL includes commands for defining views.
Transaction Control-SQL includes for specifying the beginning and ending of transactions.
Integrity-
The SQL DDL includes commands for specifying integrity constraints that the data stored in the
database must specify. Updates that violate integrity constraints are allowed.
Authorization-
The SQL DDL includes commands for specifying access rights to relations and views.
The SQL DDL allows specification of not only a set of relations but also information about each
relation, including-
● Schema for each relation
● The domain of values associated with each attribute.
● The integrity constraints.
● The set of indices to be maintained for each relation.
● The security and authorization information for each relation.
● The physical storage structure of each relation on disk.
Name Dev Kumar Srivastav
RollNo 2300330109006
● Varchar(n)-Avariablecharacterlengthstringwithuserspecifiedmaximumlengthn.
● Int- An integer.
● Small integer-A small integer.
● Numeric (p, d)-A Fixed point number with user defined precision.
● Real, double precision-Floating point and double precision floating point numbers with
machine dependent precision.
● Float(n)-A floating point number, with precision of atleast n digits.
● Date-A calendar date containing a(four digit)year, month and day of the month.
● Time-The time of day, in hours, minutes and seconds Eg. Time ’09:30:00’.
● Number-Number is used to store numbers(fixed or floating point).
table-Syntax-
Syntax-
CREATETABLETABLENAME
[(column name, column name, )]
AS SELECT column name, column name FROM table name;
Syntax -
table: Syntax-
Syntax-
tables- Syntax-
table-Syntax-
statement-Syntax-
data-Syntax-
Experiment No.2
Q1.Create the following tables:
i) client_master
ii) Product_master
Column name datatype size
Product_no varchar2
Description
varchar2 Profit_percent
number
Unit_measurevarchar2
Qty_on_hand
number
Reoder_lvlnumber
Sell_price number
Cost_price number
Q3:- On the basis of above two tables answer the following Queries:
i) Find out the names of all the clients.
ii) Retrieve the list of names and cities of all the clients.
iii) List the various products available from the product_master table.
Name Dev Kumar Srivastav
RollNo 2300330109006
vi) Find the products with description as ‘1.44 drive’ and ‘1.22 Drive’.
vii) Find all the products whose sell price is greater than 5000.
viii) Find the list of all clients who stay in city ‘Bombay’ or city ‘Delhi’ or
‘Madras’.
Name Dev Kumar Srivastav
RollNo 2300330109006
ix) Find the product whose selling price is greater than 2000 and less than or
equal to 5000.
x) List the name, city and state of clients not in the state of ‘Maharashtra’.