Assignment On SQL - XII CS

Assignment on SQL

1.     The name column of a table 'student' is given below.

Name

Anu Sharma

Rohan Saini

Deepak Sing

Kannika Goal

Kunal Sharma

Based on the information, find the output of the following queries:

-         Select name from student where name like "%a"

-         Select name from student where name like "%h%";

2.     Shopkeeper needs to change the first name of one of his customers in table 'customer'. Which command should he use for the same?

3.     While creating table 'customer', Rahul forgot to add column 'price'. Which command is used to add new column in the table. Write the command to implement the same.

4.     Surpreeth wants to add two more records of customer in customer table. Which command is used to implement this?

5.     Differentiate between delete and drop table command.

6.     Differentiate between update and alter table command.

7.     Differentiate between order by and group by command.
8.  Create the following table items.

Column Name	Data Type	Size	Constraints
Itemno	Number	3	Primary Key
Iname	Varchar	15
Price	Number	10,2
Quantity	Number	3

9.     Insert the following information:
Table: Item

Itemno	Iname	Price	Quantity
101	Soap	50	100
102	Powder	100	50
103	Face Cream	150	25
104	Pen	50	200
105	Soap box	20	100

10. Write queries based upon item table given in q. no 9

a) Display all items information.

b) Display item name and price value.

c) Display soap information.

d) Display the item information whose name starts with letter 's'.

e) Display a report with item number, item name and total price. (total price = price * quantity).

f) Display item table information in ascending order based upon item name.

g) Display item name and price in descending order based upon price.

h) Display item name, whose price is in between 50 to 100.

i) Add new column totalprice with number (10, 2).

j) Increase price value by 100.

k) Fill up totalprice = price * quantity.

l) Remove powder information.

m) Remove totalprice column.

n) Remove whole item structure.

10. Write outputs based upon item table given in q. no 9.

a) select sum(price) from item;

b) select avg(price) from item;

c) select min(price) from item;

d) select max(price) from item;

e) select count(price) from item;

f) select distinct price from item;

g) select count(distinct price) from item;

h) select iname,price*quantity from item;