SQL Assignment for BCAIII year

Create the following table

a)Item Table

                                                           

Column name	Datatype	Size
Item_no	Varchar	3
Desc	char	20
Qty_on_Hand	Number	4
Unit_price	Number	6

b)Sales Table

Column name	Datatype	Size
Area	Varchar	20
Item_no	char	4
Smno	Char	4
Name	Char	20
Qty_Sold	number	4

c)Emp Table

Column name	Datatype	Size
Empno	number	4
Ename	char	20
job	Char	10
hiredate	date
sal	number	9,2
deptno	Number	2
Comm	number	7,2

d)Dept Table

Column name	Datatype	Size
Deptno	number	2
Dname	char	20
Loc	VarChar	10

Q.2Describe the structure of the table Item,Sales,Emp,Dept.

Q.3 How you can view the Table name and no of rows in each table present in database.

Q.4 Modify the table sales by adding the column Sales_date of the type date.

Q.5 Modify the table Structure Sales by increasing the columnName from 20 to 25.

DATA RETRIEVAL

1.List only those rows whose salesman name is ‘Raghu’ from table Sales.

2.List only those rows whose unit price value is greater than Rs 2000 from table item.

3.List the distinct salesman rows from  table SALES.

4.List the names of analyst and salesman from EMP table.

5.List the names of employee whose name start with ’S’ and end with ’S’.

6.List the names of employee whose having ’I’ as second character.

7.List the employee name and hiredate in descending order of hiredate.

8.List the Dname where Dname is Clerikal,manager,admin from DEPT table..

9.List the record of employee where ename ram,shayam,raghu  are not there from EMP.

10.List the records whose salary is between 20,000 to 30,000 from EMP.

11.List the records where Loc is either delhi or jaipur from DEPT.

12.Count the number of records in EMP table.

DATA MAINTENANCE

1.Insert the following rows in table Sales:

JawaharNagar,107,S03,Raghu,3

2.Insert the below data in table item

115,Sony,25,10

3.Insert the following row in table EMP:

7311,Anthony,Clerk,18 july2007,3000,null,20

4.Insert a row into EMP by Parameter Substitution.

5.Using Update command, increase everybody salary by 10% in EMP table.

6.Give a commission of rs500 to everyone in table EMP.

7.Update the salary 25000 where ename is shyam.