1. Create a table DRUGDB with the fields given in below table and assuming data type of your own.

Consider the table DRUGDB. Write the SQL commands for queries given below:
(i) To increase the price of “Paracetamol” by 35.
(ii) To display the drugid, Rxid and pharmacy name of all records in descending order of their
price.
(iii) Display all the details of the drugs where name starts with ‘C’ and has ‘sh’ somewhere in the
name.
(iv) Display the drug name in lower case along with price rounded off to nearest interger.
(v) Delete the field name loc from drugdb table.

create database 12th;

use 12th;
CREATE TABLE DRUGDB (
RxID CHAR(10) PRIMARY KEY,
DrugID INTEGER,
Drugname VARCHAR(30),
Price DECIMAL(10,2),
PharmacyName VARCHAR(40),
Loc VARCHAR(20));
INSERT INTO DRUGDB (RxID, DrugID, Drugname, Price, PharmacyName, Loc)
VALUES ('R1000', 5476, 'AMLODIPINE', 100.00, 'RxPHARMACY', 'BEAS'),
('R1001', 2345, 'PARACETAMOL', 10.75, 'RAJPHARMACY', 'UNA'),
('R1002', 1236, 'NEBISTAR', 60.50, 'MYCHEMIST', 'SOLAN'),
('R1003', 6512, 'VITAPLUS', 150.50, 'MYCHEMIST', 'GURGAON'),
('R1004', 5631, 'COVISHIELD', 1050.50, 'OXFORD', 'PUNE');
select * from DRUGDB;

UPDATE DRUGDB SET price = price + 35 WHERE Drugname = 'PARACETAMOL';

select * from DRUGDB;
SELECT DRUGID, RXID, PHARMACYNAME
FROM DRUGDB
ORDER BY PRICE DESC;

SELECT * FROM DRUGDB WHERE DRUGNAME LIKE 'C%SH%' ;

SELECT LOWER(DRUGNAME), ROUND(PRICE, 0) FROM DRUGDB;

select * from DRUGDB;

ALTER TABLE DRUGDB DROP LOC;

select * from DRUGDB;
2. Write the commands in SQL for (i) to (v) and output for (vi) and (vii).
(i) To list the names of items and their unit price that have unit price less than 800 and discount
more than 5%.
(ii) To display the number of items that have more than 10% as discount.
(iii) To display item code and unit price in decreasing order of unit price.
(iv) To display the highest unit price of items.
(v) To display the names of items that have ‘Baby’ anywhere in their item names.
Find Output :
(vii) SELECT MID (Item,1,2) FROM Infant;
(viii) SELECT AVG(UnitPrice) FROM Infant WHERE DATEPURCHASE ‘2015–01–01’;

CREATE TABLE Infant (

ItemCode VARCHAR(20) PRIMARY KEY,

Items VARCHAR(100),

DatePurchase DATE,

UnitPrice DECIMAL(10, 2),

Discount DECIMAL(5, 2)

);

INSERT INTO Infant (ItemCode, Items, DatePurchase, UnitPrice, Discount)

VALUES

('101', 'Frock', '2016-01-23', 700.00, 10.00),

('102', 'Cot', '2015-09-23', 5000.00, 25.00),

('103', 'Soft Toy', '2016-06-17', 800.00, 10.00),

('104', 'Baby Socks', '2014-10-16', 100.00, 7.00),

('105', 'Baby Suit', '2015-09-20', 500.00, 5.00);

Select * from infant;

 select item,unitprice from infant where unitprice<800 and discount>5;

select count(*)from infant where discount>10;

select itemcode,unitprice from infant order by unitprice desc;

update infant unitprice=unitprice+unitprice*10/100;

select max(unitprice)from infan

SELECT Item FROM Infant WHERE Item LIKE "%Baby%”;

Output generation:
3. Write the commands in SQL for (i) to (v) and output for (vi) and (vii).

(i) To display the names of all Silver colored Cars.

(ii) To display name of car, Company and capacity of Cars in descending order of their seating capacity
(iii) To display the highest charges at which a vehicle can be hired from Carden.
(iv) To increase the charges by 5% for Suzuki company
(v) Write a SQL query to display CarName and Company together of those cars which name are having
'n' at anywhere or charges greater than 14
Write output for the following MYSQL queries:-
(i) SELECT MID(CarName, 2,3) FROM CARDEN;
(ii) SELECT MAX(CHARGES), COMPANY FROM CARDEN GROUP BY COMPANY;
create table carden (
Ccode integer,
CarName varchar(20),
Company varchar(20),
Color varchar(10),
Capacity integer,
Charges integer );

insert into carden values(501,"A-

star","Suzuki","Red",3,14); insert into carden
values(503,"Indigo","Tata","Silver",3,12),(502,"Innova","Toyota","White",7,15),(509,"SX4","Suzuki","S
ilv er",4,14),(510,"C Class","Mercedes","Red",4,35);

select * from carden;

select CarName from carden where Color='Silver';

select CarName,Company,Capacity from carden order by Capacity desc;

select max(Charges)from carden;

update carden set Charges=Charges*1.05 where Company='Suzuki';

select * from carden;

Select CarName,Company from carden where CarName like '%n%' or Charges>14;

4. Write the SQL Commands to perform the following:-
i) Create a student table with the student_id, name, and marks as attributes where the student_id
is the primary key.
ii) Insert the details of a new student in the above table.
iii) Use the select command to get the details of the students with marks more than 80.
iv) Write a SQL query to display names into capital letters, small letters, display first 3 letters of
name, display last 3 letters of name, display the position the letter 'A' in name.
v) Remove extra spaces from left, right and both sides from the text – “Informatics Practices
Class XII”.
vi) Display dayname, monthname, day, dayname, day of month, day of year for today’s date.
vii) Delete the details of a student in the above table.

CREATE TABLE STUDENT (

STUDENT_ID INTEGER PRIMARY
KEY, NAME VARCHAR(30),
MARKS DECIMAL(10,2) );
INSERT INTO STUDENT VALUES (125, 'ANJEEV SINGH', 98.5);

select * from STUDENT where MARKS>80;

select ucase(name) from student;

SELECT UPPER(NAME), LOWER(NAME), LEFT(NAME, 3), RIGHT(NAME,3),INSTR(NAME,'A')

FROM STUDENT;

SELECT LTRIM(" Informatics Practices Class XII "), RTRIM(" Informatics

Practices Class XII "), TRIM(" Informatics Practices Class XII ");

5.Consider the following tables Product and Client .Write SQL commands for the statement i to iv
Table : Product
P_ID Product Name Manufacturer Price
TP01 Talcum Powder LAK 40
FW05 Face Wash ABC 45
BS01 Bath Soap ABC 55
SH06 Shampoo XYZ 120
FW12 Face Wash XYZ 95
Table : Client
C_ID Client Name City P_ID
01 Cosmetic Shop Delhi FW05
06 Total Health Mumbai BS01
12 Live Life Delhi SH06
15 Pretty Woman Delhi FW12
16 Dreams Bangalore TP01
i) To display the details of those clients whose city is Delhi
ii) To display the details of products whose Price is in the range of 50 to 100
iii) To display the Clientname,City from table client, and ProductName and Pricefrom table
Product, with their corresponding matching P_ID.
iv) To increase the Price of all products by 10.

use arp;
create table product
(P_ID char(4),
Product_Name char(20),
Manufacture char(4),
Price int);

insert into product values("TP01","Talcum

 Powder","LAK",40); insert into product values("FW05","Face
Wash","ABC",45); insert into product values("BS01","Bath
Soap","ABC",55); insert into product
values("SH06","Shampoo","XYZ",120); insert into product
values("FW12","Face Wash","XYZ",95);

create table
client (C_ID
char(4),
Client_Name char(20),
City char(10),
P_id char(4));

insert into client values(01,"Cosmetic Shop","Delhi","FW05");

insert into client values(06,"Total Health","Mumbai","BS01");

insert into client values(12,"Live Life","Delhi","SH06");

insert into client values(15,"Pretty Women","Delhi","FW12");

INSERT INTO CLIENT VALUES(16,"Dreams","Banglore","TP01");

select*from client where city='Delhi';

select*from product where price between 50 and 100;

select Client_Name,City,Product_Name,Price from client,product where client.P_ID=Product.P_ID;

Update Product set price=price+10;

6. Consider the following tables Stock and Dealers .Write SQL commands for the statement i to v
 Table : Stock
ItemNo Item DCode Qty UnitPrice StockDate
5005 Ball Pen 0.5 102 100 16 31-Mar-
10
5003 Ball Pen 0.25 102 150 20 01-Jan-10
5002 Gel Pen 101 125 14 14-Feb-10
Premium
5006 Gel 101 200 22 01-Jan-09
Pen
Classic
5001 Eraser Small 102 210 5 19-Mar-
09
5004 Eraser Big 102 60 10 12-Dec-
09
5009 Sharpener 103 160 8 23-Jan-09
Classic
Table : Dealer

DCode DName
101 Reliable
Stationaries
103 Classic Plastics
102 Clear Deals
i) To display the details of all items in the Stock table in ascending order of StockDate
ii) To display ItemNo and ItemName of those items from the Stock table where UnitPrice
is more than Rupees 10
iii) To display the details of those items whose dealer code is 102 or Qty is more than 100
iv) To display Maximum UnitPrice of items for each dealer individually as per DCode from
the table Stock
v) To display the count of all items from the table Stock and group by Item
Create database d;
use d;
1)

2)

3)

4)

5)
 5. Consider the following tables Books and Issued
Table : Books
Book_ Publishers Book_name Author_name Pri Type Qty
ID ce
C0001 EPB Fast Cook Lata Kapoor 35 Cookery 5
5
F0001 First Publ The tears William 65 Fiction 20
Hopkins 0
T0001 EPB My First C++ Brian & 35 Text 10
Brooke 0
T0002 TDH C++ Brain AW 35 Text 15
works Rossaine 0
F0002 First Publ Thunderbolts Anna Roberts 75 Fiction 50
0

Table : Issued

Book_ID Quantity_Issued
T0001 4
C0001 5
F0001 2
Write the sql commands for the following

i) To show Book_name, Author_name and price of the books of First Publ Book_name
ii) To list the names from books of the text type
iii)To list the names and price from books in ascending order of their price.
iv) To increase the price of all books of EPB publisher by 50
v) To display the Book_ID ,Book_name and Quantity_Issued for all books which have been
issued.
vi) To insert a new row in the table issued having the following data:”F003”,1

Create database
d;
Use d;
Create table
Books (
Book_ID varchar(5),
Book_Name varchar(20),
Author_Name varchar(20),
Publisher varchar(20),
Price integer,
 Type char(10),
Qty integer);
Insert into Books values(‘C0001’,”EPB”,”Fast Cook”,”Latha Kapoor”,355,”cookery”,5);
Insert into Books values('F0001','First Publ','The tears','William Hopkins',650,'Fict',20);
Insert into Books values('T0001','EPB','My First C++',"Brian & Brooks",350,"text",15);
Insert into Books values('T0002',"EPB","C++ BrianWorks","A W ROSSAINE",350,"Text",50);
Insert into Books values('F0002',"First Publ","ThunderBolts","Anna Roberts",650,"Fict",50);

select * from Books;

Create table
Issued (
Bookid varchar(3),
Quantity_issued integer
);
Insert into Issued values('T01',4);
Insert into Issued values('C01',5);
Insert into Issued values('F01',2);

select * from issued;

Select Book_Name,Author_name,Price from Books where Book_name="First Publ";

Select Book_Name,Price from Books where Type="text";

Select Book_Name,Price from Books order by price;

 Update Books set price=price+50 where book_name="ERP";

Select books.Book_ID, Book_Name,Quantity_issued From books,issued Where

books.Book_Id=issued.Bookid;

Insert into issued values("F03",1);