0% found this document useful (0 votes)
12 views9 pages

Dev Exp 02 DBMS

The document outlines the objectives and concepts of SQL, including its components such as DDL, DML, and integrity constraints. It provides syntax for creating tables, inserting data, and retrieving information from databases, along with examples of domain types and specific SQL commands. Additionally, it includes practical exercises for creating tables and querying data related to clients and products.

Uploaded by

Anshul Maurya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
12 views9 pages

Dev Exp 02 DBMS

The document outlines the objectives and concepts of SQL, including its components such as DDL, DML, and integrity constraints. It provides syntax for creating tables, inserting data, and retrieving information from databases, along with examples of domain types and specific SQL commands. Additionally, it includes practical exercises for creating tables and querying data related to clients and products.

Uploaded by

Anshul Maurya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

Name Dev Kumar Srivastav

RollNo 2300330109006

Theory and Concept

Objective: Creating tables and writing Queries in SQL.

Theory &Concepts:

Introduction about SQL-


SQL (Structured Question Language)is an on procedural language, you specify what you want, not
how to get it. A block structured format of English key words is used in this Questionry language. It
has the following components.

DDL (Data Definition Language)-


The SQL DDL provides command for defining relation schemas , deleting relation and modifying
relation schema.

DML(DATA Manipulation Language)-


It includes commands to insert tuples into, delete tuples from and modify tuples in the database.

View definition-
The SQL DDL includes commands for defining views.
Transaction Control-SQL includes for specifying the beginning and ending of transactions.

Embedded SQL and Dynamic SQL-


Embedded and Dynamic SQL define how SQL statements can be embedded within general purpose
programming languages, such as C, C++, JAVA, COBOL, Pascal and Fortran.

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.

Data Definition Language-

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

Domain types in SQL-

The SQL standard supports a variety of built in domain types, including-


● Char (n)- A fixed length character length string with user specified length .

● 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).

DDL statement for creating a

table-Syntax-

Create table table name


(column name datatype(size), column name datatype(size));

Creating a table from a table-

Syntax-

CREATETABLETABLENAME
[(column name, column name, )]
AS SELECT column name, column name FROM table name;

Insertion of data into tables-

Syntax -

INSERT INTO table name [(column


name, column name,………)]
Values(expression, expression);

Inserting data into a table from another

table: Syntax-

INSERT INTO table name


SELECT column name, column name,…….
FROM table name;
Name Dev Kumar Srivastav
RollNo 2300330109006

Insertion of selected data into a table from another table

Syntax-

INSERT INTO table name


SELECT column name, column name
FROM table name
WHERE column name= expression;

Retrieving of data from the

tables- Syntax-

SELECT * FROM table name;

The retrieving of specific columns from a

table-Syntax-

SELECT column name, column name,


FROM table name;

Elimination of duplicates from the select

statement-Syntax-

SELECT DISTINCT column name, column


name FROM table name;

Selecting a data set from table

data-Syntax-

SELECT column name, column


name FROM table name
WHERE search condition;
Name Dev Kumar Srivastav
RollNo 2300330109006

Department of Computer Science &Engineering

Experiment No.2
Q1.Create the following tables:
i) client_master

column name datat ype size


client_no varchar2 6
name varchar2 20
address1 varchar2 30
address2 varchar2 30
city varchar2 15
state varchar2 15
pincode number 6
bal_due number 10,2
Name Dev Kumar Srivastav
RollNo 2300330109006

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

Q2-Insert the following data into their respective tables:

Clientno Name city pincode state bal.due

0001 Ivan Bombay 400054 Maharashtra 15000


0002 Vandana Madras 780001 Tamilnadu 0
0003 Pramada Bombay 400057 Maharashtra 5000
0004 Basu Bombay 400056 Maharashtra 0
0005 Ravi Delhi 100001 2000
0006 Rukmini Bombay 400050 Maharashtra 0
Name Dev Kumar Srivastav
RollNo 2300330109006

Data for Product Master:


Product No. Desciption Profit% Unit Qty Reorde Sell Cost
Percent measured on hand r lvl price price

P00001 1.44floppies 5 piece 100 20 525 500


P03453 Monitors 6 piece 10 3 12000 11200
P06734 Mouse 5 piece 20 5 1050 500
P07865 1.22 floppies 5 piece 100 20 525 500
P07868 Keyboards 2 piece 10 3 3150 3050
P07885 CD Drive 2.5 piece 10 3 5250 5100
P07965 540 HDD 4 piece 10 3 8400 8000
P07975 1.44 Drive 5 piece 10 3 1050 1000
P08865 1.22 Drive 5 piece 2 3 1050 1000
Name Dev Kumar Srivastav
RollNo 2300330109006

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

iv) List all the clients who are located in Bombay.

v) Display the information for client no 0001 and 0002.

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’.

You might also like