Téléchargez aux formats PDF ou lisez en ligne sur Scribd
OBJE VE: To Create tables and insert data in them.
Brief Description : DDL statements for creating a table :
CREATE TABLE tablename
(columnname datatype(size) , columnname datatype(size)):
Insertion of data into tables
INSERT INTO tablename
{(colummname,columnnaie..... )]
Values(expression, expression);
Q 1. Create Client_master table +
mysql» CREATE DATABASE student:
Query OK, | row affceted (0.01 sec)
mysql>
Database changed
mysql> CREATE TABLE client_master (
client_no VARCHAR(6),
name VARCHAR(20),
address VARCHAR(20),
address? VARCHAR(20).
ry VARCHAR(15),
=> state VARCHAR(I5),
pincode numeric(6).
bal_due numeric( 10.2)
>)
Query OK, 0 rows affected (0.02 see)
Q 2. Create Product_master table t
mysql> CREATE TABLE Product
> Product_no VARCHAR(?).
-> Deseription VARCHAR(IS5), -> Profit_percent numeric(2),
> Unit_measure VARCHAR(7),
Qty_on_hand numeric(4),
Reorder_IvI numeric(3),
> Sell_price numetic(6),
merie(6)
Cost_price 1
>)
Query OK, 0 rows affected (0.02 see)
Q 2. Insert data in respective tables :
In client_master table :
INSERT INTO client_master (client_no, name, addres
bal due)
> VALUES
> (O01 "Ivan','123 Main St, ‘Apt 101', Bombay’, Maharashtra’, 400054, 15000),
> (002', Vandana’, '456 Oak St’, 'Suite 202’, Madras’, Tamilnadu’, 7890 12, 0),
> ('003', 'Pramada’, '789 Pine St’, "Unit 303", ‘Bombay’, 'Maharashtra’, 345678, 5000),
> (004', Basu’, "987 Elm St’, ‘Apt 404’, ‘Bombay’, 'Maharashtra’, 901234, 0),
> (005, Ravi’, 654 Bireh St, ‘Api 111’, ‘Delhi’, ‘Delhi, 567890, 2000),
> (006, "Rukhmini’, 321 Cedar St’, 'Unit 606", Bombay", 'Maharashtra’, 432109, 0);
Query OK, 6 rows affeeted (0.01 see)
, address2, city, state, pincode,
In Product_master table :
mysql> INSERT INTO Product_master (Product_no, Deseription, Profit_percent,
Unit_measure, Qty_on_hand, Reorder_Iv1, Sell_price, Cost_price)
> VALUES
> ('P00001', '1.44floppie: 'picee’, 100, 20, 525, 500),
> (‘PO3453', Monitors’, 6, ‘piece’, 10, 3, 12000, 11200),
> ((P06734", 'Mouse', 5, tpicce', 20, 5, 1050, 500),
> (‘PO786S',"1.22floppies', 5, ‘piece’, 100, 20, 325, 500)
Query OK, 4 rows affeeted (0.01 sev) OBJECTIV
Brief Description : Syntax:
SELECT columaname , columnname,
FROM tablename;
client_master
er eaectsaaens niece ees
|
i Lo
cy CS ae Se
cee Ela aes cere
Esta mL eee Cras
Cro a Cedar St | Unit 686 | Bombay
6 rows in set (0.08 sec)
Produet_master :
cee
To use SELECT Command for retrieving data from tables.
Lee
Pers
Cae
eC
CR
5000.00
Peery
er
Pept) OBJE VE: To understand and use
a) DML Commands UPDATE and DELETE
b) Transaction Control Statements COMMIT , ROLLBACK and SAVEPOINT
Brief Description: The UPDATE statement is used to modify the existing records in a table.
The DELETE statement is used to delete existing records in a table.
UPDATE
mysql> UPDATE Product_master
> SET Sell_price = 1150.00
WHERE Description ='1.44floppies;
Query OK, | row affected (0.00 sec)
Rows matched: 1 Changed: 1 Wamings: 0
ere ee eT
ee 7 nchand | Rees
DELETE :
mysql> DELETE FROM clicnt_master
> WHERE client_no='001';
Query OK, 1 row affected (0.00 see)
rere
Taree! Trae
ar y 901234
sweety rea
re Oca COMMIT. ROLLBACK
mysql> START TRAN
Query OK, 0 rows aff
AVEPOINT :
SACTION;
red (0.00 see)
mysql> SAVEPOINT my_savepoint;
Query OK, () rows affected (0.00 sec)
mysql> DELETE FROM clicnt_master
-> WHERE client no = "00
Query OK, | row affected (0,00 see)
mysql ROLLBACK TO my_savepoint;
Query OK, 0 rows affected (0.00 see)
mysql> COMMMIT:
eee
ce
coy
coy
Petre
Rees | 5000.00
esc a a)
Maharashtra | | cy
cre | 2080.00
eee
aa eireeres MENT NO.
OBJECTIV
To understand and implement Integrity Constraints using full DDL.
commands.
1. Create table
2. Alter table
mysql > create table Sales_Master(
salesman_no varehar(6) primary key cheek(salesman_no like 's%"),
sal_name varehur(20) not null
address varehar(20) not null,
eity varehar(20),
pincode numeric(6),
sal_amt numerie(8, 2) not null check(sal_amt > 0),
tgt_to_get numerie(6, 2) not null check(tgt_to_get > 0),
ytd_sales numerie(6, 2) not null cheek(ytd_sales
ks varchar(30)
mysql> SELECT * FROM Sales_Master;
Empty set (0.00 sec)
Create Sales_order
mysql> CREATE TABLE sales_order (
= s_order no VARCHAR(6) PRIMARY KEY CHECK(s_order_no LIKE '0%’),
s_order_date DATE,
> client no VARCHAR(6),
salesman no VARCHAR(6) REFERENCES Sales_Master(salesman_no),
dely_type CHAR(1) CHECK (dely_type IN ('P. 'p)),
billed_yn CHAR(1),
dely_date DATE. CHECK (dely_date >— s_order_date),
order status. VARCHAR(10) CHECK (order_status IN (‘inprocess’, ‘fulfilled’,
"backorder, ‘eanceled’))
>)
2100910130011 MLT LAB(AI) s_order_details
mysql> CREATE TABLE sales_order_details
> ¢
> sorder no VARCHAR(6) REFERENCES sales_order(s_order_no),
> product_no VARCHAR(6) PRIMARY KEY,
> qty_order. ~ NUMERIC(8),
> qty disp | NUMERIC(8),
> produet_rate NUMERIC(10, 2)
>)
Query OK, 0 rows affected (0.02 see)
mysql> SELECT * FROM sales_order_details;
Lay sa CEL oo)
Query: Make the primary key to client_no in client_master
mysql> alter table client_master add constraint pk primary key(client_no);
Query OK, 5 rows affected (0.24 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query: Make the primary key (o product_no in produet_master
mysql> alter table product_master add constraint pk primary key(product_no);
Query OK, 4 rows affected (0.18 see)
Records: 4 Duplicates: 0 Warnings: 0
Query: Add foreign key constraint in Sales_Order:- Client_no fort
references client_no of client_master
mysql> alter table
client_master(client_no):
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
les_Order add constraint fk foreign key(Client_no) references
Query: Add foreign key constraint in Sales_Order_Details:- Product_no
foreign key refernces product_no of produet_master
mysql> alter table Sales_Order_Details add constraint fk1_ foreign key(Product_no) references
product_master(product_no);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0 #Alter the size of field phone_no in client_master
mysql alter table client_master add phone_no numeric( 10);
Query OK, 5 rows affected (0.20 see)
Records: 5 Duplicates: 0 Warnings: 0
#elient_master
mysql> dese client_mast
ts
Field Type| Null Key Default Fxtra
rs
client no | varchar(6) NO PRI
name | varchar(20) YES NULL
city | varehar(15) YES NULL.
stare | varehar(15) YES NULL.
pincode | deeimal(6,0) YES NULL
bal due | decimal(10,2) YES NULL
| phone_no | decimal 10.0) YES NULL
a
T rows in set (0.00 sec)
#fil
g data into Sales_Master
mysql> insert into Sales_Master values(S00001 Kiren'7A/14
Worli’Bombay' 400002,3000,100,50,'Good."Mah’);
Query OK, 1 row affected (0,04 see)
mysq[> insert into Sales_Master values(300002,’Manish
Nariman’, Bombay',400002,3000, 100, 100,Good’’Mah");
Query OK, 1 row affected (01,03 see)
mysql> insert into Sales_Mester vahues(500003,'Ravi',P-7
Bandra’ /Bombay’,400032.3000, 100, 100,'Good’,’Mah’);
Query OK, I row affected (01.03 see)
mysq[> insert into Sales_Master values(500004,'Ashish','A/5
Juhu! Bombay’,400044,3500,200,150,'Good' Mah’);
Query OK, 1 row affected (01.03 see)
64, EXPERIMENT NO. -5
OBJECTIVE: To retrieve the data using the concept of
(a) Join
(b) Set operators(Union jnterseet and minus)
THEORY AND CONCEPTS :
A JOIN clause is used to combine rows fram two or more tables, based on a related column
between them.
‘Types of the JOINs in SQL:
1. (NNER) JOIN: Retums records that have matching values in both tables
2. LEFT (OUTER) JOIN: Return all records from the lef table, and the matched
records from the right table
3. RIGHT (OUTER) JOIN: Retum all records from the
records from the left table
4, FULL (OUTER) JOIN: Return all records when there is a match in either left or right
Table
ht table, and the matched
SQL supports few Set operations which can be performed on the table data. These are
used (0 get meaningful results from data stored in the table, under different special
conditions.
1, UNION
2. UNION ALL.
3. INTERSECT
4, MINUS
SYNTAX/COMMANDS USED:
1. SELECT columns FROM table!
LEFT [OUTER] JOIN table2
ON table! column = [Link]
2. SELECT columns FROM table
RIGHT [OUTER] JOIN table2
ON table! column = [Link] 3, SELECT columns FROM table]
INNER JOIN table2
ON table!.column = [Link];
4, SELECT column_name (s) FROM table! UNION
SELECT column_name (s) FROM table2
5, SELECT column_list_l FROM table_
MINUS
SELECT columns _list_2 FROM table 2;
6. SELECT column_list FROM table_|
INTERSECT
(SELECT column_list FROM table 2)
7. SELECT * FROM First
UNION
SELECT * From Second
8, SELECT * FROM First
INTERSECT
SELECT * FROM Second;
9. SELECT * FROM First
MINUS,
SELECT * FROM Sec
10, SELECT EXTRACT(MONTH FROM "201 7-06-15"); Query : The order [Link] name and day of week on which clients placed their
Order.
mysql> select s_order_no,name,extract(day fom s_order_date) from sales_order
s.client_master ¢ where s.client_no= ¢.client no;
+-—___+.. op
s_order no name extract(day from s_order_date)
es —<$$__________+
010008 Ravi 24
016865 Pramada 18
019001 Ivan 12
019002 | Vandana
019003 Ivan 3
046866 Basu 20
++
Ny
Query: Display the month and date when the order must be delivered and the name
of the salesman to whom the order was placed.
mysql> select sal_name,extraci(month from dely_date),dely_date from sales master
p.sales_order where p.salesman_no=o.salesman_no;
be. a
sal name extract(month from dely date) dely_date
+ +. +
kiran 1 1996-01-20
kiran 4 1996-04-07
manish |1 1996-01-27
ravi 1 1996-01-20
ashish 5 1996-05-26
ashish 5 1996-05-22
+. a ‘ind out the names of products that have been sold to “Ivan”,
mysql>SELECT description
FROM sales order s,
sales_order_details d,
product_master p,
client master c
WHERE s.s_order no = d.s_order no
AND d.product_no = p.product_no
AND [Link] no client_no
AND ¢.NAME = "Ivan;
+ +
description
+ 4
1.44floppies
CD Drive
540 HDD
1 ddfloppies
Monitors
Query: For each sales order display the name of the client and the salesman.
mysql> SELECT NAME,
sal name
FROM. client_master c.
sales_master s,
sales order t
WHERE [Link] no = tsalesman no
AND [Link] no = telient no;
+ name sal name
+
+
Ivan. kiran
Ivan kiran
| Vandana manish
Pramada ravi
Ravi ashish
Basu ashish
+ +.
Query : Find out the names of clients who have purchased “CD DRIVE”.
mysql> SELECT NAME.
FROM client_master ¢,
product_master p,
sales order 5,
sales_order_details d
WHERE cclient no = s.client_no
AND s.s_order no = d.s_order no
AND d.produet_no = p.product_no
AND [Link] = 'CD Driv
ee
name
tt
Ivan
as
Query: List the product_no and s_order_no of customers who have ordered less than
5 quantity of product “1.44 floppies”.
mysql> SELECT product_master.product_no,
sales_order_details.s_order_no
FROM _ produet_master, sales_order_details
WHERE product_master.product_no = dbms [KC
AND description = "1.44 Floppies"
AND sales_order_details.qty_order <5;
S1] sales order details.product_no
+-—__—_——_—+-——____+
|PRODUCT_NO S_ORDER_NO
+-—__+-___+
PqOOO! 019001
PG0001 019003
+-—_____+-—_____+
Query: Display all clients and the salesman in the city of Bombay.
mysql> SELECT NAME ,
client no,
salesman no,
sal name
FROM client_master ¢.sales:_ master
he.
Ivan KIRAN
Ivan KIRAN
| Vandana MANISH
Basu MANISH
Pramada RAVI
Ravi ASHISH
+
+ EXPERIMENT NO-_ 6
Aim: To use aggregate functions in SQL
Theory and Concepts:
By definition, an aggregate function performs a calculation on a set of values and returns a single value, Often,
aggregate functions are accompanied by the GROUP BY clause of the SELECT statement
MySQL provides many aggregate functions that include AVG, COUNT, SUM, MIN, MAX, etc. An
aggregate function ignores NULL values wien it performs calculation except for the COUNT funetion.
AVG Function:
The AVG function calculates the average value of a set af values. It ignores NULL values in the calculation
SELECT AVG(column_name) [as name]
FROM table_name
COUNT Function;
The COUNT funetion returas the number of the rows ina table.
The COUNT function can be used as COUNT(*) and COUNT(DISTINCT expression)
SELECT COUNT(*) AS Total FROM
Ie_name SUM function
‘The SUM function returns the sum of a set of values. The SUM function ignores NULL values. Ino matching,
row found, the SUM fianetion returt's ¢ NULL value
SELECT sum(colum
FROM table_name;
name) [as (otal]
MAX Funetior
‘The MAX function returns the maximum value in a set of values
SELECT MAX (price) [as highest_price]
FROM table_names
MIN Function :
‘The MIN function returns the minimum value in a set of values.
SPLECT MIN(Price) [as lowest_price] FROM.
(able_names Query:. Count the total no of orders. mysql> select
count(s_order_no) from sales_order;
| countis_order_no)
Query: Calculate the average cost price of all the products.
mysql> select avgicost_price) from product_master;
| avg(cost_price)
3427.78,
Query:. Calculate the minimum sale price of the products. mysql select
min(sell_price) from product_master;
| min(scll_priee)
52
Query: Determine the max and min cost price. Rename the title as “max price’ and ‘min price”
mysql select min(cost_price) as 'min price',max(cost_price) as 'max price! from product_master;
min price | max price
500
11200
Q5. Count the no of products having price greater than or equal to 1590.
select count(product_no) from product_master where sell_price> 1500;
product_no) Query: Find out the product name and their quantities to be delivered. mysql> select
description.qty_order from product_master pm , sitles_order_details sod where pm.product_no =
sod.preduct_no group by(deseription);
[description —gty_order
Lag
Drive 1
1.44 floppies 4
| CD Drive 2
| Keyboards 3
| Monitors 2 Mouse
Query:. Find the product no and their quantities for orders placed by client_no “0001” and “0002”
mysql> select product_no,qty_onier from sales_order so, sales_order_details sod where sod.s_order_no =
so.s_order_no and (client_no=0001' oF client_ne ="0002')
| product_no | aty_order |
po788s
07965
p03453
Query:. Find the product no and quantities for orders placed by * Vandana * and * Ivan *, mysql> select
sed.product_no , sod.gty_order from clieat_masterent . sales_onder so , sales_order_details sod where
so.s_ordler_no ~ sod.s_onder_no and em elient_no ~ so.client_no and em. Name in (‘Ivan',’ Vandana’);
| product no qty order
pOT88S 2
poT96s
pOd453
Query:. Find order no , client no. , and salesman no. where more than one salesman has received a client
mysql> select s_order_no , client_no ,salesman_no from sales_order group by(salesman_no)
having(count(clicnt_ne)=1), order_no clignt no | salesman_no
019002 0002500001
19001 0001 500002
Query: Print the description and toxal quantity sold for cael product. mysgl select de
ption ,
ay_disp from product_master pin , sales_ovder_details sod where pm.produet_no ~ sod product
| description yty_disp
1.4 floppies 4
| Monitors 2 Mouse
1
| Keyboards 3
| CD Drive: 1
1.44 Drive D
Query: Find the value of cach product sold.
mysql> select description, sell_price from produet_master group by (description); ~
| description | sell_price
1.22 Drive 1950 1.22MMoppies
| 525 144 Drive | 1050
1 44floppies 525
[540 HDD 8400
| CD Drive
| keyboards 3150
| Monitors 12000
Mouse 1050
Query: Select produet_no, qty_order for exch produ
|. mysqP> select
product_no, qly_order from sale
+_order_devails;
| product_ne gty_order odour
pO3453
p06734
pOT8OS
pOTS8S
07965
pO7075 1
4
1
3
Query: Select product_no, description and qty_oréer for each product.
mysql> select description, pm.product_no, qty_order from product_master pm, sales_order_details sod where
pm. produet_no = sed.product_ne;
description | product_no gty_order
1.44floppies P0000 4
| Monitors | P03453 2 Mouse
06734 1
[CD Drive | POTSRS
[keyboards | PO7868
[S40 HDD Po7965
1.44 Drive P7975 EXPERIMENT 7
Aim: To write nested subqueries and correlated subqueries.
‘Theory and Concepts:
A Subquery or Inner query or a Nested query is a query within another SQL. query and
embedded within the WHERE clause.
A subquery is used to return data that will be used in the main query as a condition to further
restrict the data to be retrieved.
Subqueties can be used with the SELECT, INSERT, UPDATE, and DELETE statements along
. IN, BETWEEN, ete
with the operators like =. <>,
There are mainly two types of nested queries:
Independent Nested Queries: In independent nested queries, query execution starts
from innermost query to outermost queries. The execution of inner query is independent
of outer query, but the result of inner query is used in execution of outer query. Various
operators like IN, NOT IN, ANY, ALL ete are used in writing independent nested
n co-related nested queries, the output of inner
queries. Co-related Nested Que
query depends on the row which is being currently executed in outer query.
Subqueries with the INSERT Statement
Subqueries also can be used with INSERT statements. The INSERT statement uses the
1ed from the subquery to insert into another table. The selected data in the
1, date or number functions.
data retu
subquery can be modified with any of the char
Subqueries with the UPDATE Statement
The subquery can be used in conjunction with the UPDATE statement, Fither single or
multiple columns in a table can be updated when using a subquery with the UPDATE
statement.
Subqueries with the DELETE Statement
The subquery can be used in conjunction with the DELETE statement. mysql> select * Irom client_master;
clint no name city stale pineode bal due phone_no
| Vandana | Madras | Tamil Nadu 780001 1600.00 NULL.
| Pramada | Bombay | Maharastra | 400057 | 5000.00 | NULL
| Basu | Bombay | Maharastra | 400056 | 0.00. NULL,
[Ravi | Bombay | Delhi | 100001 | 2000.00, NULL
| Rukmini | Bombay | Maharastra | 400050 | 0.00 | NULL
5 rows in set (0.00 see)
Query: Display the customer name, address, city and pincode for the clients who live in the
same city as ‘Basu’, Basu’s details should not be displayed mysql> seleet * from client master
where cit su)
elect city from eli
\_master where nam
client no name city state pineade bal due phone_no
3 | Pramada| Bombay | Mahar 5000.00 | NULL
4 | Basu | Bombay | Maharastra | 400096 | 0.00 NULL
S [Ravi | Bombay Delhi | 100001 | 2000.00) NULL.
6
| Rukmini | Bombay | Maharastra | 400050 | 0.00 NULL
4 rows in se! (0.00 see)
Quer:
balance due as “Basu”. Basu’s details should not be displayed mysql> select name from
client master where city~(select city from elient_master where name='Rasu') and bal_due=(select hal_due
Display the details of all the customers who live in the same city and has the same
name~'Basu'); ~
fiom elient_master whe:
name
Basu
Rukmini
2 rows in set (0.00 see)
Query: Display the details of all the customers who live in the same city and has the same
balance due as ‘Basu’, Basu’s details should not be displayed mysql select * fiom elient
master
Where city-(Seleet city from client master where nameBusu’) and bal_dlue(select bal due from
client_master where name~Bost!) and name=>"Basu clint no name city stale pineode bal due phone_na
6 Rukmini Bombay | Maharastra 400050 0.00 NULL
I row in set (0.00 sec)
mysql>
elect * from product_master;
wand | Reorder_IvI | sell_price | cost_price
| product_no | Description profile_percent | unit_m
jPon0e! | L.44floppies 5 | piece 100 20 00
|P03453 | Monitors 6 | piece 10 3 11200
|P06734 | Mouse S| pieve 20 s 500
| POT86S 1.22floppies S| piece 100 20
4 rows in set (0.00 sec)
Query: Display product details of those products that have profit% less than all produets that
have *1.44floppics’ in their descriptions,
mysql> select * from product_master where profile percent<(seleet profile percent from product_ master where
Description=" 1.44 floppies
Empty set (0.01 see)
Query: Display the names of clicat who have placed orders worth Rs. 10000 or more
mysql> select name from client_master.Sales_Orcler_Details, Sales_Orcler where produet_rate > 10000 and
client_master.client_no= Sales Order.client_no and
Sales Order order_no~Sales_Order_Details.s_order_no:
Empty sei (0.00 see) Query: Display the client names who have placed orders before any orders placed by
client no ‘0003
mysql> select [Link]
s_onler_date select * from Sales: Masters
ect s_onder_date from Sales_Order where client_no-0003); Empy
rom client_master ¢, Sales_Order s where e.client_no=s.client_no and
set (0.00 sec)
salesman no | sal_neame | add
city pincode sal amt tgt to get] ytd sales | remarks. State
00001 Kiren A414 Worli Bombay 400002 | 3000.00 100.00 50.00 Good Mah
00002 Manish | 64, Nariman | Bombay 400002 | 3000.00 100.00 100.00 Good Mah
500003 Ravi | P-7 Bandra | Bombay 400032 | 3000.00 100.00 100.00 Good Mah
00004 Ashish A/S Juhu Bombay 400044 3500.00 200.00 150.00 Good Mah
4 rows in
set (0.00 see) EXPERIMENT 8
Aim: To create
i. Indexes ii. Views.
Theory and Concepts:
SQL CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables.
Indexes are used to retrieve data from the database very fast, The users cannot see the indexes,
they are just used to speed up searchesqueries,
CREATE INDEX Syntax
Creates an index on a table, Duplicate values are allowed:
CREATE INDEX index name
ON table_name (columal, column2, ..)s
SQL CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rews and columns, just like a real (able. The fields in a view are fields from
‘one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the
data were coming from one single table.
CREATE VIEW Syntax
CREATE VIEW view
column2,
FROM table_name WIIFRE condition;
ame AS SELECT columnl,
Query: Create an index on the table client_s
mysql” create index field on client_master(client_nol:
Query OK, 0 rows affected (0.18 see) Records: 0 Duplicates: 0
w 0
aster, field elient_no
nysql> dese Sales_ Order
Field Type Null Key Default Extra s order no | varchar(6) |NO | PRI|NULL |
S_order_date | date |YES | | NULL
client_no | varehar(6) | YES | (NULL |
salesman_no | varchar(6) | YES | (NULL | |
dely type [char(l) | YES | | NULL
billed yn |char(l) | YES | | NULL
dely_date | date |YES | | NULL
order.
tatus | varchar(10)|YES | |NULL |
1 srs in
set (0.00 see),
Query: Create an index on the sales_order, field s_order_no mysql> ereate index field on
Sales_Orden(s_order_no};
Query OK, 0 rows affected (0.17 see) Records: 0 Duplicates:
0 Wamings: 0 mysql> dese Sales Master;
Field Type Null Key Default Petra
| salesman_no | varchar(6) | NO_ | PRI| NULL.
|salname | varchar(20) [NO | NULL
Jadéress | varehar(20) |NO_| | NULL
Jeity | varehar(20) | YES | |NULL |
|pincode | decimal(6,0) | YES | NULL
|salamt | decimal(s,2)|NO_ | |NULL |
| tat to_get | decimal(6.2)|NO_ | | NULL! |
| ytd sales | decimal(6.2)|NO | | NULL i
Jremarks | varehar(30) | YES | | NULL |
| State | varchar(20) | YES | | NULL |
10 rows in set (0.00 see)
Query: Create an unique index on the table salesman_master, field salesman_no
mysql> create unique index fields on Sales Master(salesman_no);
Query OK. 0 rows affected (0.14 see) Records: 0 Duplicates:
0 Warnings: 0
mysql> dese Sales_Order_Details;
Field Type Null Key Default Extra
io |varcha(6) NO PRI | product_no | varehar(6) | NO | PRL |
Jaty_order | decimal(8,0) | YES NULL
laty_disp | decimal(8,0) | YES NULL
| product_rate | decimal(10,2)/YES | NULL |
——— 1 1 1 5 ows in
set (0.00 see)
Query: Create an composite index on the sales_order_details table for the column s_order_no and
product_no.
mysql> create index field on Sales_Order_Details(s_order_no, produet_no};
Query OK, 0 rows affected (0.16 sec)
Records: 0) Duplicates: 0 Wamings: 0
Query: Create view on salesman_master whose sal_amt <3500
Master where sal_amt=3500; Query OK, 0 rows
mysql> create view V as select * from Sal
affected (0.03 see)
Query: Create a view client_view on client_master and rename the columns as name,
new_city, pincode_new, state new
mysql
name .city,pincode,state from elient_master
ate view clion|_view(name_new:city_new.pincode_new.state_new) as select
ty OK, 0 rows affected (0.03 see)
Query: Sclect the client names from client_view who lives in city Bombay” mysql select
name_new from elient_view where cily_nes="Bombay";
| Pramada
| Basu
| Ravi
| Rukmini
4 rows in set (0.00 see)
Query: Drop the view client_view mysql drop view client_view:Query OK, 0 rows affected