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